How to Summarize Data by Multiple Columns in Excel

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Especially when it comes to summarizing data, there is no alternative to Excel. In this article, I will explain 5 easy ways in Excel to summarize data by multiple columns.


Download Practice Workbook

Download this workbook and practice while going through the article.


5 Easy Methods to Summarize Data by Multiple Columns in Excel

This is the dataset for today’s article. We have some employees along with their position, salary, and address. I will summarize this dataset.

excel summarize data by multiple columns


1. Use Filter Option to Summarize Data by Multiple Columns

The first method to summarize data by multiple columns is the use of the Filter feature. You can use this feature and filter your data to get a summary based on multiple columns. Let’s do it step by step.

Steps:

  • First of all, select the columns.
  • Then, go to the Data
  • After that, select Filter.

  • Excel will add a drop-down box to each column.

excel summarize data by multiple columns

  • Now, you can summarize your dataset.
  • Suppose you want to find out the employees who are Content Developer and live in Paris. To find out them,
  • Select the drop-down box of the Position

  • Then, select Content Developer from the list.
  • After that, click OK.

excel summarize data by multiple columns

  • Excel will show all the employees who are Content Developer.

  • Now, in a similar fashion, filter Paris from the Address
  • Then, press OK.

excel summarize data by multiple columns

  • Excel will get you summarized data based on the columns Position and Address.

Read More: How to Create a Summary Sheet in Excel (4 Easy Ways)


2. Create an Excel Table to Summarize Data by Multiple Columns

Now, I will explain another way to summarize data in Excel. This time, I will show how you can create a table and summarize data.

Steps:

  • First of all, select the entire dataset B4:E16.
  • Then, go to the Insert
  • After that, select Table.

excel summarize data by multiple columns

  • Create Table box will appear.
  • Check the box if your table has headers.
  • Then, press OK.

  • Excel will create a table. You will see the drop-down boxes in the headers.

excel summarize data by multiple columns

  • Now, you can summarize the data following method-1.

Read More: How to Group and Summarize Data in Excel (3 Suitable Ways)


3. Apply Slicers Feature to Summarize Data by Multiple Columns

In this section, I will show you how to apply Slicers and get summarized data in Excel.

Steps:

  • First, create a table following method 2.

excel summarize data by multiple columns

  • Then, go to the Table Design This tab will appear when you select any cell inside your table.
  • After that, select Insert Slicer.

excel summarize data by multiple columns

  • Insert Slicers box will pop up. Choose the slicers.
  • Then, click OK.

  • Position and Address slicers will appear.

excel summarize data by multiple columns

  • Suppose you want to find out the Team Leaders from Liverpool. Then, choose them from the slicers.

  • Excel will return the list.

excel summarize data by multiple columns

Read More: How to Summarize Data Without Pivot Table in Excel


4. Create a PivotTable to Summarize Data by Multiple Columns

Now, I will show another effective method to summarize data in Excel. This time, I will create a Pivot Table and summarize the dataset.

Steps:

  • First of all, select the dataset.
  • Then, go to the Insert Tab.
  • After that, select PivotTable.

excel summarize data by multiple columns

  • Excel will create a Pivot Table.

  • Now, drag Position and Address in the Filter field, Name in the Row field, and Salary in the Values Values field will automatically show the sum of Salary.

excel summarize data by multiple columns

  • Excel will edit the Pivot Table.

  • Now, you can filter the Position and Address to summarize the data.

excel summarize data by multiple columns

Read More: How to Summarize Data in Excel Using Pivot Table (2 Examples)


5. Apply Conditional Formatting to Summarize Data by Multiple Columns

Now, I will show how to apply conditional formatting to summarize data by multiple columns. You have to insert a customized formula for this purpose.

Steps:

  • Select the range B5:E16. Do not select the Headers.
  • Then, go to the Home
  • After that, select Conditional Formatting.
  • Finally, choose New Rule.

  • New Formatting Rule box will appear.
  • Select “Use a formula to determine which cells to format”.
  • After that, write down the following formula
=AND(C5="Content Developer",E5="Paris")
  • Then, set the format of the cells.
  • After that, click OK.

excel summarize data by multiple columns

  • Excel will format the cells.

  • After that, activate the Filter feature following method-1.
  • Then, select the drop-down of the Name column.
  • After that, select Filter by Color.
  • Then, choose Green
  • After that, click OK.

excel summarize data by multiple columns

  • Excel will get you the names.

excel summarize data by multiple columns

Read More: How to Summarize a List of Names in Excel (5 Effective Ways)


Things to Remember

  • Press CTRL+T to create a table in shortcut.
  • Do not select Headers in Conditional Formatting.

Conclusion

In this article, I have explained 5 ways in Excel to summarize data by multiple columns. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo