Learn to build a spreadsheet dashboard by starting with your outcome and working backward. Organize raw data separately, use VLOOKUP to pull values into your dashboard, add a column chart for comparisons, and use conditional formatting to highlight what matters.
Chapters
Now that you have seen a completed dashboard and used features to modify the display, let's discuss setting one up. The simple dashboard has user friendly functions and controls to show only the data you want to report and uses the right visualization to showcase this data. You will explore this further as you set up and add features to your own dashboard, which will allow you to pull data from your main data set using vlookups and formulas of reference. Drilling data down further, using data validation, plotting it, highlighting cells that meet certain criteria, and using conditional formatting will make this easy. When creating a dashboard, you need to ensure you include the right tools to grab your audience's attention. So start with the outcome in mind and work backwards. Before creating a chart, think about what data you are going to use. What is it and where is it located? Is it in the same workbook, pulled from the net, copied, linked or imported from another source? Depending on where it comes from, the format may need tweaking a little bit or a lot. It's best practice to get your data in order first, as this is the basis for all your visualizations. We will talk more about how you can optimise your data a little later. A smart dashboard displays only the information you want your user to see. You should not display your entire data set. It takes up too much room and draws the reader's eye away from the main message of the visualization because the sheet is too busy. The best way to create a dashboard is to keep your data sets on separate sheets and selectively pull the data you wish to display to create a chart. A formula of reference is the perfect tool for this. It's a simple formula that shows the value of a cell in another cell on the same sheet or another cell in a different sheet. This also ensures that each time you feed off the data set, you are using the same data and if the data set is edited or updated, the changes will be reflected. A formula of reference is a basic calculation that picks up a value from another cell. It's probably one of the most common formulas you will ever use. The formula of reference here is saying point to sheet 2 cell A1 and return whatever is in cell A1. To start a formula of reference, type in an equals sign. Then navigate to your cell on either the current sheet or another sheet and click Enter. Doing so will ensure that if the data in Sheet 2 Cell A1 changes any cell in any sheet that references, this will also change. Once you have extracted the data you wish to display, you can create a column chart to plot the results column charts are used to visually represent data in vertical rectangular bars where the bar length is in proportion to the data. They are great for comparing values over time or comparing data from different categories. The vertical axis is displayed on the left hand side of the chart, making it easy for the user to compare the vertical bars visually. You can apply many different types of formatting to your chart, which we will cover in more detail in the next chapter. Time to practice what you've learned. Have a go at the exercise and.
