So here goes...
My Ten Excel Principles:
- Always separate out the three stages - Obtaining the data, Analysing the data, Presenting the data (OAP). This makes your spreadsheet far more flexible and easy to follow.
- Never include numbers in formulae - always enter the variable in a cell and refer to the cell in the formula. This makes the spreadsheet easy to follow and to update when variables change.
- Don't fiddle with the data - when using data from elsewhere, don't edit it, report on it in other sheets. This minimises errors and increases efficiency.
- Use data validation where possible, to minimise data entry errors, and to make it easier to analyse and report on the data.
- Always think "Is there a way I could do this that would save me time next time?"
- Lay out data to suit Excel - not to suit the eye. Every heading, subtotal or empty column you place among the data makes it far less useable.
- Get used to using the dollar signs when you refer to cells. Used well, you can write a formula once and copy it everywhere - Saving time and reducing errors.
- Protect cells with formulae in them - it can be very difficult to find an incorrect (or missing) formula caused by accidental typing.
- Use colour and formatting (as well as descriptions) to make it obvious where data entry is required.
- Avoid merging cells unless absolutely necessary - merged cells make a spreadsheet far harder to edit.
I'm sure I will have missed a few key ones - these are very much my personal ones - and I would love to hear yours in the comments.
Click here for our our exclusive offer on Online Excel Training
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".
Always try to incorporate a test cell(s) which e.g. compares the total against a different addition of the data which makes up that total. The test cell would report either "Agreed" or "Out of balance".
ReplyDeleteI would re-write point 7 to use named ranges rather than absolute cell references. Named cells make formulas easier to understand than referring to cell references.
ReplyDeleteI really like putting in hyperlinks from a summary/report tab to the detail data tab and back again. This allows senior management to jump to the source rather than looking at the formula to figure out where the number on the summary/report tab is coming from.
ReplyDeleteThanks for this Glen. I really appreciate your information.
Carl
Canada
Glen,
ReplyDeleteGood list. This is an important topic, as I believe that improving the quality of spreadsheets is a very valuable pursuit.
I recently started a topic on the LinkedIn group Excel Blackbelts, which I titled "Spreadsheet best practice?". As of this morning there were 113 comments, including some robust disagreements! I started the discussion with a few suggestions, though others took a wide range of paths from there. My starting suggestions were:
- Use a modular design, dividing a spreadsheet into sections such as: Documentation, Data, Assumptions, Calculations, Results, and Validation.
- Avoid "constant jamming" ie. hard-coding numbers into formulae.
- Use named ranges in VBA, rather than hard-coding cell addresses.
- Keep formulae short. Split complex formulae into multiple, simple steps.
- Use formatting for description rather than decoration. eg. different colour text for assumptions compared with formulae.
- Protect worksheets to prevent inadvertent changes.
- For charts, always label your axes
BTW, I've added a link to your article at Connexion: http://www.i-nth.com/resources/connexion
Regards,
Bob.
I think it's a really useful list. I don't think there's anything I'd disagree with. My thoughts:
ReplyDelete1. Don't try to emulate a manual/paper system. That is, for example, one worksheet per customer, supplier, product, year, month or day. Then wonder how to cross refer, total and analyse the data on those hundreds of sheets. Use one sheet and include all the key data. Then use tables, sorts, filters, pivot tables, charts, and so on to slice and dice the data.
2. Don't mix raw data, reference data and analysis, for example, charts and pivot tables. I guess the same idea as your OAP :)
3. Don't use different areas of a worksheet for different types of data/information. This presents problems catering for different numbers of rows/columns in each block of data and also having to scroll around the page to see the data.
4. And one I truly hate: don't use formulae to cater for "future" transactions. That is, 100, 1,000, 10,000 rows with dozens of columns and the majority of those cells filled with formulae to test if there's any data in column A. And, if there isn't, leave the cell "blank".
5. Lastly, with the "Big Grid" in Excel 2007 and above, be careful if you use whole column or row references as this can make calculation time excessive.
Kind regards
Trevor
ooh, you really struck a chord there with no. 4 - made me shudder!
DeleteGreat string. When complete a summary would be great, maybe post on LinkedIn.Com.
ReplyDeleteDon't format the cells to be blank if the actual contents of the cell are = 0. It makes it look like there is nothing (no formula or anything) in the cell and so is hard to distinguish from actual empty cells!
ReplyDeleteDelete blank tabs. Use named ranges. Avoid VBA whenever possible. Document each line of VBA code. Use Debug.Assert for every assumption in VBA (eg. Column B is "Sales Totals"). An exception for #10 in the list above... use merged cells on the outside edge of a data table to quickly select tables or page through data. I agree to not use merged cells within a table whenever possible.
ReplyDeleteThese are indeed great tips in maximizing the usage of Excel. Thanks for sharing these helpful principles.
ReplyDeletePleased you found them useful
DeleteKeep row #1 and Column A blank!
ReplyDeleteonly in the presentation sheets, surely?
Delete