ICAEW.com works better with JavaScript enabled.

Excel tips and tricks

Some of our top tricks for excel to help you ease in to work and save yourself some time.

Keyboard shortcuts

Arrow keys: Move through the workbook

  • Shift + Arrow keys: Select multiple cells
  • Ctrl + Arrow keys: Move in ‘jumps’ through a block of cells
  • Ctrl + Shift + Arrow keys: Select in ‘jumps’
  • Ctrl + PgUp/PgDn: Move between worksheets
  • Ctrl + C: Copy current selection
  • Ctrl + V: Paste most recently copied cells

Key formula glossary

= Used to indicate what is, and what is not, a formula
+  Addition
- Subtraction
* Multiplication
/ Division
A1

A cell reference to cell A1. Will alter when the formula is pasted elsewhere,
eg moving to B1 if the formula is pasted one cell rightwards.
$A$1 A cell reference to cell A1. Will not alter when the formula is pasted elsewhere.
A1:A100 A range of cells. Can only be used in a function. 
SUM(input) A function that adds up all the cells in the input – most commonly used with a range as the input. 
COUNT(input) A function that counts how many numbers are included in the input – usually a range.

Sort and Filter

You can add filters to a table as shown below. These filters can be used to rearrange the data into order, or to filter the data, hiding unwanted rows.

"

Pivot tables

"
A PivotTable is a tool for creating an automated summary of the original data that can be created from a table (similar to the one pictured to the left). Once a PivotTable has been created, a menu appears that can be used to design the summary. Fields (from the top box of the menu) represent columns in the original table. Any one of these fields can be dragged to one of the bottom menu boxes to include that field in the summary. If a particular item is not displaying in the way you want, right click and use this Settings menu to adjust.

Conditional formatting

"
This menu, from the Home menu, can be used to apply special formats to cells. For example, you can have Excel automatically highlight numbers over 1000, or highlight blank cells. This can make a spreadsheet easier to read and use. Use Manage Rules to see what rules exist/delete any unwanted CF rules.