Get to grips with Microsoft Excel

Groups rows and columns
In Excel 2007/10, select a range of rows or columns, then click the Group dropdown menu on the Data tab and choose Rows or Columns. Now click on the Group button and a bar will appear above the spreadsheet, with numbers showing the different levels of grouping.

Reveal formulae
To see all formulae used in an Excel spreadsheet quickly, hold down Control and tap the ` key (the button to the left of the number 1 on most keyboards). Press it again to reverse the change.

Remove duplicate data
To find and remove duplicate data items in Excel 2010, select the relevant area then click Remove Duplications on the Data tab. If there are column headings, select ‘My data has headers’ and click Select All. Click OK to remove the duplicates.

Show recent documents
In Excel 2010, open the File menu, then click Options. Click Advanced options then scroll down to the Display section and type the desired number into the ‘Show this number of Recent Documents’ box.

Remove unprintable characters
Data imported from external sources can sometimes contain unprintable characters. To remove these from data contained in cell A1, say, choose another cell and use the formula =CLEAN(A1).

Clearer numbers
It’s possible to have Excel abbreviate large numbers, so that 1,000,000 becomes 1M and 1,000 becomes 1K, for example. To do this, select the relevant cells and press Control and 1 to show the Format Cells window. Select the Number tab, choose the Custom option then click in the Type box and type [>=1000000]0,,"M";[>=1000]0,"K";0 (with no spaces). Click OK.

 

Customise the ribbon
Click File followed by the Customize ribbon. Click New Group at the bottom right and then Rename. Type a name and click OK. Now drag and drop icons from the left-hand column into the new group.

Customise the Status bar
The Status bar at the bottom of the Excel window can be customised. Right-click on an empty part and choose an option from the pop-up menu.

Text to columns
If you have a list of forenames and surnames in a single column, it’s easy to separate them. In Excel 2003, select the affected column then open the Data menu and choose Text to Columns. Choose Delimited, click Next and tick the box (or boxes) representing the characters that delimit the names (typically a space). If the preview looks right, click Next followed by Finish.

Check formulae
In Excel 2003, open the View menu, point to Toolbars and choose Formula Auditing. On the toolbar that appears, click the first icon (Error Checking) to view and troubleshoot problems.

Better Autofill
Autofill is a great timesaver but if you need to lock certain cell references before using it, then append a ‘$’ symbol before both the letter and number, so A1 would become $A$1.

Make a quiz
Write a question in one cell then create a list of answers in a column off to the right somewhere. Now position the editing cursor in the cell that will contain the correct answer, open the Data menu and choose Validation. Choose List from the Allow dropdown menu, click the small icon to the right of the Source box, select the answer list and click the right-most icon in the Data Validation window that has appeared.