How to Summarize Data in Excel (8 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Summarizing data and presenting it in a concise manner is an essential skill in this data-driven world. If you need to summarize data in Excel and don’t know how to accomplish it, you have come to the right place. In this article, we will show you how to summarize data in Excel with the 8 most effective and easy methods. So let’s get started!

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Summarizing Data.xlsx

8 Useful Methods to Summarize Data in Excel

In this section, we will demonstrate 8 effective methods to summarize data in excel. To show how those methods work, we have created a scenario. Here, we have some sales data for a fast food company on a particular day. This company has 5 shops in different places, each with two salespersons. Their selling items are burgers, pizza, and Hot dogs. The sheet below gives us the information about the selling of the items, their price, the salesperson who sold them, and the shop number from where the items were sold.

1. Apply AutoSum Option to Summarize Data

Now we want to summarize the data given below.

Let’s first calculate the total amount of sales. We can do that by using AutoSum functions. Follow the steps below.

Steps:

Click on the cell where you want to display the sum. Here we have selected H4. Now go to the Editing ribbon and select this icon shown in the screenshot below.
Now select the cells which contain the selling price.

Click Enter. You’ll see the sum of all the sell prices.

Now if You click on the down arrow adjacent to the AutoSum icon, you will see 4 more options:

Similar to the Sum option, you can use these functions to summarize your data.
The Average function will give you the average value of the data.

And here you get the average data.

Similarly, you can calculate the no of entry, maximum, and minimum value by using the Count Numbers, Max, and Min options respectively.
Result of Count Numbers.

The result of MAX is here.

The result of MIN is achieved.

2. Use Excel Functions to Summarize Data

You can also manually use different built-in functions to summarize your data. Below, some functions and their usages are given.

2.1 SUM Function

We have already seen the use of the SUM function in the 1st method. We can also type the formula manually, to sum up, the cell values like below.

Steps:

Select Cell H4. Write down the formula. The formula is,

=SUM(E4:E19)

Press Enter and you will get exactly the same result.

2.2 COUNT Function

Here, we input the COUNT function manually and got the same result.

Steps:

Select Cell H6. Type the formula given below,

=COUNT(E4:E19)

And we have got our result.

2.3 COUNTA Function

We can only apply the COUNT function to the cells containing numeric values. For cells that contain non-numeric values, we must use the COUNTA function.

Steps:

If we use the COUNTA function for cells D4 to D19, we’ll get the same answer.

=COUNTA(D4:D19)

2.4 AVERAGE Function

Similar to the SUM and Count functions, we can also use the AVERAGE function manually and will get the same result.

Steps:

In cell H5, apply the AVERAGE function.

=AVERAGE(E4:E19)

2.5 SUMIF Function

Steps:

To sum data conditionally, we can use the SUMIF function. Suppose, we want to see how much sales were from Burger only. We can apply the following formula:

=SUMIF(D4:D19,"Burger",E4:E19)

Here,

D4:D19 is the Item Data range.
“Burger” is the item for which we want to sum.
E4:E19 is the range of the Sell price.

We can also calculate the total sales in shop 1 only and total sales made by Cathy by applying the following formula:
The formula for calculating total sales in shop 1:

=SUMIF(B4:B19,1,E4:E19)

Here is the result.

The formula for calculating the total sales made by Cathy.

=SUMIF(C4:C19,"Cathy",E4:E19)

And the result should be like this:

2.6 SUMIFS Function

If we want to sum data by giving more than 1 criteria, we have to use the SUMIFS function.

Steps:

Suppose we want to know the number of sales for Burger from Shop 1. Hence, we write down the following formula.

=SUMIFS(E4:E19,D4:D19,"Burger",B4:B19,1)

Where,

E4:E19 is the Selling Price range which needs to be the sum.
D4:D19 is the Item range where the 1st criteria will be searched.
“Burger” is the 1st criteria that are to be satisfied in the range D4:D19.
B4:B19 is the Shop ID range where the 2nd criteria will be searched.
1 is the 2nd criteria that are to be satisfied in range B4:B19.

Click Enter and you will get the following result :

2.7 COUNTIF Function

We can use the COUNTIF function to calculate the number of entries for a certain condition.

Steps:

Suppose we want to calculate the no of times burgers were sold across 3 shops. We can use the following formula:

=COUNTIF(D4:D19,"Burger")

Where,

D4:D19 is the Item range where the number of cells that meet the criterion will be counted.
“Burger” is the criterion that is to be satisfied in the range D4:D19.

Press Enter and you should see the following result.

2.8 COUNTIFS Function

Like the SUMIFS function, we use the COUNTIFS function when counting on multiple conditions.

Steps:

For example, if we want to find out the number of times Cathy sells Burger, we can use the following formula:

=COUNTIFS(C4:C19,"Cathy",D4:D19,"Burger")

Where,

C4:C19 is the Sales Person range where a number of cells that meet the criteria will be counted.
“Cathy” is the 1st criteria that are to be satisfied in range C4:C19.
D4:D19 is the Item range where the 2nd criteria will be searched.
“Burger” is the 2nd criteria which are to be satisfied in the range D4:D19.

Now press Enter and you should get the following result.

2.9 AVERAGEIF Function

Steps:

We want to calculate the average value of sales of Cathy, we can use the following formula with the AVERAGEIF function:

=AVERAGEIF(C4:C19,"Cathy",E4:E19)

Where,

C4:C19 is the Sales Person range where the criteria are to be satisfied.
“Cathy” is the criteria that are to be satisfied in range C4:C19.
E4:E19 is the Selling Price range in which the cells that meet the criteria will be averaged.
“Burger” is the 2nd criteria which is to be satisfied in the range D4:D19.

You should have the following result.

2.10 AVERAGEIFS Function

Steps:

If we want to use multiple criteria then we have to use the AVERAGEIFS function. Suppose, we want to calculate the average amount of sales of Burger from shop 1. Use the following formula,

=AVERAGEIFS(E4:E19,D4:D19,"Burger",B4:B19,1)

Where,

E4:E19 is the Selling Price range in which the cells that meet the criteria will be averaged.
D4:D19 is the Item range where the 1st criteria will be searched.
“Burger” is the 1st criteria that are to be satisfied in the range D4:D19.
B4:B19 is the Shop ID range where the 2nd criteria will be searched.
1 is the 2nd criteria which is to be satisfied in range B4:B19.

After clicking Enter key, you will have the following result.

3. Apply Sort & Filter Option to Summarize Data

By applying Sort & Filter option, we can quickly make the data more organized and easy to read. To use the option first select the cells and go to the Sort & Filter option in the Editing ribbon, just on the right side of the AutoSum option.

Steps:

Here, you will see that you have many options to sort out the data. You can make the order from A to Z, Z to A, and of course, you can also do the Custom Sort. The First 2 options sort the data based on the first column. If you want to do sorting based on other columns you have to choose the Custom Sort option. We will try the Custom Sort. We’ll sort the data based on the Item column.

Let’s click on the Custom Sort and we will see a window popping up like this.

From the Column drop-down list, select Item.
From Sort On the drop-down list, select Cell Values.
From the Order drop-down list, select A to Z.

And now you will see the list as sorted below based on Item:

Now we can perform many calculations easily as we have the data sorted according to our desire. For example, we will find out the total burger, pizza and hot dog sells like below.

You can also play with other custom filter options and see which one is the most appropriate for you.
Now there is also a Filter option. By choosing the Filter option, you can filter data according to your choice. Let’s see how it works.
If you choose the Filter, you will see an icon on each column header like below:

By clicking on any of the icons, you can sort out data accordingly. Here we have sorted out data based on Sell prices from small to large numbers.

Here is the result.

You can also check the other amazing features of the Filter option from our website.

4. Perform Subtotal Command to Summarize Data

This is a great tool to summarize data in groups. Suppose we want to know the sum of sales for each shop. We can easily do that by applying the Subtotal Command.

Steps:

First, select the data, and go to the Data tab.

Click on the Subtotal in the outline ribbon.

You will see a pop-up like this.

Check the above options and click OK. You will see the following results.

You can also find the average, maximum, and minimum values of each subgroup. Visit our website to learn more about the Subtotal option.

5. Create an Excel Table to Summarize Data

You can quickly create Excel Table to do the same things as in the Sort and Filter Option.

Step 1:

First, select the cells and select the Format as Table option from the Styles ribbon.

Now choose any suitable design from below.

A window will pop up like this. Select the following and click OK

You will see a table like this. Note that it has the filter in the header columns like Sort & Filter method.

Step 2:

Now you go to the Table Design tab and select check Total Row.

A row will be added below the table.

Here we have calculated in the Sell Price column. You will also find that each cell in the row will have a dropdown menu containing multiple functions like this.

You can use those functions to directly calculate the value you want. For example, If we want to calculate the number of entries, we have to select the Count option.

You can also check the other functions by yourself.

6. Utilize the Slicer Feature in Excel Table for Summarizing Data

Steps:

Slicer is a great tool for quickly summarizing the data in a Groupwise manner of an excel table. Again go to the Table Design tab, and select the Insert Slicer option.

You will see a window like this. Check the Shop ID and click OK.

You will see another window like this.
Now you can select any Shop ID and the table will show the data only related to this shop. Below we have shown this for Shops 1 and 2.

And for Shop no 2.

You can also use multiple slices like this. Check our websites for more details.

7. Run Data Analysis Toolpak to Summarize Data

The Data Analysis Toolpak is an excel Add-ins that we can use to perform extensive statistical analysis. To use this add-in, we must first enable it. Follow the steps below to use this toolkit.

Step 1:

Go to the File tab, then click on Option. A dialogue like this will pop up.

Go to Add-ins, select Analysis ToolPak, and click on Go. A new window will pop up.

Now check on the Analysis ToolPak and click OK. This will enable the Data analysis tool on the Data Tab which you will find in the right-most corner.

Click on that. A dialogue box like this will open up.

Step 2:

Click on the Descriptive Statistics and click OK. Then a new window will pop up like this.

Input the following as shown in the figure with color boxes. In the input range, select the column that contains numeric values. Here we have selected the Sell Price column.
In the Output Range, Select the cell where you want to place your Summary statistics. We have selected G4.
Check the Labels in the first row and Summary statistics. And then click OK. You will get the result as follows.

This method is indeed the easiest way to get all the necessary statistical information with just a few clicks of the mouse.

8. Use Pivot Table to Summarize Data

Pivot Table is a great tool to summarize data. Follow the steps below to use the Pivot table for summarizing data.

Steps:

Select the data cells and go to insert Tab. Click on the Pivot Table.

You will see a window like this. Now click on the Existing Worksheet and choose a suitable cell to place the pivot table. Now, click OK.

That generates a new table.

On the right side, you will see PivotTable Fields.

Drag and Drop the tables in the corresponding fields like below.

And now you can see that we have created which contains your desired summary of data.

You can also try other combinations using Pivot Table for summarizing data in Excel.

Things to Remember

You should use the first method if your data is considerably small in quantity as it is the quickest one.
If you have a considerably large quantity of data, then you better use the Pivot table method
You should only use the Data Analysis Toolpack if you need in-depth statistical analysis like Skewness, Kurtosis, etc.

Conclusion

We hope that you have enjoyed this article about how to summarize data in excel. If you have any further queries, let us know by commenting on the post. If you find this article helpful, please share this with your friends and visit Exeldemy for more articles like this.

How to Summarize Data in Excel (8 Easy Methods)

Download Practice Workbook

8 Useful Methods to Summarize Data in Excel