Sum Function Range

The SUM function can handle more than one range eg

Instead of

=SUM(A1:A100)+SUM(C1:C100)

Use

=SUM(A1:A100,C1:C100)

will total both ranges.

Zoom Percentage Shortcut

To zoom into the currently selected range use
Alt W G
Pressed in sequence, not held down.
To return to 100% zoom use
Alt W J

September 2014 Excel Yourself article

Check out my September 2014 Excel Yourself article, video and companion file are now online at the magazine site. The article covers converting a badly laid out report into a structured data layout using a formula.
The video can be viewed from the media section at the bottom of the page.

Click here to view the article.

Page Breaks

To insert a page break at the current cell use
Alt P B I
Pressed in sequence not held down.
To remove a page break use
Alt P B R

Sort current table in order

To sort the current table by the selected column in ascending order, press in sequence

Alt H S S

Do not hold the keys down, press one after the other.

To sort in descending order use

Alt H S O

Format As Table

To apply the Format As Table option using the default format, select any cell within the table and use

Ctrl + T

Press Enter to confirm the range. Make sure the option “My table has headers” is ticked.

Select a hyperlink cell

To select a cell with a hyperlink, without following the hyperlink, simply click and hold the mouse button down as you click on the cell.

Selecting visible cell only

To select visible cells only first select the range and then press

Alt + ;

You can then copy and paste to only paste the visible cells.

Do not be afraid of defeat. You are never so near victory as when defeated in a good cause.

Henry Ward Beecher (1813 – 1887)