Here are two great ideas to create dynamic outputs e.g. for your dashboards: KPI cards and dynamic chart titles that automatically update when your data changes!
KPI CARD
A KPI card reports a single key value such as sales to date or a KPI - simple but great! They are common in Power BI, but you can also create them in plain old Excel. Here’s how.
- Insert a text box (Insert, Text box) or shape (Insert, Shape) - I like rounded rectangles – and format it as you wish.
- Select the shape and in the formula bar enter =(cell reference).
- If this is a number, the format in the text box or shape is the same as the source cell. 😊
- Extra tip: You can then add text around the number by double-clicking in the shape and typing!
CHART TITLE
Add a dynamic title to a chart e.g. with a date. Instead of accepting the default chart title or manually typing in your own title, you can link a chart title to a cell.
- Simply select the chart title box and in the formula bar enter =(cell reference).
- You cannot create a formula or use any functions here. But you can do that in the source cell…
- In the example shown, I want to include the latest date in the chart title. You can get this automatically using the function MAX on the date column in your source data or (as shown here) use an input cell (cell B25).
- I have created a dynamic title (in cell C25) using the date (in cell B25), which then appears in the chart title. Fantastic!
- You can build this text in parts and join them using &
- If you refer to a number or date, you must format this using the TEXT function.
- To get the correct format, first select a cell with the format you want, go into format cell (shortcut Ctrl 1); on the “Number” tab, select “Custom”, then copy the ‘format code’ from the Type box.
-
- Paste this into your TEXT formula inside quote marks.
- In the example shown, the final formula is
="Sales month to date (EURk), as at " & TEXT(B25;"DD.MM.YYYY")
- Extra tip: You can hide your dynamic title cell behind the chart if you want to.
THE FINAL RESULT
