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…
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. (more…)
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.
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. (more…)
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)
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.
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. (more…)
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.
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. (more…)
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.
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.