Compare Weekly Sales in Excel Using a Pivot Table

Do you need to create weekly sales reports that compare the same days of the week? This kind of analysis can be tricky to ensure that you are comparing the same day of the week against the same week in the previous year. Simply comparing dates may not be sufficient, as you could end up comparing a Sunday’s revenue numbers against a Friday’s, and depending on the industry you are in, the results could look drastically different. In this post, I’ll show you how you can make reliable comparisons which look at the same weeks and the same days of the week.

Preparing the data

Let’s start with a pretty simple data set which just has the date and the sales amount, as such:

A table in Excel showing daily sales data.

The data set contains sales for January and February of 2024 and 2023. To facilitate the comparisons, I’m going to add fields for the week # and the day of the week. For the week, I can use the WEEKNUM function, which just takes the date as a single argument. And for the day of the week, I’ll use the TEXT function, which can use the “dddd” format type to specify the day. Here’s how the data looks after I’ve added those fields:

A table in Excel showing daily sales data with week and day of the week information.

Loading the data into a Pivot Table

Now that my data is ready to analyze, I can create a pivot table. While any cell on the data set is selected, I’ll click on the Insert tab and select Pivot Table. Next, I’m going to set up the pivot table as follows:

  • Columns: Year
  • Rows: Week , Day
  • Values: Sales Amount

To get the year to show, I’ll select the Date field and put the Years (Date) value under columns. You could also create a formula in the previous step to calculate the year value based on the date. Here is what my pivot table looks like thus far:

Pivot table showing sales by week and day.

There are a few things I will do improve the appearance and usefulness of the pivot table, including:

  • Removing the grand total, since I’m comparing and not adding the values.
  • Changing the report layout to a tabular format so that the Week values will now create subtotals.
  • Change the value field settings for the Sales Amount so that it resembles a currency format.
Pivot table showing sales by week and day after applying formatting.

Now, I’m ready to do the analysis in the pivot table.

Comparing values in a Pivot Table

If I want to compare values from one year to the next, I need to pull in another field for the values section. I’m going to pull in the Sales Amount into the section again. While at first, this looks like I’m just duplicating the values, I’m going to change the appearance of the second field. If I click ok the Sum of Sales Amount2 field and select Value Field Settings, I can change how the values are shown. Instead of a sum, when selecting the Show Values As tab, I have the ability to select % Difference From:

Selecting the percent difference from in a pivot table.

I then select my base field. I need to select the Years (Date) field, since I’m comparing years. As for the base item, I’m going to select (previous). If you’re always going to be comparing against a certain year, you can select the specific year. But if you always want to be comparing against the previous year, choose previous.

Selecting the percent difference from a previous year in a pivot table.

I have also renamed the field to ‘Revenue Growth %’ to signify that the value in the field represents the growth (or decline) compared to the previous year. Here’s how my data looks with the new field:

Pivot table showing percent change from the previous year.

There are a few things I need to fix there. The first is that I have a #NULL! error in the first row. This is because in the previous year, there was no sales, presumably as this would have been a holiday. To fix this, I can go into the Pivot Table options and check off the option For error values show and just leave it blank.

Setting the for error values show in a pivot table to be blank.

That gets rid of the error. Another thing I need to do is get rid of the unnecessary revenue growth field for 2023. As there is no comparable, it will always be blank for the first year. The simple solution here is to just hide the column entirely. Now I’m left with a pivot table that shows my sales data by week, day, and year, and the year-over-year change in percent:

Pivot table showing year-over-year change in revenue by week and day.

One last thing you may want to do is add some conditional formatting, to help highlight the good and bad weeks and days. Using a directional icon set could help make the results stand out:

Creating conditional formatting rules with icon sets in Excel.

By using this formatting, any values where the growth rate is more than 5%, will have a green triangle. Anything less than 0 will be red, while anything in-between will show a yellow horizontal line.

Pivot table showing year-over-year change in revenue by week and day with conditional formatting applied to highlight good and bad days.

If you liked this post on How to Compare Weekly Sales in Excel Using a Pivot Table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.


How to Group Dates by Month in a Pivot Table

In this post, I’m going to show you how to group dates in a pivot table by month. By doing this, you can do analysis by month rather than individual day. And that will also make it easier to plot the data on a chart.

For this example, I’m going to use TSA passenger volumes as my data set and analyze them by month and year. Here is the data I’m going to use, which runs up until Aug. 6, 2023:

TSA passenger volume by day and year.

If I load this into a pivot table, my fields are as follows:

Pivot table fields for TSA passenger volume data.

I have the date field which shows the current year’s dates. And there is also a field for each year, which contains the passenger volumes. If I put the Date in the Rows section of the pivot table and then years into the values section, then my pivot table looks like this:

Pivot table summarizing TSA traffic volumes by year.

There are a few things that I need to fix for this analysis to work:

  • I need to change each year field so that it is taking an average instead of summing the values. If I leave it as is, summing the values may not be helpful as the months are not going to be identical eah year. Taking an average will help smooth the data.
  • The formatting should be changed so that the values are separated by commas. This will make it easier to visually see the data. The numbers are too big and can be difficult to interpret in their current format.
  • The Row labels are broken down by year. But I already have the year values going across. This is not necessary and I need to have only the month values.

Here’s how to address these items.

To change the year field so that it takes an average, right-click on the field and select the option to summarize as an average:

Changing how to summarize values by in a pivot table.

Repeat this for each field, so that everything says average. To fix the number formatting, right-click on each field and select Value Field Settings:

Changing the value field settings in a pivot table.

Change the formatting to Number and check off the option for the 1000 separator. Repeat these steps for the other fields as well.

Next, for the date grouping, right-click on any of the date values and select the Group button:

Grouping dates in a pivot table.

At the following dialog box, uncheck years and quarters and just leave Months:

Grouping dates by month only in a pivot table.

After making all those changes, my pivot table now looks like this:

A pivot table summarizing passenger volumes by year and month.

It’s now easier to compare the different months and years. And it’s also easier to put it on a chart. If I insert a line chart, it’s easy to spot the trends by a monthly and yearly basis:

A chart comparing passenger volumes by year and month.

This is a PivotChart, as it evident from the grey drop-down options. If you prefer to get rid of the filters, go to the PivotChart Analyze tab and uncheck the Field Buttons option. Now you’ll have a cleaner chart layout. In the below example, I have also moved the legend to the bottom:

Chart showing passenger volumes by month and year.

As you can see, by grouping your pivot table dates by month, it becomes easier to analyze data. And by not doing a daily analysis, it’s possible to look at the data from a year-to-date view to compare the monthly averages. This way, you are able to still see the story behind the data without having a crowded chart.

If you liked this post on How to Group Dates by Month in a Pivot Table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.


How to Add Calculated Fields to a Pivot Table

Pivot tables do a good job of summarizing your data and showing you the totals based on various splits and categories. In some cases, however, you may want to add calculated fields to your pivot table if it doesn’t go far enough in analyzing your data. Below, I’ll show you how you can do that.

Adding a calculated field

In the pivot table below, there is a break down by sales and cost by order status:

Pivot table summarizing sales and cost by order status.

Suppose you wanted to calculate the margin to show how much you are making per order. Since in this data set a margin field doesn’t exist, it needs to be added as a calculated field. To do that, click anywhere on your pivot table and then select the PivotTable Analyze tab on the ribbon. There, you’ll see an option for Fields, Items & Sets. Click on that, and you’ll see an option to add a Calculated Field:

Adding a calculated field in Excel.

Creating the formula

In the next step, you can name your field as well as set up the formula to determine what it should be calculating. You can use the fields in your pivot table and insert them into the formula. To do this, just double-click on any one of the fields. This is a better option than simply typing in the field because if you miss a space or enter it differently, the formula will not compute.

In the case of a margin calculation where we want to know how much of revenue is remaining after costs, the formula is just sales minus cost.

Creating a calculated field for the margin.

After clicking OK, your calculated field will now show up on the pivot table:

A pivot table after adding a calculated field.

Ideally, we would also have a field that shows margin as a percentage to help add context. To do this, I can add another calculated field. For this formula, all I need to do is take the recently created margin field and divide it by sales:

Creating a calculated field in a pivot table to determine the margin percentage.

You could potentially do this all within a single calculated field. But the point here is to illustrate that you can use a calculated field within another calculated field. In some cases, it can make it easier to break them out separately. And this also gives you more flexibility in how you want to present the data.

After converting the format to a percentage, now I see a margin % in the pivot table:

Pivot table showing multiple calculated fields.

If you liked this post on How to Add Calculated Fields to a Pivot Table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.


Making Dashboards in Excel With Map and Gauge Charts

It’s time for an updated dashboard post. My original post is now three years old and probably overdue for an update. This time around, I’m going to start from scratch using a real data set from the Bureau of Labor Statistics, where I’ll walk you through my process from start to finish. To follow along, you can download the data I’m going to use from here (I’m going to use the 2020 state data. This is the XLS link).

Preparing the data

If your data is no good, then it won’t matter how great your charts and visuals look. That’s why it’s important to have a look through the data to see how usable it is. And you may not notice any issues until you start populating your charts. But one of the things that are noticeable right of the bat in this data set is that instead of empty values on this sheet, there are # or * signs.

Data set showing # and * signs in place of empty or missing values.

That’s going to be a problem if you want to do any computations on this data. You can use Find and Replace to replace the data with empty values. Note that for the *, you’ll need to find ~* rather than just *, otherwise Excel will interpret the * as a wildcard and find everything.

One other thing that I am going to do is create another column for the occupation titles. In column J, there are more than a dozen titles for the ‘major groups’ (major is indicated in column K). I am going to create a table to group them even further. I’ve put this on a separate lookup sheet:

Now, what I am going to do is insert a column on my main data sheet, after column J, which will do a lookup on this table. The formula will be as follows:


Now, I have a category field in column K for the ‘major’ group classifications:

Data set with the new category field.

Next, I’m going to convert the data into a table. To do this, click on any of the cells in your data set, and on the Insert tab, click on Table:

Table button on the Insert tab.

Once done, you should notice some default table formatting gets applied to your data set:

Data set that has been converted to a table.

And to make it easy to reference, I’m going to click on the Table Design tab, and under the Table Name section on the left, I’m going to re-name the table to tblData:

Table name set to tblData.

To change the name of a table, all you need to do is click on it and make your changes, then press enter.

Creating the pivot tables

For this dashboard, I’m going to create pivot tables and use charts to show the following:

  • Median salary for the specified position.
  • Wages by percentile.
  • Median salary for the specified state based on job categories.
  • A pie chart showing how many jobs there are by category.
  • A gauge chart showing how the median salary compares to the national average.
  • A map chart showing the median wages by state.

Median salary for the specified position

To create this visual, I’m going to create a pivot table from the tblData and put it on a new ‘PT’ tab. For this, I am just going to take the average of the A_MEDIAN column. I will also filter the O_GROUP field so that it only includes the ‘detailed’ group to avoid including the categories. I will also adjust the formatting so that it uses the accounting format. The pivot table itself contains just one value:

Pivot table showing the median value all detailed line items.

I only want this value to show up in a box but what I’m going to do is create a column chart from this. For just the number to be visible, I’m going to add a data label and then remove everything else, including the legend, gridlines, and make the column a clear color. Lastly, I’ll copy my first visual onto a new ‘Dashboard’ tab and put the words ‘Median Salary’ directly above it:

Median salary showing through a column chart.

Wages by percentile

Next, I’m going to create a bar chart that shows the wages for a position by the various percentiles that are in the data set. For this, I’m going to grab all the different percentile fields, including the median:

  • A_PCT10
  • A_PCT25
  • A_PCT75
  • A_PCT90

I’ll need to set these calculations to be averages just like on the earlier calculation. I can re-name these to ’10th percentile’, ’25th percentile’, and so on, to make it easier to read. Then, I’m going to create a 3-D bar chart, change the colors, and add some labels so it looks like this:

A 3-D column chart showing percentiles.

Median annual wage for the specified state based on job categories

Now, I’m going to create a pivot table and chart to show what the median annual wage is across the different categories I specified earlier for the selected region. This is a simple pivot table set up, all that’s needed is the A_MEDIAN average in the values section of the pivot table, the CATEGORY in the rows, and the O_GROUP to filter just the ‘major’ jobs. This will result in the creation of the following column chart:

Column chart showing median annual wages by job category.

A pie chart showing how many jobs there are by category

One of the interesting metrics in the data set is the number of jobs there are per 1,000 jobs in the given region. This is going to be similar to the previous chart, except this time I am going to use the JOBS_1000 field. I’m going to use a pie chart for this visual just to change it up a little bit.

Pie chart showing jobs per 1000 jobs.

A gauge chart showing how the median salary compares to the national average

I’m going to use a gauge chart to compare the median salary against the national median and how it compares. For detailed steps on how to create a gauge chart, please check out this post. For this visual, I need to create one pivot table just for the national median wage. To do this, I just need to grab the median value and filter the O_GROUP by ‘total.’

For the actual gauge chart, I need to set up a table for the slices and the ranges. I will go with a setup as follows:

Table to set up a gauge chart.

The % of completion will take the median value and divide it by the national average. But to avoid it going over 100, I’ll use the MIN formula. And for the ‘high end’ value, I take 200 (think of 100 as the top half of the circle and the other 100 the bottom half) and subtract the % of completion and add the size of the slice. Here is what it looks like when the median salary is greater than the national median:

Gauge chart table with values filled in.

This is what the gauge chart looks like once it’s been set up following the steps in the previous post:

Gauge chart colored in green with the black marker.

A map chart showing the median wages by state

Creating a map chart is pretty easy in this situation because we have all the state names and all I need to do here is create a pivot table with the A_MEDIAN value. Here’s what my pivot table looks like:

Median annual wage by state.

However, you can’t create a pivot chart directly from a pivot table. But there is a way around that. I’m going to create another table that copies the values from the pivot table. They simply equal the values to the left:

Converting the pivot table into a regular table.

Now, I can create a map chart based on this table:

A map chart in Excel.

I now have all of my charts set up:

Multiple charts created in a dashboard.

What’s next is to set up the slicers.

Adding and linking the slicers

I’m going to add two slicers for the dashboard, one for the state and one for the job title.

To insert a slicer, all that’s necessary is to click on any one of the pivot tables and on the Insert tab, click on the Slicer button:

The Slicer button showing on the Filters section.

Then, select the fields you want to add. Generally, I add the fields that have the most selections and longest names going down vertically. In this case, that’s the OCC_TITLE field. For the State and Category slicers, I have those going across:

Multiple slicers showing on the pivot table.

I’ve also added a title just below the slicers to give the dashboard a name. The last piece of the puzzle here is to link the slicers to the pivot tables. Previously, I linked them to all of the tables. But for some of these charts, I don’t want them to link to everything.

For the State and Category slicers, I want them to update everything except the national median pivot table. And for the OCC_TITLE slicer, it should also not update the jobs per 1000 pivot table or the median wage by category. The reason being is that those charts will lose their value if only one job is selected, as the point is they should give an overview of the different categories. Similarly, you could also unlink the state slicer to the map chart.

To manage these connections, you can slicer and select Report Connections:

Selecting the report connections button for a slicer.

From there, you can select with pivot tables you want the slicer to link to:

And to keep your slicers from staying in put despite any changes, you can also right-click and select Size and Properties and then select the option to Don’t move or size with cells:

Properties section of the slicer settings.

Now, the dashboard is ready to go!

Completed dashboard in Excel.

If you liked this post on Making Dashboards in Excel With Map and Gauge Charts, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.


How to Unpivot Data in Excel

Using pivot tables to summarize data can be a great way to display information quickly and total everything up. However, in some cases, data that you download is already in what you might call a pivot table format where it is summarized and you want to put it in more of a tabular format. In this post, I’ll show you how to unpivot data in Excel where you can turn a table like this:

Data in a summarized, table format.

into this:

Data that has been unpivoted.

Unpivot using Power Query

Rather than copying and pasting data into a tabular format and doing the process manually, you can just use Power Query to do it for you, all in a matter of seconds. First thing’s first, you need to get your summarized data into Power Query. To do that, click on one of the cells in the table and on the Data tab, click on the From Sheet button in the Get & Transform Data section:

Selecting the From Sheet button on the Get & Transform Data section.

Then, click OK on the default range and then the next screen will be Power Query:

Table showing in Power Query.

The key to making the unpivot work correctly is to determine which column(s) you don’t want to unpivot. In this case, it is only the Year field as I want to have the years listed out. With the Year column selected, I right-click on the header and select Unpivot Other Columns:

Select Unpivot Other Columns from the menu.

After clicking on that, the data is unpivoted and now it is in tabular format:

All that is left now is to press the Close & Load button in Power Query, which will then populate the data back into Excel:

You can repeat these steps for other, similar summarizes should you need to unpivot data.

If you liked this post on How to Unpivot Data in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.


How to Make Dashboards in Google Sheets

A big advantage of using Google Sheets is that the data is readily accessible online and you don’t need to worry about if people are running different versions of it like you would with Excel. One of the areas where it may be lacking is in creating dashboards. Although you can incorporate slicers, they’re not as user-friendly or nice looking as what you would get in Excel. But in this post, I’ll go over how to make dashboards in Google Sheets quickly and easily.

Here is a sample of what my data set looks like. If you want to view the data plus the dashboard I created here, you can check out the Google Sheets file here.

Google Sheets data set.

Step one is to create some pivot tables. Like with Excel, I prefer to create a pivot table for each view that I want. I will set up four pivot tables, categorizing sales by:

  • Store
  • Salesperson
  • Product
  • Date

To keep things simple, you can put each one of those fields in the ROW section while the sales can be in the VALUES section:

Pivot table editor in Google Sheets.

When creating the pivot tables, be sure to un-check the option to Show totals (this is so that they don’t show up in the charts):

Show totals option in Google Sheets for pivot tables.

What you may want to do is create one pivot table and then copy and paste others, and just change the rows. One additional step you will need to do for the pivot table that contains the dates is to also group them by month. To do that, right-click on any of the dates and select Create Pivot Date Group:

Creating a pivot date group in Google Sheets.

Then, from the following menu, select Year-Month:

Different pivot date groups in Google Sheets.

This is how your pivot tables might look like once you are done:

Set of pivot tables in Google Sheets.

Where you put these pivot tables isn’t important. The key is leaving enough space between them so that they don’t potentially overlap should your data get bigger. Otherwise, you will run into errors and have difficulty updating your data. Since my pivot tables won’t get any wider based on the selections I’ll make, there doesn’t need to be any extra columns between any of them.

Now that the pivot tables are set up, the next step is to set up the different charts for each of them. For the sales by store, I will create a pie chart to show the split among the stores:

Pie chart showing sales by store in Google Sheets.

The one thing you will want to pay attention to for each chart is the range. Since your pivot table could expand, it’s a good idea to make the range bigger than it needs to be, even if it will contain blank values. For example, changing this:

Default date range for chart in Google Sheets.

To this:

Expanded data range for chart in Google Sheets.

This will ensure that additional data gets picked up by the chart should your pivot table get bigger. This is also why it is important to ensure you don’t place any other pivot tables below one another. Ideally, you’ll want to keep them side by side rather one on top of the other.

For the pivot table that shows sales by salesperson, I’ll use a bar chart since the names can be long:

Bar chart showing sales by salesperson.

For the product sales, I’ll mix it up and have those as column charts:

Column chart showing sales by product.

And for the sales by date, I will set those up as a line chart:

Line chart showing sales by month.

I will also add a scorecard chart, using any of the pivot tables. For this, I just want to pull the total sales:

Scorecard chart showing total sales.

Now that these charts all set up, it’s just a matter of organizing how you want to see them on your worksheet:

The one thing missing to make this dynamic: slicers. To add slicers to all these pivot tables, click on any of them and click on the Data tab and select the Add a Slicer button:

Adding a slicer to a Google Sheets pivot table.

Then, select the columns you want to filter by:

Selecting the column to use in a slicer.

As long as you are referencing the correct data range, then the slicer will apply to all the pivot tables correctly. And now, if I add a slicer for the stores and only select stores A and B, my dashboard updates as follows:

Dashboard filtered by slicers.

One thing to remember when you are applying changes: don’t forget to click on the green OK button on the bottom, otherwise your selections won’t be applied:

Applying filters for slicers in Google Sheets.

If you liked this post on How to Make Dashboards in Google Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.


Convert a Summary Table in Excel Into a Pivot Table

Often times, when you download a data table from somewhere it’s not in the format you need it to be. Tables are often in a summary format where you have months going down and years going across, or vice versa. It’s the end result of what you want a pivot table to look like, but you can’t easily turn that into a pivot table itself. Below, I’ll show you how to turn a summary table in Excel that looks like this:

Summary table.

Into this:

Data in tabular format in Excel.

This format is much more Excel-friendly and one that you can easily convert into a pivot table.

Converting the table

The data I’m using is the same one that I used in an earlier post that went over transposing data. Transposing data, unfortunately, isn’t enough to make data workable if you want to convert it into a pivot table. You’ll want data to be in a tabular format so that there’s a header for the month, year, and value.

You could manually transpose one year at a time and copy the data one by one. But of course, that isn’t optimal at all. The good news is I’ve got a macro that can help you flip that data in one click. It will go through the painstaking process of reorganizing the data for you.

Here’s the code for the macro. You can just put it into a module (I’ll leave a template to download below if you aren’t comfortable doing this step yourself):

Sub flipdata()

Dim cl, nxtcl As Range
Dim lastcol, lastrow, firstcol, firstrow As Integer

'get total number of rows and columns in range
lastcol = Selection.End(xlToRight).Column
lastrow = Selection.End(xlDown).Row

'get first column and row
firstcol = Selection.Column
firstrow = Selection.Row

'assign output starting point
Set nxtcl = Cells(lastrow + 2, firstcol)

nxtcl = "Header 1"
nxtcl.Offset(0, 1) = "Header 2"
nxtcl.Offset(0, 2) = "Value"

Set nxtcl = nxtcl.Offset(1, 0)

'cycle through data

For yr = (firstrow + 1) To lastrow

    For mth = (firstcol + 1) To lastcol

        nxtcl = Cells(firstrow, mth)
        nxtcl.Offset(0, 1) = Cells(yr, firstcol)
        nxtcl.Offset(0, 2) = Cells(yr, mth)
        Set nxtcl = nxtcl.Offset(1, 0)

    Next mth

Next yr

End Sub

It will output the data a couple of rows below where your data ends. It’s important to select the entire range of data before running the macro since it will go through the range that you’ve selected, nothing else. And if there’s data below your selection, it will overwrite that.

After you’ve selected the data, then you run the macro. In my template, I’ve got a button that you can press that will do the job for you and then you’ll get something that looks like this:

Data in tabular form.

Once in this format, you can easily create a pivot table:

Pivot table.

If you’d like to download the file that contains the macro, it’s available here.

If you liked this post on how to convert a summary table in Excel into a pivot table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

google sheets pivot table slicers

How to Make a Pivot Table in Google Sheets with Slicers

Google Sheets has come a long way in being a formidable alternative to Excel. While it may not have all of the same features as Excel, Google is adding to its functionality. Creating a pivot table with slicers is now a possibility in Google Sheets, and below, I’ll show you how you can do that with the online spreadsheeting program.

The basics: creating a pivot table in Google Sheets

To create a pivot table in Google Sheets involves about the same steps as it does in Excel: compiling and organizing your data set, and then creating the pivot table. Here’s a quick look of my sample data that I have ready to use:

spreadsheet data in google sheets

Then, on the Data menu, select the option to create Pivot Table:

creating a pivot table in google sheets

The next step is selecting where you to put your pivot table:

Menu to select where to create the new pivot table.

The default, a new worksheet, will often work the best. Although the layout looks a little different, the process remains the same with a blank pivot table being your starting point:

new pivot table created in google sheets

On the right-hand side of the page, you’ll see options to put fields into columns and rows, which is what you’re used to with Excel. Again, the main difference is the layout but the logic remains the same:

adding fields to a pivot table in google sheets

When clicking on the Add button, you’ll see options as to which fields to add, even having the option for a calculated field as well:

Adding a field to the values section of a pivot table on google sheets.

In my example, I’m going to select Total Sales so that I can summarize my data based on sales:

sales total pivot table

Next up, let’s add fields for both the row and column sections of the pivot table. If you have a lot of dates in your data set, you’ll want to put the field into the Row section. Otherwise, Google Sheets may give you an error where there are too many columns.

Even if you want to use dates in the column section, you’ll better of first putting it under Rows. Then, right-click on one of the dates and select Create pivot date group:

Google Sheets create pivot date group

From here, you can group your dates so that you don’t have too many entries. In my example, I’m going to use Month as my breakdown. After that, I can move the Date field back into the Column section:

Google sheets pivot table with column and row data

The problem here is that even if you have multiple years, it’ll group it into the same month. For example, I have one entry for Dec. 31, 2018, and it has not separated that out from the 2019 values. In order to fix this, I need to change the grouping from Month to Year-Month. Then my pivot table looks as follows:

Google Sheets pivot table with year month breakdown

Now the data from December 2018 is broken out. Next up, I’ll add another field for the Row section. Here, I’ll add the Store field. And now my pivot table is looking more like what I’d expect it to:

Google Sheets pivot table row and column data filled in.

Next, let’s also add the Salesperson field as well so that we have more of a breakdown:

Google Sheets pivot table with multiple columns and rows.

One of the things that stands out right away is that in Google Sheets the layout of the pivot table is much more intuitive. One of the annoyances of pivot tables in Excel is they’re not in a tabular format by default. With Google Sheets, it’s not something you need to worry about.

It still doesn’t have the repeating rows for the Store field, but that’s a quick fix: simply click the option to Repeat row labels:

Repeat row labels option in Google Sheets is easily accessible within the field settings.

And then, voila:

google shets pivot table with multiple columns and rows

Just like with a regular pivot table you can also drill down into the individual cells to the detail. In Google Sheets, it also gives you a specific name as to what cell you’ve drilled down on, making it easier to refer back to when looking at many different tabs:

Google Sheets new tab name for drill down pivot table results.

Adding slicers to the pivot table

You can also add slicers to your pivot table to make it easier to make changes to it and update it on-the-fly. To add a slicer, just click on the Data tab while you’re on the pivot table and click on Slicer:

adding a slicer to a pivot table in google sheets

Then that will generate the slicer, where you’ll be prompted to select a column to filter by:

Click on the filter icon and then on the right-hand side you’ll see the option to select a field from a drop-down list. In this example, I’m going to select Salesperson:

adding a slicer to a pivot table in google sheets

Then, on the slicer you can filter by the values in the column:

selecting the values to filter in a slicer in google sheets

If I hit the clear button and select only Rep A, Rep B, and Rep C, this is what my pivot table now looks like:

google sheets pivot table filtered by a slicer

The slicer shows the number of items selected and as you can see, it only has the sales reps that I selected in the data. You can add more slicers for other columns but the process remains the same. The big difference you can see from Excel is that your selections are how you’d make the selections in a normal filter; you don’t have buttons for each slicer option the way you do in Excel.

There are changes that you can make to the font and color of the slicer but other than that, visually, there aren’t many changes to make. So if you’re looking to replicate a similar Excel-type dashboard in Google Sheets with many options available for how slicers look then you may be disappointed here. However, in terms of functionality, the slicers work in much the same way that they do in Excel.

A good start, but Google Sheets is still lacking

Google Sheets still has a ways to go in being a real replacement for Excel. While it does have some unique functions that Excel doesn’t, adding pivot tables and slicers is a significant step forward.

However, one area that still needs more improvement is charting. For instance, creating a chart from the pivot table is not an easy task and doesn’t look like Google Sheets is designed yet to create easy-to-use pivot charts. And until that happens, there’s still going to be a big gap between the type of dashboard you can create with Google Sheets and what you can make in Excel.

The good news is that Google Sheets has made a lot of progress and it’ll likely be even better in the future.

If you liked this post on How to Make a Pivot Table in Google Sheets with Slicers, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

big data excel pivot tables getpivotdata

How to Use GetPivotData

For many users, the GETPIVOTDATA function in Excel is a nuisance and people are often looking for how to turn it off, rather than to actually use it. It can be a bit of a clunky formula, to say the least, but it can be very useful once you’ve learned how to use it effectively, which is what I’ll show you to do in this post.

Using GETPIVOTDATA to extract data using a single criterion

I’ll start with a simple pivot table that just shows stores and sales:

If I select the cell that has the sales for Store A, Excel populates the following formula:

=GETPIVOTDATA(“Total Sales”,$B$4,”Store”,”Store A”)

Let’s break down the different parts of this formula:

“Total Sales” is the first argument, and it is the name of the data field that I’m pulling my numbers from.

$B$4 is where my pivot table is located. However, this specific cell doesn’t matter, as long as the range is somewhere on your pivot table. For example, I could use B5 or B6; as long as the cell is located on the pivot table, the result will remain the same. The cell also doesn’t have to be frozen. However, if the range refers to a cell that isn’t on the pivot table, you’ll get a #REF error.

“Store” is the third argument, and it’s the field that relates where the “Store A” item is found, which is the last argument of the formula.

In essence, the GETPIVOTDATA starts with selecting the field you want to pull data from, the second argument pointing to somewhere on the pivot table, and with the third and fourth arguments relating to the relevant criteria. Think of it similarly to how you might use a SUMIFS function where you first specify what you want to sum, and where you can keep adding criteria to it.

If I only used the first two arguments of the GETPIVOTDATA function, it would return the total for the entire pivot table:

=GETPIVOTDATA(“Total Sales”,$B$4).

This would give me a value of 394,380 – which is the total of everything in the pivot table.

Using multiple criteria in the GETPIVOTDATA function

Let’s make this calculation a bit more complex and add both a product field and one for the sales rep as well:

If we want to pull the sales that Rep A had for Product A in Store A, that means we now have three criteria instead of just one. The good news is that all we have to build off the previous formula:

=GETPIVOTDATA(“Total Sales”,$B$4,”Store”,”Store A”)

To follow the pattern, what we’ll want to do is add the field name followed by the item in the field that’s our criteria. That means we need to add the “Product” field followed by “Product A” for the item:

=GETPIVOTDATA(“Total Sales”,$B$4,”Store”,”Store A”,”Product”,”Product A”)

Now, if we want to make it more complex and add Rep A as another filter, then it’s just a matter of adding the “Salesperson” field and “Rep A”:

=GETPIVOTDATA(“Total Sales”,$B$4,”Store”,”Store A”,”Product”,”Product A”, “Salesperson”,”Rep A”)

As you can see, you can add in as many criteria as you like. The key is just making sure that the field names you use match exactly what the fields are called on your pivot table. Otherwise, if the field cannot be found or if it is misspelled, you’ll get a #REF error.

Making the GETPIVOTDATA dynamic

Entering in all these fields is not optimal, and the real value in using GETPIVOTDATA is by being able to make the results dynamic and using variables to do the work for us.

To do this, I’ll set aside cells for both the field names as well as the criteria. Here’s how the new, dynamic formula would look now:

What you’ll notice is the GETPIVOTDATA now has nothing hardcoded that you’d have to change in the formula. Instead, you could change the fields in rows 4 and 5 instead. Let’s breakdown the formula in a bit more detail to see how it works:


The first argument, TEXT(I7,””), references the Total Sales field in cell I7. The reason I use the TEXT function here is that the value needs to be forced into a text format, and by using “” as the second argument in that function, it will keep everything the same.

The following argument, $B$4 still just has to point to somewhere on the pivot table. Unless you move your pivot table, you won’t need to change this argument.

The following arguments: I4,I5,J4,J5,K4,K5 all relate to the cells that have the field and criteria data. Change the values in those cells and the formula will automatically update, rather than having to fumble around and make changes in the actual formula.


There are some important limitations that you should be aware of when using GETPIVOTDATA.

Blank values will cause errors

If in the example above you want to use fewer than three criteria, you’ll have to modify the formula, otherwise, you’ll get a #REF error if you simply clear the variables and make them blank.

Data must be visible

Another important thing to remember: if your pivot field isn’t showing the data, the GETPIVOTDATA won’t be able to use it.

Since I’ve removed the Salesperson field from the pivot table, I can no longer use that as part of the criteria in my GETPIVOTDATA formula, and hence is why there is now a #REF error in my result. Even though it is still in the dataset, GETPIVOTDATA will only be able to extract from information that is visible on your pivot table.

Order is important

The order that the fields show on the pivot table will impact how you can use GETPIVOTDATA. For example, if I have Store, then Product and then Salesperson in my pivot table, I have to take that into account when creating the GETPIVOTDATA formula; I can’t just use GETPIVOTDATA to use criteria from the Store and Salesperson fields and skip over Product. I can, however, pull the totals for Store and Product and then just not include Salesperson, since I’m not following a different hierarchy than what’s shown in the pivot table.

Too many fields will make it difficult to summarize totals at lower levels

The GETPIVOTDATA function can be very particular, especially when it comes to hierarchy. For instance, you won’t be able to total sales by rep for everything in the pivot table if you’ve got the store and product fields shown higher up in the hierarchy; you’ll need to incorporate those fields into your calculation. That means adding all those combinations where the sales rep is found, which isn’t optimal. I can’t just say I want to see the total sales this rep made, forget all the product and store combinations that come above it in the hierarchy. The easiest way would be to simplify the pivot table to remove those fields or make the Salesperson field at the top of the hierarchy, and then it would be possible to do that by just using one criterion.

Why formulas may be a better option

GETPIVOTDATA has its strengths, but as you can see, it also has many weaknesses and limitations. This is where using formulas like SUMIFS can be a lot more useful if you’re comfortable using them. GETPIVOTDATA can be useful for pivot tables that aren’t going to move or change and it could also be a bit quicker than a lookup or other function. If you’ve got a lot of cells that you need to populate, SUMIFS may just end up slowing down your spreadsheet too much, and you may find it easier just having multiple pivot tables with different views instead.

At the end of the day, if you hate GETPIVOTDATA and prefer a simpler approach like just using lookup functions to get your data, this post will show you a simple way to get rid of it when selecting cells on a pivot table.

If you liked this post on How to Use GetPivotData, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.


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:


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.