Session 8 - Excel Forms and Validation

Creating a Simple Form

Today we will look at how to create a simple Excel data form for entering information into a spreadsheet. A data form can be created using the existing column headings in a table or cell range.

For this example, I am using the same spreadsheet as last week - 2015PivotTable.xlsx

Before we can create a form, we need to add the Forms Button to the Quick Access Toolbar. Click on the little arrow at the end of the quick access toolbar to display the customise menu.

Click on More Commands, then All Commands. Locate the Forms Button and add it to the toolbar.

Highlight the table (A3:G29) and click on your newly created Form button.

Are you impressed? Excel should have created a nice data entry form for your table. You can even use this to scroll through the existing data in your table.

If you have a blank "template" or partially completed spreadsheet, you can still create a form. In our example, lets say you have the names and branches, but no data. You can create the form so that the missing data can be entered easily.

You will see that now the form has blank data - the user can enter the Q1 - Q4 figures and it will calculate the total as you go. Use the navigation buttons to move from one row to the next.

You could name the sheet to create an even more meaningful form as it would have a relevant title rather than just Sheet1

EXERCISE 1

Create a new spreadsheet to keep a list of all of your books. Have a title at the top - My Book Library

Include the following headings:
Title
Author
Edition details
Date acquired
Cost
Rating

Enter your first book straight into the spreadsheet.

With your cursor on the first item, create a form

Use this form to add another 5 or so books to the table. If you don't have the exact information handy, just make it up.

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.

Protection

Some of you may have already had "the talk" and be aware of the protection, but there may be a couple who haven't so this is a quick and nasty version.

Why Protection? The best reason to use protection (apart from the obvious) is so that all the great work you have done in preparing, formatting, and perfecting your spreadsheet is not ruined by someone putting information in the wrong spot.

By default, all of the cells in Excel are "protected", so if you want people to be able to edit cells, you need to unprotect them or unlock them. Any protection that is applied to individual cells is not enforced until the actual spreadsheet is protected.

To unprotect the cells, use Format Cells.

Once you have unprotected the cells that YOU want people to be ABLE to access - protect the sheet.

There are a few different levels of protection that you can apply. You will find these under Review.

OR from the Home Ribbon

 

You can be very specific about what you will allow others (or yourself) to change when using the spreadsheet and you can set a password which must be entered before the spreadsheet can be unprotected. Stay safe people!!