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:
Master Excel Pivot Tables, Excel Slicers and Interactive Excel Dashboards - FULL COURSE!
How to build an Interactive Excel Dashboard un UNDER 15 Minutes!
Privacy & Cookies Policy