**Download the Practice Workbook**

**8 Useful Methods to Summarize Data in Excel**

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.

### Method 1 – Apply the AutoSum Option to Summarize Data

Let’s calculate the total sales.

**Steps:**

- Click on the cell where you want to display the sum. We have selected
**H4**. - Click on the AutoSum icon (Greek letter Sigma) in the Editing ribbon.
- Select the cells which contain the selling price.

- Close the parentheses.
- Click
**Enter**.

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

- Here’s the average data.

- You can calculate the number of entries, maximum, and minimum value by using the
**Count Numbers**,**Max,**and**Min**options, respectively. - Result of
**Count Numbers.**

- The result of
**MAX**.

- The result of
**MIN**.

### Method 2 – Use Excel Functions to Summarize Data

#### Case 2.1 – The **SUM **Function

**Steps:**

- Select cell
**H4**. - Insert the formula:

`=SUM(E4:E19)`

- Press
**Enter**and you will get exactly the same result.

#### Case 2.2 – The COUNT Function

**Steps:**

- Select Cell
**H6**. - Insert the following:

`=COUNT(E4:E19)`

#### Case 2.3 – COUNTA Function

We can only apply the **COUNT **function to the cells containing **numeric** values. COUNTA counts all values.

**Steps:**

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

`=COUNTA(D4:D19)`

#### Case 2.4 – The **AVERAGE **Function

**Steps: **

- In cell
**H5**, apply the**AVERAGE**function:

`=AVERAGE(E4:E19)`

#### Case 2.5 – The SUMIF Function

**Steps:**

- We want to see the sales of Burger only.
- Apply the following formula:

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

- We can also calculate the total sales in shop 1 by applying the following formula:

`=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)`

#### Case 2.6 – The SUMIFS Function

If we want to sum with more criteria, we have to use **the SUMIFS function**.

**Steps:**

- We want to know the number of sales for Burger from Shop 1.
- Apply the following formula.

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

- Hit
**Enter**and you will get the following result.

#### Case 2.7 – The COUNTIF Function

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

**Steps:**

- We want to calculate the number of orders with burgers across 3 shops. Use the following formula:

`=COUNTIF(D4:D19,"Burger")`

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

#### Case 2.8 – The **COUNTIFS** Function

We use **the COUNTIFS function** when counting on multiple conditions.

**Steps:**

- If we want to find out how many times
**Cathy**sold Burgers, we can use the following formula:

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

- Press
**Enter**and you should get the following result.

#### Case 2.9 – The AVERAGEIF Function

**Steps:**

- Here’s how to calculate the average value of sales for
**Cathy**:

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

- You should get the following result.

#### Case 2.10 – The AVERAGEIFS Function

**Steps:**

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

- Hit
**Enter**and you will get the following result.

### Method 3 – Apply the **Sort & Filte**r Option to Summarize Data

- Go to the
**Sort & Filter**option in the Editing ribbon to get more filter options.

- You can make the order from
**A**to**Z**, from**Z**to**A**, or apply**Custom Sort**. The first two 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’ll sort the data based on the Item column.

- Click on
**Custom Sort**and you’ll see a window 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**. - Click OK.

- We can perform many calculations easily as we have the data sorted. We will find the total burger, pizza, and hot dog sales.

- You can also play with other custom filter options and see which one is the most appropriate for you.
- If you choose
**Filter**, you will see an icon on each column header like below:

- By clicking on any of the icons, you can sort the data according to the values in that column. We have sorted our data based on Sell Prices from smallest to largest.

- Here is the result.

### Method 4 – Perform the **Subtotal **Command to Summarize Data

**Steps:**

- Select the data and go to the
**Data**tab.

- Click on
**Subtotal**in the Outline group.

- You will see a pop-up like this.
- Check the options as in the image 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.

### Method 5 – Create an Excel Table to Summarize Data

**Steps:**

- Select all the cells and select the
**Format as Table**option from the**Styles**ribbon.

- Choose any suitable design from below.

- A window will pop up like this. Check the box and click
**OK**

- You will get a table. It has the filter in the header columns like the
**Sort & Filter**method.

- Go to the
**Table Design**tab and check**Total Row.**

- A row will be added below the table with totals.

- We have calculated in the
**Sell Price**column. Each cell in the row will have a dropdown menu containing multiple functions.

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

### Method 6 – Utilize the **Slicer **Feature in an Excel Table for Summarizing Data

**Steps:**

- Go to the
**Table Design**tab and select the**Insert Slicer**option.

- Check
**Shop ID**and click**OK**.

- You will see another window with buttons.
- Select any
**Shop ID**and the table will show the data only related to this shop. We have shown this for**Shops 1 and 2**.

- Here’s the filter for
**Shop 2**.

### Method 7 – Run the **Data Analysis ToolPak** to Summarize Data

**Steps:**

- Go to the
**File**tab, then click on**Options**.

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

- 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 top-right corner.

- Click on Data Analysis. A dialog box like this will open up.

- Click on
**Descriptive Statistics**and click**OK**. A new window will pop up.

- Input the as shown in the figure with color boxes. In the input range, select the column that contains numeric values. 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**. - Click
**OK**. You will get the result as follows.

**Method 8 – ****Use a Pivot Table to Summarize Data**

**Steps:**

- Select the data cells and go to
**Insert**tab. - Click on
**PivotTable**.

- Click on
**Existing Worksheet**and choose a suitable cell to place the pivot table. - Click
**OK**.

- This generates a new table.

- On the right side, you will see
**PivotTable Fields.**

- Drag and drop the ranges in the corresponding fields like below.

- We have created a table which contains your desired summary of data.

**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 large quantity of data, use the
**Pivot table**method - You should only use the
**Data Analysis****Toolpack**if you need in-depth statistical analysis like**Skewness, Kurtosis**, etc.

## 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**