Excel master soltions background
Adelaide Spreadsheet Help
Excel Master Solutions
Specialist Excel Consultants

Handy Excel Tips and Tricks

A selection of tips and tricks that I find useful and which may not be widely known. 

 Using arrow keys in chart wizard (and other) text fields 

Edit value series in chart
When you edit a range (for example a series range used in a chart) often you want to use the arrow keys to move through the text to get to the position you want to edit.
In the example on the right, we want to move the cursor left, from the end of the cell, to the ":" in front of $E$27.


 
Edit series in chart
If we press the left arrow key, Excel kindly inserts a cell reference at the current cursor position, rather than moving the cursor.
Solution:
To use the arrow keys to move through the text field, first press the F2 key.
The left and right arrow keys now move through the text field, not the spreadsheet cells.

Changing Cell Reference types

 By default Excel uses a relative reference when you enter a reference to a cell (ie A1) into a formula (ie. =sum(A1:A5)  )
If you want to change the formula so it retains its row/column reference when you fill down or across, then you need to change to an absolute reference.
Putting a $ sign in front of the row and/or column changes the row and/or column to an absolute reference.
(example =sum($A$1:$A$5)  ) 


 
Quick tip:
To quickly change cell and row references from relative to absolute, or a mix of the two, position the cursor in the formula bar on the cell reference to be changed and use the F4 key to cycle the reference type.
F4 cycles between:
  • A1 (relative column and relative row)
  • $A$1 (absolute column and absolute row)
  • A$1 (relative column and absolute row)
  • $A1 (absolute column and relative row)

Summarising data based on multiple criteria

Two relatively new functions in Excel are the "SUMIFS " and "COUNTIFS" functions.  (Excel 2010 and 2013)
These functions allow multiple critieria to be specified and the sum (or count) of cells that match the criteria can be calculated.
For example you may wish to sum (or count) the total sales made in 2015, by specific area, and by a product type. Therefore we have 3 different criteria - Year, Area and Product.
The SUMIFS function allows this to be done with a single function, as opposed to using CSE functions.

Quick tip:
Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Note: The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument

Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)