# How to Make a Sales Comparison Chart in Excel – 4 Methods

## Method 1 – Using the INDEX Function to Create a Sales Comparison Chart in Excel

The following dataset showcases Monthly Sales of a Company.

To create a sales comparison chart for company employees throughout different Months:

### Step 1: Enabling the Developer Tab

• Go to the ribbon and right-click.
• Select Customize the Ribbon.

The following image will be displayed.

• Check Developer >> click Ok.

The Developer tab will be visible on the ribbon.

### Step 2: Adding a Combo Box

• Go to the Developer tab >> InsertÂ >> Combo Box (Form Control).

• Create the drop-down box as shown below by clicking,Â  holding and dragging the mouse.

### Step 3: Editing the Combo Box

• Right-click as shown below.
• Select Format Control.

The Format Control dialog box will be displayed.

• In the Format Object dialog box, click inside the Input range box in Control.
• Select theÂ Name column.

• Click inside Cell link.
• Choose aÂ  blank cell. Here, C16.
• Click Ok.

All names are added to the drop-down.

Note: The cell that you have selected, will display the serial number of the name.

• Name the cell as Ref Number.

Click any name in the drop-down of Combo Box. Here, Harry.

As Harry is 6th from the top, the Ref Number cell displays 6.

Follow the same steps to add another Combo Box. This is the output.

### Step 4: Creating a Table for the Comparison Chart

• Select all headers and press CTRL+C.

• Select B17 and press CTRL+V.

Anew row of headers will be added.

### Step 5: Applying the INDEX Function

• Use the following formula in B18 to extract a row from the dataset.
`=INDEX(B5:G11,C16,{1,2,3,4,5,6})`

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

• Press ENTER.

• Use the following formula in B19 to extract another row.
`=INDEX(B5:G11,F15,{1,2,3,4,5,6})`

F15 represents the row to extract.

• Press ENTER.

This is the output.

You can select any name in the Combo Box drop-downs and the row will be displayed.

### Step 6: Inserting a Comparison Chart

• Select the table as shown below.
• Go to the Insert tab >> Insert Column or Bar Chart Â >> Clustered Column.

A clustered column chart will be added.

• Click the chart title and edit it. Here, Monthly Sales Comparison Chart.

This is the sales comparison chart:

You can use different combinations of names in the Combo Box drop-down, and the comparison chart will be updated.

Read More: How to Make a Comparison Chart in Excel

## Method 2 – Applying a Clustered Column Chart to Create a Sales Comparison Chart in Excel

The dataset showcases the daily sales data of 2 products. Create a Clustered Column Chart Â to compare the sales of 3 salespersons.

Steps:

• Select B5 and B6.
• Go to the Home tab >> Merge & Center.

• In the dialog box, click Ok.

The following image will be displayed.

Follow the same steps to merge cells:

• Select C7. Here,Â Products sold by Jimmy.
• Go to the Home tab >> InsertÂ >> Insert Sheet Rows.

Add a new row before C7:

Add a new row before C10. Here,Â Products sold by Howard.

This is the output.

• Select the entire dataset.
• Go to the Insert tab >> Insert Column or Bar Chart>> Clustered Column.

The following chart is displayed.

Follow the previously mentioned steps to add the chart title. Here, Daily Sales Comparison Chart.
This is the output.

## Method 3 – Using a Scatter Chart to Create a Sales Comparison Chart

Use the Scatter Chart feature.

The following dataset contains the Monthly Sales of ABC Food Court for various Products.

• Select the entire data set.
• Go to the Insert tab >> Insert Scatter (X, Y) or Bubble ChartÂ >> Scatter.

A scatter chart will be displayed.

Follow the previously mentioned steps to add the chart title. Here, Monthly Sales Comparison.
This is the output.

## Method 4 – Using a Pivot Table and a Line Chart to Create a Sales Comparison Chart

The dataset showcases sales data of 3 employees of a company in different locations.

### Step 1: Inserting a Pivot Table

• Select the entire data set.

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

• Go to the Insert tab >> Pivot Table.

• A dialog box will be displayed.

• Click Ok.

Another dialogue box will be displayed.

• Drag Month to Rows, Sales to Values, and Name to Filters.

The following image will be displayed.

### Step 2: Editing the Pivot Table

• Set a header for the Pivot Table. Here, Individual Sales.

• Double-click Sum of Sales.
• In the dialog box, select Average.
• Click Ok.

This is the output.

### Step 3: Creating a Pivot Table Without a Name Filter

• Select the pivot table created in the previous step.
• Press CTRL+C.

• Paste it into F4.

• Select any cell in the new pivot table.
• In the Pivot Table Fields dialog box, uncheck Name.

This is the output.

• Choose a header for this pivot table. Here, Average Sales.

### Step 4: Creating a Table for a Comparison Chart

• Create a table with 3 columns: Month, Person, and Average.

• Enter January in the first cell in the Month column.

• Drag down the Fill Handle to the end of the table.

Months will be displayed.

## Step 5: Using the VLOOKUP Function

• Enter the following formula in K5 to extract the values of Individual Sales.
`=VLOOKUP(J5,B:C,2,FALSE)`

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 is used for an exact match.

• Press ENTER.

The VLOOKUP function will return \$615.

Drag down the Fill Handle to see the result in the rest of the cells.

• Use the following formula in L5 to extract the values of Average Sales.
=VLOOKUP(J5,F:G,2,FALSE)

F:G is the table_array for the VLOOKUP function.

• Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

### Step 6: Inserting a Slicer

• Click any cell in the Average Sales pivot table.
• Go to the PivotTable Analyze tab >> Insert Slicer.

The Insert Slicer dialog box will open.

• Check Name.
• Click OK.

A slicer will be added.

• Add another slicer by following the same steps. Check Store Location and click OK.

Two slicers will be added.

### Step 7: Adding a Line Chart

• Select the entire Sales Comparison Table.
• Go to the Insert tab >> Insert Line or Area ChartÂ >> Line with Markers.

A line chart will be added.

### Step 8: Creating a New Worksheet

• Create a new worksheet by clicking the plus icon as shown below.

### Step 9: Adding Slicers and a Line Chart to the New Worksheet

• Go to the Support Sheet.
• Press CTRL and select the 2 slicers.
• Press CTRL+X.

• Go to the new worksheet and paste the slicers in B2.

• Go to the Support SheetÂ and select the Line Chart.
• Press CTRL+X.

• Go to the new worksheet and paste it in E2.

### Step 10: Formatting the Line Chart

• Click the paintbrush icon.
• Choose a style.

• Right-click the marked portion shown below.
• Select Format Legend.

The Format Legend dialog box will open.

• Go to Legend Options >> Top.

The legends moved to the top of the line chart.

• Rright-click any point in the orange line and select Format Data Series.

The Format Data Series dialog box will open.

• Select Fill & Line.
• Click Line and select the following options:

Line â†’ Solid Line
Color â†’TealÂ
Width â†’ 1.75 pt
Dash Type â†’ 3rd optionÂ

• Check Smoothed line.

This is the output.

• Select Markers in Format Data Series.
• Choose Solid Fill inÂ Fill option and choose the color used in the previous step.
• Click Border and select No line.

Markers will be added to the dashed line.

• Go to the Format Data Series dialog box to format the blue line by following the steps mentioned before.
• ClickÂ  Fill & Line and choose Line. Select the following options:

Line â†’ Solid Line
Color â†’ MaroonÂ
Width â†’ 1.75 pt

• Check Smoothed line.

This is the output.

• Follow the same steps mentioned before to edit the markers. Make sure the color of the marker and the line is the same.

This is the output.

### Step 11: Removing Gaps from the Line Chart

Select any location in the slicer. The line of individual sales is broken: some employees did not have any sales in that particular area at that time of the year.

• Right-click the marked portion shown below.
• Choose Select Data.

The Select Data Source dialog box will be displayed.

• Click Hidden and Empty Cells.

• In the next dialog box, select Zero.
• Click Ok.

You will be redirected to the Select DataSource dialog box.

• Click Ok.

The gaps are no longer displayed in the comparison chart.

This is the final output.

## Practice Section

Practice here.

Download Practice Workbook

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

2 Comments
1. Hi!

A very interesting and good educational page, and hopefully it will work out fine for me after you explain something that is not working for me.
In step 5 when completing the steps my drop-down list is not align horizontal but vertical!? I have tried numerous solutions and really looked if I wrote something wrong but elass canÂ´t find anything other than what you do.
I have windows 365, Swedish edition, which differs a bit from the english version, but that I think is under control. Everything works fine except the alignment. The date I have set brackets around, otherwise Excel converts it to a number.

• Reply
Lutfor Rahman Shimanto Dec 4, 2023 at 2:42 PM

Hello MATS WIKMAN

Thanks for your nice word. Your appreciation means a lot to us. I am sharing my findings regarding your issues as follows:

>> Excel drop-down lists typically display items vertically in a drop-down that displays either below or above the cell, depending on available space.

>> There is no built-in option in Excel’s Data Validation tool, frequently used to construct drop-down lists, to show the items horizontally within a single cell.

>> The Swedish edition of Excel 365 differs from the English version. In that case, you can seek help from Microsoft’s support resources for the most accurate guidance based on your specific version and language.

Hopefully, the suggestion will help you in reaching your goal. If you still have doubts, you can share your issues with precise details within the ExcelDemy Forum. Good luck!

Regards
Lutfor Rahman Shimanto
ExcelDemy

Advanced Excel Exercises with Solutions PDF