{"id":1617,"date":"2013-04-15T13:09:18","date_gmt":"2013-04-15T13:09:18","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=1617"},"modified":"2023-12-30T23:26:52","modified_gmt":"2023-12-30T23:26:52","slug":"get-back-control-of-your-excel-spreadsheets","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/get-back-control-of-your-excel-spreadsheets\/","title":{"rendered":"Get back control of your Excel spreadsheets"},"content":{"rendered":"<p><span style=\"line-height: 1.714285714;font-size: 1rem\">Poor Excel skills are costing UK businesses millions in lost revenue. According to an <a title=\"Finance groups lack spreadsheet controls\" href=\"http:\/\/www.ft.com\/cms\/s\/0\/60cea058-778b-11e2-9e6e-00144feabdc0.html#axzz2OYZ8nChH\" target=\"_blank\" rel=\"noopener\">article in the Financial Times<\/a>\u00a0more than half of financial service groups have \u201cpoorly applied or no controls for managing business critical spreadsheets\u201d.<\/span><\/p>\n<p>The article blamed losses in part due to \u201cavoidable errors in MS spreadsheets\u201d. So, what could thousands of UK businesses do differently?<\/p>\n<h2>THE PROBLEM: &#8220;Fat finger&#8221; input mistakes<\/h2>\n<p><span style=\"line-height: 1.714285714;font-size: 1rem\"><a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2013\/04\/fingers-on-keys.jpg\"><img decoding=\"async\" class=\" wp-image-1693 alignright\" alt=\"fingers on keys\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2013\/04\/fingers-on-keys.jpg\" width=\"298\" height=\"298\" \/><\/a>Excel spreadsheets are an integral part of many UK businesses. Used for anything from accounting to CRM, they are a system with a low barrier to entry. Business users start one up and start recording data.<\/span><\/p>\n<p><span style=\"line-height: 1.714285714;font-size: 1rem\">Mistakes in Excel, however, can often be harder to spot than those in <\/span><a style=\"line-height: 1.714285714;font-size: 1rem\" title=\"Word Training London\" href=\"https:\/\/www.stl-training.co.uk\/b\/wordtraining\/2012\/11\/27\/speed-up-editing-and-proofreading-with-word-2010\/\" target=\"_blank\" rel=\"noopener\">other MS applications<\/a><span style=\"line-height: 1.714285714;font-size: 1rem\">. Though error messages will appear for misspelt formulae, they won\u2019t pick up incorrectly populated fields. Help is at hand though, here\u2019s how to reduce the risk of &#8220;fat finger&#8221; input mistakes:<\/span><\/p>\n<h2>THE SOLUTIONS:<\/h2>\n<h3>Data Validation<\/h3>\n<p>All but eliminate the room for error with\u00a0<a title=\"Microsoft Excel Training\" href=\"https:\/\/www.stl-training.co.uk\/article-2064-how-restrict-data-input-using-data-validation.html\" target=\"_blank\" rel=\"noopener\">data validation formatting<\/a>. This Excel feature will allow you to:<\/p>\n<ul>\n<li><span style=\"line-height: 1.714285714;font-size: 1rem\">Make a list of possible entries, restricting the values allowed in a cell<\/span><\/li>\n<li><span style=\"line-height: 1.714285714;font-size: 1rem\">Create an automated message when incorrect data has been inputted<\/span><\/li>\n<li><span style=\"line-height: 1.714285714;font-size: 1rem\">Set a range of numeric values that can be entered into cells<\/span><\/li>\n<li><span style=\"line-height: 1.714285714;font-size: 1rem\">Determine if an entry is valid based on calculations of other cells<\/span><\/li>\n<\/ul>\n<p>By restricting the values allowed in a cell and setting formatting properties, you\u2019ll have tighter control over editing functions and are likely to experience fewer cases of fat finger mistakes. Sure, these will still be made, but they\u2019ll be visible right away.<\/p>\n<p>To view a step by step guide of how to tutorials, visit the <a title=\"Office Support Centre\" href=\"http:\/\/support.microsoft.com\/kb\/211485\" target=\"_blank\" rel=\"noopener\">Microsoft Office support centre<\/a>.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2013\/03\/data-validation.gif\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-1619\" alt=\"data validation\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2013\/03\/data-validation.gif\" width=\"633\" height=\"200\" \/><\/a><\/p>\n<h3>VLOOKUP<\/h3>\n<p><img decoding=\"async\" class=\"alignright size-medium wp-image-1620\" alt=\"vlookup\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2013\/03\/vlookup-300x190.gif\" width=\"300\" height=\"190\" \/><\/p>\n<p>If there are two tables of data that need to be cross-referenced, or you need to pull data from one table to another, don\u2019t copy and paste. <a title=\"MS Excel Training\" href=\"https:\/\/www.stl-training.co.uk\/article-1709-use-vlookup-in-excel.html\" target=\"_blank\" rel=\"noopener\">VLOOKUP<\/a> is arguably the most useful function in Excel, and mastery of it will ensure that exactly the right data ends up in exactly the right place.<\/p>\n<h3>IFERROR<\/h3>\n<p>Pre-empt the fact that your formulas may run into errors. Assume they will and use the IFERROR function in Excel to provide a \u2018catch\u2019 scenario \u2013 replacing the error with a blank or zero for instance.<\/p>\n<p>IFERROR\u2019s are a quick and easy way to see null values, often a result of human error, whether that\u2019s a formula, data entry or another error.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2013\/03\/iferror.png\"><img decoding=\"async\" class=\"aligncenter size-medium wp-image-1621\" alt=\"iferror\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2013\/03\/iferror-300x295.png\" width=\"300\" height=\"295\" \/><\/a><\/p>\n<h2>THE PROBLEM: No audit trail<\/h2>\n<p>With poor controls over quality control being blamed for huge monetary losses, auditing things like who has edited a workbook can be a useful way of keeping track of minor changes, that could have major consequences for your business. Excel offers a number of solutions to best fit your company\u2019s skills set.<\/p>\n<h2>THE SOLUTIONS:<\/h2>\n<h3>Workbook tracking<\/h3>\n<p>Quickly and simply see the changes made to your shared workbooks by tracking changes. This is great for organisations with multiple editors, allowing each author to see the additions and overwritten fields upon reopening the spreadsheet.<\/p>\n<p>Excel offers three methods of workbook tracking:<\/p>\n<ul>\n<li><strong style=\"line-height: 1.714285714;font-size: 1rem\">On-screen highlighting<\/strong><\/li>\n<\/ul>\n<p>A great way to see changes quickly, with edited boxes highlighted in a different colour for each user. Hold the cursor over the changed cell to see a brief description of the edit. It\u2019s ideal for at a glance reviewing or for workbooks with few changes.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2013\/03\/HIGHLIGHT.jpg\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-1622\" alt=\"On screen highlight\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2013\/03\/HIGHLIGHT.jpg\" width=\"489\" height=\"231\" \/><\/a><\/p>\n<ul>\n<li><strong><span style=\"line-height: 1.714285714;font-size: 1rem\">History tracking<\/span><\/strong><\/li>\n<\/ul>\n<p>Excel can produce a separate history worksheet that provides a list of change details which you can filter and search for. This method is ideal for worksheets that have incurred a series of changes.<\/p>\n<ul>\n<li><strong><span style=\"line-height: 1.714285714;font-size: 1rem\">Reviewing of changes<\/span><\/strong><\/li>\n<\/ul>\n<p>If you\u2019re evaluating comments from other users, this method is especially useful. Excel can step you through the changes made in sequence so that you can decide whether to accept or reject the amendment.<\/p>\n<h2>Utilising Excel systems<\/h2>\n<p>You can also use Excel systems as a way of preparing reports or standardising systems in more detail.<\/p>\n<h3>Creating reports using PivotTables<\/h3>\n<p>There\u2019s no faster or more convenient way to generate dynamic reports. Better still you can build controls so end users can manipulate the results with ease.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/09\/Excel-Pivot.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-218\" alt=\"pivottables in excel\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/09\/Excel-Pivot.png\" width=\"562\" height=\"316\" \/><\/a><\/p>\n<h3>Automation with macros and VBA<\/h3>\n<p>Performing repeated tasks in Excel can be tedious, which in turn can lead to laziness and human error. Macros capture repetitive tasks for easy playback.<\/p>\n<p>The language that feeds macros is VBA. It\u2019s a programming language that sits alongside Excel. It allows you to program and automate processes and while the learning curve is steep, it opens up the possibilities in Excel exponentially. The amount of human error reduced and time saved is staggering.<\/p>\n<h2>Conclusion<\/h2>\n<p>In training and consulting scenarios we have seen spreadsheets with errors and gaps in them that are waiting to be exploited. But with just an hour or two&#8217;s education we have been able to transform leaky models and flabby formulas into watertight, lean applications.<\/p>\n<p>We&#8217;ve been training Excel for years and are proud of our <a title=\"98% recommend us\" href=\"https:\/\/www.stl-training.co.uk\/best-stl-training-review.php\">98%+ recommendation rate<\/a>, delivering value with every course. Here&#8217;s what a representative of Credit Suisse had to say about an <a title=\"excel training london\" href=\"https:\/\/www.stl-training.co.uk\/microsoft\/excel-training-london.php\">Excel Advanced<\/a> course:<\/p>\n<p><em>&#8220;There were functions in Excel that have always been there until today I had no idea what purpose they served. The trainer was very informative, knowledgeable, pleasant to work with and above made the session very well run.&#8221;<\/em><\/p>\n<p>Anyone concerned that their spreadsheets are the weak point in their projections should put some research into <a title=\"excel training london best stl\" href=\"https:\/\/www.stl-training.co.uk\/microsoft\/excel-training-london.php\">Excel training<\/a>, and stop financial losses today.<\/p>\n<p><em>Intrigued? <a title=\"contact us best stl\" href=\"https:\/\/www.stl-training.co.uk\/contact\/index.php\">Get in touch<\/a> with Best STL today to discuss your training needs. Our <strong>Advanced Excel Courses London\u00a0<\/strong>are a fantastic way of getting acquainted with the higher functions of MS Excel.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Poor Excel skills are costing UK businesses millions in lost revenue. According to an article in the Financial Times\u00a0more than half of financial service groups have \u201cpoorly applied or no controls for managing business critical spreadsheets\u201d. The article blamed losses in part due to \u201cavoidable errors in MS spreadsheets\u201d. So, what could thousands of UK [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[144,171,308,419,585],"class_list":["post-1617","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-data-visualisation","tag-excel","tag-iferror","tag-pivottables","tag-vlookup"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1617","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=1617"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1617\/revisions"}],"predecessor-version":[{"id":6854,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1617\/revisions\/6854"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=1617"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=1617"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=1617"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}