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:
In cell D5 of the Dashboard sheet, type
Year
.In cell D6, type
Period
.In cell D7, type
Revenue
.In cell D8, type
Net Income
.
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:
Select cell E5 on the Dashboard sheet.
Type the equal sign (
=
) to start an Excel formula.With your mouse, select the Revenue tab.
Click on cell B4 on the Revenue worksheet. This should be the year value 2017. Hit Enter when done: