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

If you are looking for simple ways to make a sales comparison chart, this article will serve your purpose. Sales comparison charts are one of the most popular and effective ways to represent sales data visually. In Microsoft Excel, we can make sales comparison charts by following simple steps. So, let’s start with this article and learn all these steps to make a sales comparison chart in Excel.

We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

What Is a Sales Comparison Chart?

A sales comparison chart is a type of chart that allows us to compare two or more sets of sales data to each other. It assists us in making business choices by allowing us to see our data and identify any relationships between the chart elements.

1. Using the INDEX Function to Create a Sales Comparison Chart in Excel

By using the INDEX function of Excel we can easily make sales comparison charts. In the following data set, we have the Monthly Sales of XYZ Company. We are going to make a sales comparison chart for company employees throughout different Months.

Step-01: Enabling Developer Tab

• First, go to the ribbon and right-click.
• After that, select Customize the Ribbon.

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

• Now, check the box named Developer >> click Ok.

After that, the Developer tab will be visible on the ribbon.

Step-02: Adding Combo Box

• First, go to the Developer tab >> Insert dropdown >> Combo Box (Form Control) option.

• Next, create the drop-down box like the following picture by holding the left mouse button and dragging it along.

Step-03: Editing Combo Box

• First, right-click on the marked region of the following screenshot.
• After that, select Format Control.

Afterward, the Format Control dialogue box will be visible like the image given below.

• After that, click inside the Input range box under the Control tab in the Format Object dialogue box.
• Next, select the column of Name from the data set given.

• Afterward, click inside the Cell link box.
• Then you need to choose a  blank cell. You can choose any cell. In this case, we will choose cell C16.
• Now, click Ok.

After doing that, all the names should be added to the drop-down. You can check it by clicking the drop-down button.

Note: The cell that you have selected, will display the serial number of the name, that is if the name is on the top then it will display 1. If it is second from the top, it will display 2 and so on.

• After that, we have named the cell as Ref Number like the following image.

To check, click on any name from the drop-down of Combo Box. In this case, we will select Harry.

As Harry is 6th from the top, the Ref Number cell will be showing the number 6 like the following image.

By following the same steps, add another Combo Box. After that, you will be able the following image on your screen.

Step-04: Creating Table for Comparison Chart

• First, select all the headers of the initial data set and then press CTRL+C.

• After that, select cell B17 and then press CTRL+V.

After that, a new row of headers will be added like the following picture.

Step-05: Applying INDEX Function

• First, we will use the following formula in cell B18 to extract a particular row from our dataset.

Here, B5:G11 indicates the range of our data set, cell C16 represents the row number, and {1,2,3,4,5,6} are the column numbers.

• After that, press ENTER.

• Similarly, we will use the following formula in cell B19 to extract another row from the data set.

Here, cell F15 represents the row number that we need to extract.

• Afterward, press ENTER.

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

You can select any name from the Combo Box drop-downs and the respective row will be visible in the table that we created.

Step-06: Inserting Comparison Chart

• First, select the table as instructed in the following image.
• After that go to the Insert tab >> Insert Column or Bar Chart dropdown >> Clustered Column option.

Afterward, a clustered column chart will be added like the below-given image.

• Now, click on the chart title and edit the title. In this case, we are using Monthly Sales Comparison Chart as our chart title.

At this stage, your sales comparison chart is constructed and will be looking like the following picture.

You can try out different combinations of names from the Combo Box drop-down, and the comparison chart will be adjusted accordingly.

Read More: How to Make a Comparison Chart in Excel

2. Applying Clustered Column Chart to Make Sales Comparison Chart in Excel

Clustered Column Chart is a popular method of making a sales comparison chart in Excel. In the following data set, we have daily sales data of 2 products of  XYZ company. We will create a sales comparison chart for 3 salespersons of the company.

Steps:

• First, select cells B5 and B6.
• After that go to the Home tab >> Merge & Center option.

• Now, a dialogue box will pop up, and select Ok from the dialogue box.

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

By following the same steps you can merge the following cells and it should look like the picture given below.

• After that, select cell C7. Here cell C7 represents the Product sold by Jimmy.
• Then go to the Home tab >> Inset dropdown >> Insert Sheet Rows option.

Now, a new row should be added before cell C7 like the following image.

Similarly, add a new row before cell C10. Here cell C10 represents the Product sold by Howard. After adding the row, you will be able to see the following image on your screen.

• Afterward, select the entire dataset.
• After that go to the Insert tab >> Insert Column or Bar Chart drop-down >> Clustered Column option.

After selecting the clustered column, the following chart should be added to your worksheet.

By following the previously mentioned steps we will add the chart title. In this case, we are using Daily Sales Comparison Chart as our chart title.
At this stage, your sales comparison chart is ready and should look like the following image.

Read More: How to Compare 3 Sets of Data in Excel Chart

3. Using Scatter Chart to Create Sales Comparison Chart

In this section of the article, we are going to make a sales comparison chart by using the Scatter Chart feature of Excel. In the following data set, we have Monthly Sales of ABC Food Court for its various Products. Let’s learn the detailed steps to create a sales comparison chart using Scatter Chart.

• First, select the entire data set.
• After that, go to the Insert tab >> Insert Scatter (X, Y) or Bubble Chart drop-down >> Scatter option.

Afterward, a scatter chart should be added for the selected data set on your worksheet.

By following the previously mentioned steps we will add the chart title. Here, we will use Monthly Sales Comparison as our chart title.
At this stage, your sales comparison chart is created and you should see the following image on your screen.

Read More: How to Make a Salary Comparison Chart in Excel

4. Applying Pivot Table and Line Chart to Make Sales Comparison Chart

The method that will be discussed in this section is somewhat advanced. But the end result is quite outstanding. We will use Pivot Table and Line Chart to make a sales comparison chart.
In the following data set, we have yearly sales data of 3 employees of a company with various locations. Let’s learn the detailed steps to create a sales comparison chart for these employees.

Step-01: Inserting Pivot Table

• First, select the entire data set.

Note: As the data set is quite large (40 rows), it has been shown in the following 2 images.

• After that, go to the Insert tab >> Pivot Table option.

• Afterward, a dialogue box will open like the following image.

• Now, press Ok from the dialogue box.

At this stage, another dialogue box will open named Pivot Table Fields as shown in the image given below.

• After that, drag down Month to the Rows section, Sales to the Values section, and Name in the Filters section.

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

Step-02: Editing Pivot Table

• First, give a heading to your Pivot Table. Here we will use Individual Sales as the heading.

• After that, double-click on Sum of Sales as marked in the following image.
• Then a dialogue box will open and select Average from that.
• Now, select Ok.

After that, the following image should be available on your screen.

Step-03: Creating Pivot Table Without Name Filter

• First, select the pivot table that was created in the previous step.
• Then, press CTRL+C.

• After that, paste it into cell F4.

Now, you will see that another identical pivot table is added to the worksheet.

• Next, select any cell on the newly created pivot table. This will open the Pivot Table Fields dialogue box.
• Now, uncheck the box of Name.

After doing that, you will see the following image on your screen.

• Now, give a heading to this pivot table. Here, we will use Average Sales as the heading.

Step-04: Creating Table for Comparison Chart

• First, create a table with 3 columns named Month, Person, and Average like the following image.

• After that, insert January in the first cell under the Month column.

• Then, click the Fill Handle and drag it down to the end of the table.

Afterward, you will have Months up to December on your worksheet.

Step-05: Using VLOOKUP Function

• First, we will insert the following formula in cell K5 to extract the values from the Individual Sales pivot table.

Here, J5 is the Month of January which is the lookup_value of the VLOOKUP function, B:C is the table_array, 2 is the column_index_number, and FALSE means we are searching for an exact match.

• After that, press ENTER.

Afterward, the VLOOKUP function will return $615 like the image given below.

By using Excel’s AutoFill feature you can get the data for the rest of the cells as marked in the following image.

• Next, we can use the following formula in cell L5 to extract the values from the Average Sales pivot table.

Here, F:G is the table_array for the VLOOKUP function.

• Now, press ENTER.

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

By using the AutoFill feature of Excel, we can get the following output as given in the image below.

Step-06: Inserting Slicer

• First, click on any cell of the Average Sales pivot table as marked in the following image.
• After that go to the PivotTable Analyze tab >> Insert Slicer option.

Afterward, the Insert Slicer dialogue box will open in your worksheet like the picture given below.

• Now, check the box of Name in the Insert Slicer dialogue box.
• After that, click OK.

Consequently, a slicer will be added to your worksheet like the following image.

• Similarly, add another slicer by following the same steps. But in this case, check the box of Store Location and then press OK.

At this stage, two slicers will be added like the following image.

Step-07: Adding Line Chart

• First, select the entire Sales Comparison Table.
• After that go to the Insert tab >> Insert Line or Area Chart drop-down >> Line with Markers option.

Afterward, a line chart will be added to your worksheet with markers like the below-given image.

Step-08: Creating a New Worksheet

• Create a new worksheet by clicking the plus icon marked in the following image.

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

• First, go to the Support Sheet worksheet.
• After that, press CTRL and select the 2 slicers at once.
• Then press CTRL+X.

• Now, go to the newly created worksheet and paste it into cell B2.

Consequently, the 2 slicers will be added to the new worksheet.

• Again go to the Support Sheet worksheet and select the Line Chart.
• After that, press CTRL+X.

• Next, go to the new worksheet and paste it into cell E2 like the following image.

Step-10: Formatting Line Chart

• First, click on the paintbrush icon marked in the image given below.
• Then choose any style you prefer. We will use the marked option in this case.

• By following the previously mentioned steps we will add the chart title. Here we will use the Yearly Sales Comparison Chart as our chart title.

At this stage, you will be able to see the following image on your screen.

• Now, right-click on the marked portion in the following image.
• After that, select Format Legend.

After that, the Format Legend dialogue box will open in your worksheet.

• Afterward, go to the Legend Options tab >> Top option.

Now, the legends of the chart should move to the top of the line chart like the image given below.

• After that, right-click on any point on the orange line and then select Format Data Series.

Consequently, the Format Data Series dialogue box will open.

• Next, select Fill & Line tab.
• After that, click on Line and select the following options.

Line → Solid Line
Color →Teal (or whatever you want)
Width → 1.75 pt
Dash Type → 3rd option 

• Finally, check the box of the Smoothed line.

After selecting these options you will be able to see the swallowing picture on your screen.

• After that, select Markers from the Format Data Series dialogue box.
• Choose 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, markers will be added to the dashed line like the following image.

• Next, again go to the Format Data Series dialogue box for the blue line by following the steps mentioned before.
• Click on the Fill & Line tab and choose Line to select the following options.

Line → Solid Line
Color → Maroon (any other color that is different from the previous one)
Width → 1.75 pt

• Lastly, check the box of the Smoothed line.

Consequently, you will see the following image on your screen.

• Now by using the same steps mentioned before, you can edit the markers. Just make sure that the color of the marker and the line are the same.

Eventually, you will see the following image on your screen.

Step-11: Removing Gaps from Line Chart

Select any location from the location slicer. The line of individual sales is clearly broken. It occurred because certain employees did not have any sales in that particular area at that time of year. We shall now attempt to implement a better way to depict it.

• First, right-click on the marked portion of the following picture.
• After that, choose Select Data.

Afterward, a dialogue box will open named Select Data Source like the image given below.

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

• Next, another dialogue box will open, and select Zero from there.
• After that, click Ok.

Consequently, you will be redirected to the Select DataSource dialogue box.

• After that, click Ok.

Eventually, you will be able to see that the gaps in your comparison chart are no longer present.

Congratulations! You have successfully created a sales comparison chart by using the pivot table and line chart. You can choose any option from the slicers and your comparison chart will automatically change.

Read More: How to Compare Two Sets of Data in Excel Chart

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.

Download Practice Workbook

Conclusion

Finally, we have come to the very end of the article. I truly hope that this article was able to guide you to learn how to make a sales 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

• How to Make a Price Comparison Chart in Excel
• How to Create a Budget vs Actual Chart in Excel

<< Go Back to Comparison Chart in Excel | Excel Charts | Learn Excel