Lesson 9 - 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 session - PivotTableDataOnly.xlsx

N.B. Before you start, it is best to remove the totals at the bottom of the table as we want out new data to be added there.

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.

Put your cursor somewhere in the table and click on the form button that you just added.  Excel is clever, as previously mentioned, and usually makes the right assumptions for your spreadsheet and will create the form based on the data that it can see.

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.

The criteria button can be used to scroll through data that meets your condition.

You can type your criteria against the relevant column in your form. You can use all of the standard comparison operators: < > =

Use the Find Next and Find Prev buttons to scroll through the items in your table that meets the criteria.

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.

Any cells that have formulas in them will be protected, which you can see in the above example.

Class Activity 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.

************ IMPORTANT *************

Data Validation

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

For this example, I will be using the same 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 add a message to make sure that the user knows what they need to do.

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

Start by selecting the data type - in this case I selected Decimal as the data type

Based on the data type selected at "Allow", you will have different validation options for Data.

You can also set up validation and check to see if there are any cells that do not meet the criteria.

I have put criteria in that Qtr 1 has to be greater than 2000. Once I put the criteria into C4, I can copy it down so that it applies to all the date for Qtr 1.

Use Copy (any method) and then Paste Special to copy just the Validation and not the data.

Data that does not meet the validation rule  - i.e. Qtr1 > 2000 will be circled.

Text to Columns

Text to columns can be used to split a cell into multiple cells. This is useful if you have imported data in from a document or even from the internet.

The example below has data that has been copy/pasted from the internet.

I used the units that were in Group A and used copy and paste to pop them into a spreadsheet.

Use Text To Columns from the Data Tab to split the data. Excel has determined that Fixed width best suits my data, which is what I want. I want to separate the unit code from the description.

All I need to do is use the bar to tell excel where I want the data to be split. You can see the bar is now located just after the code. Excel made this assumption for me.

When I am happy with the split, I click Next and then Finish.

You can see that the data has been split into 2 columns at the point specified.

Camera Tool

And to finish off our advanced Excel, we will take a quick look at the camera tool. Its main purpose is to capture snippets of your spreadsheet and combining them onto a single page.

First, you need to add the camera to the quick access toolbar. Use the same technique that enabled us to add the form button to the toolbar.

To use the camera, select the area of the sheet you want to capture, click on the camera button,then click on the spot you want to paste the shot to.

In my example, I want the 3 highlighted areas on to sheet 2. I start by using my mouse to select the light blue data. Click on the Camera, and then click onto sheet 2.

Data is pasted onto sheet 2 - this is treated in the same way as an image. You can move it and resize it using the handles.

Below is my sheet with all 3 areas captured and placed on sheet 2.

If you would like some more Excel Tips and Tricks, here are a few links:

https://www.lifehack.org/articles/technology/20-excel-spreadsheet-secrets-youll-never-know-you-dont-read-this.html

https://support.office.com/en-us/office-training-center/excel-tips


You are now ready to complete the Assessment.

You have now covered enough of Excel to be able to complete this relatively easy task and it should now make more sense to you. Details are on Moodle. Please read the Assessment Guide and make sure that you include all parts of the assessment. If you have the guide printed, you can tick them off as you go. I am still getting assessments that have parts missing - if you follow the Assessment Guide, you can make sure that you submit everything.