Get pivot data

We want revenue and net income information on our dashboard, but they live in two different pivot tables in our data. Additionally, there is more information in those pivot tables than we want on our dashboard. The answer to this is—Microsoft's GetPivotData formula. Fortunately, you don't really need to spend much time figuring how to use the formula. Let's set up revenue and net income on our dashboard. To do this, follow these steps:

1. In cell D5 of the Dashboard sheet, type Year.
2. In cell D6, type Period.
3. In cell D7, type Revenue.
4. In cell D8, type Net Income.
   

Revenue

As a reminder, our revenue pivot table looks like this:

Now that we have our heading information, let's add revenue and expense data using these steps:

1. Select cell E5 on the Dashboard sheet.
2. Type the equal sign (=) to start an Excel formula.
3. With your mouse, select the Revenue tab.
4. Click on cell B4 on the Revenue worksheet. This should be the year value 2017. Hit Enter when done:
   

You should now have 2017 sitting in cell E5 next to Year. To add periods, follow these steps:

1. Select cell E6 on the Dashboard sheet.
2. Type the equal sign (=) to start an Excel formula.
3. With your mouse, select the Revenue tab.
4. Click on cell B5 on the Revenue worksheet. This should be period 1. Hit Enter when done.

   Note

   As these are just filters and not pivot table results, we can copy them.

5. Right-click on cell E6 on the Dashboard sheet and select Copy.
6. With your mouse, highlight cells F6, G6, and H6.
7. Right-click and select Paste.
   

Now that we have our year at the top and our periods, let's add some data. Use these steps:

1. On the Dashboard worksheet, select cell E7 and type in the equal sign (=).
2. Move to the Revenue worksheet, select cell B10 and press Enter.

This places the revenue amount for period 1 on the dashboard. If you highlight the amount, you'll see that the formula is a little strange. It looks like this:

=GETPIVOTDATA("Period Balance",Revenue!$A$3,"Year","2017","Period ID",1)

This is telling Excel to get the Period Balance value from the pivot table that starts in cell A3, where the year is 2017 and Period ID is 1. That works great for this cell, but there are two problems with this formula. First, as 2017 and 1 are hardcoded, this formula won't adjust when you copy cells for period 2, period 3, and so on. For the same reason, if the pivot table is changed to a different year or to reflect different periods, our dashboard won't update. We need to improve this formula. Also, since revenue is a credit, the amount appears as a negative, so we need to reverse the sign so that a credit appears as a positive.

To reverse the sign and make the GetPivotData formula more flexible, follow these steps:

1. Select the formula in cell E7.
2. Highlight just "2017" in the formula (including the quotes), click on cell E5, press the F4 key to add anchors (dollar signs), and hit Enter. The formula should now look like this:

   =GETPIVOTDATA("Period Balance",Revenue!$A$3,"Year",$E$5,"Period ID",1)

3. Highlight just the number 1 in the formula, click on cell E6 and key in a dollar sign ($) before the number 6 to anchor it. Hit Enter when done. The formula should look like this:

   =GETPIVOTDATA("Period Balance",Revenue!$A$3,"Year",$E$5,"Period ID",E$6)

4. Still editing the formula cell E7, enter minus (-) between = and GETPIVOTDATA to reverse the sign. The final formula should look like this:

   =-GETPIVOTDATA("Period Balance",Revenue!$A$3,"Year",$E$5,"Period ID",E$6)

5. Now, you can copy the formula to cells F7, G7, and H7:
   

Net income

We need to repeat this process to add net income. Our net income pivot table from Chapter 3, Pivot Tables – The Basic Building Blocks, looks like this:

To get net income below revenue, follow these steps:

1. On the Dashboard worksheet, select cell E8 and type in the equal sign (=).
2. Move to the Net Income worksheet, select cell B18, and press Enter. This gives you a default formula:

   =GETPIVOTDATA("Period Balance",'Net Income'!$A$3,"Year","2017","Period ID",1)

3. Select the formula in cell E8.
4. Highlight just "2017" in the formula, click on cell E5, press the F4 key to add anchors ($), and hit Enter. The formula should now look like this:

   =GETPIVOTDATA("Period Balance",'Net Income'!$A$3,"Year",$E$5,"Period ID",1)

5. Highlight just the number 1 in the formula, click on cell E6, and key a dollar sign ($) before the number 6 to anchor it. Hit Enter when done. The formula should look like this:

   =GETPIVOTDATA("Period Balance",'Net Income'!$A$3,"Year",$E$5,"Period ID",E$6)

6. Still editing the formula cell E8, enter minus (1) between = and GETPIVOTDATA to reverse the sign. The final formula should look like this:

   =-GETPIVOTDATA("Period Balance",'Net Income'!$A$3,"Year",$E$5,"Period ID",E$6)

7. Now, you can copy the formula in E8 to cells F8, G8, and H8:
   

Formatting

Now that we have a start to our dashboard, let's improve the look of our revenue and net income table. To do this, follow these steps:

1. On the Dashboard sheet, select cells D5 and E5.
2. Right-click and select Format Cells.
3. On the Font tab, set Font Style to Bold.
4. On the Border tab, click on the Outline and Inside buttons.
5. On the Fill tab, select a light-gray background for these cells and hit OK.
6. Highlight cells D6 through H6.
7. Right-click and select Format Cells.
8. On the Font tab, set Font Style to Bold.
9. On the Alignment tab, set Horizontal to Center.
10. On the Border tab, click on the Outline and Inside buttons.
    
11. On the Fill tab, select a light-gray background for these cells and hit OK.
12. Highlight cell D6.
13. Right-click and select Format Cells.
14. On the Alignment tab, set Horizontal to Left (Indent) and hit OK.
    
15. Highlight cells D7 through H8.
16. Right-click and select Format Cells.
17. On the Number tab, set Category to Number and Decimal places to 0.
18. Select the box next to Use 1000 separator (,).
19. Under Use Negative Numbers, select the red numbers with parentheses:
    
20. On the Border tab, click on the Outline and Inside buttons and hit OK.
    

Tip

We put boxes around the data because at the end, we are going to turn off gridlines in Excel. We want to highlight this data, not have it floating around in space. The lines help set this data off from other elements.

Now that we have the start of our dashboard, let's layer in some conditional formatting, starting with icon sets.