Microsoft Office Tips & Tricks
View our handy hints for users of Microsoft Office - now includes video tutorials
Microsoft Excel Hints and Tips
Handy hints for best practise when using Excel
While users of Excel who are self taught should be congratulated, we often find in our classroom courses that people have also learned bad habits from the start that have become ingrained.
These tips should help you use Excel in a more efficient way - making your daily tasks or record keeping that little bit easier.
Excel Tutorial Video - navigation fundamentals
Excel Navigation techniques , moving around data blocks and use of space within data sheets
Excel Tip |
Why do it? |
|
Enter Data in columns
with labels at the top and no spaces. |
Most functions in Excel are looking for data arranged in columns I.e. lists. In this way functions like Autosum, Subtotals, Filters, Pivot tables and so on can automatically detect the data they should be addressing. If your cursor is in the data block there is no need to select the block manually. A great time saver. |
|
Do not leave space
within data blocks for decorative purposes. |
Spaces tell Excel you have reached the beginning or end of a block of data anything outside the block i.e. separated by a space is not part of that block. We should use spaces to deliberately separate blocks of data from one another so that we can select whole blocks more easily using shortcuts. In this way functions like Autosum, Subtotals, Filters, Pivot tables and so on can automatically detect the data they should be addressing.In this way functions like Autosum, Subtotals, Filters, Pivot tables and so on can automatically detect the data they should be addressing. If your cursor is in the data block there is no need to select the block manually. A great time saver. |
|
Autosum is an often
used feature, however many people use too many steps
to make it work. |
Go to the bottom of the column of data. Shortcut click in the column then Ctrl + down arrow, (Useful for large data sets) Then use Alt + = for Autosum and just press the enter key to complete. |
|
Selecting the current
block of data in Excel. |
There is a very useful shortcut for this. Click within the required data set. Then press Ctrl + Shift + 8 from the numbers running along the top of the keyboard (not the numeric keypad). The current data block is now selected. |
|
Selection techniques |
People often struggle
to select the correct data set particularly when it
crosses a page boundary |
Click on cell A4 then hold down the Shift key and click on cell D8. You will have selected from A4 to D8. Now hold down the Shift key and click on cell D12. Notice you were able to adjust the selection without starting from the beginning again. |
|
Shortcuts |
|
|
Ctrl + Home key |
Moves the cursor to
cell A1 |
|
Ctrl + End key |
Moves the cursor to
the end of the current worksheet. |
Ctrl + Down Arrow
Start with your cursor in a block of data.
|
Moves the cursor to
the Bottom
of the current data block. |
|
Ctrl + Right Arrow Start with your cursor in a block of data. |
Moves the cursor to the Right hand edge of the current data block. |
|
Ctrl + Left Arrow Start with your cursor in a block of data. |
Moves the cursor to the Left hand edge of the current data block. |
|
Ctrl + Up Arrow Start with your cursor in a block of data. |
Moves the cursor to the Upper edge of the current data block. |
Microsoft Project Tips
Microsoft Project Video Tutorial
How to create a combination view to look at resource over allocation in Microsoft Project - Visualising data in multiple panes.
View more Microsoft Tutorial Videos on our Youtube page
If you have a handy Excel tip that you'd like to share, why not email and we'll consider publishing it here.
Alternatively, if you'd like to learn more about how to improve your Excel knowledge, call John Cowie on 0141 563 8200 to discuss an excel training course or use our enquiry form to ask John a question.