Microsoft Excel 2007 – Part 1

Class Notes
Part 1 – Tour
1. See “Excel 2007 Part1 - Presentation.xls” for examples of how Excel can be used.
http://whpl.mywinterhaven.com/Excel2007.htm
2. Office Button
Note: Office 2007
creates smaller file
sizes than Office 2003.
Options with an
arrow provides Displays the
multiple options most recent
with an explanation 17 files.
of each.

3. Office Button | Open | Excel 2007 Part 1 Exercises.xlsx

Notice the file extension, xlsx. All Office 2007 file end with an “x” (Office 2007 file
without macros) or an “m” (Office 2007 file with macros). “X” refers to XML, Extensive
Markup Language. It allows different types of files to share data.
Note: To enable filename extensions to be seen
a. Open My Documents e. Uncheck Hide exte4nsions for known file types
b. Click Tools f. Click Apply
c. Click Folder Options g. Click OK
d. Click View
4. Save As vs. Save
Save As allows you to change the filename and/or location of where the file is saved.
Save is good for doing quick saves. It does not give the option to change filename
and/or file location. It is designated by the symbol on the Quick Access Toolbar.
Do a Save As and change the filename by adding the word My as shown:
Office Button | Save As | Excel Workbook | My Excel 2007 Part 1 Exercises.xlsx
5. Demo: Rows, columns, cell, active cell, labels, values (For review from home, see *)
6. Navigation: horizontal & vertical scrollbars, arrow keys, Ctrl Home, Ctrl End, End Arrow,
Enter, Tab (For review from home, see *)
7. Demo: Formula Bar; Name Box (For review from home, see *)
8. Demo: Sheet 1, 2, 3, New; Worksheet Tab Scrollbar (For review from home, see *)
(Covered extensively in Microsoft Excel 2007 Part 2)

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 1

9. Ribbons (For review from home, see *)
 Tabs, Groups
 Gallery (collection of commands or options)
Some galleries have a Live Preview of the data to be changed.
i.e. Select entire worksheet (Ctrl, Shift, End)
Home | click down arrow next to color text button | Drag mouse over colors
 Some buttons have enhanced screen tips.
i.e. Hover the mouse over the Merge & Center button.
Home | Merge & Center
 Help
10. Title Bar (displays filename) Minimize Restore Down Close
Affects Excel
Help Affects the open file/not
Excel
11. The Quick Access Toolbar is a place to place commands that are frequently accessed.
By default, only the Save, Undo and Redo buttons are enabled.

To add more commands:

a. Click the down arrow next to the Quick Access Toolbar.
b. Either select one of the commands listed, or click “More Commands…” for an
extensive list of other available commands.
Some commands that are handy to add are: New, Open, Quick Print, and Print Preview.

To remove commands:
a. Click the down arrow next to the Quick Access Toolbar.
b. Click More Commands…
c. On the right side of the screen, select the command to remove
d. Click Remove
e. Click OK

To set the Quick Access Toolbar to the default settings (Save, Undo, Redo):
a. Click the down arrow next to the Quick Access Toolbar.
b. Click More Commands…
c. Click Reset.

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 2

12.Demo: Status Bar (For review from home, see *)
 On the very left of the Status Bar, the status of the selected cell is displayed:
 Ready – Active Cell
 Enter – Data is being typed into the cell and the Enter key (or Tab or arrow
keys or ) has not yet been pressed. Many menu items are not available
when this status is displayed. Note: The button will be discussed in
Microsoft Excel 2007 Part 2.
 Edit – The active cell is being edited and the Enter or Tab keys have not
been pressed. Many menu items are not available when this status is
displayed.
 Point – This shows that the active cell is being selected as part of a formula.
 Views
Note: After selecting Page Layout or Page Break Preview, and then going back to
Normal view, the worksheet shows page breaks that display as dashed lines.
 Zoom slider
 Quickly perform a calculation using the Status Bar. When a range of cells is
selected the following calculations are displayed:
 Average – the average of all numbers
 Count – a total count of all cells containing numbers and text
 Numerical Count – a count of the numeric values
 Min – the smallest number
 Max – the largest number
 Sum - the total of all numbers
Select a range of cells to activate calculations on the Status bar:
a. Hold the mouse over cell A1.
b. Hold down the left mouse button.
c. While holding down the left mouse button, drag the mouse to cell G5.






d. Release the mouse button. As when a selected cell has a dark, black
border, a selected range has this as well.
e. Look at the calculations in the status bar.

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 3

Part 2 – Worksheet Tab Exercise 1: Basic Spreadsheet
1. Column Width
a. Notice that part of the cell contents in A2, A3 and A4 cannot be seen.
b. Select A2 and look in the Formula Bar.
The entire name can be seen.
c. Select B2 and look in the Formula Bar.
d. Press the Delete key. The contents of A2 overlaps into B2. Look in the Formula Bar.
Nothing is displayed because B2 is really empty…you deleted the number 11.
e. Type 11 in B2 and press Enter.
f. To make column A wide enough for all names to be seen:
1) Hold the mouse over the line between the column
letters A and B. The cursor will change as shown.
2) Double click the left mouse button.
g. Repeat steps in f. above for column B.
h. Other examples of insufficient column width:
1) ###
a) Hold the mouse over the line between columns J and K.
b) Hold the left mouse button down.
c) Drag the mouse until 3.00 is displayed.
d) Select J5.
e) Type 1234567
f) Press Enter.
e) The pound (#) signs means that the column is not wide enough.
2) 1E+06 (these numbers can vary depending on number of digits in the cell)
a) Hold the mouse over the line between columns J and K.
b) Hold the left mouse button down.
c) Drag the mouse until 5.14 is displayed.
d) 1E+06 is a scientific notation meaning that the column is not wide enough.
3) Resize the column using the double-click method.
4) Click Undo until the contents of J5 is gone.
2. Edit cell – In A3, change Sandy to Sandra
a. Select A3
b. Hold mouse over the Formula Bar between Sandy and Bullock; the cursor will change
to an I beam.
c. Click the left mouse button at the end Sandy to drop off a blinking cursor.
d. Hit the Backspace key to remove the “y” and type “ra”
e. Notice Status Bar says Edit and that many ribbon options grayed out.
f. To complete the edit, press the Enter key
g. Notice the Status Bar says Ready and the previous grayed options are now active.

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 4

 3. Fill Handle
The fill handle is the tiny black box in the lower right corner of an active cell.

Many of the Fill Handle’s features will be covered in Microsoft Excel 2007 Part 2.
In this example, it will be used to copy the text and increment the number.
a. Select cell C1.
b. Hover the mouse over the Fill Handle; the cursor will change to  .
If it changes to (move cursor) , the mouse is not in the correct place.
c. When the  is displayed, hold down the left mouse button.
d. While holding down the left mouse button, drag the mouse to cell F1.
e. Release the mouse button.
SSAAVVEE
4. AutoSum
By clicking the AutoSum button on the Home ribbon, Excel automatically adds cells
above or to the left of the active cell using the SUM function.
AutoSum Rows
a. Select cell G2.
b. From the Home tab, Editing group, click AutoSum
Because Excel cannot distinguish that the Emp # is not to be part of the formula, it
automatically includes
this cell in the equation.

c. In this situation, ignore the blue box created by Excel and select the correct range of
cells. Click cell C2.
d. While holding down the left mouse button, drag the mouse to cell F2.
e. Release the mouse button.
f. Press Enter.
g. Repeat steps c. through f. for cell G3.
h. Repeat steps d. through f. for cell G4.
i. Click Undo 2 times to remove formulas in G3 and G4.
j. Select cell G2.
k. Use the Fill Handle to copy the formula to cells G3 and G4.
AutoSum Columns
a. Select cell C5.
b. From the Home tab, Editing group, click Autosum.
c. Press Enter.
d. Press the up arrow key on the keyboard.
e. Use the Fill Handle to copy the formula to cells D5 through G5.
Demo partial column of numbers (i.e. no number in first cell)

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 5

 5. Alignment
Center column headings
a. Select cell A1.
b. While holding down the left mouse button, drag the mouse to cell B1.
c. From the Home tab, Alignment group, click the Center button.
Right align column headings
Tip: It is “sometimes” better to right align column headings in the column contains all
numbers as it makes it easier to read.
d. Select cell C1.
e. While holding down the left mouse button, drag the mouse to cell G1.
f. From the Home tab, Alignment group, click the Align Text Right button.
Center numbers in Emp # column
a. Select cells B2 through B4.
b. From the Home tab, Alignment group, click the Center button.
Demo centering contents of a column.
6. Number Format
Format numbers with a comma separator and zero decimal places
a. Select cell C2.
b. While holding down the left mouse button, drag the mouse to cell G5.





c. From the Home tab, Number group,
click the arrow in the lower right corner.
d. Complete the Format Cells
window as shown…

Demo the difference between the previous number format and: Home | Number | ,
SSAAVVEE
7. Insert Rows
Insert 2 header rows
a. Select cells A1 through B1.
b. From the Home tab, Cells group, click the word “Insert,” not the icon.
c. Select Insert Sheet Rows.

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 6

 8. Merge & Center
a. Select A1.
b. Type Report
c. Press Enter.
d. Select A1 (press Up Arrow key).
e. While holding down the left mouse button, drag the mouse to G1.
f. From the Home tab, Alignment group, click the words Merge & Center.
g. Click Merge & Center again. (unmerges cells)
h. Click Merge & Center again.
9. Add Borders
a. Select cells A3 through G7.
b. From the Home tab, Font group, click the down arrow on the Borders button.
c. Select All Borders.
d. Optional:
1) Select A7 through B7.
2) From the Home tab, Alignment group, click the arrow next to Merge & Center.
3) Click Merge Cells
10. Page Layout
From the Page Layout tab, margins can be set as well as selecting Portrait or Landscape
Orientation. To determine the margins and orientation, it’s best to do a Print Preview.
Print Preview
a. Click Office Button.
b. Select Print.
c. Select Print Preview.
d. Notice that the report would look better if it were centered. The margins and
orientation are fine.
e. Click Close Print Preview.
Margins
f. From the Page Layout tab, Page Setup group, click Margins.
g. Click Custom Margins…
h. Click the box next to Horizontally.
i. Click Print Preview.
j. Click Page Setup
k. Click OK.
l. Click Close Print Preview.
Demo: Compare Page Layout view with Print Preview
SSAAVVEE
11. Printing
a. Click the Office Button c. Select Print
b. Select Print d. Click OK
Winter Haven Public Library  http://whpl.mywinterhaven.com Page 7
Part 3 – Worksheet Tab Exercise 2: Basic Formulas

Finished Sheet
Don’t forget to press the
Enter key after entering
the formulas!

Extras:
 Place a single Bottom Border where needed
Home | Font | Border button arrow | Bottom Border
 Multiplication – Currency Format F13-G13 and F15-G15
Home | Number | $
 Mixed Operators – Percent Format G21
Home | Number | %

Formula View:
To switch between regular view
and formula view as shown here,
Ctrl ~

To switch back to regular view,

Ctrl ~ again.

SSAAVVEE
Part 3 – Worksheet Tab PEMDAS: The Order a Formula is Calculated
Select worksheet tab “PEMDAS.” Demo

Part 4 – Worksheet Tab Exercise 3a: Working with Large Spreadsheets

Note: The data in this spreadsheet was acquired from http://www.wunderground.com (History Data)
This worksheet is too wide and too long to fit on a single page.
Problems with this spreadsheet:
Columns are not wide enough.
o Some dates show as ########.
o The complete column labels cannot be seen.
o Class To Do – Resize all columns:
1. Select the All button.

2. Hold the cursor over the line between any 2 columns. When the cursor
changes to , double click the left mouse button.
o Wording in column labels are too wide.

Column and row labels scroll off the screen.

Class to Do – Scroll through spreadsheet
Spreadsheet is so large that:
o When scrolling down, the column labels scroll off the screen.
o When scrolling to the right, column A containing the date labels scrolls off the
screen.

Printed pages are not clear as column and row labels, as well as page numbers and a
report heading, do not appear on every page.
Class to Do - Print Preview
Notice that:
o Not all pages show both the column and row labels (column headings and dates)
o There is no heading at the top of the report describing the contents.
o There are no page numbers.
Close Print Preview

Print Preview activated the page break dotted lines.

It allows for a better view of what columns will be
printed on each page.
Notice that the 2 Humidity columns break across 2 pages. Perhaps it would be best if
both Humidity columns were displayed on the same page.
Click the Page Layout button on the Status Bar for another view of the page breaks.

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 9

Select Worksheet Tab Exercise 3b
Below are the changes already made to this spreadsheet in the order they were made:
Column headings were shortened and wrapped around in the cell where possible.
Examples: Changed to

Changed to

Inserted 3 rows to accommodate a report heading. NOTE: Inserting a report heading into the
spreadsheet is optional if it is only to be
printed and not viewed on the screen.
Changed to
In this case, inserting a Header would be
enough.

In the example above, rows were inserted by selecting the entire rows 1 through 3
(notice the arrow near row number 3) and then clicking the icon
on the Insert button instead of the word Insert.
Note: In Part 2, Exercise 1, Step 7, rows were inserted by selecting cells and clicking
the word Insert on the Insert button.
Typed the report heading in A1 and A2 and then “Merged and Centered” heading.
Changed to

See Part 2, Exercise 1, Step 8. Merge & Center for details.

Shaded cells containing column and row labels with light aqua as follows:
1. Select all cells containing column labels (A4 to P4).
2. Hold down the Ctrl key…do not let go.
3. Select all cells containing date (A5 to A305) Tip: If you go down too far, don’t panic!
Just continue holding down the mouse button and drag the mouse back up to the
last date. Note: Using the Ctrl key to
4. Release the Ctrl key and mouse button. select multiple ranges at
5. From the Home tab, select the arrow next one time is optional.
This could have been done
to the Fill Color icon (paint can) and Select
by changing the color of
Aqua, Accent 5, Lighter, 80%. each section separately.

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 10

Class To Do - Steps to complete the formatting of this spreadsheet:
1. Wrap Text Within Cell
a. Select cell B4
b. Hold the mouse over the Formula Bar, after the letter F (it will change to an I-
beam cursor) and click the left mouse button to drop off a blinking cursor.
c. Use the Left Arrow key on the keyboard to move the cursor between the words
Temperature and High.
d. Backspace as to change Temperature to Temp.
e. Hold down the Alt key (next to the Spacebar).
f. Tap the Enter key (this will wrap HighF around to a second line)
g. Let go of the Alt key.
h. Press Enter to complete the editing of this cell.
Note: Text wrapping will also be covered in the Microsoft Excel 2007 – Part 2 class.
2. Resize all columns using the All button.
a. Select the All button.

b. Hold the cursor over the line between any 2 columns. When the cursor changes
to , double click the left mouse button.
3. Add Borders.
a. Select cell A4.
b. Hold down both the Shift and Ctrl keys. This key combination, to select the entire
c. Tap the End key. spreadsheet, is a TIP on the last page of
d. Release the Shift and Ctrl keys. the Microsoft Excel 2007 Tips handout.
e. From the Home tab, click the arrow next to the Borders button.
f. Select All Borders
4. Center Spreadsheet column labels and data.
a. Select cell B4
b. Ctrl-Shift End
c. From the Home tab, click the Center button.
d. Select cell A4
e. Click the Center button.
5. Freeze Panes to prevent column and row labels from scrolling off the screen.
a. Select cell B5 This does not have any effect on printing.
b. From the View tab, click Freeze Panes Freeze Panes is a TIP on Page 4 of the
c. Click Freeze Panes Microsoft Excel 2007 Tips handout.
6. Click Print Preview. Note the following problems:
The Report Heading is not centered and it does not appear on succeeding pages.
Column and row labels do not appear on every page.
SSAAVVEE There are no page numbers.

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 11

7. Print row and column labels on every page.
a. From the Page Layout tab, Page Setup group, click Print Titles.
b. From the Page Setup dialog box, click the “Collapse Dialog button” next to the
“Rows to repeat at top:” box.

c. Click row 4.

d. Click the “Collapse Dialog button” to display the entire Page Setup dialog box.
e. Click the “Collapse Dialog button” next to the “Columns to repeat at left:” box.

f. Click column A.

Column P was automatically inserted because “Merge and Center” was used on
cells A1 through P1. However, we only want to repeat column A on every page.
g. Note the blinking cursor in the “Columns to repeat at left” box. Backspace over
the letter P and type A.

h. Click the “Collapse Dialog button” to display the entire Page Setup dialog box.
i. Click OK.
j. Print Preview to display the results.
k. Close Print Preview.
8. Create Header and Footer.
Header:
a. From the Page Layout tab, Page Setup group,
click the arrow in the lower right corner.
b. Click the Header/Footer tab.
c. Click Custom Header.
d. Press the Tab key on the keyboard to move the blinking cursor to the Center box.
e. Type WH Weather NOTE: Normally this heading would be
f. Press Enter. the same as the report heading shown in
g. Type 2009 the spreadsheet. However, this is quicker
to type during class.
h. Click OK.

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 12

 Footer:
a. Click the down arrow next to “(none)” in the “Footer:” section.
b. Select “Page 1 of ?.”

i. Click OK.
j. Print Preview to display the results.
k. Scroll down and click Next Page.
Problem: The report heading in the spreadsheet displays as well as the Header.
c. Close Print Preview.
9. Set Print Area. NOTE: This step is not necessary if rows
were not inserted at the top for a report
a. Select cell A4.
heading.
b. Shift-Ctrl End (to select spreadsheet)
c. From the Page Layout tab, Page Setup group, click Print Area.
d. Click Set Print Area.
e. Print Preview.
f. Close Print Preview.
10.Insert Vertical Page Break. (You want all “Wind” columns on the same page.)
a. Click any cell in column M (not in row 1 or 2 as these are merged cells).
b. From the Page Layout tab, Page Setup group, click Breaks.
c. Click Insert Page Break.
d. Print Preview
e. Close Print Preview.
11.Insert Horizontal Page Break. (You want only 1 month per page.)
a. Select cell A36 (the first day of the next month).
b. Click Breaks.
c. Click Insert Page Break.
d. Select cell A64 (the first day of the next month)
Note: Normally, you would repeat the above steps for each month. However, for the
sake of time, we will only do the first 2 months.
e. Click Breaks.
f. Click Insert Page Break.
g. Print Preview.
h. Click Next Page.
i. Close Print Preview.
SSAAVVEE

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 13

 Header/Footer Scenarios:

Long Spreadsheet:
1. Insert rows at the top of the spreadsheet for a report heading.
Rows 1-3 contain the report heading (or as many rows you want).
Row 4 contains column labels.
2. Rows to repeat across the top: 1 through 4
a. Page Layout tab | Page Setup group | Sheet tab | Print Titles

3. Optional: Insert Footer (If no footer is wanted, click OK)

a. From the Page Setup dialog box
(shown above), select the
Header/Footer tab.
b. Select a Footer ONLY
(no Header)

c. Check to make sure Bottom margin is larger than the Footer margin.
i. From the Page Setup dial box (shown above), select the Margins tab.
ii. Adjust margins as necessary.
Higher Lower

iii. Click OK
4. Optional: Insert Page Breaks

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 14

 Header/Footer Scenarios:

Wide Spreadsheet or Long & Wide Spreadsheet WITH NO added rows for a report heading.
1. Row 1 & Column A to repeat.
a. Page Layout tab | Page Setup group | Sheet tab | Print Titles

2. Insert Footer and Header.

a. From the Page Setup dialog box
(shown above), select the
Header/Footer tab.
b. Enter a Custom Header.
c. Select a Footer.
3. Check to make sure Top and Bottom margins are larger than the Header and Footer
margins.
a. From the Page Setup dial box (shown above), select the Margins tab.
b. Adjust margins as necessary.
Higher Lower

Higher Lower

c. Click OK
4. Optional: Insert Page Breaks.

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 15

 Header/Footer Scenarios:

Wide Spreadsheet or Long & Wide Spreadsheet WITH added rows for a report heading.
NOTE: This is the same scenario used in Exercise 3.
1. Insert rows at the top of the spreadsheet for a report heading.
Rows 1-3 contain the report heading (or as many rows you want).
Row 4 contains column labels.
2. Row 4 and Column A to repeat.
a. Page Layout tab | Page Setup group | Sheet tab | Print Titles

3. Insert Footer and Header.

b. From the Page Setup dialog box
(shown above), select the
Header/Footer tab.
c. Enter a Custom Header.
d. Select a Footer.
2. Check to make sure Top and Bottom margins are larger than the Header and Footer
margins.
a. From the Page Setup dial box (shown above), select the Margins tab.
b. Adjust margins as necessary.
Higher Lower

Higher Lower

c. Click OK
3. Optional: Insert Page Breaks.
4. Set Print Area.
a. Select all cells in the body of the spreadsheet (not the report heading rows).
b. Page Layout tab | Page Setup group | Print Area | Set Print Area

*For review from home, download: http://whpl.mywinterhaven.com/ComputerClasses/Excel/Excel2007QuickReference.pdf

Winter Haven Public Library  http://whpl.mywinterhaven.com Page 16