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

`=B6<>B14`

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

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

`=COUNTIF(Table_2,B5)=0`

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

`=COUNTIF(Table_1,D5)=0`

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

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

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