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.
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.
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:
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.
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:
If I load this into a pivot table, my fields are as follows:
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:
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:
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:
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:
At the following dialog box, uncheck years and quarters and just leave Months:
After making all those changes, my pivot table now looks like this:
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:
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:
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.
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:
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:
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.
After clicking OK, your calculated field will now show up on the pivot table:
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:
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:
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.
