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.


Download Practice Workbook


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.


4 Methods to Make Sales Comparison Chart in Excel

In this article, we are going to learn 4 effective ways to make a sales comparison chart in excel. After learning these methods, you will be able to make sales comparison charts in Excel without any issues.
We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.


1. Using INDEX Function to Create a Sales Comparison Chart

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.

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart


Step-01: Enabling Developer Tab

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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.

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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.

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart


Step-04: Creating Table for Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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


Step-05: Implementing INDEX Function

  • First, we will use the following formula in cell B18 to extract a particular row from our dataset.
=INDEX(B5:G11,C16,{1,2,3,4,5,6})

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.

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

  • Similarly, we will use the following formula in cell B19 to extract another row from the data set.
=INDEX(B5:G11,F15,{1,2,3,4,5,6})

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.

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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.

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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.

how to make sales comparison chart in excel Using INDEX function to create Sales Comparison Chart

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


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.

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

Steps:

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

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

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

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

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

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

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

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

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.

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

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

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

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.

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

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


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.

how to make sales comparison chart in excel Using Scatter Chart to Create Sales Comparison 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.

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

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.

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

Read More: How to Make a Salary Comparison Chart in Excel (Create with Easy Steps)


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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart


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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

  • Now, press Ok from the dialogue box.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart


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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart


Step-04: Creating Table for Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart


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.
=VLOOKUP(J5,B:C,2,FALSE)

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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

  • Next, we can use the following formula in cell L5 to extract the values from the Average Sales pivot table.
=VLOOKUP(J5,F:G,2,FALSE)

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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart


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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

Afterward, markers will be added to the dashed line like the following image.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart


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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

  • After that, click Ok.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

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.

how to make sales comparison chart in excel Applying Pivot Table and Line Chart to Make Sales Comparison Chart

Read More: Year Over Year Comparison Chart in Excel (Create in 4 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.

how to make sales comparison chart in excel


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

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