Share this content
AIA

Seven simple Excel tricks

by
9th Apr 2012
Share this content

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

From time to time you may encounter worksheets where dates are stored in yyyy-mm-dd format, such as 2012-12-31 as opposed to 31/12/2012. You can convert such inputs with just a few mouse clicks. 

  1. Select the column or cells containing the dates you wish to convert.
  2. Choose Data and then Text to Columns.
  3. Click Next twice, then on the third tab choose Date, and then YMD.
  4. Click Finish to complete the process.

Instant amortisation table (yes, with a z)

You can add an amortisation table to any Excel workbook with a few mouse clicks:

  1. Right click on any worksheet tab and then choose Insert. 
  2. Navigate to the Spreadsheet Solutions tab and then double click on Loan Amortization.

Span Excel across more than one screen

Multiple monitor users often launch two different sessions of Excel when they need to see two spreadsheets side by side. Doing so can be viable in certain circumstances, but typically it’s more effective to have a single Excel session that spans both monitors. To do so, open any version of Excel and then double click on the words Microsoft Excel at the top of the screen. Your Excel window should now only cover part of a single monitor. Position your cursor over the title bar and hold down the left mouse button to move Excel up to the top corner of the screen. Next, use your mouse to drag the right-hand corner of Excel across the second monitor.

To then see two different workbooks side by side, choose View, Arrange All, and then double click Vertical. In Excel 2003 and earlier, choose Window, Arrange, and then double click Vertical.

Excel 2010: Filter blank rows

The Filter command, which appears on both the Home and Data tabs in Excel 2007 and 2010, allows users to pick and choose which rows to display within a list. However, it can be tedious trying to isolate blank rows from a lengthy spreadsheet. You must click the Filter arrow, unclick All, and then scroll down to the bottom to click Blanks. In Excel 2010, simply type the word Blanks in the search box instead.

Excel 2010: Recover unsaved workbooks

It's painful when you accidentally (or even purposefully) close a workbook without saving and then regret it. In Excel 2010, you can sometimes recover your work. To do so, choose File, Info, click the Manage Versions button, and then choose Recover Unsaved Workbooks. Excel 2010 automatically keeps a copy of many (but not all) unsaved workbooks for a few days. 

To make the most of this feature, set your AutoRecover option to two minutes instead of the default of ten. To do so, click File, Options, and then Save. Make sure that Save AutoRecover Information settings is set to two minutes and that Keep the Last Autosaved Version if I Close Without Saving is checked as well.

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

Many users overlook the Repair Workbook feature in Excel. To access it, navigate to the Open window in the usual manner, and then click once a workbook name. Click the arrow on the right-hand side of the Open button, and then choose Open and Repair. This will sometimes correct problems with a malfunctioning workbook. If this step doesn't resolve your problem, install the free OpenOffice product, and use the Calc spreadsheet program to try to open your damaged Excel workbook. 

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

You might also be interested in

Replies (6)

Please login or register to join the discussion.

avatar
By orangewolftr784
29th Nov 2019 08:52

good content. keep it up. mcdvoice

Thanks (0)
avatar
By orangewolftr784
29th Nov 2019 08:52

good content. keep it up. mcdvoice

Thanks (0)
avatar
By cicadeltv58
04th Dec 2019 07:35

Great idea. Keep writing mate. mybpcreditcard

Thanks (0)
avatar
By furzugefyo
20th Feb 2020 20:17

Really great post thanks for share article. Mybpcreditcard

Thanks (0)
avatar
By sheree2334
21st Jul 2020 09:30

awesome trick. thanks for sharing innovative ideas. MYBPCreditCard

Thanks (0)
avatar
By bobbylove
30th Aug 2020 15:34

Thanks for sharing knowledgable content. https://mcdvoice.world/

Thanks (0)