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