You don’t have to use an IF function to get the most out of logic calculations in Excel.
Sum Function Range
The SUM function can handle more than one range eg
will total both ranges.
Experience is the name everyone gives their mistakes.
Oscar Wilde (1854 – 1900)
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
Luck favours the mind that is prepared.
Louis Pasteur (1822 – 1895)
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.
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
Curiosity is the key to creativity.
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
Don’t find fault, find a remedy.
Henry Ford (1863-1947)
When developing formulas in Excel you often need to know when a cell is blank. There are different ways to check if a cell is blank, the one you chose will depend on what you are trying to achieve.
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.
Well done is better than well said.
Benjamin Franklin (1706-1790)
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.
A problem well stated is a problem half solved.
Charles Franklin Kettering 1876-1958
To open Windows File Explorer hold the Windows key down and press E
Do not be afraid of defeat. You are never so near victory as when defeated in a good cause.
Henry Ward Beecher (1813 – 1887)