11 Unexpected Things I learned From Dissecting Chandoo’s Excel Dashboard Template

by Brad Edgar | Nov 23, 2014 | LEARN EXCEL DASHBOARDS

When it comes to Excel, my favorite topic and the thing I have the most fun with is building Excel dashboards. The truth is, a dashboard is like a picture, it’s worth a 1000 words. You can LITERALLY at a glance determine the health of your business by simply plugging in your raw data into a dashboard and then getting some output that will either tell you the good news or the bad news. That being said, Chandoo has come out with a pretty slick product that allows you to dump pretty much any set of KPIs (key performance indicators) into the dashboard and have a beautiful looking finished product dashboard that summarizes your numbers. Today however, I want to look at 11 unexpected things that I learned from dissecting Chandoo’s Excel dashboard template workbook. (more…)

6 Excel Charts, One Excel Chart Guide: How to Choose the Best Chart For You

by Brad Edgar | Nov 12, 2014 | LEARN EXCEL CHARTS

When we are in the process of building an Excel dashboard or even just a chart for that matter, we want to be able to decide on a whim what chart type we need to use based on the result that we are looking for. My friend John Michaloudis over at myexcelonline.com was kind enough to share his Excel chart guide and even went so far as letting me post it up on my website with my logo to share with you (you’re the man John!). This amazing infographic is exactly what you need if you find yourself stuck or for those situations where you really don’t want to think about what the best chart approach might be.

Feel free to dive in and have a look and make sure to save the link to keep the InfoGraphic handy. You can checkout John’s site by clicking the link above or by clicking the infographic (this will bring you directly to his blog post).

If you have any questions, be sure to leave them in the comments.

(more…)

How the Large Formula Can Be The Secret Ingredient To Your Dashboard Preparation

by Brad Edgar | Nov 10, 2014 | DASHBOARD FORMULAS, LEARN EXCEL DASHBOARDS

The large formula is an excellent tool that I haven’t really used that much until just recently. The beauty about it is that you can search through a large set of data, and identify the first, second, third, fourth …..Nth largest number/value in any given data set for a column/field. When I started to really think about this, I realized that this could be a great tool (perhaps even the secret ingredient) to building detailed dashboards showing the top 3, 5 or 10 records for pretty much any data set that you could think of. In this post, you are going to use the large formula first to identify the top 3 records of your entire data set, then you’ll use the index formula in combination with the match formula to identify the corresponding values that will make up your entire record. We will then have a look at how you can find the top 3 records based on a certain criteria within your data set. Here you will also use the index match formula using multiple criteria to get your corresponding values to complete each of the top 3 records.

(more…)

3 Intelligent Ways to Use Shapes in Excel

by Brad Edgar | Oct 31, 2014 | EXCEL OBJECTS

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.

Summary

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

BUY THIS DASHBOARD.

If you enjoyed this article, makes sure to follow me on Feedly by clicking the link below.

The Simplest Ways to Make The Best of a Sparkline in Excel

by Brad Edgar | Oct 24, 2014 | EXCEL INTERMEDIATE, LEARN EXCEL DASHBOARDS

How do you make the best of a sparkline in Excel? Well I’ve decided to answer that question in this post. Excel sparklines are ideal for quickly plotting trends especially when you are looking at a data set / series or a report. There are several features that you may or may not already know about, but I will review the top 5 things that I feel will help you make the best use out of Excel sparklines.

(more…)

« Older Entries

Next Entries »