1. Data validation
Data validation is a great feature to use with your dashboard since it controls the data that is entered into a spreadsheet. It prevents users from entering data that is not within set criteria, by limiting them to a drop-down list of options or specifying a strict range for entries.
2. Select dates from a list
To see how this works, you are going to create a dashboard that uses a dataset from the Australian Stock Exchange, or ASX, to display various outcomes.
First, you will set up your data to allow a user to pick different dates, show the opening and closing stock for these dates, and plot them in a line chart. Once set up, a user only needs to click the downward pointing arrow to the right of the cell to pick a date from the dataset.
3. Remove blank rows or columns
The first step is to prepare your data by making sure it is formatted correctly. In addition to formatting your column headings, you should remove any blank rows or columns. Click on either the row number or column letter, select Edit, then delete the row number or column letter.
4. Format dates
Format your dates by highlighting the relevant range and selecting the Format tab, where you will see the different types of date formats available. In this slide showing the Qantas ASX dataset, the date is formatted to month month day day, year year, but there are other formatting options available.
5. Format decimals
Make sure your decimal places are consistent to ensure professionalism, but also because spreadsheets round numbers with decimals. You can change decimal places by highlighting the cells you wish to change and selecting the increase or decrease tool on your toolbar. Note these are hidden behind the menus in the format dates screenshot.
Don't forget to use a heading to give your data meaning. Consider which data you will need and set up the named ranges accordingly. Once you have user-friendly names for your data, you can commence data validation using the meaningfully named ranges, rather than the cell references.
6. Creating the data validation
Now that your data is ready to use, you will create your validation on the actual dashboard sheet.
You want the user to select the dates from your dataset. In the cell directly under the Dates heading select Data, then Data validation.
In the box to the right of List from a range, type in your named range. Note that you have the option to select Show warning, which will give the user a warning that they have an incorrect entry in the cell, or Reject input which rejects anything other than a selection from the list. Check the Show warning and click Save to complete the validation.
In your cell on your dashboard sheet, you will now see a downward pointing arrow. When this is clicked, a list of the Dates in the Dates range will display and the user can select accordingly.
7. Testing the list: reject input
Make sure you test your list. You can set up your Validation to either show a warning, or reject input to stop invalid entries. Select a few different dates from the list, then try to enter an invalid entry and see the message that appears.
8. Testing the list: validation help
If you want to give your user a bit of assistance, you can check the Show validation help text option and enter in a helpful message, like "Select a date from the list to see the Opening and Closing prices".
9. Putting it into practice!
Let's practice setting up cleaned data with named ranges before analyzing it and creating data validation on your dashboard.