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.

View information on our Excel training day courses

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
Shift and click

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 0791 787 4739 to discuss an excel training course or use our enquiry form to ask John a question.