Learn how to use widgets in Excel to improve the visual quality of your presentation. Today’s article introduces you to widgets. With its help, we can display the main KPIs and trends. Widgets make the spreadsheet look better.
Let’s see the core components of a widget! We’ll show the following data:
- Name of the widget, for example, “Abandon rate”
- Value for the selected period
- Value for the previous period
- Change from the previous period (in %)
- Change from the previous year (%)
- Annual trend line
A modern excel dashboard contains widgets and key performance indicators. Because a space-saving layout is a vital component for any report, we have to implement the six components mentioned above.
The label is a dynamic text box, it’s linked to the data Worksheet.
Value for a current/previous period
If you want to display the actual value and the previous period’s value, use a simple link from the calculation sheet. No further calculation is necessary.
Change from a previous period/year
To calculate the changes using percentages, apply the formula:
= (actual value – previous period’s value)/(previous period’s value)
Annual trend line
If you want to create trendlines, the most effective way is to apply sparklines. Sparklines are needed a single cell! That is all we require. Create the mini chart on the calculation (or data) Worksheet and – with the help of the camera tool – link it to the main dashboard sheet. A small annual trend will appear with an autorefresh option.
How to customize Widgets?
- Go to View tab on the ribbon and disable gridlines
- Use flat color schemes to change the background-color of labels
- Merge cells to create better positions
- Apply bold numbers and darker font color for the current period
- Use smaller, lighter font size for previous periods
- Conditional formatting icon sets are perfect for visualization
- Highlight the minimum and maximum points on the sparklines