Computer Training

Creating a Basic Spreadsheet

Sorting Cells

1. Select the range of cells that requires sorting. Next, click on either the ascending order (A to Z) or descending order (Z to A) button located on the Standard Toolbar.
2. An alternative method is to select the range of cells and then click on the menu Data | Sort. Choose from the options provided.

Relative and Absolute Cell Referencing

Relative referencing refers to Excel recognizing that when you copy the same formula to a cell you want only the formula and not the same answer.

For example:

1. Enter the number (12) in cell A1 and A2, then press the Enter key.
2. In cell A3 type (=A1*A2), and then press the Enter key. Cell (A3) now displays the square root of 12 (144).
3. Now copy that formula to cell (B3) by selecting cell (A3) and then drag the AutoFill handle to cell (B3). Release the mouse button and press the Enter key or click outside the active cells.
   Note: Until data is entered in cells B1 and (B2), cell (B3) will display the number (0).
4. Enter the number (13) in cell B1 and the number (12) in cell (B2). The result in (B3) is now (156). Excel referenced only the formula and not the cells where the formula originated.

Absolute referencing refers to forcing Excel to reference a cell by a specific row, number, column letter, or both by typing a dollar sign in front of the column letter and /or row number.

For example:

1. In cell (A5) type (=$A$1*$B$1) and then press the Enter key. Cell (A5) displays the square root of 12 (144).
2. Select cell (A5) and then drag the fill handle to cell (B6). The formula remains absolute because of the dollar signs in the formula and therefore references cells (A1) and (B1).

If you only wanted the column references to remain absolute you would remove the $ from the row references.

For example:

1. In cell (A1), type the number (2).
2. In cell (A6), type the formula (=$A1*A$2). Using the same numbers from the prior example the answer is (24).
3. In cell (B2), type the number (3) and then use the fill handle to copy the formula from cell (A6) to (B6). The answer is (6).

Filling Text in Columns or Rows

Excel can repeatedly fill common text in a column or row. Examples are Days, Months, and Time.

1. Type the weekday Monday in cell (D3), and then press the Enter key. Reactivate the cell and then drag the AutoFill handle across a range of cells (multiple cells). Excel automatically adds the related column headings (Tuesday, Wednesday, Thursday, etc.) in the preceding columns or rows.
2. Excel can also AutoFill time. Type (12:00 a.m.) in the first cell (make sure there is a space between the last zero and a.m.) press the Enter key.
3. Next type the desired increment for time (12:30 a.m.) To continue the time entry select the two entries and then drag the AutoFill handle to the last cell that displays the end time.

Moving Around the Worksheet

1. To move to the top row in the sheet use the shortcut keys Ctrl+Home, or click once in the gray area directly below the up arrow in the vertical scroll bar.
2. To move to the bottom row in the sheet use the shortcut keys Ctrl+End, or click once in the gray area directly above the down arrow in the vertical scroll bar.

Clearing an Entire Worksheet

To clear an entire worksheet first select the worksheet by clicking on the square in the upper corner of the worksheet between column title (A) and row title (1). Under the menu Edit | Clear | All to remove all contents and formats.

Note: If the worksheet has been formatted - merged cells, numbers, or applied borders , press the Delete key to clear all contents. Pressing the Delete key does not remove any cell formatting.

Freezing Panes

1. To keep column and row labels visible click below the first column label and to the right of first row label.
2. Click on the menu Window | Freeze Panes, when you scroll up or down the column labels remain visible. Scroll to the left or right and the row labels remain visible.
3. To unfreeze the pane, click on the menu Window and then Unfreeze Panes.
   

Using the Drag and Drop Method to Move Data

For keyboard enthusiasts

1. Highlight a range of cells that contain data (B3:B14). Place the arrow on any border of the selected cells. Hold the Ctrl key down and a small thin black + sign displays above the mouse pointer.
2. Press and hold the Ctrl key and Left mouse button down and then drag the selected range to an open cell (F5). Release the Ctrl key followed by the Left mouse button.
   Note: If you release the mouse button before the Ctrl key a copy and paste is executed.
3. Alternatively, you could use the Cut and Paste tools to accomplish the same task. Highlight the desired range, use the keyboard shortcut Ctrl+X or click on the Cut icon (scissors) on the Standard Toolbar.
4. Click in the desired cell and then use the keyboard shortcut Ctrl+V or the Paste icon to move the data.
   

Search | Site Map | Ask Scranton | Choosing Scranton | My Scranton | Campus Contacts