Excel Formatting Shortcuts Add-in

The Formatting Shortcuts add-in allows you to create keyboard shortcuts to quickly apply font colors, fill colors, or cell styles to the selected cell(s).  Cell Styles include: font color, fill color, number formatting, text formatting, border properties, alignment, cell protection, etc.

This add-in will be a huge time saver for anyone that does any type of financial modeling or data entry.  The video below contains many examples on how the add-in can be used to make you more efficient with keyboard shortcuts.

Video

Easy to Use

The add-in adds a “Formatting Shortcuts” button to the Add-in tab on your ribbon.  Clicking the button opens the following form where you can customize the key for the keyboard shortcuts and the format type.  The cell format can be changed by simply clicking the Cell Format button, then selecting a cell in your workbook that contains the formatting you want to apply when the keyboard shortcut is pressed.

Pressing the keyboard shortcut will apply the saved formatting to the selected cell(s).  It's that easy!

Format Types include:

1. Font – Applies the font color to the selected cell(s).
2. Fill – Applies the fill color (cell shading or background) to the selected cell(s).
3. Font+Fill – Applies both the font and fill color to the selected cell(s).
4. Cell Style – Applies the cell style to the selected cell(s).  Formatting for cell styles includes:
   – font color
   – fill color
   – number formatting
   – text formatting (bold, italics, underline, etc.)
   – border properties (color, weight, line style, etc.)
   – alignment (left, right, centered, etc.)
   – cell protection

Preserve the Undo Stack

One really great feature of this add-in is the ability to preserve the undo stack.  Running a macro that modifies anything in the workbook will typically clear the undo stack.  The undo stack is the stored history of actions that you are typically able to undo with the undo button or Ctrl+Z.  Clearing of the undo stack can be a major issue with macros or add-ins because you are not able to undo changes that could have been made by an accidental button press.

This is especially true with applying cell formatting.  Most of the time when I'm designing a model, I go through a lot of iterations with cells styles and formatting to determine what looks best.  I constantly use Ctrl+Z to undo changes.  So I worked really hard to figure out a way to keep this functionality with this add-in.

Full Undo Preserve for Cell Styles

The full undo stack is preserved when you create and use a keyboard shortcut with the Cell Styles format.

This is done by using the Send Keys method to apply the formatting.  Basically, the formatting of the cell style is stored in the add-in worksheet, and when you press the keyboard shortcut, the macro copies the stored cell and pastes the formatting into the selected cell(s) in your worksheet.  The paste is done with the send keys method and uses the keyboard shortcut Alt+E+S+T to paste formats.

This is definitely a workaround.  It works well, but I really can't guarantee that it will work in every instance.  So there is a way to turn it off in the Options menu.  When the Preserve Undo Stack for Styles option is turned off, the undo will function like the other formatting options described below.

Benefits of Using Cell Styles

The other great benefit of cell styles is that you can change the formatting of the style, and all cells that contain that formatting will automatically be changed.  So if you have a model with “input cells” that are shaded blue, and you want to change the shading to green, you just need to modify the cell style and all the cells in the workbook will be updated.  The Formatting Shortcuts add-in will also be updated so your keyboard shortcut will apply the newly modified style.

This can be a huge time saver.  I was recently working with a large model where the users decided they wanted to see the numbers in thousands instead of whole dollars.  There were thousands of cells throughout the workbook that needed to be changed.  Fortunately those cells were already formatted with a custom cell style.  So the change took about 10 seconds.  All I had to do was modify the number formatting of the style and all the cells in the workbook were updated.

Download the Lite Version

 

Full Version

The full version of the Formatting Shortcuts add-in is now a part of our Hero Tools Suite which combines all of our add-ins into one add-in with over 100 features.

Click Here to Learn More about Hero Tools

Have Questions?

Here is the help page for the Formatting Shortcuts Add-in.

Please contact us if you have any other questions.

Thanks! 🙂