Full Transcript

A spreadsheet is an electronic file that contains a grid of columns and rows used to organize related data and perform calculations. Excel is a spreadsheet program used to create and modify electronic spreadsheets. Before the existence of spreadsheet programs like Excel, people handwrote figures in...

A spreadsheet is an electronic file that contains a grid of columns and rows used to organize related data and perform calculations. Excel is a spreadsheet program used to create and modify electronic spreadsheets. Before the existence of spreadsheet programs like Excel, people handwrote figures in paper ledgers to track quantitative data. An electronic spreadsheet makes data-entry changes easy, and if the formulas are correctly constructed, the results recalculate automatically and accurately. 1 A worksheet is a single spreadsheet that typically contains descriptive labels, numeric values, formulas, functions, and graphical representations of data. A workbook is a collection of one or more related worksheets contained within a single file. Each worksheet is identified by a sheet tab. By default, new workbooks have one worksheet. 2 It is important to plan the structure of a worksheet prior to entering data. First, state the purpose of the worksheet. For example, the purpose might be to compute the cost of a number of products given their quantity and unit cost. Decide what input values are needed by establishing the input area. The input area is a range, or group, of cells. Decide what output values are needed where the output area is also a range of cells. Assign the inputs and results into rows and columns. For example, you may choose to enter each product in a row where the quantity and unit cost values are in columns. The result might also be placed in a column. 3 Formulas are combinations of cell addresses, math operators, values and/or functions used in calculations. A function is a built-in formula, including SUM and AVERAGE functions. Excel designates a formula with a preceding equal sign (=). One example of formula is =A1+A2 which adds the values in cells A1 and A2 and displays the sum. A second example of a formula is =C2*5 which multiplies the value in cell C2 by 5 and displays the product. 4 Order of precedence, also called order of operations, controls the sequence in which math operations are computed. Parentheses are done prior to exponentiation. This would be followed by multiplication and division, and finally addition and subtraction. Multiple operators of the same precedence are evaluated from left to right. 5 Inserting a worksheet in the workbook can be done at any time. The worksheet can be added by clicking the New sheet tab, clicking the Insert arrow on the HOME tab, rightclicking any sheet tab, or by pressing Shift+F11. Deleting a worksheet will eliminate extra data and reduce the file size. Deleting a worksheet can be done by clicking the Delete arrow on the HOME tab or right-clicking any sheet. If the sheet contains data, Excel will display a warning; if there is no data, Excel will not display a warning. 6 Figure 1.18 shows a Move or Copy dialog box. Moving a worksheet changes its order among sheet tabs. The sheet can be also be dragged to its new location. Copying a worksheet makes a duplicate sheet. The sheet can also be dragged while holding the Ctrl key. 7 Figure 1.21 shows changing the column width. The column width is the horizontal measurement of a column. A column should be widened to fully display all data. If it is too narrow, text entries may appear truncated and numeric entries will display the error message #####. To enlarge a column to accommodate the largest entry, double-click the column border to widen the column to the right. To manually alter column width, drag the column divider on the right side of the column. Alternatively, select the Column Width or AutoFit Column Width options on the Format menu. 8 The row height is the vertical measurement of a row. It is a value between 0 and 409 based on point size, where one point equals 1/72 of an inch. The row height is automatically adjusted if you increase the font size of the cell content. If you use Alt+Enter to create multiple lines, this may require a row height adjustment. Manually adjust row height using the same techniques as for column width. You can drag the row divider or double-click the divider for a best fit. Alternatively, select the Row Height or AutoFit Row Height options on the Format Cells menu. 9 Figure 1.22 shows a hidden column and a hidden row. Hiding a column or row prevents it from displaying and printing. Use the Format command to hide or unhide the selected range. Remember, the values in hidden columns and rows may be used in calculations. Unhiding a column or row returns it to view. Select the columns or rows surrounding the hidden area and issue the Format command. 10 Figure 1.29 shows sample ranges. A range is a rectangular group of cells. The easiest way to select a range is to drag from the upper left cell to the lower right cell. A nonadjacent range contains a group of ranges that are not next to each other. To create a nonadjacent range, select the first range, hold down the Ctrl key, and select the second range. Continue holding the Ctrl key to add more ranges to the group. A range is selected to perform group formatting or to manipulate the group of cells as a unit with respect to moving, copying, or deletion. 11