How to Make a Comparison Table in Excel (2 Methods)

While working with Microsoft Excel, often we need to make a comparison table. Most of the time, excel tables contain a lot of random data which might not make any sense at all. But, if you compare data in tables, those random data give us useful information. The information gained from the data comparison in excel tables helps us to make important decisions in real life (e.g. in business). We can compare table data in several ways. For example, you can use Conditional Formatting or Excel Charts to compare data in tables. This article will guide how you can prepare a comparison table in excel.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

2 Methods to Make a Comparison Table in Excel

First I will discuss how you can make a comparison between excel tables or columns.

1. Comparison of Two Tables with Conditional Formatting

Let’s assume, we have two tables containing several electronic items’ sales data. We can see that the unit prices of headphones, portable chargers, and gaming gear are different on these two tables. Consequently, their total sales are different too. Now I will highlight the differences in Table 2 using Conditional Formatting in excel.

Steps:

• Firstly select Table 2 (B14:E18).

• Next, from Excel Ribbon, go Home > Conditional Formatting.

• Now select the New Rule option from the Conditional Formatting drop-down.

• As a result, the New Formatting Rule dialog appears. Select the rule type: Use a formula to determine which cells to format. Type the below formula in the field: Format values where this formula is true. After that, press Format.

• Then the Format Cells dialog shows up. Go to the Fill tab and choose the color and press OK to close the Format Cells dialog.

• Again press OK.

• Finally, we will get the below output. All the items that have different unit prices and total sales are highlighted in Table 2.

Read More: Compare Two Tables and Highlight Differences in Excel (4 Methods)

Similar Readings

• How to Make Group by Same Interval in Excel Pivot Table (2 Methods)
• Calculated Field Sum Divided by Count in Pivot Table
• Excel Pivot Table Group by Week (3 Suitable Examples)
• How to Calculate Percent Frequency Distribution in Excel (2 Methods)
• How to Insert A Pivot Table in Excel (A Step-by-Step Guideline)

2. Comparison between Columns of Two Tables in Excel

You can compare columns using Conditional Formatting in excel. Suppose I have two lists of fruits in columns B and D. Now, I will highlight the fruits that are only in one column. Follow the below steps to make a comparison between these two columns.

Steps:

• Initially, I will name both lists as tables. To name the list of column B, select the range B5:B14 and type Table_1 in the Name Box (left to the Formula Bar).

• Similarly, name the other range (D5:D15) as Table_2.

• Now, I will highlight the fruits of Table_2 that are not present in Table_1. To do that, select Table_1, and go to Home > Conditional Formatting > New Rule.

• When the New Formatting Rule window shows up, Select the below Rule Type and type the below formula in the Edit Rule Description box. After that, choose the format color and press OK.

Here the COUNTIF function counts the number of cells in Table_2 that is equal to the value of Cell B5.

• As a result, here is the result we receive. All the fruits of Table_1 which are not in Table_2 are highlighted in green.

• Similarly, now I will highlight the fruits of Table 1 that are not present in Table 2. Select Table_2, go to Home > Conditional Formatting > New Rule.
• Then, type the following formula in the Edit Rule Description box of the New Formatting Rule dialog, choose the format color, and press OK.

• As a result, we can see all the Table_1 fruits that are not in Table_2 are highlighted in pink.

⏩ Note:

You can name a data range following the path Formulas > Define Name.

Read More: How to Compare Two Tables for Differences with Excel VBA (3 Methods)

Make a Comparison Chart from Table in Excel

Basically, there is no chart in excel that is named ‘Comparison Chart’. In excel, when values in a table are graphically represented to understand patterns of data, we call it a ‘Comparison Chart’. Suppose we have state-wise sales data for Headphones in the below table. Just looking at these data actually makes no sense. On the other hand, if you put these data in a bar excel chart, you will easily understand what is happening here. Based on the information we get from the chart, we can take strategic decisions to improve sales revenue in a certain area. Let’s follow the below instructions to represent the following data in a combo chart.

Steps:

• First, select the table, and go to the Insert tab. Next, go to the Charts section, from the Bar Chart drop-down click Clustered Column option from the 2-D Column (see screenshot).

• Consequently, you will get the below chart. Now click on the chart area, and go to the Chart Design section.

• After that, go to Chart Design > Change Chart Type.

• As a consequence, the Change Chart Type dialog appears. From the All Charts tab click on the Combo option.

• You will see a combination of chart types. Now select Chart Type: Line for Revenue column and make it as Secondary Axis. Then press OK.

• Finally, we can see that revenue for each state is displayed in a line chart along with a secondary axis located on the right side of our chart. You can analyze which state has less revenue and what measures you can take to improve revenue.

Read More: Types of Tables in Excel: A Complete Overview

Conclusion

In the above article, I have tried to discuss several methods to make a comparison table in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

Related Articles

• How to Lookup a Table and Return Values in Excel (3 Simple Ways)
• [Fix] The Pivot Table Name Is Not Valid (7 Causes with Solutions)
• How to Get a Count in Excel Pivot Table Calculated Field
• How to Group Columns in Excel Pivot Table (2 Methods)
• Pivot Table Custom Grouping: With 3 Criteria
• How to Extend Table in Excel (4 Ways)