Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Summarize Data Without Pivot Table in Excel

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.

How to Summarize Data Without Pivot Table in Excel

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.

Use AutoSum Tool to Summarize Data in Excel

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

Use AutoSum Tool to Summarize Data in Excel

  • 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:

Summarize Data Without Pivot Table Using Subtotal Feature

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

Summarize Data Without Pivot Table Using Subtotal Feature

  • Therefore, you will see the Subtotal window pop up.

Summarize Data Without Pivot Table Using Subtotal Feature

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.

Summarize Data Without Pivot Table Using Subtotal Feature

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

Excel Summarize Data Without Pivot Table

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.

Apply SUBTOTAL Function to Add Data in Excel

  • Now, insert this formula in cell C15.
=SUBTOTAL(9,C5:C14)

Apply SUBTOTAL Function to Add Data in Excel

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.

Data Summarizing with Sort & Filter Tool

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

Data Summarizing with Sort & Filter Tool

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

Data Summarizing with Sort & Filter Tool

  • 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.

Data Summarizing with Sort & Filter Tool

  • 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.

Data Summarizing with Sort & Filter Tool

  • 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.

Data Summarizing with Sort & Filter Tool

  • 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.

Summarize Data with Excel Table

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

Summarize Data with Excel Table

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

Summarize Data with Excel Table

  • 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.

Summarize Data with Excel Table

  • 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.

Use Slicer to Get Added Values in Worksheet

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

Use Slicer to Get Added Values in Worksheet

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

Use Slicer to Get Added Values in Worksheet

  • 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.

Insert SUMIF Function to Sum Data Without Pivot Table

  • 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)

Insert SUMIF Function to Sum Data Without Pivot Table

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

Note: You can also apply the IF function with the combination of 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 (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.

Apply Descriptive Statistics to Summarize Data in Excel

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

Apply Descriptive Statistics to Summarize Data in Excel

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

Apply Descriptive Statistics to Summarize Data in Excel

  • 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.

Summarize Data Without Pivot Table Using Consolidate Tool

  • 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.

Summarize Data Without Pivot Table Using Consolidate Tool

  • 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.

Combine and Compare Data with Excel VBA

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

Combine and Compare Data with Excel VBA

  • 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

Combine and Compare Data with Excel VBA

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

  • Next, click on Run on the Macros window.

Combine and Compare Data with Excel VBA

  • 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.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo