Tip #1: Convert SmartArt to Shapes
One of the first tricks Excel users learn when building dashboards is the ability to link the contents of a cell to a shape. This is great for displaying information in banners and KPIs (key performance indicators).
We can get artistically creative with our dashboards and free ourselves from the limitations of row and column dimensions.
Although liberating, the library of shapes can be flat and somewhat uninteresting.
SmartArt to the Rescue!
SmartArt was introduced to the world in Office 2007, and it provides an array of interesting shape combinations and artistic stylings.
We can use SmartArt shapes to display cell information in a way similar to regular shapes.
Example of SmartArt to Display Cell Data
We have a dataset where we want to display the totals for three apps.
We want to display this information in a visually appealing way, and we believe SmartArt shapes will work well for this report.
We access SmartArt by selecting Insert (tab) -> Illustrations (group) -> SmartArt.
We can select any SmartArt shape we wish. In this example, we’ll use the Chevron Accent Process shapes.
Customizing the Look of SmartArt
Once we have our SmartArt shape inserted, we are presented with new ribbons that allow us to customize the look, and in some cases behavior, of the SmartArt shape.
There’s a Small Problem
Unlike a shape object where you can select the shape and type a cell address in the Formula Bar to link the cell contents to the shape…
… we are unable to perform this simple link on a SmartArt shape.
But we CAN convert the SmartArt shape to a simple shape which then can be linked to cells in the traditional manner.
Select the border (edge) of the SmartArt object then right-click the SmartArt object and select Convert to Shapes.
This converts the object to a group of shapes. You can ungroup the shapes by right-clicking the group and selecting Group -> Ungroup.
NOTE: You can perform the equivalent of “Convert to Shapes -> Ungroup” by performing the Ungroup operation twice (i.e. Ungroup -> Ungroup).
Now that we have broken the SmartArt shape into its constituent pieces, we can link the individual shapes to cells just as we did when working with traditional shapes.
Also, we can customize the shape’s border, fill, font, etc. to our style.
This makes for a much more visually interesting report while retaining the dynamic nature of standard cells. If our data is updated and the totals change, the once-SmartArt shape will reflect the change in data.
Tip #2: Hide Unused Rows and Columns
When you finalize your dashboard, it’s a good idea to limit the area at which users can navigate.
A common tactic is to lock the worksheet so the user can’t change anything outside of a designated range, but that doesn’t prevent the user from seeing what is beside the dashboard.
There may be helper tables, calculations, datasets, etc. that we want to ensure remain hidden.
A great way to accomplish this (perhaps in tandem with sheet protection) is to select all the rows and columns outside the dashboard range and hide them.
Hide the Unused Columns
To hide the unused columns, select the first column you wish to hide (i.e. Column “I”) then press CTRL-Shift-right arrow.
Next, we hide the selected columns by either right-clicking any selected column heading and click Hide or select Home (tab) -> Cells (group) -> Format -> Hide & Unhide -> Hide Columns.
Hide the Unused Rows
To hide the unused columns, select the first row you wish to hide (i.e. Row 18) then press CTRL-Shift-down arrow.
Next, we hide the selected rows by either right-clicking any selected row heading and click Hide or select Home (tab) -> Cells (group) -> Format -> Hide & Unhide -> Hide Rows.
Tip #3: Select Objects From the Selection Pane
When you have several objects (i.e. shapes, icons, images, SmartArt, etc.) on your worksheet, it can become difficult to select a specific object when it is closely surrounded by other objects.
The Selection Pane provides a list of all Drawing Layer objects on the worksheet.
Hiding/Unhiding Shapes
By clicking the small “eye” icon to the right of each listed shape, you can selectively hide or display any shape on the worksheet.
Selecting Shapes
You can select multiple shapes by clicking the first shape, then hold CTRL to select the remaining shapes.
Renaming Shapes
If you have many of the same shape (i.e. Arrow1, Arrow2, Arrow3), you can double-click a selection in the list and provide a more useful name.
Tip #4: Using Linked Pictures
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.
Our Example
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.
=Dashboard!B3Next, 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.
Conclusion
Hopefully, these tips will bring new life into your dashboard building routine.
These and many more dashboarding techniques can be learned through my online course Visually Effective Excel Dashboards at XelPlus.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
