{"id":3593,"date":"2019-06-30T16:24:47","date_gmt":"2019-06-30T04:24:47","guid":{"rendered":"https:\/\/kinetics.co.nz\/?p=3593"},"modified":"2019-06-30T16:24:47","modified_gmt":"2019-06-30T04:24:47","slug":"9-common-excel-mistakes","status":"publish","type":"post","link":"https:\/\/new.kinetics.co.nz\/?p=3593","title":{"rendered":"9 Common Excel Mistakes"},"content":{"rendered":"

[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”3.22″][et_pb_row admin_label=”row” _builder_version=”3.25″ background_size=”initial” background_position=”top_left” background_repeat=”repeat”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” custom_padding__hover=”|||”][et_pb_text admin_label=”Text” _builder_version=”4.9.2″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” hover_enabled=”0″ sticky_enabled=”0″]We were reminded of the risks of messing up spreadsheets in a recent blog post at Oracle<\/a>:<\/p>\n

The scary fact they quote is that 88% of all spreadsheets<\/a>\u00a0are wrong! The consequences of relying on bad information as a result can be insane!\u00a0<\/strong>That reminded me of a couple of lessons from years ago in terms of good spreadsheet design.<\/p>\n

 <\/p>\n

1. Check calculations<\/strong><\/h2>\n

It\u2019s so easy, especially when adding\/inserting new rows, to inadvertently sometimes miss out a \u2018sum\u2019 formula.<\/p>\n

Always try to create cross-check calculations that make sure your formulas add up.<\/strong>\u00a0In this simple example, it\u2019s pretty obvious:<\/p>\n

\"\"\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

As your tables become more complex, maybe with blocks and sub-blocks, for example a P&L, then it becomes easier to make an error and more important than ever to add checks.<\/p>\n

In this case, it’s simply checking the verticals and horizontals match \u2013 one minus the other should be zero.<\/p>\n

Excel will often try to warn you of errors with a small hazard symbol, but I have found that it\u2019s not reliable.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

2. Highlight inputs and outputs on your \u201cwhat-if\u201d models<\/strong><\/h2>\n

To make it easy for other people to use your complex models, it pays to highlight the fields that operators should complete, and even to group them together, as well as making the results clear.<\/p>\n

 <\/p>\n

3. Avoid inserting rows to make attractive spaces as a design feature<\/strong><\/h2>\n

Excel thinks the data is in separate lists and will treat blank rows differently depending on the functions you need to use. For example, autofill will stop at a blank row as will formulas, so you could find your worksheet doesn\u2019t update as you expect.<\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Avoid inserting blank rows or columns in a worksheet, instead use formatting to emphasise key data<\/strong> which will help to make the worksheet easy to read and absorb.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

4. Don\u2019t use Excel when you should be using other software<\/strong><\/h2>\n

Excel is an amazing tool and can be bent to your will. That means many people try using it for things that go beyond what it was designed for<\/strong> , e.g. project plan and task management (doesn\u2019t have enough capability for anything beyond basic task management).<\/p>\n

The main misuse is as a database. Excel is not a relational database, but is often used as one.<\/strong> Worksheets become unwieldy when used for complex project plans or to manage tasks, or to store raw data. This kind of use is fine for manageable sized lists, but if your users have to scroll or split the worksheet, then it\u2019s likely using Excel as a database is the wrong software and you should consider Access or SQL instead.<\/p>\n

 <\/p>\n

5. Printing the whole spreadsheet<\/strong><\/h2>\n

\"\"<\/p>\n

It\u2019s a pretty common occurrence that a user presses \u2018Print\u2019 and only then realises that the entire worksheet is printing, including any blank rows and columns. Instead, select the data you wish to print, then go to File > Print > Print Selection to print just that data.<\/strong> This will save the trees (as well as printer toner) and stops your colleagues from hating you for clogging the printer for ages.<\/p>\n

 <\/p>\n

6. Selecting the entire column when you apply formatting.<\/strong><\/h2>\n

It\u2019s easy to select an entire column or row in Excel by clicking on the header and shading it, or adding a line etc. But this can have unexpected side effects.<\/strong> For example, it can slow your PC down, impacting performance, and it can be a pain when printing. But the big one is confusing others, especially if they wonder if there is an error i.e. did you mean to make all the rows beneath your data bright yellow? Is something missing or is it a formatting error? It impacts productivity and creates a lack of confidence about your valid data\/formatting when others stop and question your data.<\/strong><\/p>\n

Instead, just select the data not the entire column (or row) (or make a RANGE)<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

7. Use data validation in models<\/strong><\/h2>\n

Typing free text may seem quicker at the time and Excel does a great job trying to help, especially with \u2018autofill\u2019 which tries to help you apply consistency. However this leaves it open for different user\u2019s different typing styles.<\/p>\n

Instead, use data validation to ensure users select the correct text label,<\/strong> e.g. company name. Go to Data > Data validation and set criteria that ensures users can only select the correct data.<\/p>\n

 <\/p>\n

8. Using Red as an emphasis colour<\/strong><\/h2>\n

This is common in cases where you want the data to stand out. But be careful that colour has connotations<\/strong>. For example, Red means negative and can send your accountant in a spin! Also, your emphasis can be lost the minute someone prints the worksheet<\/strong> (if they use B&W, greyscale).<\/p>\n

 <\/p>\n

9. Be careful Merging cells<\/strong><\/h2>\n

This can be really troublesome for sorting and filtering data, as you can\u2019t easily \u2018fill down\u2019 formula over the merged cell. Cells are often merged as a design decision, but it\u2019s generally better to use Format Cells > Alignment > Center across your selection.<\/strong> This performs the same task if you\u2019re looking to improve the look of your data headers.<\/p>\n

 <\/p>\n

As with all suggestions, these are generalisations.<\/strong> There are always exceptions and Excel is delightfully flexible \u2013 that\u2019s its charm.<\/p>\n

We\u2019d love to know your favourite suggestions, especially any we haven\u2019t noted above.[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"

We were reminded of the risks of messing up spreadsheets in a recent blog post at Oracle: The scary fact they quote is that 88% of all spreadsheets\u00a0are wrong! The consequences of relying on bad information as a result can be insane!\u00a0That reminded me of a couple of lessons from years ago in terms of […]<\/p>\n","protected":false},"author":5,"featured_media":3587,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"We were reminded of the risks of messing up spreadsheets in a recent [wow_colorme]blog post at Oracle<\/a>[\/wow_colorme]:\n\nThe scary fact they quote is that [wow_colorme]88% of all spreadsheets<\/a>[\/wow_colorme] are wrong! The consequences of relying on bad information as a result can be insane!\u00a0<\/strong>That reminded me of a couple of lessons from years ago in terms of good spreadsheet design.\n\n \n

1. Check calculations<\/strong><\/h2>\nIt\u2019s so easy, especially when adding\/inserting new rows, to inadvertently sometimes miss out a \u2018sum\u2019 formula.\n\nAlways try to create cross-check calculations that make sure your formulas add up.<\/strong>\u00a0In this simple example, it\u2019s pretty obvious:\n\n\"\"\"\"\n\n \n\n \n\n \n\n \n\n \n\nAs your tables become more complex, maybe with blocks and sub-blocks, for example a P&L, then it becomes easier to make an error and more important than ever to add checks.\n\nIn this case, it's simply checking the verticals and horizontals match \u2013 one minus the other should be zero.\n\nExcel will often try to warn you of errors with a small hazard symbol, but I have found that it\u2019s not reliable.\n\n\"\"\n\n \n\n \n\n \n\n \n\n \n

2. Highlight inputs and outputs on your \u201cwhat-if\u201d models<\/strong><\/h2>\nTo make it easy for other people to use your complex models, it pays to highlight the fields that operators should complete, and even to group them together, as well as making the results clear.\n\n \n

3. Avoid inserting rows to make attractive spaces as a design feature<\/strong><\/h2>\nExcel thinks the data is in separate lists and will treat blank rows differently depending on the functions you need to use. For example, autofill will stop at a blank row as will formulas, so you could find your worksheet doesn\u2019t update as you expect.\n\n\"\"<\/a>\n\n \n\n \n\n \n\n \n\n \n\nAvoid inserting blank rows or columns in a worksheet, instead use formatting to emphasise key data<\/strong> which will help to make the worksheet easy to read and absorb.\n\n\"\"\n\n \n\n \n\n \n\n \n

4. Don\u2019t use Excel when you should be using other software<\/strong><\/h2>\nExcel is an amazing tool and can be bent to your will. That means many people try using it for things that go beyond what it was designed for<\/strong> , e.g. project plan and task management (doesn\u2019t have enough capability for anything beyond basic task management).\n\nThe main misuse is as a database. Excel is not a relational database, but is often used as one.<\/strong> Worksheets become unwieldy when used for complex project plans or to manage tasks, or to store raw data. This kind of use is fine for manageable sized lists, but if your users have to scroll or split the worksheet, then it\u2019s likely using Excel as a database is the wrong software and you should consider Access or SQL instead.\n\n \n

5. Printing the whole spreadsheet<\/strong><\/h2>\n\"\"\n\nIt\u2019s a pretty common occurrence that a user presses \u2018Print\u2019 and only then realises that the entire worksheet is printing, including any blank rows and columns. Instead, select the data you wish to print, then go to File > Print > Print Selection to print just that data.<\/strong> This will save the trees (as well as printer toner) and stops your colleagues from hating you for clogging the printer for ages.\n\n \n

6. Selecting the entire column when you apply formatting.<\/strong><\/h2>\nIt\u2019s easy to select an entire column or row in Excel by clicking on the header and shading it, or adding a line etc. But this can have unexpected side effects.<\/strong> For example, it can slow your PC down, impacting performance, and it can be a pain when printing. But the big one is confusing others, especially if they wonder if there is an error i.e. did you mean to make all the rows beneath your data bright yellow? Is something missing or is it a formatting error? It impacts productivity and creates a lack of confidence about your valid data\/formatting when others stop and question your data.<\/strong>\n\nInstead, just select the data not the entire column (or row) (or make a RANGE)\n\n\"\"\n\n \n\n \n\n \n\n \n

7. Use data validation in models<\/strong><\/h2>\nTyping free text may seem quicker at the time and Excel does a great job trying to help, especially with \u2018autofill\u2019 which tries to help you apply consistency. However this leaves it open for different user\u2019s different typing styles.\n\nInstead, use data validation to ensure users select the correct text label,<\/strong> e.g. company name. Go to Data > Data validation and set criteria that ensures users can only select the correct data.\n\n \n

8. Using Red as an emphasis colour<\/strong><\/h2>\nThis is common in cases where you want the data to stand out. But be careful that colour has connotations<\/strong>. For example, Red means negative and can send your accountant in a spin! Also, your emphasis can be lost the minute someone prints the worksheet<\/strong> (if they use B&W, greyscale).\n\n \n

9. Be careful Merging cells<\/strong><\/h2>\nThis can be really troublesome for sorting and filtering data, as you can\u2019t easily \u2018fill down\u2019 formula over the merged cell. Cells are often merged as a design decision, but it\u2019s generally better to use Format Cells > Alignment > Center across your selection.<\/strong> This performs the same task if you\u2019re looking to improve the look of your data headers.\n\n \n\nAs with all suggestions, these are generalisations.<\/strong> There are always exceptions and Excel is delightfully flexible \u2013 that\u2019s its charm.\n\nWe\u2019d love to know your favourite suggestions, especially any we haven\u2019t noted above.","_et_gb_content_width":"","footnotes":""},"categories":[4,2],"tags":[],"class_list":["post-3593","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-news","category-tips-tricks-and-hints"],"_links":{"self":[{"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=\/wp\/v2\/posts\/3593","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3593"}],"version-history":[{"count":0,"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=\/wp\/v2\/posts\/3593\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=\/"}],"wp:attachment":[{"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3593"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3593"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/new.kinetics.co.nz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3593"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}