In most cases when working with data in Excel, you will be adding data directly into cells, or deriving data through pivot tables or formulas. But when working with dashboards, it is important to consider visual elements for end users. A way to get better-looking dashboards is through using shapes to convey key pieces of information.
Using Shapes in Power BI
In Power BI, if you want to quickly draw attention to a single figure, use Card visualization. This creates a box that shows the aggregation of a single figure; such as the sum of all sales.
However; in Excel, it is more challenging to create something similar to the card visualization. To adjust the font size in a single cell may require an increase in the height of an entire row or width of a column.
In order to create cards similar to Power BI, the best way is to create a shape or text box. This allows for your visuals to not be tied to a single cell!
The Data Tab
Before starting to create the dashboard, it is good practice to create a second tab where the data is stored for the dashboard.
This allows the dashboard to focus on creating effective visuals, and provides a secondary location for the less visually appealing elements.
Collect the data you need for your shape.
On your data tab, you need to collect the information that you need for your dashboard.
This tab is not intended to be viewed by the final audience of the dashboard. As a result, it is more important to ensure proper functionality versus effective visuals.
When using PivotTables or array formulas, it is more important to ensure that data won’t overlap if more information is added.
Name a cell for your shapes data.
Shapes can only get data from named cells. In this example, we have named the cell TotalSales.
If a PivotTable is used to get total figures, use the GETPIVOTDATA function instead of referring to the table directly!
All work on the Data tab is now complete. If users do not need to audit data on the tab, you can right-click on the data tab and choose to hide the worksheet.
The Dashboard Tab
The dashboard tab should only contain elements that you want to show to the end users of the report. This can include slicers for data filtering, dropdowns, maps or other visual elements.
Unlike the data tab, it is very important that all elements on the dashboard tab are visually appealing.
Creating a shape to store data.
Shapes are available under the Insert > Shapes on the ribbon. From there, you can pick the shape that makes the most sense for your dashboard.
Add data to the shape.
To add data to the shape that you have selected, add the named range in as a formula.
Once you have added the named range into the formula bar, it links the data from the data tab to the shape.
Format data in the shape.
Modifying the look of data within a shape is similar to modifying data in a cell; you can bold text, change the font or other similar effects.
The important thing to realize is that this will not impact any underlying cells or your data, so you can use larger fonts without impacting other data on the worksheet!
Adding text to describe the shape.
At this point, the data is on the shape, but we have no way of knowing what the metric on the shape is.
To do this, we add a text box.
This should normally go above the text that is in the shape; though it can go elsewhere in the shape if it makes sense.
At this point, the text box has a default white background and black text. The next step is to fix the text box.
Fixing the colours on the text box.
The colour options for the text box are found under the Shape Format menu option. First, change the shape fill to the same colour as the shape containing the data.
Next, select No Outline under the Shape Outline to make sure that there is no border on the text box.
Finally, adjust the font, colour and size of the text within the text box to get it to match the fonts from the shape containing the data.
This looks great! We have a data card, similar to Power BI that will update as data changes in our data tab.
Making sure that the text and shapes move together.
Finally, we just need to make sure that the text box and the shape containing the data can move together.
To do this we group the two objects by holding down Shift and clicking on both objects.
Then, right-click to bring up the object menu and select Group. This will bring the two objects together, so that they can be used as a single object!
This is one way to make your dashboard data in Excel look more like a dashboard, and less like an Excel spreadsheet.
Other ways to improve your dashboards include:
- Effective use of slicers to control tables and PivotTables.
- Hiding grid lines to make the sheet look more like a canvas.
- Using timelines to filter data.
I look forward to making more posts on dashboards in Excel in the future!