Inserting dynamic cell values into shapes & text boxes in Excel
The 1st step towards building dynamic dashboards
When you’re building dashboards in Excel it can be frustrating having to insert your metrics into the standard grid layout. If you want to be able to move your metrics around and precisely place them on the page, then you need to add your metrics into a text box.
That seems easy but many people are never taught how to do it. I’m going to show you how and give you some insights into why this is the building block to more complex Excel designs.
First step: Under the “Insert” tab, select the text box option and click and drag to add it to your worksheet
Then click on your shape and select the “Shape Format” tab. Here you can remove the background and outline color. This will make it so all that people see is the text itself.
Once you have a nice clear text box, you can navigate to the formula bar (just above your workbook) and type “=”
Then select the cell with the value that you want included in your text box.
At this stage, you should now see your metric inside of your textbox. You can change your fonts, color, and style to make it match your design.
And voila! You now have text that can be moved freely across your workbook.
So why is this the first step towards building dynamic dashboards?
Using text in this way lets us break free from the traditional grid layout in Excel. Instead of relying on placing metrics in a cell, we can layer our metrics over shapes and designs in the background.
That opens us up to building dashboards using visual design principles that are similar to those in PPT. You can layer shapes and images then place your metrics on top. That gives you a huge amount of flexibility to build dashboards with almost any design that you can imagine.
If you want this template and others like it then join my newsletter. I send out free templates each month.
