Share this content

whats the most off the wall excel trick you have used?

excel is clever, but sometimes too clever, requiring a bit of lateral thought (or a eureka moment) to achieve what sounds simple but is in fact not catered for. an example is a recent post about how to copy formula across columns, but referring to cells down rows.

why not share your eureka moments with the world? solutions that solved a problem without resorting to visual basic!
Alastair Harris


Please login or register to join the discussion.

02nd Apr 2005 17:52

To show formulae in a sheet
I like the trick ctrl + ` (the key to the left of the number 1 on the top row) which alternately shows and hides formulae in a sheet, found by accident as the best things ususally are.

Thanks (0)
03rd Apr 2005 09:22

Excel tricks...
Best trick I found was to replace Excel with Open Office (just as good & free!). This after replacing IE with Firefox. Anything to replace Microsoft is a good trick in my book! ;o)

Thanks (0)
29th Mar 2005 12:09

Go to and download the password unlocking add in. You then need to go to tools - add-ins and install the add in.

This lets you unlock protected sheets, it does not let you open password protected workbooks - follow the link on the website if you want to do this!

Once you can bypass the password protection on a workshhe you can do loads of things, for example:

1) Every time someone sends a stupid quiz round where the answers are hidden you can unlock it and complete the quiz in seconds, thus crushing your colleagues pitiful attempt to work out who sang an obscure 70s hit.

2) If people insist on hiding parts of spreadsheets they send you, you can now unlock it and send it back unlocked to them. This always upsets personnel - there problem they should get real jobs!

As you an see loads of uses, but my apologies to those of you who thought that sticking the name of your dog on a workbook would actually stop people opening it, but isn't it better you found out that your passwords don't mean anything?

Have fun!!!

Thanks (0)
29th Mar 2005 17:24

Drill down
When pivot tables were new (Excel 5) by accident I double-clicked on a cell and by magic got a new sheet with a data list of the breakdown of the figure I clicked on - though in 2005 I'm sure this is common knowledge to most, but may be news to some?

I built upon this idea into a multi-dept reporting solution with drill down for a large corporate client. 66 depts, more than 250,000 items. Who still thinks Excel has limit of 65,536 records?

Any use?

Thanks (0)
24th Mar 2005 09:51

Exel is alright
If you have links to another workbook use ctrl + [ to open the book from the one you are using.

Also I highly recommend openoffice (Thought I'd get that in) ;)

Thanks (0)
31st Mar 2005 21:18

Thanks Rob Brown
The excel add in(s) is quite interesting, a new area to me.

I always 'craked' those stupid quiz using alternative methods, but I can now go direct !!

Thanks (0)
By Anonymous
25th Mar 2005 10:41

Not really that clever
But ctrl tab to move between open sheets is handy when comparing data from different sheets.

Thanks (0)