How the function to get the data of the pivot table works

Earlier, I already talked about the fact that when referring to a cell pivot table instead of a regular reference, the GET.PIVOTO.TABLE.DATA function is returned (see ). If you are interested How To overcome this inconvenience, I recommend referring to the mentioned note. If you are interested, Why this happens, and also, what are the positive aspects of the GET.DATA.PIVOTO.TABLE function, then I offer a fragment of the book by Jelen, Alexander. (chapter 15). The technique under consideration will allow you to cope with many problems that cause headaches for users of pivot tables, in particular:

Refreshing a PivotTable removes previously applied formatting. Numeric formats are lost. The results of adjusting the width of the columns disappear.
Does not exist easy way creating an asymmetric pivot table. The only option is to use named sets, but this method is only available to those using Data Model PivotTables and not regular PivotTables.
Excel cannot remember templates. If you need to create PivotTables over and over again, you will have to re-group, apply calculated fields and members, and perform a number of other similar tasks.

In fact, everything described here is not new. Moreover, similar techniques have been used since Excel 2002. However, my communication with users shows that less than 1% are familiar with them. The only question users have is how to turn off the weird GET.PIVOTO.TABLE.DATA feature. It's a pity…

Download note in or format, examples in format

Well, let's start in order.

How to abandon the problematic function GET.DATA.PIVOTO.TABLE

The GET.DATA.Pivot.TABLE function has been a headache for many users for a long time. All of a sudden, without any warning, the behavior of PivotTables changed in Excel 2002. Once you start creating formulas outside of the PivotTable that refer to its data, this feature comes out of nowhere.

Suppose, in the pivot table shown in Fig. 1, you need to compare the data for 2015 and 2014.

Rice. 1. Initial pivot table

Add the heading "% Growth" to cell D3.
Copy the format from cell C3 to cell D3.
In cell D4, enter an equal sign.
Click on cell C4.
Enter the / (slash) sign to indicate the division operation.
Click on cell B4.
Type -1 and press the key combination to stay in the same cell. Format the result as a percentage. You will see that the West region experienced a 43.8% drop in revenue (Figure 2). Not very good results.
After you finish entering your first formula, select cell D4.
Double click on the small square located in the lower right corner of the cell. This box represents a fill handle that can be used to copy a formula to fill an entire report column.

After copying the formula is complete, looking at the screen, you will understand that something is wrong - each region showed a drop of 43.8% over the year (Fig. 3).

Rice. 3. After copying the formula to all cells in the column, you will see that each region showed a drop of 43.8%

It hardly happens in real life. Any user will tell you that after doing the above Excel Actions will create the formula =C4/B4–1. Return to cell D4 and pay attention to the formula bar (Figure 4). It's just some damn thing! The simple formula =C4/B4–1 no longer exists. Instead, the program substitutes a complex structure with the GET.PIVOTAL.TABLE.DATA function. Why does this formula give correct results in cell D4, but when copied to the cells below it fails to work?

The first reaction of any user to what happened will be the following: “What is this strange GET.DATA.PIVOTO.TABLE construction that messed up my report?” Most users will want to get rid of this feature right away. Some will ask the question: “Why did Microsoft give us this feature?”

There was nothing like it in excel times 2000. When I started seeing GET.DATA.PIVOTOMATIC.TABLE on a regular basis, I just hated it. When someone asked me at one of the seminars how it can be used for the good of the cause, I was dumbfounded. I have never asked myself such a question! In my opinion, and in the opinion of most Excel users, the GET.DATA.PIVOTAL.TABLE function was a product of evil that had nothing to do with the forces of good. Fortunately, there are two ways to disable this feature.

Blocking the GET.PIVOTO.TABLE.DATA function by entering a formula. There is an easy way to prevent the GET.DATA.PIVOTO.TABLE function from appearing. To do this, you need to create a formula without using the mouse or cursor keys. Just follow these steps.

Go to cell D4 and type = (equal sign).
Enter C4.
Type / (slash for division).
Enter B4.
Enter -1.
Click Enter.

Now you have created a custom Excel formula, which can be copied into the cells of the column below and with which you can get the correct results (Fig. 5). As you can see, you can create formulas in areas outside of the PivotTable that refer to the data inside the PivotTable. And those who do not believe that this is possible, let them perform the described actions on their own.

Rice. 5. Just type =С4/В4–1 from the keyboard and the formula will work as it should

Some users will feel uncomfortable about the fact that the usual order of entering formulas is violated. In addition, the proposed option is more laborious. If you are one of these users, the second way is for you ...

Disable the GET.PIVOTO.TABLE.DATA function. You can permanently disable the GET.PIVOTO.TABLE.DATA function. Click on the menu ribbon File → Options. In the opened window Optionsexcel go to tab Formulas and uncheck the option use functionGetPivotData for PivotTable links. Click Ok.

Alternative option. Click on the pivot table, and in the contextual tab that appears Analysis click on the dropdown next to the button Options. Uncheck the box next to Create GetPivotData(Fig. 7). The checkbox is enabled by default.

Why did Microsoft offer us the GET.PIVOTO.TABLE.DATA function. If this feature is so terrible, why did the developers at Microsoft enable it by default? Why do they care about maintaining support for this feature in newer versions of Excel? Are they aware of user sentiment? And we move on to the most interesting ...

Using the GET.PIVOTO.TABLE.DATA function to improve pivot tables

Pivot tables are a great invention of mankind. A PivotTable is created with just a few clicks of the mouse, eliminating the need for advanced filters, the BSUM function, and data tables. With PivotTables, you can create one-page reports based on huge amounts of data. Against the backdrop of these advantages, some of the disadvantages of PivotTables, which are inexpressive formatting and the need to convert PivotTables to values for additional customization, fade into the background. On fig. Figure 8 shows a typical pivot table creation process. In the case under consideration, everything starts with the initial data. We create a pivot table and use all possible techniques to customize and improve it. Sometimes we convert the PivotTable to values and do some final formatting.

The new PivotTable technique introduced by Rob Colley (a Microsoft developer) and discussed below is an improvement on the process described above. In this case, a primitive pivot table is first created. This table does not need to be formatted. Then, a one-step, relatively time-consuming process is followed to create a beautifully formatted shell that will contain the final report. After that, the function GET.DATA.PIVOTOMATIC.TABLE is used to quickly fill the report in the shell with data. After receiving new data, you can place it on the sheet, update the primitive pivot table and print the report that is in the shell (Fig. 9). This technique has a number of undeniable advantages. For example, you don't have to worry about formatting a report right after it's created. The process of creating pivot tables becomes almost completely automated.

The following sections show you how to create a dynamic report that displays actuals for past months and plans for future months.

Creating a primitive pivot table. The initial data (Fig. 10) are presented in the form of transactions containing information on planned and actual indicators for each region in which there are company branches. Planned indicators are detailed at the level of months, and actual - at the level of individual days. Planned indicators are created for the year ahead, and actual - for the past months. Because the report will be updated every month, this process is greatly simplified if the PivotTable data source grows in size as new data is added to the bottom. In older versions Excel creation such a data source was carried out using a named dynamic range using the OFFSET function (for more details, see). When working in Excel 2013, just select one of the data cells and press Ctrl+T (create table). A named dataset will appear, automatically expanding as new rows and columns are added.

Now let's create a pivot table. The GET.PIVOT.TABLE.function is quite powerful, but it can only return the values that appear in the actual pivot table. This function is unable to crawl the cache to calculate items that are not in the PivotTable.

Create a pivot table:

Choose a team Insert → pivot table and then in the dialog box Create a PivotTable click OK.
In the PivotTable Fields list, select the field date. A list of dates will appear on the left side of the pivot table (Fig. 11).
Select any date cell, for example, A4. On the context tab Analysis, located in the set of contextual tabs Working with pivot tables, click the button Group by field(for details see). In the dialog box Grouping select option Months(Fig. 12). Click OK. The names of the months will appear on the left side of the pivot table (Fig. 13).
Drag box date in the PivotTable Columns area.
Drag box Index to the column area of the PivotTable Field List.
Select field Region, which will be displayed in the left column of the pivot table.
Select field Income, which will appear in the PivotTable Values area.

Rice. 11. Start by grouping by field date

At this stage, our pivot table looks rather primitive (Fig. 14). I really don't like the labels Row names And Column names. It is inappropriate to display totals for Jan Plan And Jan Fact in column D, etc. But don't worry about appearance this pivot table, because no one else will see it except you. Starting from this point, we will create a report shell, the data source for which will be the pivot table we just created.

Creating a report wrapper. Paste in workbook blank sheet. Let's put aside the PivotTable tools for a moment and move on to the usual Excel tools. Our task is to use formulas and formatting to create a beautiful report that is not embarrassing to show to the manager.

Perform the following steps (Fig. 15).

In cell A1, enter the name of the report - Planned and actual indicators by regions.
Go to the tab home, click on the button Cell styles select format Heading 1.
In cell A2, enter the formula = MONTH (TODAY (); 0). This function returns the last day current month. For example, if you are reading this on August 14, 2014, cell A2 would read August 31, 2014.
Select cell A2. Press the key combination Ctrl+1 to display the dialog box Cell Format. On the tab Number click on the item All formats. Enter a custom number format in the form "From the month" MMMM "planned indicators"(Fig. 16). As a result, the calculated date will look like text.
In cell A5, enter a heading Region.
Enter the region titles in the remaining cells in column A. The region titles must match the region names in the PivotTable.
If necessary, add labels to the column for department totals.
Add a line at the bottom of the report Total for the company.
In cell B4, enter the formula =DATE(YEAR($A$2),COLUMN(A1),1). This formula returns the dates 01/01/2014, 02/01/2104, etc., the first days of all 12 months of the current year.
Select cell B4. Press the key combination Ctrl+1 to open the window Cell Format. On the tab Number In chapter All formats enter custom number format MMM. This format displays the three-letter name of the month. Align the text to the right edge of the cell.
Copy the contents of cell B4 to the range C4:M4. A row with the names of the months will be displayed at the top of the pivot table.
In cell B5, enter the formula =IF(MONTH(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fact, and for the current and future - Plan.
Add a heading to cell N5 Outcome. In cell O4 - Outcome, O5 - Plan, Р5 - % deviation.
Enter the usual Excel formulas used when calculating department totals, company total row, grand total column, and % variance column:
1. in cell B8, enter the formula = SUM (B6: B7) and copy it to other cells in the row;
2. in cell N6, enter the formula = SUM (B6: M6) and copy it to other cells of the column;
3. in cell P6 enter the formula =IFERROR((N6/O10)-1;0) and copy it to other cells of the column;
4. in cell B13, enter the formula = SUM (B10: B12) and copy it to other cells in the row;
5. in cell B17, enter the formula = SUM (B15: B16) and copy it to other cells in the row;
6. in cell B19, enter the formula =SUM(B6:B18)/2 and copy it to other cells in the row.
Apply the Heading 4 style to the captions in column A and to the headings in rows 4 and 5.
For the cell range B6:O19, select the number format ###0.
For cells in the P column, select the number format 0.0%.

So, we have completed the creation of the report wrapper shown in Fig. 15. This report includes all required formatting. The following section demonstrates how to use the GET.PIVOTAL.TABLE DATA function to complete a report.

Rice. 15. Report wrapper before adding formulas

Using the GET.PIVOTO.DATA.function to populate the report shell with data. From now on, you will be able to experience all the benefits of using the GET.PIVOTO.TABLE.DATA function. If you have cleared the checkbox that enables this function, return to the appropriate setting and return the checkbox (see description in fig. 6 or 7).

Select cell B6 of the report shell. This cell corresponds to the northeast region and the actual figures for January.

Type = (equal sign) to start entering the formula.
Go to the sheet from the pivot table and click on the cell that corresponds to the northeastern region and the actual figures for January - C12 (Fig. 17).
Press key Enter to finish entering the formula and return to the report shell. As a result, Excel will add the function GET.PICTABLE.TABLE DATA to cell B6. The cell will show $277,435.

Keep this number in mind as you will need it when comparing it to the results of the formula you will be editing later. The formula generated by the program has the following form: =GET.DATA.PIVOTAL.TABLE(" Income " ;'Fig. 11-14′!$A$3; "Region" ; "North-East" ; "Date" ;1; "Indicator"; "Fact"). If you've been ignoring the GET.PIVOTAL.TABLE function so far, it's time to get to know it better. On fig. 18 this formula is shown in edit mode along with a hint.

Function arguments:

Data_field. A field from the PivotTable Values area. Please note: in this case, the field is used Income, but not Amount in the field Income.
PivotTable. With this option, Microsoft asks you "Which PivotTable do you want to use?" It is enough to specify one of the pivot table cells. The entry ‘Fig. 11-14'!$A$3 refers to the first cell in the PivotTable where data is entered. Since in our case you can set any cell related to the pivot table, leave the argument unchanged. Cell address $A$3 fits in every way.
Field 1; element 1. In the automatically generated formula, the name is selected as the field name Region, and as a field value - Northeast. This is where the cause of the problems that arise when working with the function GET.PIVOT.TABLE DATA lies. Auto-selected values cannot be copied because they are hardcoded. Therefore, if you copy formulas in the entire report area, you will have to change them manually. For Northeast, substitute the cell reference as $A6. By specifying a dollar sign in front of the name of column A, you determine that the row part of the reference can be changed when copying the formula into the cells of the column.
Field 2; element 2. This pair of arguments specifies the field date with a value of 1. If the original pivot table was grouped by month, the month field retains the original field name date. The numeric value of the month is 1, which corresponds to January. It is hardly advisable to use such a value when creating huge formulas that are set in tens or even hundreds of report cells. It's better to use a formula that calculates the field values date, like the formula in cell B4. Instead of 1, in this case, you can use the formula MONTH (B $ 4). The dollar sign before the 4 indicates that the formula can assign values to the field date based on other months as the formula is copied into the row cells.
Field 3; element 3. In this case, the field name is automatically assigned Index and field value Fact. These values are correct for January, but for subsequent months the field value will have to be changed to Plan. Change the hardcoded field value Fact to link B$5.
Field 4; element 4. These arguments are not used because fields are over.

The new formula is shown in fig. 19. In a minute, instead of a hard-coded formula designed to work with a single value, a flexible formula was created that can be copied to all cells in the dataset. Press key Enter, and you will get the same result as before editing the formula. The edited formula becomes: =GET.PIVOTOMAT.TABLE DATA("Revenue" ;'Fig. 11-14′!$A$3; "Region" ;$A6; "Date" ;MONTH(B$4); " Indicator " ;B$5)

Rice. 19. Upon completion of editing, the formula GET.DATA.PIVOTO.TABLE is suitable for copying to all cells of the range

Copy the formula to all empty cells in columns B:M where results are calculated. Now that the report contains actual numeric values, you can make some final adjustments to the column widths.

In the next step, we will set up the GET.PIVOTO.TABLE.DATA formula to calculate the total planned indicators. Simply copying the formula into cell O6 will display the #REF! The reason for this error is that the word Outcome in cell O4 is not the name of the month. To ensure correct operation of the GET.PIVOTO.TABLE.DATA function, the required value must be in the pivot table. But since in the original pivot table the field Index is the second field in the column area, the data column Plan result is actually absent. Move the box Index so that it becomes the first in the column area (Fig. 20).

Rice. 20. Adjust the layout of the fields in the columns area so that a column appears Plan Outcome

Compare with fig. 14. There, in the COLUMN area, the field was the first to go date, which led to the fact that at first the columns were grouped by date, and within each month by plan / fact. Now the first field is the Indicator, and in the summary, the columns come first Plan, internally sorted by month, followed by all columns Fact.

Returning to the shell sheet of the report, stand in cell O6, type = (=) and refer to cell N12 on the sheet of the pivot table corresponding to the planned totals for the Northeast region. Click Enter. The result is the formula = GET. DATA. OF. A. PIVOT. TABLE ("Income"; 'Fig. 11-14′! $ A $ 3; "Region"; "North-East"; "Indicator"; "Plan"). Edit it: =GET. SUMMARY.TABLE.DATA("Revenue";'Fig. 11-14′!$A$3;"Region";$A6;"Indicator";O$5). Copy this formula to other cells in column O (Fig. 21). Please note that even when moving different areas of the PivotTable report, the wrapper works correctly. Of course, if you make some fields of the summary inactive, the shell will not cope with this ...

Rice. 21. Final report that can be presented to the manager

You now have a well-formatted report wrapper that uses values from a dynamic pivot table. Although the initial report creation took quite a long time, updating it is only a matter of a few minutes.

Report update. To update the report with data for future months, follow these steps:

Insert the actuals below the original data set. Because the table format is selected for the source data, table formatting is automatically propagated to new rows of data. It also expands the definition of the original PivotTable (I've already added the actuals for the entire year in the Excel file).
Go to pivot table. Right click and select Refresh. The appearance of the pivot table will change, but it's not scary.
Go to the report wrapper. In principle, everything has already been done to update the report, but it does not interfere with testing the results. Change the formula in cell A2, for example, to this: =OMONTH(TODAY() +31 ;0) and see what happens.

By adding new actual sales data every month, you don't have to worry about re-creating formats, formulas, and so on. The described process of updating the report is so simple that you will forever forget about the problems that arose during the preparation of monthly reports. The only problem may arise if the company is reorganized, as a result of which new regions may appear in the pivot table. To ensure formulas work correctly, make sure that the totals in your report match the totals in the PivotTable. When a new region appears, simply add it to the wrapper sheet and drag and drop the corresponding formulas.

I didn't think I'd ever say the following: “The GET.PIVOTO.TABLE.Function is the greatest boon. How did we exist without it before?

In the original, Jelen's initial data was arranged so that further formulas worked correctly only in July 2015. In the Excel file attached to this note, I modified the initial data, as well as some formulas so that everything worked, regardless of the date when you You will experiment with the attached Excel file. Unfortunately, the formulas had to be complicated.

Earlier, I already talked about the fact that when referring to a pivot table cell, instead of a regular reference, the GET.PIVOTO.TABLE.DATA function is returned (see). If you are interested How To overcome this inconvenience, I recommend referring to the mentioned note. If you are interested, Why this happens, and also, what are the positive aspects of the GET.DATA.PIVOTO.TABLE function, then I offer a fragment of the book by Jelen, Alexander. (chapter 15). The technique under consideration will allow you to cope with many problems that cause headaches for users of pivot tables, in particular:

Refreshing a PivotTable removes previously applied formatting. Numeric formats are lost. The results of adjusting the width of the columns disappear.
There is no easy way to create an asymmetric pivot table. The only option is to use named sets, but this method is only available to those using Data Model PivotTables and not regular PivotTables.
Excel cannot remember templates. If you need to create PivotTables over and over again, you will have to re-group, apply calculated fields and members, and perform a number of other similar tasks.

Download note in or format, examples in format

Well, let's start in order.

How to abandon the problematic function GET.DATA.PIVOTO.TABLE

Suppose, in the pivot table shown in Fig. 1, you need to compare the data for 2015 and 2014.

Rice. 1. Initial pivot table

Add the heading "% Growth" to cell D3.
Copy the format from cell C3 to cell D3.
In cell D4, enter an equal sign.
Click on cell C4.
Enter the / (slash) sign to indicate the division operation.
Click on cell B4.
Type -1 and press the key combination to stay in the same cell. Format the result as a percentage. You will see that the West region experienced a 43.8% drop in revenue (Figure 2). Not very good results.
After you finish entering your first formula, select cell D4.
Double click on the small square located in the lower right corner of the cell. This box represents a fill handle that can be used to copy a formula to fill an entire report column.

After copying the formula is complete, looking at the screen, you will understand that something is wrong - each region showed a drop of 43.8% over the year (Fig. 3).

Rice. 3. After copying the formula to all cells in the column, you will see that each region showed a drop of 43.8%

It hardly happens in real life. Any user will tell you that after doing the above steps, Excel will create the formula =C4/B4-1. Return to cell D4 and pay attention to the formula bar (Figure 4). It's just some damn thing! The simple formula =C4/B4–1 no longer exists. Instead, the program substitutes a complex structure with the GET.PIVOTAL.TABLE.DATA function. Why does this formula give correct results in cell D4, but when copied to the cells below it fails to work?

There was nothing like this in the days of Excel 2000. When I started seeing the GET.DATA.PIVOTE.TABLE function on a regular basis, I just hated it. When someone asked me at one of the seminars how it can be used for the good of the cause, I was dumbfounded. I have never asked myself such a question! In my opinion, and in the opinion of most Excel users, the GET.DATA.PIVOTAL.TABLE function was a product of evil that had nothing to do with the forces of good. Fortunately, there are two ways to disable this feature.

Go to cell D4 and type = (equal sign).
Enter C4.
Type / (slash for division).
Enter B4.
Enter -1.
Click Enter.

You have now created a regular Excel formula that you can copy into the column cells below and get the correct results (Figure 5). As you can see, you can create formulas in areas outside of the PivotTable that refer to the data inside the PivotTable. And those who do not believe that this is possible, let them perform the described actions on their own.

Rice. 5. Just type =С4/В4–1 from the keyboard and the formula will work as it should

Using the GET.PIVOTO.TABLE.DATA function to improve pivot tables

The following sections show you how to create a dynamic report that displays actuals for past months and plans for future months.

Creating a primitive pivot table. The initial data (Fig. 10) are presented in the form of transactions containing information on planned and actual indicators for each region in which there are company branches. Planned indicators are detailed at the level of months, and actual - at the level of individual days. Planned indicators are created for the year ahead, and actual - for the past months. Because the report will be updated every month, this process is greatly simplified if the PivotTable data source grows in size as new data is added to the bottom. In older versions of Excel, the creation of such a data source was carried out using a named dynamic range using the OFFSET function (for more details, see). When working in Excel 2013, just select one of the data cells and press Ctrl+T (create table). A named dataset will appear, automatically expanding as new rows and columns are added.

Create a pivot table:

Choose a team Insert → pivot table and then in the dialog box Create a PivotTable click OK.
In the PivotTable Fields list, select the field date. A list of dates will appear on the left side of the pivot table (Fig. 11).
Select any date cell, for example, A4. On the context tab Analysis, located in the set of contextual tabs Working with pivot tables, click the button Group by field(for details see). In the dialog box Grouping select option Months(Fig. 12). Click OK. The names of the months will appear on the left side of the pivot table (Fig. 13).
Drag box date in the PivotTable Columns area.
Drag box Index to the column area of the PivotTable Field List.
Select field Region, which will be displayed in the left column of the pivot table.
Select field Income, which will appear in the PivotTable Values area.

Rice. 11. Start by grouping by field date

At this stage, our pivot table looks rather primitive (Fig. 14). I really don't like the labels Row names And Column names. It is inappropriate to display totals for Jan Plan And Jan Fact in column D, etc. But do not worry about the appearance of this pivot table, because no one else will see it except you. Starting from this point, we will create a report shell, the data source for which will be the pivot table we just created.

Creating a report wrapper. Insert a blank sheet into your workbook. Let's put aside the PivotTable tools for a moment and move on to the usual Excel tools. Our task is to use formulas and formatting to create a beautiful report that is not embarrassing to show to the manager.

Perform the following steps (Fig. 15).

In cell A1, enter the name of the report - Planned and actual indicators by regions.
Go to the tab home, click on the button Cell styles select format Heading 1.
In cell A2, enter the formula = MONTH (TODAY (); 0). This function returns the last day of the current month. For example, if you are reading this on August 14, 2014, cell A2 would read August 31, 2014.
Select cell A2. Press the key combination Ctrl+1 to display the dialog box Cell Format. On the tab Number click on the item All formats. Enter a custom number format in the form "From the month" MMMM "planned indicators"(Fig. 16). As a result, the calculated date will look like text.
In cell A5, enter a heading Region.
Enter the region titles in the remaining cells in column A. The region titles must match the region names in the PivotTable.
If necessary, add labels to the column for department totals.
Add a line at the bottom of the report Total for the company.
In cell B4, enter the formula =DATE(YEAR($A$2),COLUMN(A1),1). This formula returns the dates 01/01/2014, 02/01/2104, etc., the first days of all 12 months of the current year.
Select cell B4. Press the key combination Ctrl+1 to open the window Cell Format. On the tab Number In chapter All formats enter custom number format MMM. This format displays the three-letter name of the month. Align the text to the right edge of the cell.
Copy the contents of cell B4 to the range C4:M4. A row with the names of the months will be displayed at the top of the pivot table.
In cell B5, enter the formula =IF(MONTH(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fact, and for the current and future - Plan.
Add a heading to cell N5 Outcome. In cell O4 - Outcome, O5 - Plan, Р5 - % deviation.
Enter the usual Excel formulas used when calculating department totals, company total row, grand total column, and % variance column:
1. in cell B8, enter the formula = SUM (B6: B7) and copy it to other cells in the row;
2. in cell N6, enter the formula = SUM (B6: M6) and copy it to other cells of the column;
3. in cell P6 enter the formula =IFERROR((N6/O10)-1;0) and copy it to other cells of the column;
4. in cell B13, enter the formula = SUM (B10: B12) and copy it to other cells in the row;
5. in cell B17, enter the formula = SUM (B15: B16) and copy it to other cells in the row;
6. in cell B19, enter the formula =SUM(B6:B18)/2 and copy it to other cells in the row.
Apply the Heading 4 style to the captions in column A and to the headings in rows 4 and 5.
For the cell range B6:O19, select the number format ###0.
For cells in the P column, select the number format 0.0%.

Rice. 15. Report wrapper before adding formulas

Select cell B6 of the report shell. This cell corresponds to the northeast region and the actual figures for January.

Type = (equal sign) to start entering the formula.
Go to the sheet from the pivot table and click on the cell that corresponds to the northeastern region and the actual figures for January - C12 (Fig. 17).
Press key Enter to finish entering the formula and return to the report shell. As a result, Excel will add the function GET.PICTABLE.TABLE DATA to cell B6. The cell will show $277,435.

Function arguments:

Data_field. A field from the PivotTable Values area. Please note: in this case, the field is used Income, but not Amount in the field Income.
PivotTable. With this option, Microsoft asks you "Which PivotTable do you want to use?" It is enough to specify one of the pivot table cells. The entry ‘Fig. 11-14'!$A$3 refers to the first cell in the PivotTable where data is entered. Since in our case you can set any cell related to the pivot table, leave the argument unchanged. Cell address $A$3 fits in every way.
Field 1; element 1. In the automatically generated formula, the name is selected as the field name Region, and as a field value - Northeast. This is where the cause of the problems that arise when working with the function GET.PIVOT.TABLE DATA lies. Auto-selected values cannot be copied because they are hardcoded. Therefore, if you copy formulas in the entire report area, you will have to change them manually. For Northeast, substitute the cell reference as $A6. By specifying a dollar sign in front of the name of column A, you determine that the row part of the reference can be changed when copying the formula into the cells of the column.
Field 2; element 2. This pair of arguments specifies the field date with a value of 1. If the original pivot table was grouped by month, the month field retains the original field name date. The numeric value of the month is 1, which corresponds to January. It is hardly advisable to use such a value when creating huge formulas that are set in tens or even hundreds of report cells. It's better to use a formula that calculates the field values date, like the formula in cell B4. Instead of 1, in this case, you can use the formula MONTH (B $ 4). The dollar sign before the 4 indicates that the formula can assign values to the field date based on other months as the formula is copied into the row cells.
Field 3; element 3. In this case, the field name is automatically assigned Index and field value Fact. These values are correct for January, but for subsequent months the field value will have to be changed to Plan. Change the hardcoded field value Fact to link B$5.
Field 4; element 4. These arguments are not used because fields are over.

Rice. 19. Upon completion of editing, the formula GET.DATA.PIVOTO.TABLE is suitable for copying to all cells of the range

Copy the formula to all empty cells in columns B:M where results are calculated. Now that the report contains actual numeric values, you can make some final adjustments to the column widths.

Rice. 20. Adjust the layout of the fields in the columns area so that a column appears Plan Outcome

Rice. 21. Final report that can be presented to the manager

You now have a well-formatted report wrapper that uses values from a dynamic pivot table. Although the initial report creation took quite a long time, updating it is only a matter of a few minutes.

Report update. To update the report with data for future months, follow these steps:

Insert the actuals below the original data set. Because the table format is selected for the source data, table formatting is automatically propagated to new rows of data. It also expands the definition of the original PivotTable (I've already added the actuals for the entire year in the Excel file).
Go to pivot table. Right click and select Refresh. The appearance of the pivot table will change, but it's not scary.
Go to the report wrapper. In principle, everything has already been done to update the report, but it does not interfere with testing the results. Change the formula in cell A2, for example, to this: =OMONTH(TODAY() +31 ;0) and see what happens.

I didn't think I'd ever say the following: “The GET.PIVOTO.TABLE.Function is the greatest boon. How did we exist without it before?

For PivotTables, the GET.PIVOTO.TABLE DATA function returns the data stored in a PivotTable report.

To get quick access to the function, you must enter an equal sign in a cell (=) and select the desired cell in the pivot table. Excel will generate the GET.PIVOTOMAT.DATA function automatically.

Disable GetPivotData Creation

To disable the automatic generation of the GET.PIVOTO.TABLE.DATA function, select any cell in the pivot table, go to the tab Working with pivot tables -> Options to the group Pivot table. Click the down arrow next to the tab Options. In the drop-down menu, uncheck the box CreateGetPivotData.

Using Cell References in the GET.PIVOTO.TABLE DATA Function

Instead of specifying the names of items or fields in the function GET.DATA.PIVOTO.TABLE, you can refer to cells located on the sheet. In the example below, cell E3 contains the product name, and the formula in cell E4 refers to it. As a result, the total volume for cakes will be returned.

Using PivotTable Field References

There are no questions about how links to pivot table items work, problems arise if we want to refer to a data field.

In the example, cell E3 contains the name of the data field "Count", and it would be nice to refer to this cell in the function, instead of having the name of the field in the formula GET.DATA.PIVOTO.TABLE.

However, if we change the first argument data_field to a reference to cell E3, Excel will return the error #LINK!

GET.PIVOTO.TABLE.DATA(E3,$A$3)

A simple addition of an empty string (“”) to the beginning or end of the cell reference will solve the problem.

GET.PIVOTO.TABLE.DATA(E3&"";$A$3)

A simple correction of the formula will return the correct value.

Using dates in GET DATA

If you use dates in the GET.PIVOTO.TABLE function, you may have problems even if the date appears in the PivotTable. For example, the formula argument below is the date “04/21/2013”, and the PivotTable contains a field with sales dates. However, the formula in cell E4 returns an error.

GET.PIVOTO.TABLE.DATA("Volume",$A$3,"Date","04/21/2013")

To prevent date-related errors, you can use one of the following methods:

Compare date formats in formula and pivot table
Use the DATEVALUE function
Use the DATE function
Refer to cell with valid date

Compare date formats in formula and pivot table.

To obtain the correct result, when using the GET.PIVOTOMAT.DATA function, make sure that the date formats of the formula argument and the pivot table are the same.

In cell E4, the formula uses the date format “DD.MM.YYYY” and returns the correct information.

Using the DATEVALUE function

Instead of manually entering the date in the formula, you can add the DATEVALUE function to return the date.

In cell E4, the date is entered using the DATEVALUE function, and Excel returns the required information.

GET.PIVOTO.TABLE.DATA("Volume",$A$3,"Date",DATEVALUE("04/21/2013"))

Using the DATE function

Instead of manually entering the date in the formula, you can use the DATE function, which will correctly return the necessary information.

GET.PIVOTO.TABLE.DATA("Volume",$A$3,"Date",DATE(2013,4,21))

Link to a cell with a date

Instead of manually entering a date in a formula, you can refer to a cell that contains a date (in whatever format Excel treats the data as dates). In the example in cell E4, the formula refers to cell E3 and Excel returns the correct data.

GET.PIVOTO.TABLE.DATA("Volume",$A$3,"Date",E3)