Computer Training

Creating a Basic Spreadsheet


Changing Column Widths

  1. To adjust the width between two columns place the mouse pointer on the line between the column headers (E, F). When the pointer changes to a two-headed arrow double-click and Excel will automatically fit the column to the contents of the cell.
  2. An alternative method is to click and drag the two-headed arrow either left or right. Notice that a small window displays the column width as you move the column.

Inserting Rows or Columns

To insert a column or row:
  1. Right click in a cell to activate the context menu and choose Insert. Another menu follows asking if you would like to insert an entire row or column.
  2. Click on the menu Insert and choose either Rows or Columns. Excel will insert a new column to the left of the cell or a new row below the active cell .

To insert more than one column or one row:
  1. Click on a column or row heading, then drag the thick white plus sign over the desired number of column labels (A, B, C, etc.) or row labels (1, 2, 3, etc.) .
  2. Click on the menu Insert | Columns/Rows, and Excel will insert the exact number of columns/rows that were highlighted.

Formatting a Cell


Formatting a cell displays the contents in various formats for text, numbers, fonts, colors, and borders. Examples would include the number of decimal places in currency, text position, various fonts, and borders.


Brief Exercise
This exercise was designed to help apply the information learned from the preceding web pages. Remember to save your work often. Select a new worksheet before beginning the exercise.
  1. In cell (A1) type Loose Change Cafe and then press the Enter key.
  2. In cell (A2) type Week of (mm/dd/yy) to (mm/dd/yy) [add 6 days to the end date]. Press the Enter key.
  3. In cell (A3) type Daily Sales Revenue and then press the Enter key twice.
  4. In cell (A5) type Week Day and then press the Enter key.
  5. In cell (A6) type Cash Receipts and then press the Enter key four times.
  6. In cell (A10) type Total Revenue and then press the Enter key.
  7. In cell (A11) type Average Revenue and then press the Enter key.
  8. In cell (C5) type Monday and then press the Tab key. Select cell (C5) and then drag the fill handle across to cell (H5). Release the mouse button.
  9. In cell (C6) type the number (1200) and then press the Tab key or use the right keyboard arrow. In cell (C7) type the number (1500) and then press the Tab or Enter key.
    Notice that the text (Monday) is aligned left and that the (numbers) are aligned right.
  10. Select cell (C6) and drag the thick white plus sign to cell (C7). When the plus sign changes to a thin black + sign drag the fill handle to cell (H6) and release the mouse button. The cells are now incremented by the amount of (300) and remain highlighted.
  11. Right-click anywhere within the highlighted cells and the Format Cells dialog box will open. Choose the tab Number and the category Currency.
    Note: Inspect that the text boxes Decimal places and Symbol include the number (2) for decimal places and the ($) symbol. Click on the button OK.

Formatting Instructions for Cell A1
  1. Select cell (A1), drag the white plus sign to cell (H1), and then release the mouse button. Click on the Merge & Center button located on the Formatting Toolbar. From the same toolbar, select the color yellow for the background fill.
  2. Click on the drop down arrow next to the Border button and add an outside border (3rd row, 3rd button from the left).
  3. Click on the drop down arrow next to the Font size box an change the font size to (24). Repeat the same steps to change the font to AGaramond in the Font box and the style to Bold . Press the Enter key.
  4. Repeat the prior steps and format cell (A2), except this time change the font to size (14) Bold. Do not add a border, background color, or change the font.
  5. Repeat the same steps for cell (A3).
  6. Select the cell range (A5:H5) and change the font size to (11) Bold. Reselect the same range and add a border. Click on the Top & Bottom Border button (2nd row, 3rd from the left).
  7. Select the cell range (A6:H6) and change the font size to (11) Bold.
  8. Repeat the step (7) for cell range (A10:A11) and add a Thick Black Border (last row & box).
  9. Adjust the headings for column (A) and (B). Place the mouse pointer on the fine line between both column headings and then double-click the mouse button.
  10. Adjust the column width manually for column (B) to (9.43).
  11. AutoFit columns (C, D, E, F, G, and H) by holding down the Ctrl key while clicking on each column heading. Click on the menu Format | Columns and the choose AutoFit Selection. The selected columns are adjusted to their cell's contents.
  12. Select the cell range (C5:H5) and then center the headings.

Calculations

  1. Use the AutoSum feature for addition. To find the sum of a row or column first select a cell for the answer and then click on the AutoSum button located on the Standard Toolbar.
  2. If the range of cells automatically selected are not the desired cells select the new range now and then press the Enter key for the Sum.
To assign a formula: Either type an = sign in the desired cell or use the function box on the Formula Bar.
  1. Select cell (B10) and then the AutoSum button. Select the select cell range (C6:H6). Press the Enter key and your answer will be $11,700.00.
  2. Select cell (B11) and then click on the down arrow next to the AutoSum button. Select Average from the menu and then select cell range (C6:H6). Press the Enter key and the answer will be $1950.00.
  3. An alternative method would have been click inside cell (B11) and then click on Function Box icon to open the Insert Function dialog box. When the dialog box opens, you choose the function Average, which opens the Function Palette to the right.
  4. Collapse the dialog box by clicking on the small icon that resembles a spreadsheet. Next, drag the mouse arrow over the cell range (C6:H6). Press the OK button.
Multiplication
The asterisk is used for multiplication when referencing two or more cells.
For example:
  1. Type an equal sign (=) in the cell that will be used to display the product and then reference the cells by typing the cell references or use the mouse.
  2. To display the product of cells (C6) and (D6) in cell (E7) enter this following formula in cell (E7) =C6*D6.
Subtraction
Using the above method substitute the asterisk with a hyphen between the cell references.

Division
Using the above methods substitute the asterisk or hyphen with a Slash key (/) between the cell references.

Search | Site Map | Ask Scranton | Choosing Scranton | My Scranton | Campus Contacts
Disclaimer: The University of Scranton does not endorse views or opinions found on pages directly or indirectly accessed from our Web site.
© 2006 The University of Scranton Web site Powered by ActiveCampus™ Software by LiquidMatrix