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.
PIVOTBY is a relatively new function that Excel introduced in 2021. It is a dynamic, formula-based alternative to pivot tables. It’s part of Excel’s push toward more dynamic, flexible data analysis, especially for users who prefer formulas over the drag-and-drop interface of traditional PivotTables. In this article, I’ll walk you through what PIVOTBY does, how to use it, and provide some examples to help you master it.
What Does the PIVOTBY Function Do?
PIVOTBY summarizes data based on one or more grouping columns and returns an array with calculated values—similar to a pivot table, but directly inside a formula.
It allows you to group data by categories (like region, product, or date) and then apply an aggregation function (like SUM, AVERAGE, COUNT, etc.) on another column. Here are the main arguments for the function:
row_fields: this the range which will be used to group rows.
col_fields: this is the range which will group the columns.
values: this is a range for the data which is to be aggregated.
function: this determines how the data should be aggregate (e.g. SUM, AVERAGE, COUNT)
How the PIVOTBY Function Works
Here’s a sample data set that I am going to use to illustrate how the PIVOTBY function works:
In the above table, dates are in column A, the name is in column B, followed by store in column C, department in column D, and amount in column E. Assuming my data is in a table called tblData, I can use the following syntax to create a pivot table showing sales by name and store:
You can also pull in multiple columns or rows with the help of the CHOOSE function. Suppose you wanted to pull in both the name and the department a person is from. Here’s how you can accomplish that, while also removing totals:
The benefit of this setup is that since this is a spill function, it will automatically update and populate the data and no refresh is necessary. There are, however, downsides to consider:
If there is not enough space for the pivot table, you will encounter a #SPILL! error.
Unlike a conventional pivot table, you can’t drill down to see the details
This won’t work on versions of Excel prior to 2021.
If you just want an easy way to summarize your data without the need for drilling down, then the PIVOTBY function can work well and be a suitable replacement for a normal pivot table. For more of a comparison between the function your typical pivot table, check out this video:
If you liked this post on How to Use the PivotBy Function 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.
Creating a Pivot Table is one of the fastest ways to summarize large amounts of data in Excel. But if your data is spread across multiple sheets, it can seem a little overwhelming. The good news is: you don’t have to manually copy everything into one sheet. You can easily combine multiple sheets into a single Pivot Table.
In this Excel tutorial, I’ll walk you through how to make a Pivot Table from multiple sheets, step-by-step, with the help of Power Query. If you want to follow along, download the practice file here.
Loading the data into Power Query
You will need to have your data setup in tables in order to combine all of it. However, you can do that all at once. Go through each tab and select your data, and under the Data tab, click on the option to get data From Table/Range:
Excel will automatically detect your range. You can adjust if need be, otherwise, you just need to confirm whether it contains headers (which it should to avoid issues later on):
This now opens up Power Query, where your data is now visible:
On the Home tab, select the option for Close & Load and select Close & Load To and choose Only Create Connection.
This ensures the data is added into Power Query but it does not create a new tab. Repeat these steps for the other tabs.
If you forget to select the option to only create a connection, then you can just delete the tab that is created afterwards; the result is the same — only a connection will be created. After loading five tables in this practice file and creating the connections, you should see the following queries under the Queries & Connections pane on the right-hand side (this should automatically display when you first add a table to Power Query).
With the data loaded, now let’s go into Power Query by right-clicking on any of these connections and selecting Edit.
In Power Query, on the left-hand side, you can edit the table names so that you know what they relate to:
By right-clicking on the table names, you can change them. I’ve renamed them so that it is clear which regions they relate to:
Table1: Northwest
Table2: Northeast
Table3: Southwest
Table4: Southeast
Table5: Central
In each table, I’m also going to add a field called Region, where I will list the names of those tables. By going into the Add Column tab and selecting Custom Column, I can enter in the new column name as well as the value. This is the custom column I’m creating for the Northwest table:
After creating the column, this is what the updated table looks like:
Repeat these steps for the other tables, entering their specific regions for the Region field. In order to ensure the data is consolidated correctly, you’ll want to ensure that the field names are the same.
Appending the queries together
With all the data in Power Query, we can append the queries together, to create one large table. To do this, go to the Home tab and select the Append Queriesas New option, which will create an entirely new query:
Select the option to append three or more tables and select all of them and then click OK:
Now, all the tables are appended into one query:
Now, this appended table can be loaded into Excel. In this instance, you don’t want to create just a connection but instead download the entire table into Excel.
This table can now be used to create a Pivot Table as you normally would, through the Insert -> Pivot Table option. And now, when creating your pivot table, you’ll see the values from all regions combined. You can now slice the data based on region and month, and whatever other fields are in the data set.
If you enter more data and want to update your pivot table, what you first need to do is go to the Data tab and click on Refresh All. This will refresh your queries. You will also need to do another refresh to ensure that the pivot table updates after that. This can be done by just right-clicking on the pivot table and clicking refresh rather than doing Refresh All again, which will also update the queries and may be more time consuming.
If you liked this post on How to Make a Pivot Table From Multiple Sheets in Excel (Step-by-Step Guide), 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.
Did you know you can create a pivot table in Google Sheets which automatically updates as you add data to it? Remarkably, it’s an easier process than in Excel where you would need a macro or where you might need to right-click on the pivot table and select to refresh the data. Here’s how we can go about creating a pivot table in Google Sheets, and having it automatically update.
Creating a pivot table in Google Sheets
For this example, I’m going to use the following data for my pivot table:
To create a pivot table with this range, all I need to do is, with a cell selected, to go to the Insert menu and click on Pivot Table
As long as you have a cell selected on your data set, Google Sheets will automatically detect your range. If it looks correct, you can just select whether you want it to be placed in a new sheet or an existing sheet, and then click on Create.
The next part is to setup the pivot table and ensure that the value section contains values and you have something in either the rows, columns, or filters sections to summarize your data. In my example, I’m going to summarize my data by rep and store:
The pivot table is setup but the problem is it won’t automatically update. Here’s how we can fix that.
Setting up your pivot table so that it automatically updates in Google Sheets
The problem with this pivot table lies with the range. While Google Sheets correctly detected a range, it also set it to a specific number of rows. My data set went up to row 201 as it contained 200 rows of data. But if I add more data, my pivot table won’t automatically expand. To get around this, I need to adjust my pivot table range.
With my pivot table selected, I can see the range that it references in the Pivot table editor pane:
If I add another row of data, I can adjust this range so that it goes from A1:G202. But this would be a very tedious task if every time I added data I needed to remember to adjust the range. Instead, what I can do is adjust my range so that it references entire columns. By doing this, Google Sheets will automatically detect the size of my data set. In this example, I just need to set my range to A:G:
Now my pivot table will include a blank value under the Salesperson field as well as a blank store value.
To fix this, what I can do is hide row 3 and column B, since these ranges contain the blanks. And as long as the blank values always appear first, this can be an effective way to hide the data, even if the pivot table expands.
If you like this post on How to Create an Automatically Updating Pivot Table 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 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 are a powerful feature in Excel that allow users to summarize, analyze, and visualize data. One of the more advanced features of pivot tables is the ability to add calculated fields. Calculated fields enable you to perform calculations on the data within your pivot table without modifying the original dataset. This can be incredibly useful for generating new insights and custom metrics. In this post, I’ll show you how you can take them a step forward and even incorporate IF statements within calculated fields. Here’s the data set that I’m going to use for this example:
How to add calculated fields in a pivot table
To add a calculated field to a pivot table, take the following steps:
1. Convert your data into a Pivot Table.
2. Click on any cell within your Pivot Table to activate the PivotTable Analyze tab on the ribbon.
3. On the PivotTable Analyze tab click on Fields, Items & sets and then select Calculated Field
4. Enter a name for your calculated field in the Name box.
5. Write out the formula you want to use in the Formula box. You can use existing fields (columns) from your dataset by double-clicking on the field names listed in the Fields box.
6. After you’ve completed writing your formula, click Add then press OK. Your calculated field will be added to the PivotTable, typically in the Values area.
How to use an IF statement in your calculated field
One of the more powerful uses of calculated fields is the ability to include conditional logic using an IF statement. This allows you to create dynamic calculations that can change based on the criteria you set. For my pivot table, I just have a list of dates to start with:
Suppose I want to create a calculated field which will show a value if it is profit (i.e. a gain), and a loss field which will show a value when it is negative.
In the formula box, I’ll write an IF statement for my profit field calculation. It will reference the gain/loss field which I already have. If the value is positive, it will retrieve that value, otherwise it will be zero.
Now, I’ll click on Add and then I’ll setup the Loss field:
Now when I add these fields to my pivot table, I have one column for the profit values, and one for the losses:
If you like this post on How to Track Income and Expense in a Single Chart, 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.
Do you want a quick way to clean up your pivot table and remove blanks and errors from it? Below, I’ll show you how to do that with just a few steps. In the below pivot table, I have error values and blank row values, which indicate that data is missing:
Ideally, we would adjust our data set to ensure that this data is cleaned and there are no errors. But if you need to quickly clean this up, here’s what you can do.
How to remove error values from a pivot table
To prevent error values from showing on your pivot table, follow these steps:
1. Select your pivot table.
2. On the PivotTable Analyze tab, click on Options
3. Under the Format section, check off For error values show
4. If you want something else to show in place of an error value, enter it in that field. Otherwise, leave it blank and then press OK.
Now your pivot table will not show any error values on it:
There’s still the issue of the (blank) value in the row labels. Let’s address that issue next.
How to remove (blank) row labels from a pivot table
Follow these steps to get rid of the ‘(blank)’ row values which appear in your pivot table:
1. Select the drop-down filter button on your pivot table.
2. Select Label Filters and Does Not Equal
3. Set the criteria so that it does not equal(blank)
This will now remove the blanks from your pivot table:
Now both the blanks and error values are gone from your pivot table.
If you like this post on How to Hide Blanks and Error Values on 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.
Do you want to be able to use a VLOOKUP with a pivot table? While there isn’t a way to natively do so, there is a way you can make it look as though your pivot table has a lookup function within there, and make it so that it expands along with your data. Suppose you have the following pivot table, which shows employee spending:
Let’s say we want to look up the department that the employee belongs to, based on the following lookup table:
We can’t create a field that does a lookup within a pivot table, but we can make it look as if that’s what we are doing.
Copy your pivot table formatting to make it look as though you’ve added another field
I can create a field called ‘Department’ directly next to my pivot table. And what I can do to make it look as though it’s a continuation of my pivot table is to use the Format Painter so that I can copy the formatting over. To do this, simply select the formatting for the pivot table header, click Format Painter, and then click on the new field. Now it looks as though it’s the same format as your pivot table:
The one drawback is that if you adjust your pivot table, you’ll need to update the formatting. You’ll also want to make sure you don’t expect your pivot table to expand — i.e. you won’t be adding any more fields to expand it horizontally. If you do so, you’ll encounter an error saying that there isn’t enough room for your pivot table. In that case, you can insert a column. But ideally, you would set this additional field once you’ve added all the fields you plan to use in your pivot table.
Using the VLOOKUP function next to your pivot table
The next step is to use the VLOOKUP function the way your normally would. With the employee name in cell A2, and my lookup table in columns F:G, I can set my formula up as follows:
=VLOOKUP(A2,F:G,2,FALSE)
But this is still not ideal as copying this formula down to far will show errors for both grand totals and blank values:
The solution here is to add an IF statement before the VLOOKUP function. In the below example, my formula is checking for both a blank value and a ‘Grand Total’ value. If either criteria is met, it returns a blank:
Now I can copy my formula down and the formula won’t return a value when the value in column A is blank or is a grand total:
Now it appears as though my lookup function is dynamic and automatically adjusting based on my pivot table selections.
Adding the field to the data set is the ideal solution
Creating a field by adding a formula next to your pivot table can work if your table never expands. But if it might need to, a more versatile option is to simply add the field into your original data set and do the lookup there.
In this data set, I’m missing the department field. But if I add the VLOOKUP formula here, I can pull in the department values right in there. The formula is setup the same and by doing it this way, I can add the field directly to my data set:
Now, when I update my pivot table I can directly add the department field right into the Rows section:
Then, my pivot table shows the additional field, and I won’t run into any issues whether I need to add rows or columns:
In some cases, you might just want a quick way to do a lookup and not adjust the data set, in which case the first method can be preferable. But if you are able to add the field directly into your data set, that is the ideal approach.
If you like this post on How to Use VLOOKUP with Pivot Tables, 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 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:
Select the range of data you want to analyze.
Go to the Insert tab and click on PivotTable.
Choose where you want the pivot table to be placed.
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.
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.
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:
Right-click on any date in the pivot table.
Select Group from the context menu.
In the Grouping dialog box, select how you want to group your dates.
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:
Go to the PivotTable Analyze tab.
Click on Fields, Items & Sets in the Calculations group.
Select Calculated Field from the dropdown menu.
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.
In the PivotTable Analyze tab, click on Insert Slicer in the Filter group.
The Insert Slicers dialog box will appear, listing all the fields available in your pivot table.
Select the fields you want to use as slicers. You can choose multiple fields if needed (e.g., Product, Store, Salesperson).
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.
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.
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.
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:
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:
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:
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.
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:
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.
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:
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.
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:
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:
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.
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.
