When I first started to learn how to use Excel Pivot Tables, I’m not going to lie, I was a little intimidated. At the time I really wasn’t certain about how and why I should be using them to analyze and summarize my data. Because I was a newbie I analyzed my data by literally manually selecting the cells that I wanted to summarize in combination with simple formulas like SUM and AVERAGE. At this point I was still unaware that the majority of the manual interaction with my data set could be eliminated with formulas like SUMIF and AVERAGEIF. I completely skipped over learning the SUMIF/SUMIFS and AVERAGEIF/AVERAGEIFS formulas and went right into learning pivot tables. I think it was because I was always intrigued and excited about learning an entirely new concept/application within Excel. Once you start to learn how to use pivot tables you’ll realize that all of the manual work is completely removed and you’ll notice your productivity sky rocket. I am going to show you 10 productivity hacks that can be applied to Pivot Tables in Excel 2010 / 2013 and definitely most can also be applied to earlier versions of Excel as well (one of the features may only be applied to Excel pivot tables 2013).
Download the Workbook For Free Before We Begin
The Excel file is indeed free. When you click the ‘GET IT FREE’ button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you’ll need is your email address!
Productivity Hack # 1: You Won’t Need to Manually Summarize Large Data Sets with Multiple Error Prone Formulas
When using formulas, you type out and select cell references from a given data set which requires several manual steps. When you use a pivot table, you select your entire data set and then let the application do the work.
Like anything else that you do, there are going to be occasions where you are going to make errors (I’m not saying always, but it does happen- admit it). In this regard, I can at least speak for myself. Don’t get me wrong Excel formulas are great but it’s easy to make a little mistake or typo that could potentially cost you or the company you are working for a lot of money. Check this excerpt out from an article on the Forbes.com (click the quote to read the full article).
All I’m saying is that there is some room for error when using formulas or copying and pasting formulas for that matter. If you were to use a pivot table to summarize your data, you would reduce the odds of an error as significant as this one is from occurring. The reason why is simple, with a pivot table, you select your tabular data set (make sure you have column headers and no blank columns or rows in the data set- pivot tables 101) and create the pivot table and really for the most part, the application does the rest of the work for you.
Resources
- More on setting up Excel pivot tables from excel-easy.com.
How to Setup the Pivot Table So You Don’t Need to Use Formulas to Summarize Your Data
◊ Select any cell within your tabular data set. Here I underlined tabular. This means that you shouldn’t have any breaks in your data set (i.e. spacing between rows or columns) and you should have column headings for each column in your data set.
◊ Now you can go over to the insert tab of the quick access ribbon and select insert, click on Pivot Table, and then you’ll see the Create PivotTable screen where your data range has already been selected and that the pivot table will be created on a new sheet in your workbook. Click OK.
◊ Select within the Pivot Table area and then drag and drop each of your fields within one of the areas of the Pivot Table. The most common are the row labels and the value area. The value area will be where you drag your numbers and the data that you will summarize, while the row label and column areas are where your descriptive fields will go. The filter section is exactly what it sounds like, you will add the fields that you would like to potentially filter. You can summarize your data in as many ways as you would like – have a look below.
Productivity Hack # 2: Use Excel Tables with Pivot Tables and You’ll Never Have to Update Your Source Data Reference
The source data reference is the data set and range that is referenced by the pivot table. As the source data changes and more information is added, if the data is not in a table, you need to update your source data range. If a table is used for your source data, the only thing you’ll need to do is use the table as a reference. As new records or columns are added to the source data table, referencing the table means that you do not need to update your source data to include the new data. The only thing you’ll need to do is update your pivot table cache (this is done through using the refresh option under the analyze section in the Excel ribbon).
Note: The pivot table cache is a snapshot of your source data. Until you update the cache through refreshing your data set via the pivot table, the data will remain unchanged.
How to Create a Table then Use it as your Source Data for Your Pivot Table
◊ Use CTRL+A to select your entire data set then type CTRL +T to create a table.
◊ You’ll have a window pop-up to create the table. You’ll want to select the “My table has headers” as long as your data set already has column headings (which you should do). Once you click OK, you’ll notice your data set will now have a table design tool available in the quick access ribbon. This table design in the ribbon will be available as long as you’ve selected a cell within your pivot table. From the design tool you can then create a pivot table using the “Summarize with Pivot Table” option. You’ll then get a pop-up window to create the pivot table and you’ll see that it is referencing your current table.
◊ Once you’ve clicked OK, and the new pivot table worksheet is created, you can add the fields into the appropriate sections of your pivot table. You can now add data to your table and then simply press refresh to update your pivot table data. Again, if you were not referencing a table, you would need to update the source data but because we referenced a table we are going to save ourselves some time.
Productivity Hack # 3: Quickly Access Detailed and Filtered Data from Your Pivot Table Using the Double Click Drill Down
This is a super useful feature when using pivot tables. When you are looking at your pivot table data, you can access the detailed information and records that makeup your summarized value within the value section of the pivot table. For example, if we want to see the sales records for the west for SalesPerson2, we simply need to double click the corresponding value in the pivot table. This acts as a huge time saver. Just think about the amount of time you would spend trying to filter and unfilter your data to get the same results. This way, you also have a neat little table of details that you can include in your slides should you require them.
How to Use the Double Click Drill Down Within Your Pivot Table
◊ This one is pretty simple. Determine which detailed data you would like to see and double click the corresponding value. For example, if we want to see all sales records for SalesPerson2 within the West Region, we will double click the value as shown below.
Productivity Hack # 4: Answer your Business Questions at Lightning Speed by Moving Your Fields Between Pivot Table Sections
To me this is where pivot tables rule all other functionality in Excel. Being able to grab massive data sets from a transactional table and analyze and develop conclusions at lightning speeds is priceless. Your boss can ask you a list of questions and you can answer all of those questions in hardly any time at all by simply moving your fields around the pivot table sections.
How to Answer Multiple Different Business Questions With a Single Pivot Table
- What was the highest selling month in dollar value for SalesPerson1 in 2014?
Here we found that the month of September 2014 (see highlighted value in yellow) was the highest selling month for SP1 with a total value sold of $1.3 M.
Make sure to review how and which sections I’ve moved my fields around to get the answer that I am looking for.
Note – I’ll show you in the next productivity hack how to group dates and numbers to get the same result as I did in my columns sections.
- In which region did SalesPerson2 sell the most quantity?
You’ll notice that we need to switch our value section from total value to quantity just based on the question that is being asked. You can handle this question in multiple ways using the pivot table but let’s quickly have a look at one of the ways to answers this.
NOTES: I used a filter on salesperson within the row label section and I used sorting on the values based on region from highest to lowest quantity.
- Which item had the highest number of orders (determines which product was sold the most often)?
Keep in mind here that we can use filters as well as sorting to narrow down our results to answer the question that is being asked. I am including all of the data and using mostly the sort option. Excel has the capability of filtering and showing the top/bottom X (where x is the number specified by the user) so that you don’t even need to sort your data and you can go direct to your answer. The reason I am including and leaving things open is because I want to show you how easy it is to answer your questions simply by moving some fields around the different sections of the pivot tables.
Here all we had to do was add item to the row label section and add item number again (to get a count of the number of lines/records that had item) to the value field. I then sorted based on the item number and the corresponding values. I will be showing you more in depth examples of sorting and filtering later on in this post.
That is all I am going to show you for examples, but try answering the following questions by moving fields around the different sections and let me know your results and how you got them in the comments.
- Which item sold the most quantity in July?
- Which product category sold the most quantity and dollar value?
Productivity Hack # 5: Group Dates, Numbers and Text in the Pivot Table to Quickly Summarize Your Data Set Without Manually Creating New Fields in Your Source Data
This is another one of my favorite features of Excel pivot tables. Grouping allows you to use standard take a standard date field and group them into seconds, minutes, hours, days, months quarters and years. You can also group numbers into custom categories for distributions or you can manually group text. Let’s have a look how grouping works within an Excel Pivot Table.
How To Group Dates into Years and Months using Pivot Table Feature
◊ Add your date field to either the row label or column section of the pivot table. From here, right click on one of the dates and select the group option. You can also type ALT+SHIFT+→ (alternatively, to ungroup you can type ALT+SHIFT+←).
◊ Once you select the group option you’ll be prompted with the following screen. Use the left click button of your mouse to select the date categorization that you would like displayed. Here we selected years and months. Now click OK.
◊ Once you’ve clicked OK, you’ll notice that your data set has now been broken down into Years and then months.
For additional grouping examples (i.e. number grouping and text grouping), check out this link from contextures.com.
Productivity Hack # 6: Use the Advanced Sorting and Filtering Pivot Table Features to Find What you are Looking for Without Scanning the Entire Data Set
Using the sorting and filtering features within the pivot table will drastically speed up the process of finding an answer to your business question. Because you are filtering or sorting data in the order that you require the data to be displayed, you’ll find the information that you are looking for much faster. Let’s go over using these features in Excel.
Pivot Table Filter Options – Values or Labels
Below you’ll see a list of label and value filters that can be used when filtering your data within the pivot table.
Label Filters
Value Filters
Please do make note that the Top 10 pivot table filter option allows you to also do bottom 10. Also, you can change from top/bottom 10 to any number/value that you would like.
How to Use the Pivot Table Filter Options
Often times when we are using a pivot table to summarize our information, we will still encounter a large enough summarized data set that we need to filter down even further. This is where the filter options come in handy. Let’s say we would like to know the top 3 items sold based on dollar value. We can do this using the top 10 filter option that is provided in Excel 2010/2013.
Notice how these are not sorted in order of highest value to least value sold for the top 3. I’ll show you how you can do this in the sorting section below.
More Resources on Pivot Table Filters
⇒ Other ways to use the top 10 filter in Pivot Tables.
⇒ Review of pivot table filter section, value filters and label filters.
Pivot Table Sorting Options
When it comes to sorting, you can sort your row labels/column labels or you can sort by the values within your pivot table. Notice that you setup your sort based on a particular field. In the case below it is region. In the example, our values will sort in ascending order based on the sum of the total value only if we drill down in our row label section to the region field (which is below the salesman in the hierarchy). Make note of this because if you were to add additional row labels under the region, lets say for example item, you’ll notice that if you expand your selection to show the details, your values will not be sorted. To sort your values by item, you’ll need to include a sort at that level as well.
How to Use the Pivot Table Sorting Options
There are times where we want to sort either our labels/fields or our values within the pivot table. Excel provides you with functionality to do both. In our filter example I showed you how you can use the top 10 filter to show you the items that had the highest value sold. What we are going to do now using the sort option is sort the top 3 values from highest to lowest.
Additional Resource on Excel Pivot Table Sorting
⇒ Pivot table sorting from the Contextures blog
Productivity Hack # 7: Use Conditional Formatting to Identify Key Performance Indicators that Require Attention
The pivot tables is by far the best tool for summarizing large sets of data. This also makes it perfect for measuring a companie’s performance using key performance indicators. Companies often set targets and goals that are based on a set of criteria. For example, lets say that the company establishes a target that each salesperson sells $3.5 M a year. Let’s have a look at how we can do this.
How to Use Conditional Formatting Icon Sets for Key Performance Indicators
◊ Setup your pivot table sections as required to show how much each salesperson has sold.
◊ Now that the pivot table is setup, we can now setup conditional formatting to identify the salesman who do not meet the $3.5 M criteria. In this dashboard, it’s very easy to tell who hasn’t reached the company target, however let’s go through the practice of setting up the formatting. You need to select the values (within the value section of the pivot table) that you would like to apply the conditional formatting to. Once selected, go to the home tab from the quick access ribbon and select the conditional formatting drop down, go to icons and then select the icons of your choice.
◊ Review and setup your conditional formatting criteria. This can be done by going to the manage rules under the conditional formatting drop down on the home tab on the quick access ribbon.
◊ The last thing that we want to do is make sure that the data within our pivot table changes as new salespersons are added to our raw data set and included in the new pivot table cache. To do this you’ll need to setup the pivot table so that the formatting is applied to all total sales values for the field salespersons.
Now that we have this setup, as more data is added to our table and pivot table, we can easily monitor the performance of each salesperson.
Additional Resources for Conditional Number Formatting Setup Within a Pivot Table
⇒ Conditional formatting in Excel PivotTables
⇒ Excel KPI Dashboard: How to Monitor Your Sales to Reach Your Targets
Productivity Hack # 8: Use the Show Value As Option of the Pivot Table to Automatically Calculate Changes in Values Between Dates.
The show value as option that is available to fields that are included in the value section of the pivot table can be used to calculate changes between dates or fields that are added within the column section of your pivot table. In this particular example, I’ve included an older blog post of mine that looks at how to calculate the change in inventory between dates. In order to give you an idea of how this is setup, I’ve included an image from that blog post. You can check out the entire article to learn how to set this up here. Try doing this with a standard set of data using formulas without the pivot table and you’ll realize just how much time this option is saving you.
Productivity Hack # 9: Use Interactive Data Slicers Or Timeline (2013 Only) to Filter Your Pivot Tables
The interactive data slicer that was originally added in Excel 2010 was an awesome addition to pivot tables (and tables). In Excel 2013, Microsoft also decided to add a timeline slicer into the mix which allows us to quickly navigate and filter through date fields that are included in our data model and pivot table. These data slicers added a nice touch to allow the dashboard consumer to visually and easily interact and filter the pivot table report.
How to Add a Data Slicer and Timeline To Your Pivot Table
◊ Select anywhere within the range of your pivot table and then go to then analyze table of your pivot table tools from the quick access ribbon. From here you can then go to the filter section and add either a slicer or a timeline. You insert a timeline the same way that a data slicer is inserted into the pivot table.
◊ Once you select insert slicer, the following window will come up asking which fields you would like to include in your slicer/filter. Note that you can add multiple fields here. Once you select the field that you want the slicer for (in our case the item number), you can then press OK. Note when using the timeline, only date fields will be displayed during the selection process.
◊ Voila, you’ll now notice that a slicer has been added to your dashboard and can be used to filter your data. You can use standard windows functionality to select/deselect multiple items (CTRL + Left Click or SHIFT + Left Click). The same applies to the timeline as seen below but with a few additional options (drag and release at each end of your current selection). You will notice that the timeline can be displayed by day, month, quarter or year (pretty slick).
Productivity Hack # 10: Summarize your Values Using Averages instead of Count and Sum to Calculate Things Like Average Order Size
Clearly summarizing your data using the sum and count formulas can be highly effective. Nevertheless, there are occasions where we need to see how we are performing on average and perhaps even more specifically for the sake of this example how many pieces are being ordered on average by item. This can easily be done using the summarize value field by average within the value field settings.
How to Summarize Your Pivot Table Values By Average Instead of Sum or Count
◊ Right click on the value field settings from within the pivot table value section or within the table field list section and select value field settings.
◊ Now select average for the summarize value field by. This will update your values to be summarized using the average.
◊ Below you’ll see that our values have been summarized by average. If you were to double click any of the values and calculate the average quantity between all of the lines, you’ll notice that this is exactly what has been done using the pivot table (the equivalent of using the averageif formula but much quicker).
Bonus Hack #11: Excel Pivot Table Shortcut Cheat Sheet From Pivot-Table.com
| Shortcut | Action |
| Ctrl + Shift + * | Select entire pivot table (not including Report Filters) |
| Ctrl + A | Select entire pivot table (not including Report Filters) |
| Spacebar | Add or remove checkmark for selected field in PivotTable Field List |
| Alt + Shift + Right Arrow | Group selected pivot table items |
| Alt + Shift + Left Arrow | Ungroup selected pivot table items |
| Down Arrow | Select next item in PivotTable Field List or Items List |
| Up Arrow | Select previous item in PivotTable Field List or Items List |
| End | Select last item in PivotTable Field List or Items List |
| Home | Select first item in PivotTable Field List or Items List |
| Alt + Down Arrow | Open field list for active cell |
| Ctrl + – | Hide selected item or field |
| Shift + Ctrl + = | When data field selected, opens Calculated Field dialog box |
| Shift + Ctrl + = | When field heading cell selected, opens Calculated Item dialog box |
| Alt + D, P | Open the old PivotTable Wizard |
Source: This list was created and released by pivot-table.com.
Conclusion
Well if you’ve made it this far I salute you. This was indeed a very long article but it does includes a lot of solid information about pivot tables and how they help you be productive. The best thing to do when getting acquainted with a new tool or software is to seriously just test it out and see what you can get it to do. The same goes for pivot tables. The more you mess around with them, the more you’ll realize there’s an abundance of good functionality that can help make your life easier as you’ve seen above.
I think one of my favorite productivity hacks on this list is using the grouping functionality. I just can’t get enough of it. What was yours?
Let me know in the comments. I hope you enjoyed this and if you did please help share and spread the word and knowledge!
And #12 – With Power Planner you can collaborate,comment and do Sales Forecasts, Budgeting and Planning on Power Pivot models. http://www.power-planner.com.