Among all the powerful features and functions Excel has to offer, the ability to create a project dashboard is one of the most useful ones for most corporate employees or small businesses. A dashboard gives you a complete overview of your data with dynamic visualization of numbers and stats. So, whether you want to track your sales in real time or check the concurrent visitors on your website, an Excel dashboard is a great way to do it.
✕ Remove Ads
However, like most things in Excel, creating a dashboard doesn't typically provide instant gratification. It involves cleaning up your datasets, creating pivot tables, generating charts, and then collating all of them in a single sheet. The end result can be extremely rewarding though, so I'm here to help you jump right in!
Related
15 best Excel plugins that you need to be using
Must-have Excel add-ins you can't afford to ignore
Posts4
Fetching live data
Since we're dealing with live data to create the dashboard, the first step is to link Excel with the data source so that all the required data can be fetched in real-time. Excel has quite a few options for importing live data. You can import it from a website, a SQL server database, Azure, or other enterprise data solutions.
✕ Remove Ads
Head to the Data tab in Excel and select Get Data. Then, choose whatever option your organization uses to gather data.
Enter the relevant URL or credentials and the data will be imported to Excel. Once that's done, it's important to ensure the data is in the form of an Excel table. We won't be able to create pivot tables without this, which are essential for creating a dashboard.
✕ Remove Ads
To do this, select the imported data and navigate to the Insert section in Excel. Then, click on Table. You'll now have a table structure along with the imported data that is ready to be worked on. Creating a table ensures any blank data is omitted and each parameter is defined.
Before creating the table, make sure the imported data has headers for each column. Otherwise, the data won't be aligned properly. Also, select My table has headers when inserting the table.
✕ Remove Ads
Creating pivot tables
Now that we have a table with the relevant data and headers, it's time to create pivot tables. Essentially, these pivot tables will help you recognize certain patterns in the data. For example, if you're working with sales figures, these tables will help you realize on which day you made most of the sales, which city you shipped to the most, or which is the most popular SKU in your inventory. Here's how you can create and insert pivot tables.
Create a new sheet inside the same workbook and rename it Pivot Tables . Then, follow the subsequent steps in this sheet. Name the initial sheet with the information table as Data . This way, it's easy to differentiate between the different portions of your dashboard without mixing them up.
✕ Remove Ads
Head to Insert and click on PivotTable.
Click on Select a table or range. Then, choose the small table icon at the end of the text field.
Now, head to the Data sheet and select the entire table. Click on the same table icon again to expand the menu. Select OK.
✕ Remove Ads
The pivot table will be generated. Now, it's time to select what parameters we want in our pivot table. Using the pane on the right, select the different labels you want to include in the pivot table.
✕ Remove Ads
Since you may want to compare multiple types of data with different parameters, I recommend creating a few copies of the pivot table within the same sheet. This way, you can use one table to compare sales based on dates, one based on location, and one based on category.
Once you create multiple pivot tables, simply click on them and use the right pane to edit the categories.
✕ Remove Ads
Generating charts
With the pivot tables ready, it's now time to create charts that can visually represent all the data on our dashboard. This way, you don't have to keep track of all the numbers in multiple different tables at all times. Simply glancing at the charts on your screen should give you a rough idea of all the figures and trends. Here's how to generate the relevant charts.
Click on the first pivot table. Go to the Insert tab and select the type of c hart you wish to insert. Let's select the Pie chart as an example.
Then, select the next pivot table and choose the type of chart you wish to create. Repeat the same process for all the pivot tables.
Double-click on the charts to modify the way they look. You can change colors, add or remove numbers, etc.
✕ Remove Ads
Once you've created all the charts and graphs, copy them into a different sheet within the same workbook, which you can name Charts .
Adding slicers
Our dashboard is shaping up to be pretty comprehensive, but it requires one last touch. Microsoft likes to call it a slicer, but I prefer referring to it as a filter or mode selector. It's essentially a quick shortcut to select what parameters and data you want to see on your dashboard. It offers an efficient way to switch between the different data types, allowing you to filter your data. Here's how to add slicers to the charts we just created.
✕ Remove Ads
Switch to the Pivot Tables sheet and select a pivot table. Then, navigate to the PivotTable Analyze option section and select Insert Slicer.
Select the relevant category based on which to filter the results and click on OK.
Repeat the same process if you want to add multiple slicers based on other categories. Right-click on each slicer and choose Report Connections.
Here, enable the checkbox next to each pivot table and click on OK.
✕ Remove Ads
Now, when you click on the different filters inside the slicer, you will see the data in the charts varying. So, you can now use these slicers for precise information like the sales via credit card in Los Angeles during the month of August.
Once you've added all the required slicers, copy and paste them into a new sheet entitled Slicers .
✕ Remove Ads
Collating all elements
At this point, we have all the required elements necessary to create a dashboard segregated into dedicated sheets. So, all we have to do now is create a final sheet titled Dashboard and move all individual elements into it. First, head to the Charts section and move all of them to the Dashboard sheet. Then, go to the Slicers sheet and copy all the elements into the Dashboard sheet as well.
✕ Remove Ads
Ta-da! We now have a full-fledged dashboard with live data in Excel! And when it's connected with a live data source, as and when the information fluctuates, the changes will automatically be reflected in the dashboard. You can use the slicers to filter the data and create more graphs, charts, or slicers as time goes by and you have more specific queries.
Related
10 cool projects you can do with just Microsoft Excel
Excel-erate your creativity beyond boring spreadsheets
Posts5
Become a data master
Once you start using pivot tables and creating visual dashboards on Excel, there's no going back. It's so helpful to visualize all your data in one place, especially if you're working with dynamic data like sales, leads, claims, etc. It's also quite simple and quick to set up once you get the hang of it.
✕ Remove Ads