Excel Dashboard: Create Advanced Dashboards (With Template) In Just A Few Minutes!

Knowing how to create Excel dashboards is one of the highest-selling skills in the current job market. Not a lot of Excel users can perform it, and even if they can, it doesn’t live up to the current standards.

In this article, I’ll be showing you how to convert your blank spreadsheet into this gorgeous dashboard within a very short time, even if you are only getting started with Excel!

I’ve broken down all the steps precisely with visual aid, and have provided the tools you can use to make your dashboard even more interactive.

Go through the pros & cons list to know how volatile dashboards can be, and follow the tips in the end to avoid making mistakes in the process.

So, what are you waiting for? Let’s make some fancy-looking dashboards right now in Excel!

What Is Excel Dashboard?

Excel dashboard is basically an interface where analyzed data, KPIs or other tracked points are presented together for an assessment.

Based on dashboard presentation, the management team is able to decide upon future actions about projects or employees.

These dashboards are customizable and can bear wide ranges of data that are refined through the process to produce an outcome that is easily comprehensible.

Thus, dashboard specialists are paid a large amount and are often part of a project team to generate project tracking in one place!

3-Way Preparations For Excel Dashboard

There are three important things you should touch-down before you go down on creating your dashboard. Going through this process will reduce a lot of your future troubles and allow you to generate the most spot-on dashboard!

Import

The very first thing you’re going to do is import your data set in your Excel spreadsheet. I prefer working my data on a fresh sheet so that I can organize it easily without messing up the original data.

You can easily do this by either opening your raw data file and then going to:

Worksheet tab > Right-click on the data sheet > Move or Copy > To Book (select your new book that you’ll be working on) > check Create a Copy > OK

This way, Excel won’t transfer your raw data to your new worksheet, rather it’ll copy it over so that you are allowed to work freely.

Or you can go to Data > Get External Data and select your external source for importing. In both cases, you’ll have a fresh start to push onward.

Clean-Up

Having clean data is very necessary before analyzing them and putting the results into the dashboard. Data containing unnecessary spaces, characters or symbols will disrupt your workflow and result in errors that will be very difficult to fix in the latter stages.

I prefer sorting my data into distinct columns and rows that are adjacent to each other. If there’s any blank cells, rows or columns in-between, you won’t be getting an accurate outcome.

Also, I create headlines for heading columns and format them into bold for better understanding. It’ll be taken care of automatically if you draw your data into a table.

Furthermore, a data set containing duplicate data will give you false outcomes. To fix this, select your entire data set and go to:

Data > Remove Duplicate Data

It’ll remove any duplicate values you may have in your set. Take a quick scroll through the data set to see if you can find out any typos or discrepancies that shouldn’t exist.

There are some other means of removing duplicate data as well. If everything checks out, you are free to take your data set to the next level!

Workbook Layout

The layout refers to the workbook that you’ll be creating your dashboard in. The way I work, I keep at least four worksheets, if not, more – for obvious reasons that I’m going to explain now.

Dashboard: This sheet will be the final outcome of our entire struggle. It’ll be organized and trimmed in the later section and better kept blank at this stage.

Raw Data: In this sheet, you’ll be importing your data and refine it as much as you can following the Clean-Up step.

Charts: It’s the sheet where you are going to generate PivotTables, charts or every other bit of analysis. You could do it within your other sheets, but it would simply become too much jumbled up and challenging to organize. Thus, a separate sheet proves to serve me better in this regard.

New Data: Always make room for new data so that the dashboard remains up to date very easily. If you import new data to your Raw Data sheet, it can mess up your dashboard until you’re done organizing and sorting it out.

The New Data sheet gives you the breathing space before you introduce the final version to the existing one. You are free to add as many New Data sheets as you want to ensure a smooth workflow.

Once you’ve sorted all of these out, you’re ready to proceed to the Excel dashboard and create meaning out of large data sets into interactive analyses.

How To Create An Excel Dashboard?

Now that you’ve managed your basic data, I’ll be showing you how you can create the most outstanding and up to the mark dashboard in Excel!

Step 1: Understand Your Requirements

To create your dashboard, you must look at your raw data and ensure you have a good mental mapping of what you’re trying to achieve. It’ll make visualizing your data much easier.

With a great understanding of your requirements, you’ll know which portions you’ll need to work with and the ones you can leave out.

It creates room for creativity and saves you time from working extensively with data that’s not going to be used in the end.

Once you know who your target audience is, what you’re trying to highlight and how you are going to analyze your data in accordance with these, you’ll be done with your first step.

Step 2: Think Of A Layout In Your Mind

Thinking of a layout comes hand in hand with understanding your requirements. There is a wide range of layouts that you can implement in your dashboard.

Your layouts can be very basic resembling the classic Excel look, or even more advanced that might look like the outcome of a sophisticated analytic software generated interface.

All of these can be achieved in Excel, it all depends on how much time and effort you want to put into it.

If the way your dashboard layout looks is going to impress your client, then I’ll ask you to keep following this article till the end because I’ll show you a captivating layout ready to be implemented in your work!

Step 3: Filter The Data

When working with a big spreadsheet with numerous data that you won’t necessarily be needing for your dashboard, the smart approach would be to use filters with your data.

Applying a filter would categorize and sort your data in the way that you’ll find manageable to analyze.

It also allows you to filter out unnecessary data and keep only the relevant ones to the dashboard very easily.

To apply a filter, you can follow this:

Select your data area
Go to Data > Filter at the ribbon area or press Ctrl+Shift+L
Check the data columns that you need
Once filtered, select the data and copy it
Go to the Charts sheet and paste the filtered data there

And that’s it! You’ll find the filter button at the top. Click there to include or exclude the information you want. You can also sort your data ascending or descending order based on your data column.

Step 4: Create A Table

It’s not a mandatory step, however, I’ll encourage you to go through with this so that you can make charts out of it using Pivot Analysis and have a more organized space to work on.

Also, tables create safer visuals and they don’t automatically include the Total row into the chart data, which might have led you to false outcomes.

Tables update the calculations automatically and the charts or other visuals are refreshed on the dashboard according to your tweaking.

To insert an Excel table, select your desired data set and go to Insert > Table. You can then sort and organize your data without worrying about messing up your outcome.

Also, it’ll enable you to visualize your data in the most convincing manner!

Step 5: Assess Which Charts Will Better Highlight Your Data

There is a wide range of Excel charts that you can choose from to be added to your dashboard. You can add bar graphs for comparative data, line charts to show progress or pie charts for displaying contributions.

So it’s up to you which charts you are going to be including in your dashboard. My advice is, don’t add too many charts because that renders your dashboard incomprehensive at times.

For my data set that shows sales records, profits and growth over time, I’ll be including three types of charts: bar graph, line chart and pie chart.

The bar graph alone shows the employees from highest to lowest product sales, and the colored areas in the middle will highlight which products they’ve sold the most.

The line charts will show monthly revenue generation as well as the number of units that have been sold each month.

The pie chart will compare the sales amount between employees, stressing on who brought in most of the money overall.

These three charts alone are enough to represent the dashboard’s main purpose. This way, you’ll have to assess your own charts that will bring more applicable and precise visuals.

Step 6: Compose The Dashboard

There are some tricks to composing your dashboards that not a lot of people will teach you. Let me show you how I go about it so that you can follow in my footsteps to convert your dashboard into the most dashing interface!

For starters, I go to the Page Layout tab and turn off the Gridlines and Headings. This provides more room for visuals and doesn’t interfere with the designs.

Now I’ll tell you a secret. When I’ve decided the layout I’ll be following, I go to Microsoft PowerPoint (yes, PPT) to design out that layout.

I use shapes as background and for the boxes of charts, graphs and numeric highlights. I use gradient colors with or without outlines to make the shapes look more neat.

Once I’m satisfied with the alignments, I copy all of these and paste them on to my Excel dashboard sheet.

Excel maintains the integrity of the PPT organizing and allows me to change color whenever I want. I can further re-adjust the shape positions in Excel to finalize the layout.

As per the charts, I pick a theme that goes with my design. I double-click on the chart to bring out the Format Chart Area option to make the fill color to no color and the outline to no outline, so that the chart doesn’t show its own separate box.

Nifty little tricks like this will make your dashboard into an outrageously official design with formatting options at your fingertips. And the best part is, it’s only a one-time designing process as the dashboard will keep on updating itself no matter how you alter your original data set.

Step 7: Fine Tune For Perfection

Further fine tuning of the dashboard can be achieved in Excel. Go to Find & Select and click on the Selection Pane.

There, you’ll find all your shapes imported from PowerPoint lying around. Select all of them and group them together into one for convenience.

Having that group selected, you can add shadow, highlights or reflection that’ll give your dashboard a sophisticated look.

Add Slicers to your Table data to make the dashboard into an interactive one. Adding a Timeline will further enhance your data visualization.

You can copy & paste these Slicers & Timeline pieces onto your dashboard and place them inside your designated shape.

Clicking on each of the slicer options or timeline will show you visual data based on that filter instantly! This makes your dashboard as lively as can possibly be!

Step 8: Customize The Shapes & Graphs

Understanding color combination comes in as a great help in customizing the shapes & graphs and colorizing them.

I’ve picked an overall blue theme for the dashboard, you can settle for any color you want according to your taste and requirements.

For the graphs, I only keep the Axes, Gridlines & Legend and compress it down to fit inside my premade layout box designated for this cause.

One of the line charts contains Axes, Data Labels & Gridlines while the other excludes Data Labels since the numbers are too large that generates a messy look.

I also remove the filter options by selecting the chart, going to PivotChart Analyze and selecting ‘Hide all’ from the Field Buttons option.

As per the slicers and timeline, I right-click on them, go to Slicer Settings and untick the Display header option.

Lastly, I throw in the same color as my theme to the slicers and timeline to make it look seamless. Simply select your Slicer, go to the Slicer tab on top and select a design from the Slicer Styles.

Right-click on a style and select Modify, highlight Whole Slicer and click Format. There, go to the Fill tab and select the color closest to your theme color and hit OK.

Additional touches may include text boxes containing the Top Salesperson or Highest Selling Product. Remember that these won’t be updated automatically and you’ll have to manually adjust them.

[Note: If you have separate PivotTables for your different charts, you’ll have to connect the slicers and timeline to those individually. To do that, select your slicer or timeline, go to their unique tab and open Report Connection. There, check the charts that you want to be responding with your slicer or timeline control and hit Okay once you’re satisfied.]

Step 9: Have Room For Additional Data

I always keep a sheet called New Data so that I can bring in additional data sets at any time of my operation. I initially paste the raw data into the New Data sheet and filter or sort it the way I did with my previous data.

Then I copy all of it and paste it inside the table that I have in my Raw Data sheet. Once done, you’ll see your dashboard updating itself automatically including all your charts and graphs!

Something you should know, the shapes brought in from PowerPoint react to Excel themes. If you feel you’re not comfortable with the current color scheme, simply go to Page Layout and change the Colors or Theme to see your entire Excel dashboard changing its scheme!

A note: if you include a custom design to your Slicers & Timeline, you’ll have to manually match the theme color from the Modify section that I’ve previously shown you. If you use a generic color scheme, it’ll change automatically with the color option.

Important Tools & Functions For Excel Dashboard

Let’s scroll through some of the most essential tools & functions in Excel that’ll upgrade your existing dashboard to the professional grade!

Formulas

If you are creating your dashboard with spreadsheet data and keeping the original Excel grids, then Excel formulas will come in very handy.

Formulas like SUM, IF, AND and so on should generate effective data that is automatically updated in your dashboard without demanding any interference from your account!

Macros

Excel Macros are a more advanced feature in this field. Macros allow you to record your button-presses and convert them into an action that you can witness live.

For instance, in your dashboard you can add a hide/show button that opens up a specific chart upon clicking on it. This is attained by including Macros into the mix and it can literally spice up your dashboard a few degrees instantly!

Conditional Formatting

When conditional formatting is paired up with Excel functions, your dashboard data will gain a depth to its values.

For instance, color-coding sales rates that are beneath the expectation mark are to be colored red, the one in the optimum area is blue and the good sales numbers marked green automatically should render your dashboard visuals more interactive.

Drop-Down Lists

Drop-down lists in Excel helps out in organizing the dashboard by a huge margin! It saves up space in the main dashboard which gives you room for adding more information and prevents it from becoming a data-jumbled mess.

You can add these from the Data tab > Data Tools > Data Validation button.

Similarly, you can utilize Scroll bars and Check-boxes in your dashboard to draw out the precise analyses that even you might not have foreseen in a very compact space!

Slicers & Timelines

As you’ve seen from my workflow, slicers & timelines let you filter your data focusing on one or many items that you want to highlight. You can even press & hold Ctrl to select multiple data sources and your dashboard will react accordingly!

Charts

Charts are a great tool to add to your dashboard and the grand aspect about them is that they update instantly based on your tweaking and filtering!

PivotTable

PivotTable lets you sort your data to generate the most refined visuals so emphasize on the specific data that you’re targeting.

Excel Dashboard: Pros & Cons

From my experience, I’ll shed some light on some of the good sides and bad sides of Excel dashboards so that you know exactly what you’ll be dealing with when you invest yourself in it.

Pros

Some of the positives of Excel dashboards that I found to be highly pleasing are:

Dashboards are interactive and they update instantly, reacting to your filtering buttons
Great for presenting KPIs in offices
Data visualization with dashboards is one of the most effective tools in your professional life
Dashboards hold all of the vital data including visuals in one place, so it’s very easy to comprehend in minimum amount of time
It’s customizable, thus leaves room for numerous paths that you can explore

There are way too many good things about dashboards and these are the most vital ones that you must be wary of.

Cons

Although these dashboards are highly rewarding, there are some downsides to them as well that I feel obligated to inform you about:

Excel dashboards aren’t very easy to master at first go. You’ll need time, patience and practice to generate the most effective dashboards for your project
Complex dashboards can be messed up in an instant, so precision and constant attention is required for a neat outcome

If you keep this in the back of your mind while working with your dashboard, you’ll get past the barriers over time and become the dashboard master in your workforce!

Tips For Properly Utilizing Excel Dashboard

Last but not the least, I’ll leave you with a few tips for utilizing your dashboard to the full potential that I’ve had to learn the hard way.

If your dashboard is quite difficult to understand, it’s always wise to leave a tutorial guide.
There are some combination charts that perform the same task as two or three separate charts. Use them to save you space in the area.
Always name your charts, tables and sheets so that you can always distinguish between them whenever the need calls for it.
Refrain from using formulas that’ll take a large amount of time to calculate the outcome. It’ll slow down your dashboard interactions and might even crash your software.
Keep ONLY the relevant tabs in your spreadsheets. Just because you can include many options doesn’t mean you have to.

Remembering these tips should refine your workflow and spare you the pain of starting your dashboard from scratch again.

Carry Out Dashboard In Your Work

Now that you’re at the very end of this article, I hope it has been helpful for you in many regards. This article has broken down every bit of task I had to undergo while generating my Excel dashboard.

Thus, it’s not only informative for the sake of dashboard making, but also it highlights some other aspects and features of Excel in the process.

For your convenience, I’ve included the Excel file that I worked on during this article’s generation.

You can swap out my data with your very own to use this as a template, or you can start from scratch and try to create a unique dashboard of your own.

Similar Post: