Excel Productivity

I’m a bit of an Excel wiz. One of my interests is to help people learn how to get the most out of Excel. It is really topical for me at the moment as I am working on some monster spreadsheets at work that are very badly designed an therefore quite dangerous as it is very easy to get the wrong results. One of the spreadsheets I’m working on has over 800 individually entered links in it – and there is a way to simply modify it to enable users to have 1 link then reference all the other links from that one link – the productivitity increases will be amazing – I just need to convince everyone now…

So, I would like to do a bit of a series in my blog on my best productivity tips for Excel. The reason I got into programming is that I HATE typing (no hate is definitely not a strong enough word here), so I would come up with macro’s and learn tips to make it easier to get the data in to the spreadsheet. I’ve been using Excel since 1991 and Excel 2.1 (it came with runtime version of windows and ran from DOS). I conquered the Excel 4.0 macro language then they went and changed to VBA and I had to learn all over again because they use very different approaches.

I would love to know if any of my tips help you to learn something about Excel or make using Excel easier. Over the next few weeks, I am going to take one topic at a time and expand on it a bit. So here are my top Excel productivity tips.

• Know when to use Excel and more importantly when NOT to use Word.  
• One thing in one place, once – more of a database tip, but if you have to re-type anything there is a better way. If something seems too hard to do, there is an easier way. Lots of little tips on data entry, text formulas and my favorite – concatenation.
• Set up your spreadsheet like a database – have column headings, no blank columns and definitely no blank rows and use Freeze Panes for row and column headings. You can then use such excellent gems as Filtering, Pivot Tables an database functions.
• Learn your keyboard shortcuts – this is a big productivity increase – especially keyboard shortcuts like ctrl+” (copy from above) and ctrl+; (today’s date) – try them out, and I will explain a bit more later.
• Set up autocorrect and autotext – being in construction it need to type quantities in metres squared and metres cubed frequently – so I set up an autotext to turn m2 into m².
• There are at least 3 additional tools that need to be on your toolbar – I will explain what they are and what they do – it’s probably 2 posts, one on outlining and one on pictures and drawing objects.
• Excel is not only for accountants – so don’t use the accountant’s currency format (the $ sign tool) which pushes the dollar signs to the left of the column (more of a fastidious proper way to do things tip, but I will throw in a few of these too).
• I’ve invented a few excellent navigational tools using the Ctrl+Arrows key combinations that I will share with you.
• Know how to use the page set up options and page break preview, and also know when to use manual vs automatic page breaks.
• Using the Autofill handles effectively.
• Don’t tag things with colours because you can’t use the colours for adding and counting etc without using a macro – use tags and Conditional Formatting and the formula’s CountIf and SumIf instead.
• Use Hyperlinks to navigate around a big sheet.

That’s probably enough to start off with, but I will probably think of some more as I go along. After this, I will do a series on Word also.