Pivot Tables and Tables in Excel: Tips, Tricks, and Best Practices
Updated: Jan 17
Using Excel without Pivot and Tables is like going to the beach when there is no sun: you can do it, but what is the point? People sometimes avoid tables because they are generally used for big data sets, and some people shudder at the thought of big data sets in general and particularly in Excel. That is not a surprise given that sometimes Excel can be slow, there is a higher chance of making an error, and it is hard to analyze and visualize larger amounts of data.
Excel has its limits, like everything else. But that is why knowing Pivot and Tables can help you a lot in your everyday work life. They are built for data processing.
Pivot Tables
If I have to pick one single best feature in Excel, it would definitely be Pivot tables. I worked with them a lot while crunching the numbers in the financial sector, and my clients used them even more in their analysis. So basically, they are widely used, and knowing how to handle them can help you a lot in your everyday work - they are powerful, easy to use, and will save you a lot of time. Creating them is a first step for sure:
When creating pivots, Excel will automatically add two new pop-ups: PivotTable Analyze and Design, and you can find all the features you need to work with them there.
RECOMMENDED PIVOT
A super helpful feature I overlooked for the first couple of years working in Excel is Recommended PivotTables. You can find it in the Insert tab. Excel will analyze the data and give you some templates to help you make your pivots faster - use them.
DESIGN
Design-wise, you can edit your tables pretty fast, choose tabular, outline, or compact form, show or hide subtotals and fill in the blanks (repeat line items). You can also change colors and add banded rows or columns.
VALUES
You can show values in a couple of ways in Pivot tables. Usually, the value is shown as SUM or COUNT automatically, but you can show AVG, MIN, MAX, etc. You can also show value as an index or percentage of grand/row/column total, etc. There are options to do it using both right-click in the pivot table or in the Values window, and it’s super fast.
SLICER
One of my favorite features in Pivot is definitely the slicer you can add to your data set with a click. It helps filter the data significantly faster and is great for building dashboards. You can also add more than one slicer. If the data is somehow connected (for example, product categories and products), they will cancel each other out, meaning clicking on one will automatically filter the other:
If you want to learn how to show text in pivot tables, check one of my previous blogs.
UNPIVOT
Unpivoting data is as cool as pivoting them, but you will need help from the Power Query feature. It’s actually pretty easy to do this. The first step is to turn your data into a table so you can load them in the query:
Power Query will open in the new window, and you then have to pick the columns you want to unpivot to one column. Choose the unpivot option in the Transpose window, and load the results back to the sheet:
And your data will be fastly unpivoted in the new sheet:
Tables
Let’s start with making a table. You can do it by clicking on Insert option > Table or using a shortcut CTRL + T. If you want to know more about shortcuts, check out this blog. You can also choose a predefined design for your table, which is pretty cool. When working with tables, the Table Design window automatically pops up, and this is where you will find all the features.
You can also remove or add new data in your table at any point using Resize Table feature.
AUTOMATED FORMULA CALCULATION
One of the most common mistakes in Excel models is inconsistent formulas. When you use tables, that can't happen because formulas automatically spread across the whole column. Tables also expand automatically to include new data, which is pretty handy.
<