How to put subtotal formulas. How to calculate the sum of a column in Excel. Conditions for using the feature

While working in Excel, the user may be faced with the need to summarize an intermediate result, in addition to the usual general one.

The article will consider the table of sales of goods for the month, since most often this function is used for the presented operation. It will look like three columns: product name, date and revenue amount. Using subtotals in Excel, it is possible to calculate the daily earnings of a particular product. In addition, at the end you can sum up the amount of sales of goods. Now we will take a closer look at how subtotals are made in Excel.

Conditions for using the feature

It is worth immediately clarifying that in order to use the presented operation, the table must meet the requirements. There are three points to follow:

• the header in the table should be located on the first line of the sheet;
• The cell area should be formatted by default;
• the table must be filled with data.

If you meet all these requirements, then you can easily draw an intermediate result. Now let's take a closer look at the process itself.

Creating a subtotal. Using a special tool

How to create a subtotal in Excel? The first method will be demonstrated using a standard tool of the same name in the program. So let's get started.

Step 1: Opening the tool

First you need to open the "Subtotal" tool itself. It is located on the toolbar in the "Data" tab (in the "Structure" tool group). Before opening it, you will need to select the cell in which one of the product names is located, in in this example select "Potatoes".

Step 2: Display Settings

After clicking on the tool, a window will open in which you need to set parameters for displaying information in the program. In this case, you need to find out the amount of revenue for a certain day, so in “At each change” you need to select “Date”.

Since we will be calculating the amount, in the “Operation” drop-down list you must select the “Amount” value. It is also worth noting that on at this stage You can select other variables depending on your need. Among those proposed are:

• minimum;
• quantity;
• maximum;
• work.

All that remains is to determine where the result will be output. To do this, in the "Add total by" field, you need to check the box next to the name of the column in which the result will be displayed. Since we are calculating the amount, we select “Amount of revenue, rub.”.

There are also several other variables in this window: "Replace running totals", "End of page between groups" and "Totals under data". Of the important ones, you can only mark the first point, and set the rest as desired.

Step 3: Final

After making all the settings, you just have to click on the “OK” button. Now in the program you can observe subtotals by date. It's worth noting that you can collapse and expand groups using the minus sign on the left side of the window. The overall result will be summarized at the very bottom.

Creating a subtotal. Using a special formula

In addition to the method presented above, how else can you calculate subtotals in Excel? The formula is what will help us.

Those who often work with the program know that it is easier to use the special “Function Wizard” tool to enter formulas. It is to him that we turn.

Step 1: Open the Feature Wizard

Before entering the required totals, you need to select the cell in which they will be displayed.

Just select any cell without any data entered into it. Next, you need to click on the “Insert Function” icon, which is located next to the input line. You can see the exact location in the picture below.

The Function Wizard window will open, in which in the “Select a function” field you need to select “SUBTOTAL” and click “OK”. To make your search easier, you can sort alphabetically by selecting “Full alphabetical list” from the “Category” drop-down list.

Step 2: Entering Function Arguments

As a result of all the above actions, a window will open in which you need to specify all the necessary arguments. Let's look at everything in order. In the line "Function number" you must indicate a number from the list, where:

In our case, we need to calculate the amount, so we need to enter the number “9”.

In the "Link 1" input field, you must select the area of ​​cells with data that will be processed using the function. To do this, you can use two ways: enter everything yourself or specify using the cursor. We will use the second option since it is simpler. To do this, click on the button on the right and select the desired area, and finally click on this button again.

The familiar window will appear again, in which you can specify the second link (there are four in total). If you do not need to do this, then click on the “OK” button and the results will be displayed in the table.

Summarize intermediate results in Excel spreadsheet you can use the built-in formulas and the corresponding command in the “Structure” group on the “Data” tab.

An important condition for using the tools is that the values ​​are organized in the form of a list or database, identical records are in the same group. When creating a summary report, subtotals are generated automatically.

Calculating subtotals in Excel

To demonstrate the calculation of subtotals in Excel, let’s take small example. Let's say the user has a list with sales of certain products:

It is necessary to calculate the revenue from the sale of individual groups of goods. If you use a filter, you can get records of the same type based on a given selection criterion. But the values ​​will have to be calculated manually. Therefore, let's use another tool Microsoft Excel– by the “Subtotals” team.

To ensure that the function produces the correct result, check the range against the following conditions:

• The table is designed as a simple list or database.
• The first line is the names of the columns.
• The columns contain values ​​of the same type.
• There are no empty rows or columns in the table.

Let's get started...

1. Let's sort the range by the value of the first column - data of the same type should be nearby.
2. Select any cell in the table. Select the “Data” tab on the ribbon. Group “Structure” - team “Subtotals”.
3. Fill out the “Subtotals” dialog box. In the “At every change in” field, select the condition for selecting data (in the example, “Value”). In the “Operation” field, assign a function (“Amount”). In the “Add by” field, you should mark the columns to whose values ​​the function will be applied.
4. Close the dialog box by clicking OK. The original table takes the following form:

If you collapse the rows in the subgroups (click on the “minuses” to the left of the row numbers), we get a table of only subtotals:

Each time the Title column is changed, the subtotal in the Sales column is recalculated.

To have each subtotal followed by a page break, check the “End of page between groups” checkbox in the dialog box.

To display intermediate data ABOVE the group, clear the “Totals below the data” condition.

The subtotals command allows you to use several statistical functions simultaneously. We have already assigned the operation “Sum”. Let's add the average sales values ​​for each product group.

Call up the “Subtotals” menu again. Uncheck the “Replace current” checkbox. In the “Operation” field, select “Average”.

Formula “Subtotals” in Excel: examples

The SUBTOTALS function returns a subtotal to a list or database. Syntax: function number, reference 1; link 2;… .

Function number – a number from 1 to 11 that specifies the statistical function for calculating subtotals:

1. – AVERAGE (arithmetic mean);
2. – COUNT (number of cells);
3. – COUNT (number of non-empty cells);
4. – MAX (maximum value in the range);
5. – MIN (minimum value);
6. – PRODUCT (product of numbers);
7. – STANDARD DEVIATION (standard deviation according to the sample);
8. – STANDARDEVAL (standard deviation of the population);
9. – SUM;
10. – VAR (sample variance);
11. – VAR (variance over the general population).

Features of the “work” of the function:

• returns results for explicit and hidden rows;
• excludes rows not included in the filter;
• Counts only in columns, not suitable for rows.

Let's look at an example of using the function:

1. We create an additional line to display subtotals. For example, “sum of selected values.”
2. Let's turn on the filter. Let us leave in the table only the data for the value “Amadis Dining Group”.
3. In cell B2, enter the formula: .

Formula for the average value of the subtotal of the range (for the “Retro” hallway): .

Formula for maximum value (for bedrooms): .

Subtotals in an Excel PivotTable

In a PivotTable, you can show or hide subtotals for rows and columns.

1. When generating a summary report, an automatic summation function is already included to calculate the totals.
2. To apply another function, in the “Working with Pivot Tables” section on the “Options” tab, find the “Active Field” group. The cursor must be in the cell of the column to whose values ​​the function will be applied. Click the “Field Options” button. In the menu that opens, select “others”. We appoint the desired function for subtotals.
3. To display totals for individual values, use the filter button in the right corner of the column name.

In the “Pivot Table Options” menu (“Options” - “Pivot Table”), the “Totals and Filters” tab is available.

Download examples with subtotals

Thus, there are three ways to display subtotals in Excel lists: the “Structure” group command, the built-in function, and the pivot table.

Good day, friends!

I dedicate this article to the opportunity to create subtotals in Excel. Even if you have heard or know what it is, it will still be useful to read or listen to my information. By and large, subtotals are an automatic summing up of results based on a given criterion and grouping them for convenience and good data visualization.

This is quite a useful and convenient tool for working in spreadsheets, which will come in handy for you, and for greater convenience, I advise you to use these totals in tandem with grouping cells, this is generally a super power. I also ask you to draw your attention to the fact that before working with the results, it is imperative to correctly sort your data.

So, let's take a closer look at what these results are, how to create them, how to work with them, and, in fact, how to remove them if the need for this opportunity no longer exists. And let’s start all our research from the beginning, or rather, with how we can create an interim total.

How to make subtotals in Excel

Now let’s use the created example to see step by step how to do this:

Working with grouped data

After the manipulations, working with data will be significantly simplified. Data can be conveniently visualized using data grouping and creating as many levels as is convenient for you, but, alas, no more than 8 nested ones; from experience I can say that this is enough for the eyes.

By clicking on the data grouping icons, you hide and display the data structure that is most relevant to you on this moment. For example, you can display only the grand total, display totals by data groups, and display a full breakdown of the data in detail. Also, all this can be done using icons "Hide details" or "Show".

Remove subtotals

In the event that subtotals in Excel are no longer needed, or your data in the table will need to be rearranged in a new way, in this case the totals can be deleted without losing the quality of the original data. To do this you need:

1. Open a tab in Control Panel "Data" and in the block "Structure" click on the icon "Subtotal".
2. In the dialog box that appears "Subtotals" press the button "Remove everything".
3. Once clicked, all your data will be ungrouped and subtotals will be deleted.

If you still need to leave subtotals, but don’t need grouping, you can in the taskbar on the tab on the tab "Data" in the button dropdown menu "Ungroup" select item "Delete structure", the grouping structure will be deleted, leaving you with subtotals.

And that's all for me! I really hope that all of the above is clear to you. I would be very grateful for your comments, as this is an indicator of readability and inspires me to write new articles! Share what you read with your friends and like it!

While wealth has not yet been acquired, the desire for it is exhausting; once acquired, it is tormented by worries; when it is lost, longing for it is tormented. Democritus

When working with tables, there are often cases when, in addition to general totals, you also need to add intermediate ones. For example, in a table of sales of goods for a month, in which each individual line indicates the amount of revenue from the sale of a specific type of product per day, you can add up daily subtotals from the sale of all products, and at the end of the table indicate the amount of total monthly revenue for the enterprise. Let's find out how you can make subtotals in Microsoft program Excel.

Conditions for using the feature

But, unfortunately, not all tables and data sets are suitable for applying the subtotal function to them. The main conditions include the following:

• the table must have the format of a regular cell area;
• the table header should consist of one line and be placed on the first line of the sheet;
• The table should not contain rows with blank data.

Creating subtotals

In order to create subtotals, go to the “Data” tab in Excel program. Select any cell in the table. After that, click on the “Subtotal” button, which is located on the ribbon in the “Structure” tool block.

Next, a window opens in which you need to configure the display of subtotals. In this example, we need to view the amount of total revenue for all products for each day. The date value is located in the column of the same name. Therefore, in the “At each change in” field, select the “Date” column.

In the “Operation” field, select the “Amount” value, since we need to enter exactly the amount for the day. In addition to the amount, many other operations are available, among which are:

• quantity;
• maximum;
• minimum;
• work.

Since the revenue values ​​are displayed in the “Amount of revenue, rubles” column, then in the “Add totals by” field, select it from the list of columns of this table.

In addition, you need to check the box next to the “Replace running totals” option if it is not checked. This will allow you, when recalculating the table, if you are performing the procedure of calculating subtotals with it not for the first time, not to duplicate the recording of the same totals multiple times.

If you check the “End of page between groups” checkbox, then when printing, each block of the table with subtotals will be printed on a separate page.

When you check the box next to the “Totals under data” value, subtotals will be set under the block of rows, the sum of which is added up in them. If you uncheck this box, then the totals will be shown above the rows. But it is the user himself who determines what is more convenient for him. For most people, it is more convenient to place the totals below the lines.

After all the subtotal settings have been completed, click on the “OK” button.

As you can see, subtotals have appeared in our table. In addition, all groups of rows combined by one subtotal can be collapsed by simply clicking on the minus sign to the left of the table, opposite a specific group.

This way, you can collapse all rows in a table, leaving only subtotals and grand totals visible.

It should also be noted that when the data in the rows of the table changes, the subtotals will be recalculated automatically.

Formula “INTERMEDIATE.RESULTS”

In addition, it is possible to display subtotals not through a button on the ribbon, but by using the ability to call a special function through the “Insert Function” button. To do this, after first clicking on the cell where the subtotals will be displayed, click the indicated button, which is located to the left of the formula bar.

The Function Wizard opens. Among the list of functions, look for the item “INTERMEDIATE RESULTS”. Select it and click on the “OK” button.

A window opens in which you need to enter the function arguments. In the “Function number” line, you need to enter the number of one of eleven data processing options, namely:

1. arithmetic mean;
2. number of cells;
3. number of filled cells;
4. the maximum value in the selected data array;
5. minimum value;
6. product of data in cells;
7. sample standard deviation;
8. population standard deviation;
9. sum;
10. sample variance;
11. variance in the population.

So, enter in the field the number of the action that we want to apply in a particular case.

In the “Link 1” column you need to indicate a link to the cell array for which you want to set intermediate values. It is allowed to introduce up to four separate arrays. When adding the coordinates of a range of cells, a window immediately appears allowing you to add the next range.

Since manually entering a range is not convenient in all cases, you can simply click on the button located to the right of the input form.

At the same time, the function arguments window will be minimized. Now you can simply select the desired data array with the cursor. After it is automatically entered into the form, click on the button located to the right of it.

The function arguments window opens again. If you need to add one or more data arrays, then add using the same algorithm that was described above. Otherwise, click on the “OK” button.

After this, subtotals of the selected data range will be generated in the cell in which the formula is located.

The syntax of this function is as follows: “INTERMEDIATE TOTAL(function_number, cell_array_addresses). In our specific case, the formula will look like this: “INTERMEDIATE.TOTAL(9;C2:C6)”. This function, using this syntax, can be entered into cells manually, without calling the Function Wizard. Just remember to put an “=” sign in front of the formula in the cell.

As you can see, there are two main ways to generate subtotals: through a button on the ribbon, and through a special formula. In addition, the user must determine which value will be displayed as a total: sum, minimum, average, maximum, etc.

We are glad that we were able to help you solve the problem.

Ask your question in the comments, describing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

Did this article help you?

The subtotal function in Excel is a simple and convenient way analyzing data from a specific column or table.

Intermediate calculations reduce calculation time and allow you to quickly view data on the amount, quantity of goods, total income, etc.

By the way, to work more effectively with tables, you can familiarize yourself with our material Excel Hot Keys - The Most Essential Options.

Sheets Excel document contain a large amount of information. It is often inconvenient to view and search for data.

To make life easier for users, the program developers created functions for grouping data.

With their help, you can easily search for the fields and columns you need, as well as hide information that is not needed at this moment.

You can summarize several sheet groups using the “Subtotals” function.

Let's take a closer look at the operating principle of this function and its implementation. in all versions of the table processor Excel.

Function Syntax

In Excel, the option is displayed as INTERMEDIATE.RESULTS (No.; link 1; link 2; link 3;…; linkN) , where the number is the designation of the function, the link is the column by which the result is summarized.

Link fields can include a maximum of 29 links and spacing used in running subtotals.

The number can be any number from 1 to 11. This number shows the program what function should be used to count totals within a selected list.

Totals option syntax indicated in the table:

	Determining the arithmetic mean of two or more values.
	Calculate the number of numbers that are presented in the argument list.
	Count all non-empty arguments.
	Shows the maximum value from a defined set of numbers.
	Shows the minimum value from a defined set of numbers.
6-PRODUCT	Multiplies the specified values ​​and returns the result.
7-STD DEVIATION	Analysis of the standard deviation of each sample.
8-STAD DEVIATION P	Deviation analysis for the total data set.
	Returns the sum of the selected numbers.
	Analysis of sample variance.
	Analysis of common variance.

Example of implementing subtotals

Running subtotals includes the following operations:

• Data grouping;
• Creation of results;
• Creating levels for groups.

Grouping tables

Follow the instructions to group the rows. In our case, we link columns A, B, C:

1 Using the mouse, select columns A, B, C, as shown in the figure below.

Fig. 2 – selecting several columns

2 Now, keeping the selection, Open the “Data” field on the program toolbar. Then on the right side of the options window, find the icon "Structure" and click on it;

3In the dropdown list click on "Group". If you made a mistake at the stage of selecting the required table columns, click on "Ungroup" and repeat the operation again.

Fig. 3 – information grouping

4 In a new window in select “Strings” and click on "OK";

Rice. 4 – row grouping

5 Now again select columns A, B, C and conduct a grouping, but already by columns:

Fig.5 - creating a group of columns

Fig.6 – group control panel

Creating subtotals

The subtotal option, thanks to the use of groups and standard eleven functions (Table 1), allows you to quickly summarize the completed table data.

Let's look at the practical use of this function.

The result of the action will be the appearance of groups that are sorted by each item size. Follow the instructions:

ABOUT determine for which data you need to perform final calculations. In our case this is the column "Size". Its contents need to be sorted. Sorting is carried out from the largest element to the smallest. Select the “Size” column;

Find the field "Sorting" and click on it;

In the window that appears, select “Sort by range of specified values” and press the key "OK".

Fig. 7 – sorting column contents

• As a result, all sizes will be displayed in descending order. Only after this we can carry out interim results that affect the data on the size chart of the ordered items.

Fig. 8 – sorting result

Now let's move on to implement the “Subtotal” function:

• On the program toolbar, open the “Data” field;
• Select the "Structure" tile;
• Click on “Subtotals”;

Fig.9 – location of the option on the toolbar

Next, a window will open for setting up and displaying subtotals. In the first field, select the name of the column for which you will summarize. In our case it is “Size”. With each change in this field, the data will change in the totals;

In the "Operation" field select the function type, which applies to all elements of the column selected in the first field. To calculate sizes, you must use quantity counting;

In the “Add totals by” list, you select the column in which subtotals will be displayed. Select the item “Size”;

After setting all parameters again double-check the entered values ​​and click OK.

Fig. 10 – result processing

As a result, the table will be displayed on the sheet, grouped by size. Now it is very easy to see all the ordered items in sizes Small, Extra Large and so on.

Fig. 11 – result of executing the function

As you can see from the figure above, all totals are displayed between the shown groups in a new line.

Total for clothes with size Small - 5 pieces, for clothes with size Extra Large - 2 pieces. The total number of elements is also shown below the table.

Group levels

You can also view groups by level.

This display method allows you to reduce the amount of visual information on the screen - you will see only the necessary total data - the number of clothes of each size and the total quantity.

Click on the first second or third level in the groups control panel. Choose the most suitable option presentation of information:

Fig. 12 – second level viewing

Deleting totals

After some time, other data will be entered into the table, so there will be a need to re-group the information.

The old subtotals and their calculation type will no longer be relevant, so they need to be deleted.

To delete a total, select the “Data” - “Structure” - “Subtotals” tab.

In the window that opens, click on the “Remove all” button and confirm the action with the “OK” button.

Fig. 13 - deleting data

Subtotals in Excel are a very convenient tool for analyzing information. You can use it in several ways: using special functionality, formulas or pivot tables.

In this case, there are mandatory conditions:

• names must be repeated more than once;
• entries must be arranged in one column.

Let's look at each method in more detail.

How to calculate subtotals in Excel

To take advantage standard function, you need to perform a few simple operations.

1. Create a table that meets the requirements described above.

1. Select the entire range of values. Use the sort icon. Select the A to Z tool.

1. Thanks to this, all cells will be sorted by product name.

1. After this, make some cell active (it is not necessary to select the whole cell). Go to the Data tab. Use the Structure tool.

1. In the menu that appears, select the specified button.
2. Next, specify the desired operation. To continue, click on "OK".

1. The outcome will be as follows. Additional buttons will appear on the left side of the editor.

1. If you want, you can click on the “-” icon. Thanks to this, the cells will be grouped. As a result of this action, only the calculation results will remain.

If you don’t like this filter, you can return everything back using the buttons with the “+” symbol.

Nesting has several levels. You can collapse all the information entirely.

How to make multiple results

For this purpose you will need to follow the following steps.

1. Let's say that you already have some kind of summing up. We will use our example. Make any cell active. Then click on the Structure tool.

1. After this, we again select the item we are familiar with.

1. Next, select some other operation for calculation. Be sure to uncheck the box next to “Replace running totals.”

1. Only after this can you click on the “OK” button.

1. Thanks to this, you will see that there are now several intermediate values ​​in the table.

How to remove subtotals

The process is almost the same as during creation.

1. Select the entire range of cells. Click on the “Structure” menu item.

1. Select the specified tool.

1. Click on the “Remove All” button.

1. As a result, the Excel editor will take its previous appearance.

This way you can delete any number of intermediate counts.

Calculations using the function

In addition to the method described above, in this editor exists special team, with which you can display the total value in some other cell. That is, it can even be located outside of this table.

To do this, just take a few simple steps.

1. Go to any cell. Click on the "Fx" icon. In the Insert Function window, select the Full Alphabetical List category. Find the item you need there. Click on the “OK” button.

1. After this, a window will appear asking you to provide the function arguments.

In the first field you must indicate the number of the required operation. You can read more on the official Microsoft website. In this case, you do not need to know English - all information is available in Russian.

Please note that the formula used here is to calculate the regular amount. The SUMIF function is not included in this list.

1. Let's use the number 9 as an example.

1. Then you need to click on the second field, otherwise the selected range of cells will be inserted into the first line, and this will lead to an error.

1. Then highlight some values. In our case, we will calculate the amount of sales of the first product. At the end, click on “OK”.

1. The result will be as follows.

In this case, we calculated the amount for only one product. If you want, you can repeat the same steps for each item separately. Or simply copy the formula and manually correct the range of cells. In addition, it is advisable to format the calculations in such a way that it is clear which product the calculation result corresponds to.

Using an Excel PivotTable

To take advantage of this opportunity, you need to do the following:

1. Specify the desired range.
2. Go to the "Insert" tab.
3. Use the Table tool.
4. Select the desired item.

1. Choose something that is best for you. The number of options depends on the information highlighted.
2. After this, click on “OK”.

1. By default, Excel will try to add all the values ​​in the table.

In order to change this, you need to follow these steps:

1. Double click on the title of the specified field.
2. Select the operation you need. For example, "Average".
3. Save the changes by clicking “OK”.

1. As a result, you will see the following:

Conclusion

In this tutorial, we looked at all the main ways to create subtotals in the Excel editor. If things don't work out for you, your information may not meet the requirements stated at the beginning.

Video instruction

If you have any difficulties, try watching the video below. Various nuances are discussed there with additional comments.

Function "Subtotals()" used to calculate a subtotal (sum, average, number of values, etc.) in a range in which there are hidden dates.

The peculiarity of the function is that it is intended for use in conjunction with other Excel tools (for example, autofilters).

To demonstrate the benefits of Excel functions Intermediate.totals() will consider a truncated unloading from the event database.

Task: using the selected parameters, evaluate key indicators in comparison with the situation in all areas.

Events can be filtered by different parameters, but for comparison we would like to understand the indicators by filtered parameters and compare them with the situation as a whole in all areas.

To do this, you can use the Excel function =Subtotals(). The function =Subtotals() performs calculations based on the filtered values.

You can specify the calculation in the formula:

• Medium – number 1 or 101 – for sample
• Counting values ​​– number 3 or 103
• Amount - 9 or 109
• Etc.

• If the function number is three-digit, for example, 109, then the function works as subtotals, i.e. calculates values ​​based on filtered parameters.
• If the number is single-digit or double-digit 2 or 11, then the subtotals () formula works like a standard formula for the selected function.

Let's move on to our events:

We have an unloading from the database using the following columns:

• Situation
• Actions
• Number of clients
• Volume_Sales_Up to
• Volume_Sales_Plan
• Volume_Sales_Act
• Costs_Plan
• Costs_Act

To assess the situation as a whole, we introduce a standard summation formula:

To calculate subtotals for the selected parameters, enter =SUBTOTAL(109;RC:RC), where

• 109 – sum function for calculating subtotals;
• RC:RC – reference to the summation range.

Now, having filtered the column “Situation” - “Situation 4” and “Action” - Actions 2 and 3, we will get a recalculation of subtotals for each of the indicators and can compare them with the “Total” indicators:

As a result: It can be seen that the % increase in sales for filtered events is higher than for all events, and they planned to get results lower than the total, but got higher. Those. worked better than planned and better than all events.

Excel formula =subtotals() – great tool to calculate indicators based on filtered parameters.

If you have any questions, please contact us!

Join us!

Download free apps for forecasting and business analysis:

• Novo Forecast Lite- automatic forecast calculation V Excel.
• 4analytics - ABC-XYZ analysis and emissions analysis Excel.
• Qlik Sense Desktop and QlikViewPersonal Edition - BI systems for data analysis and visualization.

When working with data in Excel, you often end up building large tables with various calculations and comments, which often makes them difficult to understand. To avoid such troubles, use the structure Excel data. It is located on the “Data” tab, “Structure” section.

This section contains 3 functions. Let's look at them briefly (for more complete information follow the links):

• Group – combines multiple rows or columns into a single group. This allows you to hide detailed information from view, and, if necessary, quickly access it;
• Ungroup – cancels merging rows and columns into groups completely or partially;
• Intermediate results – combining “related” data into groups and drawing some results from them.

Two buttons (“+” and “-”) in the upper right part of the section reveal or hide the details of the group, but it is often more convenient to use these buttons in the fields of the Excel sheet that appear when combining data into structures.

When building large tables, it is useful to use data grouping to hide detailed information by presenting at its best the most important. At the same time, when it is necessary to obtain clarification on any indicators, grouped data can be quickly displayed by expanding a specific group. Let's take a closer look.

We have a sales report for all working agents for the year. Information is presented by month. But such detail for the purposes of our analysis is redundant, but if necessary, we must quickly obtain it. Therefore, there is no need to delete it. We could simply hide the columns and that would serve our purpose. But if you are making a report not for yourself, but for management or someone else. You cannot be 100% sure that those who will work with your tables will be proficient in Excel. The way out of the situation is grouping. It is located on the “Data” tab, “Structure” section.

In our example table, select the report columns that contain detailed information, then click the “Group” icon. It is not possible to create several groups at once, so create them one by one. This is what the report looked like before grouping:

The figure shows that data is grouped by month for certain quarters, thereby forming 4 groups. Only quarterly results are not included. By clicking on the minus icons, detailed information is hidden. The report looks like this:

It now looks more compact and displays the most important information. At the same time, by opening any of the groups, you can familiarize yourself with the details.

Pay attention to the numbers shown in the picture. They present buttons that allow you to quickly reveal and hide all groups at once. Button 1 hides all levels of groups, button 2 reveals groups of the first level. If another group is created within a group, it will be assigned to level 3, and another one will be added to the buttons, with the number 3, etc.

In order to remove columns from a group, you need to select them and in the “Structure” section click on the “Ungroup” icon. If you want to remove the entire grouping at once, select the entire Excel sheet and click the same icon. You will be prompted to ungroup columns or dates. Select the option you want and click OK. The application will remove all groups top level, leaving the lower ones.

You can group both columns and rows. Both are subject to the same rules.

The “Structure” section, in addition to grouping and ungrouping data, has another function – “Subtotals”. With its help, you can set automatic grouping for data with a summary of it. To make it clearer what we are talking about, consider an example table of employees with their sales results for the quarter.

By selecting the table and clicking on the “Subtotals” icon, the application will display a window:

In the “For each change in:” field, you must specify the title of the table column by which the summing up will be determined. The cells of the top rows of the selected table act as the header. The labels in the cells of the specified column will serve as row headings. The program will check the titles for similarity. If the row headers match, then it adds it to the total calculation; if they do not match, then the result for the group defined by one header is displayed in an additional line. Therefore, we recommend sorting by this column in advance.

In the second field “Operation:” you select which total needs to be summed up: Sum, quantity, average, etc.. In the last field “Add total by:” you select the column that contains the necessary data to display the result.

Below are 3 checkboxes (flags):

• Replace current totals – if set, then the results summed up earlier will be replaced, if not, then new results will be added to the old ones;
• End of page between groups – if set, then when printing each separate group with the result will be located on a new sheet;
• Totals under the data – if set, the result will be located at the end of the group, if not, then at the beginning.

The Remove All button removes the entire data structure.

This is how the table was transformed after applying the described function to it:

The new cells added contain the mathematical function SUBTOTAL(), which can be found in the article about mathematical functions - Excel Mathematical Functions.

The mechanism for summing up interim results has common features with another more powerful tool Excel applications– Pivot tables.

Let's calculate the subtotals in the MS EXCEL table. For example, in a table containing information about sales of several different categories of goods, we calculate the cost of each category.

We have a table of product sales (products are repeated). See example file.

Let's calculate the cost of each product using MS EXCEL Subtotals (Data/Structure/Subtotals).

To do this you need:

• make sure column titles have headings;
• column data Goods, for example using ;
• by selecting any cell in the table, call Subtotals(on the menu Data/Structure);

• in field " For every change in:" choose Product;
• in field " Operation" choose Sum;
• in field " Add totals by» check the box next to the value Price;
• Click OK.

As can be seen from the picture above, after using the tool Subtotals, MS EXCEL created three levels of data organization: structure controls appeared to the left of the table. Level 1: Grand total (cost of all goods in the table); Level 2: Cost of goods in each category; Level 3: All table rows. By clicking the appropriate buttons, you can present the table at the desired level of detail. The pictures below show levels 1 and 2.

Copy only rows with subtotals

Copying only rows with subtotals to another range is not so easy: even if the table is grouped at the 2nd level (see the figure above), then selecting the cells with totals (in fact, the range will be selected A4:D92) and copying it to another range we get the entire table. To copy only the Totals we use (we will use the fact that MS EXCEL when creating the structure Subtotals inserts total rows with added word Bottom line or in the English version - Total).

• create in range D5:D6 criteria board: in D5 place a column heading that contains the words Total, i.e. word Product; V D6 enter *Total (all rows whose Product column contains values ​​ending with the word will be selected Bottom line) The asterisk means *;

• select any table cell;
• call ( Data/ Sort & Filter/ Advanced);
• in the Range of conditions field, enter D5:D6 ;
• set option Copy the result to another location;
• in field Place result in range specify an empty cell, for example A102 ;