All You Need to Know About
Excel Pivot Tables Tutorials
Learn how to use Excel Pivot Tables to analyze lots of data and create awesome reports with drag & drop ease.
Pivot Tables in Excel are one of the most powerful features within Microsoft Excel. A Pivot Table allows you to analyze more than 1 million rows of data with just a few mouse clicks, show the results in an easy to read table, “pivot”/change the report layout with the ease of dragging fields around, highlight key information to management and include Charts & Slicers for your monthly presentations.
Pivot Tables are used by Project Managers, Finance Analysts, Auditors, Cost Controllers, Sales Analysts, Financial Controllers, Information Technology, Human Resources, Doctors and Statisticians just to name a few. You would be surprised on how widely used Excel Pivot Tables are!
Let us go over a couple of Pivot Table basics to get you started.
Insert a Pivot Table
You need to have a dataset ready so that we will create a Pivot Table based on it. Prepare to be amazed!
STEP 1: Click inside your dataset. We have a table of sales data.
STEP 2: Go to Insert > Pivot Table
This will insert your very first Pivot Table!
STEP 3: Place the Pivot Table in a New or Existing Worksheet.
This will let you determine the location of where you want to put your pivot table in.
STEP 4: Drag and Drop the fields. Our setup in our example is as follows:
Column – YEAR
Row – Region
Values – Sum of SALES
What we are saying here is create a report wherein the rows are the regions, columns are the years, then the values inside the pivot table will be the sum of sales (based on the grouping of year and region)
You now have your Pivot Table! And the best part is no manual calculations were made. Excel just works it magic, determines what are the row values, column values based on your data, then works to group and sum the values together!
This is very exciting if you have a big set of data and want to make sense of it. Whether it be identifying trends, comparing performances, finding outliers, you name it!
Refreshing Pivot Tables
When the information in your data set gets updated you need to Refresh your Pivot Table to see those changes in your Pivot Table. Sometime you might be wondering on why your pivot table values are not reflecting the updated data set that you have.
There are three ways to do this and they are very easy to do!
First click on your Pivot Table and:
1. From the Ribbon choose: PivotTable Tools > Options > Refresh
2.Press ALT+F5
3.Right click in your Pivot Table and choose Refresh (see how this is done below)
So assume we already have our Excel Pivot Table setup.
STEP 1: Change the information in your data set. Let us change one value to a very big number so that the change is very much pronounced when we refresh our Excel Pivot Table.
STEP 2: Click in the Pivot table. You can select any cell inside.
STEP 3: Right click and select Refresh.
The Pivot Table values are now updated with just one click! You could see the 2014 values have changed and reflected our updated data set.
Group Dates With a Pivot Table
One of the things you can do with the Pivot Table is to group data together. And grouping dates is one of the most commonly used functions.
Grouping Dates is very easy with a Pivot Table. All you have to do is Right Click on your Date values (which are either in the Row or Column Labels of your Pivot Table), then choose the Group option. And that’s it!
From the dialogue box you can choose to Group by Days, Months, Quarters or Years. Let us run through a quick example.
STEP 1: Right click on your Date values and choose Group.
STEP 2: Select the combination of grouping you prefer. Click OK.
In our example we want to Group by Quarters and Years.
Your pivot table is now grouped by dates! Again Excel has done the hard work for you!
Inserting Slicers in the Pivot Table
Now let’s have a quick sneak peek of how to use slicers with the Pivot Table. It adds another layer of interactivity that you can do with Pivot Tables. Who said this should be a static report?
Slicers in Excel are visual filters or interactive buttons that allow you to see what items have been chosen within a Pivot Table.
They are a must for anyone wanting to wow their boss by adding interactivity in their reports! I show you how to insert a Pivot Table Slicer below:
STEP 1: Select your Pivot Table by clicking anywhere inside it. Select any cell and you should be good to go.
STEP 2: Go to Options(Excel 2010)/Analyze(Excel 2013&2016) > Insert Slicer
Select the Month and Year Fields.
Click OK.
Prepare to be amazed!
Your slicer is now ready! Go crazy clicking, slicing and dicing your data!
Just with this, you can filter your data depending on the selections you have made. It is easier now to zero in on specific combinations that you need or what your boss will need!
TIP: Hold down the CTRL key to select multiple items in your Slicer.
Read on for the top things you can learn about Excel Pivot Tables!
We also have materials to provide free excel pivot table training to you! You can check our list of youtube videos below wherein we show you the whole world with our excel pivot table tutorials:
Top 10 Excel Pivot Table Tutorials
- 50 Things You Can Do With Excel Pivot Tables– I have compiled an interactive tutorial on the 50 different things you can do with an Excel Pivot Table.Read more
- Use An External Data Source To Import Data Into An Excel Pivot Table– When creating an Excel Pivot Table, what happens if your data source is in another location? You can simply use the External Data Sources feature in your Pivot Table and Excel will magically import the data for you!Read more
- Connect Slicers to Multiple Excel Pivot Tables– What about if you had multiple Pivot Tables from the same data set and wanted to connect a Slicer to all of the Pivot Tables, so when you press a button all the Pivot Tables change?Well this is possible with the Report Connections (Excel 2013, 2016, 2019 & Office 365) / PivotTable Connections (Excel 2010) option within the Slicer.Read more
- Group By Month With Excel Pivot Tables– Before I was a Pivot Table guru, I had to get individual rows of daily sales and group them into a report showing the monthly sales during the year. Thankfully there is the Pivot Table way (I wish I had known this back then), which is quick and reduces the risks of making any errors.Read more
- Customize an Excel Slicer– Excel Slicers are very boring looking and there are only 14 different Slicer Styles to choose from, ranging from Light to Dark and with a few colors to choose from. Read on if you want to create your own Slicer using your creative mind with super cool colors and fonts!Read more
- A PivotTable report cannot overlap another PivotTable report – Ever had the scenario wherein you updated your Pivot Table data source, then tried refreshing your Pivot Table, and this error shows up: “A PivotTable report cannot overlap another PivotTable report.” Keep on reading!Read more
- Show Averages With an Excel Pivot Table– A Pivot Table is the most powerful feature within Excel as it allows you to analyze your data in many different ways, all with a press of a button. The Summarize Values By option allows you to choose a type of calculation (Sum, Count, Average, Max, Min, Count Numbers Product, StdDev, StdDevp, Var, Varp) to summarize data from the selected field.Read more
- The Ultimate Guide to Excel Pivot Table Slicers– Pivot Table Slicers are a visual filter in the form of an interactive button. There are several cool things that you can do with Pivot Table Slicers, like customize them, filter them, connect them to multiple Pivot Tables plus much more!Read more
- Lock the Excel Pivot Table but not the Slicer– Sometimes when you are sharing an Excel Pivot Table with your colleagues you do not want the other user(s) to mess with your Pivot Table layout and format. What you can do is lock the Pivot Table and only allow the user(s) to select the Slicers, making your report interactive and secure from Excel novices like your boss.Read more
- Top 3 Excel Pivot Table Issues Resolved– I have shown my Free Pivot Table webinar to over 40,000 people over the last couple of years and I continually get the same questions from my webinar attendees regarding the little issues they have when using a Pivot Table. Read on to see the top 3 Excel Pivot Table issues resolved.Read more
Latest Tutorials
