The Definitive Guide to Creating Dashboards in Excel

Gyana

16 min • 31 March, 2021

Dashboards are a vital component of any serious business. Such tools are endlessly helpful when it comes to tracking Key Performance Indicators (KPIs), monitoring your metrics, and following other key data points your business relies on to make well-informed decisions that will help you succeed and grow.

Here’s an example.

Imagine having a tool where you could see all your social media statistics in one place. You can see all your follower counts across all platforms, your post engagement rates, click-through rates, and so on, all laid out in an easy-to-understand way. In short, you can see the exact condition of your online presence instantly.

This is what dashboards are used for. Fast, accurate data and performance monitoring. You can implement dashboards in any area of your business, wherever you’re handling data. The best dashboards consist of elements that include:

  • Graphs
  • Numbers
  • Gauges
  • Tables
  • Charts
  • And more

Basically, you’re taking large quantities of data and making it easy to analyse through visual media. Sounds great, right?

So the question you’re probably wondering is how you get yourself a dashboard. Fortunately, the answer is relatively easy. You could use a third-party tool, or you can make your own using spreadsheet software like Excel.

This is what we’re focusing on today as we provide you with the absolute definitive guide on how to make your own Excel Dashboard for your business. We’ll share the complete step-by-step on making your own, plus exploring all the essential tips you need to know for the best outcome.

Ready? Let’s get straight into it.

Getting Started with Your Dashboard

Before we jump into making the physical dashboard itself, there are six considerations you need to be thinking of:

1. The purpose of your dashboard

Every dashboard needs a purpose. If you define why you need a dashboard, life becomes much easier when designing your product and putting each component together. It’s always important to have this goal in mind.

Some dashboard purpose ideas you could use include things like:

  • Monitoring your business’s overall performance

  • Monitoring the performance of a specific department or project within your business

  • Tracking the success rates of a project

  • Finding out the status of your business in a particular area

2. What information are you tracking?

What data points will you be using to create the information on your dashboard? There’s a ton of information out there, but your dashboard will only ever need the essential KPIs and metrics to give you the best overall view of how well you’re succeeding in your dashboard’s purpose.

For example, suppose you’re tracking how successful your social media campaigns are. In that case, you may be interested in your targeted ad campaign’s click-through rate. In this instance, this means your follower count KPI isn’t necessary and therefore doesn’t need to be included in the dashboard.

Filter down your data to what you need and don’t need before you start making your dashboard.

3. Where are you importing your data from?

Hand in hand with the consideration above, you need to be thinking about how you’re getting data from its source and into your dashboard. Are you able to automatically merge and integrate the data into your spreadsheet, or will you manually input the information? Are there tools available that will make this process simpler?

Make sure you’re doing your research.

4. How often will be you updating your dashboard?

Your dashboard update frequency will vary depending on its purpose (which is why it’s so important to define what this is!). Some dashboards require daily updates, some weekly, some monthly, and others may be quarterly. Some dashboards may even need updating on an hourly basis, so consider this and how your importing process is going to keep up.

How you design your dashboard (especially the raw data pages) will depend on how often it needs to be updated in this way.

5. Who needs access to your dashboard?

Dashboard information needs to be accessible to those who need it and those who make big decisions based on what they see. This consideration dramatically affects how you design your dashboard because you need to think about how its users will interact with it.

For example, if you’re creating a dashboard for a client, you will need to make it as straightforward as possible to use, designed to be as intuitive as possible.

6. How compatible does your dashboard need to be?

Combined with the point above, what format does your dashboard need to be exported to? Can it be accessible to anyone through Excel or OneDrive, or will other applications need to be able to open it?

You may want to embed the spreadsheet into a presentation or allow certain people to add data and make edits from third-party software.

For your dashboard to be successful, these are all thinking points you need to consider. Take some time to write down your thoughts and ideas and plan your approach. By doing this, you should have a clear idea about the type of dashboard you’re going to be creating in the following steps.

The Step-by-Step Guide to Creating Your Excel Dashboard

There are a ton of templates out there for you to get started with if you’re looking for something specific, or you can design your own, which is what we’re going to be focusing on today since this will give you so many more options and opportunities to have the dashboard that works exactly how you want it too.

Let’s get right into it.

Step One - Setting Up Your Workspace

Start by opening Excel and creating a new document. Then save the file with an appropriate file name like ‘Social Media Dashboard’ or ‘Project Dashboard.’ You want your dashboard to be easy to recognise and effortless to manage.

You should be on a blank screen like the one below.

Screenshot 2021-03-31 at 13.12.09.png

Next, set up your tabs. You need a tab for displaying your dashboard data, which will be your main visual focal point, and you need a secondary tab for inputting all your data.

You can do this at the bottom of the screen, so it looks something like this:

Screenshot 2021-03-31 at 13.12.18.png

It’s that simple. Now the framework of your dashboard is ready to start taking data!

Step Two - Setting Up Your Data Pages

Click on the ‘Raw Dashboard Data’ tab and start setting up the page to hold all the data you’re going to be working with. Again, this can be whatever you need it to be, but for the sake of an example, we’re going to be using social media metrics to track our social media campaign efforts.

Screenshot 2021-03-31 at 13.14.06.png

As you can see, we have inputted our metrics on the left-hand side, and we’re going to have this dashboard to be able to compare our metrics over the course of a full calendar year.

While we’re here (specifically if you’re using your dashboard to compare various time frames, like daily or monthly), you’ll want to head over to your Dashboard tab and create a drop-down menu, so you’ll be capable of switching between the different dates.

From your dashboard tab, click a clear cell that won’t overlap your design, and click Data > Data Validation.

Screenshot 2021-03-31 at 13.14.53.png

Then change the Allow value to a ‘List,’ click Source, and then highlight the cells you want the list to form from and click ‘Okay,’ and you’ll end up with a drop-down menu that enables you to cycle through the various months.

Screenshot 2021-03-31 at 13.15.57.png

It can become rather complicated to make your drop-down menu interactive (which is known as a ‘dependant drop-down menu), so the month changes with the data from the raw data tab, so we highly recommend this video tutorial that can show you how.

Step Three - Importing Your Data

You can now start inputting your data.

You can do this manually, but if your data is recorded in physical form, you have no choice but to input the data manually.

However, if you are working with online data, like social media statistics, you may be able to input everything automatically. There may also be third-party applications that can help with this process, depending on the data type you’re working with.

TIP: Don’t forget, you can format cells to display certain types of data, like time, currency, and percentages. Doing this will make it much easier to manage your data later on.

If manually, then go ahead and return to the guide once you have input all your data.

If you’re taking the automatic approach, follow the steps below.

First, highlight the cell you want to import data into, and then click the ‘DATA’ tab in the top menu.

Screenshot 2021-03-31 at 13.18.10.png

On the left-hand side, you can see the ‘Get External Data.’ Click this, and you’ll be able to import data from various formats, including:

  • Text documents
  • CSV files
  • Websites and HTML links holding table-convertible data
  • Another Excel spreadsheet or compatible table

For example, if you’re using accounting software to manage your business’s books, you can export all the data into a CSV file and then import this directly into Excel. You will need to play around with how the content is imported into your tables to get everything to line up correctly.

You may also be able to use third-party tools to help you import data automatically or enables you to convert data into CSV file formats that will allow it to be imported.

When you’re done, you should have a table that looks something like this:

Screenshot 2021-03-31 at 13.19.11.png

Step Four - Creating the Calculation Page

For some dashboards, you’re going to need to create a ‘calculations’ page, so you can process some of the data you’re using.

In the social media example we’re using, we will compare the figures and how they have changed over time, comparing the page’s growth on a month-by-month basis. However, Excel is a very powerful application, and there are seemingly infinite limits to the formulas you can create.

In this example, we want to work out a percentage increase or decrease in the Watch Time from the month prior. This can get very complicated, depending on the data you’re using, but we can try to make it as simple as possible.

Start by making a Calculations tab.

Screenshot 2021-03-31 at 13.19.57.png

Now divide the page up into Widgets that will calculate the various data points you need. We’ll use the watch time data point.

Be sure to use formulas and =SUM commands to fill in your data cells. This is so the cells import the data directly from your raw data tab. If you then change the data on your raw data page, all your calculations will update automatically.

Never input the data into this page manually. Always use formulas to prevent problems from forming.

Screenshot 2021-03-31 at 13.20.37.png

You can then input the formulas to find your data point. In this case, working out the rolling percentage change using the basic =(Cell-Cell)/Cell formula, and then changing the cell format to Percentage.

Screenshot 2021-03-31 at 13.21.01.png

You’ll need to repeat this process for every calculation you need to make.

Step Five - Creating Widgets

Now it’s time to bring your dashboard to life by creating the widgets you’ll use to look up information. For this point, you’ll want to add the following as a minimum:

  • A label that titles the widget
  • The datasheet value
  • Any calculation data
  • Graphs and visual accompaniments

You can easily use =(cell) formulas to input the data from the raw and calculations tabs or connect the widgets to your dependent drop-down menu to make it more interactive. It depends on the data you’re using and how you want to present it. This is where you have the opportunity to get creative.

If you’re adding graphs to create a visual representation of your data (in this example, how watch time has changed monthly), you can highlight the data you want to use and then click the Chart button in the header menu.

Screenshot 2021-03-31 at 13.22.27.png

By following the on-screen chart-creation wizard, you’ll end up with a dashboard widget that looks something like this:

Screenshot 2021-03-31 at 13.23.01.png

Simply repeat this process for every dashboard widget you want to create.

Step Six - Making Your Dashboard Visually Appealing

While the dashboard above functionally works, that doesn’t mean it’s going to any good. If you were showing this data to a client, they wouldn’t be able to tell what’s going on with ease.

Remember how one of the considerations of making a dashboard was to make it accessible as possible?

Step Six is all about tidying your dashboard up to ensure it looks the part, and there are endless ways you can do this depending on what you want it to look like. We highly recommend highlighting all the cells on your dashboard and filling them white to create a solid background, and then making your titles and widget labels nice and bold.

With a bit of playing around, you can get your Dashboard tab looking something like this:

Screenshot 2021-03-31 at 13.24.31.png

There are endless designs you can play with, but the idea is to create a dashboard that suits the goal you’re trying to fulfill, which will be individual to your business and what you’re trying to achieve.

The Best Alternative to Excel Dashboards

There’s no doubt that creating your own dashboard in Excel is hard work, and you only need to make one error in your formulations to have the whole thing mess up. Design-wise, it can take a large time investment to make your dashboard look professional.

Fortunately, there are much easier alternatives out there that aim to make your life easier and handles all the technical side of dashboard creation on your behalf.

Take Gyana.

Gyana is a no-code dashboard and report generator that will let any professional create a dashboard in minutes, without technical skills.

How does that work, you ask? Well, Gyana is a BI tool, meaning a software that can gather data, manipulate it, prepare it, use it to create visualisations, and then drop them into shareable dashboards and reports.

The advantages against Excel are evident. Instead of having to go through each step in this guide manually multiple times, you can use Gyana’s connectors and file uploads to bring in all the data you have scattered around your company. Then, with a simple drag-and-drop interface, you can make all the calculations and visualisations you need. Then, you can drop them into reports or dashboards, that you can share with your clients or team, in minutes.

If you find creating a dashboard in Excel too much of a hassle, give Gyana a try. There’s a free trial you can access in seconds, no credit card needed.

Essential Excel Dashboard Creation Tips You Need to Know

If you do decide to create your own dashboard, these are some essential tips you’re going to need to remember to make the whole process as simple and as successful as possible.

Don’t over complicate your dashboard

The simpler your dashboard, the better. You could spend hours and hours making a dashboard look as good as it can look, but the more you add to your project, the more complicated things are going to be. Try to keep refining your dashboard down, so you’re only keeping what’s essential.

Practice freezing your data

Dashboards get complicated fast when you scroll through your data and everything moves with the screen. Using the ‘freeze pane’ function, you’ll be able to keep certain rows or columns on the screen. This means when you’re scrolling around through your data, the identifying row column is always in view, minimizing the risk of misreading important information.

Color-code your data for easy information

It’s highly recommended you color-code your data to make it stand out and read clearly to whoever is using your dashboard. Take the percentages in the example we shared, for example. Notice how we changed the colour to show a positive percentage in green and decreases in red, making it incredibly obvious what the data widget is telling you.

Make the most of shapes

People using your dashboard may get a bit foggy if they see the same graphs and designs repeatedly, which is why it’s a good idea to mix things up. You can use the Excel shape feature to add interesting shapes to your widgets that will help your data stand out clearly, as long as you’re not overcomplicating things!

Don’t overcrowd your tabs

If you need to add more tabs at the bottom of your dashboard to make calculations or store dashboard data, then it’s highly recommended that you do so.

Don’t try and squeeze everything in your raw data tab if it doesn’t fit properly because you’re only going to make your life more complicated, as well as increasing the chances that mistakes will be made.

Avoid going heavy on the design

It can be tempting to make your design look amazing with tons of shapes and colors, but again, this will only make things more complicated and overwhelming for whoever is trying to use it. Instead, remember the key advice that simpler is better.

Make use of space

While on the subject of design, it’s always a good idea to spread things out if you have any doubt about your layout. Play around with ideas and see what works for you, but remember that cramming everything in together may only confuse some users.

Don’t forget your main goal

Going back to the first and foremost point of this guide, make sure you remember why you’re creating a dashboard in the first place. If you’re stuck and wondering how to proceed, always consider the best solution that will help you fulfill your dashboard’s goal.

Additional Resources

Conclusion

Microsoft Excel is part of many people's professional lives. Creating a dashboard in Excel is a valuable skill that's getting more and more relevant by the day. We hope this guide will help you find your way around the software and empower your operations.

Hero Promo 04.png