Excel Tips

Follow these Excel tips and best practice guidelines.

Enter Data in columns with labels at the top and no spaces.

Why do it? 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.

Why do it? 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.
• 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 – Shift and click

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.