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.


Download Practice Workbook


Significance of a Comparison Chart

As the name refers, a Comparison Chart is such 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. Fo this reason, a Comparison Chart is a highly efficient way of visualizing datasets.


4 Ways to Make a Comparison Chart in Excel

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 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.

how to make a comparison chart in excel Applying Clustered Column Chart to Make a Comparison Chart in Excel

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

  • After that, press OK.

how to make a comparison chart in excel Applying Clustered Column Chart to Make a Comparison Chart in Excel

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.

how to make a comparison chart in excel Applying Clustered Column Chart to Make a Comparison Chart in Excel

  • 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.

how to make a comparison chart in excel Applying Clustered Column Chart to Make a Comparison Chart in Excel

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

how to make a comparison chart in excel Applying Clustered Column Chart to Make a Comparison Chart in Excel

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.

how to make a comparison chart in excel Applying Clustered Column Chart to Make a Comparison Chart in Excel

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

how to make a comparison chart in excel Applying Clustered Column Chart to Make a Comparison Chart in Excel

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.

how to make a comparison chart in excel Applying Clustered Column Chart to Make a Comparison Chart in Excel

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

how to make a comparison chart in excel Applying Clustered Column Chart to Make a Comparison Chart in Excel

  • 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.

Read More: Side-by-Side Comparison Chart in Excel (6 Suitable Examples)


2. Using 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.

how to make a comparison chart in excel Using Scatter Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Using Scatter Chart to Create a Comparison Chart

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.

how to make a comparison chart in excel Using Scatter Chart to Create a Comparison Chart

Read More: How to Compare Two Sets of Data in Excel Chart (5 Examples)


3. Utilizing 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.

how to make a comparison chart in excel Utilizing Combo Chart as Comparison Chart in Excel

  • 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.

how to make a comparison chart in excel Utilizing Combo Chart as Comparison Chart in Excel

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

how to make a comparison chart in excel Utilizing Combo Chart as Comparison Chart in Excel

Read More: How to Create Month to Month Comparison Chart in Excel


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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart


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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart


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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart


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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart


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

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart


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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart


Step-12: Adding Location Slicer

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart


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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

  • 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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

  • Then hit OK again.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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.

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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

how to make a comparison chart in excel Applying Pivot Table and Line Chart to Create a Comparison Chart

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.

Read More: How to Make Sales Comparison Chart in Excel (4 Simple Ways)


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.


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!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo