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
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.
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