How Do You Summarize Data in Excel?

Introduction To Summarizing Data In Excel 

Data for Excel is a way of summarizing large amounts of data into a few numbers. For example, if you have 3,000 sales at $50 each, you could summarize this by saying that total sales were $150,000. The summarization of data in Excel is doable in many ways. 

Approximately 54% of businesses use Excel, which doesn’t include any other spreadsheet programs. Excel and Google Sheets are used by more than 2 billion people worldwide.

If you want an easy way to create simple summaries of numbers in Excel quickly, then use AutoSum! You can find this function on the Home tab > Number section > AutoSum button. It’s great for doing quick calculations, such as adding up columns or rows, without going through all the steps required to create an Excel formula. 

10 Ways to Summarize Data in Excel 

In this method, we will summarize the data in a single cell. This will help us make the initial data analysis very easy and fast. There are some easy steps you can follow to summarize your data: 

Get the Data Ready for Summarization 

First, you will need to import data from an external source. You can do this by clicking on the Data tab and selecting the From Other Sources option in the Get External Data group. 

Next, convert a range of cells into an Excel table so that you can create unique headings for each column of data and add descriptive captions for rows that contain no information other than column titles. Now use the AutoFilter feature to filter out unnecessary rows (e.g., those with incorrect data) or columns (e.g., headers). 

Finally, create a pivot table using this filtered data to display all possible summaries within one table! This is done by going back to “Get External Data” again but choosing PivotTable instead. 

Quick Summary With Auto Functions 

SUMIFS: Use the SUMIFS function to add values that meet multiple conditions. 

SUMIF: Add up values that meet a specific condition or criteria. 

COUNTIFS: Count cells that meet multiple criteria using the COUNTIF function. 

AVERAGEIFS: Calculate an average only if the cell value meets all of its criteria using AVERAGEIF in Excel, or use AVERAGE() for a more straightforward average calculation without any conditions.

MAXIFS: Find the maximum value from a range of cells where each row’s maximum is found by applying different conditions with MAX(). 

Fast Analysis With Sort and Filter 

Sorting and filtering are the most common ways to analyze data in Excel.

You can use sorting to arrange your data to make it easier for you to compare. For example, if you have a list of names, you can sort by last name or first name so that each person’s name is grouped based on their last or first name. You can also sort by more than one column at once—for example, sorting by the last name and then by the first name when arranging people’s names into alphabetical order.

Sorting does not change the values of any cells; instead, it reorders them so that the highest value appears at the top of your worksheet window, and each row has one number sorted over another (e.g., 1 > 2). Filtering lets you decide how much information from your dataset should appear onscreen while using less memory than other methods like PivotTables. So if you’re working with large datasets or lots of filters, it will help speed up your work! 

Summarize Data With SubTotal Feature 

You can create subtotals and grand totals in your Excel worksheets. For descriptive data summarization with the Subtotal feature, click on any cell within that column and then go to data> Subtotals.

The Subtotal dialog box will appear. You will have three choices from this dialog box:  

• Create subtotal below: This option creates a subtotal or a total below your original data source. 
• Create summary rows above: This option creates a subtotal or a total above your original data source. 
• Show all detailed records in a separate table: This option displays all detailed records as individual tables on top of one another (as shown below). This can be useful if you want to see both amounts together at once so you can easily compare them side by side (e.g., compare sales figures month-to-month). 

Summarize Data With an Excel Table 

Use the Table feature for the summarization of data for a single column. For example, show how well your sales reps get customers to sign up for a new service or product. If that’s the case, you can use an Excel table for descriptive data summarization of these results by each rep, then create a chart. To do this:

• Select any cell in your worksheet and select Insert > Table. 
• Choose options as needed and click OK when finished (e.g., selecting “Total Row”). 
• Use Total Row if you want totals at the bottom of each row rather than at the top of each column (like on Google Sheets). This option is useful when using Conditional Formatting since it will show all values instead of just summing them up for convenience’s sake!

If you want totals at the top of each column, choose No Total Row instead. This option is helpful if you’re using a chart to show how well your sales reps get customers to sign up for a new service or product. If that’s the case, you can use an Excel table to summarize these results by each rep and then create a chart.

To do this: 

• Select any cell in your worksheet and select Insert > Table. 
• Choose options as needed and click OK when finished (e.g., selecting “Total Row”). 
• Use Total Row if you want totals at the bottom of each row rather than at the top of each column (like on Google Sheets). 

Using Slicers to Summarize By Different Dimensions 

One way to summarize data using slicers is by using a single-dimension slicer. Once you select the cell in your table and click on the insert tab on the ribbon, you will see all the options available under the custom lists group. 

You can select one option in each category and create multiple slicers based on these different dimensions.

Using this method, you can sum up values over different dimensions of customer data like age group, gender, etc., making it easier for people unfamiliar with Excel or with limited knowledge of formulas.  

You can also use the above method to create a single slicer showing both summation and average values. This way, you can get a quick idea of how different dimensions contribute to each cell’s overall value. 

Summarize With Excel Pivot Tables 

You can use pivot tables to summarize large amounts of data in an existing worksheet and across multiple worksheets. For example, you can create a pivot table that summarizes information by different dimensions (for example, year, month, or product category) or use a single pivot table to summarize data from multiple worksheets. 

Pivot tables are dynamic and automatically adjust when you change the source data they’re based on. This means that if you double-click any value in your pivot table, Excel will automatically calculate all corresponding values so that they’re always accurate and up-to-date. 

Cross-Tabulation Pivot Tables: This type of pivot table is called a crosstab because it allows you to create cross tables that show values in multiple rows and columns. For example, use this pivot table to summarize your sales figures by product category and year. You’ll get an exact breakdown of how much money each product category brought in during each year. 

Summarize Data With Excel Functions 

• SUM – Use this function to find the sum of the values in a column. If you have a list of numbers in cells A1:A10, you can use the following formula in cell B1 to calculate their sum: 
• AVERAGE – Use this function to find the average of the values in a column. If you have a list of numbers in cells A1:A10, you can use the following formula in cell B1 to calculate their average: 
• COUNT – Use this function to count how many items are present in any range that contains data. In cell C5, for example, if we wanted to count how many times “Apple” appears as part of our product names (in columns D through H), we could enter =COUNT(D5:H5) into cell C5, and that would return 4 (or whatever number corresponds with how many times Apple appears). 

Advanced Excel Functions for Summarizing Data 

Descriptive statistics are often used for data summarization of a data set. The most common descriptive statistics are: mean, median, and mode for continuous variables; and count proportion and relative frequency for categorical variables. 

A summary table is a collection of statistical values calculated from the original data with formulas in Excel. You can easily create a summary table as shown below, with or without using the PivotTable feature in Excel 2010 or 2013 versions.

A summary table can be created using several Excel functions, such as SUM and AVERAGE. These functions are used to calculate various statistics from data, such as the mean, median, mode, and variance for continuous variables, and count proportion and relative frequency for categorical variables. 

Summarize With Descriptive Statistics From Analysis Toolpak 

Descriptive statistics are a set of techniques used to summarize data. They can summarize data, identify patterns, and make predictions. The most common descriptive statistics include the following: 

• The mean or average of the values in a distribution 
• The median (middle value) of a distribution 
• Variance, which measures how spread out the values in a distribution are from their average value (mean) or from each other

Conclusion 

Descriptive data summarization is an integral part of any analysis, and it can be done manually, but it is time-consuming and prone to errors. Excel offers many functions that can help create summaries quickly and accurately by using filters or pivot tables. There are also other functions like SUMIFS and AVERAGEIFS, which are only available in Excel. You can acquire these skills and more from the comfort of your home by joining UNext Jigsaw’s Certificate Program in People Analytics & Digital HR program.