A great deal of analytical activity is performed using Excel spreadsheets. Their simplicity and power make them an ideal analytics environment, albeit one fraught with risk. Organizations are supporting critical business objectives with Excel, so Risk Management is imperative in ensuring that these workbooks perform as intended. Eliminating risk completely is impractical, but simple risk reduction ideas can improve the quality of spreadsheets. I have been using spreadsheets for almost 30 years and have some simple suggestions for mitigating the following three risks in Excel 2010: Data Security Risk, Calculation Risk and Data Entry Risk.
Data Security Risk
Depending on the level of sensitivity of your data, there are different approaches to protecting it from spreadsheet users. A simple way to protect an Excel worksheet is to hide it by right-clicking on the worksheet and selecting Hide. Unfortunately, many Excel users know how to unhide a worksheet by right-clicking on any worksheet and selecting Unhide.
The next level of security is to “Very Hide” a worksheet. This can be achieved through Excel’s Visual Basic for Applications (VBA) environment. Hold the Alt key and press F11 (Alt-F11) to toggle between the spreadsheet and the VBA environment. Once in VBA, make sure the Project Explorer (Ctrl-R to show) and Properties window (F4 to show) are visible. Click on the worksheet that you want to hide in the Project Explorer and its properties will appear in the Property Window. The property of interest is called Visible, use its drop-down menu to select ‘2 – xlSheetVeryHidden’. The worksheet is now hidden and cannot be Unhidden from the spreadsheet screen (can only be Unhidden from VBA). This is a great way to hide a worksheet – just don’t forget that you’ve hidden it!
Another way to protect data is to use a database such as Microsoft Access. You can have a secured database which holds the data in tables and another database which grants permission to selective views of that data for use in Access or Excel. Databases offer greater levels of security than spreadsheets.
Calculation Risk
Nobody’s perfect. Studies have shown that a high percentage of spreadsheets contain errors. When thousands of cells are involved in a model, it’s not uncommon to create a formula that returns an incorrect result, especially when the formula is lengthy and difficult to read. A formula can be easier to write and read when cell references are replaced with named ranges.
Take a look at this formula:
And this one:
They are the same formula but the shorter one is using named ranges instead of cell references for the calculation. Formulae that are easier to read are less likely to contain errors. To replace cell references with named ranges, click on the Name Manager button on the Formulas tab. Click the New button to open the New Name dialog box. Enter a name in the Name: field, click on the corresponding cell in the Refers To: field then click OK on the New Name dialog box and click Close on the Name Manager. When you write a formula and click on the newly named cell, the name is used in place of the cell reference. Named ranges make sense for cells that are referred to a lot, such as a period ending date or a region growth factor.
Data Entry Risk
Garbage in, garbage out. In order to make good decisions, model insights must be based on good data. The easiest way to validate data entry in Excel 2010 is to use the Data Validation feature on the Data tab.
Choose a cell to apply constraints to then click the Data Validation button to open the Data Validation dialog box. Click on the Settings tab and in the Allow: field, select Decimal. Set the Data: field to ‘between’, Minimum: to 0 and Maximum: to 1. Click OK to close the dialog box and then test the cell. Only numbers between 0 and 1 can be entered in this cell, which is useful for percentages.
Another popular use of Data Validation is to create a drop-down list like in the example pictured below. Click on cell E3 then click the Data Validation button. From the Allow: field, select List to display a field called Source: and choose the range of cells that contains the values for the drop-down list (=$A$1:$A$4).
Cell E3 is now a drop-down list that will only accept the specified values.
Access Database Forms are another great way to improve data entry. Date fields can use Calendar Controls to alleviate any confusion in calendar date entry. Some regions of the world read 7/2/2013 as July 2, 2013 but others see February 7, 2013. The Calendar Control ensures that the format will not affect the date entered.
If you like these tips and you’re interested in learning more about how Excel VBA can improve your Excel experience or how Access Databases can help you manage and manipulate data, I encourage you to take a look at these courses offered at the CMA Professional Development Institute.
Scott Cuthbertson BBA
Scott is an Analytics Professional who has worked with over 100 clients in the retail and financial services industries. He has helped organizations improve their labour scheduling, inventory replenishment, performance management, organization design and pricing. He has been a practitioner of analytics since childhood, using data models to win Hockey Pools. His expertise lies in developing data models, reporting, algorithms and solutions for any organization that wants to use their data to improve decision making. He also teaches at the CMA Professional Development Institute.
If you are interested in analytic solutions or training:
Email:
scottjcuthbertson@gmail.com
LinkedIn:
http://ca.linkedin.com/in/scottcuthbertson
Blog:
http://theanalyticspro.blogspot.ca/
© Scott Cuthbertson 2013
Blog: Words From The Analytics Pro
