pivottables4a

4 Biggest Annoyances of Pivot Tables and How to Fix Them

Pivot Tables are great tools, especially for quickly summarizing data and allowing you to avoid entering in complex formulas to do so. However, they aren’t perfect and definitely have their quirks about them.

Here are four things that most users aren’t crazy about, and how to fix them.

The layout

When you create a pivot table it puts the table into a layout that is less than optional for spreadsheets. It’s not only hard to use for any other purpose, but it’s also not the easiest format to read. I always prefer a format that resembles more of a spreadsheet itself, in a grid format, without the indented rows.

To change the format, go to the Design section of the PivotTable Tools and select Report Layout and select Show in Tabular Form

This is what the pivot table looks like after the change:

This might still look a little cluttered, but what you can also do is get rid of any subtotals by simply right-clicking on the Total and unchecking the Subtotal button, and then it’s a bit cleaner:

The table still needs a little work for me, and that brings me to the second issue:

Labels do not repeat

You’ll notice that the value in the date field only shows up once. And so if I had many entries for a given date there would be a lot of blank values in the first column. Again, this is not ideal for a large data set and so this is where I’d like to make a change as well.

I go back to the Report Layout section, except this time I select the option to Repeat All Item Labels

Now my table looks like this:

This is what I would hope the pivot table looks like right off the bat. Unfortunately, Microsoft does not agree and opts for the indented version with subtotal at every section and no repeating values.

But that’s not all, there’s another little nuance you may notice when it comes to pivot tables:

Formatting won’t stay consistent

You’ll notice that in the sales data, I have a total of 2158 for January 4th. Anytime you’re dealing with numbers in the thousands, you’ll probably want some formatting that shows a comma in there just to make the numbers more readable. However, if you simply select the entire column and change the formatting, it will revert back to the default if you refresh the pivot table after adding more data.

What you need to do is actually change the field settings. To do this, right click on any of the numbers in that column and select Value Field Settings.

From there, select Number Format and then select the format you want it to show up as. Doing this will ensure the formatting won’t change when you refresh the data.

Tip: if you routinely make these changes like I do, you may want to check out the add-in I created which will make all these changes for you at the click of a button.

This brings me to the fourth most irritating item when it comes to pivot tables:

GETPIVOTDATA

By default, when you reference a number in a pivot table, it will auto-generate a formula called GETPIVOTDATA. In most cases, you probably won’t find this helpful, especially if you just want to reference some pivot table values elsewhere in your workbook. If you just want to reference cell E4 and copy the formula down, you’ll need to adjust the pivot table settings, as the GETPIVOTDATA will make it a bit painful to accomplish this.

Back under the PivotTable Tools section, select the Analyze tab and on the Options menu on the left-hand side, uncheck where it says Generate GetPivotData

Now you can reference values from a pivot table the way you would any other cell in your workbook.

If you have any other frustrations with pivot tables, please share them in the comments.

Creating a Dynamic Dashboard in Excel





Do you want to create a dashboard that will update all of your charts simultaneously based on what filters your users select? Follow the steps below and you can create a professional-looking dashboard without having to use any complex formulas or programming.

Preparing the Data


If you have data in Excel that you want to use to create a dashboard, there are a couple of things you’ll want to do first to make sure everything goes well.

1. Ensure your data is free of error cells, as this will result in errors.
2. Have proper headings setup so that you know what you are referencing in your dashboard. 
3. Setup a named range for your data, ideally a dynamic one. This will make it easier to link everything to your data quickly and easily.

Making sure your data is clean and ready to go is the most important step, but unfortunately the one that is easily overlooked. After all, if you’re data is no good, your outputs won’t be either. Garbage in, garbage out.

You can follow along with my sample data, which can be downloaded here.

Setting up the Pivot Table


First up, let’s look at creating a Pivot Table (see this post for an into into pivot tables). 

I’ve assigned a name of Dataset1 to my data, and this is what I will referencing when I create a pivot table. With a named range, I don’t have to worry about selecting the data before clicking the create pivot table button, I can do it from anywhere.


Once I’ve got my pivot table ready to go, the next thing to do is to select my fields. The fields that I have to choose from in my data set include: date, store, salesperson, and product. 

For my first pivot table I’ll want to look at the date because I want to start from a high level and work my way down. No sense in starting from the detail when I don’t have any context yet.

For the rows, I’ll select Dates, and in the values I’ll select Total Sales. My table now looks like this:


Ultimately, it doesn’t really matter if you want to select columns or rows for this as it’s going to be in a chart anyway. In Excel 2016, my dates were automatically grouped into months, which is what I wanted. If you want to change the grouping, simply right-click on the dates values and click Group


Then select the how you want the dates to be grouped



Next, I want to clean up my formatting so that my total sales have commas and so that the data is easier to read. To do this, I’ll right-click on that field and choose Value Field Settings



Then click on Number Format and then select Accounting.



Creating a Pivot Chart


Now it’s time to convert this into a chart. Select the Insert tab and in this example I’m going to select a regular Column Chart



What you have now is a simple column chart that shows your sales by month. The only thing special about a pivot chart is that you’re able to filter it based on your pivot table.

You’ll notice there are drop downs on your pivot chart that you select to modify your data. I can select only certain months to look at. 


The amount of options you have on here depends on how many fields you added to your pivot table. Whether you make the changes on your pivot chart or pivot table doesn’t matter, the chart will update all the same.

However, for the purposes of a dashboard, I’m going to get rid of these ugly filter buttons on my chart. To do this, click on your chart and click the button for the Field Buttons and this will remove the buttons. 



Now that the pivot chart is ready to go, you can now go about and format it how you like. 

Formatting and Tidying Up


Once you’re done formatting the chart, move it on to another tab. Because you’re creating a dashboard you probably won’t want your original pivot table to show up along with it. For this reason I usually move all the charts onto a separate tab.

Lastly, you’ll probably want to format your chart so that it is more appealing to your users.

Rinse and Repeat


For a dashboard, you’ll want to create multiple charts and so you’ll likely want to create another pivot chart following the same steps as above. In the next chart you can focus on a more detailed analysis, such as sales by store or rep.

In my example, I added three more charts in total and decided to mix it up by using a column chart, a pie chart, a stacked chart, and a bar chart. Mixing it up a little will keep your dashboard more interesting for your users.

Adding Slicers


Once you’ve added your charts, the next thing you’ll want to do is add slicers. Note that slicers are new to Excel 2010 and if you have an older version you will not be able to utilize these features.

For an overview on slicers, refer to this post.

The real advantage of using slicers is that they can be linked to multiple pivot tables and pivot charts. This allows you to now turn your dashboard into a dynamic one that will update as the user selects options from the slicers.

Once you’ve inserted slicers, you want to make sure that each slicer is connected to every pivot table. To do this, simply right-click on the slicer and select Report Connections.


On the next screen you can see all the pivot tables and charts that the slicer is connected to. Ensure that you have ticked off all the ones you want it connected to and then click on OK.


By doing this your slicer will now update all those charts and tables automatically. Repeat this step for every slicer you create.

Your dashboard is now ready to use and anyone that makes a selection on one of your slicers will see all the charts update immediately.


Using Slicers in Pivot Tables

I am going to use the same pivot table that I left off with in my last post and now I am going to add slicers to show you how they can make your life easier.

***Please note slicers are a new feature in Excel 2010 and you will not be able to use them on older versions***

To insert a slicer is very simple, while having selected a cell on your pivot table, go to the Insert tab and click on Slicer.

Then you will be prompted to select which field you want to use a slicer for:

I am going to select the first four fields: Date, Customer, Store, and Sales Rep. Once you’ve made your selections, click OK.  Now you have four different slicers that are on top of your pivot table:

This is of course not ideal, so the first thing you will want to do is move them. You can easily re-size the slicers as you see the current one I have selected for Sales Rep shows circles along the edges that I can use to re-size it.

First what I am going to do is insert a column into column A so that my pivot table pushes over into the next column. I will also insert a few rows above it to make room as well.

I am doing this so it is easier to move over a slicer to the left of the pivot table.

For slicers that have long filters (for example customer names), I prefer to put those above the pivot table, otherwise the names might get cut off. For shorter names, such as the three letter month abbreviations, those can go into the column to the left of the pivot table. Because they are short, they won’t need a big column to accommodate them. However, this is just my preference and you can put slicers wherever you think is most convenient.

I’ve re-arranged my slicers so the months are to the left of the pivot table and everything else is above it:

The slicer to the left of my pivot table looks fine, but the problem is the slicers at the top only show a couple items each, and would require me to scroll to find my selection. You may need to scroll, but there is a lot of empty space that can be used otherwise.

What I want to do is add another column in the slicer. If I select the Customer slicer, under the Options tab under the Slicer Tools section, on the right-hand side there is a field for columns. The default is set to 1, but if I change it to 2 my slicer now looks like this:

This is already a big improvement since now instead of seeing just two items I see four. I can add more columns but in this case without expanding my slicer horizontally it will truncate the names. You can stretch out the slicer as you see fit and adjust the columns accordingly. After adjusted my slicers all to be two columns, my pivot table and slicers now look like this;

If you are finding it hard to line up your slicers so they are even, select the slicer and under Slicer Tools and Options, click the Align button and check off Snap to Grid and Snap to Shape. Doing this will make it easier for your slicer to lock on to other slicers and make it easier to line them up. Do this for each slicer you are having issues lining up. Or you could select all your slicers (using ctrl) and apply the settings to all of them at once.

Now that the slicers are setup they are ready to use. Slicers effectively are filters in a pivot table, but the key difference is their ease of use for any user.

Whatever options I want to filter by I can just click on in the individual slicers. In the below example I am going to select all the sales to Customer ABC and Store 1 for Rep 01. My selections below reflect these selections and now my data table has filtered this data out:

If I want to remove any filters then all I can just click on the filter icon and the red x in the top right corner of the filter. This will reset the selections for that slicer.

Based on my selections I cannot choose any option besides Oct for the Date slicer. This is because based on the criteria I have selected in the other slicers there are no other months that are available. The unavailable selections are indicated by the faded light blue selections.

Pivot Table Basics – How to Create and Setup

Pivot tables in Excel allow you to easily summarize data and group information by category. The benefits of pivot tables is you can also double click on any number to see the individual items that make up the totals. The downside is pivot tables are not always very flexible to work with.

In this introductory post I will go over the following:

  • How to create a pivot table
  • How to setup the pivot table’s rows and columns
  • Grouping dates into months, years, and quarters
  • Filtering data in a pivot table
  • Changing the formatting of fields
  • Changing pivot table values to averages
  • Show values as a % of a column or row
  • Adding more fields and changing views to tabular
  • Viewing contents that make up a cell

If you want to follow along with my example you can download my sample file here

How to Create a Pivot Table


This is a simple step, the only requirement is your columns should have headers as you can see from my sample data table below. Select any cell on your data table and click on the Insert tab and click on the Pivot Table button.


That will pop up the next screen:

Excel automatically determined my table range because I had selected a cell on my data. If I hadn’t, then Excel would not be able to pull this information. As long as you make sure you click the insert pivot table button when you have your data selected then usually Excel will get the range correct. Of course if the information is incorrect you can change the range at this screen.

You can select whether you want the pivot table in a new worksheet or the existing one. In most cases you’ll want a new worksheet. In this screen I usually just click OK as the defaults usually work fine.

Setting Up the Pivot Table


When the pivot table is generated you will see the following:

This pivot table is blank and not terribly useful right now. On the right-hand side you will see this:

This is where you will select where you want your data to show. Suppose I want to see a summary of total sales by customer and by month.

First I will find the Total Dollars field from the above list and drag it into the Values section of the pivot table.

Next, I will move the Customer field into the Rows section. Whether I put it into rows or columns will not matter, but normally you want the field with the most amount of items to go into the rows section, because if you have a lot of data in the columns section you will have to do a lot of scrolling to see all of the table. In this example I don’t have that many customers so it does not matter.

Lastly, I will move the Date field into the Columns section.

Your pivot table should look something like this:

If the dates don’t quite look like this then that is fine. In this version of Excel it automatically grouped my dates.

Grouping Dates by Month, Year, Quarter, etc…

If you want to group your dates or want to change the grouping, select one of the dates on your pivot table and under the PivotTable Tools  section, under the Options (or Analyze, depending on which version of Excel you have) tab, click on Group Field button.

After clicking that button you will see the following options:

Excel has automatically divided my data into months, quarters, and year. I am going to un-select quarters since I only want to see months and years. Once I make that change I click on OK and it will update my pivot table:

I know have a summary that shows total dollars split by customers and by month. But I’ve decided I do not want to see 2010. I can filter the data even though I’ve included it in my pivot table.

Filtering Data in a Pivot Table


To filter the data, I will click on the Column Labels button since I put the dates into columns. I will see a drop down option to select the field I want to filter (assuming I have more than one field).

I select the Years field and then in the list of checkboxes below I can uncheck the ones for 2010 and 
< 1/1/2010:


After updating my selections I click on OK. Now anything prior to 2011 has been removed from my pivot table. 

Changing the Formatting of Fields


My pivot table looks okay so far except that I don’t like the number formatting that is used. You could change the formatting by selecting the columns and making your changes but the problem with this is it is a temporary solution. If you refresh your pivot table at any point the formatting will go back to what it was. In order to change the formatting for a field, simply right-click on one of the data points and click on Value Field Settings

Which will bring up this pop up:
From here I click on the Number Format button. That gets me back to the Format Cells pop up:
Here I’m going to change my format to Accounting so that it has commas and dollar signs. However, you can change to whatever format you prefer. I now hit OK and get back to my pivot table which now looks like this:

Changing Pivot Table Values to Look at Averages


Currently I see the total dollar value of my sales, but what if I wanted to see the average instead? Then I can right-click on any of the dollar amounts and again go back and select Value Field Settings.

Here I can change from Sum to Average. If I click on OK now my table will show averages now:

You will notice at the top now instead of Sum of Total Dollars it now says Average of Total Dollars. This is a quick way to check what data you are in fact looking at.

Show Values as a % of a Column or Row

I’ll go through another scenario now, assuming that I don’t care about averages but instead want to see the sales as a percentage of the total month. Again I go back to Value Field Settings and change the calculation back to Sum and then click over on the tab on the right called Show Values As

In the drop down selection I select % of Column Total. The reason I select this is because I want a % of the total for the month. Since my dates (and as a result, months) are in the columns, I select columns here. After clicking OK I now see the following pivot table:

If I wanted to see what % of the customer’s sales occurred per month then I could use the % of Row Total rather than column. I am going to revert back to showing as values for the next example. To do this you can just go back to Value Field Settings and under the Show Values As tab, select No Calculation.

Adding More Fields and Changing Views to Tabular


I am going to now add another field to the rows, specifically, I am going to drag the Salesperson field over to the row columns. If you want the pivot table to first be sorted by Customer and then Sales Rep, the Sales Rep field should be dragged under the Customer field. If you want the pivot table to first sort by Sales Rep and then by Customer, make sure the Sales Rep field is dragged above the Customer field.

In my example I am going to put the Sales Rep field underneath the Customer field since I think that is a more logical hierarchy in this example. I’ve added another field, but now I get to a common pivot table problem, my table looks like this:

If I want to use the pivot table in a formula or copy it as values somewhere, then this format is not very helpful since it doesn’t have all the information on one line. I want the customer to show on every line. To do this, I go under the PivotTable Tools section again and under Options/Analyze I click on Report Layout and select Show in Tabular Form

My pivot table now looks like this:

I’ve now gotten my column back for the Customer and Sales Rep fields, but the Customer field is still showing blanks for many of the items.

Again I will go back to the Report Layout options and this time select Repeat All Item Labels

Now my pivot table looks like this:

This is an easier format to follow since it is has all the relevant data on one line and it is easier to read. If I don’t want to see all the customers and the sales rep detail, I can collapse the field by pressing on the – button next to the customer’s name at the top. This will change it to a + and collapse the field. If you want to do this for all customers, right click on any customer and select Expand/Collapse and select Collapse Entire Field.

This will now give me a tidier pivot table:

Viewing Contents That Make Up a Cell


Lastly, I will go over how to see the contents of a cell. As mentioned earlier, one of the benefits of a pivot table are that you can simply double click on a cell to see what makes up its contents.

For instance, if I clicked on the cell in the first row for $30,625, it will open a new sheet with the following in it:

What this tells me is that cell was made up only one of this one entry. If there were multiple entries for that customer, rep, month, and year, then you would see a list of all of those items. In this case it was only made up of one transaction.

Pivot Tables: Avoid Changing Data Sources with Named Ranges

When working with a pivot table, you determine a data source to use for that table. But what if later you add rows or columns? You’ll normally have to change the data source to include the updated range, otherwise your pivot table isn’t including your changes. Unless you use a named range.

First, select the Name Manager under the Formulas tab and click on New

However, instead of selecting all the data, I’ll use the offset formula.

The OFFSET function allows you to specify the size of your data set. The formula below will keep the range equal to all the nonblank rows and cells starting from cell A1:

=OFFSET(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A),counta(Sheet1!$1:$1))
This is the first cell of your data source

This is the first column of your data source

This is the first row of your data source

Once you’ve created a new name for your data set, put the formula above in the Refers to field and press OK:



Now you’ve created your custom, auto-updating range. All you need to do now is when creating a pivot table (or changing its data source), put in the named range.

Note this will not work properly if you have gaps in your columns or rows. The COUNTA function counts how many non-blank cells are within the range. So you could manually override the formulas if need be, but that would defeat the point of this post.

But if you follow the above steps and use the formula above, your pivot table will automatically be updated with any new rows or columns you add to it. All you’ll have to do is refresh the pivot table. And because the offset formula forces the range to change, that will automatically happen in the pivot table as well.