KCL • CCH • Minor programme • AV1000 • Numerical and graphical analysis

AV1000
Fundamentals of the digital humanities
Basic operations

  1. Scrolling
  2. Selecting cells
  3. Entering data
  4. Formatting numbers and text
  5. Aligning text
  6. Formulas
  7. Editing cells
  8. Sorting data
  9. Filling adjacent cells
  10. Copying, inserting, deleting, moving
  11. Referencing
  12. Importing data

I. Scrolling a worksheet

  1. At any one time, the Excel window may display only part of your worksheet. To display rows and columns that are not currently in view, you can scroll the worksheet within the window both vertically and horizontally. Either use the scroll bars at the right and bottom of the screen (as in Word), or use the arrow keys.
  2. To change the size of the Excel window, and so display more of the worksheet, use the size box in the lower right-hand corner of the window (Mac version) or any edge of the window or the corners (PC version), just as in Word.
  3. Zoom control boxNote also that the zoom control box on the toolbar (shown at right) can be used to adjust magnification of the sheet.

II. Selecting cells

Before carrying out any operation in Excel you usually need to select the cell or range of cells on which you wish to operate.

  1. To select a single cell, simply point at it and click with the mouse, or use the arrow keys. The selected cell is known as the active cell, its reference is displayed in the name box. The contents of the active cell is displayed in the formula bar.
  2. To select an entire column, click on the column letter at the top of the sheet (where you find the column designators A, B, C, etc.
  3. To select a row, click on the row number at the left of the sheet.
  4. To select the entire sheet, click on the unlabelled box at the intersection of the column designators and row numbers in the upper left-hand corner of the sheet.
  5. To select a range of cells, click on the first cell in the range then drag to the last cell of the range.

III. Entering data

  1. To enter text:
    1. select the cell, row, column or range
    2. type the text for the first cell
    3. press Enter (or Return)
    4. type the text for the next cell, etc.

    To skip a cell in a range, press Enter, or click on the Enter box in the formula bar. To cancel an entry before you have pressed Enter, click on the Cancel box in the formula bar, or if you have already pressed Enter, choose the Undo Entry command on the Edit menu. Alternatively, reselect the cell and press Delete.

  2. To enter numbers (as numbers):
    1. select the cell, row, column or range
    2. type in the number, press Enter
    3. continue, as above.
  3. To enter numbers as text:
    1. begin each entry of a number with a single quote
    2. type in the text, e.g. '143

Note that you can use the Tab key instead of Enter if you wish to move from left to right, rather than from top to bottom.

IV. Formatting numbers and text

  1. Format Cells dialogue: Number tab

    For numbers a wide range of options are available under the Cells option of the Format menu, including General, Percentage, Date, Time, Currency, Fractions. To select a number format,

    1. click on Cells in the Format menu
    2. click on the Number tab, as shown here
    3. click on the type of format from the list
    4. if a specific format appears to the right of the list, choose a specific subtype, or enter the desired format in the Code box
    5. click on OK.
  2. For text a variety of formatting options are also available. These may be exercised from the Formatting toolbar (see Toolbars under the View menu) or from the Cells option under the Format menu, as in the image at right.

V. Aligning text

As a default, text is aligned along the bottom edge of the cell against the left-hand border, numbers against the right-hand border. To change the alignment,

Format Cells dialogue: Alignment tab
  1. Select a cell range, then use the options on the Formatting toolbar for centre, right, left, or (right-) justified alignment, or to centre over several columns.
  2. Alternatively, select a cell range, click on Cells in the Format menu (which will cause the dialogue box shown at right to appear), then on the Alignment tab, then on the desired entry in the list, then on OK. Note the option for causing the text to wrap. Note that text may be aligned incrementally, by degrees, using either the graphical orientation gauge or the numerical box below it.

VI. Formulas

Formulas are to the spreadsheet what a macro is to a wordprocessor, i.e. a defined series of numerical, logical, date, database or other operations that may be invoked by entering the corresponding formula into a cell. This formula refers to other cells, performs the operation(s) on them, and displays the result. Excel and other commercial spreadsheet programs offer a very wide range of simple to complex formulas. For our purposes here, only the simplest ones are used.

There are two main ways of entering a formula: direct typing into a cell or use of the Paste Function button. In addition, the SUM function has a button on the toolbar.

  1. Direct typing: select the cell, then type in the formula, e.g. =SUM(C4:C6). Note that an equal-sign (=) must precede all formulas and that the range of cells is indicated by the initial cell ID followed by a colon (:) followed by the terminal cell ID.
  2. Paste Function:
    1. click on the Paste Function button: Paste Function button
    2. Paste Function dialogueclick on the function category in the left-hand list of the dialogue (see right)
    3. click on a function name in the right-hand list
    4. click on Next to move to the following dialogue box
    5. fill in the boxes as needed by the function you have invoked
    6. click on the OK button or press Enter.
  3. Autosum. To invoke the sum function most easily on a range of cells, click the Autosum button on the toolbar (labeled with a Σ), adjust the range of cells, and press Enter to accept.

VII. Editing cells

  1. To replace the current contents of a cell, select it, type in the new value, press Enter.
  2. To modify the current contents of a cell, double-click the cell to edit within it; use the cut, copy, paste commands (or the corresponding buttons on the toolbar) as needed; press Enter. Note that once you are editing within the cell, the cursor keys move you from character to character within the cell, not between cells.
  3. To remove the current contents, select the cell or range, click on Clear in the Edit menu, then on All, Formats, or Contents, then click on OK. Clear Formats removes the current formatting but retains any formulas and values; Clear Contents removes formulas and contents but retains formatting; Clear All removes everything.
  4. Undo and Redo buttonsTo undo your last action, click on the Undo function in the Edit menu, or the Undo button in the toolbar. Note also the Redo button.

VIII. Sorting data

  1. Select the range that you wish to sort.
  2. Sort dialogueSelect Sort from the Data menu.
  3. Choose whether to sort rows or columns: click on the Options button in the Sort dialogue box; select Sort Top to Bottom in order to sort rows (the default) or Sort Left to Right for columns.
  4. Three criteria may determine the sort-order, and this order may be ascending or descending. Choose the appropriate criteria, then click on OK.

You are well advised to save your worksheet before sorting, since an error in specifying the range can render your data useless. Remember that to select the entire sheet, you need only select any single cell in the sheet.


IX. Filling adjacent cells

Filling is to the spreadsheet what cut-and-paste (or copying) is to the wordprocessor, but it is more constrained because of the rigid structure of the sheet.

  1. To copy cell contents most easily, select a cell or range of cells, then use the File Handle of the selection, at its lower right-hand corner, to propagate the contents across a range by dragging and dropping.
  2. Alternatively, to copy formulas across the worksheet, select the range that begins with the source cell(s) and that includes the destination cell(s), click on Fill in the Edit menu, then click on Right. To copy down the worksheet, click on Down.
  3. To create a series (of dates, numbers, or text mixed with numbers), enter two examples from the series in two contiguous cells, select these, then drag the File Handle across the range you wish to fill.

Note well that spreadsheets use Relative References by default when copying formulas. This means that when e.g. =SUM(B5:B10) in cell B11 is copied to cell C11, it will read =SUM(C5:C10). Remember that if you wish to have Absolute References you will need to specify them explicitly in the formula. See the section on Referencing for more information.

X. Copying, inserting, deleting, moving

  1. To move or copy a range of cells:
    1. select the cell(s) to be moved or copied (see above, on Selecting cells, for a review)
    2. click on Cut or Copy in the Edit menu, or click on the Cut or Copy button on the Standard toolbar
    3. select the destination area.

    4. click on Paste in the Edit menu or on the Standard toolbar.

      Note that Paste Special on the Edit menu offers a number of options for selective pasting, e.g. only the formula, the format or the value.

  2. To insert a row or column:
    1. select the row or column positioned before the row or column that you wish to create
    2. click on Columns or Rows in the Insert menu
  3. To delete a row or column:
    1. select the row or column,
    2. click on Delete in the Edit menu

XI. Referencing

As noted above, cells are referenced by specifying first the column letter and row number, e.g. D6, H78. These are relative references. Because of the way formulas are handled by spreadsheet software, however, a refinement must sometimes be made. This refinement is called the Absolute Reference.

In an Absolute Reference, there are three possibilities:

  1. $column-number row-number. The column is fixed but the row remains variable, so that wherever the formula is copied, the column will be the same and only the row will change when it needs to. Thus, for example, the reference $B23 copied to the cell one column to the right and one column down becomes $B24.
  2. column-number $row-number. The opposite of the above: column is variable, row fixed. Thus, for example, the reference B$23 copied to the cell one column to the right and one column down becomes C$23.
  3. $column-number $row-number. Neither column nor row change wherever the formula is copied.

Typically an absolute reference is used to refer to a calculated value, located in one particular cell, from various other points in the sheet. When you use a relative reference, the formula is treated as being relative to the cell in which it appears; so that a formula in B3 that refers to A3 gets changed when copied to B4 so that it refers to A4. The reason is that it's very common to set up a whole column of formulas each performing some calculation on the values from each row in turn, and rather than require you to retype the formula with the new row number you can just copy one formula. But if the calculation involves some value that comes from the same cell every time, rather than one from the current row, then you need an absolute reference to refer specifically to that cell.

Both kinds of reference are automatically updated by Excel if you insert new rows or columns that shift the formulas and the cells they refer to.

Formulas often refer to ranges of cells; you do this by typing the address of the top-left cell of the range, then a colon, and then the address of the bottom-right cell. So B3:B23 refers to the range of cells in column B from rows 3 through 23.

XII. Importing data

Some of the time the data you may wish to have in a spreadsheet is available as the output of another program or is otherwise provided in electronic form. If it is already formatted as a table, with the "columns" marked either by a fixed-width arrangement or by a standard delimiter (tab, comma, semicolon or other consistent and unique marker), then you can import it into Excel directly and thus avoid tedious retyping. The air-pollution data provided by the National Air Quality Information Archive (U.K.), for example, comes in "comma-delimited" format and so can be imported quite easily.

Take, for example, the Tower Hamlets Roadside Nitrogen Dioxide in parts-per-billion (ppb) for 1997-8. (Look at the page now, by clicking here.) Note the neat array of numbers separated by commas. This is clearly a file designed for importing into a spreadsheet.

To import the data, follow these steps:

  1. Using the standard copy-and-paste technique or Select All under the Edit menu of Mozilla, select the data on the page for Tower Hamlets just referenced.
  2. Run a text-editor, such as Notepad.
  3. Paste the data just copied into a new document, then save the document as a text file.
  4. Run Excel.
  5. Choose Open under the File menu.
  6. For Files of type: select All Files (*.*).
  7. Find the file you just created, then open it. This will cause the Text Import Wizard (Step 1 of 3) dialogue box to appear, as shown below. Text Import Wizard step 1
  8. Choose the Delimited option, then click Next >.
  9. In the second Text Import Wizard dialogue box, choose the Comma option for the delimiter. You should then see the data correctly parsed, as shown here. Click on Next >.Text Import Wizard step 2
  10. Finally, since the first column expresses date, identify the data there as type Date, leave the rest as is, and click on Finish.Text Import Wizard step 3
  11. You will then see the data in spreadsheet form. You should then save it as type Microsoft Excel Workbook (*.xls) so that you do not have to import the data as text a second time.

revised January 2008