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

Get FREE Advanced Excel Exercises with Solutions!

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 you on how you can prepare a comparison table in Excel.

Table of Contents hide

Make a Comparison Table in Excel: 2 Methods

1. Comparison of Two Tables with Conditional Formatting

2. Comparison between Columns of Two Tables in Excel

Make a Comparison Chart from Table in Excel

Conclusion

Make a Comparison Table in Excel: 2 Methods

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 formula below 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, choose the color, and press OK to close the Format Cells dialog.

Again, press OK.

Finally, we will get the output below. 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 steps below 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 received. 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 table below. 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 make strategic decisions to improve sales revenue in a certain area. Let’s follow the instructions below 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 chart below. 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 a 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

Download Practice Workbook

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

Make a Comparison Table.xlsx

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.

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

Make a Comparison Table in Excel: 2 Methods

1. Comparison of Two Tables with Conditional Formatting

2. Comparison between Columns of Two Tables in Excel

Make a Comparison Chart from Table in Excel

Conclusion

Related Articles

Leave a reply Cancel reply