Pros Use This Technique to Avoid PivotTables

If you find PivotTables a bit daunting, then Tables are a great alternative
because they can also be filtered with Slicers to create an interactive report. And with the right functions you can aggregate the data while ignoring the rows that are filtered by the Slicer.

🎓RELATED LESSONS:

LEARN MORE
===========

🔔 SUBSCRIBE if you’d like more tips and tutorials like this.

📢 Please leave me a COMMENT. I read them all!


🎁 SHARE this video and spread the Excel love.

Or if you’re short of time, please click the 👍

⏲ TIMESTAMPS
==============
0:00 Slicers for Excel Tables
0:15 Formatting data in a Table
0:50 Inserting & working with Slicers
3:31 Linking Slicers to Formulas
6:13 Limitations

#pivottables #dataanalysis #exceltips

[ziggle]

Great job Mynda! I clearly need to use slicers more. Also, the tip on SUBTOTAL and hidden rows (manually hidden vs filtered) was really useful. Thanks.

[dougmphilly]

I never knew that i could use subtotals with tables like this. you are one of the best.

[happymystic]

This is a very helpful video. I thoroughly enjoy these bite-sized ‘snacks’ that are not only easy to consume but also convenient to include in the daily work routine. More of them, please 👍👍👍 … Cheers

[richardhay]

"Clear All" on the QAT? Not on my QAT! Alt, A, C always!

[kebincui]

Excellent as always.👍🌹 I have been using this technique for teh report runner I made for my colleagues in the past years. As compared to pivot tables, this has the advantage of presenting summary information in addition to the detailed/filtered supporting data underneath. This is easy for verification and across checks for my colleagues if they want. The tables I use this method on are the tables that are loaded from power query; therefore they are updated automatically every time I when the power quries refresh.

[dave]

Wow! Very informative and very impressive presentation. I was impressed enough that it got me to both "like" and subscribe. 😀

[mrpdmasta]

Best part about tables vs pivot tables. You can share data with colleagues in a locked table and they can still filter and sort it. Can’t do that with pivot tables, where you can’t sort by ascending or descending

[sc]

Matchless as always Mynda. Source no doubt juicy enough of great info of Excel's priceless feature of Slicers

[theharsh]

She is unbelievably helpful with these vids.

[GeertDelmulle]

I completely agree with everything you said in this video.
Including aggregations on top and having the “Clear All” function on the QAT: it’s on mine for years now, and I instruct everyone to do the same.
Thanks Mynda for this great recap! :-)

[gerbherb]

@ 2:40 Tip on clearing all filters, not using the mouse: press CTRL+SHIFT+L two times.

[sørenf-ls]

Super - but can you build a table with a column that summarize over the shown values i.e. total sales in area A, B C etc.? I believe this is 99% of pivot table usage

[chrism]

Thanks Mynda! Great video. I'm glad Excel has so many ways to get things done. And with the new PIVOTBY function there are even more.

[shoppersdream]

Thanks once again. I wanted to make my Report look like yours. Quick Question, I copied and pasted the pivot table without a data source. The problem is that it was not in table format. It was in a range so now when I try to make it a table, it gives filters on the first row. The first 3 rows have column labels and row labels. The third row is the one I want Filters on and not on the first row. How do I do that without removing any column labels or row labels? Also, I am trying to sort it ascending but it puts the Grand Total in between and messes up the Grand Total values. Any advice Please? Thanks

[vishwanath]

Yet another gem from Mynda!! Thank you 🙏🏽

[richardhay]

Actually "pros" will now be using PIVOTBY and GROUPBY!! Time for a video on these?

[Rkcuddles]

Can’t get my teams to use tables and spill formulas… ‘didn’t copy the formula down’ excuse gets old quickly

[Toqeer-Grt]

how to make slicer for 1st row ( witch contains headings ). witch you use in the middle of your video ( Pro use this technique to avoid pivot table ) please send the link

[ar]

You are one of the best Excel teachers on YT! I have a very large CSV file for stock data (date, symbol, close, volume) and I have an Excel 365 spreadsheet that looks like a stock screener with many column titles: symbol, name, price, volume and several price change % periods with formulas for calculations. I don't want to see the CSV data in my spreadsheet (screener), but I want the spreadsheet to use the CSV data for formulations. What would be the best way to do this please? I also need to be able to sort the columns. PS I don't like =stockhistory or data scraping. Thank you again for helping others. I try to do this every day.

[shoppersdream]

Mynda, Which theme are you using? I don't have this color. Thanks