How To Create A Dashboard in Excel?

How To Create A Dashboard in Excel?

Most people take Excel to be a number crunching tool… boring and unattractive.

Actually, Microsoft Excel has been slowly transforming… adding more and more features that make presentation of number much better with every new edition of Excel.

The Excel 2016, 2019 & Microsoft Office 365 versions are extremely powerful and you can create dynamic dashboards with buttons, check boxes, drop downs, scroll bars, conditional formatting, pivot charts, slicers, and a host of other features, just by using Excel.

What is an Excel Dashboard?

An Excel Dashboard is a simple tool for the management, allowing them to get a glance at the business in just a single screen – without looking at multiple sheets of data or charts.

See the dashboard created in the image here. You may not even realize that it was created in Microsoft Excel.

Plus, it is not just a pretty dashboard. There are things that can change – with  the click of a button, you can change the division, product, year, and the entire set of graphs, numbers, charts will change dynamically. Try that in PowerPoint….

Is Excel Good for Creating Dashboards?

Yes, Excel has all the important features that can make a complete dashboard. In fact, small companies rely on Excel for almost all their work as they can’t afford the more expensive Business Intelligence software. You can create multiple charts, KPIs, to be displayed in a single page, and set them to refresh automatically upon opening.

There are several options to add buttons, dropdowns, check boxes, radio buttons that add interactivity to Excel dashboards. With these control, plus macros, and Power Query running in the background to fetch and clean data, is all you need to make any kind of dashboard in Excel.

How to Create a Dashboard in Excel from Scratch?

It is pretty easy to make a Dashboard in Microsoft Excel. Here are some steps for you to follow:

Step 1: To make a dashboard in Excel, you must begin with the end picture in mind.

If you do not have a clear end picture in mind, it may make sense to do some brainstorming on a piece of paper.

Better still, gather a bunch of colleagues and managers, and do this in a meeting, to understand what are the key things you must measure. KPI, ROI, Net Margin, Market Share, Cost per acquisition etc. are good things to measure. This can be translated into any industry. So you can measure Number of hotel nights booked, Number of patients admitted vs discharged, average hours worked per week etc.

You also have to think about the Dimensions you would like to slice this data on – by Country Department, Quarter, Zone, Area, Cost Center, etc. This will allow you to compare the metrics from the different periods and it becomes much easier to track if the performance is getting better or worse.

Step 2: Collect the Required Data For the Excel Dashboard

Once you know what you want to track and measure, you must make sure that this data is collected, and is available. You might have to extract it from your ERP or internal databases or systems.

Microsoft Excel can load any kind of data – be it text file, CSV file, directly scrape off the data from the Internet, or even read it from a SQL RDBMS database.

Step 3: Clean the Data

Extract & take the required data. You will have to clean it using Excel Formulas and functions, or you can use Power Query. Remove useless columns.

Use of functions like these can be handy

• FILTER
• CONCATENATE
• LEN
• PROPER
• DATEDIFF
• TODAY
• NOW
• LEFT
• RIGHT
• FIND

Step 4: Build Connections With Master Data

Raw data on its own may not be very useful. Build the right VLookups to link with Master Data for our Dashboard Creation in Excel.

You can link Excel to other external or internal workbooks, and worksheets. You must know how to copy or link data from external worksheets and workbooks.

The good thing is that once the connection is built, you don’t have to open the associated file to use it.

Excel will automatically open the linked files when the management dashboard is being refreshed.

Step 5: Build Measures & Calculations For the Excel Dashboard

A few new formulas may be required to be built, to calculate the key measures. This requires knowledge of advanced excel formulas like the ones here. You may need more or less….

Starting out with these key Advanced Excel Functions is going to be crucial for a good Excel Dashboard.

• VLOOKUP or XLOOKUP
• INDEX,
• MATCH,
• OFFSET,
• SUMIF,
• LARGE
• SUBTOTAL
• FILTER

Step 6: Build Charts & Tables With the  KPIs in the Excel Dashboard

The next step is to start building the dashboards with the KPIs, Charts, Pivots to summarize the data at the right granularity.

You can add pivot tables, Excel Tables, Charts, and Summary Calculations. You can also use Conditional Formatting for these values, to be able to pin point them easily. Once the Dashboard looks great, you move to the next step in Excel Dashboard creation, where interactivity is added.

Step 7: Add Interactivity With Excel Controls

Once the KPIs are in place, it is required for your to build the interactivity in, to slice and dice by country, division, zone, periods like quarters, months etc. You can add Slicers, Buttons, Dropdowns, Check boxes to check or uncheck, and based on the selection, the appropriate charts and KPIs can be updated.

Step 8: Begin Using The New Dashboard

That’s it. Your Excel dashboard is ready. Begin using it. Tweak it as needed. Share it with other colleagues, so you can gather more feedback, and improve it. Once a dashboard becomes ready, it becomes easy to measure things throughout the company, based on the same benchmark or metrics.

Learn How To Create Excel Dashboards

Of course, to start building such Excel Dashboards, you must know some Basic & Advanced featured of Excel.

Once you know these, you can begin to learn how to create such dynamic dashboards using Excel.

Attend Excel Dashboard Training in Singapore

You can attend our 2 day Most Popular Excel Dashboards Training in Singapore – it starts at the basics, and covers step by step features required to create such dashboards with Excel, on your own.

Plus we provide you with snacks, tea/coffee breaks, so everything required is available… and you can focus on learning and building the Excel dashboards quickly, and easily.

Intellisoft runs Dashboard MasterClass in Singapore, Malaysia, Indonesia, Hong Kong, Dubai, Qatar, India, and many other countries.

If you would like to join our next Excel Dashboard Training Course, simply contact us, and we will advice you of the dates, training schedule in the city of your choice.

Contact us to enroll for this practical, hands on training. We will email you the Course Brochure too, so you can see the topics covered in detail.

Customized Corporate Training is available for this Excel Dashboard Training in Singapore

So far we have conducted a number of such classes for the staff of large MNC companies, teaching them how to create such reports and dashboards with ease, and they have been highly popular and successful… requiring us to do multiple repeat sessions for other divisions, countries also.

Do learn these simple techniques to create useful, and interactive Dashboards using Excel and beyond. You will be amazed at the ease with which you can track and measure things. Better still, everyone will be on the same page.

Removing confusion from the corporate culture is a major step towards success with Excel Dashboards

Cheers,
Vinai Prakash
Intellisoft Systems Team
Singapore