CP Lab – Experiment 8


Aim:  Creating and validating lists, Creating spreadsheet using template

Objectives:  Implement data validation options and creating templates.

Hardware requirements: Any CPU with Pentium Processor or similar, 256 MB RAM or more, 1 GB Hard Disk or more.

Software requirements:  Windows 64 bit / Ubuntu 14 Operating System, MS office 2010 or later, Open Office for Ubuntu

Theory: Part of creating efficient and easy-to-use worksheets is to do what you can to ensure the data entered into your worksheets is as accurate as possible. Although it isn’t possible to catch every typographical or transcription error, you can set up a validation rule to make sure that the data entered into a cell meets certain standards.

  1. To create a validation rule, display the Data tab on the ribbon and then, in the Data Tools group, click the Data Validation button to open the Data Validation dialog box.
  2. You can use the controls in the Data Validation dialog box to define the type of data that Excel should allow in the cell and then, depending on the data type you choose, to set the conditions data must meet to be accepted in the cell.
  3. For example, you can set the conditions so that Excel knows to look for a whole number value between 1000 and 2000.
  4. To require a user to enter a numeric value in a cell, display the Settings page of the Data Validation dialog box, and, choose either Whole Number or Decimal from the Allow list.
  5. With Excel, you can create validation rules for cells in which you have already entered data.
  6. Excel doesn’t tell you whether any of those cells contain data that violates your rule at the moment you create the rule, but you can find out by having Excel circle any worksheet cells containing data that violates the cell’s validation rule.
  7. To do so, display the Data tab and then, in the Data Tools group, click the Data Validation arrow.
  8. On the menu, click the Circle Invalid Data button to circle cells with invalid data.
  9. You can turn off data validation in a cell by displaying the Settings page of the Data Validation dialog box and clicking the Clear All button in the lower-left corner of the dialog box.
  10. To give custom input message, click the Input Message tab. In the Title box, type title message. In the Input Message box, type your message.
  11. To give custom error message, Click the Error Alert tab. On the Error Alert page, in the Style list, click Stop. In the Title box, type Error, and then click OK.

Using Workbooks as Templates for Other Workbooks

After you decide on the type of data you want to store in a workbook and what that workbook should look like, you probably want to be able to create similar workbooks without adding all of the formatting and formulas again. When you have settled on a design for your workbooks, you can save one of the workbooks as a template for similar workbooks you will create in the future. If you want your template workbook to have more than the standard number of worksheets (such as 12 worksheets to track shipments for a year, by month), you can add worksheets by clicking the Insert Worksheet button that appears to the right of the existing worksheet tabs.

  1. To create a template from an existing workbook, save the model workbook as an Excel template file (a file with an .xltx extension), which is a file format you can choose from the Save As Type list in the Save As dialog box.
  2. If you ever want to change the template, you can open it like a standard workbook and make your changes.
  3. When you have completed your work, save the file by clicking the Save button From the list of available templates, you can double-click the template you want to use as the model for your workbook.
  4. Excel creates a new workbook (an .xlsx workbook file, not a template file) with the template’s formatting and contents in place.
  5. After you save a workbook as a template, you can use it as a model for new workbooks.
  6. To create a workbook from a template in Excel, click the File tab to display the Backstage view, and then click New.
  7. When you click New in the Backstage view, the top of the middle pane displays the blank workbook template, templates you have used recently, sample templates, and templates you created.
  8. Below that list is a set of template categories available through the Office.com Web site and a search box you can use to locate helpful templates on Office.com.

Conclusion: In this experiment, we have studied data validation options and creating templates in Excel.

R.No Name of Student Date of Performance Date of Evaluation Grade Sign of student Sign of Faculty

Reference Book: Microsoft Excel 2010 – Step by Step

Leave a Reply

Your email address will not be published. Required fields are marked *