While working in the Excel program, the user may be faced with the need to draw an intermediate result, in addition to the usual general.
The article will consider the table of sales of goods for the month, since most often it is this function that is used for the presented operation. It will look in the form of three columns: the name of the product, date and amount of revenue. Using the "Excel" subtotals, it is possible to calculate the daily earnings of a product. In addition, at the end you can summarize the amount of sales of goods. Now we will consider in detail how the subtotals are made in Excel.
Function Terms of Use
It should immediately be clarified that in order to use the presented operation, the table must meet the requirements. In total, three points should be observed:
- the heading in the table should be located on the first line of the sheet;
- cell area should be formatted by default;
- the table should be filled with data.
If you have met all these requirements, then you can easily summarize the intermediate result. Now let's take a closer look at the process itself.
Create a subtotal. Using a special tool
How to create subtotals 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 this example, select "Potato".
Step 2: Display Setup
After clicking on the tool, a window opens in which you need to set the parameters for displaying information in the program. In this case, you should find out the amount of revenue for a certain day, so in the drop-down list "For every change" you need to select "Date".
Since we will calculate the amount, in the "Operation" drop-down list, select the "Amount" value. It is also worth noting that at this stage you can select other variables, depending on your need. Among the proposed are:
- minimum;
- quantity;
- maximum;
- composition.
It remains to determine only where the result will be displayed. 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 calculate the amount, we select "Revenue amount, rubles."
Also in this window there are several more variables: "Replace current totals", "End of page between groups" and "Totals under data". Of the important ones, only the first item can be noted, and install 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 dates. It is worth noting that using the minus sign in the left part of the window you can collapse and expand groups. At the very bottom, a general summary will be made.
Create a subtotal. Using a special formula
In addition to the above method, how else can Excel calculate the subtotals? Formula is what helps 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: Opening the Feature Wizard
Before knocking out the necessary results, it is necessary to select the cell in which they will be displayed.
Just select any cell without any data entered. Next, click on the "Insert Function" icon, which is located next to the input line. You can see the exact location in the picture below.
A window of the function wizard will open, in which in the "Select a function" field you need to highlight "INTERMEDIATE. RESULTS" and click "OK". To facilitate searches, you can use alphabetical sorting by selecting the "Full alphabetical list" item in the "Category" drop-down list.
Step 2: Entering Function Arguments
As a result of all of the above actions, a window opens in which you need to set all the necessary arguments. Let's consider everything in order. In the line "Function Number", you must specify the number from the list, where:
In our case, you need to calculate the amount, so you need to enter the number "9".
In the input field "Link 1" you need to 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 by the end, 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.