EconomicDashboard

Create a Dashboard in Excel to Track Economic Indicators

Creating a dashboard can be an effective and efficient way to pool in many data points. In this post, I’ll show you how to create a dashboard that factors in several economic indicators, including inflation, interest rates, housing starts, GDP, unemployment, and the performance of the stock market. It will utilize power query and allow you to easily refresh the data.

Creating and collecting the data points

To make the data that I’m dynamic, I will also use a variable for the current date, so that the data will automatically update. In this example, it will be called todaysdate which is equal to the following formula:

=TEXT(TODAY(),"YYY-MM-DD")

Below are the sources for the data that I will use in creating this dashboard along with the Power Query links I will use (along with the variable for the date). I’ll also set up the Power Query links as named ranges in the Excel spreadsheet, making it easy to reference them within the queries.

Unemployment:

Named Range: unemployment

Source: https://www.bls.gov/charts/employment-situation/civilian-unemployment-rate.htm

Power Query: https://www.bls.gov/charts/employment-situation/civilian-unemployment-rate.htm

GDP:

Named Range: gdp

Source: https://fred.stlouisfed.org/series/A191RL1Q225SBEA

Power Query: https://fred.stlouisfed.org/graph/fredgraph.xls?drp=0&fo=open%20sans&mode=fred&ts=12&tts=12&width=1168&nt=0&thu=0&trc=0&id=A191RL1Q225SBEA&cosd=1947-04-01&coed=2022-12-07&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Quarterly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-12-07&revision_date=2022-12-07&nd=1947-04-01

Interest Rate:

Named Range: interest

Source: https://fred.stlouisfed.org/series/DFEDTARU

Power Query: https://fred.stlouisfed.org/graph/fredgraph.xls?bgcolor=%23e1e9f0&drp=0&fo=open%20sans&mode=fred&recession_bars=on&ts=12&tts=12&nt=0&thu=0&trc=0&id=DFEDTARU&cosd=2008-12-16&coed=2022-12-07&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Daily%2C%207-Day&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-12-07&revision_date=2022-12-07&nd=2008-12-16

Inflation:

Named Range: inflation

Source: https://data.bls.gov/timeseries/CUUR0000SA0?years_option=all_years

Power Query: https://data.bls.gov/timeseries/CUUR0000SA0?years_option=all_years

Housing Starts:

Named Range: housing

Source: https://fred.stlouisfed.org/series/HOUST

Power Query: https://fred.stlouisfed.org/graph/fredgraph.xls?bgcolor=%23e1e9f0&drp=0&fo=open%20sans&mode=fred&ts=12&tts=12&nt=0&thu=0&trc=0&id=HOUST&scale=left&cosd=1959-01-01&coed=2022-12-07&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2022-12-07&revision_date=2022-12-07&nd=1959-01-01

Stock Market:

Named Range: stockmarket

Source: https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC

Power Query: https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC

Loading the data into Power Query

Note on named ranges

Using the links above, I’ll create the connections in Power Query and make adjustments where necessary. To reference a named range in Power Query, you can use the following code as an example:

NamedRange = Excel.CurrentWorkbook(){[Name="namedrange"]}[Content]{0}[Column1],

The name is case-sensitive so if you use a named range that is all in lowercase as I have done, then those references also need to be in lowercase in Power Query. However, for the purposes of this example, you don’t need to use named ranges and it is an optional step.

Creating the Power Query connections

To create a Power Query connection, I’m going to start by going into the Data tab and selecting From Web under the Get & Transform Data section. For the unemployment rate data, I’ll use the link for that:

Selecting the URL to create a power query connection from the web.

After click on OK, I’ll select the table that I want to use, which is the first one on the list:

Selecting the table to use in Power Query.

I’ll click on the Transform Data button before loading it. What I will do is split the Month column so that I have both a Month and Year field. To do this, I’ll select the column, right-click and select the option to Split by Delimiter and use a space. I’ll also use this opportunity to put in my named range for the data link. In the Power Query window, under the Home tab, there’s an option to click on the Advanced Editor. Here, I’ll enter my NamedRange variable and use that when referencing the Source:

Using the advanced editor in Power Query to reference a named range.

When you’re running a query for the first time, you may see a warning asking you about Privacy Levels. Set these to Public and select Save.

Setting the privacy levels in Power Query.

Now it’s time to repeat the steps for the other data sources.

Transforming the data in Power Query

There will be some adjustments that need to be made along the way when loading the data. For example, for the data that comes from the FRED website, there are some rows at the top that need to be removed:

Removing extra rows in Power Query that appear at the top.

In this case, I’ll need to click the Remove Rows button at the top, and specify that I want to Remove Top Rows and enter a value of 11, to remove the first 11.

For the housing and inflation data, I need to make additional adjustments since the data is raw and doesn’t show the percent change, which is what I want. Here are the steps I’m going to take for those queries:

  • Unpivoting the data. This is important for the sake of making sure that months are not going across and are instead going vertically. Refer to this post on how to flip and unpivot data in Power Query.
Unpivoting data in Power Query.
  • Generating previous and current period data. I’ll create a calculated column to calculate the current period and the previous period. After the current period column is created (by simply joining the month and year together), I’ll duplicate the query so that there is an additional table for the inflation data. As for the previous period, this involves subtracting 1 from the year to get the previous year’s values. Then, the year and month are concatenated:
Calculating the current and prior-year period dates in Power Query.
  • Doing a lookup of the prior-year period. I’ll now merge the query with the one I copied earlier (the other inflation period). This involves doing a lookup of the previous period on the other table’s current period. The goal here is to get the prior-year period’s value. Here’s an overview of how to merge queries in Power Query.
Merging queries in Power Query.
  • Calculating the percent change. Once the prior-year period’s value is loaded and on the same row, I can create a custom column to calculate the year-over-year change, which is just the new value / old value -1.
  • Removing unneeded values. The final steps involve removing any blank values from the inflation rate and removing and periods that contain the word “HALF” indicating half-year values. Lastly, I’ll split the columns back out so I again have the year and month broken out, this time, along with the inflation rate %:
Power Query table showing the inflation rate by month and year.

These steps will be similar for the housing data, except I won’t need to unpivot the data since it isn’t broken out by month and year.

Creating the pivot tables and linking to the data

Now that the data is loaded, the next step is to link to it or create pivot tables, to populate the dashboard. For the unemployment data, I will summarize the average by year:

Pivot table showing unemployment data averages by year.

For the GDP tab, I’ll pull in just the four most recent quarters. To do this, I can use the INDEX function and the COUNTA function to grab the furthest values. For the most recent period, I can use the following formula:

=INDEX(A:A,COUNTA(A:A),1)

For more recent periods, I’ll deduct 1, 2, and 3 from the COUNTA value:

The GDP growth rates in Excel for the past four quarters.

The interest rates I will leave as is as that data can chart smoothly given that there normally aren’t many interest rate changes.

For the inflation rate, I will again take the average annual rate using a pivot table but only looking at data since 2010:

Pivot table showing the average inflation rate by year.

On the housing tab, I will break out the average housing starts by quarter, again using a pivot table:

Pivot table in Excel showing housing starts by quarter.

Creating the dashboard

Now that the pivot tables are set up, I can start putting together the dashboard.

For starters, I’m going to go for a clear, dark background, setting it to black. I’m going to create headers for each of the different categories: Unemployment, GDP, Interest, Inflation, Housing Starts, and Stock Market. I’ll link to the key data, referencing the key metric that I want from each tab. Each header will take up three columns, with a space between each one:

Key economic indicators showing in Excel.

What I will also do is create some conditional formatting rules for these values so that they can appear green or red based on their values. Refer to this post for an in-depth overview on conditional formatting. Below the values, I will also extract the date of the most recent data and put it within a formula, to show when the data was last updated:

Economic indicators in Excel with conditional formatting applied to them.

Next, I’ll create the charts for the different pivot tables. This is really down to preference and style, but I’ll use a combination of bar, column, and line charts to display the data. Here’s how the dashboard looks after adding a title:

A dashboard showing economic indicators, using headers and charts.

And with the data all coming from the web and utilizing Power Query, you can simply just refresh the data to pull the latest numbers, making your dashboard dynamic and easily updateable.


If you liked this post on How to Create a Dashboard in Excel to Track Economic Indicators, 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.

H2EAnimateDashboards

Animate Your Dashboards in Excel With This Free Template

Dashboards in Excel can update when a user makes a selection on a slicer or refreshes data. You can even use macros to automatically update a chart or dashboard for you. In this post, I’ll share with you a template that I’ve created that will allow you to effectively play your dashboard, updating it from one period to the next, and showing the change in the chart over time. Here it is in action:

The template has three sections: one for pivot tables, one for the data, and one for the dashboard. You can set the file up however you want, the main area that needs to remain largely the same is the dashboard sheet. Every chart on this sheet only will automatically get updated. And for it to work properly, all the charts need to be linked to the one timeline chart in here (i.e. there cannot be more than one). For information on how to set up your timeline (or any other slicer for that matter) so that you can link it to multiple charts, you’ll need to learn about how to adjust Report Connections in this post.

Once you’ve got the charts you want to be connected to the timeline, then it’s a matter of just updating the settings section on the Dashboard tab. This is off to the left, with the values that you need to enter/update highlighted in yellow.

Settings section on the template.

These simply specify what date you want to start from, where you want to end at, and by which interval you want to jump (e.g. x days/months/years). Depending on the frequency you select, your dashboard can either play very quickly, or very slowly.

The last step is to just click the Animate Dashboard button at the end of the home tab:

Animated dashboard button.

Upon clicking this, the timeline will jump by the intervals you specified. No other changes will be made to any filters or slicers you have selected. The only changes will take place to the timeline, at which point, you should see something similar to the video posted at the top of this post.

You can download the free Animate Dashboard template for free, from here.


If you liked this free template that helps you animate your dashboards, 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.

stockdashboard

Creating a Stock Market Dashboard in Excel

Want to create a dashboard to track the stock market and the latest business-related news? Below, I’ll show you how you can create a stock market dashboard using Excel and Google Sheets to pull in all the data you’ll need. If you’d prefer to just download the file, you can do so here.

Step 1: Compiling the data

You can get stock prices into Excel using the STOCKHISTORY function. However, that isn’t available on older versions of Excel and it also doesn’t pull in the current day’s prices. Using Google Sheets can be more effective for this purpose. Plus, on there, I can pull in business-related news as well.

To start, I’m going to pull in values for the Dow Jones, Nasdaq, and S&P 500. I’ll also download the values of a couple of exchange-traded funds (ETFs) that track healthcare and tech stocks. To get the latest price, you can use the built-in GOOGLEFINANCE function that’s only available on Google Sheets. To get the latest value of the Dow Jones, the following formula will work:

=GOOGLEFINANCE(“.DJI”,”price”)

And to calculate the percentage change:

=GOOGLEFINANCE(“.DJI”,”changepct”)/100

For the Nasdaq, you’ll use “.IXIC” and for the S&P 500 the ticker is “.INX”

For the ETFs, since they aren’t indexes, there is no period beforehand and I reference XLK for tech and XLV for healthcare. In my Google Sheets file, I have a simple layout for the values and their changes that I will later pull into Power Query:

Stock market indicators in Google Sheets.

Next, I’ll also download the latest business-related news. Google Sheets has another unique function for this: IMPORTFEED. All you need to do is find an rss feed from a website that you want to pull information from. Not every website has an rss feed but what you can do is just do a Google search for the name of a source and ‘rss’ to see if you can find a link. There are three sources I’m going to use for this dashboard:

CNBC: https://www.cnbc.com/id/10001147/device/rss/rss.html

WSJ: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

NYT: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

I will pull them all in the same way, using the IMPORTFEED function. Here’s an example with the CNBC feed:

=IMPORTFEED(“https://www.cnbc.com/id/10001147/device/rss/rss.html”,”items”,true,10)

In Google Sheets, the top articles from each of those rss feeds will show up, including the title, URL, date created, and even a brief summary:

News articles pulled into Google Sheets using the IMPORTFEED function.

Now, it’s time to pull all this data into Excel.

Step 2: Loading the data into Excel using Power Query

To import data from Google Sheets into Excel, you need to first share the sheet. While in Google Sheets, go into File -> Share -> Publish to web. Then, you’ll be prompted to select what you want to share. I’ll start with the Markets tab I created and then the News tab:

Publishing data to the web from Google Sheets.

Copy this URL as you’ll need it to load the data into Power Query. While you’re back in Excel, go under the Data tab and click on the From Web button under the Get & Transform Data section. You’ll be prompted to enter a URL. This is where you’ll paste the link that you copied from Google Sheets:

Creating a query in Excel using the from web option.

On the next page, select Table 0 as where you want to extract data from. And if you want to do some cleanup (getting rid of extra columns), you can do so by clicking on the Transform Data button:

Selecting a table for Power Query to pull data from.

To remove any unneeded columns in Power Query, just right-click on a column header and click Remove:

Removing a column from Power Query.

Once you’re done, click on the button to Close & Load if you want the data to be loaded on a new sheet. If you want to control where it gets pasted, then use the drop down and select Close & Load To.

Repeat these steps for the other Google Sheets tab.

In addition, I’m also going to load data from a few other sources:

Top 100 Gainers on Yahoo Finance: https://finance.yahoo.com/gainers/?offset=0&count=100

Top 100 Losers on Yahoo Finance: https://finance.yahoo.com/losers?offset=0&count=100

Upcoming IPOs from IPOScoop: https://www.iposcoop.com/ipo-calendar/

The process for importing these links into the dashboard is the same as for Google Sheets. Go through Power Query, import from web, and paste in the URL plus make any formatting changes necessary. The next step involves putting all this data together in a dashboard.

Step 3: Creating the dashboard

In my spreadsheet, I’ve created two tabs: one that hold all my Power Query downloads (the ‘Data’ tab) and a ‘Dashboard’ tab for where all the information will be displayed.

To make the set up of the dashboard easy to manage, I’m going to change the column width to 10 for everything. To do that, press CTRL+A to select all the cells on the Dashboard tab, then right-click on any of the headers, and there you’ll be able to select column width.

First up, I’m going to get the indexes and market indicators as a starting point. To do this, all I need to do is link to the values and the percentages for the S&P 500, Dow Jones, Nasdaq, Tech, and Healthcare tickers I imported from Google Sheets. By default, I’ll set the formatting for all the cells to be green:

Market indicators imported into Excel from Google Sheets.

To make this more dynamic, I will add some conditional formatting so that if the percentage change is negative, the corresponding cells will highlight in red. For this, I can select all the cells in green above and create a conditional formatting rule the starts with where the first percentage is (in my spreadsheet, it is cell E6):

=E$6<0

This is a simple rule but by not freezing the column (E) and freezing only the row (6), it can be applied to all the cells above. I can apply a red background color so that if any of the percentages are negative, the cells will highlight accordingly:

Market indicators imported into Excel from Google Sheets with negative values showing up in red.

For the next part of the dashboard, I will copy over the news stories that were also downloaded from Google Sheets. This time, I’m going to use the HYPERLINK function so that I can not just link to the title but also create a clickable link that will allow me to open the story should I want to open it in my default browser. The function itself is simple and involves just two arguments, one for the actual URL and another for what the text should show up. Since it’s shorter, I’m going with the title. After applying some formatting and copying all three sources, this is what my dashboard looks like:

Stock dashboard showing stock market indicators and the latest business news.

For the last part of the dashboard, I’m going to pull in the tables from the other data sources (top 100 gainers, losers, and upcoming IPOs). If these are on the Data tab, you can just cut and paste them onto the Dashboard tab. And for each one of the tables, I’m going to create a chart based on the symbol and the percent change.

To do this, select the Symbol column and the % Change columns. Then under the Insert tab in Excel, open up the charts and select Treemap. If you selected too many columns or didn’t specify which ones you wanted, you might get a different look. But if you only selected those two, you should see something like this:

Treemap chart in Excel.

Since the chart includes the symbols, the legend can be deleted. Also, I’m going to change the color scheme so that it goes from dark green to light green. This change can be made by clicking the Change Colors button next to the chart:

Changing the color scheme of a treemap chart.

To add the percentage to each of the boxes, right-click on one of the ticker symbols and click Format Labels. Then, check off the box for value so that the percentages will also show up next to the symbols:

Treemap chart in Excel showing ticker symbols and percent changes.

These steps can be repeated for the other charts. However, for the losers table, since the percentage change is negative, it needs to be flipped to positive first. To do that, that query needs to be edited. If you click on Queries & Connections section under the Data tab, you’ll see a list of all your queries. Click on the one that takes you to the top losers query. Right-click edit and Power Query will open up.

Once in Power Query, select the % Change column and under the Transform column at the top, click on the Standard drop down, which will show you all the different calculations you can apply:

Power Query menu showing standard calculation operators.

Click on Multiply and then for the value in the next box, enter -1. Pressing OK will then flip all the values to negatives.

Multiplying values in Power Query.

Now, you can create the same Treemap chart for this table. For the IPOScoop download, the field I’m going to use is Est. $ Volume. This query will also need to be edited in order to use that field since it is text. Although it is a bit more complex since this field contains text and dollar signs, there’s a relatively easy way to parse out what you need.

In Power Query, select the column, and under the Add Column tab, click on the Column From Examples button (choose the option for From Selection):

Column from Examples button in Power Query.

That will create a new column:

Power Query editor after adding a new column from examples.

In Column1, I can enter the value that I want Power Query to extract. If I just enter a few values to show what I want (in this case, I only need to enter 300), Power Query fills in the rest, figuring out what I am trying to do. It’s an easy way to parse data in Power Query.

The Power Query column from examples filling in the rest of my values.

After creating the new column, I can change the format from text to currency by clicking on the ‘abc’ letters in the title:

Changing a column's format in Power Query.

Now that I have the column created, I can remove the original one and load the data back into Excel and proceed with making a Treemap for this chart using the symbol and the newly created column.

The last thing I’m going to do is create a new column to show the change in volume to determine how much more (or less) trading there was for each stock on the day compared to the average. This will compare the average three-month volume with the current day’s volume. The one complication is that some of the values contain letters:

Stock trading volumes showing letters and numbers.

To convert these values, it’s important to first parse out the letters. If a value doesn’t contain a letter, then it is in thousands. I’m going to set everything to millions. So if the value doesn’t contain a letter, it will be multiplied by 0.000001 to convert it into a fraction of a million. And if it contains a ‘B’, it will multiply by a factor of 1,000. Otherwise, the value will remain as is. Here’s how the first part of the formula will look like, which involves determining the multiplication factor:

IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))

Since the letter is always at the end of the string, just using the RIGHT function (which looks at the right-most string) will suffice. This result needs to be multiplied by the remaining value. That value can be extracted by using the SUBSTITUTE function which will replace one value with another:

SUBSTITUTE([@Volume],”B”,””)

In the above formula, the value of B will be replaced with an empty string. This is the same as simply removing the value. To ensure that any ‘M’s are also removed, I will embed this formula within another one that will substitute out those values:

SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)

I multiply this by the first part of the formula, and my numerator is as follows:

(IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))*SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)

For the denominator, I’m going to use the exact same formula, except instead of the current volume, I’m going to use the field for the three-month average:

IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))*SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””))

The last part involves putting all this together:

=(IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)/(IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””)))-1

The -1 at the end is to put the change in a percentage of less than 100%.

Another step you might consider at this point to help identify these changes is to format these numbers so they are easier to read. You can use conditional formatting (color scales) to easily highlight the highs and lows. And if you want to format the percentages so that they show commas and negative percentages show up red, use the following in the custom number format:

#,##0%;[Red]#,##0%

The semi-colon before the [Red] separates out what the percentages should look like when they are positive (the part before the semi-colon) and what they should like when negative (the part that comes afterward). The [Red] text indicates the value should be in red text.

Here’s how this section looks as part of my dashboard:

Stock market dashboard showing top and bottom gainers.

And here’s a snapshot of the dashboard as a whole.

Stock market dashboard in Excel.

One thing to remember: if you want to update the queries and the dashboard, make sure you go under the Data tab and click the Refresh All button. Otherwise, your data may not be up to date.

Also, to prevent your tables from stretching out when updating the queries, select each one of them and under the Table Design tab, click the Properties button (under the External Table Data section), where you should see this:

External data properties in Excel.

Make sure the Adjust column width checkbox is unticked. This will prevent your columns from stretching out and disrupting your layout.


If you liked this post on Creating a Stock Market Dashboard 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.

H2Edashboards

Making Dashboards in Excel With Map and Gauge Charts

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

Preparing the data

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

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

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

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

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

=IF(L2=”major”,VLOOKUP(J2,Sheet1!A:B,2,FALSE),””)

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

Data set with the new category field.

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

Table button on the Insert tab.

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

Data set that has been converted to a table.

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

Table name set to tblData.

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

Creating the pivot tables

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

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

Median salary for the specified position

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

Pivot table showing the median value all detailed line items.

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

Median salary showing through a column chart.

Wages by percentile

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

  • A_PCT10
  • A_PCT25
  • A_MEDIAN
  • A_PCT75
  • A_PCT90

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

A 3-D column chart showing percentiles.

Median annual wage for the specified state based on job categories

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

Column chart showing median annual wages by job category.

A pie chart showing how many jobs there are by category

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

Pie chart showing jobs per 1000 jobs.

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

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

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

Table to set up a gauge chart.

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

Gauge chart table with values filled in.

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

Gauge chart colored in green with the black marker.

A map chart showing the median wages by state

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

Median annual wage by state.

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

Converting the pivot table into a regular table.

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

A map chart in Excel.

I now have all of my charts set up:

Multiple charts created in a dashboard.

What’s next is to set up the slicers.

Adding and linking the slicers

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

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

The Slicer button showing on the Filters section.

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

Multiple slicers showing on the pivot table.

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

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

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

Selecting the report connections button for a slicer.

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

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

Properties section of the slicer settings.

Now, the dashboard is ready to go!

Completed dashboard in Excel.

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

dashboardsgs

How to Make Dashboards in Google Sheets

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

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

Google Sheets data set.

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

  • Store
  • Salesperson
  • Product
  • Date

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

Pivot table editor in Google Sheets.

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

Show totals option in Google Sheets for pivot tables.

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

Creating a pivot date group in Google Sheets.

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

Different pivot date groups in Google Sheets.

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

Set of pivot tables in Google Sheets.

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

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

Pie chart showing sales by store in Google Sheets.

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

Default date range for chart in Google Sheets.

To this:

Expanded data range for chart in Google Sheets.

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

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

Bar chart showing sales by salesperson.

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

Column chart showing sales by product.

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

Line chart showing sales by month.

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

Scorecard chart showing total sales.

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

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

Adding a slicer to a Google Sheets pivot table.

Then, select the columns you want to filter by:

Selecting the column to use in a slicer.

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

Dashboard filtered by slicers.

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

Applying filters for slicers in Google Sheets.

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

guagechart6

How to Make a Gauge Chart in Excel

Whether you’re building a dashboard or just wanting another chart to add to your disposal, the gauge chart is always a popular choice. In this post, I’ll show you how you can create it painlessly and add it to your disposal next time you want to use a chart. It’s especially useful if you’re looking to compare actual vs forecast or need to track a completion percentage

As long as the version of Excel you’re using has a doughnut chart available, you’ll be able to follow these steps.

STEP 1: Set Up the Data for the Gauge Chart

First, you’ll want to set up two columns: one for the intervals and how big the pieces of the gauge chart will be.

For the intervals, normally, what I’ve seen is a 25/50/25 split, meaning the first and last portions are the same size, with the middle being the largest. The column needs to add up to 200, and so the last piece in this example would be 100.

For the second column, this is where you’ll determine where the marker shows up to track your progress or where your actuals come in at.

  • The first number should be 0
  • The second number the percentage; how far on the gauge chart you want the marker to be. This is where you’ll probably want to use a formula as this is the only number that should move on this chart.
  • The third number is how big the marker should be. In this example, I set it to five, and that’s about the highest I’d suggest it should be.
  • The last number is the remainder – here too, you’ll want the total for the column to add up to 200.

Here’s how my columns look right now:

gauge chart table

To move on to step two, create a chart using the Marker and Interval columns (include the labels).

STEP 2: Select the Two Columns and Create a Combo Chart

On the All Charts tab, at the very bottom, you’ll see an option for Combo. The Interval column should be a Doughnut chart while the Marker column should be a Pie chart. You’ll want them on two different axes, so make sure you have Secondary Axis ticked off as well.

excel combo charts

STEP 3: Format the Data Series on the Charts

Right click on the chart and select Format Data Series and select Angle of First Slice to 270 degrees. You’ll need to do this for both charts. To switch between charts, click on the Series Options button and select the other series.

series options selection excel

STEP 4: Change the Colors

Using the Series Options from above, make sure you have the Marker series selected. Here is where it gets a little tricky – you’ll need to select every part of the chart and make it blank except for the size of the slice – which you’ll probably want black.

If you have trouble moving across the different parts of the chart, use CTRL + left/right arrow keys to move along the sections. Your chart should now look something like this:

bar chart and doughnut excel

Now, switch over to the Interval series. Here you’ll do the same, except now you’ll be changing the bottom half of the doughnut so that it is blank, and everything else you can change to your liking. In my example, I’m going to go from red to light green to dark green. Here is what the chart looks like after those changes:

gauge chart basic

STEP 5: Additional Formatting (Optional)

You can do any additional formatting to the chart to make it look how you want. In my example, I added a bevel and some shadows to it to make it stand out a little more. I also shrunk the size of the slice to two:

gauge chart excel bevel

Save the Gauge Chart for Future Use

If you like your chart and think you’ll reuse it in the same type of layout, what you can do now is save it as a template. To do that, simply right click on the chart and select Save as Template

excel chart save template

Now, if you have the data in the same format you can go back to insert chart and look for the Templates folder which will now have the saved chart template:

excel chart template

That’s all there is to it! Please let me know if you run into any issues or require clarification on any of the steps above.


If you liked this post on How to Make a Gauge Chart 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.

Creating a Dynamic Dashboard in Excel




Do you want to create a dashboard that will update all of your charts simultaneously based on what filters your users select? Follow the steps below and you can create a professional-looking dashboard without having to use any complex formulas or programming.

Preparing the Data

 
If you have data in Excel that you want to use to create a dashboard, there are a couple of things you’ll want to do first to make sure everything goes well.
 
1. Ensure your data is free of error cells, as this will result in errors.
2. Have proper headings setup so that you know what you are referencing in your dashboard. 
3. Setup a named range for your data, ideally a dynamic one. This will make it easier to link everything to your data quickly and easily.

Making sure your data is clean and ready to go is the most important step, but unfortunately the one that is easily overlooked. After all, if you’re data is no good, your outputs won’t be either. Garbage in, garbage out.

You can follow along with my sample data, which can be downloaded here.

Setting up the Pivot Table

 
First up, let’s look at creating a Pivot Table (see this post for an into into pivot tables). 

I’ve assigned a name of Dataset1 to my data, and this is what I will referencing when I create a pivot table. With a named range, I don’t have to worry about selecting the data before clicking the create pivot table button, I can do it from anywhere.
 
 
Once I’ve got my pivot table ready to go, the next thing to do is to select my fields. The fields that I have to choose from in my data set include: date, store, salesperson, and product. 

For my first pivot table I’ll want to look at the date because I want to start from a high level and work my way down. No sense in starting from the detail when I don’t have any context yet.
 
For the rows, I’ll select Dates, and in the values I’ll select Total Sales. My table now looks like this:
 
 
Ultimately, it doesn’t really matter if you want to select columns or rows for this as it’s going to be in a chart anyway. In Excel 2016, my dates were automatically grouped into months, which is what I wanted. If you want to change the grouping, simply right-click on the dates values and click Group
 
 
Then select the how you want the dates to be grouped
 
 
 
Next, I want to clean up my formatting so that my total sales have commas and so that the data is easier to read. To do this, I’ll right-click on that field and choose Value Field Settings
 
 
 
Then click on Number Format and then select Accounting.
 
 
 

Creating a Pivot Chart

 

Now it’s time to convert this into a chart. Select the Insert tab and in this example I’m going to select a regular Column Chart

 
 
What you have now is a simple column chart that shows your sales by month. The only thing special about a pivot chart is that you’re able to filter it based on your pivot table.
 
You’ll notice there are drop downs on your pivot chart that you select to modify your data. I can select only certain months to look at. 
 
 
The amount of options you have on here depends on how many fields you added to your pivot table. Whether you make the changes on your pivot chart or pivot table doesn’t matter, the chart will update all the same.
 
However, for the purposes of a dashboard, I’m going to get rid of these ugly filter buttons on my chart. To do this, click on your chart and click the button for the Field Buttons and this will remove the buttons. 
 
 
 
Now that the pivot chart is ready to go, you can now go about and format it how you like. 

Formatting and Tidying Up

 
Once you’re done formatting the chart, move it on to another tab. Because you’re creating a dashboard you probably won’t want your original pivot table to show up along with it. For this reason I usually move all the charts onto a separate tab.

Lastly, you’ll probably want to format your chart so that it is more appealing to your users.

Rinse and Repeat

 
For a dashboard, you’ll want to create multiple charts and so you’ll likely want to create another pivot chart following the same steps as above. In the next chart you can focus on a more detailed analysis, such as sales by store or rep.

In my example, I added three more charts in total and decided to mix it up by using a column chart, a pie chart, a stacked chart, and a bar chart. Mixing it up a little will keep your dashboard more interesting for your users.

Adding Slicers

 

Once you’ve added your charts, the next thing you’ll want to do is add slicers. Note that slicers are new to Excel 2010 and if you have an older version you will not be able to utilize these features.

For an overview on slicers, refer to this post.

The real advantage of using slicers is that they can be linked to multiple pivot tables and pivot charts. This allows you to now turn your dashboard into a dynamic one that will update as the user selects options from the slicers.

Once you’ve inserted slicers, you want to make sure that each slicer is connected to every pivot table. To do this, simply right-click on the slicer and select Report Connections.

 
On the next screen you can see all the pivot tables and charts that the slicer is connected to. Ensure that you have ticked off all the ones you want it connected to and then click on OK.
 
 

By doing this your slicer will now update all those charts and tables automatically. Repeat this step for every slicer you create.

Your dashboard is now ready to use and anyone that makes a selection on one of your slicers will see all the charts update immediately.