Lesson 12 Spreadsheets

1. Lesson 12Spreadsheets Computer Concepts BASICS 4th Edition Wells

2. Objectives Understand the purpose and function of a spreadsheet Identify the parts of a spreadsheet window Enter labels, values, formulas, and functions in a spreadsheet Use the AutoSum feature to enter the SUM function Understand relative and absolute cell references Change column width and row height 2

3. Objectives (continued) Format data in a spreadsheet Insert and delete cells, rows, and columns. Save and print a spreadsheet Add headers and footers in a spreadsheet. Sort data in a spreadsheet Insert clip art in a spreadsheet Create a chart from spreadsheet data 3 3

4. Vocabulary absolute reference active cell cell cell reference chart footer formula formula prefix function header label order of evaluation 4

5. Vocabulary (continued) range relative cell reference spreadsheet theme value workbook worksheet 5 5

6. Spreadsheet Software Defined A spreadsheet is a row and column arrangement of data Electronic spreadsheet software, such as Microsoft Office Excel 2007, is used to evaluate, calculate, manipulate, analyze, and present numeric data Calculations are updated automatically, which makes this type of software very effective for numerous applications, such as preparing budgets, financial statements, and sales reports 6 6

7. The Anatomy of a Spreadsheet A spreadsheet looks like a grid of columns and rows. In Excel the grid is referred to as a worksheet. The terms spreadsheet and worksheet are used interchangeably. Columns are identified by letters and rows are identified by numbers. The point at which a column and a row intersect or meet is called a cell. Each cell has a name, called the cell reference (or cell address), which is represented by the column letter and the row number. 7 7

8. The Anatomy of a Spreadsheet (continued) The active cell is the cell in which a user is working currently and is surrounded by a thick border. By default, a workbook contains three worksheets named Sheet1, Sheet2, and Sheet3, as shown on the sheet tabs at the bottom of the window. 8 8

9. Selecting Cells To select a cell with the mouse, point to the cell and click. You can select a group of cells by clicking the first cell and dragging the mouse to the last cell of the group or by using keyboard shortcuts. When selecting a group of cells, the group is called a range. It is identified by the address of the cell in the upper-left corner, followed by a colon, and then the cell in the lower-right corner; for example, A1:D5 identifies all the cells from cell A1 through cell D5. 9 9

10. Selecting Cells (continued) You can select an entire column by clicking the column letter at the top of the spreadsheet or an entire row by clicking the row number. To select a cell that is not visible on the screen, you can use the vertical and horizontal scroll bars or scroll boxes to display the area of the spreadsheet that contains the cell(s) you want to select. 10 10

11. Entering Data in a Spreadsheet The data that is entered in a spreadsheet will be one of four types—a label, a value, a formula, or a function. A label is alphabetical text and aligns at the left side of the cell. It also can contain numerical data not used in calculations, such as zip codes, telephone numbers, dates, and so on. A value is a number and aligns at the right side of the cell. A formula is an equation that performs a calculation. A functionis a built-in formula that is a shortcut for common calculations, such as finding totals or averages. 11 11

12. Entering Data in a Spreadsheet (continued) When you press Enter, the next cell down in the column becomes the active cell. You can also press the Tab key to enter the data and move to the next cell in the row. You can edit data in a cell by selecting the cell and then typing the new data. You also can double-click the cell and then move the insertion point to where you want to edit the data. Use the Delete or Backspace keys or retype the data as desired. 12 12

13. Entering Formulas and Functions A formula performs calculations, such as adding, subtracting, averaging, and multiplying. To enter a formula in a cell, first type an equal sign (=). This symbol, called the formula prefix, identifies the data as a formula and not a label. For the formula, you can enter cell references, arithmetic operators, and/or functions. 13 13

14. Entering Formulas and Functions (continued) Formulas that contain more than one operator are called complex formulas. The order of evaluation determines the sequence of calculation. Multiplication and division are performed before addition and subtraction Calculations are performed from the left side of the formula to the right side Change the order of evaluation by using parentheses. Calculations enclosed in parentheses are performed first For more complex calculations, use a function which is a prewritten formula that automatically calculates a value based on data you insert. 14 14

15. Entering Formulas and Functions (continued) • Examples of order of evaluation 15

16. Entering Formulas and Functions (continued) To enter a function, type the equal sign (=), the name of the function (such as SUM), an opening parenthesis, the value(s), cell, or range of cells to be calculated in the function (referred to as the argument), and then the closing parenthesis. You can use the AutoSum feature, which is located in the Editing group on the Home tab, as a shortcut for entering the SUM function. To copy data, you can use the Copy and Paste commands, the drag-and-drop method, or the fill handle. 16 16

17. Entering Formulas and Functions (continued) Relative and Absolute Cell References: When you copy cells that contain formulas, the cell references change to accommodate the new location. This is called a relative cell reference. If you want the value of a cell referenced in a formula to remain constant when copied, then you need to make it an absolute cell reference. This means that the content of the cell will not change when copied to another cell. To create an absolute cell reference, type a $ before the column letter and a $ before the row number in the cell reference to remain the same. For example, $A$4 is an absolute cell reference for cell A4. 17 17

18. Formatting a Spreadsheet Changing Column Width and Row Height: The appearance of the spreadsheet is almost as important as the accuracy of the data that it contains. To adjust column width: Position the pointer on the vertical line between the column letters at the top of the worksheet. Click and drag to adjust width. You can also double-click to adjust the column width to its best fit. To adjust row height: Position the pointer on the line between the row numbers at the left of the worksheet. Click and drag to adjust height. You can also double-click to adjust the row height to its best fit. 18

19. Formatting a Spreadsheet (continued) Formatting Data: Following is a list of some of the formatting tools, located on the Home tab: Alignment: By default, text is aligned to the left in a cell and numeric data is aligned to the right. You can change alignment using the buttons in the Alignment group. Orientation and wrapping: Data in cells can be rotated or wrapped to fit the cell. Font: You can change the font, font size, font style, and font color of data just as you would in a word-processing document. Format Painter: The Format Painter provides a time-saving way to apply formats consistently throughout a worksheet. 19 19

20. Formatting a Spreadsheet (continued) To insert a cell, rows, or columns, click Insert in the Cells group on the Home tab. The Insert gallery is displayed with options to Insert Cells, Rows, Columns, and Sheets. You can delete cells, rows, and columns by selecting them and pressing the Delete key. You can format numbers to have a set number of decimal places, to have a comma, or to be displayed as a percentage. Some of the more commonly used formats can be applied using buttons in the Number group on the Home tab. 20 20

21. Formatting a Spreadsheet (continued) • Formatting numbers 21

22. Formatting a Spreadsheet (continued) Excel cell styles allow users to quickly apply professional-looking formatting to a cell or a group of cells. A style is a collection of formatting selections, such as cell color, font, and font size. To give a worksheet a more professional look, you can apply a theme. A themeis a predefined set of attributes, including fonts, colors, chart styles, cell styles, and fill effects. 22 22

23. Printing a Spreadsheet To preview a spreadsheet, click the Office button, point to Print, and then click Print Preview. In portrait orientation, the page is oriented toward the short side of the paper. In landscape orientation, the page is oriented toward the long side of the paper. You can define the part they want to print by selecting it first. To print the entire spreadsheet as is, click the Office button, point to Print, and then click Quick Print. To clear the selected Print area, click the Print Area button and then click Clear Print Area. 23 23

24. Working with Other Spreadsheet Tools You can make spreadsheets more useful and attractive by inserting headers, footers, and other objects such as clip art. You can also sort the data in a spreadsheet according to a specified column or columns. You also can hide data. Use headers and footers to place information at the top or bottom of the spreadsheet. If a spreadsheet is more than one page long, the header and footer information will appear on every page. 24 24

25. Working with Other Spreadsheet Tools (continued) Sorting Spreadsheet Data: Sorting is organizing or rearranging data in either ascending or descending order. When you sort data in ascending order, the alphabetic information is arranged in A to Z order and numeric information sorts from the lowest to the highest number. When you sort data in descending order, alphabetic information is sorted from Z to A and numbers from highest to lowest. You can sort data in a worksheet according to the data in one column or more than one column. 25 25

26. Working with Other Spreadsheet Tools (continued) Adding Objects to a Spreadsheet: You can add clip art, photos, and other objects to a spreadsheet to enhance its appearance. You can use tools in the Illustrations group on the Insert tab to add objects, such as Pictures, Clip Art, Shapes, and SmartArt graphics. SmartArt is a collection of professionally created diagrams. 26 26

27. Creating Charts A chart is a visual representation of spreadsheet data. The chart type selected will depend on the data to be represented. Chart added to worksheet 27 27

28. Summary • The primary use of Excel spreadsheets is to enter, calculate, manipulate, and analyze numbers. • Columns in spreadsheets are identified by letters, and rows are identified by numbers. • The point at which a row and a column intersect is a cell. • A cell that has been selected (highlighted or outlined with a black border) is referred to as the active cell. • A range of cells is a group of closely situated cells. 28

29. Summary (continued) • Alphabetic text information in cells is referred to as labels; numeric information in cells that can be calculated is referred to as values. • A formula is a type of data that performs a calculation. To enter a formula in a cell, you must first type an equal sign. • A function is a built-in formula that performs calculations ranging from simple to complex. • You can copy data by using the Copy and Paste commands or the fill handle. 29

30. Summary (continued) • A relative cell reference refers to cells that change when they are copied into other locations. An absolute cell reference refers to cells that do not change when they are copied into other locations. • The AutoSum feature enables you to quickly add a range of cells. • You can change the appearance of data by using a variety of formatting tools and options or by applying one of the Excel cell styles. • Selected data in a spreadsheet can be hidden so it will not be displayed or printed. • The contents of a spreadsheet can be displayed in chart format. • A chart displays the spreadsheet data visually so that data can be understood more easily. 30