Excel Master Solutions

Specialist Excel Consultants

Specialist Excel Consultants

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

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.

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.

If we press the left arrow key, Excel kindly inserts a cell reference at the current cursor position, rather than moving the cursor.

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.

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) )

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) )

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)

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.

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.

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]…)