Return to Elective Courses
Excel
Project 4
Creating Templates, Workbooks with Multiple Worksheets, and Web Pages
Introduction
Consolidation - process of summarizing worksheet data on multiple worksheets on one worksheet
Template - special workbook or worksheet used as a pattern to create similar workbooks or worksheets
consists of a general format and formulas common to all worksheets
Creating the Template
Template - contains labels, formulas, and formats used in each of the sheets
dummy data used in place of actual data to verify formulas
Note: difference between developing workbook and template is the way it is saved.
Template - xlt extension
Workbook - xls extension
Common Types of Series
Date/Time - (Jan, Feb, Mar …)
AutoFill - (1, 1, 1, …)
Linear - (1, 2, 3, …)
Growth - multiples values by a constant factor
create by pointing to Fill on Edit menu and clicking Series
Click Growth option button
Applying Number Formats
Fixed dollar sign - displays in the same position in cell
assign click Currency button
Floating dollar sign - displays immediately to left of the first significant digit
assign by selecting desired format from Format Cells dialog box
Creating Customized Format Code
Format code - series of format symbols
To view - select Custom in the Category list box
can create your own format codes or modify Excel codes
4 Sections Format Code
Positive numbers
Negative numbers
Zeros
Text
$*#,##0.00_;[Blue]#,##0.00;0.00;"The answer is "@
for positive for negative for zero for text
numbers number numbers
Format code need not have all 4 sections
Alternative Uses of Templates
Xlstart folder - Excel’s startup directory
autotemplates - templates stored in Xlstart folder
save templates to Xlstart folder
- select it
later by clicking New on File menu
- if saved using file name Book, Excel uses it as default Blank Workbook template as you begin workbook
Drilling Entry Down
Through Worksheets
Drilling an entry - enter a number once and drill it through worksheets so that it displays in the same cell on all selected worksheets
advantage - efficient way to enter data common among worksheets
Referencing Cells on Other Sheets in Workbook
Sheet reference - sheet name and cell reference to other sheets in workbook
=Los Angeles!B5
sheet reference exclamation cell
or sheet name point reference
Sum Multiple Sheet Cells
Create sum
in cell B5 on sheet4
=sheet1!B5 + sheet2!B5 + sheet3!B5
quicker way
:
=SUM(sheet1:sheet3!B5)
3-D range
- range that spans two or more sheets in workbook
3-D reference
- reference in 3-D range
Entering a Sheet Reference
Enter sheet reference by typing or clicking sheet tab in Point mode.
If range is located on several worksheets, click 1st sheet tab, drag through cell or range, hold down SHIFT key, click sheet tab of last sheet to reference
Excel includes the cell(s) on end sheets and all sheets in between.
Adding Comments to Workbook
Comments (notes) - used to describe the function of cell, range of cells, a sheet, or entire workbook
identify workbooks
clarify entries
Assign comments - use Comment command on Insert menu
Overall Workbook Comments
1. Worksheet title
2. Author’s name
3. Date created
4. Date last modified (use N/A if not
modified)
5. Template(s) used
6. Short description of purpose
Headers, Footers, Margins
Header - printed at top of every page
Footer - printed at bottom of every page
Margins (defaults) - Top-Bottom is 1 inch, Left-Right is .75 inches
Page setup - used to change margins, headers, and footers
Excel does not copy page setup characteristics when one sheet is copied to another
Web Pages
Intranet - closed network
browser - Netscape Navigator, Microsoft Internet Explorer
HTML (Hypertext Markup Language) - format of Web Page, allows you to mark or tag a document so it can be viewed by browser
Save as HTML command on File menu
End
Excel
Project 4
Computer Assignment 4
Return to Elective Courses