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:
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.
Before we jump into making the physical dashboard itself, there are six considerations you need to be thinking of:
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
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.
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.
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.
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.
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.
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.
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.
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:
It’s that simple. Now the framework of your dashboard is ready to start taking data!
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.
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.
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.
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.
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.
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:
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:
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.
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.
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.
You’ll need to repeat this process for every calculation you need to make.
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:
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.
By following the on-screen chart-creation wizard, you’ll end up with a dashboard widget that looks something like this:
Simply repeat this process for every dashboard widget you want to create.
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:
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.
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.
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.
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.
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.
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.
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.
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!
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.
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.
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.
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.
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.