How to Make a Comparison Chart in Excel (4 Effective Ways)

If you are looking for ways to make a Comparison Chart in Excel, then this article will serve this purpose. Comparison Charts are widely used in data visualization. In Excel, we can easily make a Comparison Chart by following some simple steps. So let’s start with the article and learn all these steps to make a Comparison Chart in Excel.

Significance of a Comparison Chart

As the name refers, a Comparison Chart is a kind of chart where we can compare two or more different kinds of data and understand various correlations among them. With the help of a Comparison Chart, we can easily visualize our data along with their trends, correlations among different parameters, etc. And all of this can be done without going through the long, tedious datasets. For this reason, a Comparison Chart is a highly efficient way of visualizing datasets.

In this article, we are going to learn 4 simple methods to make a Comparison Chart in Excel. After learning these methods, you will be able to create both simple and advanced Comparison Charts in Microsoft Excel.
We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

1. Applying a Clustered Column Chart to Make a Comparison Chart in Excel

Clustered Column Chart is one of the most popular methods of creating a Comparison Chart. In the following dataset, we have sales data of ABC company for different states and cities. We will make a Comparison Chart of Sales among different States.

Steps:

If we look carefully, we can see that there are a total of 3 states in the 6 columns. So, firstly, select two cells of Arizona.
After that, go to the Home tab.
Then, from the Alignment group choose Merge & Center.

After selecting Merge & Center, you will be able to see the following image on the screen.

After that, press OK.

Afterward, you will be able to see that the two cells are merged together.

Similarly, for the other two states, we can follow the same procedure and merge them. Then, your dataset should be looking like this.

Now, click on cell C7. Here cell C7 represents Salt Lake City in the state of Utah.
After that, go to the Home tab >> Insert dropdown >> Insert Sheet Rows option.

After that, you will be able to see the following picture on your screen.

Similarly, add another blank row over the city of Houston. After that, your dataset should be looking like the following image.

Now, select the whole dataset.
Go to the Insert tab >> Insert Column or Bar Chart dropdown >> Clustered 2-D Column option.

After that, a Clustered Column Chart should be visible on your screen like the below-given image.

At this stage, we are going to format our chart to give it a better look and visibility.

First, click on the paintbrush icon as marked in the following image.
After that, choose your preferred style.

After selecting the style, you will be able to see that the chart has been formatted with your preferred style.

Now, click on the Chart Elements.
Then check the box of Data Labels.

At this point, Data Labels will be added to the chart like the following picture.

After that, click on the Chart Title as marked in the following image.

Afterward, type your preferred chart title. In this case, we are typing Sales Data.

After typing your chart title, your Comparison Chart is created and it should be looking like the below-given image.

2. Using a Scatter Chart to Create a Comparison Chart

In this portion of the article, we are going to use the Scatter Chart to create our Comparison Chart. In the following dataset, we have sales data of XYZ company for various States. Let’s learn the detailed steps to create a Comparison Chart using Scatter Chart.

Steps:

First, select the entire dataset.
After that, go to the Insert tab.
Then select Insert Scatter (X, Y) or Bubble Chart.
Afterward, choose Scatter from the drop-down.

After that, a Scatter Chart for the selected dataset will be visible on the screen like the following image.

Now select your preferred style of the chart by following the same steps mentioned before.
After that, you will be able to see that your chart has been formatted with your preferred style.

After that, edit the chart title by following the previously mentioned steps. In this case, we are using Sales Review as our chart title.
Afterward, the following image will be visible on your screen and your Comparison Chart is created.

3. Using Combo Chart as Comparison Chart in Excel

Now, we are going to create a Comparison Chart using the Combo Chart feature of Excel. In the following dataset, we have half-yearly sales data of a company. We will create a Comparison Chart for the dataset for different Month.

Steps:

Firstly, select the entire dataset.
After that, go to the Insert tab.
Then, click on Insert Combo Chart.
Afterward, select Create Custom Combo Chart from the drop-down.

Now, a dialogue box will pop up. Then, select Clustered Column for Sales and Total Cost.
After that, select Line for Profit.
Next, check the box of Secondary Axis beside Line.
Afterward, press OK.

After that, your chart should be looking like the following image.

Now, choose your preferred style and edit the chart title by following the previously mentioned steps.
At this stage, your Comparison Chart is ready and you will be able to see the below-given image on your screen

4. Applying Pivot Table and Line Chart to Create a Comparison Chart

This method is a somewhat advanced way of creating a Comparison Chart. By using Pivot Table and Line Chart we are going to create a dynamic Comparison Chart.
In the following dataset, we have yearly sales data of a company for various states. Lets’s start learning the method in detailed steps.

Step-01: Inserting Pivot Table

First, select the entire dataset.

Note: The dataset is quite large (it contains 124 rows). That is why it is shown in the following 5 images.

After selecting the dataset, go to the Insert tab.
Then click on Pivot Table from the Tables group.

After that, a dialogue box will pop up, and select Ok from the dialogue box as marked in the following picture.

Afterward, another dialogue box will be visible on your screen named Pivot Table Fields.

Next, drag Month in the Rows section, Sales Qty in the Values section, and the Name in the Filter section.

Afterward, you will be able to see the following Pivot Table on your screen.

Step-02: Editing Pivot Table

Now, give a heading of the table like the following image. Here we are naming the table as Individual Sales Qty.

After that, double-click on the Sum of Sales Qty as marked in the following image.
Then, a dialogue box will open, and select Average from the dialogue box.
Finally, press OK.

At this point, the following image will be available on your screen.

Next, select the data of the Average of Sales Qty column as highlighted in the following picture.
Then, go to the Home tab and click on the Decrease Decimal option once.

Afterward, the decimal points on the pivot table should be gone like the following image.

Step-03: Creating Pivot Table Without Name Filter

First, select the entire table.
After that press CTRL+C to copy the table.

Now press CTRL+V to paste the copied table in cell G4 and you will be able to see the following image on your screen.

After that, click on any cell of the new Pivot Table.
Afterward, from the Pivot Table Fields dialogue box, uncheck the Name box.

After doing this, the Name filter from your new Pivot Table should be removed.

Now, give a heading to the table. In this case, we have used Average Sales of All Employee as the heading.

Step-04: Constructing Table for Comparison Chart

First, create a table consisting of 3 columns named Month, Individual, and Average and give a heading like a picture below.

After that, type Jan (abbreviation of January) in the first cell under the Month column.

Afterward, drag the Fill Handle up to the end of the table.

Then, you will be able to see the following image on your screen.

Step-05: Using VLOOKUP Function

First, use the following formula in cell M5 to extract values from the Average of Sales Qty column of the Individual Sales Qty Pivot Table.

=VLOOKUP(L5,B:C,2,0)

Here L5 is the Month of Jan that is our lookup_value for the VLOOKUP function, and B: C is the table_array where will search for the lookup value, 2 is the column_index_number, and 0 means we are looking for an exact match.

After that, hit ENTER.

Now, the VLOOKUP function should return 255 like the following image.

Afterward, use Excel’s AutoFill Feature to complete the rest of the cell of the column and you will get the following output.

In the Average column, we are again going to use another VLOOKUP function. But in this case, our table_array will change.

Now, we can use the following formula in cell N5.

=VLOOKUP(L5,G:H,2,0)

Here, G:H is the changed table_array.

Afterward, use the AutoFill option to get the rest of the data.
Consequently, your table should look like the image given below.

Step-06: Inserting Name Slicer

In this step, we are going to introduce a Slicer for the names of our dataset. To do this, we need to follow these steps.

First, click on any cell of the Individual Sales Qty Pivot Table like the following image.

When you select on the Pivot Table a new tab will be visible in the ribbon named PivotTable Analyze. Click on that.
Afterward, select Insert Slicer from the Filter group.

After that, an Insert Slicer dialogue box will open and check the box of Name from that dialogue box.
Then, press Ok.

After doing this, a slicer should be added to your worksheet like the following image.

Step-07: Adding Line Chart

First, select the entire dataset.
Then, go to the Insert tab >> Insert Line or Area Chart drop-down >> choose Line with Markers.

At this point, the line chart will be added to your worksheet.

Step-08: Creating a New Worksheet

Now, create a new worksheet by pressing the Plus sign in the marked portion of the following image.

Step-09: Adding Slicer and Line Chart to New Worksheet

First, select the Slicer from the Support Sheet worksheet.
After that, press CTRL+X.

Next, go to the newly created worksheet and paste it here on cell B2 by pressing CTRL+V.

Consequently, you will be able to see that the Name slicer has been added to the new worksheet.

Now, select the line chart from the Support Sheet worksheet and then press CTRL+X.

After that, paste it into cell E2 of the new worksheet and your chart will be added like the following image.

Step-10: Formatting Chart

First, edit the chart title by following the steps mentioned before. Here we are going to use Yearly Sales Review as our chart title.

After adding the chart title, you will see the picture given below, on your screen.

Now, right-click on the Legend of the chart and then select the Format Legend option.

Afterward, the Format Legend dialogue box will open and select Legend Options.
Then choose Top as the Legend Position.

Now, the Legends should be moved to the top of the chart like the following image.

Now, right-click on any point on the orange line and then click on the Format Data Series option.

Next, the Format Data Series dialogue box will open, and select Fill & Line.
After that, click on Line and select the following options.

Line → Solid Line
Color → Pink (or whatever you want)
Width → 1.5 pt
Dash Type → Second option

Finally, check the box of the Smoothed line.

After selecting these options your chart should look like the following image.

Now, click on Markers in the Format Data Series dialogue box.
Select Solid Fill from the Fill option and add the same color that you choose in the previous step.
After that, click on Border and select No line.

Afterward, your chart should look like the following image.

After that, again go to the Format Data Series dialogue box for the other line by following the steps mentioned before.
Then, click on the Fill & Line tab and select Line to choose the followings.

Line → Solid Line
Color → Green (or any other color different from the previous one)
Width → 1.5 pt

Finally, check the box of the Smoothed line.

Eventually, you will be able to see the image given below on your screen.

Now, by following the same steps mentioned before, edit the markers. Make sure that the color of the marker and the line are the same.
After editing the markers, your chart should look like the following image. Here, the dashed line is the average sales of all employees and the solid line is the line for sales of the individual employee.

Step-11: Inserting Location Slicer

By following the steps mentioned before, open the Insert Slicers dialogue box.
After that, check the box of Location and then hit Ok.

Afterward, Location Slicers will be added to the worksheet.

Now, select the Slicer tab.
After that, click on Buttons.
Then select Columns and increase it to 2 from the drop-down.

After that, the slicer will be in 2 columns like in the following picture.

Now, resize the size of the slicer by dragging the marked point in the following image.

Afterward, you can see the following image on your screen.

Step-12: Adding Location Slicer

Firstly, select the slicer from the Support Sheet worksheet.
After that, press CTRL+X.

Afterward, paste the slicer into the new worksheet that is created in Step-08.

At this stage, your chart should be looking like the image given below.

Step-13: Editing Location Slicer

First, right-click on the location slicer.
Then select Report Connections.

Afterward, Report Connections (Location) dialogue box will open like the following picture.

Now, check the box of the Pivot Table 7.
After that, hit Ok.

Then click on any location and name like the following image. Here we have selected Alabama and Adam Holmes.

You can see that the line of individual sales is not continuous. It has happened because some employees didn’t have any sales in that particular location at a particular time of the year. Now, we will try to represent it in a better way.

First, right-click on the data series that has broken lines.
After that, click on Select Data.

Now, the Select Data Source dialogue box will open, and click on Hidden and Empty Cells from the dialogue box.

Afterward, the following image will be visible on your screen.

Now, select Zero from the dialogue box.
After that, press Ok.

After pressing OK you will be redirected to the Select Data Source dialogue box like the image given below.

Then hit OK again.

All of the broken lines are no longer visible. At this stage, you will see a continuous solid line in your chart.

Step-14: Checking If Comparison Chart Is Working or Not

Now, you can click on any of the locations or names. The chart will automatically change. Here we have selected the location Texas and the name Kobe Tyler.

After selecting the location and name, your Comparison Chart should be changed like the following image.

Congratulations! You have successfully created the dynamic Comparison Chart with the help of the Pivot Table and Line Chart in Excel and you can change your chart quickly with some clicks.

Practice Section

For doing practice by yourself we have provided the practice sections in each worksheet on the right side. Please do it by yourself.

Things to Remember

Do not forget to label the horizontal and vertical axes of the chart.
Use appropriate scales to understand the chart clearly.

Download Practice Workbook

Comparison Chart.xlsx

Conclusion

Finally, we have come to the very end of our article. I truly hope that this article was able to guide you to make a comparison chart in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!

Frequently Asked Questions

1. Can I create a comparison chart with a combination of different chart types?

Yes, you can create combination charts where you can use different chart types within the same chart in Excel.

2. Can I update my comparison chart automatically when the data changes?

Yes, you can update your chart automatically if it is based on a dynamic range or source. This is especially helpful when you have linked the chart to a table or a range using Excel formulas or named ranges.

3. Can I copy and paste my comparison chart from Excel to other documents or presentations?
Yes, you can copy and paste your comparison chart from Excel to other documents or presentations. First, create the chart in Excel, simply select it, press Ctrl+C to copy and Ctrl+V to paste it into another presentation.

Comparison Chart in Excel: Knowledge Hub

<< Go Back to Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!