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.)

 

 

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.

 

 

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.

 

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 f_x 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.

 

 

 

 

 

 

 

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.