20

The Problem

When using the SUM function to sum monthly data into quarterly totals, you can NOT create the formula for the 1st quarter and then copy/drag the formula to the right.

Click to Enlarge

The cell references will only move one column to the right instead of three columns.  If we copy the formula above for Q1 to the right, the resulting formula will give us the total for Feb-Apr.  The correct formula should be a total for Apr-Jun.

The Solution

The Quarter Formulas Add-in creates the correct formulas for the quarters and full year in just a few clicks.  And it’s FREE!

And it Gets FASTER!

You can add the Quarter Formulas button to the Quick Access Toolbar to launch it with a keyboard shortcut.  Then you will be creating all the formulas in under 2 seconds!

These articles will explain how to setup the Quick Access Toolbar (QAT) and use the QAT for keyboard shortcuts.

Benefits

• Four quarter and full year SUM formulas created in 2 seconds.
• No typing or complex formulas needed.
• Automatically determines 1st month cell when Q1 cell is selected.

The add-in file and installation guide are included in the zip file.  The add-in is compatible with Excel 2007, 2010, 2013.  Please let me know if you are interested in a version compatible with Excel 2003.

Instruction Guide

This add-in will save you lots of time when creating quarter and full year formulas, and it is very easy to use.

The Formula Tools button is a drop-down menu that includes a button for the Quarter Formulas function.  More functions will be added to this menu in the future.  See below for instructions on how to setup a keyboard shortcut to launch the function.

Report Structure Requirements

The Quarter Formulas add-in requires all the month data be in 12 consecutive columns.  The four quarter and year total formulas will be created in five consecutive columns.  You will choose the starting cell (Q1) that the formulas will be created in.  The created formulas do NOT have to be next to the monthly data.

Creating the Formulas

1. Click the Quarter Formulas Button.
2. Select the 1st Quarter cell where you want the formulas to be created and click OK.
– Formulas for Q2-Q4 and Full Year sum will be created to the right of this cell.
– The selection defaults to the active (selected) cell in the worksheet.  Selecting the Q1 cell prior to clicking the Quarter Formulas button will save you a step.
3. Select the cell that contains month 1 data and click OK.
– This is the 1st month in the 1st quarter (January if you are on a calendar year).
– The selection defaults to 12 cells to the left of the Q1 cell selected in the previous step.  If the quarterly total cells are directly to the right of the monthly data then you will not have to select the month 1 cell.  This will save you another step.
4. The SUM formulas will be created in the Q1 cell and the four cells to the right of it.
– Copy the formulas down to fill all the rows in your report.

116

Keyboard Shortcuts to Change Font & Fill Color or Cell Style

Have you been searching Google to find keyboard shortcuts to apply a font or fill color to a cell?

Well, you can stop your search… 🙂

No “Good” Built-in Shortcuts

Unfortunately, there are no built-in keyboard shortcuts in Excel for font or fill colors.  There are a few “workaround” methods that I explain in another blog post, 5 Excel Keyboard Shortcuts for Font & Fill Colors.  But these built-in methods are either slow, or do not provide a direct solution.

Keyboard Shortcuts for Font & Fill Colors

So I developed an add-in that allows you to create keyboard shortcuts to change the font & fill colors.  This add-in also lets you create keyboard shortcuts to apply other cell formatting properties like number formatting, borders, font size, protection alignment, etc.

Time Saving Shortcuts

The Formatting Shortcuts add-in will save you a lot of time if you are currently using the mouse to format cells.  Again, this includes any type of formatting and is not just limited to font and fill colors.

The image below shows some common tasks that we all do in Excel to format cells.  It includes the buttons on the ribbon that we press over and over again to apply formatting._

The Formatting Shortcuts add-in will allow you to create keyboard shortcuts for any of these actions.

The keyboard shortcuts are quick and easy to press in one step.  This will save you a ton of time over having to navigate to the ribbon (toolbar) with the mouse.

Video Demo

Here is a video that explains how the add-in works.  You will see that it is fast to setup and easy to press the shortcut keys.

In the video above I demonstrate a few different ways to apply different formatting types with the custom keyboard shortcuts.

Keyboard Shortcuts Are EASY to Press

The keyboard shortcuts you create all start with Ctrl+Shift.  Then you get to choose the letter at the end of the key combination.

For example, in the screenshot below I have setup the shortcut Ctrl+Shift+S to apply the yellow fill color to a cell or range.

The shortcut Ctrl+Shift+S is very easy to press because the keys are close together and can all be pressed at one time with your left hand.

This beats alternative methods of having to press Alt+H+H, then pressing the arrow keys to find the color on the palette.  That method is slow and difficult to perform.

But the Ctrl+Shift+{letter} shortcuts you use with the Formatting Shortcuts add-in are easy and efficient.  They will save you a lot of time.

The Formatting Shortcuts add-in also allows you to undo your mistakes.  If you have ever recorded your own macro and assigned a shortcut key to it, you know that you lose the undo history when you press the shortcut key to run the macro.

This is not the case with the add-in.  The full version of the Formatting Shortcuts add-in allows you to retain the full Undo History in Excel.  So you can use Ctrl+Z or the Undo button if you accidentally press one of the shortcut keys.

The screencast below shows an example of this.  In this video I setup a shortcut key for a cell style that contains number formatting, borders, font, and fill colors.  I then use the keyboard shortcut Ctrl+Shift+A to apply the formatting to a few cells. Finally, I undo my actions using the Undo button.

Features

1. Easy to Use – The shortcut keys and cell formatting can be changed with the click of a few buttons.
2. Unlimited Options – Any combination of formatting properties can be applied with a keyboard shortcut.  This includes custom styles and all formatting options.
3. Full Undo History – The undo history is retained so you can undo any changes made by the keyboard shortcuts.  If you have recorded or written your own formatting macros, you know that undo history is typically lost when you run a macro.  This add-in works around that to retain the undo history.  More details about undo on the help page.
4. Save Time – Improve your efficiency and style worksheets in a fraction of the time it would take with mouse actions.
5. Consistency – The add-in saves your styles so you can start using the keyboard shortcuts when you open a new workbook.  No setup needed (even for custom styles).  Using the same styles throughout your models will make it easier for users to read and understand.  Read more about styles on the help page._

Formatting Types

1. Font Color – Applies the font color to the selected cell(s).
2. Fill Color – Applies the fill color (cell shading or background) to the selected cell(s).
3. Font+Fill Color – Applies both the font and fill color to the selected cell(s).
4. Cell Styles (full version only) – Applies the cell style to the selected cell(s).  Formatting for cell styles include:
– font color
– fill color
– text formatting (bold, italics, underline, etc.)
– border properties (color, weight, line style, etc.)
– alignment (left, right, centered, etc.)
– cell protection
– The image below shows the Cell Styles menu.  Cell styles can be added and modified (customized) in Excel.

Click to Enlarge

Versions

The add-in is available in two versions.  Both versions are compatible with Excel 2007, 2010, and 2013.

Lite Version

The lite version is free to download below and allows you to create up to three keyboard shortcuts for font color, fill color (cell background), or both font & fill color.  It has a single undo feature, which allows you to undo your keyboard shortcut action one time.

Full Version

The full version is \$14.99 US and allows you to create  12 keyboard shortcuts.

It includes the addition of the cell styles format type to create shortcuts for all cell properties (number formatting, borders, font, alignment, etc.)

It also includes the ability to preserve the full undo history.  This means you can undo any of the formatting you applied with the keyboard shortcuts using the Undo button or Ctrl+Z.  The Cell Styles and Undo History are two great features that I think you will find really useful.

_

The full version also includes a Key List feature.  Clicking the Key List button will create a new workbook with a list of all your shortcut keys.  You can print this list and use it as a reference to learn and memorize your shortcuts.

The full version includes a full money-back guarantee.  If you are not satisfied, you may get a full refund within 90 days of purchase.

Both versions include free upgrades and support.

Product Comparison

Lite Version – FREE
Full Version – \$14.99 US

Compatible with: Excel 2007, 2010, 2013, 2016 for Windows Only

All purchases are backed by our 100% Money-back Guarantee.

What’s in the Zip?

The zip file contains: the add-in file, installation guide, and user guide.

How to Install an Excel Add-in Guide

13

How to Install an Excel Add-in – Guide

An add-in file is an Excel file that contains macros (code) and has the extension “.xla” or “.xlam” (2007 and later). When opened, the worksheets in the file are hidden and the macros are typically accessed through buttons that are added to the toolbar or ribbon. Once installed, an add-in will automatically open every time Excel is opened.

Installation

Here is a video that walks through how to install the add-in.

Please see my video below on how to add the add-in’s folder location to the Trusted Locations list.  This is now a critical step due to a July 2016 Office Security update.

Installing the add-in is pretty easy, and should only take a few minutes.

– Pick any folder on your hard drive that is easy for you to find.
– My suggestion is to create a folder named “Excel Campus” and place it in your Documents folder.
2. Open Excel and go to the Options Menu.
– This varies depending on your version of excel.
– 2007 – Office Button > Excel Options
– 2010 & 2013 – File > Options
3. Click the “Add-Ins” option on the left-side menu, and then click the “Go…” button.
– You may have to wait a few seconds for the window to appear.
5. Locate the add-in file you saved in Step 1, and click OK.
6. The Add-in will appear in the list and should be enabled, click OK.
7. Installation Complete (almost) – The add-in is now installed.  However, there is now an additional step due to an Office Security Update in July 2016. Please see the section below for instructions.

Trust the File Location

Important Note: There is one additional step to this installation due to an Office Security Update released in July 2016.  Here is a video that explains it in more detail.

The folder that the add-in file is saved in needs to be added as a Trusted Location in Excel.  The instructions on how to trust the folder location are below.  I also have an article and video that describes this issue in more detail.

1. Open the Excel Options menu
1. File > Options
1. 1. Trust Center > 2. Trust Center Settings… > 3. Trusted Locations > 4. Add new location.
3. Browse for the folder that you saved the add-in file in.
4. Press OK, the folder should now appear in the Trusted Locations list.
5. Press OK on the Trust Center and Excel Options menu to close the menus.
6. The add-in is now in a trusted location and the XL Campus tab will appear every time you open Excel.

Unblock the File

Some users still have issues with the add-in’s ribbon disappearing after trusting the folder location.  If this happens to you, you will need to Unblock the file by changing a file property.

1. Locate the Add-in file (.xla, .xlam) in Windows Explorer.
2. Right-click the file and select Properties.
3. At the bottom of the General tab you should see a Security section.  Check the box that says Unblock.
4. Press the OK button.
5. Close Excel completely and re-open it.  The add-in should now load and any custom ribbons will appear.

You will only need to do this unblock one time.  However, if you download an updated version of the file then you will have to repeat the steps above to unblock it.

Hopefully these additional security steps will be fixed in a future update to Office.  If you completed all the steps above then you should see the add-ins ribbon tab load every time you open Excel.