Introduction to Microsoft Excel

Introduction to Excel

Overview

Goals

  • To learn basic, introductory Excel skills in a manner that is practical, attractive, and fun.
  • To leave the course with a solid, demonstrable knowledge of the topics covered.

 

Objective

  • To receive a certificate of completion that is based on competency and attendance.
  • The general format of each class will include an instructor-led lecture, in-class exercises, and individual assignments. There will also be handouts that can be used as a review and as future reference.
  • Encourage students to take advantage of the computer lab to practice what they’ve learned in class as well as work on any assignments they may have. Make self-paced tutorials available during computer lab time should they miss a class or need additional work. We encourage the Mavis Beacon Teaches Typing tutorial during computer lab hours.

In order to get the most out of this class, students should already be familiar with:

Navigating through Microsoft Windows and Word using pull down menus, toolbars; keyboard familiarity and mouse control; saving, closing, and retrieving files

 

Week 1: Getting Started with Excel

  • Course Introduction
  • Understanding Terminology
  • Exploring Excel Window
  • Opening, Closing, and Saving Workbooks

· Discovering Practical Uses for Excel

 

Week 2: Editing Worksheets, Using Workbooks

· Entering Data (text, numbers, dates)

· Using AutoComplete and AutoFill

· Selecting, Cutting, Copying, Moving, Pasting, Deleting and Inserting Data

· Undoing Actions

· Sizing and Inserting Columns and Rows

· Sorting Data

· Using and Customizing Multiple Sheets

 

Week 3: Formatting Data and Worksheets

· Changing Cell Format

· Changing Text Font, Alignment and Size

· Painting Formats

· Using Borders and Shading

· Autoformat

· Headers and Footers

· Defining Page Setup Options

· Previewing and Printing Worksheets

 

Week 4: Basic Formulas and Functions

· Creating and Editing Formulas

· Using Cell References

· Using Functions

· Using AutoCalculate

 

Week 5: Charts and Graphs

· Creating and Editing Charts

· Changing Styles and Formats

· Changing Cell References

 

Week 6: Review, Practice; Last day of Formal Class

· Macros

· Attaching Files to e-mail

· Review and Catch-up

 

 

 

 


Class 1

Terminology/Definitions

 

 

Workbook electronic file in which your work and store data

Worksheet area of a workbook in which you enter and manipulate data; an Excel worksheet consists of a grid of 256 columns by 65,536 rows

Title Bar contains name of active application; when you maximize workbook window, workbook title appears in Excel title bar; otherwise, title appears in a separate title bar within workbook window

Menu Bar contains names of currently available command menus and system menu icons

Toolbars bars containing shortcuts for commonly used commands

Formula Bar displays contents of active cell

Name Box displays cell reference of active cell; also displays name of current function when using Formula Palatte

Sheet Tabs tabs containing names of individual worksheets within a workbook; you move between worksheets by clicking on sheet tabs; tab for active worksheet displays in bold

Status Bar displays messages about active worksheet selection

Gridlines vertical and horizontal lines that extend across worksheet and define cells

Columns contains worksheet data and are labeled from A through Z, AA through AZ, and so on

Cell Pointer thick, rectangular box that indicates location of the active cell; the worksheet containing the active cell is the active worksheet

Horiz. Scroll Bar contains scroll arrows and scroll box you can use to scroll worksheet left or right; you can click on scroll arrows, drag scroll bar, or click anywhere in scroll bar to scroll through worksheet

Vert. Scroll Bar contains scroll arrows and scroll box you can use to scroll worksheet up or down

Rows contains worksheet data and are labeled 1 through 65,536

Cell point in a worksheet at which a column and a row intersect

Office Assistant animated character that displays in your workspace to provide assistance when you are using an Office application

                     
   
 
   
 
   
 
   
 
   
 
   


Class 1

Review

 

If you missed the first Excel class and would like to catch up, or if you attended and would like a review, try the following exercises.

 

1) Create a personal folder on the Network

2) Open Excel and identify the following parts of the Excel window (if you can’t identify one, ask a lab assistant)

a. Workbook

b. Worksheet

c. Title Bar

d. Menu Bat

e. Toolbars

f. Formula Bar

g. Name Box

h. Scroll Bars

i. Sheet Tabs

j. Status Bar

k. Taskbar

l. Min, Mix and Close buttons for the file and the application

m. Gridlines

n. Columns

o. Rows

p. Cells

To learn about other features of Excel, try the following

Rest your mouse pointer on any symbol or part of the screen, after a few seconds, a box should appear that will tell you the name of that feature

3) Also, you can go the HELP menu and select “What’s This” then click on any part of the screen or any command in the menus and Excel will display a box that shows not only the name of that feature, but a brief description.

4) Try making a list of people in your family using their first name, last name and birthday like the example shown below. (Hint: To edit a cell you’ve already typed in, select the cell and press the F2 key on your keyboard.)

 

A

B

C

1

Katie

Peterson

7/10/74

2

Mike

Peterson

12/10/72

3

Nikki

Parker

11/6/54

4

Robin

Parker

5/13/61

5

Lillee

Parker

3/29/82

6

Molly

Parker

8/15/88

7

Jesse

Parker

8/4/91

8

Nancy

Peterson

2/23/50

9

Henry

Peterson

1/6/47

10

Peter

Peterson

2/15/77

 

5) Insert a new row and add headings to your columns by following these steps:

a. Click on the number for row 1.

b. Go to the INSERT menu and select ROWS.

c. Type the headings First Name, Last Name, and Birthday in the new row.

d. Select your headings, then click on the B button on the formatting toolbar so that your headings appear in bold as shown below.

e. With your headings still selected, click on the CENTER button on the formatting toolbar so that your headings appear in the middle of the cells.

 

A

B

C

1

First Name

Last Name

Birthday

2

Katie

Peterson

7/10/74

3

Mike

Peterson

12/10/72

4

Nikki

Parker

11/6/54

5

Robin

Parker

5/13/61

6

Lillee

Parker

3/29/82

7

Molly

Parker

8/15/88

8

Jesse

Parker

8/4/91

9

Nancy

Peterson

2/23/50

10

Henry

Peterson

1/6/47

11

Peter

Peterson

2/15/77

 

f. If the row isn’t wide enough to see everything you’ve typed, double-click on the line in between the column letters.

6) Order you list by name using the following steps.

a. Select all of your information.

b. Go to the DATA menu and select SORT

c. Pick the option to sort by Last Name, then by First Name in ascending order. (Make sure the button is selected that shows your list has a header row.)

d. You should see that your list is now in alphabetical order. Now try sorting by age.

e. The following table shows the example data sorted by birthday in descending order.

 

A

B

C

1

First Name

Last Name

Birthday

2

Jesse

Parker

8/4/91

3

Molly

Parker

8/15/88

4

Lillee

Parker

3/29/82

5

Peter

Peterson

2/15/77

6

Katie

Peterson

7/10/74

7

Mike

Peterson

12/10/72

8

Robin

Parker

5/13/61

9

Nikki

Parker

11/6/54

10

Nancy

Peterson

2/23/50

11

Henry

Peterson

1/6/47


Class 1

Homework

 

Part One: Review

Pretend you are about to start your own computer consulting business and need to make a list of all the office supplies you will need to purchase. Using the data below, create a worksheet. Put the column headings in the first row. Save and print it for class.

 

Item Unit Cost Unit Number Needed

Computer $899.79 1

Printer $229.30 1

Desk $300.00 1

Desk Chair $143.78 1

Customer Chairs $50.00 chair 3

Printer Paper $25.00 ream 3

Pens $10.00 20 pens 2

Notepads $6.00 3 pads 5

Paper Clips $1.00 100 clips 3

Stapler $4.99 1

Staples $0.89 100 staples 5

Business Cards $5.49 100 cards 3

Folders $4.79 20 folders 5

 

 

Part Two: Start Your Budget

Choose to make a budget for anything, real or imaginary. Throughout the course, you’ll build upon the work you start now. Enter each item in your budget and its cost in a worksheet. Remember to save your work!

 

Below is an example of a yearly budget a college student might prepare to plan for costs other than room, board and tuition. Your budget can be for anything you want!

 

Item Cost

Text Books $300

Notebooks $30

Binders $25

Pens $20

Pencils $12

Paper $40

Eating Out $200

Entertainment $150

Flight Home at Christmas $400

Class 2

Tips

Editing Data in a Cell

· To edit data in a cell without just retyping all of it, double click on that cell so you see a flashing cursor.

OR

  • Select a cell and then click on the formula box.

 

To Select Groups of Cells

  • To select a column, click on that column’s letter.
  • To select a row, click on that row’s number.
  • To select the whole worksheet, click on the gray square in the upper left corner of the worksheet.
  • To select side-by-side cells, click and drag over those cells OR select one cell and hold down the shift key as you move to the last cell you want selected.
  • To select non-contiguous cells, click on the first cell and thn hold down the control key as you click on the other cells you choose.

To Copy and Paste Data

To Cut and Paste Data

Select the cell(s). Then…

Click Edit on the Menu Bar. Choose Copy. Select the cell where you want to paste. Click Edit. Choose Paste. Hit ESC to remove the “copy” border.

Click Edit on the Menu Bar. Choose Cut. Select where you want to paste. Click Edit. Choose paste.

OR

OR

Click the Copy tool on the Tool Bar. Select the cell(s) where you want to paste. Click the Paste tool. Hit ESC to remove the “copy” border.

Click the Cut tool on the Tool bar. Select the cell(s) where you want to paste. Click the Paste tool.

OR

OR

Drag and Drop: Hold down the control key as you move the mouse to the border of the selected region. When the mouse changes to an arrow with a plus sign, click and drag the mouse to the right until the border shadow fills its new cells. Drop it!

Drag and Drop: Move the mouse to the border of the region so it changes to an arrow. Click and drag until the border shadow fills its new cells. Drop it!

OR

 

Use Auto Fill: Click on the fill handle and drag over the cells where you want to paste the data.

 

 

To Transpose Data

“Transpose” means to switch the columns into rows and the rows into columns. All you have to do is select the cells, copy them, select “Paste Special” in the Edit menu, check the box to transpose and click OK.

 

To Insert Rows or Columns

Select a cell in the row below or the column right of where you want the row/column inserted. Choose Row or Column from the Insert menu.

 

To Name Sheets

Double-click on the sheet tab. Type the name over the label already there. Press enter.

 

To Delete a Column or Row

Select the column or row. Choose Delete on the Edit Menu. OR Right-click on the row or column and choose Delete on the pop-up menu.

 

To Resize a Column or Row

Move the mouse between the column letters or row numbers so the mouse pointer becomes double headed. Click and drag the column/row to whatever size you want.

If you want the column/row to fit the size of the longest entry, double-click the separation between columns/rows when the double headed arrow appears.

 

Class 2

Homework

Part One

 

Your teacher, Ms. Mary Pavan, has asked you to help her set up a worksheet to organize her grades. She plans to give three major exams this term for her Microsoft Excel 101 class.

 

  1. Create a worksheet file that summarizes student exam grades. Put the title, “Excel Exam Grades,” in cell A1. If students were absent for a test, leave the appropriate cell blank.

 

The students and their exam grades for Test 1, 2, and 3 are:

 

Holder: 87, 68, 80

Ellis: 90, 70, 73

Arthur: 78, 96, 80

Krump: 75, 90, 93

Bailey: 71, 89, 80

Costa: 67, 79, 80

Jenkins: absent, 80, 70

Ingersol: 98, absent, 70

Fields: 76, 90, 90

Danvers: 88, absent, 80

Gonzalez: 84, 91, 76

 

  1. Put the student names in alphabetical order by sorting them.
  2. Add a row above the student names and scores. Label the columns: Student name, Test 1, Test 2, Test 3.
  3. Add a column in front of the names. Give each student an ID number, starting with 201. Use auto fill instead of typing in all the numbers.
  4. Change the column width of the name column to fit the widest entry.
  5. Copy the data from Sheet 1. Paste it into Sheet 2.
  6. Rename Sheet 1 “Last Names.”
  7. Rename Sheet 2 “First Initials.”
  8. In Sheet 2, edit the names to include a first initial for each student as follows:

 

Arthur, M.

Bailey, F.

Costa, A.

Danvers, A.

Ellis, S.

Fields, L.

Gonzalez, H.

Holder, R.

Ingersol, J.

Jenkins, K.

Krump, G.

 

Part Two: Project

 

  1. Insert two rows above your budget. In the new A1 cell, enter the title for your table. In the second row, label the columns with appropriate headings (example: “Item” or “Cost”).
  2. Insert a column before the table. Label it “Item Number.” Use auto fill to number each item in your list.
  3. Copy and paste your table from Sheet 1 to Sheet 2.
  4. In Sheet 1, sort your list so the items are in alphabetical order. In Sheet 2, sort the items by cost, greatest to least.
  5. Rename Sheet 1 “ABC Order.” Rename Sheet 2 “Cost Order.”

 


Class 3

Instructor’s Outline

 

Format Cells

Paint Format

Autofill

Rotate Text

Merge Cells

Change Font Type and Size

Shading and Borders

Insert 3D objects

Page Setup

Headers and Footers

Page Break Preview

Print Preview

AutoFormat

 

 

Class 3

Tips

 

Formatting Numbers

  1. Select the cell(s) you want to format.
  2. Click on Format on the menu bar.
  3. Choose Cells.
  4. Select the Number tab at the top of the dialog box.
  5. Choose the format style you want: General, Number, Currency, or Accounting.
  6. Click OK.

 

Formatting Dates

  1. Select the cell(s) you want to format.
  2. Click Format on the menu bar.
  3. Choose Cells.
  4. Select the Number tab at the top of the dialog box.
  5. Choose Date.
  6. Choose the type of date on the right side of the dialog box.
  7. Click OK.

 

Changing Text Orientation

  1. Select the cell(s).
  2. Click on Format on the menu bar.
  3. Choose Cells.
  4. Select the Alignment tab at the top of the dialog box.
  5. Click and drag the Orientation to the angle you want.
  6. Click OK.

 

Merging Cells

  1. Select the cells.
  2. Click the “Merge and Center” tool on the toolbar. (Note: you could also go through the Format menu to Cells, then Alignment, then select the Merge Cells option.)

 

Changing Font Type, Size, Color, Borders or Background

  1. Select the cell(s).
  2. Go to the appropriate tool on the toolbar.
  3. Click on the drop down arrow to open the menu.
  4. Choose the font type, size, color, or background that you like.
  5. Note: you could also do this through the Format menu by choosing Cells and then going to the Font, Borders, or Patterns tab.

 

3-D Objects

  1. Make sure you have the drawing toolbar. If not, open it up by clicking View on the menu bar, then Toolbars, then Drawing.
  2. Draw any object.
  3. While the object is still selected, click on the 3-D tool and choose the one you want.

Autoformat

  1. Select the region you want formatted.
  2. Choose Format on the menu bar.
  3. Choose Autoformat.
  4. Scroll through the options.
  5. Choose the one you like.
  6. Click OK.
  7. Note: you can still change individual things about your table.

Page Setup

  1. To change the page setup (how it prints out, not how it looks on your screen), go to File on the menu bar, then choose Page Setup.
  2. Go through the different tabs to change things like margins, centering on the page, layout, headers and footers, etc.

 

 

Class 3

Homework

 

Part 1

Format the South and East worksheets as we did the North worksheet in class. Choose different formats for each worksheet. Put at least one 3-D object in each worksheet. Print out South in portrait orientation. Print out East in landscape orientation.

 

Part 2

Format the budget you’re doing for your long-term project. Since you have 2 worksheets, make sure you do both of them. Print out a copy of each to bring to class.

 


Class 4

Tips

 

Creating Formulas

1. Select the cell you want the formula in.

2. Type =.

3. Enter the formula by clicking on the appropriate cells and typing the operators (+, -, *, /, etc.).

4. Click the check mark on the formula bar.

5. You can autofill this formula into other cells in the same row or column by clicking and dragging the fill handle.

 

Editing Formulas

1. Select the cell where the formula is.

2. Click in the formula box to edit the formula.

3. To change the cell reference, highlight it, then click on the new cell reference.

4. To make a cell reference absolute, insert a $ sign before its column letter and/or its row number.

 

Using Functions

1. Select the cell where you want the function.

2. Click on the fx button on the toolbar.

3. Select the function you want.

4. Use the Formula Palette to select the cells to include.

 

Note: You can also type the function and operators into the formula bar.

 

Class 4

Homework

Part One

  1. Use the table of student exam grades from Lesson 2 Homework for this assignment (Sheet 1/Last Names). If you don’t already have it done, make the table so it looks like the one below.
  2. Add a column to the right of Test 3. Label it “Average.”
  3. Add 3 rows below the table: “Maximum,” “Minimum,” and “Average.”
  4. Use formulas and functions to fill in the averages, maximums, and minimums. Put the average for each student in his/her row. Put the maximum, minimum, and average for each test in that test’s column.
  5. Merge and center “Excel Exam Grades” over the top of the table.
  6. Autofit the column widths.
  7. Format the table in a style you like.
  8. In page setup, add a header that says “Prepared by [Your Name].”
  9. Add a footer with the date, time, and page number.
  10. Print out the table centered horizontally and vertically with the paper in portrait orientation.

 

 

 

 

 

 

Excel Exam Grades

ID

Student Name

Test 1

Test 2

Test 3

Average

201

Arthur

78

96

80

?

202

Bailey

71

89

80

?

203

Costa

67

79

80

?

204

Danvers

88

80

?

205

Ellis

90

70

73

?

206

Fields

76

90

90

?

207

Gonzalez

84

91

76

?

208

Holder

87

68

80

?

209

Ingersol

98

70

?

210

Jenkins

80

70

?

211

Krump

75

90

93

?

Maximum

?

?

?

?

Minimum

?

?

?

?

Average

?

?

?

?

 

Part Two

Add formulas and functions to your budget project. In particular, you may want to add sums.

 


Class 5

Tips

Making Charts

Use the Chart Wizard!

  1. Select the data you want included in the chart.
  2. Click on the Chart Wizard button on the standard toolbar. This will open the Chart Wizard dialog box.
  3. Choose the type of Chart you want. Click Next.
  4. Click on the Series tab at the top of the dialog box. Click on the button to the right of the Category (X) axis labels text box to select the labels from your table. Press Enter. Click Next.
  5. Select the Titles tab if necessary. Add titles and data labels. Click on the Data labels tab. Choose the option you want. Click on Next.
  6. Specify the location of your chart. Click on Finish.

 

Resizing and Moving Charts

  1. To resize a chart, click on its blank area. Then move to a corner handle so you get a black, double-headed arrow.
  2. Hold down Shift as you click and drag the chart bigger or smaller.
  3. To move a chart, click and drag on the chart itself.

 

Formatting Charts

  1. Open the Formatting Charts toolbar (through the View menu to Toolbars).
  2. Right click on the feature of the chart you want to change. Choose Format _____. Then use the dialog box to make it the way you want.
  3. Click OK when you’re finished.

 

Note: Click on Angle Text Downward or Upward buttons to slant the text.

Note 2: Use the Hide/Show buttons to hide or show the legend or the data table.

Modifying Charts

You can change chart type, size, location, and data after you’ve made a chart.

 

To change the type of chart, dlick on the chart. Then click on the drop-down arrow next to the Chart Type button. You’ll see the Chart Type palette. Choose the column chart option.

 

To move the Legend: Right click on the legend. Click on Format Legend. Click on Placement. Choose. Click OK.

 

Editing Chart Data

This is easy! If you data changes in your table, the chart automatically changes. Try it.

 

Placing and Modifying Data Tables in Charts

A data table is a table that is placed within a chart and includes only the data used in the chart.

  1. Click in the chart’s blank area. Then click on the data table button. You get a data table that’s too big!
  2. Right click on the data table. Choose a smaller font. Click OK.
  3. Click on the Hide Legend button to remove the legend from the chart.

 

Printing a Chart

To print a chart without other worksheet data, click on the chart. Print Preview the chart. Click Print.

 

To print a chart with the other data, click on the print preview button. Click on Setup. Click on landscape in the Orientation section and Fit to in the Scaling section. Then click on OK. Click on Print. Click OK.

 

 

Class 5

Homework

Part One

  1. Use the “Excel Exam Grades” table from Homework 4.
  2. If you haven’t done it already, format all the cells with numbers in them to be in the Number style with no decimal places.
  3. Use Functions to fill in the Maximum, Minimum, and Average for each test and for the overall Averages.
  4. Make a column chart of all the student grades. Format it anyway you want.

Hint: Select B3 through E13 before you click on the Chart Wizard button.

 

Which student(s) improved throughout the class? ____________________________

 

Which student got the highest test score? ___________________________________

 

Which student got the lowest test score? ____________________________________

 

Part Two

Add a chart to your budget. An especially useful chart for a budget may be a pie chart, so you can see what you spend the most money on.


 

Faith (for Content): 
Category: