Most of you have
probably discovered that in Excel there is the 'proper' Microsoft way to do
things which usually involves clicking on buttons on the ribbon or
mini-toolbars, and there is an easy way - the shortcut. There are so many shortcuts available that
even I don't know all of them. However,
I am surprised even when teaching advanced students, how some things that I do
instinctively come as a big surprise to others.
Here are my 5 favourites:
- Select cells using the Shift and Arrow keys
Often
in Excel you need to select a range of cells - a row or a column or an entire
table.
The
obvious method is to use the mouse to click and drag but one slip and you've
selected something completely different and you have to start over again.
I
prefer to select cells with the keyboard as it's quicker and more accurate.
Start
by selecting the first cell in your range (i.e. the top left corner of your
range).
Hold
down the Shift key and use the Down Arrow.
Now you have two cells selected.
Keep
holding down the Shift key and use the Right Arrow. Now you have four cells selected.
You
can keep using the arrow keys until you have the whole range selected
Or
You
can hold down the Ctrl key too.
By
holding down Ctrl + Shift and using the arrows your selection will
automatically extend to the last cell with data in it. So if you have a full row / column of data
you can quickly and easily select all of it.
To select a whole table of data, hold down Ctrl + Shift, press the down
arrow once, then the right arrow once and you have the whole table.
Gone
too far?
Maybe
you didn't want to include that Total row or column in your selection. Release the Ctrl key, keep holding down the
Shift key and use the up or left arrow to change your selection.
To
summarise:
Shift
+ Arrow selects cells one at a time in the direction of the arrow.
Ctrl
+ Arrow moves the selection in the direction of the arrow to the end of a block
of data.
Ctrl
+ Shift + Arrow moves to the end of the block of data, selecting all of the
cells in between.
- Easily create a chart
Another
keyboard shortcut for you.
To
create a chart without having to click through the Insert tab simply select the
data you want to use and press the F11 key.
This
will create a new chart on a separate sheet.
- Double click the Autofill handle
One
quick trick that often surprises my clients is the Autofill handle. Most know that you can click & drag the
handle to fill in a series or copy a formula down a column. But double click on the handle and it will
fill to the bottom of the column. Excel
looks in the column to the left and keeps filling until it finds a blank cell.
- Switch between worksheets
I
use the keyboard quite a lot for regular commands like copy and paste. I've got quite used to using Alt+Tab to
switch between applications or between workbooks, but always found it a pain to
have to use the mouse to switch between sheets in the same workbook. One shortcut it took me a while to find is
using Ctrl+PageUp or Ctrl+PageDown to switch between sheets. Life is much easier now.
- Insert today's date
Sometimes
you need to enter today's date. It's
quick and easy enough to type if you can remember it, but if you're anything
like me you have to think about it and possibly even look it up.
There
are 2 ways to enter today's date in a cell
- Press Ctrl + ; (semicolon) to enter today's date in the current cell. This is a static date stamp that will not change. Use this where you want to show a fixed date e.g. the date a change was made to the data or the date something was purchase.
- Use the =TODAY() function. This will change the date to whatever is the current date, so if you open the file again in a month's time, it will show the date in a month's time. Useful if you are creating, for example, an invoice template which when the details are filled in will be saved in a static format such as PDF. Next time the template is opened it will show the current date again.
I hope you found these tips useful.
If you want to find out more about NIMBUS training and how I can help you to work more efficiently please visit my website www.nimbus-solutions.co.uk
Jo Hansell
NIMBUS solutions
