charts Archives - HowtoExcel.net

How to Create a Map Chart in Excel Showing the Most Expensive Zip Codes in Each U.S. State

April 15, 2025

by howtoexcel charts excel practice real world examples

Map charts can help display a lot of data, showing you not only the largest values relative to other values. Below, I’m going to walk you through the steps of creating a map chart using real-world data, which looks similar to the one below:

This is a chart I found on realtyhop.cm that I thought would be good to create from scratch. If you want to follow along, you can download the data from their website.

Here is a snippet of what the data looks like, loaded in Excel:

Table showing Median List prices of homes in the U.S.

You’re not able to create a map chart with a pivot table, so instead, I’ll create the data that is necessary for the map chart. For starters, I’ll use in the UNIQUE function to grab a list of the unique values from the table:

=UNIQUE(Table1[State])

My table is called Table1, with the State field referring to the specific states. This produces a list of unique state values:

A list of U.S. states in an Excel spreadsheet.

Next, I’ll pull in the largest price in the table based on state, using the MAXIFS function:

=MAXIFS(Table1[Median List Price],Table1[State],H2)

Where H2 is the state in my list. After doing that, I also need to lookup the related zip code. For this, I can use the INDEX and MATCH functions to look up multiple criteria:

=INDEX(Table1[Zip Code],MATCH(H2&I2,Table1[State]&Table1[Median List Price],0),1)

Where H2 is the state value and I2 is the median list price. I now have a table that following extracted values:

A table in Excel showing the highest median list price for a home in the U.S. by zip code and state.

In order for the labels to display correctly on the chart, I also need to convert the values so that they show K for thousands and M for millions. The initial formula look as follows:

=IF(I2>=1000000,"$"&ROUND(I2/1000000,1)&"M","$"&ROUND(I2/1000,0)&"K")

What this formula does is checks if the value is at least $1 million, and if it is, then it adds an “M” to the end and converts it to a decimal. If it’s in thousands, then it divides it by 1,000 and adds a “K” to the end.

Here’s what the output looks thus far:

Excel table with list prices converted into K for thousands and M for millions.

The ROUND function is used to limit the decimal places.

Since the labels also incorporate the zip code, that will also need to be added to the label. And since that value needs to be forced onto a separate line, I’ll need to use character code 10 to do that. I need to apply text wrapping to ensure it is displayed correctly in the table. The last thing I’ll do is move the median list price to the end and the label next to the state, making it easy to separate the values.

Excel table that is ready for use in a map chart.

Now with the table setup, the next step is to create a map chart. From the Charts section on the Insert tab, there is an option to select Maps and there is an option for a Filled Map:

To ensure the data is selected correctly, right-click on the chart once it is setup and click on Select Data. Under the Legend Entries, ensure that just the column related to the Median List Price is selected. And for the Horizontal Category, just the first two columns for State and Label are selected. If you’ve set it up correctly, you should see something like this:

Next, it’s time to add the labels. Right-click anywhere on the chart and select Add Data Labels. Then right-click on any of the labels and select Format Data Labels. From here, you want to select the Category Name option — this pertains to the label column that was created earlier, which combines the value along with the zip code. Now the chart looks as follows:

Map chart with labels displaying values.

Now, let’s adjust the color scheme so that it is red. Right-click on the data series again, then open up a section under the series option which says Series Color:

Adjusting the series color for a map chart in Excel.

You can adjust the color based on the minimum, midpoint, and maximum values, and the gradient will update accordingly.

This now gives me a map chart that is highlighted in red:

All that’s left at this point is to apply a title, and this can be done with the use of a text box. And after adding vertical red line next to the title, we get a fairly similar chart to the one initially shown:

Map chart in Excel highlighted in red with a title.

If you liked this post on How to Create a Map Chart in Excel Showing the Most Expensive Zip Codes in Each U.S. State, 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.

How to Create Dynamic Min-Max Range Charts in Excel

March 18, 2025

by howtoexcel charts

A good chart in Excel can display a lot of useful information in one visual. In this example, I’m going to show you how to display minimum and maximum values, as well as the range between them. This can be helpful in reviewing products to identify not only how cheap or expensive they may be, but also how much the price has fluctuated in the past. Narrow ranges may imply not a lot of movement, whereas wide ones could mean that there’s a lot of variation, and opportunity to buy that product at a lower price, assuming that past trends repeat.

For this example, I’m going to use some tech products and use sample price ranges that might be applicable to them:

A table of tech products, showing minimum and maximum values.

Calculating the range between min and max values

Determining the range can help you analyze just how large the delta is between two values, particularly the high and the low. To calculate it, simply take the maximum value and subtract the minimum. Assuming my first max price is in C2 and the min price is in B2, here’s how the formula will look:

=C2-B2

This value is entered into the next column, resulting in the following column added to this table:

Table in excel showing a list of tech products, displaying their minimum and maximum values, along with their ranges.

You can quickly spot the product with the largest range — the smartwatch. The high-priced item can have significant fluctuations depending on the quality and features it offers. The more modestly priced flash drive, however, has a much smaller range and less room for variance.

Plotting the values on a bar chart

The next step involves putting all this data on a chart. In particular, you’ll want to create a stacked bar chart. When selecting your data ensure you select the first option on the stacked bar section, which doesn’t contain a large legend. In this example, there should only be three different series: min, max, and range.

Modify the bar chart hierarchy

In order to ensure that your bar chart is displaying correctly and stacking properly, you’ll want to modify the way the different series are ordered. Right-click on the chart and click Select Data. By default, it will arrange the data in the order of the columns. However, I want to ensure that the range comes between the min and max values. That way, the min value is added first, then the range is stacked, followed by the max value. By using the arrow keys under the Legend Entries, you can adjust the order of the series. This is how it should look:

Data in a bar chart with the legend first stacking the minimum value, followed by the range, and then the maximum price.,

Adjusting the fill colors

By changing the hierarchy, you can ensure the chart is stacked in the correct order. But you’ll still want the min and max bars to not be visible. The point is to plot those values but to only show the range. To do this, right-click on any bar chart and select Format Data Series.

Select the fill bucket, and ensure that the series you’ve selected is either the min or the max. This can be done click clicking on the down arrow next to Series Options:

Selecting the correct series using the series options drop-down menu in Excel.

This can be an easy way to select the correct data series. Now, with the correct field select, you can go back to the fill settings and chose the option for No fill.

Selecting the no fill option in Excel for a bar chart.

Now, repeat these steps for the other series. In my example, that’s going to be the Min price. And now, after selecting no fill, I’m only left with a chart that shows the range:

Chart in excel showing a range of values.

Adding min and max labels to the chart

What you may also want to do is add labels to the chart, to display the maximum and minimum values. To do this, right-click on either one of the bars that doesn’t pertain to the range, and select Add Data Labels. You’ll see a number displayed, and if you right-click on it, you can select the option to Format Data Labels.

Formatting data labels on an Excel chart.

You can adjust the label to show it in the center, inside end, or inside base. Inside base will put at the start of the bar value, while inside end will display it at the end. In my example, I’m selecting inside end for the minimum value, so that its shows towards the right.

I’m also going to right-click on the label and select Change Data Label Shape and select a rounded rectangle, to ensure the value is easy to see.

Changing the data label shapes on a chart in Excel.

I’ll repeat the steps for the maximum value label, and select inside base. And with the rounded rectangle added, this is how my chart looks after these changes:

Chart in excel displaying min, max, and range values.

Formatting changes and final adjustments

The only thing left to do at this stage is to apply any final changes. Since I have the min and max values listed, I’m going to opt to remove the axis at the bottom, and add only horizontal gridlines. I can also remove the legend since the min and max values should be self explanatory. I’ll also change the bar chart color to a light blue color.

If you like this post on How to Create Dynamic Min-Max Range Charts in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

How to Create Excel Charts to Visualize Variances in Stock Performance

January 22, 2025

by howtoexcel charts stocks

If you want to compare and contrast between two sets of values, a chart can help you accomplish that. In this post, I’m going to show you how you can compare multiple investment returns, and using a chart to not only map them but to also display the variances. For this example, I’m going to compare the historical returns of the S&P 500 against the S&P TSX 60.

Step 1: Setting up the table

The first thing to do is to plot the values into a table format, making it easy to compare the differences. Below, I have multiple columns for the year, S&P 500 returns, and the S&P TSX 60 returns.

Table in Excel comparing investment returns.

Step 2: Calculating the differences and starting point values

The goal of this chart is going to be to not just plot the values of these returns but to also show the gap between them. This can be a little tricky since the value scan be both positive and negative. It’s important to determine where the starting point for the column chart needs to be, and where it finishes.

The first calculated column I’m going to create is for the starting point. If the two returns are both negative, then it will take the largest value (i.e. smallest negative number). Otherwise, it will take the smallest positive value. The formula for the first set of values in row 2 looks as follows:

=IF(AND(B2<0,C2<0),MAX(B2:C2),MIN(B2:C2))

Next, it’s necessary to calculate the gap for when there are both positive and negative returns, and when a crossover needs to happen to get to 0. This column, called crossover, will check to see if the returns are both positive or both negative. And if they are, then it will remain 0 since no crossover is needed. Otherwise, it will return the starting value. The formula for this column is as follows:

=IF((B2*C2)>0,0,D2)

By multiplying the values for both returns, it checks to see if there is a mix of positive values. If there is, then the value will be negative. If they aren’t, then it will return a 0 value.

Lastly, there is a column for the final difference field. First, it checks whether there is a value in the crossover field. If there is, it takes the maximum value in column B or C, which will be the other part of the gap to be filled in . If there isn’t a crossover value, then it looks at the starting value. If the starting value is positive, then it takes the largest value and subtracts the positive one. Otherwise, if the starting value is negative, then it takes the lowest value and subtracts the largest. This is what the formula looks like:

=IF(E2<>0,MAX(B2:C2),IF(D2>0,MAX(B2:C2)-MIN(B2:C2),MIN(B2:C2)-MAX(B2:C2)))

And here is the table, with the values calculated:

Table comparing investment returns in Excel.

Step 3: Creating the chart

Now that the data is setup, the next step is to plot the values on a chart. This requires the use of a combination chart. To create it, make sure a cell on the data set is selected, click on the Insert tab and select the button to pop out more Charts. Go to the All Charts tab and select Combo. The S&P 500 and S&P TSX 60 fields, select the option for Line with Markers and for the others, choose Stacked Column.

Initially, the chart is likely going to look incorrect and may include the date field. To fix this, right-click on the chart and click on Select Data. Remove the Year field if it is there. For the other fields, adjust the Horizontal Axis labels to ensure they are reflecting the year:

Modifying the data source for a chart in Excel.

The chart still has a lot to fix in terms of formatting:

Chart in Excel showing a range of values and variances.

For the line charts, right-click on them and select Format Data Series. Since only the data point is necessary, I will go under the fill bucket tab and under the Line category, select No Line.

Next, the the starting value column, format the data series and for the fill bucket, set the Fill option to No Fill. Then, change the fill color for the difference and crossover columns so that they are the same color. I’ll use grey in this example.

To ensure the column charts stack properly, you’ll also want to modify the order of the series. Right-click to select the data, and ensure that Crossover field comes before the Starting field, followed by the Difference field. Here’s how the chart looks at this stage:

To clean up the chart a bit, I will move the labels to the bottom. This can be done by right-clicking on the years and selecting Format Axis. Under the Labels section, set the Label Position to Low. I’ll also delete the legend labels for the Crossover, Starting, and Difference fields. The last change is to set a chart title. This is what the final result looks like:

Excel chart comparing the returns of the S&P 500 versus the S&P TSX 60.

If you like this post on How to Create a Chart to Show a Range of Differences, 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.

Add Horizontal Lines to Stock Charts to Identify Min and Max Values in Excel and Google Sheets

November 19, 2024

by howtoexcel charts google sheets stocks

If you’re pulling in stock prices into your spreadsheet, you can easily plot those values on a chart. And one way to help visualize the data is to add horizontal lines to help you identify where the maximum and minimum values are. I’ll go over how this can be done in both Excel and Google Sheets.

Pulling in the historical stock prices in Excel and Google Sheets

In Excel, you can use the STOCKHISTORY function to pull in historical stock prices. And in Google Sheets, there’s the GOOGLEFINANCE function. Both are fairly straightforward functions which can extract stock prices going back days, months, and even years.

Below, I’ve pulled Nvidia’s stock price history from Jan 1, 2024 through to Oct 31, 2024, in Excel:

And here is the price history in Google Sheets:

NVDA stock price history in Google Sheets.

Calculating the highs and lows

In Google Sheets, you can pull in a stock’s 52-week high and low from right within the GOOGLEFINANCE function. But in this example, I’m going to calculate the minimum and maximum values based on the range that has been downloaded. This will make the chart more dynamic, allowing you to have these values updated based on your range.

In both Excel and Google Sheets, I’ll setup columns for HIGH and LOW. I’ll use the MAX function to get the highest value and the MIN function to get the lowest value. The only argument needed is the column which contains the closing price. The same value needs to be repeated in both of these columns to ensure the line is horizontal.

The formula is exactly the same whether you’re using Excel or Google Sheets. What’s important, however, is to ensure the values are the same all the way down; you’ll want to copy it all the way to the bottom. Here’s how it looks in Excel:

NVDA stock price history in Excel showing highs and lows for a period.

Plotting the values on a chart

Next, with the highs and lows added, it’s just a matter of creating a line chart which shows these values. The default formatting in Excel already does this effectively for me, displaying the high and low ranges:

High and low values plotted on a chart in Excel.

At this stage, it’s just a matter of any additional formatting you may wish to do, such as changing the line colors. I also prefer to make these dotted lines, and this can be done by changing the dash type. Here’s what my finished chart looks like in Excel:

Excel stock chart showing maximum and minimum values.

To format the individual lines, right-click to Format Data Series, where you can then change the color and the dash type. The changes I made above are to change the color to black and the lines to a dash. I have also added vertical gridlines to the chart by going to the Chart Design tab and selecting Add Chart Element and Gridlines and then clicking Primary Major Vertical.

On Google Sheets, the process is largely the same. The main difference is that you need to access the edit chart menu and under the Customize tab, select your options for gridlines, color, and dash type for an individual data series.

Google Sheets stock chart showing maximum and minimum values.

If you like this post on Add Horizontal Lines to Stock Charts to Identify Min and Max Values in Excel and 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.

How to Create a Secondary Axis in Google Sheets

October 21, 2024

by howtoexcel charts google sheets

If you want to create a chart in Google Sheets that displays different kinds of data, you may benefit from using a secondary axis. If you just use a single axis, that may not be ideal as depending on the scale, your data may not display correctly if the values are either too large or too small for a series.

In the chart below, I have data which shows actual sales numbers along with year-over-year growth rates. They are plotted with just a single axis being used. As you can see, it becomes difficult to see the growth rate because the values are so low.

Chart showing Netflix's sales data and growth rate.

The right line for the growth rate is barely visible at the bottom. Since the growth rate is in percentages, the values will be fairly small (less than one), hence they are only slightly visible. To fix this, I need to adjust the axis the growth rate shows on. Here’s how to put that series on a separate axis:

Select the chart, click on the three dots in the right-hand side, and select Edit
Click on the Customize tab and go under Series
Select the series you want to put onto another axis. In my example, it is the growth rate.
At the bottom, change the Axis selection from Left axis to Right axis

Changing a series axis in Google Sheets.

That’s it, now the growth rate shows more clearly and I can see the related axis on the right-hand side of the chart.

Chart showing Netflix's sales data and growth rate with a second axis.

You may also want to display the series differently, such as showing by dashes rather than a straight line, but that is an optional step. To make changes to an individual series, simply select the series as you did when changing the axis, and make changes in that same screen. In the chart below, I’ve simply changed the line so that is shows as a dash:

Chart showing Netflix's sales data and growth rate with a second axis and a dashed line.

For another example of how to add a secondary axis, check out the below video:

If you like this post on How to Create a Secondary Axis 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.

How to Track Income and Expense in a Single Chart

August 27, 2024

by howtoexcel budgets charts

Do you want to create a budget which tracks income and expenses on just one chart? There’s an easy way you can combine them where you can show positive and negative values in a single column. Suppose we have the following income and expenses over a 14-day period:

You might be tempted to plot these values on a simple column chart like this:

Column chart showing income and expenses.

This, however, means you’re using up two columns for each day. One for income, and one for expenses. While it is effective, what you can also do is combine the income and expense amounts into a single column. The key is to change the chart type and instead of using the default Clustered Column Chart, you select the option for a Stacked Chart. By doing this, you’ll now have both values on a single column:

Stacked column chart showing income and expenses.

This is still not optimal, however, as now we are just adding the income and expense together. Even though they are color coordinated, there is a better way to display this. Ideally, we will show expenses being a negative outflow on a given day while income will be positive. To fix this, let’s flip the expenses so that they are negative. But rather than doing this manually, you can do this with just a few steps.

To flip values from positives to negatives in Excel, do the following:

1. Enter a value of -1 into a cell.

2. Copy that cell.

3. Select the range which contains the values you want to flip from positive to negative.

4. With those cells selected, right-click and select Paste Special and select Multiply

Doing this will flip your values negative by multiplying all the values by a factor of -1. You can also follow these steps if you want to flip negative values into positives.

After updating the formatting, my table now looks like this:

Table of income and expenses with expenses showing negative values.

And now my chart has also updated to show negatives beneath the income.

Stacked column chart showing income and expenses with expenses showing below income.

This ensures I’m not taking up extra space with an extra column and it still makes it easy to compare the outflows versus the inflows.

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.

Create a Heat Map in Google Sheets

July 1, 2024

by howtoexcel charts google sheets

Heat maps are visual representations of data where individual values are represented by colors. They are particularly useful for identifying patterns, trends, and outliers in large data sets at a glance. By using a spectrum of colors, typically ranging from cool (blue) to warm (red), heat maps make it easy to see which values are higher or lower, helping users quickly understand the data’s distribution and key insights.

Why heat maps are useful

Visual Clarity: Heat maps turn complex data sets into easy-to-understand visual formats.
Quick Analysis: They allow for the rapid identification of trends, patterns, and outliers.
Enhanced Decision Making: By highlighting critical data points, heat maps aid in making informed business decisions.
Comparative Insights: They facilitate the comparison of different data points within a set.

How to create heat maps in Google Sheets

Follow these steps to create a heat map in Google Sheets:

Step 1: Prepare Your Data

Ensure your data is organized in a clear and structured manner. Each column should represent a different variable, and each row should represent a different observation or data point. You should give Google Sheets enough data so that it can determine the location for your data points. In the example below, I have U.S. states listed in column A and values in column B. Since I’ve labeled column A as ‘State’ that gives Google Sheets sufficient information to map the data points. If I had a list of countries, then I would label the header as ‘Country.’

Step 2: Insert the chart

Select any data point on your table and on the Insert menu, select Chart. Google Sheets will create a default chart but you can change it by selecting the Chart Type and then selecting Geo Chart from the Map section.

Step 3: Select the correct map

When the map chart is created, it may not automatically detect the correct area. In my example, it selects the entire world.

Map chart in Google Sheets showing the entire world.

The chart looks incorrect as nothing is filled in, but this is because I’m at too high of a level to see the values. I need to adjust my chart to focus just on the United States. To fix this, edit the chart and under the Customize tab, select the Geo settings and change the region to United States.

Selecting the region settings in Google Sheets.

You can adjust the range per your individual data set. But in this example, since my data has U.S. states only, then I need to select the United States. And once I do so, now my chart is filled in:

Map chart showing values for the United States.

Step 4: Adjust the color scales

By default, the chart shows green and red colors for high and low values, respectively. I can customize this in the Geo section as well. You can modify this so that blue colors are for the low values, green for the maximum values, and yellow for the mid-range values:

Modifying the colors on the heat map in Google Sheets.

Step 5: Modify other chart settings

The last step is, as with any other chart, modifying the font, background color, border color, and any other settings for the chart. These can also be changed in the Customize section of the chart settings.

Modifying the chart settings in Google Sheets.

Once you’re done, the heat map chart is ready to go:

If you like this post on How to Create a Heat Map 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.

Creating a Map Chart in Excel and Google Sheets

June 4, 2024

by howtoexcel charts google sheets

If you have sales data organized by country, you can create map charts in both Excel and Google sheets. These charts can make it easy to visualize sales and identify patterns and trends. Below, I will compare the different ways to create map charts in Excel and Google Sheets, and highlight any similarities and differences.

For this example, I’m going to use a data set which just includes two fields, one for the country and one for the sales data.

Creating a map chart in Excel

To create a map chart in Excel, all you need to do is click anywhere on your data set and insert a chart. Excel will likely automatically detect the data and recommend a Filled Map as an option. But if it doesn’t, you can select a Map option under the All Charts tab:

You’ll now have a chart that displays the values based on a color scale. In this example, the larger values are in a darker shade of blue whereas the smaller values are in light blue. And if there is no data, the countries are filled in grey.

As with other Excel charts, you can specify a different color scheme and chart layout. In the chart below, I’ve used a theme which has a black background.

A map chart in Excel with a black background.

By using the dark theme, it makes it easier to focus on areas where there is data, as those countries stand out more prominently. You can also manually adjust the color scheme for the chart by formatting the data series. To do so, right-click on the chart, select Format Data Series and under the option for Series Color, you can specify a 3-color range. And you can adjust what the minimum, midpoint, and maximum values should look like. This logic is similar to how you might set up conditional formatting rules in Excel.

Formatting a data series in a map chart in Excel.

With more colors, readers can now see more variation in visualization.

Map chart showing three different colors.

Creating a map chart in Google Sheets

To create a map chart in Google Sheets, the process is comparable to Excel’s. Simply select a cell on your data set and when you create a chart, select the option for Geo Chart under the Map section

The result is similar to Excel, with the countries being shaded based on their values:

Under the Customize section of the chart settings, you can specify what the max, min, mid values should look like. In addition, you can specify how countries without values should be displayed.

In Google Sheets, you also have a bit more flexibility in how to zoom in on data. In the region drop down, you can specify whether you want to look at the entire world, or narrow in on specific continents.

Customizing a map chart in Google Sheets.

If I select North America, then I will only get a view of that continent, even if there is data for other countries.

A map chart in Google Sheets focusing on North America.

Google Sheets also allows you to create a Geo chart with markers, which is a bit similar but the difference is the countries are not filled in. Instead, there are circles representing the values.

A geo chart in Google Sheets using markers.

With this type of chart, you can add another field to track the size of the circles. In the following data table, I also have a field for the average sale price.

Table showing sales and average sale price by country.

The average sale prices are highest in North America and smallest in Asia, and that is visually represented in the chart below. In addition to having the colors indicating the overall sales values, I can compare the average prices by looking at the size of the circles.

Overall, creating map charts is easy whether you’re making them in Excel or Google Sheets. In Google Sheets, however, there is some added flexibility, and the ability to use markers allows you to utilize an additional field in map charts.

If you like this post on Creating Map Charts in Excel and 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.

Automatically Update Your Excel Chart With New Data Using Power Query

May 6, 2024

by howtoexcel charts power query

When you add data to an Excel spreadsheet, with regular charts, you often need to trigger a refresh to make sure that your chart reflects the latest data. But, by using Power Query, you can automate that process without the need for macros. In this post, I’ll walk you through the process of getting your data into Power Query, and how to set it up so that your charts will update automatically.

Step 1: Importing Data into Excel with Power Query

The first step involves pulling data into Power Query. This can be from various sources like databases, web pages, or local files. In this example, I’m just going to use data that’s on another sheet, but it can also be from another workbook. Here is an excerpt of some sales data since the start of the year:

Table showing daily sales data in Excel.

To get this into Power Query, I just need to click on any of the cells in the table and then under the Data tab, under the Get & Transform Data section, select the From Table/Range option. Now my data is in Power Query.

But before loading the data back into Excel, I’m also going to group the totals by week. To do this, I’ll click on the Group By option in the Home tab in Power Query. I’ll create a column name called Weekly Total and sum the Sales Amount:

Now I have sales broken down by week which I can import back into Excel.

Sales data in Power Query broken down by weeks.

At this stage, I’ll click on Close & Load. Now I have another table of the data in Excel, this time, linked to Power Query and broken out by week:

Sales data from Power Query broken down by week.

Step 2: Creating a Chart from Imported Data

Next, let’s go ahead and create a chart to show these daily sales totals. For this example, I’ll use a simple column chart showing the weekly sales. To do this, click on your data set anywhere and on the Insert tab, select the option for a Column Chart. After applying some formatting, this is what my chart looks like:

Chart showing weekly sales data in Excel.

Step 3: Setting Up Automatic Refresh

With my chart and table now setup, I can go ahead and set the automatic refresh. When a query is created in Power Query, you will see it under the Queries & Connections pane. To show this pane, go under the Data tab and click on Queries & Connections. Then, right-click on the query and select Properties. You’ll now see the following options:

As you can see, there is an option to specify how often you want to refresh the data. You can have it refresh when you click on the Refresh All button but you can also set it to refresh when the file first opens. And you can even specify it to refresh every few minutes. I can even set it to refresh every minute:

Query properties in Excel set to refresh every minute.

Depending on how often your data may change, you may want to adjust this accordingly. But one thing to keep in mind is that whatever changes you made in Power Query, refreshing the query will trigger all those steps, which can make it time consuming if there are many steps for the query to go through.

But by setting up a rule to refresh every x number of minutes, you can have control over how often your data updates. And since it’s linked to a chart, your chart will also automatically update.

If you like this post on How to Automatically Update Your Excel Chart With New Data Using Power Query 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.

How to Add a Second Y-Axis in Microsoft Excel

April 16, 2024

by howtoexcel charts

Using a second Y-axis in Microsoft Excel can significantly enhance your data presentation, particularly when dealing with variables of differing scales. This guide will walk you through the steps to add a second Y-axis in Excel, helping you display your data more clearly and effectively.

Why Use a Second Y-Axis?

1. Scale Variation: When charting different types of data together, one variable might be significantly lower or higher in magnitude than the other. Using two Y-axes allows each variable to be scaled according to its own range, making the chart easier to read and interpret.

An Excel chart showing website visitors versus page views.

The above chart shows website visitors, which are measured in thousands, versus page views, which are in tens of thousands. By using a second axis, it makes it possible plot both of the values on the chart without making one series look far smaller than the other.

2. Different Units: If your data variables are measured in different units (e.g. dollars versus percentages), a second Y-axis can help represent each in an appropriate context without confusing the reader. A common example may be where you want to plot the revenue on one axis and the year-over-year growth rate on a separate axis, as is the case in the chart below.

An Excel chart showing monthly revenue and a year-over-year growth rate.

3. Clarity and Emphasis: Using a second Y-axis can help emphasize the relationship between two different variables, making your analysis clearer and more impactful. In the following example, it’s easy to see the relationship between a rising customer satisfaction score and higher product sales.

An Excel chart showing product sales versus customer satisfaction.

Step-by-Step Process to Add a Second Y-Axis

Here’s how you can add a second y-axis to your charts.

Step 1: Prepare Your Data

Organize your data in Excel with your independent variable (e.g., time, dates, categories) in one column and the dependent variables in adjacent columns.

Step 2: Create a Combo Chart

Highlight your data range.
Go to the Insert tab.
Click to expand the Charts section and select the Combo chart from the bottom.

Step 3: Add the Secondary Axis

When selecting your chart types, check off the option for a Secondary Axis for at least one of the series.
While it’s not necessary to use a different chart type, setting it up that way can be helpful to distinguish the values more easily from one another.

Step 4: Customize the Secondary Axis

Once your chart is loaded into Excel, click on the secondary axis (now visible on the right) to select it.
Right-click and choose Format Axis to open the Axis Options pane.
Adjust scale options such as minimum and maximum values, tick mark spacing, and number formats to better align with the secondary data series.
You can also add axis titles by selecting the chart and clicking the Add Chart Element option from the Ribbon (under the Chart Design tab). Then, select Axis Titles and either Secondary Vertical for the secondary axis or Primary Vertical for the primary axis.

Adding a second Y-axis in Excel can turn a confusing overlap of data into a clear and insightful visualization, perfect for presentations or in-depth analysis. By following these steps, you can master the use of dual Y-axes in your charts, making your reports and presentations more professional and effective.

If you like this post on How to Add a Second Y-Axis in Microsoft Excel please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.