Formatting Shortcuts Add-in Help

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.

Formatting Shortcuts Exce Button Userform

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

 

Formatting Shortcuts Update Format

    1. Click the format box (to the right of the Format Type drop-down menu).
    2. Select a cell that contains the formatting you want to save.
    3. Click OK on the Change Cell Formatting pop-up window.
    4. 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:

  1. Select the cell(s) you want to apply formatting to.
  2. 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)Formatting Shortcuts Keyboard Shortcut
  3. 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.

Formatting Shortcuts Select Cell Style

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.

Preserve Undo Options
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

Excel Cells Styles Menu

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.

18 comments

Your email address will not be published. Required fields are marked *

  • 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?

  • 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 Jon,

    My add-in consistently disappears when loading Excel 2016. Any suggestions for that?

    Thanks,
    Qua

  • 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?

  • 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.