You don’t have to use an IF function to get the most out of logic calculations in Excel.
Monthly Archives: September 2014
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.
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)
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
Curiosity is the key to creativity.
Akio Morita
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)
How to Identify Blank Cells in Excel
Find the technique you need
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)
Excel Links and Deleted and Inserted Rows
How to avoid #REF! errors
When linking to another sheet, a deleted row can often cause a #REF! error. You can use the INDEX function to avoid these errors.
Selecting visible cell only
To select visible cells only first select the range and then press
Alt + ;
You can then copy and paste only paste the visible cells.
Any visible formulas are converted to values.
A problem well stated is a problem half solved.
Charles Franklin Kettering 1876-1958
Windows tip
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)
Returning from hyperlink
To return from following a hyperlink in Excel press
F5 function key and then press Enter
Love not what you are, but what you may become.
Miguel de Cervantes (1547 – 1616)
Horizontal bullet chart in Excel
Excel Yourself article
Check out my follow up article and VIDEO on the ITBDigital website on how to convert a vertical bullet chart into a horizontal one.
Click here for full article and video.
For the original bullet chart post click here
These techniques are based on ones in the great book
Excel 2007 Dashboards and Reports For Dummies by Michael Alexander
Good discussion of pie chart vs bar charts for comparisons
A great initiative by the University of Sydney to bring forward the use of a rating system for foods. Stay tuned for an App. Find out what’s healthy and what’s not.