Posts in category: pivot table

Pivot tables are one of the most powerful tools in Excel and Google Sheets, allowing users to quickly analyze and summarize large datasets. This article will provide a comprehensive guide to pivot tables, including advanced features and common troubleshooting tips.

What is a Pivot Table?

A pivot table is a data summarization tool that is used in the context of data processing. Pivot tables can automatically sort, count, and total data stored in one table or spreadsheet and display the summarized data. This makes them invaluable for data analysis tasks, especially when dealing with large datasets.

How to Create a Pivot Table in Excel

Creating a pivot table in Excel is straightforward:

1. Select the range of data you want to analyze.
2. Go to the Insert tab and click on PivotTable.
3. Choose where you want the pivot table to be placed.
4. Drag and drop fields into the Rows, Columns, Values, and Filters areas to organize your data.

TIP: You can use ALT+N+V+T as a shortcut to create a pivot table in Excel instead of going through the Insert tab.

For a detailed step-by-step guide, check out our article on how to create a pivot table in Excel.

In some cases, you may have a data set which shows as a summary and with important fields going across horizontally rather than vertically. That can be challenging, but you can use Power Query to help you flip your data into a tabular format, which can be more useful for data analysis.

TIP: Do you keep changing the pivot table layout because you don’t like the default Excel setup? You can change the pivot table defaults!

Combining multiple sheets into one pivot table

If you are have multiple worksheets, then you don’t have to create one pivot table for each of them. Instead, you can combine them together with the help of Power Query. By doing so, this can drastically make your data analysis more efficient by having multiple sheets linked into just one pivot table, where you can easily slice and dice data. And with Power Query, it’s easy to trigger a refresh.

Working with Dates in a Pivot Table

Dates are a common type of data that often require special handling in pivot tables. Analyzing date-related data can provide valuable insights into trends, seasonality, and performance over time. One of the most powerful features of pivot tables is the ability to group dates into various intervals such as months, quarters, and years. This can make your data analysis more effective, especially when dealing with long periods.

Grouping Dates in a Pivot Table

Grouping dates allows you to summarize data on a periodic basis. This is particularly useful for identifying monthly trends and patterns, such as sales performance or seasonal variations. To group dates in a pivot table, follow these steps:

1. Right-click on any date in the pivot table.
2. Select Group from the context menu.
3. In the Grouping dialog box, select how you want to group your dates.
4. Click OK.

This is a great way of grouping your existing data. But you can also add to your data by creating calculated fields, which can take your pivot table to the next level.

Creating Calculated Fields in a Pivot Table

Calculated fields in pivot tables allow you to perform custom calculations on the data within your pivot table. This feature is invaluable for creating new metrics, combining existing data in meaningful ways, and enhancing your data analysis capabilities without altering the original dataset.

A calculated field is a new field that you add to your pivot table, which derives its value from performing calculations on other fields in the pivot table. For example, you can create a calculated field to calculate profit by subtracting costs from revenue or to determine the percentage change between two periods.

How to Add a Calculated Field

Here are the steps to create a calculated field:

1. Go to the PivotTable Analyze tab.
2. Click on Fields, Items & Sets in the Calculations group.
3. Select Calculated Field from the dropdown menu.
4. Define the Calculated Field:

• In the Insert Calculated Field dialog box, enter a name for your new field in the Name box.
• In the Formula box, enter the formula you want to use. You can use standard arithmetic operations (e.g., +, -, *, /) and reference other fields by their names.
• For example, to calculate profit, you might enter a formula like =Revenue - Costs.

5. Click Add and then OK to insert the calculated field into your pivot table.

Benefits of Using Calculated Fields

• Custom Metrics: Create specific metrics tailored to your analysis needs, such as profit margins, growth rates, or weighted averages.
• Dynamic Analysis: Calculated fields update automatically as you change the layout or filter data within your pivot table.
• Enhanced Insights: Combine data from different fields in new ways to uncover deeper insights and trends.

Tips for Using Calculated Fields

• Use Descriptive Names: Give your calculated fields clear and descriptive names to make them easily identifiable in your pivot table.
• Test Your Formulas: Ensure that your formulas are correct and yield the expected results by testing them with sample data.
• Avoid Overcomplicating: Keep your calculated fields as simple as possible. Complex calculations can be harder to manage and troubleshoot.

By mastering calculated fields, you can significantly enhance the analytical power of your pivot tables, allowing for more sophisticated and insightful data analysis. You can even add IF statements to a pivot table with the help of calculated fields.

After leveraging calculated fields to generate custom metrics and enhance your data analysis, the next step is to efficiently filter and explore your pivot table data. This is where slicers come into play, offering an intuitive and interactive way to refine your data views and focus on specific subsets of information.

Setting Up Slicers in a Pivot Table

Slicers provide a user-friendly way to filter data in pivot tables, making it easier to view and analyze specific subsets of your data. They are particularly useful for interactive dashboards and reports, allowing users to quickly change the data displayed without modifying the underlying pivot table.

What is a Slicer?

A slicer is a visual filter in the form of a button that allows you to filter pivot table data quickly. Slicers make it easy to filter data by simply clicking on the values you want to include or exclude, providing a more intuitive and interactive way to work with your pivot tables. This is similar to how you would filter your data on a table by using drop-down options; slicers simply make the process easier.

How to Add a Slicer to a Pivot Table

Adding a slicer to your pivot table can be done with just a few clicks. Simply activate your pivot table, then take the following steps:

1. In the PivotTable Analyze tab, click on Insert Slicer in the Filter group.
2. The Insert Slicers dialog box will appear, listing all the fields available in your pivot table.
3. Select the fields you want to use as slicers. You can choose multiple fields if needed (e.g., Product, Store, Salesperson).
4. Click OK to add the slicers to your worksheet. Each selected field will have its own slicer.

Using Slicers to Filter Data

Once slicers are added to your worksheet, you can use them to filter your pivot table data:

Filter Data: Click on the buttons within the slicer to filter the data. Each button represents a unique value from the field you selected.

Multi-Select: To select multiple values, hold down the Ctrl key (or Cmd key on Mac) while clicking on the slicer buttons.

Clear Filters: To clear all filters applied by a slicer, click the Clear Filter button (a small filter icon with an X) in the top right corner of the slicer.

Benefits of Using Slicers

• User-Friendly: Slicers provide a simple, visual way to filter data, making it easy for anyone to use, even those unfamiliar with pivot tables.
• Interactive Reports: Slicers are perfect for interactive dashboards and reports, allowing users to dynamically filter data and gain insights quickly.
• Multiple Field Filtering: You can use multiple slicers simultaneously to filter data by different fields, providing a more granular view of your data.
• Consistent Filtering: Slicers ensure consistent filtering across multiple pivot tables that share the same data source, keeping your reports synchronized.

Slicers are not limited to Excel; you can also use slicers in Google Sheets.

TIP: You can adjust the size and shape of your slicers. You can also spread the selections across multiple columns. Under the Slicer tab, just change the number of columns you want for that selection.

Now that you’re familiar with slicers, the next step is to integrate these elements into a comprehensive and visually engaging dashboard. Dashboards combine multiple pivot tables, charts, and other data visualizations into a single, cohesive view, providing a powerful tool for data analysis and reporting.

Creating Dashboards with Pivot Tables

Dashboards are powerful tools that can help visualize a company’s performance, various economic data, travel statistics, and any other reports you want to analyze. They provide an interactive interface for users to explore and analyze data, making it easier to gain insights and make informed decisions.

What is a Dashboard?

A dashboard is a visual representation of key metrics and data points, typically displayed in a single view. Dashboards combine various elements such as pivot tables, charts, and interactive filters to provide a comprehensive overview of your data. They are particularly useful for monitoring performance, identifying trends, and facilitating data-driven decision-making.

Creating Pivot Charts

For any metric you want to create a chart or visualization for, you’ll want to consider creating a pivot table for it. From there, you can use pivot charts to do the rest.

To insert a pivot chart:

• Click anywhere in your pivot table.
• Insert a chart the way you normally would.
• Select the type of chart that best represents your data (e.g., bar, line, pie chart) and click OK.

Format your pivot charts to enhance readability. Add titles, labels, and legends as needed. Use colors and styles that make the charts visually appealing and easy to interpret.

Combining Elements into a Dashboard

To create a cohesive and interactive dynamic dashboard, combine your pivot tables, charts, and slicers into a single worksheet. Some things to consider when doing so:

Layout Design:

• Arrange the pivot tables and charts in a logical and visually appealing layout. Group related elements together.
• Leave space for slicers and ensure they are positioned in a way that is easy for users to interact with.

Add Visual Enhancements:

• Use shapes, colors, and borders to highlight key areas and separate different sections of your dashboard.
• Add headers and text boxes to provide context and explanations for the data presented.
• Arrange the slicers on your dashboard so they are easily accessible. Slicers should be placed near the relevant pivot tables and charts to facilitate easy filtering.

TIP: Connect Slicers to multiple pivot tables. To do this, right-click on the slicer and select Report Connections. Check the boxes for all the pivot tables you want to filter with the slicer.

Benefits of Using Dashboards

• Real-Time Insights: Dashboards update automatically with changes to your underlying data, providing real-time insights.
• User-Friendly Interface: Slicers and interactive charts make it easy for users to explore and filter data without advanced technical skills.
• Comprehensive View: By combining multiple data points and visualizations, dashboards offer a holistic view of performance, trends, and key metrics.
• Improved Decision-Making: Dashboards facilitate data-driven decision-making by presenting clear and actionable insights.

By following these steps, you can create powerful and interactive dashboards that leverage the full capabilities of pivot tables, charts, and slicers. This enables you to present complex data in an accessible and visually engaging format, driving better understanding and more informed decisions. You can create a dashboard in Google Sheets using similar approaches.

While pivot tables offer powerful data analysis capabilities and can significantly enhance your ability to work with large datasets, they are not without their challenges.

Biggest challenges with pivot tables

Some of the most common issues that users often encounter with pivot tables are the following:

• Pivot tables by default aren’t formatted in a convenient way; users often end up adjusting the layout so that it is in a tabular setup.
• Labels do not repeat, and that can make it difficult to read the table to determine what each line relates to. Here too, users need to change the default layout.
• The formatting for fields can change when the data is refreshed if users haven’t adjusted the actual field settings themselves.
• When referencing a pivot table in a formula, the GETPIVOTDATA function can be triggered if the option isn’t disabled.
• The data source is updated and when it does, the pivot table doesn’t include the newly added information without manually having to adjust the range.

Pivot tables are incredibly useful in data analysis and by learning how to create and use them, you can improve your data analysis capabilities and create some stunning visuals. But be sure to watch out for some common pitfalls when creating pivot tables.

Is your pivot table not updating even though you’re refreshing the data? If that’s the case, that usually means there’s a problem with the data that your pivot table is referencing. Here’s how you can troubleshoot and fix the issue. Note: If your pivot table is linked to power query, you’ll want to first review that your query is updating correctly before moving forward.

Check your pivot table’s data source

The first thing you’ll want to do when you’re looking into problems with your pivot table’s data, is to start by going straight to the source. Click anywhere on your pivot table, as doing so will display the PivotTable Analyze tab on the ribbon. If you click in here, you’ll see an option to Change Data Source. Even though you may not necessarily want to change the data source, this is where you can see where your pivot table is pulling data from.

When you click on the button to change the source, you’ll see the table or range that your pivot table is referencing. In this example, it’s referencing the range A1:L150 in the Sales_Transactions tab:

If your data goes beyond the range, that’s where your problem exists. In my example above, the pivot table range only goes to row 150, but my data goes all the way to row 201.

Since the pivot table has a hardcoded range, anytime I add data beyond row 150, this pivot table will not include it, even if I do a refresh. The band-aid solution is to simply update the range to go to row 201. I could set a larger number for the row to give myself more of a buffer, but the danger is always that the pivot table may not be optimally sized, and the risk is that not all of the data will be included even when a refresh is done.

When creating pivot tables, the ideal solution is to put your data in a table

To avoid the issue of a pivot table refresh not updating your data, the best option is to put your data into a table. Once in a table, your range will automatically update, and you no longer need to worry about how many columns or rows to include; the table will expand as you add more data.

To create a table, click anywhere on your data set, and go to the Insert tab, where you’ll see a button for Table. By clicking this button, Excel will create a table and auto-detect your range. By default, it’ll also applying some formatting so that you’ll recognize it’s in a table format. But you can also change the color scheme of your table if you prefer.

Once you create a table, Excel also assigns a name to it. If it’s the only table in your sheet, you might see a name such as Table1 in the Table Design tab, under the Table Name field:

This reference now becomes a named range that you can use when creating or updating a pivot table. Rather than a fix ranged of cells, the pivot table can simply reference the table. And after making this change, any data that is added to the table will be included when the pivot table refreshes.

Creating a named range without a table

You don’t have to create a table to setup a dynamic named range for your data set, but it’s the easiest option. Another way is to create a named range that uses the OFFSET Function, which can automatically adjust based the number of rows and columns.

This is a more complicated setup, but how it works involves setting up a named range. Start by going to the Formulas tab and clicking on Name Manager. Then, click on the button for New, which will allow you to create a new name, and specify what range it refers to.

For the OFFSET function, you’ll need to also use the COUNTA function to count the number of non-blank rows and columns. Start by setting cell A1 as your starting position, and here is what the full formula will look like:

=OFFSET(A1,0,0,COUNTA(A:A),COUNTA(1:1))

This formula starts in cell A1 (which is where the pivot table begins in my example), doesn’t offset any rows or columns (the first two zero values indicate this), and then it counts the number of non-blank rows and columns, ensuring that it automatically expands. If you’re using this approach, you’ll want to make sure you have no other tables or data on the sheet, to ensure the COUNTA function is not picking up additional columns or rows, and expanding your range too far. And to keep it simple, I suggest putting your pivot table in cell A1.

Once you’ve created this named range, you can now use it as the data source for your pivot table, and it will do the same job as the table in that it will automatically expand as you add data.

If you liked this post on How to Fix a Pivot Table That Is Not Refreshing, 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.

Creating a pivot table is not difficult to do. If you just go to the Insert menu in Excel, there is an option to select a Pivot Table. As long as you have your data selected, Excel goes to work and creates the table. But making a good pivot table is an entirely different story. And that’s the real challenge — making your pivot table easy to read and understand, and not cumbersome to update. To help you with that process, here, I’ll list 10 of the most common mistakes you can make when creating a pivot table, and how you can avoid them!

1. Not cleaning your data beforehand

The old adage of “garbage in, garbage out” is true when it comes to data analysis, and pivot tables are no exception. If you have data that has incorrect values, then there’s no sense in creating a report on it. Take the time to check and review your formulas, make sure you don’t have any blank or missing values (or at least keep them to a minimum), and also check for spelling inconsistencies. That last one can be an easy one to miss. If you have a value with an extra space, that won’t be the same as a value without one.

If you spend the time of cleaning up and reviewing your data before you create a pivot table, you’ll save yourself some headaches and possible embarrassment later on; no one wants to provide their boss with a report that is incorrect.

2. Forgetting to use an Excel table

To convert your data into a table is easy — just use the CTRL+T shortcut. By doing so, you’ll ensure that your table will automatically expand and your formulas will copy down as you add new entries and rows. And as long as you pivot table references your table, then you won’t have to worry about re-adjusting the range later on. Otherwise, if you’re referencing a static range, the danger is that you forget to include new data as you add on to it. And in that situation, your report may once again be incorrect.

3. Not refreshing after data changes

Even if you’re using a table, you still need to ensure that your pivot table is refreshed. Unless you have a macro setup, this is a process you’ll need to do manually. By right-clicking on your pivot table and clicking Refresh, your data will update. You can also go to the Data tab and select Refresh from there.

Alternatively, if you right-click and select Pivot Table Options, under the Data tab, you can choose to Refresh data when opening the file, which will do a refresh when first opening the file.

4. Using merged cells in source data

Merged cells can cause lots of issues in Excel, including with pivot tables as that can cause problems when grouping your data. If you have merged cells, that can also mean that values are missing from certain fields. Merged cells should generally be avoided in Excel, and even if you want to spread a title across multiple cells, as you might with a header, there’s an easy way to look as though you’re merging a value without actually merging the cells.

5. Dragging text fields into the values area

If your pivot table looks like this, where you have a count of values when you’re expecting to see a summation or an average, it’s likely that you’ve put a text value in the values section:

This happens because Excel doesn’t know what to do with these values since it can’t add them. In the above example, a text field (category) has been placed in values section and upon doing so, Excel simply does a count of them. To fix this, ensure that you are correctly organizing your pivot table and not putting any text fields into the values section.

6. Not renaming field labels

When creating a pivot table, you might be tempted to leave the default names when setting it up. However, that can lead to unnecessarily long titles such as Count of Category or Sum of Sales.

You can fix this by selecting the arrow next to the pivot table field and clicking on Value Field Settings:

You might be frustrated that you can’t change the name to the same label. If I were to change the field to simply say ‘Sales’ then I would get an error stating that it’s already in use since that is the source name. However, an easy workaround is to just add an extra space. By renaming it to ‘Sales ‘ it no longer triggers an error and in the pivot table, it will look as though I’ve used the same name as the source. It also saves space and can make your field names more meaningful; there’s no reason you have to use the defaults.

7. Using Inconsistent Date Formats

If your dates are not entered as dates, and some are reading as text, or perhaps are entered as day/month/year rather than month/day/year, this can be another problem, because it can mean your data isn’t being grouped correctly.

This can be a harder issue to catch and that’s where doing a spot check of your pivot table after it has been created is helpful. You can clean your data beforehand, but errors like this are more difficult to spot. This is why it’s always a good idea to review your pivot table, and make sure it makes sense. If you have sales in a future month, for example, that can be an easy way to spot that you have a possible problem with your dates.

8. Repeating the same layout changes over and over again

When you first setup a pivot table, the default layout may not be what you want. But instead of making the same changes over and over again, why not simply save them as your new default? Once you’ve made the changes that you want, such as repeating labels and/or displaying the pivot table in a tabular format, you can save your pivot table layout as a default. This can be done by going to File-> Options -> Data and selecting Edit Default Layout for your pivot table.

You can import the layout by just selecting the pivot table you’ve modified and clicking on the Import button.

9. Making changes to individual cells rather than fields

One frustrating and easy-to-make mistake is to format pivot tables the way you might regular tables and other data in Excel. You might be tempted to select an entire column and change the formatting to how you want it. The problem? Once your pivot table refreshes, the data reverts back to its previous form. This is happening because you aren’t adjusting the underlying field settings.

To adjust the actual field, select it from your pivot table layout and go into Value Field Settings. Once there, go into the Number Format option and make any formatting changes there.

Now once you make the changes, they will remain intact, even after you update your pivot table.

10. Overcomplicating the Layout

As easy mistake to make with a pivot table is by cluttering it up with too many fields. In the below example, I have too many fields listed in the Rows section, and they’re in an order which isn’t logical, going from category->size->color->shoe model.

This doesn’t make the pivot table easy to read and understand. I have almost recreated the original table by setting it up this way. A good mix involves putting some fields in the rows section and some in the columns. And a good rule of thumb to follow is to use the fields which contain the most number of items in the rows section, rather than columns. This is because it’s a lot easier to scroll up and down with a mouse wheel versus horizontally and having to drag the scroll bar.

If you liked this post on 10 Pivot Table Mistakes to Avoid, 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 X and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

The default pivot table layout can oftentimes be suboptimal. The compact view doesn’t make it easy for analyzing data, especially when you have many fields. If you’re like me, one of the first things you probably do after creating a pivot table is to change the layout so it’s easier to view the data. The good news is you don’t have to keep repeating those steps. You can change the default so that when you create a pivot table, it’ll already have your desired settings applied. In this post, I’ll show you how to do that.

Here’s a sample pivot table, which shows you sales broken out by city, state, customer, and in this case, the type of product (shoe) sold:

There are many things which are suboptimal here, including the following:

• The compact format has put the customer, city, and state fields all in the same column.
• There are many subtotals, which create repeating values in this data set and are unnecessary.

To start, I’ll make these changes manually and then save those options as my default.

To turn off subtotals, I can go into the Design tab (the pivot table has to be selected for this to be visible) and under Subtotals, select the option to not show subtotals:

To change the layout from compact, I’ll stay in the Design tab and select Report Layout and choose Show in Tabular Form.

This now produces the following pivot table:

This, however, is still not ideal as the state values only appear once. Instead, I’d like to see the value repeating so that every line has every field filled in. This makes it ideal if you want to use any formulas that reference the pivot table. Back in the Report Layout section, there is an option to select Repeat All Item Labels. Upon doing this, now my pivot table is filled in for all possible fields:

This is how I prefer to setup my pivot table. But rather than having to repeat the process each time, I can save these settings.

How to save your preferred pivot table layout as the default

To save your preferred layout (after setting it up), go into the File tab and select Options at the bottom, which will open up the Excel Options. And if you navigate to the Data section, you will see the first option relates to the default layout of Pivot Tables — click the button to Edit Default Layout.

Next, you’ll find all the different options you can specify for your pivot table:

You can specify these different settings for subtotals, grand totals, and labels. Or, what you can also do is import the layout. To do this, simply click on a cell in your pivot table, and then click on the Import button. In my example, cell P14 contains my pivot table:

After clicking the Import button, the settings are automatically applied and updated:

As you can see, it has applied the changes for me, without having to make the changes manually from the different boxes and drop downs. This can be helpful if you’ve already setup a pivot table the way you want, rather than determining which different settings you want to apply. When you click on OK, now your settings will be applied.

The next time you create a pivot table, these saved settings will be in place and you won’t have to change them again. These settings are saved to your computer and even if you open a new Excel file and create a new pivot table, they will take effect.

If you liked this post on How to Change the Default Layout of Your Pivot Table 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Pivot tables in Excel are useful for summarizing data. But you can do more than just simple summations and can also calculate running totals based on multiple fields. In the spreadsheet which I’ll use for this example, I have sale by month and by category.

Creating and setting up the pivot table

The first step is to create the pivot table. This can be done by just clicking anywhere on the data set and going to the Insert tab and clicking on Pivot Table. I’ll put the dates in the Row section and the sales field in the Values section. I’ll insert the sales field a second time so that I can have one value for the raw monthly sales alongside the running total. Here’s what it looks like thus far:

To create a running total, I’m going to right-click on the second sales column and select Value Field Settings. Next, in the Show Values As tab I’ll select Running Total In and use Month as my base field. I’ll also rename the field to say Running Total:

This sets up the pivot table to show my total sales alongside the running total. A good way to check to see that it is correct is to see that your grand total in the original sales field matches the last value in the running total:

You can also have running totals reset based on the category. In my data, I have electronics and furniture sales. To break it down by those sections, I’ll add the Category field above the Month field in the Rows section. Now I have the running totals broken out by category while still tracking year-to-date values.

This format may be a bit confusing since the subtotals are above the data. What you may want to do is move the subtotals to the bottom of each section, so that it’s easier to compare them against the running totals to make sure they match up. To do this, click on the pivot table to activate the Design tab. Within there, there is a drop-down option for Subtotals where you can select to Show all Subtotals at Bottom of Group.

Upon selecting that option, the totals will now appear at the bottom.

If you like this post on How to Calculate Running Totals 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.