An Excel Dashboard Visualize your data and make your spreadsheets easy to use with an Excel Dashboard Visualize your data and make your spreadsheets easy to use with an Excel Dashboard Sometimes a simple sheet format calculation is not attractive enough to make its data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More Whether for your own reference or to pass on information to others, it's a great way to separate the wheat from the chaff.
In this guide, I'm going to produce a dashboard that will serve as a companion to my annual review of The Simpsons special Halloween episodes. . However, you can apply the same methods to anything from a fitness planner to a sales report.
These three techniques will transform a standard Excel dashboard into a useful and aesthetically pleasing resource that will save you time and effort in the long run. The Basic Settings 5 Excel Document Settings You Should Never Forget Check 5 Excel Document Settings You Should Never Forget When you select an Excel template or share a spreadsheet, it may contain custom settings that you don't want anyone to see. Always check these settings. Read More The standard is to use one or more sheets to do the work behind the scenes, so the dashboard is clean and tidy.
The Camera tool is very useful for our purpose. It makes it easy to display a section from another part of your spreadsheet exactly where you want it. We are going to use it to place a chart on our dashboard.
First, we need to set up our table.
As you can see, I've kept things relatively simple for now. Simply track the IMDB user rating of each episode. Next, create another sheet called Board if you haven't already.
Next, select the area of your spreadsheet that contains your chart and click the Camera Button:You may need to add it to the Quick Access Toolbar. Go to your Dashboard Click on the sheet and click where you want to place the chart.
You should end up with something like the above. There is no need to make fine adjustments at this point, just insert it into the Dashboard sheet. Once we have all of our components ready, we can tackle our design.
The next thing I want to add to my dashboard is a drop-down menu How to Create Interactive Excel Charts with the INDEX Formula How to Create Interactive Excel Charts with the INDEX Formula We'll show you how to use the INDEX formula and a basic menu. Down menu to create interactive Excel charts. This simple process will turn static graphics into dynamic information hubs. Read More A dashboard is designed to offer information at a glance, so it's a good way to make sure you're not overloaded.
To set this up, we need to go back to the sheet where we saved our data. Copy and paste your table headers further down the page.
You'll also see that I've added a 1 as a placeholder, highlighted in yellow. This will play a crucial role in the INDEX function. Faster Excel spreadsheet lookup:replace VLOOKUP with INDEX and MATCH. Find Excel spreadsheets faster:replace VLOOKUP with INDEX and MATCH Here is how INDEX and MATCH can provide a better solution. Read More
Copy this code into the left cell below the headers of the table you just pasted.
= ÍNDICE (A2: G11, $ A $ 29, 0)You will need to make changes to reflect your spreadsheet. The first group of cells should cover all of your data, minus the headers. The second is the placeholder number we just added, and the third can be left as zero. In this case we are not specifying a column.
Drag that formula to fill the entire row.
Above, you'll see that I changed the placeholder number to 2 and the cells refilled with information from the second row of cells. This is the base of our dropdown menu.
Time to use our old friend the Camera tool. Select a cell with the information you want to include in the dashboard, click the icon, then go to Dashboard Sheet and place it where it needs to be.
Again, there's not much of a need to make things look aesthetically pleasing just yet. We are concentrating on the skeleton of our control panel at the moment. Go back to the sheet containing the data and change the number manually, to see if the values in the dashboard change.
My spreadsheet works fine, so I'll move on to adding the dropdown.
Go to the Developer tab and choose the Combo box .
Place it where you want, then right click and select Format Control .
For the Input Range , enter the range of cells you want to draw from, and for Cell Link , Enter the cell to which we add a number. In both cases, remember to add a reference to the sheet they are on.
Your dropdown should now work as intended. Try it.
Finally, we are going to add a simple way to schedule tasks. Completing my Horror Tree House Looking Back in Time for Halloween Means Sticking to a Strict Schedule 5 Ways to Automatically Schedule Your Day 5 Ways to Automatically Schedule Your Day Are all the little things getting in the way of your productivity? Instead of freaking out over things you can't control, plan ahead and automatically schedule some of the things you can control. Read more . I want my dashboard to automatically tell me which episode I need to watch on a given day.
To implement this, we'll go to the sheet that contains our data and add a new column to the far left of the document.
I have added the date I plan to watch each episode. In another context, you can add the due date for a particular task. 9 Best Time Management Tools to Stay Organized. 9 Best Time Management Tools to Stay Organized. The time management tools shown here will help you make the most of your days. Suitable for any business or personal situation. This is the time to organize your life! Read more . At the bottom of the column, I've used =TODAY() to produce a cell that always displays the current date.
Next, I'm going to insert the following formula into the cell directly to the right:
= VLOOKUP (A12, A2: B11, 2, FALSO)You can see what it looks like below.
The first cell points to today's date, then the cell range specifies where VLOOKUP should find Excel spreadsheets faster:replace VLOOKUP with INDEX and MATCH Find Excel spreadsheets faster:replace VLOOKUP with INDEX and MATCH Still using VLOOKUP to look up information in your spreadsheet? Here is how INDEX and MATCH can provide a better solution. Read More try to find a match Find Excel spreadsheets faster:replace VLOOKUP with INDEX and MATCH Find Excel spreadsheets faster:replace VLOOKUP with INDEX and MATCH Still using VLOOKUP to look up information in your spreadsheet? Here is how INDEX and MATCH can provide a better solution. Read more.
The number 2 dictates that the result should be pulled from the second column across, and FALSE confirms that we're only looking for an exact match.
This works great, so let's fill in the rest of the row with the formula, then use the Camera tool one more time to add this information to the dashboard.
We now have a considerable amount of information to display on our dashboard. It's time to start tweaking the layout.
This stage of dashboard creation is all about making your own aesthetic choices. How to Change the Look of Your Windows 10 Desktop How to Change the Look of Your Windows 10 Desktop You've just upgraded to Windows 10 and are wondering how you can personalize and personalize it. We help you turn your desktop into a virtual Windows experience, step by step. Read more . However, it's important to remember that our finished product is intended to provide information at a glance, so large fonts and contrasting colors are always a good idea.
My first choice was the Page Layout tab and uncheck the box marked Printing under Grid lines . All of our information comes from another sheet, so the grid isn't particularly helpful. I also used the Format Cells Menu to change the background color appropriately. Simpsons -as a shade of yellow.
This still doesn't look particularly pleasing aesthetically, but we're still very early in the process. Next, head over to the datasheet so we can adjust the cells that are being served in our dashboard. We're going to change the colors and fonts there, before heading back to the control panel sheet to tackle the layout.
My first step is to change all the text to a condensed version of Futura, a font commonly used by the program.
Next, I came up with a Halloween-themed black and orange color scheme that would contrast nicely with the yellow background.
At this point I decided to go back to the datasheet to modify some cells. The ones containing a number or a year seemed a bit too broad for the data in question.
This is our finished dashboard. The top right section will update based on the current date, and the bottom section will change based on the dropdown selection. It's clear, it's concise, and it offers a good amount of information without being overbearing.
The dashboard I have put together above is specifically designed for a particular purpose. However, the same techniques have a wide variety of different applications.
For example, understanding how to use the TODAY function in conjunction with other formulas can be used for various task scheduling purposes. Similarly, the Camera tool is as flexible as it gets.
The key is to establish what your dashboard should do and determine how Excel can make those 10 Easy Excel Screensavers You Might Have Forgotten Easy 10 Easy Excel Screensavers You Might Have Forgotten These ten tips are sure to minimize your time when performing low-cost Excel tasks. productivity category of the spreadsheet. Read more . It is very powerful software. The key is knowing which functionality does what.
Do you have any tips for producing Excel dashboards? Do you need a hand with a particular problem? Join the conversation in the comments section below.