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...