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

Wednesday, April 11, 2012

Excel Custom Shortcuts


Background
Prior to attending the BYU MBA program I worked at a management consulting firm in Salt Lake City.  Being able to work quickly in Excel is a vital skill for an Analyst in the consulting industry.  The firm that I worked for developed a proprietary Excel productivity add-in that contained custom tools and shortcuts to enable their analysts to work more efficiently.

Problem Description
In May of this year I will be joining Intel’s finance department and performing similar financial analyses and model building using Excel.  Custom shortcuts can greatly reduce the time required to perform routine actions, such as cell formatting or cleaning messy data.  Unfortunately as mentioned above, the Excel add-in I enjoyed at my previous firm is unavailable to me.  This could result in me not being as effective as possible when building models and formatting financial spreadsheets at my new job.

Overview of Solution
Recalling some of my favorite custom shortcuts, I decided to make an attempt to rebuild their functionality from ground up.  This will enable me to save time when working in Excel and maximize my job performance.  To create a professional look and feel, I added my own custom menu to the Excel toolbar using the CommandBar object, as well as custom keyboard shortcuts for some macros.
The custom shortcuts contained in this add-in are as follows:

Shortcut Name
Key Stroke
Description
Reverse Text
Ctrl T
Switches color format of text & background
Trim
N/A (access via menu)
Removes spaces preceding and following the text
Format as Header
Ctrl Q
Centers and bolds text
Comma Style
Ctrl Shift M
Comma format no decimal
Currency Style
Ctrl Shift D
Dollar format no decimal
Center Across Bold
Ctrl J
Center across selection and bold
Toggle Gridlines
Ctrl Alt G
Toggle the gridlines
If Is Error
Ctrl E
Replaces errors with “” (blank)
Copy as Picture
Ctrl G
Copies selection as a picture
Save & Close
Ctrl Shift W
Saves and closes the workbook

To use this add-in download the “CustomShortcuts.xla” file and place it in the following directory: 

C:\Users\YOURUSERNAME\AppData\Roaming\Microsoft\AddIns

The last step is to open the office menu, select Excel Options, click the Add-Ins item in the list of the left, make sure the Manage dropdown list at the bottom shows Excel Add-Ins, and then press the Go button. Finally this brings up the add-ins dialog box.  Simply click the checkbox next to “Custom Shortcuts” and the add-in will be enabled.

To download the add-in and documentation clink the links below:

No comments:

Post a Comment

Blog Archive