There are lots of shortcuts available in Microsoft Office, and this list of quick Excel tips contains some that I personally use on a daily basis. Some of these are essential shortcuts for anyone using large volumes of data during their working day such as selection techniques, for example.
Many people I train do not know any way of selecting data in a worksheet other than by clicking and dragging, but this is not only time consuming and difficult to do, it is also prone to error. Dragging your cursor over 25,000 rows of data and then accidentally clicking somewhere else is frustrating and demoralising to say the least – and as a double-whammy, you have to do it all again!
These are all Quick Excel Tips
Some quick Excel tips are applicable to other Office programs as well, but all of these Excel tricks are specific to Excel. However, they will work in any version, including older ones, so everyone who uses Excel can benefit from them.
1. Automatically totalling multiple columns and rows
- We often want to add totals to several columns of figures, and although you can add the first total and fill across the others, there is a better way. This quick Excel tip explains…
- How to add a Totals row to several columns at once
- Select ALL the cells that will contain the totals
- With the cells highlighted hold down ALT and press the = sign
- Excel adds an Autosum function to each column in one go
2. Selecting large blocks of data – and identifying missing data
- When you have a lot of data to work with it is often awkward to select the data by clicking and dragging. I never do this because it is so easy to mis-select cells, but by using this tip you will never have to suffer that again!
- How to navigate to the bottom of a column of data
- Click the first cell in a column, hold down CTRL + click the Down arrow
- Excel moves to the last cell in the column
- If there is a gap Excel will stop there so it is also a great way of identifying missing data in a large model
- How to select all the cells in a column
- Click the first cell as before, then hold down Shift+Ctrl+down arrow
- Excel moves to the bottom (or a gap) and highlights the cells
- You can press the Right arrow to select adjacent columns
- How to select all the populated cells
- Click in any cell, hold down Ctrl and press * from the numeric keypad
3. Quick Excel tip – Creating a Custom List
- Most of us have lists of words we use frequently and it can be useful to have Excel automatically fill in the items for us
- This can be achieved by creating a Custom List, and this quick Excel tip shows you how
- How to set up a Custom List
- The easiest way is to type the list into a blank cell range first, then highlight them
- From File go to Options > Advanced > Edit Custom Lists near the bottom
- Click Import to get Excel to add your list to List entries
- Click OK then OK again
- Now whenever you start typing a word in the list Excel will let you fill the others by dragging the fill handle. In the image below I have created a custom list for metals:
This example was based on a real-life situation involving commodity traders who were attending one of my classroom training sessions. They used these metals all day in their work and wanted to be able to quickly fill in the list automatically. This quick Excel tip proved very popular that day!
4. Incrementing numbers
- It isn’t just months and days that can be filled by dragging, numbers can be made to increment too
- How to increment numbers by one
- Type in the first number, then hold down Ctrl and drag the fill handle down
- Excel will add 2, 3, 4 etc.
- How to increment by a different amount
- If you want a different sequence type in the first 2 entries and select them both then drag the fill handle down
- Excel will continue the sequence for you e.g. 1, 3, 5,7, 9 etc.
5. Applying Layout changes to several worksheets at once
- It is easy to add layout items, formatting, calculations – in fact most of the structure of a worksheet to several sheets at once
- This saves repetition and streamlines workflow
- How to add structure to several sheets simultaneously
- Add enough sheets to the workbook to complete the model e.g. if you want a sheet per month you will probably want 12 plus a summary sheet
- Right-click on the first sheet tab and choose Select all sheets
- Excel will add the word Group to the title
- Add whatever you want to the sheet (titles, formatting, calculations, grouping etc.) but don’t click the sheet tabs again until you have finished
- Don’t add any data or it will be added to all the sheets
- When you are done, de-select the sheets by clicking another tab
- All sheets will now contain the same structure
- You can now add the data as and when it becomes available