This is the help page for the Formatting Shortcuts Add-in.
Install
Please see the Excel Add-in Installation Guide for details.
Overview
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.
Guide
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.
Contact Me
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… 🙂
Download
The add-in is available for download on the Formatting Shortcuts Add-in page.
Hi Jon,
Can we have more than 12 option for fill/font color and instead of using Ctrl+Shift combination, we should have Alt/Ctrl+number combination as shortcut key?
Thanks for the suggestion Deepak!
I have installed this Add-in, but whenever I open any excel file, it doesn’t load automatically.
I have to double click the file of this software everytime.
please provide me solution.
Hi Hitendra,
I just published a video on how to install an Excel Add-in that might help. Check it out and let me know if you have questions. Thanks!
just realized that in office 365, excel, you also have to add the location of the addin in the trust center setting to see it.
Hi Len,
That is due to a recent Microsoft Office Security Update. Here is an article that explains how to fix disappearing add-ins. You have to add the add-in to the trust center, as you said. Some users also have to Unblock the add-in, which is a new property of files that are downloaded from the internet. Thanks again!
Hi Jon,
My add-in consistently disappears when loading Excel 2016. Any suggestions for that?
Thanks,
Qua
Hi Qua,
Sorry to hear that. That can happen if Excel crashes on you. Here is a video on how to re-enable an add-in when it disappears on you. I hope that helps.
Jon, is current paid version 2.5?
Hi Leonard,
My apologies for not getting back to you sooner. Yes, the latest version is 2.5.
I have noticed that each time I use my keyboard shortcuts to apply my preset formatting choices, there’s about a 30% chance that the Num Lock turns itself off. It happens with different formatting shortcuts that have varying number types, so I cannot narrow down why this is happening. It also only turns the Num Lock off–never back on again. I’m using Excel 2013 on Windows 7. Any suggestions?
Everytime I open an excel file there is always a notification box that says ” A file named ” EC_Formatting Shortcuts_Lite .xlsm” already exists in this location. Do you want to replace it” How to prevent this from happening over and over again everytime i open an excel file
I also have this problem. Is there a fix for it? Also, now on the latest Office 365 (as at June 2020), my Excel opens multiple instances every time I turn my computer on. All of those instances need to be manually closed – each one with closing this error. Very frustrating. Any fix?
This happens to me too, any way to fix it ?
How can I delete this from Excel 365?
Sir, 25th Jan.2021.
Very clearly defined the topic ” how to Add-In Topic “.
I appreciate your efforts.
Thanking you and awaiting more ideas in near future too.
kanhaiyalal Newaskar.
For some reason, this add-in seems to automatically turn numlock on whenever Excel launches (but only if this particular add-in is enabled).
I find this annoying as I rely on numlock being off for other parts of my PC workflow.
Any chance you could fix/remove this bug/feature?
I followed the instruction, but … when I pushed the ‘Save & Close’ button, I received an error msg:
‘Two or more Keys are assigned the letter: D between the formatting Shortcuts tool and the Data Picker Tool.
Each key should contain a unique character.’
I subsequently tried a few other keys and styles with the same result. 🙁
Would appreciate your support.