Sanjida Mehrun Guria
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.
Download Workbook
Get the sample file to try it yourself.
10 Easy Ways to Summarize Data Without Pivot Table in Excel
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 (4 Easy Ways)
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 Data in Excel Using Pivot Table (2 Examples)
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 (3 Easy Methods)
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 Effective Ways)
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.
=IF(B9=B4,””,SUMIF(B:B,B9,C:C))
Read More: How to Group and Summarize Data in Excel (3 Suitable Ways)
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 informations based on the given condition according to the dataset. To perform, follow the step 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 Make Summary in Excel From Different Sheets
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.
Conclusion
I hope it is an efficient article on how to summarize data without a pivot table in excel. Let us know your suggestions. Follow ExcelDemy for more blogs like this.
