Excel Dashboards: Your Guide to Getting Started

April 16, 2014 Excel  No comments

Excel Dashboards: Your Guide to Getting Started

 

Excel Dashboards are a great way to display your data. They give you a comprehensive snapshot of your data and save you the time and energy, because you don’t have to perform your own analysis of the data. They are very customizable and allow you to help steer attention to key trends, comparisons, and exceptions. Essentially, dashboards are a great tool for putting all of your information in one place and making it easy to read. This walkthrough of Excel Dashboards will teach you all the basics to create and use your own dashboard.

What to Include in Your Dashboard

The most important question to keep in mind when you’re creating your dashboard is what is important? To display the most important and relevant data, you should keep a few things in mind. The first thing to keep in mind is the performance indicators. For example, you can display new customer acquisition, demographics of potential customers, turnover, outstanding balances, cycle time, availability, etc. The next thing to think about including on your dashboard is decision points like the status of an ongoing operation or what’s going on in a certain area. Another thing to keep in mind is the gap to goal. So, using graphics that display how close the actual results are to goal is helpful information. Finally, you need to consider the end-user. You need to make it user-friendly and relevant to them or your dashboard won’t be used. You need to customize it to fit their terminology, objective, and scope of influence, so that they can understand and use it effectively.

Creating a Dashboard

To create your dashboard, you first need to determine your goal and the appropriate information to use. Now on a blank sheet, you’ll need to create the background for the dashboard. To do this, go to the Drawing Tools section of the “Format” tab, and you will be able to change the fill color of the cells in the worksheet or add shapes. Next, you can create charts, graphs, and table to add using the data from your other spreadsheets. After you create the visual parts of your dashboard, you’ll need to cut and paste the charts, graphs, and tables to the spreadsheet with your recently created background. From there you can arrange your visuals in a logical order and apply different effects to them.

Elements to Add to Your Dashboard

On your dashboard, there are many ways to represent your data. Here are a few examples of elements that you can add to your dashboard to represent your data.

  • Gauges: This is good to use if you have a single value that you want to compare to a goal, because it lets you show how well you are progressing by pinpointing where your data falls as compared to where you want and don’t want it to be.
  • Bar Charts: This type of chart is good for comparing different related parts of a whole. For example, they can be used to show something like overall sales as compared to how well each product/service sells.
  • Heat Maps: Maps like this can be used to show trends. For example, you can see where a certain product is popular or sells best.
  • Charts & Graphs: Charts and graphs are a quick and easy way to show your data graphically. They let you easily identify trends in the data and make conclusions.
  • Table: This type of graphic works well for organizing and filtering a set of data that can be easily interpreted.
  • PivotTable: This type of graphic is perfect for taking a long and complicated table and making it more relevant and use-friendly by highlighting the important information.

Creating an Interactive Dashboard

Interactivity allows the viewer to interact with the dashboard, by going to the spreadsheet containing the raw data that is summarized by the chart, graph, or table or changing the data that is shown. To create an interactive button, add a shape (for example a rectangle) to your dashboard. Then, you will need to apply the effects and formatting that you want. Next, you can add text to describe where the button will take the viewer or what it will do. Finally, to add the interactivity, you will need to hyper link the button to the spreadsheet you want it to be linked to. To add the hyperlink, go to the “Insert” tab and click on “Hyperlink.” From there, select Link to “Place in This Document.” You can then select the sheet and cell that you want the button to be linked to. You can also choose to link your button to a web page, another type of document, or a graphic by inserting the location you want in the “Link to” section of the Hyperlink dialog box.

Sharing Your Dashboard

Once you’ve finished your dashboard, it’s easy to share with others. You can send it as an attachment in an email or post it to the web. There are few different ways that you can share your dashboard on the web. You can save your workbook as a webpage. To do this, go to the Microsoft Office Button in the top left corner and select “Save As.” From the drop-down options, select “Web Page (*htm; *html)  and make sure to choose to select “Entire Workbook” if you want the information from your other tabs available or select “Sheet” if you only want your dashboard sheet on the webpage. Next, click “Publish” and the “Publish As Web Page” dialog box will appear. From there, you must select what you want published from the document. For example, if you want the entire workbook published, select “Entire Workbook.” After you have selected what you want to publish, you can add a title by clicking on “Change” under “Publish as.” Now, you must save your file to the drive, folder, web folder, etc where you want it saved by clicking “Browse” next to the “File name” box. For your webpage to update automatically each time you update the data, check the box “AutoRepublish every time this workbook is saved.” If it’s not an interactive dashboard, another simple way to share your dashboard is to take a screen shot, and simply upload the image to your website.

We here at DTA hope you now feel confident in your ability to use Excel Dashboards. It is our desire to bring you the best advice possible to effectively and efficiently use your desktop features so you can focus on your work. Subscribe to our blog to receive weekly Desktop tips. If you’re looking for more in-depth training check out our upcoming instructor-led, live online trainings.

Liked these Excel Database tips? Have questions? Leave your comments below; we’d love to hear from you.

Getting Social with Excel: Spread the word and share the knowledge!

Kevin – Desktop Training Academy

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>