This is the help page for the Formatting Shortcuts Add-in.
Please see the Excel Add-in Installation Guide for details.
The Formatting Shortcuts Add-in allows you to create keyboard shortcuts to apply cell formatting to the selected cell(s). Formatting options include:
- Font Color
- Fill Color
- Font+Fill Color
- Cell Style (full version only)
Once the add-in is installed you will see the Formatting Shortcuts button in the Add-ins tab of the Ribbon. Pressing this button opens the Formatting Shortcuts window.
Setup Keyboard Shortcuts
Keyboard shortcuts can be setup and changed with the following options:
- Key – You can choose and enter any letter in the text box of the key fields. The key must be one character in length and be a letter character. Number key should NOT be used. However, symbols do work because the Shift key is part of the keyboard shortcut.
- Format Type – The format type determines what formatting properties will be applied to the selected cell(s) when the keyboard shortcut is pressed. Options in the drop-down menu include:
- Font – Changes the font color only.
- Fill – Changes the fill color only.
- Font+Fill – Changes both the font and fill colors.
- Cell Style (full version) – Changes the cell style. Cell styles can contain all formatting properties of a cell including:
- font color
- fill color
- text formatting (bold, italics, underline, etc.)
- border properties (color, weight, line style, etc.)
- alignment (left, right, centered, etc.)
- cell protection
- Cell Format / Style – The boxes contains an example of what the formatting should look like when the keyboard shortcut is pressed.
How to Change the Format
- Click the format box (to the right of the Format Type drop-down menu).
- Select a cell that contains the formatting you want to save.
- Click OK on the Change Cell Formatting pop-up window.
- Formatting is saved. Click Save & Close button and start using your keyboard shortcut.
Note, not all properties for a cell style will be visible in the Cell Format boxes, but they are still applied correctly when the keyboard shortcut is pressed. Borders, font size, number formatting, etc. will not be visible in the format boxes. The style name will appear, and the keyboard shortcut will apply the formatting for that cell style. Cell styles can be modified in the Cell Styles menu on the Home tab.
Use the Keyboard Shortcuts
To use the keyboard shortcuts:
- Select the cell(s) you want to apply formatting to.
- Press the keyboard shortcut by pressing and holding the keys in the following order:
Ctrl + Shift + A (or the key you designate in the setup)
- The cell formatting will be applied. You can use the Ctrl+Z or the Undo button to undo. See undo options below.
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.
The Send Keys method (Preserve Undo Stack for Styles) can be enabled/disabled in the Options menu. You can disable this option if you have any issues with the keyboard shortcuts not being applied to the cells properly.
The Delay for Send Keys is a time value in seconds that can be changed. The send keys operation is dependent on the speed of your computer, and you might need to add some delay time to get it to work properly. If you get the letters “est” in a cell after pressing the keyboard shortcut, increase the delay time and try again. Please let me know if you have any questions or issues with this feature.
Single Undo for Font & Fill Formatting
The send keys method described above does not work when you choose Font, Fill, or Font+Fill as the formatting option. However, I did add an undo code that will allow you to undo the changes made with the keyboard shortcut to apply formatting. For example, if you press Ctrl+Shift+A to change a cells font to red color, you will still be able to undo this change by pressing Ctrl+Z or the undo button. But you are limited to this one undo. All the actions you performed prior pressing the keyboard shortcut will be lost.
If you want to have the ability to undo several changes, it is best to use the Cell Styles formatting option. You can easily create custom styles to mimic the Font & Fill formatting options. So if you just wanted to have a keyboard shortcut to fill a cell with red background, you could create a style for this and assign a keyboard shortcut to it. Setting the format type to Cell Style, instead of Font, would give you the full undo history.
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.
New features will be based on your feedback and requests. So please don't hesitate to leave a comment or contact me with anything you'd like to see added. You request it, I'll do my best to create it. Now if we could just get a keyboard shortcut to make a cup of coffee… 🙂
The add-in is available for download on the Formatting Shortcuts Add-in page.