To quickly move around the spreadsheet grid sheet try using
Ctrl + arrow keys
You will move the active cell in the direction of the arrow key you click.
In a table you will move to the far edge of the table as far as there are entries.
If the row or column is blank you may find yourself at the extreme edge of the spreadsheet grid.
Press the opposite direction arrow to move back.
Press Ctrl + Home to go to the top left of the screen if you need to.
When you first start out using VBA and writing VBA (macro) code it is useful that the VBA Editor helps you quickly identify when you have made a mistake. If a line of code has an error it will display a dialog box that explains the error and turns the line of code red.
You can use the keyboard to quickly select whole rows or columns.
To select a whole column, or columns, use:
Ctrl + Space
To remember this shortcut – both Ctrl and column start with C.
To select the whole row, or rows, use:
Shift + Space
To remember – s and r are next to each other in the alphabet.
Creative minds have been known to survive any kind of bad training.
Anna Freud (1895-1982)
In Australia our financial year is from 1 July to 30 June. If you need to work with days elapsed or days remaining in the financial year you can use a few formulas to do the calculations for you.
To see Excel’s different print views, use these shortcuts (all pressed in sequence, not held down)
Alt w l
Alt w i
Alt w p
They are in order
Normal view (the standard grid view of Excel)
Page Break Preview (non-print areas in grey)
Page Layout (looks like MS Word)
If you do a lot of financial modelling then this free online course may be worthwhile. There are videos and pdfs, plus example Excel files.
I attended a BPM course many years ago and it was very good.
An error doesn’t become a mistake until you refuse to correct it.
Anonymous
To see as much of the Excel grid on the screen as possible you can use Full Screen mode. Press
Alt w e
Pressed in sequence, not held down.
To return to the normal view, press the Esc key.
Check out my July 2014 article on Bullet charts on the CPA Australia ITBDigital website – click here to see the article. The video is below.
Bullet charts were developed by Stephen Few – see his pdf on bullet charts click here.
The technique is based on one used by Michael Alexander in his great book Excel 2007 Dashboards and Reports for Dummies by Wiley.
The greatest mistake you can make in life is to be continually fearing you will make one.
Elbert Hubbard (1856–1915)
In a previous blog post I explained how to do a 3D SUM calculation in Excel that SUMs through sheets. You can view it here. I have seen a few posts in forums recently about performing a 3D SUMIF calculation. Whilst the SUM function can work with 3D ranges, the SUMIF function can’t.
The new kid on the block for all things Excel. Based in India, the site has lots of freebies and Excel resources. Chandoo is an Excel MVP.
You can select a range and use the following sequence to set that range as the print area
Alt p r s
Pressed in sequence, not held down.
He who has never made a mistake never made a discovery.
Samuel Smiles (1812-1904)
A great example of Seth Godin’s ability to look at things differently.
Not all those that wander are lost.
J R R Tolkien (1892-1973)
Imagination is more important than knowledge.
Albert Einstein (1879-1955)
It is easy to print things from Word. It is not so easy in Excel. Excel has this huge grid and if you don’t tell Excel otherwise, it will print everything on the sheet, whether you want it to or not.
Excellence is to do a common thing in an uncommon way.
Brooker T Washington (1856-1915)
The premier site for all things Excel. Includes an Excel forum. Run by Bill Jellen (Excel MVP) and author.
Print titles are the rows (top) and columns (left) that you can set to print on all pages. To open the dialog to set them up use
Alt p i
Pressed in sequence, not held down.
To create a blank Excel file use
Ctr + n
All things are difficult before they are easy.
Thomas Fuller
Seth Godin writes a great blog and provides really interesting insights. Highly recommend you subscribe to his email posts.
He has written many books and and always provides a different slant on the topics he covers.
Hyperlinks are a great tool as they allow you to speed up and simplify navigation within a file. Sometimes hyperlinks can be frustrating. See how to remove some of those frustrations below.
The shortest answer is doing.
English proverb
An Australian-based site with great Excel resources. Has function and formulas information as well as VBA macro resources, plus an Excel Forum.
The important thing is not to stop questioning.
Albert Einstein
A great website run by David Beard, which focuses on exercise as we age and the benefits associated with it. We are living longer, so we need to make sure we keep exercising to stay healthy as we age.