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.

**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**B****4: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**B****4:B19.**

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

### 3. Apply **Sort & Filte**r 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 Statistic**s 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.

## Summarize Data in Excel: Knowledge Hub

- How to Summarize Text Data in Excel
- How to Summarize Data by Multiple Columns in Excel
- How to Summarize Data Without Pivot Table in Excel
- How to Create Summary Table in Excel
- How to Create Summary Table from Multiple Worksheets in Excel
- How to Summarize Subtotals in Excel
- How to Summarize a List of Names in Excel
- How to Group and Summarize Data in Excel
- How to Create a Summary Sheet in Excel
- How to Make Summary in Excel From Different Sheets

**<< Go Back to Data Analysis with Excel | Learn Excel**