| Creating Enclosures Using Microsoft Excel |
|
|
ARTICLES > Care of Collections 3679
Creating Enclosures Using Microsoft Excel
by Shelagh Linklater
Abstract
A template was developed in Microsoft Excel to help create custom-made enclosures for artifacts and records. The program streamlines and clarifies the process of making boxes and folders. Measurements input by the user produce a diagram with specific dimensions, cutting and fold lines. Instructions, tools and materials are included with hyperlinks to suppliers’ websites. Although originally developed as a teaching tool for nonconservators, it is very flexible and can be augmented to suit particular needs. This can be used to produce containers for individual records or as a planning tool for larger re-housing projects.
Introduction
There are a large variety of archival enclosures available commercially. Custom-made enclosures are sometimes necessary to cut costs and to house odd size records or artifacts. Excel, a calculation and graphing program, ensures accuracy in the making of these containers by creating diagrams, calculating dimensions and estimating expenses. The program consists of several worksheets each representing a different type of enclosure such as boxes, folders and wrappers.
How to Use the Template Each sheet contains a list of materials, suppliers and a diagram with custom measurements (Fig. 1). Formulas for each dimensions of the enclosure are contained in separate cells beneath the diagram. Each formula takes into account the thickness of the enclosure material in addition to the dimensions of the record or artifact. The cell number for each calculation is added to textboxes within the diagram (Fig. 2). From left to right: Figure 1. Template. Printout of a template for clamshell box showing all features; Figure 2. Template Detail. Detail of template showing formula calculations for each dimension; Figure 3. Instructions. Detail of template showing instructions and supplier’s lists with hyperlinks and Figure 4. Calculation Table. Image of a calculation table.
As record measurements are input by the user, the custom dimensions for cutting and folding appear within the diagram in these textboxes.
Additional information aids the user in completing a project. Instructions and selection criteria for particular artifacts or records can be added to a textbox (Fig. 3). Accompanying this are materials and suppliers with hyperlinks to websites so current prices can be researched. Expenditures are calculated at the end of the worksheet. This table calculates the number of enclosures that can be made per sheet plus the cost of each enclosure (Fig. 4). How to Create a Template To create an enclosure template, begin with a set of written instructions for making an enclosure. Alternatively a ready-made enclosure could be dismantled and used as a model (Fig. 5). Diagram Open a worksheet in Excel and create a rectangle by double-clicking on the Rectangle symbol on the Drawing toolbar. This rectangle can be infilled with a color to help delineate diagram markings (Fig. 6). Sketch out the diagram of the enclosure by inserting rectangles within this shape multiple times. Use the line tool to denote creasing or cutting lines. A dotted line indicates a crease. A solid line represents a cutting line (Fig. 7).
Dimensions
Calculations for each dimension of the enclosure are created in cells beneath the diagram. Enter the length, width and depth of the record in centimeters in three separate cells beneath the diagram and label each cell accordingly (Fig. 8). Use the cell number of each record dimension in the following formula. Each formula takes into account the thickness of the enclosure material in addition to the measurements of the record or artifact (Fig. 9).
The cell number for each calculation is added to textboxes within the diagram. To create a textbox, click the textbox symbol on the toolbar. Place the cursor within the diagram to its correct size. The textboxes are added to the diagram for each dimension of the enclosure. Add arrows on either side of the textbox to indicate the extent of each measurement (Fig. 10).
Add calculation totals to create the overall board measurements. Underneath the diagram, create two cells labelled with overall length and overall width. To create the width, for example, start from the left side of the diagram and add each line in sequence (Fig. 11). Add the cell numbers of these summations to the diagram, one on either side. Arrows can also be added to show breadth. As record dimensions are input by the user, the measurements for cutting and folding lines are calculated to appear within the diagram in these textboxes.
Upper, from left to right: Figure 5. Model. Picture of a dismantled box; Figure 6. Rectangular Insert. Image of Excel spreadsheet with rectangular insert; Figure 7. Sketch. Image of Excel spreadsheet with image of a slip case inserted; and Figure 8. Record Measurements. Detail of spreadsheet showing where record measurements are entered.
Lower, from left to right: Figure 9. Formulas. Detail of spreadsheet showing formulas for enclosure calculations; Figure 10. Diagram textboxes. Detail of spreadsheet showing how textboxes are inserted in a diagram, and Figure 11. Overall Board Measurement. Illustration showing calculation of overall board measurement.
Instructions
Written instructions can be added by inserting text in a textbox. To create a textbox, click the textbox symbol on the toolbar, place it beneath the diagram and pull on the sizing handles of the textbox until it reaches the desired size. Instructions can then be typed in this textbox (Fig. 12). Hyperlinks Hyperlinks can be added to link users to supplier’s websites. To create ahyperlink, select Insert from the toolbar and from the drop down menu select Insert Hyperlink. Type the text to represent the hyperlink in the box labelled Text to display. This could for example be the name of the supplier (Fig. 13). In the box labelled Address, type in the URL of the supplier’s website and then click OK. Cost Calculation Table Price information can be retrieved from these websites and inserted in a cost calculation table. To create this table, insert seven columns and label two columns with the length and width of the overall board measurement. Insert the cell number of the calculated overall board dimension in these cells. In the next two columns, label them with the height and width of the purchased board. Multiply the purchased board measurements and subtract them from the overall board measurements to estimate the number of enclosures made from a single sheet (Fig. 14). Insert the price and dimensions of the single, purchased sheet retrieved from the supplier’s website. Divide the number of projects per sheet by the price of a single sheet to calculate the cost per enclosure.
Protecting Data
The worksheets can accidentally be altered creating inaccurate results. To prevent calculations from becoming corrupted, cell formulas need to be locked and protected. To protect data, open the worksheet and press CTRL+A to select the entire sheet (Fig. 15). From the Format menu, choose Cells (or press CTRL+1) and then click the Protection tab. Deselect the Locked check box and then click OK. Press F5, and then click the Special button. Click the Formulas option button in the Go To Special dialog box. Ensure the four check boxes below it are selected. Click OK and Excel will select the cells that contain formulas (Fig. 16). Press CTRL+1 again, click the Protection tab, select the Locked check box, and click OK. From the Tools menu select Protection and Protect Sheet… and click OK to activate protection (Fig. 17). To deactivate protection later, from the Tools menu select Protection and Unprotect Sheet. From left to right: Figure 12. Instructions. Detail of spreadsheet showing how textbox is inserted and instructions are added; Figure 13. Hyperlinks. llustration showing the Insert Hyperlink box; Figure 14. Spreadsheet table showing cost and material calculations; Figure 15. Format Cells menu. llustration showing a Format Cells menu; Figure 16. Go To Special menu. Illustration showing a Go to Special menu with check boxes, and Figure 17. Protecting a spreadsheet Illustration showing how to protect a spreadsheet from the Tools Menu.
In addition to the guidelines provided by the article, the author has made available a template for creating different types of enclosures (Clamshell Box, Wrapper, Phase Box, Box with Lid, Slipcase, 4 Flap Folder and Portfolio) which is now available to download.
Instructions for working with the Template:
- Only enter data under the heading Enter Record Measurements. If the other cells are altered, the results can become skewed.
- The formulas on each sheet are protected with a password to prevent accidental alteration. If you wish to customize the sheets, go to Tools - Protection - Unprotect Sheet on the toolbar. When you are prompted for a password insert: ‘box’.
- After making your own alterations, go to Tools - Protection - Protect
Sheet and re-enter the password to protect the sheet again.
References
2. Durham University, “Four Flap Folders”
http://palimpsest.stanford.edu/byorg/abbey/an/an15/an15-6/an15-610.html
About the author
Shelagh Linklater
10.“The Preservation of Oral History Tapes,” Dawson and Hind ,Volume 14, No. 1, Winter, 1987 pp. 21.
▲TOP
To save this article or see images in maximum quality please
Permanent link to this resource: http://www.e-conservationline.com/content/view/569/100/ |
