Setting up a basic dashboard
1. Setting up a basic dashboard
Now that you have seen a completed dashboard and used features to modify the display, let's discuss setting one up!2. Dashboard features
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 dataset. 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.3. Getting your data right for the dashboard
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 optimize your data a little later.4. Extracting your data
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 datasets 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 dataset you are using the same data and, if the dataset is edited or updated, the changes will be reflected.5. Formulas of reference
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.6. The column chart
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.7. Let's practice!
Time to practice what you have learned. Have a go at the exercise and see how you go!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.