Tips to Create a Dashboard in Excel
The article shares some tips to help you create Dashboard in Excel.
Creating a dashboard in Excel is not a very tricky task. Still, it requires some intelligent play to present the information excitingly. Your data should tell the audience a story, and to achieve this, we share some tips that can help you plan your dashboards efficiently.
What is an Excel Dashboard?
Excel dashboards offer the easiest way to skim through the information rather than reading detailed reports. These overviews are particularly helpful in presenting quick information in a short time. Dashboards offer a concise report on achieving goals and progress on targets and are a popular tool among senior management of organizations globally.
Content
Best-suited MS Excel courses for you
Learn MS Excel with these high-rated online courses
Plan Your Presentation
Before you create a dashboard, keep a few things in mind so that you don’t have to make any massive modifications later –
The metrics that will be included –
- Which of those metrics will be KPIs versus which will be just supporting information or context
- Which views of each metric will be included: a trend, a total, a comparison to a target or a previous period, etc.
A simple outline of how the dashboard would appear will help organize the information based on what you plan to include. It will be faster to draw rectangles on a sheet of paper than to rearrange blocks of information in Excel. It is an excellent way to plan and avoid errors.
Related: How to Create MIS Report in Excel
Restrict the Information to One Page
The basic principle behind creating a dashboard is straightforward – keeping all the information on a single page and not dividing it into multiple pages.
Since the point of a dashboard is to provide a simple performance view, it’s essential to keep everything in perspective without turning the page or even scrolling down. We should keep everything so that it can be reached at a glance. You can have reference sheets, but the main dashboard should only be one.
Customize Your Widgets
Depending on how you planned your presentation, you must use the right widgets. Focus on a few metrics like –
- M-o-M or Y-o-Y comparisons of data
- Any specific trend observed
- Font size to showcase the highlights of your data
- Color palette theme as per your brand presence
- Overall design
Keep The Dashboard Print Ready
You would never know when your senior managers ask you for the hard copy of the data you are presenting, for references, of course!
It is always good to keep in mind the possibility of having to print spreadsheets to review them on paper. We recommend – defining the print area and adjusting the layout, orientation, margins, and header/footer so that the dashboard can be easily printed. Avoid using the Fit to Page feature no matter how hurry you are since it can scale and distort your presentation.
Related – How to Choose the Right Chart in Excel
Group the Metrics
Always keep your dashboard clean and concise, and grouping information is the key to achieving this. Metrics like traffic data, sales numbers, CTRs, bounce rates, social media performance, etc., need to be categorized neatly. Furthermore, you can find several ways to group your information by categories, departments, products, etc.
In the above image, you can clearly distinguish which arrangement looks clean. Grouping makes it easier for your audience to locate data points. Also, label the elements of your dashboard. They should be self-explanatory.
Must Explore – MS Excel Courses
Maintain the Consistency
Sometimes, you may have to repeat the information for a similar set of metrics, but keep in mind that you must be consistent while presenting data points. Using similar visualizations and layouts within groups will make it easier for your audience to relate to them. They will know what the image is trying to convey without referring to the previous image.
Experts advise using line charts when presenting percentage data.
Keep Your Columns Narrow
Even though Excel is far better and more accessible than any web analytics tool for design flexibility, it’s still a grid at the end of the day. This can be a limitation if you configure the ideal column size for the design. While there is no definitive answer, one thing we can try is to make all the columns for the layout sheet the same size as the rows.
Read MS Excel Tutorials
Always Mention the Context
Data without marking is unrelatable. Imagine you are just given a single digit to understand the sales report, will you? No, you wouldn’t because you would need the context.
Always mention metrics that define your dataset. If you say sales data on your dashboard, present it concisely with total numbers achieved, the growth achieved, etc.
Use Dropdowns
Every non-automated dashboard should have at least one dropdown on the dashboard itself, and that is a selector for choosing the date to display. That single cell should be named, and the key by which all the data displayed is updated.
Depending on our dashboard, it is better to use dropdowns in the cell to have additional control of what is displayed on the dashboard. For example:
- If the dashboard displays data on a weekly, monthly, or quarterly basis
- How many periods to include in the historical trends on the dashboard
- Which metric is used to sort a list of values on the dashboard
- Whether the metric will be sorted ascending or descending
Automate As Much As Possible
Although it sounds obvious, sometimes we leave this step behind, resulting in dashboards that aren’t as automated as they could be. Several of these tips that we have listed already help with automation or at least get closer to it. But the better we know Excel and the better we develop a dashboard structure in general, the more automated it will be.
Prepare your next Dashboard in Excel keeping these handy tips in mind!
Top Trending Articles in MS Excel:
Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel
Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio
Top Picks & New Arrivals
