Guest Author - Consuelo Herrera, CAMS, CFE
Microsoft Excel offers a variety of tools when it comes to create forms
You can create your own forms by setting up a worksheet with the text, graphics, and formatting you want and then saving the workbook as a template. To fill out a blank copy of the form, users base a new form on the template by using the New command on the File menu.
Design print or on-screen forms
Users can print the sheet and fill out the form on paper or fill out the blank cells on-screen and then print the form. If users fill out a form on-screen in Microsoft Excel, you can include features that automate entering and analyzing the information.
Design and use data entry forms
If you want to use a form to enter data in an Excel list or database, you can either use a built-in data form or create a customized form or template.
You can add graphics to your forms. For example, you can add scanned images and drawing objects that you create with the Drawing toolbar, including text boxes and the text effects you create with the WordArt tool.
You can add graphics to your worksheets and charts to make them more visually appealing, to create eye-catching reports, or to add emphasis. For example, you can display a logo on your worksheet, create a flowchart, and use graphics in chart data markers. You can make your worksheet interactive by using graphic objects as hyperlinks or by assigning buttons to macros.
Types of graphics
Two types of graphics can be used in Microsoft Excel: bitmap-type pictures and metafile-type pictures.
Bitmap-type pictures are made of dots, or pixels. Photographs, for example, are bitmap-type pictures. When you select a bitmap-type picture, the Picture toolbar appears with tools you can use to crop the picture, add a border to it, set transparency, or adjust its brightness and contrast. Unlike metafile-type pictures, bitmaps can't be disassembled, or ungrouped.
Metafile-type pictures usually look like illustrations and are made up of separate lines and curves joined in a group. Most illustrations, or clip art, are in metafile format. You can ungroup and convert them to drawing objects, and then edit the graphics by using the Excel drawing tools. For example, you might insert a clip art image of a person, ungroup it, change the color of the person's clothing, and then add the modified image to another clip art image. The ready-made AutoShapes that come with Excel are in this format. When you select a metafile-type graphic, the Ungroup command is available on the Draw menu.
You can add controls to on-screen forms to make data entry simple, and to collect and process the entered information. Controls are available from the Forms toolbar.
For example, users of on-screen forms can select items from a drop-down list (combo box) control that is linked to cells that contain the list.
For printed forms, you can add labeled check boxes and option buttons. Set the controls to be cleared (unchecked) by default so that the boxes and small circles appear blank, suitable for marking on paper. If users must write several lines of information, you can use light gray cell borders to add lines to the form.
On-screen forms only
You can add macros to a form to automatically process data and further customize the form, depending on users' entries. For example, if a user selects a check box labeled "Married," a macro linked to the check box control might display the entry "Name of spouse."
To include instructions about filling out a form, you can add comments to the cells.
Because the form has all the functionality of a worksheet, you can use the cell data in formulas. For example, as users fill out item numbers and quantities in an order entry form, formulas can calculate totals, tax, and shipping costs.
Make your forms suitable to your own needs and maximize the use of Excel features.