charts Archives - HowtoExcel.net

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.

Create Multiple Line Charts in Excel Showing Actuals and Growth Rates

April 1, 2024

by howtoexcel charts

A line chart in Excel can be a great way to display a time series in Excel. But did you know that you can show a series’ actual values along with the year-over-year growth rate, or change? By using a second axis and adjusting the scales, I’ll show you how you can create this effect in Excel.

Setting up the data

For this example, I’m going to use the personal savings rate as an example, specifically, to show what it was from 2017 until the end of 2019, and how much it changed. The raw data shows me the savings rate per month:

Personal savings rate by month, shown in Excel.

To calculate the year-over-year change, I’ll add a formula to determine the difference. In this case, I’m going to show the change in percentage points. To do this, all that’s necessary is to take the current month’s savings rate and deduct the savings rate from the same period last year.

Personal savings rate by month along with the percentage change, shown in Excel.

Plotting the data into a line chart

Next, after selecting one of the data points, I can go into the Insert tab to select a chart. To balance both of these line charts, it’s important to put each series on a different axis. When selecting a chart, got to the Combo section and have line charts selected for each series, but put one on a Secondary Axis.

Right now, the line charts are still a bit too close together:

To create more a buffer, the next thing I’ll do is adjust the axis scales. This can be done by right-clicking on an axis and selecting the option to Format Axis. The key is to stretch one axis so that it doesn’t overlap with the other one. For the orange line chart (showing change), I can adjust the scale so its minimum value is -10 and the maximum is +30. This pushes the line chart down to within a fairly narrow range.

Two line charts showing on two different axis.

Next, I’ll add some data labels to show the values. I’ll also adjust the savings rate so it starts at the same point as the line showing the year-over-year change. Now, it’s easier to see both the actual savings rate and the change, displaying both the actual and the year-over-year change.

Two data series in Excel plotted on line charts showing the growth rate and the actuals.

If you like this post on how to Create Multiple Line Charts in Excel Showing Actuals and Growth Rate 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 Progress Charts in Excel

March 19, 2024

by howtoexcel charts financial analysis

Whether you’re managing projects or working towards a goal, visualizing progress is important to ensure you’re on track for meeting your target. And by using charts in Excel, you can easily track your progress, making it easy for you to view and share it with your stakeholders. This post will walk you through the steps to create insightful progress charts in Excel.

Using a bar chart to track progress

One example where you might want to track progress is if you’re tracking how much progress a sales rep may be making with a customer account. You may have various stages in the process, such as making an initial contact, obtaining an in-person interview with management, all the way to getting a signed and approved contract. Rather than having someone verbally track this progress for you, you can use a combination of checkboxes and charts to help visualize progress.

With the help of Excel’s checkboxes, we can create a table which looks like this, making it easy to simply tick off boxes to indicate progress by prospect:

Table showing sales rep progress by prospect.

This table, while it’s helpful, isn’t easy to visualize the progress. Using a COUNTIF function, we can count the number of times a checkbox is set to TRUE. With 6 possible values, if there are 6 checkboxes ticked off, that tells us the sales rep has fully completed all the stages in the funnel and the deal is now closed. By then dividing this number by 6, we can numerically display the percentage of completion:

Table showing sales rep progress by prospect along with a percentage of completion.

Now, this data can be put into a chart, which displays those percentages.

Progress chart showing a sales funnel's percentage of completion.

Another way to display this progress is by adding another field, simply to show the total number of stages. Then, we can plot on a chart the # of checkboxes that have been ticked off, along with the number of total stages.

Progress chart showing overlapping bar charts to indicate the level of progress.

To make this work, make sure you do the following:

Select both the series for the # of checkboxes ticked off, and total stages. When selecting the data, make sure that the the field which contains the total is on top, in the legend entries.
Format the data series and ensure that series overlap is set to 100%. This way the bar charts will completely overlap with one another.

Creating a circle progress chart

Bar charts can be effective when you want to track multiple projects and tasks at once. But if you want to track just one project individually, or your overall total progress, then a circle chart may be more effective for that purpose.

Going back to the previous example, let’s suppose we want to track the progress of one prospect at a time. Company A is at 83%. Here’s how we could show that on a circle chart. We can display the information in the following way, to show what the progress is, in both raw numbers and as a percentage

Excel table showing progress and remaining percent.

By setting it up this way, we can now create the following donut chart in Excel:

Donut chart in Excel showing the level of progress.

With the percent %, I used that as a data label to put within the middle of this chart.

Using a donut chart, you can easily set up progress for one particular project. But it doesn’t have to be for just one particular item; this can also be part of a greater set of key performance indicators.

If you like this post on How to Create Progress 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 Effective Comparison Charts in Excel

December 5, 2023

by howtoexcel analysis charts

Comparison charts are invaluable tools in Excel, widely used across business, education, and research to visually represent data. These charts not only simplify complex information but also highlight key trends and comparisons. A comparison chart in Excel is a visual representation that allows users to compare different items or datasets. These charts are crucial when you need to show differences or similarities between values, track changes over time, or illustrate part-to-whole relationships.

In this article, we’ll compare a company’s sales, expenses, and overall profits by year. Here is some sample data:

Excel table showing sales, expenses, and profit by year.

Types of Comparison Charts in Excel

There are many types of charts you can use in Excel to compare data. Here are a few examples of common charts you might use when comparing data, and how they look:

Bar Chart:
Creating a bar chart in Excel starts with selecting your data and choosing the ‘Bar Chart’ option from the ‘Insert’ tab. Bar charts are particularly useful for comparing individual items or categories. To enhance readability, consider adjusting the bar colors and adding data labels. In the bar chart below, you can easily compare sales versus expenses versus profits, and also compare those values by year.

Column Chart:
Similar to bar charts but oriented vertically, column charts are ideal for showing changes over time. After selecting your data, choose ‘Column Chart’ from the ‘Insert’ tab. Play with colors and axes to make your chart stand out. Whether you prefer to go with a column chart or a bar chart may simply come down to your preference.

Line Chart:
Line charts are perfect for tracking trends over periods. Select your data, click ‘Insert’, and then ‘Line Chart’. Customize your line chart by changing line styles and adding markers for key data points. Line charts may be more useful when there are fluctuations that you want to plot. Here is the chart based on the current sample data:

Here’s a look at the chart when there are greater fluctuations in the data:

A line chart in Excel with fluctuations.

Pie Chart:
For part-to-whole comparisons, pie charts are your go-to option. After selecting the data, find ‘Pie Chart’ under the ‘Insert’ tab. Enhance your pie chart by experimenting with different slice colors and adding a legend for clarity. This is ideal when you want to compare individual parts of a greater total. Suppose you wanted to analyze what made up the company’s sales. This is where a pie chart might be most appropriate:

A pie chart in Excel that compares revenue by product.

Excel has many more charts available for you to use, but these are good starting options when doing analysis. After you’ve selected the right chart, there are further enhancements you can focus on.

Tips for creating effective comparison charts

Here are some tips and things you can focus on to make your charts even better:

Simplify and Focus: Avoid cluttering your chart with too much information. Focus on the key data points you want to compare. This can sometimes mean creating multiple charts instead of trying to fit everything into one.

Use Appropriate Scale and Axes: Ensure that your axes are scaled properly to accurately reflect the differences in data. Misleading scales can lead to incorrect interpretations.

Color and Design: Use color effectively to differentiate data sets and draw attention to key points. However, be mindful of color blindness and avoid using colors that might be hard to distinguish.

Clear Labels and Legends: Use labels and legends that clearly describe what each part of your chart represents. Avoid jargon or abbreviations that might not be understood by all viewers.

Consistent Formatting: Keep formatting like font size, color schemes, and line styles consistent across all charts, especially when they will be viewed together.

Data Integrity: Ensure your data is accurate and up to date. Misleading or incorrect data can harm credibility.

Accessibility: Make your charts accessible to everyone, including those with visual impairments. This can involve using larger text, high-contrast colors, and providing alternative text descriptions where necessary.

Checklist for creating comparison charts

[ ] Chart Type Selection: Choose the most appropriate chart type for your data.

[ ] Data Accuracy: Verify the data for accuracy and relevance.

[ ] Simplification: Remove unnecessary data or split into multiple charts if needed.

[ ] Scaling and Axes: Check that axes are scaled properly to accurately represent the data.

[ ] Color Usage: Use distinct colors to differentiate data sets; consider color blindness.

[ ] Labels and Legends: Ensure all parts of the chart are clearly labeled.

[ ] Consistent Formatting: Maintain consistent formatting across all elements.

[ ] Review for Clarity: Check if the chart conveys the intended message clearly.

[ ] Accessibility Compliance: Ensure the chart is accessible to all audiences.

[ ] Feedback: If possible, get feedback from others to see if the chart is easily understandable.

If you like this post on How to Create Effective Comparison 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.