Session 9 - Validating cell data and advanced charts

Data Validation

Data Validation is simple in Excel 2013. You can access the data validation button from the Data ribbon.

For this example, I will be using the Pivot Table spreadsheet.

To start with, I am going to validate the Branch.

Click on the Data Validation button to load the dialog box.

We want to restrict the data that can be entered to ensure that a valid branch is entered. The easiest option is to choose List and allow the user to select the Branch from a drop down list. You can either type in the items for the list separated by a comma, or you can have the list items on the spreadsheet and put the range details in.

You can validate other data types such as numbers and dates.

You can also set up validation and check to see if there are any cells that do not meet the criteria. One way of doing this would be to copy and then using Paste Special, paste only the validation.

Validation with Form Controls

You can also use form controls to validate data. This is useful if you want to use one of the funky control slike spin buttons or check boxes.

Here is an example using a spin button

Combo Charts

Combo charts allow you to have different values in your chart represented using different chart types. Here are some examples.

ASSESSMENT TASK

Now for your Excel assessment task. I did tell you that it was easier than the Access one - It is. See assessment sheet on Moodle for details.