X Close

Digital Education team blog

Home

Ideas and reflections from UCL's Digital Education team

Menu

Excel Tips and Tricks

By Caroline Norris, on 8 June 2017

The following are accompanying notes for an InEDITA lunchtime session on Excel.

Sharing spreadsheets

Make sure shared spreadsheets are as self-explanatory as possible.  Clearly label with appropriate row and column headings.  Add additional text either in cells or as comments.  Set up the spreadsheet so it is easy to view on screen and print:

  • Freeze panes (View tab) – column and row headings remain visible as you scroll a large sheet
  • Orientation (Page Layout tab) – set to landscape or portrait according to the shape of your data
  • Page Break Preview (View tab) – insert or drag page breaks to appropriate places
  • Print Titles (Page Layout tab) –  select rows/columns to repeat on every page
  • Fit to (File > Print > Page Setup) – scale your data to fit to a certain number of pages

Naming cells
Name a cell containing a fixed value (e.g. rate of VAT) and then use this name rather than the cell reference in your formulas.  This can reduce errors and make your formulas easier to understand.

  • Define Name  (Formulas tab) – to name the selected cell or type a name in the Name Box (where the cell reference normally appears)
  • Name Manager (Formulas tab) – to view, edit or delete names in a workbook.
    Note: if you delete a name already referenced in formulas you will break them!

Working with datasets

A dataset is a block of related values (text, numbers, dates).  A good dataset will have no completely blank rows or columns but leave a blank line between the dataset and any formulas (e.g. a column/row with totals) and include the blank column/row in the formulas.  This makes it easier to extend the dataset later.

Rearrange data method 1

  1. Select the columns to be moved and hover your mouse over the right hand edge of your selection (note the cursor changes to four arrows).
  2. Hold down the Shift key and then click and drag to the new position (a solid black line will indicate where your text will move to).

To move rows follow the same process but hover your mouse over the bottom edge of your selection.

Rearrange data method 2

  1. select the columns to be moved and Cut them
  2. right-click on the column to the right of where you want them to go
  3. select Insert Cut Cells

To move rows follow the same process but right-click on the row below where you want them to go.
Tip: a similar process can be used to Copy cells

Keyboard shortcuts to select cell ranges

  • Ctrl + * to select the current range (stops at first blank row or column)
  • Ctrl + Shift + arrow keys to select adjacent data

Selecting visible cells
If you select a range of cells, hidden or filtered cells are usually included in the selection.  To select only the visible cells:

  1. Select the data you want to copy
  2. Press ALT + ; (this changes the selection to only include visible cells)

Analysing selected data ‘on the fly’

  • Status bar (green strip at the bottom of the sheet) provides SUM, AVERAGE and COUNT information for selected cells (non-numeric data is ignored)
  • Quick Analysis (icon appears in bottom right of selected range) to quickly apply formatting, add formulas, create charts etc.

Cleaning up your data

The following features can help to find and remove errors, duplicate values and erroneous spaces in your data:

  • Filter (Data tab) – apply Filter and then click on the drop-down arrow to see unique values in that column
    Misspelt entries are easier to spot alongside correctly spelt versions. Note that leading or trailing spaces are ignored.
  • Conditional Formatting > Highlight Cells Rules > Duplicate Values (Home tab) – to highlight duplicates or unique values within a selected range
  • Text to columns (Data tab) – splits column into two or more columns (e.g. when column contains first name and surname)
    Specify that the data is Delimited and that the Delimiter is a space

TRIM function – removes all leading and trailing spaces and any extra spaces between words (leaves one space between each word)

  1. Insert a new blank column 2 to the right of column 1 (that you want to trim)
  2. Fill column 2 with the formula =TRIM( ) referencing the adjacent cells in column 1 in the brackets
  3. Copy column 2 and use Paste Values to replace the data in column 1
  4. Delete column 2

Customising Excel

Add frequently used (or difficult to find) commands to your Ribbon or Quick Launch toolbar.

  • Right-click on a command on the ribbon and select Add to Quick Access Toolbar
  • File > Options > Customise Ribbon – to add new commands
    You need to create your own custom Group on a Tab first.
    Tip: Make sure you know the exact name of the command. Under Choose commands from: select All commands and then find it alphabetically.

Leave a Reply