Afia Kona
If you want to get summary statistics in Excel, you have come to the right place. Here, we will walk you through 7 easy and effective methods to do the task effortlessly.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
7 Methods to Get Summary Statistics in Excel
The following dataset has the Name, Math Score, and Science Score columns. Using this table, we will go through 7 easy and effective methods to get summary statistics in Excel. Here, we used Microsoft Office 365 to do the task. You can use any available Excel version.
1. Use of Status Bar to Get Summary Statistics in Excel
In this method, we will use the Status Bar to get summary statistics in Excel.
Steps:
Let’s go through the following steps to do the task.
- First of all, we select cells C5:C12 of the Math Score column.
- Then, in the Status Bar, you can see the Average, Count, and Sum of the selected cells.
Here, however, Maximum and Minimum are missing.
- Next, to add Maximum and Minium to the Status Bar, we will right-click on the Status Bar.
- At this point, mark Minimum and Maximum from the Customize Status Bar.
Here, you can mark any item that you want to show on your Status Bar.
- Furthermore, we select cells C5:C12 of the Math Score column.
Then, in the Status Bar, you can see the Average, Count, Min, Max, and Sum of the selected cells.
Hence, you can get summary statistics in Excel.
Read More: How to Show Menu Bar in Excel (2 Common Cases)
2. Applying SUM, AVERAGE, MAX, MIN, and COUNT Functions
In this method, we will use the SUM, AVERAGE, MAX, MIN, and COUNT functions to get summary statistics in Excel.
Steps:
Let’s go through the following steps to do the task.
First, we will calculate the Sum of the Math Score.
- To do so, we will type the following formula in cell C16.
=SUM(C5:C12)
- After that, press ENTER.
As a result, you can see the Sum of the Math Score in cell C16.
- Furthermore, we will type the following formula in cell C17 to calculate the Count of the Math Score.
=COUNT(C5:C12)
- At this point, press ENTER.
As a result, you can see the Count of the Math Score in cell C17.
- Next, we will type the following formula in cell C18 to calculate the Average of the Math Score.
=AVERAGE(C5:C12)
- Moreover, press ENTER.
Then, you can see the Average of the Math Score in cell C18.
- Furthermore, we will type the following formula in cell C19 to calculate the Minimum of the Math Score.
=MIN(C5:C12)
- Next, press ENTER.
Therefore, you can see the Minimum of Math Score in cell C19.
- After that, we will type the following formula in cell C20 to calculate the Maximum of the Math Score.
=MAX(C5:C12)
- Furthermore, press ENTER.
Hence, you can see the Minimum of Math Score in cell C19.
- Afterward, we follow a similar procedure to calculate the summary statistics of the Science column.
Read More: How to Calculate Root Mean Square Error in Excel
3. Using Data Analysis ToolPak to Get Summary Statistics for One Quantity
In this method, we will use the Data Analysis toolpak to get summary statistics in Excel. This is a quick and handy method to get all the statistics summaries.
Let’s go through the following steps to do the task.
Step-1: Enabling Analysis ToolPak
In case your Data Analysis ToolPak is not enabled, you can follow the following steps to enable the Data Analysis toolpak.
- First, go to the File tab.
- Then, select Options.
At this point, an Excel Options dialog box will pop up.
- Then, from Add-ins >> click on Go.
Next, an Add-ins dialog box will pop up.
- After that, mark on Analysis Toolpak >> click OK.
This will add the Data Analysis toolpak.
Step-2: Getting Summary Statistics Using Data Analysis Toolpak
In this step, we will use the Data Analysis toolpak to get summary statistics in Excel.
- In the beginning, from the Data tab >> we will select Data Analysis.
Next, a Data Analysis dialog box will appear.
- Then, from Analysis Tools >> select
- Moreover, click OK.
At this point, a Descriptive Statistics dialog box will appear.
- Then, select cells C4:C12 as Input Range.
- Furthermore, as our data has a header, we will mark Labels in first row.
- Moreover, we will select Output Range as the output options.
- Along with that, we will select cell F2 in the Output Range box.
- In addition, mark Summary statistics.
- Next, click OK.
As a result, you can see the summary statistics in Excel.
Read More: Descriptive Statistics – Input Range Contains Non-Numeric Data
4. Use of Quick Analysis Feature
In this method, we will use the Quick Analysis feature to get summary statistics in Excel.
Let’s go through the following steps to do the task.
Step-1: Empowering Quick Analysis Feature
In case your Quick Analysis feature is not enabled, you can follow the following steps to enable the Quick Analysis feature.
- First of all, go to the File tab.
- Afterward, select Options.
At this point, an Excel Options dialog box will appear.
- After that, go to the General option.
- Afterward, from the User Interface options >> mark Show Quick Analysis options on selection.
This will enable the Quick Analysis feature when you will select a range of values.
Step-2: Obtaining Summary Using Quick Analysis Feature
In this step, using the Quick Analysis feature, we will get summary statistics in Excel.
- First, we will select the cells C5:D12.
- After that, we will click on the Quick Analysis feature which is on the left bottom side of the selected data, marked with a red color box.
Here, you can select the cells C5:D12 and press CTRL+Q to bring out the options of the Quick Analysis feature.
- Furthermore, select Totals.
Next, you can see in the Total option the Sum, Average, and Count along with some other options.
- Further, we will hover our mouse on the Sum, and you can see the Sum of Math Score and Science Score in cells C13 and D13 respectively.
- At this point, we will click on Sum.
As a result, you can see the Sum of the Math Score and Science Score in cells C13 and D13 respectively.
- Moreover, we will hover our mouse on the Average, and you can see the Average of Math Score and Science Score in cells C13 and D13 respectively.
- At this point, we will click on Average.
Hence, you can see the Average of the Math Score and Science Score in cells C13 and D13 respectively.
- Afterward, we will hover our mouse on the Count, and you can see the Count of the Math Score and Science Score in cells C13 and D13 respectively.
- Next, we will click on Count.
Therefore, you can see the Count of the Math Score and Science Score in cells C13 and D13 respectively.
Read More: How to Make a Box Plot in Excel (With Easy Steps)
Similar Readings
- How to Set Intervals on Excel Charts (2 Suitable Examples)
- How to Remove Last Modified By in Excel (3 Ways)
- If a Value Lies Between Two Numbers Then Return Expected Output in Excel
- How to Move Up and Down in Excel (5 Easy Methods)
5. Using Table Feature to Get Summary Statistics in Excel
In this method, we will use a Table to get summary statistics in Excel.
Let’s go through the following steps to do the task.
Step-1: Inserting Table
In this step, we will insert a Table.
- First of all, we will select the entire dataset by selecting cells B4:D12.
Here, you can select the entire dataset by clicking on cell B4 and pressing CTRL+SHIFT+Right arrow+Down arrow.
- Then, from the Insert tab >> select Table.
At this point, a Create Table dialog box will pop up.
- Here, make sure My table has headers is marked.
- Then, click OK.
As a result, you can see the Table.
Step-2: Acquiring Summary Statistics Using Table
In this step, we will use the Table to get summary statistics in Excel.
First, we will click on a cell of the Table.
- Here, we clicked on cell D9.
This will bring out a Table Design tab.
- Then, from Table Style Options >> select Total Row.
Then, you can see the Total in cell D13.
Along with that, you can see two drop-down arrows in cells C13 and D13.
- Next, we will click on the drop-down arrow of cell C13.
As a result, you can see several statistics term in the drop list.
- Furthermore, we will select Sum from the drop list.
Hence, you can see the Sum of the Math Score in cell C13.
- Moreover, we will click on the drop-down arrow of cell D13.
- Along with that, we will select the Average from the drop list.
Then, you can see the Average of the Science Score in cell D13.
Therefore, in a similar way, you can find out other statistical terms as well.
Read More: [Fixed!] Up and Down Arrows Not Working in Excel (8 Solutions)
6. Inserting Pivot Table to Get Summary Statistics in Excel
In this method, we will insert a Pivot Table to get summary statistics in Excel.
Let’s go through the following steps to do the task.
Step-1: Adding Pivot Table
In this step, we will add a Pivot Table.
- First of all, we will select the entire dataset by selecting cells B4:D12.
- Next, from the Insert tab >> select PivotTable.
At this point, a Pivot Table from table or range dialog box will pop up.
- Then, we will select the Existing Worksheet.
- In addition, we will select cell B15 as the Location.
- Moreover, click OK.
As a result, a Pivot Table will be created in the Existing Worksheet.
Step-2: Gaining Summary Statistics Using Pivot Table
In this step, we will use the Pivot Table to get summary statistics in Excel.
Here, we will find out the summary statistics for Math Score.
- In the beginning, from PivotTable Fields >> we will mark Math Score.
- Along with that, we will drag the Math Score into the Values group.
Therefore, you can see the Sum of Math Score in cell B16.
Next, we will find out the Count of the Math Score.
- To do so, from PivotTable Fields >> we will mark Math Score.
- Along with that, we will drag the Math Score into the Values group.
As a result, you can see the Sum of Math Score2 in cell C16.
- After that, we will right-click on cell C15 >> select Summarize Values By from the Context Menu.
- Along with that, we will select Count.
- In a similar way, we will find out the Average, Maximum, and Minimum of the Math score.
Hence, you can see the Average of Math Score in cell D16, Max of Math Score in cell E16, and Min of Math Score in cell F16.
Therefore, you can get summary statistics in Excel.
7. Employing Power Query to Get Summary Statistics in Excel
In this method, we will use Power Query to get summary statistics in Excel.
Let’s go through the following steps to do the task.
Step-1: Employing Power Query
In this step, we will employ a Power Query.
- First, we will select the entire dataset >> go to the Data tab.
- Furthermore, from Get & Transform Data group >> Select From Table/Range.
At his point, a Create Table dialog box will appear.
- Then, click OK.
As a result, you can see the created Power Query.
Step-2: Getting Summary Statistics Using Power Query
In this step, we will get the summary statistics using Power Query. Here, we will find out the Summary statistics for Math Score.
- First, we will go to the Transform tab.
- After that, from the Table group >> select Group By.
Next, a Group By dialog box will pop up.
- Then, select Advance.
Furthermore, you will notice that the Name box has three dot points on the right side.
- Next, we will click on the dot points.
- Moreover, select Delete from the Context Menu.
- Along with that, we will click on Add aggregation to add another group.
- After that, we will type Sum in the first New column name box.
- Furthermore, under the Operation group, we will click on the drop-down arrow of the Count Rows box >> select Sum from the drop list.
- Along with that, under the Column group, we will click on the drop-down arrow of the Name box.
- Next, as we want to find the summary statistics for Math Score, we will select Math Score from the drop list.
As a result, you can see the first New Column Name box has Sum, the Operation box has Sum, and the Column has Math Score.
- In a similar way, we will type Average in the second New column name box.
- Furthermore, we select Average in the second Operation box.
- In addition, we will select Math Score in the second Column box.
- Further, to add another group, we will click on Add aggregation.
- After that, following a similar procedure, we add 3 more groups named Median, Maximum, and Minimum.
- Then, click OK.
As a result, you can see the summary statistics in the Power Query.
Afterward, we will import these summary statistics to the Excel sheet where we have the main dataset.
- To do so, we will go to the Home tab.
- Afterward, from the Close & Load group >> select Close & Load To.
Next, an Import Data dialog box will pop up.
- Then, we will select the Existing Worksheet.
- Furthermore, we will select cell B15 to put the data.
- Moreover, click OK.
Therefore, you can see the summary statistics of the Math Score.
Read More: How to Move Data from Row to Column in Excel (4 Easy Ways)
Practice Section
You can download the above Excel file to practice the explained methods.
Conclusion
Here, we tried to show you 7 methods to get summary statistics in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.
Related Articles
- Create an Organizational Chart in Excel from a List
- How to Add Trailing Zeros in Excel (2 Easy Ways)
- Calculate WACC in Excel (with Easy Steps)
- Make Sankey Diagram in Excel (with Detailed Steps)
- Make a Venn Diagram in Excel (3 Easy Ways)
- How to Calculate APR in Excel (3 Simple Methods)
- Make Trial Balance in Excel (with Easy Steps)
- Make Fishbone Diagram in Excel (with Easy Steps)
