HUMAN Capital - Summer 2015 - (Page 8)

TECHTALK Using Spreadsheets: My One Professional Regret BY ADAM CZARNECKI, BA, CHRP I have learned quite a few things over the course of my human resources career. I have also made mistakes that make me cringe when I reflect upon them and sometimes wish time travel was possible-but all were good learning opportunities. However, I do have one professional regret that I wish I could go back and redo. That regret is creating the vast majority of my human resources forms in MS Word instead of MS Excel. Upon reflection about why I always started with Word, the simple answer was, "because that's how it has always been done." Manuals, policies, reports, investigations and interviews are all done using Word documents so it is just a natural step to create forms in Word. They are esthetically pleasing, fast and easy to create; however, you end up with a static document that requires additional manual analysis. A performance review is an excellent example of how Excel can create a superior product over Word, an example can be found here or on my LinkedIn profile. Other than the automatic computation and analysis Excel can do, here are some tips for creating superior forms with Excel. Once you have created your form, give it a white canvas look on the computer screen as a final product instead of the default grey boxes by changing the color of the grid from grey to white. This gives the impression of a clean white document. The #DIV/0! Error can be distracting to a user before data is entered into Excel. You can easily hide these error notifications with conditional formatting. Click on the cell (or multiple cells) with the error, from the home tab click on "Conditional Formatting", "New Rule", choose the second option "Format only Cells that Contain," from the drop down choose "errors," and change the font to white. Now the font of the error is white and appears as an empty cell to the user, when data is entered it will automatically change it to a black font. 8 | Control which cells a user can enter data in. Hold the "CTRL" button and click on all the cells that you want the user to be able to enter data in. Then right click on one of the cells, "Format Cell", click on "Protection" and uncheck the "Locked" option, then "ok." Now click on the Review Tab on the ribbon, and then "Protect Sheet." I recommend not putting in a password as 1) you'll forget, and 2) most users do not know how to unprotect it. Then uncheck "Select Locked cells." This will now protect the document and only allow specific cells to be fillable, avoiding the chance of a user accidently deleting or changing a formula in a cell. In addition, it makes the filling of the form faster as the user can "Tab" through the cells instead of clicking on them. Hide your side calculations in two ways. You can either change all your calculations from black to white font or use the Hide feature. Highlight the Columns you want to Hide, right click and select Hide. Now the user will not be able to see or access those cells. The most valuable use in Excel is creating check boxes that can provide values and calculate formulas and scores. It is complicated; however, a quick google search will show you how and this will evolve your forms to a new professional level. When I demonstrated the new performance review forms to my managers-the checkboxes, on the fly calculations and analysis-they were genuinely excited and motivated to start using this new tool. When was the last time your managers was excited about using a new form from the HR department? O Adam Czarnecki, BA, CHRP, is a member of the Senior Management team of a heavy duty truck dealership group in Alberta where he is responsible for HR, H&S and IT. He is an HRIA Board Member, a HR instructor at the University of Calgary and Mount Royal University. He can be reached at

Table of Contents for the Digital Edition of HUMAN Capital - Summer 2015

Leadership Matters
Tech Talk
Seven Best Practices to Improve Candidate Care Through the Recruitment Process
Diversity and Inclusiveness in Today’s Workplace
Navigating the Current Job Market: Students, Professionals and Employers
The Meaning of Turnover
Changes in the TFW and LMIA Process
The Changing Face of Training and Development
Policy Corner
Legal Source
Suppliers Guide
Index of Advertisers/

HUMAN Capital - Summer 2015