Placing two data sets in an Excel chart is an effective way to quickly understand data and make important business decisions. However, when the values in your data sets widely vary, your chart may be difficult to comprehend. A great way to mitigate this problem and make your data clear and easy to understand is by creating an Excel chart secondary axis. Here are four simple steps to creating a secondary axis in no time to present multiple data sets.
Step 1: Create a table of your data sets
First things first, we need a set a data to create a chart. Create a table in your Excel spreadsheet using the data below:
In this table, we have listed total units sold, along with total revenues.
Step 2: Create your Chart from the Table
Highlight the table like the image below.
Go to “Insert,” then “Charts.” Select “Line,” and click on the first 2-D line chart listed.
Now you have your chart. As you can see, it is impossible to see how unit price affected total sales, as the total sales and unit prices vary greatly. So now it’s time to begin adding an Excel chart secondary axis.
Step 3: Go to Format Data Series
Right-click on the line you want to add a secondary axis, then click “Format Data Series.”
Step 4: Select “Secondary Axis”
Select “Secondary Axis” under “Series Options” in the “Format Data Series” box, then click “Close.”
And now you’re done! With the help of an Excel chart secondary axis, you have gone from having a confusing, misleading chart to a clear and concise illustration of your data. So the next time you need to create an Excel chart using two sets of data, don’t sweat it! Just follow these four easy steps, and in just a few minutes, your data presentation problems are solved!