Excel shapes can play a vital role in making your dashboards and reports look amazing and can also be used for functional and practical purposes. I’ve put together a short list of intelligent ways you can use shapes in Excel. We’re going to look at how to pretty up your dashboards and what you can do with shapes to make practical use of them.
Intelligent Tip #1: Use Excel Shape Layering to Build Attractive Dashboards
Excel shapes are more often than not used to improve and upgrade how things are looking in your spreadsheet. When people think of shapes in Excel, what likely comes to mind are flow charts and visually appealing spreadsheets. You can use smaller shapes and other shape properties to build some pretty nice looking dashboards. Our friends over at datapigtechnologies.com do an awesome job at explaining some different methods you can use to spruce up your reports and dashboards using shape layering (shape layering with datapigtechnologies.com).
Intelligent Tip #2: Use Shapes in Excel to Build Custom Button Links
Using shapes in Excel to create custom button links is an awesome way to navigate between worksheets, to link to documents on shared servers or simply to link to a website. Creating a hyperlink out of a shape is incredibly easy and painless to do so let’s have a quick look.
How to Create a Hyperlink Out of a Shape in Excel
STEP #1: Create the shape that you would like to use in your Excel spreadsheet. To do this, go to the insert tab on the quick access ribbon and then under illustrations, select the multi-shape icon and then select the shape of your choice.
STEP #2: Once the shape has been created, you can add text by right clicking and going to edit text. After you’ve added the text, type CTRL + K.
STEP # 3: After you’ve typed CTRL + K, you’ll notice the insert hyperlink screen will come up. From here you can select a link to an existing file on your computer, server or web page, you can link to a place within the document, you can create a new document, or you can create and open up an email with a predetermined email address, subject line as well as content. Below you’ll see that to place a hyperlink to another page in your Excel spreadsheet, we are going to go to “place in this document” and then we will select the sheet that we would like to link to.
If you want to create a link to a web page, another document on a server or on your computer, you would select the “existing file or web page” option and then you could either get the path to a document or type in the address to a website.
Here is what the final product will look like once you’ve built links to all of the worksheets within your spreadsheet.
Intelligent Tip #3: Use the Shapes in Excel to Dynamically Display Important Data Using Cell Referencing
Another really cool feature is that you can reference a cell using a shape in Excel by simply typing the “=” symbol and then selecting the cell that you would like to refer to in the formula bar. In order to make this dynamic, all you need to do is make sure that the cell that you are referring to is updating based on a formula that you are using to summarize your information. Have a look below.
Dynamic Cell Referencing Using Shapes in Excel – Final Product
Notice below that as you change your customer number that the values within the shapes on the dashboard update to reflect the new value that is contained on your calculations tab. This is a pretty cool feature considering not much was done to make it happen.
To summarize what you read above, Excel shapes are flexible and can help with changing the appearance as well as provide some functional and practical applications to your reports and dashboards. In three short points:
◊ You can use shape layering to build some really awesome looking dashboards.
◊ You can create button links using Excel hyperlink functionality combined with shapes.
◊ You can make the content and data within your shape dynamic by referring to dynamic cells within the formula bar.
Can you think of any other ways that make Excel shapes that much more amazing? Make sure to leave a comment with your ideas.
Buy the Dashboard Used in this Post for Just $5
Learn How to Solve Your Business Problems Using Excel.
Get exclusive access and updates to Excel tools, tutorials and content that will help you become the office hero!