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.
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.
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.
It’s time for an updated dashboard post. My original post is now three years old and probably overdue for an update. This time around, I’m going to start from scratch using a real data set from the Bureau of Labor Statistics, where I’ll walk you through my process from start to finish. To follow along, you can download the data I’m going to use from here (I’m going to use the 2020 state data. This is the XLS link).
Preparing the data
If your data is no good, then it won’t matter how great your charts and visuals look. That’s why it’s important to have a look through the data to see how usable it is. And you may not notice any issues until you start populating your charts. But one of the things that are noticeable right of the bat in this data set is that instead of empty values on this sheet, there are # or * signs.
That’s going to be a problem if you want to do any computations on this data. You can use Find and Replace to replace the data with empty values. Note that for the *, you’ll need to find ~* rather than just *, otherwise Excel will interpret the * as a wildcard and find everything.
One other thing that I am going to do is create another column for the occupation titles. In column J, there are more than a dozen titles for the ‘major groups’ (major is indicated in column K). I am going to create a table to group them even further. I’ve put this on a separate lookup sheet:
Now, what I am going to do is insert a column on my main data sheet, after column J, which will do a lookup on this table. The formula will be as follows:
=IF(L2=”major”,VLOOKUP(J2,Sheet1!A:B,2,FALSE),””)
Now, I have a category field in column K for the ‘major’ group classifications:
Next, I’m going to convert the data into a table. To do this, click on any of the cells in your data set, and on the Insert tab, click on Table:
Once done, you should notice some default table formatting gets applied to your data set:
And to make it easy to reference, I’m going to click on the Table Design tab, and under the Table Name section on the left, I’m going to re-name the table to tblData:
To change the name of a table, all you need to do is click on it and make your changes, then press enter.
Creating the pivot tables
For this dashboard, I’m going to create pivot tables and use charts to show the following:
Median salary for the specified position.
Wages by percentile.
Median salary for the specified state based on job categories.
A pie chart showing how many jobs there are by category.
A gauge chart showing how the median salary compares to the national average.
A map chart showing the median wages by state.
Median salary for the specified position
To create this visual, I’m going to create a pivot table from the tblData and put it on a new ‘PT’ tab. For this, I am just going to take the average of the A_MEDIAN column. I will also filter the O_GROUP field so that it only includes the ‘detailed’ group to avoid including the categories. I will also adjust the formatting so that it uses the accounting format. The pivot table itself contains just one value:
I only want this value to show up in a box but what I’m going to do is create a column chart from this. For just the number to be visible, I’m going to add a data label and then remove everything else, including the legend, gridlines, and make the column a clear color. Lastly, I’ll copy my first visual onto a new ‘Dashboard’ tab and put the words ‘Median Salary’ directly above it:
Wages by percentile
Next, I’m going to create a bar chart that shows the wages for a position by the various percentiles that are in the data set. For this, I’m going to grab all the different percentile fields, including the median:
A_PCT10
A_PCT25
A_MEDIAN
A_PCT75
A_PCT90
I’ll need to set these calculations to be averagesjust like on the earlier calculation. I can re-name these to ’10th percentile’, ’25th percentile’, and so on, to make it easier to read. Then, I’m going to create a 3-D bar chart, change the colors, and add some labels so it looks like this:
Median annual wage for the specified state based on job categories
Now, I’m going to create a pivot table and chart to show what the median annual wage is across the different categories I specified earlier for the selected region. This is a simple pivot table set up, all that’s needed is the A_MEDIAN average in the values section of the pivot table, the CATEGORY in the rows, and the O_GROUP to filter just the ‘major’ jobs. This will result in the creation of the following column chart:
A pie chart showing how many jobs there are by category
One of the interesting metrics in the data set is the number of jobs there are per 1,000 jobs in the given region. This is going to be similar to the previous chart, except this time I am going to use the JOBS_1000 field. I’m going to use a pie chart for this visual just to change it up a little bit.
A gauge chart showing how the median salary compares to the national average
I’m going to use a gauge chart to compare the median salary against the national median and how it compares. For detailed steps on how to create a gauge chart, please check out this post. For this visual, I need to create one pivot table just for the national median wage. To do this, I just need to grab the median value and filter the O_GROUP by ‘total.’
For the actual gauge chart, I need to set up a table for the slices and the ranges. I will go with a setup as follows:
The % of completion will take the median value and divide it by the national average. But to avoid it going over 100, I’ll use the MIN formula. And for the ‘high end’ value, I take 200 (think of 100 as the top half of the circle and the other 100 the bottom half) and subtract the % of completion and add the size of the slice. Here is what it looks like when the median salary is greater than the national median:
This is what the gauge chart looks like once it’s been set up following the steps in the previous post:
A map chart showing the median wages by state
Creating a map chart is pretty easy in this situation because we have all the state names and all I need to do here is create a pivot table with the A_MEDIAN value. Here’s what my pivot table looks like:
However, you can’t create a pivot chart directly from a pivot table. But there is a way around that. I’m going to create another table that copies the values from the pivot table. They simply equal the values to the left:
Now, I can create a map chart based on this table:
I now have all of my charts set up:
What’s next is to set up the slicers.
Adding and linking the slicers
I’m going to add two slicers for the dashboard, one for the state and one for the job title.
To insert a slicer, all that’s necessary is to click on any one of the pivot tables and on the Insert tab, click on the Slicer button:
Then, select the fields you want to add. Generally, I add the fields that have the most selections and longest names going down vertically. In this case, that’s the OCC_TITLE field. For the State and Category slicers, I have those going across:
I’ve also added a title just below the slicers to give the dashboard a name. The last piece of the puzzle here is to link the slicers to the pivot tables. Previously, I linked them to all of the tables. But for some of these charts, I don’t want them to link to everything.
For the State and Category slicers, I want them to update everything except the national median pivot table. And for the OCC_TITLE slicer, it should also not update the jobs per 1000 pivot table or the median wage by category. The reason being is that those charts will lose their value if only one job is selected, as the point is they should give an overview of the different categories. Similarly, you could also unlink the state slicer to the map chart.
To manage these connections, you can slicer and select Report Connections:
From there, you can select with pivot tables you want the slicer to link to:
And to keep your slicers from staying in put despite any changes, you can also right-click and select Size and Properties and then select the option to Don’t move or size with cells:
Now, the dashboard is ready to go!
If you liked this post on Making Dashboards in Excel With Map and Gauge Charts, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Using pivot tables to summarize data can be a great way to display information quickly and total everything up. However, in some cases, data that you download is already in what you might call a pivot table format where it is summarized and you want to put it in more of a tabular format. In this post, I’ll show you how to unpivot data in Excel where you can turn a table like this:
into this:
Unpivot using Power Query
Rather than copying and pasting data into a tabular format and doing the process manually, you can just use Power Query to do it for you, all in a matter of seconds. First thing’s first, you need to get your summarized data into Power Query. To do that, click on one of the cells in the table and on the Data tab, click on the From Sheet button in the Get & Transform Data section:
Then, click OK on the default range and then the next screen will be Power Query:
The key to making the unpivot work correctly is to determine which column(s) you don’t want to unpivot. In this case, it is only the Year field as I want to have the years listed out. With the Year column selected, I right-click on the header and select Unpivot Other Columns:
After clicking on that, the data is unpivoted and now it is in tabular format:
All that is left now is to press the Close & Load button in Power Query, which will then populate the data back into Excel:
You can repeat these steps for other, similar summarizes should you need to unpivot data.
If you liked this post on How to Unpivot Data in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
