It’s no secret that being a power user in Excel has its perks.  Research shows that users who truly know how to use the power of Excel can potentially increase their salary by up to nearly $4,000 per year (dependent on their career).  If you don’t believe me, have a look at this awesome infographic from Skilledup showing you how you can benefit from learning Excel.

If you’ve mastered basic and intermediate skills in Excel, the next big step to achieve that Excel power user status is to learn how to make an Excel dashboard.  That being said, let’s help get you on your way to that next salary increase.

 

 

1. Determine the purpose of the dashboard

---

Work with the dashboard user(s) on determining the requirements for the dashboard.

This is where you are going to want to ask a million questions to the end user of the dashboard.  If you don’t, you’re going to regret it later because you’ll need to go back to the drawing board and rebuild everything if you miss something in this step.

In this step, you’re going to want to come up with the common goal for the dashboard. Truly consider what questions or problems the users are looking to answer or solve.

Now that we’ve captured the requirements, let’s move on to designing and modelling the dashboard.

 

2. Create a design and model of your Excel dashboard

---

Build a design and model of the dashboard by drawing it up in your favorite iPad/tablet app, or do it the old fashioned way with a pen and paper.

A great tool that I use to draw up my dashboards is Evernote’s Penultimate.  Remember this doesn’t need to be perfect – I’m sure I’ve made that quite clear below.

Dashboards provide an overview and answer to a specific question or a problem. Your goal here is to answer the question or solve the problem and to do this in a way that is clear and easily understood.

Don’t concern yourself with all of the bells and whistles, just focus on getting the problem solved or the question answered.  Be sure to circle back around with the individuals who will be using the dashboard to ensure you’ve captured everything that will be required for the final dashboard.

 

 

Once you’ve got your design, we can start getting our data organized.

 

3. Establish from where you will be pulling your data(data source), and build your data worksheet in Excel

---

Determine from where you will be pulling your data.

You’ve already gathered your user requirements which also means you should know what data will be required to build your dashboard.

That being said, follow these steps to get your data into Excel.

• Determine if you’ll require multiple data sources.
  • If so, will you need multiple data sheets for your dashboard?
  • Build a single worksheet or multiple worksheets based on your requirements.

• Is your data properly structured?
  • Data in Excel should be structured in such a way that you have column headings (field names) with row records for each of your data sets.  This will make it easy for you to summarize your data with formulas and pivot tables (more on pivot tables here).

Note: If your data is not structured, you can use Excel tools like text to column to cleanup your data.

• How often does your data need to be updated?
  • If data needs to be updated frequently, strongly consider trying to use direct connections to your database from Excel. Here are a few examples of what can be used (by no means is this a comprehensive list):
    • WebQuery connections to business intelligence software.
    • Connecting to an Access Database.
    • PHP/webserver ODBC connection – Microsoft Query.
    • SQL server connection.
  • If you do not need to update your data often, the next best option is to create a table once you’ve copied and pasted your data into Excel.  Here’s a great article on 10 Reason’s to use Excel’s Table Object.  Pay close attention to number 4 – automatic nomenclature.
  • If you don’t use a table, seriously consider using and creating dynamic name ranges for each of your fields/columns.  Name ranges or table names is the easiest and best method to use when building formulas on your calculations worksheet.

• Choose the method of import of your data into Excel and finalize your data worksheet(s).

 

4.  Create a calculation worksheet in Excel

---

Use formulas, pivot tables and form control reference cells in your calculation worksheet to build the engine of your Excel dashboard.  Your dashboard will literally run off of this worksheet so it plays an integral role in your final product.

• Review the requirements and the design/model of your Excel dashboard from steps 1 and 2.

• Build and summarize your information from your data worksheet(s) using formulas and pivot tables.  You are going to summarize information in a way that provides answers and solutions to your questions or problems in step 1. Here is a list of formulas that you may want to consider learning as per Chandoo’s Top 10 Formulas for Aspiring Analysts:
  • SUMIFS Formula.
  • VLOOKUP Formula.
  • INDEX MATCH Formula.
  • IF Formula.
  • Nesting Formulas.
  • Basic Arithmetic.
  • Text formulas.
  • NETWORKDAYS & WORKDAY formulas.
  • SMALL & LARGE Formulas.
  • IFERROR Formula

Of course Chandoo didn’t stop there in his article, so be sure to check it out for some bonus formulas.

• Structure your calculations worksheet in an organized fashion.

Note: Learn how to use ranking with slicers.

• Use name ranges in this worksheet to make for easy reference on your Excel dashboard.
  • The dashboard worksheet will not carry formulas and will simply refer to the summarized cells that you’ve created on the calculations worksheet.

Now that you’ve built the engine, let’s move forward to the most exciting part, the dashboard.

 

5. Create the dashboard worksheet in Excel

---

Use the summarized information in the calculations worksheet to build and draw up the dashboard that you modeled in step #2.

• Review your dashboard model/design that you created in step #2.

• Do your absolute best to make sure that the dashboard is fully visible on one page.
  • Having to scroll left to right or up and down to get the entire picture makes for a bad user experience.

• Build the interactive control sections of your dashboard and place any cell links into an organized section of your calculations worksheet.

For more on learning how to setup interactive features on your dashboards, checkout this Making Your Dashboard Interactive section of Chandoo’s website.

• Build out the remainder of your dashboard.
  • Add charts and summarized data that refer back to data sets on your calculations tab and that may also interact with your interactive control settings and cell links.
  • Add any additional conditional formatting or number formatting to your dashboard.
  • Format the dashboard to make it look clean and professional.

• Do a final review of the dashboard to ensure that you’ve captured all requirements outlined in step #1 by the users.

• Now that you’ve finished, hide the calculations worksheet to prevent the users of the dashboard from accidentally tampering with the calculations required to run the dashboard. 

• Thoroughly test the dashboard and review and check your results by manually running calculations and scenarios.  Fix any bugs that you may encounter.
  

• Save the dashboard and send it out to your soon to be happy users!

 

 

Well that’s my 5 step process on how to make a dashboard in Excel. Did I miss any crucial steps that you would have included?  What rituals or techniques do you add to your dashboards to make sure you’re getting the best result possible?  Make sure to let me know in the comment section below.

 

---

 

A Strange but Easy Way to Use Excel Slicers to Upgrade Your Dashboards

SIGN-UP TODAY AND GET THE FREE PDF

[hcshort id=”12″]