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.

Comparison of Two Tables with Conditional Formatting

Steps:

  • Firstly select Table 2 (B14:E18).

Comparison of Two Tables with Conditional Formatting

Comparison of Two Tables with Conditional Formatting

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

Comparison of Two Tables with Conditional Formatting

  • 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

Comparison of Two Tables with Conditional Formatting

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

Comparison of Two Tables with Conditional Formatting

  • Again press OK.

Comparison of Two Tables with Conditional Formatting

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

Comparison between Columns of Two Tables in Excel

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

Comparison between Columns of Two Tables in Excel

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

Comparison between Columns of Two Tables in Excel

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

Comparison between Columns of Two Tables in Excel

  • 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

Comparison between Columns of Two Tables in Excel

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.

Comparison between Columns of Two Tables in Excel

  • 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

Comparison between Columns of Two Tables in Excel

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

Make a Comparison Chart from Table in Excel

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

Make a Comparison Chart from Table in Excel

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

Make a Comparison Chart from Table in Excel

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

Make a Comparison Chart from Table in Excel

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

Make a Comparison Chart from Table in Excel

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

Make a Comparison Chart from Table in Excel

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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo