As a Compensation Practitioner, I’m familiar with spreadsheets; and I’m even more familiar with huge spreadsheets. For practitioners without the luxury of specialized software to manage the workflow of large scale salary changes and/or job changes, there may be a real struggle to balance the amount of information provided to managers, usually in excel spreadsheets.
Providing too little information may result in uninformed decisions, and providing too much information may overwhelm your end-user.
Consider, for example, the amount of information required for year-end planning activities. Many organizations consolidate annual promotions, merit increases, market adjustments, bonuses, etc. into a single event, often times managed through one a single, large spreadsheets that may be overwhelming at first glance.
This article will describe how to incorporate visual basic (it’s not that hard, I promise…) to aid in the balancing act of providing enough information without overwhelming the user.
For the purpose of this article, I am using a fictitious end of year planning workbook with several employees, which can be downloaded here:http://justin-hampton.com/excel-tips/simplify-huge-spreadsheets. This workbook presumes the manager will review personal employee information, incentive plans, make merit recommendations, market adjustment recommendations, and promotional recommendations. While this article looks through the lens of a compensation practitioner, the concepts can be applied to any large spreadsheet with naturally occurring sections.
In our worksheet, the columns are laid out as such:
- Columns A:J provides details about the employee
- Columns K:R provide details about the employees incentive plans
- Columns S:Z detail the merit information
- Columns AA:AC are for market adjustments and
- Columns AD:AL are for the Promotions
Since we are focusing on how to hide and unhide columns, we will assume that all of the data is correct and that the formulas within the spreadsheet are correct and make good, logical sense.
Creating the Trigger (Button)
The first thing one needs to do is to create the button that the end-user will click to toggle the columns from hidden to unhidden, or vice-versa. To create the button, find the “Developer” tab in the ribbon and choose “Insert” (the button with the toolbox). Once you click the toolbox, a drop down will appear, and the user should select the form button, as found on the top left of the image, below:
Once you have dropped the button into the spreadsheet, the next step is to apply the visual basic code. DON’T WORRY! IT’S REALLY EASY!
Adding the VBA to the Button
To open the VBA environment, press and hold ALT+F11. This will open up a window that looks like the figure below. To add the VBA code into the workbook, double click “ThisWorkbook” in the top left window of your VBA environment. Once you click ThisWorkbook, the grey area will turn white and you will be able to input text into this field.
This is where we will add the code that lets us assign our VBA to the button.
For this exercise, I am just going to copy and paste the code that I’ve provided, below, and I’ll explain how to update the code to meet your needs:
The first line is the name of the VBA and can be what you’d like it to be. For my project, I’ve called it Hide_IncentPlan. The Sub and the closing parentheses () are part of the code and must remain.
In the first line of code, we are saying that if columns K through R are not hidden, hide them. The second line says if the lines of code are hidden, go to the next action, which is the third line of code. The third line of code is telling Excel to unhide the columns.
Making changes to the code can be done by changing the range (“K:R”) in this case, and the worksheet (“End of Year Worksheet”) in this case.
If you would like to create multiple column ranges that can be toggled between hidden and unhidden, then coping and pasting this code repeatedly – and then changing the following elements:
- Sub Name (Hide_IncentPlan),
- Worksheet: “End of Year Worksheet”
- Column Range: “K:R”
Once you have completed creating your different ranges and adding the appropriate buttons, you should be left with a worksheet where end-users can simply click a button to hide or unhide the different sections that they are reviewing.
By partitioning out the different sections (incentive plans, merit, market adjustments, and promotions) we have found a way to providing managers enough information to make informed decisions, but have organized it in such a way that they are not overwhelmed.
The worksheet, above, can be found here: http://justin-hampton.com/excel-tips/simplify-huge-spreadsheets