Seven simple Excel tricks

Excel lecturer David Ringstrom has started publishing a series of quick Excel tips on our sister site AccountingWEB.com. This article presents a selection of seven quick ideas ranging from data conversion to amortisation tables and filtering blank rows in Excel 2010.

Date conversion trick - How to switch dates stored in yyyy-mm-dd format (eg 2012-12-31) to 31/12/2012 using Text to Columns.

Instant amortisation table - Add an amortisation table by right-clicking the worksheet tab and inserting the templage from the Spreadsheet Solutions tab.

Span Excel across more than one screen - Use  View, Arrange All to position vertically aligned sheets across your screens.

Excel 2010: Filter blank rows - Select the Blanks option at the bottom of the Filter menu, or type the word "blanks" in the  Excel 2010 search box instead.

Excel 2010: Recover unsaved workbooks - Set AutoRecover option to two minutes instead of the default of 10 via the File-Options menu. Also click the "Keep the Last Autosaved Version if I Close Without Saving" option.

Identify duplicate values in Excel 2007/2010 - Select a range of cells. Then, on the Home tab, click Conditional Formatting, Highlight Cells Rules, and then Duplicate Values. Select a formatting option and then click OK. To isolate duplicate instances, right click on a highlighted cell, then choose Filter, and then Filter by Selected Cell's Color.

Recover damaged Excel workbooks - To access the Repair Workbook feature in Excel, go to the Open window, then click a workbook name once. Click the arrow on the right-hand side of the Open button, and then choose Open and Repair. 

Further reading

David Ringstrom's Excel tutorials on AccountingWEB.co.uk

ExcelZone Compendium 2007

Excel navigation tips

ExcelZone - a huge archive of tips and tutorials. Be sure to visit the subscriptions page in MyAccountingWEB to up for our monthly ExcelZone bulletin

Continued...