Customise the Quick Access Toolbar

The Quick Access Toolbar appears in the top-left corner of the Excel window in Excel 2007 and 2010. By default, it contains buttons to save your work and undo or redo/repeat the last action. You can customise what appears here, and it's a very useful place to dock your most commonly used tools.

Click the 'Office' button followed by 'Excel Options' and 'Customize' in Excel 2007. In Excel 2010, choose 'File | Options | Quick Access Toolbar'. Select a tool that you want to add in the left-hand column and click 'Add'. Change the available commands using the dropdown list labelled 'Choose commands from'. Click 'OK' to save your changes.

Move the Quick Access Toolbar

When you're customising the Quick Access Toolbar, you can opt to have it displayed at the bottom of the ribbon instead of at the top of the window. This means you won't have to move the mouse pointer as far to access each tool in the Quick Access Toolbar.

Increase compatibility

There are plenty of people still using Office 2003 and earlier. If they haven't kept up to date with updates and import filters, there's every chance that they won't be able to open '.XLSX' files without going to some trouble.

Remove this potential hassle by setting your default save format as Excel 97-2003. Click the 'Office' button and choose 'Excel Options | Save'. Under 'Save Workbooks', choose 'Excel 97-2003 Workbook' and click 'OK'. In Excel 2010, simply choose 'File | Options | Save | Excel 97-2003 Workbook'.

Copy as a picture

If you need to copy a chart or a set of cells to a new location outside Excel, consider doing so as a picture. You won't be able to further process the data, but all your formatting options will be preserved.

Select the chart or cell range and then click the downward arrow below 'Paste'. Choose 'As Picture | Copy As Picture'. You can now paste the image file wherever you like.

Name a range

Quickly create a named range of cells that you can return to later by selecting the cells you want to name and then clicking in the cell name bar directly to the left of the formula bar.

Enter the name you want to give to this range of cells and press [Enter]. You can now quickly return to this cell range by clicking the arrow to the right of the cell name bar and choosing the range that you previously saved. You can store several cell ranges.

Change the editing order

When you're entering data in a set of cells and you press [Enter], by default you move down to the next cell in the column. If you're entering data across rows, you can change this behaviour.

In Excel 2007, click the 'Office' button, followed by 'Excel Options', or in Excel 2010, choose 'File | Options'. Now select 'Advanced' and tick the box marked 'After pressing enter move selection'. Change the direction in the dropdown list.

Compress pictures

If you have pictures or other graphical elements embedded in your Excel spreadsheet, they can greatly increase the document size. If you need to email the spreadsheet or post it online, you can make the size much smaller simply by compressing all the pictures in the sheet.

Select one of the pictures and you should see the Format tab under 'Picture Tools'. In the 'Adjust' group, choose 'Compress Pictures'. Click 'Options' and ensure that the options to delete cropped parts of the picture and to apply basic compression when saving are both enabled. Below these, select the level of compression you want to use and click 'OK' twice.