Excel Chapter 1 – Introduction to Excel
Concepts – At a Glance Summary
KeyConcepts(blue) – most important concepts in this chapter
Tips(red) –useful shortcuts and information for more productive use of Excel
StickyPoints (green)– areas that might cause difficulty for students
Spreadsheet–the computerized equivalent of a ledger.
Grid of rows and columns
Used to organize data, recalculate results automatically, and make decisions
Excel is an example of a spreadsheet program
Worksheet–a single spreadsheet
Contains descriptive labels, numeric values, formulas, functions, and graphics
Aworkbookis a collection of related worksheets
Active cell–cell you are working in, where data will be input.
Parts of the Excel Window
Ribbon--made up of tabs, groups, and commands.
Tab--each tab is made up of several groups so that you can see all of its functionswithout opening menus.
Office Menu--displays when you click the Office button. Contains the commands New, Open, Save, Save As, Finish, Share, Print, and Close.
Formula Bar -- appears below the Ribbon and above the workbook screen.
NOTE: Shows the active cell’s contents; displays the contentsof cells; used to enter or edit cell contents
Name Box–contains the cell reference of the cell currentlyused in the worksheet.
NOTE: Appears to the left of the Formula Bar. Displays the active cell’s address or a name it has been assigned.
Sheet Tabs--located at the bottom left of the Excel window
TIP: Tellsthe user what sheets of a workbook are available. Three sheet tabs, initially namedSheet1, Sheet2, and Sheet3, included when a new workbook opened. Click on the sheet you want to work with.
Rename sheets by double-clicking its tab, type the new name, and press Enter.
Status Bar--Located at the bottom of the Excel window; displays information abouta selected command or operation in progress.
Select All Button--the square at the intersection ofthe rows and column headings; used to select all elements of theworksheet.
Cell–intersection of a column and row.
TIP: Cell references: columns are labeled with letters; rows labeled with numbers. Cells always referred by column letter first, then row number
Types of data you can enter into a cell:
- Text–letters, numbers, symbols, and spaces
- Value–numbers that are intended to be used in calculations
- Formula--combination of constants, cellreferences, arithmetic operations, and/or functions displayed in a calculation.
NOTE: Formulas always begin with an =
Editcell contents using one of three methods:
- Select cell, click in the Formula Bar, make changes and press Enter.
- Double-click cell, make edits, and press Enter.
- Select cell, press F2, and make edits.
Save As is used when the file needs to be named, renamed, or save in a different location.
Saveis used when the file needs to replace its previous version.
Tip – Office button in Open or Save As dialog box can be used to perform basicfile management
Arithmetic Operators–used to perform mathematical functions in formulas include addition, subtraction, multiplication, division
Order of Precedence--rules that control the order or sequence in which arithmetic operations are performed.
- Calculate left to right
- Order: parentheses, exponentiation, multiplication or division, and finally additionor subtraction.
Insert rows–rows are inserted above the selected row, by default.
Insert columns–columns are inserted to the left of the selected column, by default.
Tip – youcan also insert individual cells.
Sticky Point –when you insert or delete rows and columns, cell references are adjusted to reflect the addition or deletion
Range–a rectangular group of one or more cells, designated by the diagonally opposite corners, typically the upper-left and lower-right corners. Can be contiguous or non-contiguous
Move--transfers cell content from one location to another.
Delete--removes all cell content.
Copy–copies cell content to the Clipboard.
Paste–places content of Clipboard into selected cell.
Paste Special--allows users several different options when pasting material.
Tip–activate Shortcut menu by right-clicking mouse
AutoFill--enables users to copy the content by dragging the fill handle (a small black square appearing in thebottom-right corner of a cell) over an adjacent cell or range of cells.
Sticky Point –as you move the mouse around the cell, the cursor assumes different shapes. Make sure students recognize the shape of the fill handle.
Merge and Center–merges cells into one and centers the content of the merged cell.
AutoFit–automaticallyadjusts the height and width of cells.
Format Cells dialog box--controls the formatting for numbers, alignment, fonts,borders, colors, and patterns.
Page Setup and Printing–allows you to define options for printing and send one or more worksheets to a printer.
- Portrait orientation-- prints vertically down the page.
- Landscape orientation--prints horizontally across the page.
- Margins tab–use to control margins or center worksheet on page
- Headers and Footers tab–define text that will print on the top or bottom of each page
- Sheets tab–allows you to set other options for printing
Comments--provide documentationto others who may view the file
****************************************************************************
Connectionsto learning– PracticalProjects for the Student Excel User
- Track income and expenses more easily and with less chance of error (Spreadsheet)
- Chart data to study effects of phenomena such as earthquakes or voting results (Chart Wizard)
- Perform a what-if analysis for decision-making; grade recordkeeping (Mathematical formulas)
- Create an electronic file cabinet of information (Workbooks/Worksheets)
REAL WORLD CONNECTIONS FOR YOUR STUDENTS
In the following demonstration, students learn the basics of Excel. They will need to learn how to manipulate data in almost every aspect of their personal lives─to manage their budget, to keep a current address bookwith birthdays, to track expenses for their taxes.
In the real world, Excel is a necessity in every occupation. If you are an entrepreneur, you need to track your business expense and employees; if you are a photographer, you need to track clients’ work and choices; if you sell cosmetics (such as Mary Kay), you need to keep track of your customers’ changing preferences. If you are in any corporate environment, you may need to chart your division’s growth and manage expectations. If you are an environmentalist, you may, for example, need to track water pollution levels for a government report.
Case Study Lecture Demonstration Document:
Weddings by Grace
1. Open the student file.
2. Save the file.
3. Write a formula.
STEPS:
To write a formula to total cells, click in D9 to make it the active cell. Type =D6+D7+D8. Click the Enter button (Check Mark icon) on the Formula bar
TIP: The total (2050) displays in the cell, but also point out the formula in the formula bar that is producing the results.If students pressed Enter on the keyboard rather than the Enter button, then the cell below became the active cell and no formula displays in the formula bar. Have students move back up to D9.
NOTE: Rather than show the SUM function first, this walk through shows students how to create formulas from scratch so that they understand what is going on and how Excel works. You might want to discuss the difference between adding the numbers on a calculator and using cell locations in formulas, which shows the power of an electronic worksheet and can excite students who see the potential.
To copy the formula, with D9 as the active cell, point to the bottom right corner of the cell to display the small black cross cursor (the Fill handle). Click and drag the small black cross cursor (the Fill handle) over through F9. When students lift up on the mouse button, the results display in the cells.
TIP:This is a good time to discuss relative formulas and how they adjust to the correct location. Have students make E9 the active cell and notice how the formula adjusted to the correct location and then look at F9.
To write a formula to calculate the difference, click in F6 to make it the active cell. Type =D6-E6 Click the Enter button on the Formula bar.
TIP: This is a good time to explain that all formulas and functions begin with the = to alert Excel that what follows is a calculation. This formula subtracts the two cells to show the difference, so students must write the simple subtraction formula.
To copy the formula, with F6 as the active cell, point to the bottom right corner of the cell to display the small black cross cursor (the Fill handle). Click and drag the Fill handle down through cell F25. When students lift up on the mouse button, the results display in the cells.
NOTE: The formula is copied to all the selected cells in the column adjusting to every location. Notice that because some of the cells have no information to reference, the formula displays zeros, which are deleted in the next step.
TIP:You might want to point out the difference between the Fill handle and the Selection tool and have students play with getting both. They can avoid many errors if they understand the difference between the two cursors.
4. Save the file, this time with the same name.
STEPS:
To save the file, click the Office Button. Click Save. The workbook is saved with all the changes you have made under the same name: chap1_case_wedding_solution. The workbook is saved with .xlsx extension.
TIP: Students should save the file after every step because this is a long walk through. That way if they make an error or get lost, they can repeat the last step rather than starting from the beginning.
5. Clear cells.
6. Format numbers.
STEPS:
To select the cells to format, click in D6 to make it the active cell. Point to the middle of D6 to get the white cross cursor (the Selection tool) and click and drag over through column F and down through row 25 to select the range D6 to F25.
NOTE: The selected range displays with a black box around it.If some students used the small black cross cursor (the Fill handle), then they copied the contents of D6 to the entire worksheet. Click Undo and repeat the process using the Selection tool.
To format numbers in a worksheet, click Accounting Number Format in the Number group of the Home tab. Click outside the selected area to remove the selection.
CAUTION: Some of the cells display ##### because they are not wide enough. You might want to explain that Excel displays this code to alert the user to widen the cell.
7. Widen columns.
STEPS:
To widen columns, point in the column heading between column F and G until the 2-headed sizing arrow displays. Click and drag column F to the right to widen the column. The correct numbers replace the ### in the widened cells.
TIP: You might also mention that students can double-click the 2-headed sizing arrow in the same location of the column heading rather than drag to the right, and Excel automatically sizes the column.
8. Fill cells to create borders.
STEPS:
To use color to fill cells, click in C10 to make it the active cell. Click and drag the Selection tool over through F10 to select the range C10 to F10. Click the drop-down arrow on the Fill Color command in the Font group of the Home tab. Click Accent 6, Tint 60%.
TIP: Once a color is selected, Excel keeps it as the active color, so to repeat the process, simply click the Fill Color button.
- Click in C12 to make it the active cell. Click and drag the Selection tool over through F12 to select the range C12 to F12. Click the Fill Color command in the Font group of the Home tab to select the active color Accent 6, Tint 60%. Click in C24 to make it the active cell
- Click and drag the Selection tool over through F24 to select the range C24 to F24. Click the Fill Color command in the Font group of the Home tab to select the active color Accent 6, Tint 60%.
9. Merge and center cells.
STEPS:
To merge and center a title over columns, click in A1 to make it the active cell. With the Selection tool, click and drag over through column F, so that you have selected A1 through F1. Click Merge and Center in the Alignment group of the Home tab. Click in A2 to make it the active cell. With the Selection tool, click and drag over through column F, so that you have selected A2 through F2. Click Merge and Center in the Alignment group of the Home tab.
TIP: If students need to edit a merged cell at a later time, they need to make the first cell (A1 in this example) the active cell.
10. Format text.
STEPS:
To format text, click the cell A1 to make it the active cell Drag the selection tool down one cell so that the two merged cells are selected. To fill the cell, click the Fill Color command in the Font group of the Home tab to select the active color Accent 6, Tint 60%. To change the font color, with the same cells selected, click the Font Color drop-down arrow in the Font group of the Home tab. Click Accent 6, Shade 50%.in the resulting box. To bold the text, with the same cells selected, click Bold in the Font group of the Home tab. To change the font size, with the same cells selected, click the Font Size drop-down arrow in the Font group of the Home tab. Click 16 in the resulting box. Click outside the selected cells to remove the selection and see the results.
11. Add clip art.
STEPS:
To add clip art, click in A7 to make it the active cell. Click the Insert tab. Click Clip Art in the Illustrations group. Type wedding in the Search for box in the Clip Art pane. Click the Go button. When Excel displays wedding images, select one and double-click it.
TIP:
The image displays in the worksheet and should fit in the blank area; if not, you can size it using the sizing handles.
12. Emphasize category headings.
STEPS:
To format text, click the cell C4 to make it the active cell. Using the Selection tool, drag down one row and then to the right through F select the range C4 to F5. Click the Home tab. Click Font Color in the Font group to select the active color Accent 6, Shade 50%.
TIP:
Once a color is selected, Excel keeps it as the active color, so to repeat the process, simply click the Font Color button.
Click Bold in the Font group of the Home tab. Click in C11 to make it the active cell. Click Font Color in the Font group to select the active color Accent 6, Shade 50%.. Click Bold in the Font group of the Home tab. Click in F25 to make it the active cell. Click Bold in the Font group of the Home tab.
13. Add your name in a cell
14. Save the workbook.
15. Preview and print the worksheet and exit Excel.