A great way to make your dashboards and reports stand out from typical offerings is to “disconnect yourself from the grid”. What this means is to not be restricted to the height and width dimensions of rows, columns, and cells.
We want to have the ability to size and position our visualizations independent of the worksheet grid.
We want to give the user the ability to select an App from a dropdown list then display a line chart next to the selection.
Step 1: Setup Data Validation
Select the cell for the App dropdown and click Data (tab) -> Data Tools (group) -> Data Validation. Select the Allow as List and the Source as the cells containing the App names.
Step 2: Create the Data Preparation Table for the User’s Selection
We need to create a list of monthly sales for the selected App from the Step 1 dropdown list.
We begin by creating a link in cell A10 to the dropdown cell on the dashboard.
Next, we create a lookup to find the selected App in the data and return the related monthly sales. This can be performed with any lookup function you prefer (Lookup, VLookup, HLookup, Index/Match, XLookup). We will use HLookup.
In cell B2, enter the following VLookup function.
=VLOOKUP($B$10, $A$5:$G$7, Column(), False)
NOTE: The COLUMN() function is used to dynamically generate column return positions for the VLOOKUP function (ex: 2, 3, 4, 5, 6, and 7).
This could also be performed using Index/Match with the following formula:
=INDEX(B5:B7, MATCH($A$10, $A$5:$A$7, 0))
If you are running a more recent version of Office 365, you could use the XLookup function as follows.
=XLOOKUP(Dashboard!B3, A5:A7, A5:G7)
Step 3: Build the Line Chart Using Sparklines
Select cell H10 and click Insert (tab) -> Sparklines (group) -> Line.
The Data Range is the returned values from the Step 2 lookup function(s) and the Lookup Range is cell H10.
Our result is as follows.
Step 4: Display the Sparkline on the Dashboard
Now we display the Sparkline chart in cell H10 on the dashboard by selecting the Sparkline in cell H10 and clicking Copy.
Next, select the dashboard sheet and click the lower part of the Paste button and select Linked Picture.
The results are as follows.
The linked picture can be positioned and sized independently of the row or column dimensions.
If we select a different App from the App list, the Sparkline on the data-prep sheet changes which is then relayed to the dashboard sheet.