The will to win means nothing without the will to prepare.
Juma Ikangaa
Monthly Archives: August 2014
Excel and Financial Year Quarters
Australian solution
The Australian Financial Year runs from 1 July to 30 June. To determine which quarter a date falls into there are at least a couple of formulas that will do the trick.
Repeat with F4
To repeat the last action you have performed on any range, or cell, you can press the F4 function key.
Not all actions can be repeated, but most can.
If you have recently performed two actions only the last one will be repeated.
The future will depend on what we do in the present.
Mahatma Gandhi (1869-1948)
If you can’t outplay them, outwork them.
Ben Hogan (1912 – 1997)
Convert to a Number
Numbers formatted as text (often imported from other systems) can cause issues in Excel.
To convert a number formatted as text into a real number that can be used in calculations use a formula like.
=A1*1
This will convert a number formatted as text in A1 into a real number.
NOTE: it will return an error if the value cannot be converted into a number.
The aim of an argument or discussion should not be victory, but progress.
Joseph Joubert (1754-1824)
Summing Coloured Cells in Excel
A SUBTOTAL trick worth learning
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.
Not everything that can be counted counts, and not everything that counts can be counted.
William Bruce Cameron
Populate a Range
To populate a range with the same entry you first select the range then use
Ctrl + Enter
This is handy for entering zeroes in input ranges. It can also be used to enter a formula in a range.
Symbols
To insert special symbols or characters in a cell use
Alt n u
Pressed in sequence, not held down.
If you only have a hammer, you tend to see every problem as a nail.
Abraham Maslow
Arrange Windows Tiled
To arrange multiple windows in the current file in a tiled layout use
Alt w a Enter
Pressed in sequence , not held down.
Easy to remembr if live in WA (Western Australia) like I do.
Excel and Side Ways Sorting
Yes, it is possible
I found out something new from one of my webinar attendees recently. You can sort sideways in Excel.
New Window
To open a new window for the current file, which allows you to resize and see two separate views of the same file use
Alt w n
Pressed in sequence, not held down.
Be careful when you open multiple windows. Make sure you close the windows with the highest numbers first. Look at the top of the window to see the window number.
If you don’t, you can lose view and print settings.
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.
Antoine de Saint-Exupery (1900-1944)
Take the attitude of a student. Never be too big to ask questions. Never know too much to learn something new.
Og Mandino (1923 – 1996)
An Excel MVP and a chart guru. His site has GREAT chart resources.