tag:blogger.com,1999:blog-1869924468172210809.post8746037512551767619..comments2024-03-22T10:09:26.517+00:00Comments on Not Just Numbers: Excel Tip: My Ten Principles for Excel Good PracticeGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-1869924468172210809.post-78262760123555214822016-01-21T10:51:53.752+00:002016-01-21T10:51:53.752+00:00only in the presentation sheets, surely?only in the presentation sheets, surely?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-57852648377825523032016-01-21T10:49:47.284+00:002016-01-21T10:49:47.284+00:00ooh, you really struck a chord there with no. 4 - ...ooh, you really struck a chord there with no. 4 - made me shudder!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-25633078682726638582015-11-19T14:09:29.859+00:002015-11-19T14:09:29.859+00:00Keep row #1 and Column A blank!Keep row #1 and Column A blank!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-66581553001340193672014-01-14T12:37:39.570+00:002014-01-14T12:37:39.570+00:00Pleased you found them usefulPleased you found them usefulGlen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-52545244368426672392014-01-14T02:13:13.514+00:002014-01-14T02:13:13.514+00:00These are indeed great tips in maximizing the usag...These are indeed great tips in maximizing the usage of Excel. Thanks for sharing these helpful principles. Ramon Andrewshttp://www.cloudstaff.com/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-14464764080620068032013-05-14T18:40:34.891+01:002013-05-14T18:40:34.891+01:00Delete blank tabs. Use named ranges. Avoid VBA whe...Delete 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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-60713321794493439842013-05-09T09:32:48.027+01:002013-05-09T09:32:48.027+01:00Don't format the cells to be blank if the actu...Don'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!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-31520946085006290832013-05-08T18:09:47.710+01:002013-05-08T18:09:47.710+01:00Great string. When complete a summary would be gr...Great string. When complete a summary would be great, maybe post on LinkedIn.Com.Christopherhttp://excelandaccess.com/about-us.htmlnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-40550294510438149192013-05-08T16:46:04.099+01:002013-05-08T16:46:04.099+01:00I think it's a really useful list. I don'...I think it's a really useful list. I don't think there's anything I'd disagree with. My thoughts:<br /><br />1. 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.<br /><br />2. Don't mix raw data, reference data and analysis, for example, charts and pivot tables. I guess the same idea as your OAP :)<br /><br />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.<br /><br />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".<br /><br />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.<br /><br />Kind regards<br /><br />TrevorTrevor Shuttleworthnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-62593829982594428952013-05-08T08:59:56.837+01:002013-05-08T08:59:56.837+01:00Glen,
Good list. This is an important topic, as I...Glen,<br /><br />Good list. This is an important topic, as I believe that improving the quality of spreadsheets is a very valuable pursuit.<br /><br />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:<br />- Use a modular design, dividing a spreadsheet into sections such as: Documentation, Data, Assumptions, Calculations, Results, and Validation.<br />- Avoid "constant jamming" ie. hard-coding numbers into formulae.<br />- Use named ranges in VBA, rather than hard-coding cell addresses.<br />- Keep formulae short. Split complex formulae into multiple, simple steps.<br />- Use formatting for description rather than decoration. eg. different colour text for assumptions compared with formulae.<br />- Protect worksheets to prevent inadvertent changes.<br />- For charts, always label your axes<br /><br />BTW, I've added a link to your article at Connexion: http://www.i-nth.com/resources/connexion<br /><br />Regards,<br /><br />Bob.Bob Watsonhttp://www.i-nth.comnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-32565471037764429112013-05-07T17:33:17.071+01:002013-05-07T17:33:17.071+01:00I really like putting in hyperlinks from a summary...I 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.<br /><br />Thanks for this Glen. I really appreciate your information.<br /><br />Carl<br />CanadaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-50180743827706524542013-05-07T14:30:50.982+01:002013-05-07T14:30:50.982+01:00I would re-write point 7 to use named ranges rathe...I 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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-71746928306402034152013-05-07T13:13:04.133+01:002013-05-07T13:13:04.133+01:00Always try to incorporate a test cell(s) which e.g...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".Anonymousnoreply@blogger.com