These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, April 10, 2012

Excel Hotkey Upgrade

Business Description

Zions Bancorporation is bank holding company that operates over 500 full-service banking offices in Arizona, California, Colorado, Idaho, Nevada, New Mexico, Oregon, Texas, Utah and Washington. The Corporate Finance Department produces a variety of management reports including the annual budget, forecasted financial statements, bank stress tests, macroeconomic forecasts, regulatory capital ratios and liquidity analysis.

Business Problem

Each report produced by the Corporate Finance Department must be presented in a clean and professional manner as these reports are sent to key stakeholders including the executive management team, the Board of Directors, common stock investors and the Federal Reserve Bank and other bank regulators. Formatting an Excel file to appear clean and professional can often take longer than retrieving the data from a database or performing calculations within Excel. Improvements in the speed at which Excel users are able to format their documents can, therefore, greatly improve the output of the department as a whole.

Although we do not often think about it, the seemingly small amount of time each user requires to reach over to the mouse, select a cell range, click on the appropriate ribbon and button and move his/her hands back to the keyboard can add up to a significant amount of time throughout the day. This amounts to lost productivity for the user and the department as a whole.

Solution: Excel Hotkey Upgrade

Excel hotkeys are one method to increase the speed at which users format reports in Excel. Some of the most popular hotkeys include ctrl + B to make the font bold, ctrl + C to copy a range and ctrl + V to paste a copied or cut range.

For my final project in MBA 614, I decided to build an Excel Add-In that would create new hotkeys that will speed up other frequently used formatting properties in Excel. The macros in this file perform the following functions and are called by the following keystrokes:

  1. Ctrl + Shift + B: Toggles through a variety of different border options for the selected range.
  2. Ctrl + K: Toggles through a variety of interior color options for the selected range.
  3. Ctrl + Shift + K: Toggles through a variety of formats for the numbers in the cells of the selected range.
  4. Ctrl + J: Auto-fits all the rows in the selected range.
  5. Ctrl + Shift + J: Auto-fits all the columns in the selected range.
  6. Ctrl + Shift + H: Turns on and off the view gridlines property in the selected sheet.
  7. Ctrl + N: Decrease the font size of the cells in the selected range.
  8. Ctrl + Shift + N: Increase the font size of the cells in the selected range.
  9. Ctrl + M: Decrease the column width of the selected range .
  10. Ctrl + Shift + M: Increase the column width of the selected range.
  11. Ctrl + L: Decrease the row height of the selected range.
  12. Ctrl + Shift + L: Increase the row height of the selected range.
Implementation of the Solution

To activate the file on a user’s computer, all the user needs to do is download the xlsm file attached with this document, save it as an Excel Add-In, go into the Add-Ins subsection of the Excel Options menu and activate the Add-In once it has been saved. It should be saved in a location where it will not need to be moved as this will stop the functionality of the add-in.

To access the file, click on the link below:

No comments:

Post a Comment

Blog Archive