Most of us certainly rely on our trusted MS Excel dashboard to operate our companies on a daily basis, but many of us struggle to transform that data into something that would pique people’s attention and make them want to learn more about it. So, how do you go about achieving this ostensibly impossible goal? This is where the Excel Interactive Dashboard comes into play. What is an Excel Dashboard, first and foremost, before we dig into its depths? A dashboard is a visual representation of the critical information that an individual must send to a customer in order to achieve one or more objectives that may fit fully on a single computer screen and thus be watched at a glance. It’s a thorough and detailed visual report or analysis of your project that can be shared with others involved. If you don’t know how to do it, creating an excel dashboard may be laborious, time-consuming, and complicated. But don’t worry; that’s where we’ll be entering.
In this blog, we will cover all the basics you need to understand to create a beautiful interactive dashboard for your data.
- Build the pivot table and charts
- Making the Dashboard
- Making the backdrop
- Formatting the sheet to finish the dashboard.
- Tips and tricks to better your experience with the Excel dashboard
These are the few basic steps you will require to allow you to create an interactive dashboard in Excel for your data. Read through for more information.
–
Build the pivot table and charts for interactive dashboards in Excel
To summarise, analyze, explore, and present summary data, you may utilize a PivotTable. PivotCharts provide visuals to the summary data in a PivotTable, allowing you to observe comparisons, patterns, and trends more readily. Both PivotTables and PivotCharts let you make educated decisions about your company’s vital data. You may also leverage existing PivotTables to construct new tables, or link to external data sources like as SQL Server tables, SQL Server Analysis Services cubes, Azure Marketplace, Office Data Connection (.odc) files, XML files, Access databases, and text files.
Dashboards aren’t exclusive to Excel; they may also be produced with PowerPoint. Excel dashboards provide a more dynamic way of displaying data than PowerPoint dashboards, which are more linear and static. In Excel, an interactive dashboard is essentially a slice of visualization that allows your data to tell a story. A dashboard is only functional if it is dynamic, user-friendly, and compatible with the computer you’re using.
Step by step process Build the pivot table and charts
- Firstly, open your Excel document.
- Now select your data on the sheet.
This data is from a companies BBQ selection and they want to create a dashboard around this information.
- After that click on “Insert” followed by “Table”.
Click on “OK”.
Our data is now in a table.
- Change table name to “Data”.
- Now click on “Summerise with pivot table”.
- Configure the information on the final conversion using the screenshot below as a reference.
- Rename the pivot table.
Edit the Pivot Table Fields
For this first section of the pivot table chart, I want to display the products sold by name and a bar graph to show the price of each product in correspondence to the name of the product, this will outline the products well. So in the field type, I will select Name and price.
- Drag dataset information into the appropriate categories
In this example, I want a row of products and the value set prices for the products.
This is my pivot table made, now for the bar chart.
- Firstly click on “Pivot Table analyze”.
- Now click on “Pivot Chart”.
A chart will automatically be created. You can switch between different chart types post creation.
Anyway, we have our first pivot table made, we will make a total of depicting the price of products, one with the weight of the products and one with the inventory of the products in a pie chart.
Making another pivot chart
- Right-click on the “Line pivot” sheet.
- Click on “Move or Copy”.
- Select “Create a copy”, and press “OK”.
- Rename to whatever you want.
I’ve decided to rename it to inventory for the inventory pie chart.
That’s it, your new sheet is ready for a pivot chart. Select one cell from the corresponding Pivot Table, navigate to the Insert Tab, then click on the Pivot Chart Button to generate the Pivot Charts. The default chart type is a basic vertical bar chart, but you can simply modify it by going to the Chart Design Tab > Change Chart Type.
The two most sophisticated of the five chart types, the Map Chart and the Waterfall Chart, are the sole exceptions. Both of these now need copying the Pivot Table Data outside of the Pivot Table and then inserting a conventional chart that refers to the copied data. Copy the row and column headings and then use the GETPIVOTDATA method to reference the exact data to create a dynamic copy of a Pivot Table outside of the Pivot Table. All filtering in the Pivot Table will be applied to that cell automatically when you use this function.
Furthermore, we propose removing any extraneous chart features during this chart preparation step, such as the title, which is redundant because we will subsequently insert well-designed titles for the tiles and charts in the dashboard.
–
Making the dashboard
A decent dashboard design may quickly direct the audience’s attention to the most crucial information. Starting with visually separating the background noise from the main dashboard area, this is a good place to start. It should be sized to encompass the entire viewable area. Then, as a contrast, we employ a modern tile pattern that is distinguished by its hues, which are either deeper or brighter. That’s the beauty of the dashboard you are not limited to your imagination create it whatever way you need to create it. Follow the steps below to start making the dashboard.
Step by step process – Making the dashboard
- Firstly, create a new Sheet and call it “Dashboard” (the name is logical as it is our main dashboard).
- After that, select the first two or three rows depending on how tall you want your title bar to be. Now Click on “Merge and Center”.
- After that assign a fill of your choice.
This again is up to you and what you want your theme to represent. I want a dark and woody theme, for the title bar, I will adopt that particular color pallet.
- Now move all your charts across, roughly placing them to your preference, take your time with this step.
I’ve added a title and added the charts into the dashboard, We will make this look a lot better later on in the process. Follow through to add slicers into the dashboard.
Step by step process – Adding slicers
Slicers are the magical component that will allow us to make our charts dynamic and filterable in several dimensions. We create a slicer for each dimension we wish to filter and link all of the slicers to each graphic. It’s a relatively easy process, just follow the step-by-step guide to see how you can add slicers into your dashboard.
- Firstly, select your chart.
In this case, I have chosen the prices chart
- Now navigate over to “Insert”.
- Now click on “Slicer”.
- Repeat for the other charts
And here are the slicers, they will give a more accurate representation of the information you plotted, you don’t need to have slicers if you have no requirements for them. You can now adjust in any way you want.
–
Making the backdrop
Now I will show you how to add a backdrop to your sheet and refine it to your preference. We will be using PowerPoint to make the backdrop, if you have access you can use adobe illustrator to make an even better dashboard background.
This to be clear is not a mandatory step, your dashboard is complete if you want something simple, however, the extra steps are to really elevate your dashboard to the next level and really make it look stunning.
- Open PowerPoint.
- Now click on “Insert”.
- After that click on “Shapes”.
- Select a Square.
- Begin constructing the spacings for your dashboard.
Each part of the graph will go in these corresponding boxes, the bar graph for products will go altered to a vertical layout, which will allow for better organization and visual aesthetics within the dashboard.
This is what the finished backdrop looks like with all the colors added to finish the layout.
–
Formatting the sheet to finish the interactive dashboard
- Go back to your sheet and click on “Page Layout”.
- Now click on “Background”.
- Adjust the preferences.
This is what it looks like after all the preferences are set. Make sure your charts fit into the background.
After fiddling with the settings I finally got the look I want. I removed the backdrop from the charts and make them colored white which contrasts well with the backdrop. Make sure again you have the correct settings to fit your preferences.
–
Tips and tricks to better your experience with the Excel dashboard
Make use of dynamic charts
Dashboards that can’t be interacted with aren’t very useful. All of your excel dashboards should include controls that allow you to adjust the markets, product information, and other little aspects. Most importantly, the user must have total control over his or her personal excel dashboard and be able to make adjustments whenever and wherever they want.
Dynamic ranges are required for generating interactive charts. The OFFSET() method may be used to do this. You may also add some fun features to your excel dashboard, such as welcoming the user and selecting the appropriate profile when they open it. All of this is possible with macros. All of this is possible with macros. Simply record a macro and add a FOR NEXT or FOR EACH loop to it. If you’ve never recorded a macro before, there are a number of websites that can provide you with custom macros that are suited to your specific needs.
Making a macro isn’t as difficult as it appears. You must click anywhere on the Ribbon to do so. Select customize Ribbon from the drop-down menu. Select primary tabs from the drop-down menu that appears. Select the Developer option. Click the OK button. The Developer Tab is now located next to your final tab on the Ribbon. Now go to the Developer tab and choose Insert. Click the Command Button in the ActiveX Control. This button should be dragged to the active cell.
Create your dashboard report in Excel
If you’re still using Excel 2003 or 2007, the default charts aren’t particularly appealing, so avoid them like the plague and utilize suitable formats instead. Excel 2010 and 2013 are significantly improved, however, they still require improvement. Keep in mind that a chart is meant to find actionable patterns in data, and you should try to make the most of it. This also implies that you should delete any flashy, glittering elements from your excel dashboard that bring no value. Instead, you may make a hierarchy of pertinent focal and contextual data and engage in some rudimentary interactivity, if not much.
Allow your audience to independently examine the data by providing them with their own filters and controls. This is when interactive graphics come into play. You can still identify patterns and learn how to construct a spectacular dashboard if you’re new to interactive excel dashboards. You can dive down deeper into the data for better charts if you are an expert at it.
Conclusion
That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.