The tutorial explains how to use the Excel Subtotal feature to automatically sum, count or average different groups of cells. You will also learn how to display or hide the subtotal details, copy only subtotal rows, and how to remove subtotals. Worksheets with a lot of data can often look cluttered and difficult to comprehend. Luckily, Microsoft Excel provides a powerful Subtotal feature that lets you quickly summarize different groups of data and create an outline for your worksheets. Please click on the following links to learn the details. • • • • • • • • What is Subtotal in Excel? Generally speaking, subtotal is the sum of a set of numbers, which is then added to another set(s) of numbers to make the grand total.

Open the Excel worksheet containing your data. Insert a row below each group of data that will be presented as a subtotal. In a cell on the same row, type 'Subtotal' to mark the column. You may want to add a different background color to the row or use bold text to make the subtitle more prominent.

In Microsoft Excel, the Subtotal feature is not limited to only totaling subsets of values within a data set. It allows you to group and summarize your data using SUM, COUNT, AVERAGE, MIN, MAX and other functions. Additionally, it creates a hierarchy of groups, known as an outline, which lets you display or hide the details for each subtotal, or view just a summary of the subtotals and grand totals. For example, this is how your Excel subtotals can look like: How to insert subtotals in Excel To quickly add subtotals in Excel, perform the following steps. Organize the source data The Excel Subtotal feature requires that the source data be arranged in a proper order and should not contain any blank rows. So, before adding subtotals, be sure to sort the column that you want to group your data.

The easiest way to do this, is click the Filter button on the Data tab, then click the filter arrow, and select to sort either A to Z or Z to A: To remove blank cells without messing up your data, please follow these guidelines:. Add subtotals Select any cell within your dataset, go to the Data tab > Outline group, and click Subtotal. If you want to add subtotals only for some part of your data, select the desired range before clicking the Subtotal button. Define the subtotal options In the Subtotal dialog box, specify the three primary things - which column to group by, what summary function to use, and which columns to subtotal: • In the At each change in box, select the column containing the data that you want to group.

• In the Use function box, select one of the following functions: • Sum - add up the numbers. • Count - count non-empty cells (this will insert Subtotal formulas with the function). • Average - calculate the average of numbers.

• Max - return the largest value. How to find wireless mac address for xbox 360. • Min - return the smallest value. • Product - calculate the product of cells. • Count Numbers - count cells that contain numbers (this will insert Subtotal formulas with the function).

• StdDev - calculate the standard deviation of a population based on a sample of numbers. • StdDevp - return the standard deviation based on an entire population of numbers.

• Var - estimate the variance of a population based on a sample of numbers. • Varp - estimate the variance of a population based on an entire population of numbers. • Under Add subtotal to, select the check box for each column that you want to subtotal. In this example, we group the data by the Region column, and use the SUM function to total numbers in the Sales and Profit columns. Additionally, you can select any of the following option: • To insert an automatic page break after each subtotal, select the Page break between groups box. • To display a summary row above the details row, clear the Summary below data box. To show a summary row below the details row, select this check box (usually selected by default).

• To overwrite any existing subtotals, keep the Replace current subtotals box selected, otherwise clear this box. When bootcamping a mac, what slic do i pick for windows. Finally, click the OK button. The subtotals will appear below each data group, and the grand total will be added to the end of the table. Once subtotals are inserted in your worksheet, they will recalculate automatically as you edit the source data.

If the subtotals and grand total are not recalculated, be sure to set your workbook to automatically calculate formulas ( File > Options> Formulas > Calculation options > Workbook Calculation > Automatic). 3 things you should know about Excel Subtotal feature Excel Subtotal is very powerful and versatile, and at the same time it's a very specific feature in terms of how it calculates data. Below, you will find the detailed explanations of Subtotal's specificities. Only visible rows are subtotaled In essence, Excel Subtotal calculates values in visible cells and ignores filtered out rows.