Working on loads of data often seems difficult, especially if it has repetitive information. In this situation, most Excel users use a pivot table to summarize that data. But apart from that, excel has many other ways to summarize data without a pivot table. In this article, we will learn how to summarize data without a pivot table in Excel in 10 easy ways.

**Table of Contents**Expand

## How to Summarize Data Without Pivot Table in Excel: 10 Easy Ways

Let us take an example for describing the methods. Here, the dataset shows the information on amounts spent on a trip by 5 friends.

As you can see some participants spent money more than once. So it gets difficult to gather all the amounts at once spent by that single person. Therefore, we will use the methods below to summarize these data without a pivot table.

### 1. Use AutoSum Tool to Summarize Data in Excel

In this first method, we will use the **AutoSum** tool to get the summation of the values in a single click. Let’s check the process:

- First, select
**cell C15**because we want the output in this cell. - Then, go to the
**Home**tab and select**AutoSum**under the**Editing**group.

- Right after that, you will see that
**cell C15**is already showing**the SUM formula**with the reference cell.

- Now, press
**Enter**. - Finally, you will get the total of the spent amounts.

- Along with it, you can also get the information on
**average**,**minimum**or**maximum**amounts from the dataset by clicking on the options from the drop-down below:

**Read More: **How to Create a Summary Sheet in Excel

### 2. Summarize Data Without Pivot Table Using Subtotal Feature

Here, we will use the** Subtotal **feature in Excel to summarize data on the worksheet. Follow the process below:

- In the beginning, organize the dataset according to the names like this:

- Then, select
**cell range B4:C14**. - After that, go to the
**Data**tab and select**Subtotal**under the**Outline**group.

- Therefore, you will see the
**Subtotal**window pop up.

Here, we want to summarize each participant’s amount individually and therefore put Participants in the At each change in box. Then, we want the total of each participant’s spent amount, so insert Sum in the Use function section. Lastly, we want a subtotal of the amounts so marked check the Amount box.

- After selection, press OK.
- Now, you can see the subtotal amounts are visible in the dataset.

- Along with it, you can use the
**Plus**(**+**) and**Minus**(**–**) icons to toggle the cells like this:

**Read More: **How to Summarize Text Data in Excel

### 3. Apply SUBTOTAL Function to Add Data in Excel

People often mix up **the SUBTOTAL function** with the **Subtotal** feature. Though we get the same output from both of them, the process is different. Let’s see how it works.

- First, organize the dataset according to names.

- Now, insert this formula in
**cell C15**.

`=SUBTOTAL(9,C5:C14)`

Here, the argument **function_num** requires the serial number from the list. Therefore, we entered **9** as we wanted the **SUM** of the values.

- Then, press
**Enter**. - That’s it, you will see the total in the selected cell.

**Read More: **How to Summarize Subtotals in Excel

### 4. Data Summarizing with Sort & Filter Tool

The **Sort & Filter** tool is very effective for data summarizing in Excel. Simply go through the steps below:

- Firstly, select
**cell range B4:C14**. - Secondly, go to the
**Home**tab and click on**Sort & Filter**.

- Thirdly, select
**Sort A to Z**from the**drop-down**section.

- Afterward, you will see that the names are sorted according to alphabetical order.

- Now, as we sorted the names, simply select any consecutive cells adjacent to a similar name.
- For example, here we selected
**cell range C5:C7**.

- Then, look at the bottom of the tabs and you will see the
**SUM**,**AVERAGE**and**COUNT**values all at once.

- So far, we have done the sorting part, now let’s move on to filtering them.
- For this, select the
**Filter**option under the**Sort & Filter**section.

- Following, you will see arrows on the dataset titles.
- Click on the one with the title
**Participant**. - Then, select any name to filter. For example, we selected
**Pamela**.

- After this, press
**OK**. - Finally, you can see only the selected names and their relevant values.

**Read More: **How to Summarize a List of Names in Excel

### 5. Summarize Data with Excel Table

Using the **Table** tool is a very effective one for summarizing data in Excel. Simply follow the steps below:

- In the beginning, go to the
**Home**tab and select**Format as Table**under the**Styles**section.

- Here, you will see numerous types of tables to choose from.

- After that, you will be directed to the
**Create Table**window. - Insert the
**cell range B4:C14**and hit**OK**.

- Now, the table is created like this:

- Thereafter, select any cell inside it to enable the
**Table Design**tab on the ribbon. - Now, go to the
**Table Design**tab and mark the**Total Row**box.

- Lastly, you will notice a new row to input any total value with a filter icon beside it.
- Select any option from it to get your desired output.

### 6. Use Slicer to Get Added Values in Worksheet

Basically, **Slicer** is the additional feature to filter data in **Excel Table**. Let’s see how it works.

- First, create a table following the method above.
- Then, go to the
**Table Design**tab and select**Insert Slicer**.

- Then, you will see the
**Insert Slicers**window asking for the option for creating a slicer. - Here, select the option
**Participant**and press**OK**.

- After this, you will have the participants’ name list.

- Select any one of them and you will get the summarized output like this:

### 7. Insert SUMIF Function to Sum Data Without Pivot Table

At this stage, we will apply **the SUMIF function** to sum data based on the range of cells selected. It will work on the conditions** true** or** false**, where the conditions are referred to as **criteria**. Follow the process below:

- First, create a new table with the same titles of the dataset.

- For instance, we want to know the amount spent by
**Erin**. - Therefore, insert the name in
**cell E5**. - Then, insert this formula in
**cell F5**.

`=SUMIF(B5:B14,E5,C5:C14)`

- After this, press
**Enter**. - Finally, you will see the total amount spent.

**Note**

**:**You can also apply

**the**

**IF function**with the combination of the

**SUMIF**function to get the same value. The formula will be:

**=IF(B9=B4,””,SUMIF(B:B,B9,C:C))**

**Read More: **How to Group and Summarize Data in Excel

### 8. Apply Descriptive Statistics to Summarize Data in Excel

**Descriptive Statistics** helps to find out all types of data regarding the selected numeric values. Let’s check the process below:

- First, go to the
**Data**tab and click on**Data Analysis**.

- Then, select
**Descriptive Statistics**among the**Analysis Tools**and hit**OK**.

- After that, insert
**Input**and**Output Range**. - Along with that, mark checked the
**Summary Statistics**box.

- Next, press
**OK**. - Finally, you will see the detailed statistics of the numeric values.

### 9. Summarize Data Without Pivot Table Using Consolidate Tool

Another method to summarize data is to use the** Consolidate** tool in Excel. It will help to combine a certain number of information based on the given condition according to the dataset. To perform, follow the steps below:

- Firstly, go to the
**Data**tab and select the**Consolidate**icon under the**Data Tools**group.

- Then, the
**Consolidate**window pops up. - Here, insert the
**Function****Sum**. - Besides, insert the cell range
**B5:C9**as**Reference**. - Also, keep the
**Left Column**box checked.

- After this, press
**OK**. - Now, you can see the summarized data just beside the original one.

- You can apply any other function as well in the
**Function**box.

**Read More: **How to Summarize Data by Multiple Columns in Excel

### 10. Combine and Compare Data with Excel VBA

In this last method, let us apply **VBA Macro **code to find the repetitive names in a nutshell. Just follow the process below:

- In the beginning, go to the
**Developer**tab and select**Visual Basic**.

- Then, in the new window, select
**Module**under the**Insert**section.

- After that, insert this code on the blank page:

```
Sub ListDuplicates()
For x = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("B:B"), Range("B" & x)) > 1 Then
If Application.WorksheetFunction.CountIf(Range("E:E"), Range("B" & x)) = 0 Then
Range("E" & Cells(Rows.Count, "E").End(xlUp).Row + 1).Value = Range("B" & x).Value
End If
End If
Next x
End Sub
```

- Following, press
**F5**or click on the**Run Sub**button.

- Next, click on
**Run**on the**Macros**window.

- Finally, you will get the repetitive names beside the dataset.

**Download Workbook**

Get the sample file to try it yourself.

## Conclusion

I hope it is an efficient article on how to summarize data without a pivot table in Excel. Let us know your suggestions in the comment box.