How to Create a Dashboard in Excel?
Let’s Learn the Smart sheet Dashboard Solution
Dashboards give you unprecedented visibility into your work. You can get a quick, visual status update on your projects or drill down into important details, all in one place. Dashboards can boost your organization’s speed by allowing you and your team to see more, manage more, and communicate more.
This article will walk you through how to make a dashboard in Excel from scratch or how to use a free dashboard Excel template. We’ll also share the best practices for researching and building your dashboard, dashboard dos and don’ts, and common questions about dashboards in Excel. And, you’ll learn about a new kind of dashboard from Smart sheet, called Sights, and how it can help you do your best work.
What Is a Dashboard?
Dashboards track KPIs, metrics, and other data points in one visual, central place. They give you a high-level view of work, helping you make quick decisions and keeping everyone up to date. A dashboard’s visual nature simplifies complex data and provides an at-a-glance view of current status or performance in real time.
Dashboards are made up of tables, charts, gauges, and numbers. They can be used in any industry, for almost any purpose. For example, you could make a project dashboard, financial dashboard, marketing dashboard, and more.
Before Building the Dashboard: Your should Research, Questions, & Things to Consider :
Before you start building your dashboard, first take some time to reflect on why you need a dashboard, what purpose it will serve, where the data will come from, and what you capabilities you do and don’t need.
It might also be helpful to mock up your Excel dashboard on a piece of paper. Draw boxes for each data type to get a sense of the layout and add quick sketches of the type of graphs you want to include. This mock up will help get everyone on the same page and let you get approval from stakeholders before you start spending time and money on the actual dashboard.
Questions to Ask Yourself
Why are you creating this dashboard?
Are you trying to prove or disprove a hypothesis? Is this dashboard for a specific task, like showing status of a project, or does it need to achieve a broader goal, like measuring business performance? Understanding why you are building the dashboard will help guide the design and data.
Do you need to track certain KPIs?
Your dashboard should only highlight data that adds value. Make sure you understand the most important KPIs and create the dashboard around those metrics. Anything outside those main KPIs aren’t necessary
Who needs to see the dashboard?
Is this for a colleague, manager, stakeholder, external vendor, or C-level executive? How do these people prefer to digest information? How much time do they have to look at this dashboard? Think about who you’re making the dashboard for and keep their preferences in mind.
Where will the data come from?
Do you need to manually import data into your dashboard or will you use an integration or connector to automatically sync and refresh data? What other tools do you use to gather data?
How up to date does the dashboard need to be?
Can you update your dashboard weekly or monthly, or does it always need to show real-time, updated information? Depending on what you chose, this will change the way you build your dashboard.
What format does the dashboard need to be in?
re you emailing a static dashboard or providing a link to a dynamic one? Does the dashboard need to be embedded in presentations or decks? Do you want to share read-only access or do you want to provide editing capabilities to certain people?
Consider the following things to Design the DASHBOARD :
What do you want to include on your dashboard? You can choose from static tables, pivot tables, dynamic charts, Excel gauge widgets, or non-charting objects, like auto-shape objects. Do you want to add a lot of small charts or a couple big charts? Identifying the elements you want to add to your dashboard will help you group similar data together and give you an idea of the layout.
Dashboard background color:
How much color do you want to incorporate in your dashboard? Do you want to add a dashboard background color to make the dashboard elements pop? Do you want to color-code similar charts?
Enhancing the dashboard UI:
How important is ease of use? Do you want to spend time enhancing the dashboard UI? You could add hierarchy to the layout for easy navigation, add drop-down lists, add labels to each graph with auto-shape objects, or use freeze panes to prevent users from scrolling.
Using PowerPoint and Excel to Obtain Dashboard :
You could also make an interactive dashboard with PowerPoint. Adding interactivity to a pure Excel dashboard can be challenging, and usually requires Macros (or VBAs), the programming language used within Excel. However, if you add the charts and dashboard components you create in Excel to PowerPoint, you can easily add an interactive element.
For example, you could create five pie charts showing the exact same data over the course of five years. If you add one pie chart to each PowerPoint slide, you can move through these slides and the chart will look like it’s in motion.
Using PowerPoint and Excel together also makes it easy to share your dashboard. You can simply save the dashboard as PowerPoint Show and email it to colleagues.
visit my channel to Learn Dashboard : https://www.youtube.com/learnmoreeasily
How to Make a Dashboard in Excel
Here’s a step-by-step Excel dashboard tutorial:
1. How to Bring Data into Excel
Before creating dashboards in Excel, you need to import the data into Excel. You can copy and paste the data, or if you use Comm Care, you can create an Excel Connection to your export. But, the best way is to use ODBC (or Live Data Connector). ODBC can connect your apps to Excel, passing real-time data from your app to Excel. As data is updated in your app, your Excel dashboard will also be updated to reflect the latest information. This is a perfect option if you track and store data in another place, and prefer creating a dashboard in Excel. Data can be imported two different ways: in a flat file or a pivot table.
2. Set Up Your Excel Dashboard File
Once you have added your data, you need to structure your workbook. Open a new Excel Workbook and create two to three sheets (two to three tabs). You could have one sheet for your dashboard and one sheet for the raw data (so you can hide the raw data). This will keep your Excel workbook organized. In this example, we’ll have two tabs.
3. Create a Table with Raw Data
- In the Raw Data sheet, import or copy and paste your data. Make sure the information is in a tabular format. This means that each item or data point lives in one cell.
- In this example, we’re adding columns for Project Name, Timeline, Number of Team Members, Budget, Risks, Open Tasks, and Pending Actions.
- If needed, you can use a formula to automatically add all the values in a column. We will do this for our Budget, Risks, Open, and Pending Actions columns. Click on an empty cell at the bottom of the column, and type =SUM(. After the open parenthesis, click the first cell in the column and drag your mouse down to the last cell. Then, add a close parenthesis to your formula. Repeat as necessary.
4. Analyze the Data
Before building the dashboard, take some time to look at your data and figure out what you want to highlight. Do you need to display all the information? What kind of story are you trying to communicate? Do you need to add or remove any data?
Once you have an idea of your dashboard’s purpose, think about the different tools you can use. Options include:
- Excel formulas like SUMIF, OFFSET, COUNT, VLOOKUP, GETPIVOTDATA and others
- Pivot tables
- Excel tables
- Data validation
- Named ranges
- Conditional formatting
- Excel dashboard widgets
Don’t worry, you don’t need to know how to use every single one of these Excel tools. With some basic knowledge of charts and pivot tables, you can make a beautiful Excel dashboard.
5. Build the Dashboard
Add a Gantt Chart
We’ll add a Gantt chart to visually show your project timeline.
- Go to your Dashboard sheet and click Insert.
- In the Charts section, click the bar chart icon and select the second option.
- You’ll now have to link this bar chart to the Project Name, Start Date, and Duration columns in your Raw Data sheet.
- For complete step-by-step instructions on how to create a Gantt chart in Excel, click here.
Create and Format Charts
- In your Dashboard sheet, click Insert and select the kind of chart you’d like to make. For this first example, we’ll create a column chart.
- Right-click on the chart and click Select Data.
- Click Add in Legend Entries (Series).
- In the Series name field, click the title of the column you want to add on the Raw Data sheet. Hit enter.
- In the Series values field, select all the data in that corresponding column. Hit enter and then click Ok.
- You’ll notice that your X-axis is not correctly labeled. To fix this, click Edit in the Horizontal (Category) Axis Labels and in the Raw Data Sheet, select what you’d like to display on the X-axis.
- To add a title to your chart, select your chart and click the Design tab.
- Click Add Chart Element > Chart Title > Above Chart.
A pivot table allows you to extract and highlight the most important information from a large data set.
Here’s how to insert a pivot table:
- Go to your Dashboard sheet and on the Insert tab, click the Pivot-table button.
- A pop-up box will appear. In the Table/Range field, click the icon at the end and and select your whole data table from your Raw Data sheet. Click OK.
- The PivotTable Field List will appear on the right side of your screen. Select which subsets of data you would like to include in your pivot table by clicking the boxes.
- If you’d like to include another pivot table in your dashboard, repeat steps 1-3.