Spreadsheet Introduction PDF

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 Figure 1.1 in the textbook illustrates elements specific to the Excel window. 3 Worksheet rows lie horizontally and are numbered from 1 to 1,048,576. Worksheet columns lie vertically and are labeled from A to Z. Successive groups of 26 columns are labeled AA to AZ, BA to BZ, etc. A cell is the intersection of a row and column. Each cell has a cell address made up from the column letter and row number. For example, the cell address of the top left cell is A1. 4 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. 5 Text is any combination of letters, numbers, symbols, and spaces not used in calculations. Special entries like phone numbers and Social Security numbers are considered to be text even though they contain digits. By default, text entries are left-aligned in a cell, although alignment options such as centering can be used. Multiple lines can be entered in the same cell by pressing the ALT+Enter key combination between the lines. 6 Values and dates represent a quantity upon which math calculations can be performed. Values and dates can be entered using common formats, including percent and dollar signs. Examples include 9/15/2012 or $50.00. By default, numeric entries are right-aligned in a cell, although other alignments such as center or left are possible. 7 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. 8 When formulas are entered in a worksheet, the result appears in the cell. The formula itself is displayed in the Formula Bar when the cell is selected. 9 The above table shows what mathematical keys to use when entering formulas in Excel. 10 It is best to use cell addresses in formulas rather than actual data. For example, if cell A1 contains the value 5, and you need to add B1 to this value, use =A1+B1 versus =5+B1 Remember, if the data changes, you want Excel to recalculate the answer. 11 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. 12 Table 1.3 lists the complete order of precedence. The order of precedence can be changed by using parentheses. 13 Figure 1.7 shows formula results based on the order of precedence. 14 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, right- clicking 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. 15 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. 16 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. 17 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. 18 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. 19 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. 20 Table 1.5 shows the four merge options. 21 See Table 1.6 for common numeric formats such as General, Number, Currency, Accounting, Comma, Date, and Time. Many numeric formats permit the user to specify the number of decimal places and options for negative values including parentheses and/or red color. 22 A header is content that appears at the top of each printed page. A footer is content that appears at the bottom of each printed page. The Header/Footer tab of the Page Setup command also offers standard options, such as page number, as well as options for customization. 23 Table 1.8 shows Header and Footer Element options. 24