Keyboard Shortcuts Archives - Page 2 of 3 - Excel Campus

#### Archive

Category Archives for "Keyboard Shortcuts"
205

## 7 Keyboard Shortcuts for the Filter Drop Down Menus

This post will explain 7 keyboard shortcuts for the filter drop-down menus.  This includes my new favorite shortcut, and it’s one that I think you will really like!

79

## 5 Excel Keyboard Shortcuts for Font or Fill Colors

This article explains all the possible ways to apply font or fill (background or shading) colors using keyboard shortcuts, including a way to customize your own.

22

## Best Keyboards for Excel Keyboard Shortcuts

Looking for the best keyboard to use with Excel?

This post explains things to look for in a keyboard that will make it easier to use the most common keyboard shortcuts in Excel for Windows.

73

## Move and Align Chart Titles, Labels, Legends with the Arrow Keys

The free Chart Alignment Add-in for Excel allows you to quickly align the objects within a chart.  The chart titles, labels, and legends can be moved using the arrow keys on the keyboard or with the alignment buttons in the add-in window.

15

## Excel Keyboard Shortcuts for the Menu Key (Right-click Context Menu) – Convert Text to Numbers – Ignore Errors

This post will explain keyboard shortcuts to access the Error Checking, Paste Options, and Quick Analysis menus using the Menu Key on the keyboard.  You will learn keyboard shortcuts for converting text to numbers, ignoring errors, pasting values, and more..

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

12

## Move Excel Charts with Arrow Keys

This post and video contain lots of tips for moving your charts and shapes with the arrow keys.  This is very useful when you need to arrange and align multiple shapes on your worksheet.  I also have a free Chart Alignment add-in that allows you to move the objects inside a chart (titles, labels, legends) with the arrow keys and alignment buttons.

### Move Chart with Arrow Keys

Typically when you select a chart with your mouse, the box surrounding the chart looks like the following.

When the chart is selected like this, nothing happens when you press the arrow keys to try and move the chart around the worksheet.  Instead, the objects within the chart are cycled through and selected.  Not what we want.

#### Solution #1

Hold Ctrl and Left Click the chart –  This selects the shape object that the chart resides in.  Circles will appear on each corner of the chart.

The chart can now be moved around on the sheet with the arrow keys, making it easy to align it with other shapes, charts, or cells in your worksheet.

#### Solution #2

Select multiple charts or shapes – Hold the Ctrl key and select at least two charts using left click.  The bounding box will still look like the same as when you only have one chart selected, but you are able to move the charts with the arrow keys.

### Move or Create a Shape Inside a Chart

Moving shapes inside a chart can get a bit confusing.  Shapes, such as a text box or rectangle, can reside in two places:

• either inside the chart object,
• or on the worksheet (outside of the chart).

The confusing part is that a shape residing outside the chart on the worksheet can be placed on top of the chart.  This makes it look like the shape is inside the chart even though it is not.  In this case, when you move the chart, the shape will NOT move with it unless you have both the shape and the chart selected.

A shape located inside the chart will move with the chart.  This makes it easier to move and align the chart because you don’t have to worry about selecting multiple objects when doing the alignment.

There are two ways to get a shape inside a chart:

1. Create the shape inside the chart – First select the chart so the bounding box appears around it, then draw the shape inside the chart object area (Insert tab
2. Paste a shape inside the chart – Cut or Copy an existing shape on your worksheet, then select the chart and paste the shape.  This will put the shape inside the chart.

### Move Shape Inside Chart with Arrow Keys

Now that your shape is residing inside your chart, you can use the arrow keys to move it around and align it with the other objects in your chart (title, axis, legend, etc.)  See instructions below the screencast.

To move a shape inside the chart with arrow keys:

1. Ctrl+Left Click the border of the chart so you get the circles on the corners.
2. Then Ctrl+Left Click the shape inside the chart.  You will notice that the circles remain on the top left corner of the chart.
3. Keyboard arrows are now enabled so you can move and align your shape (text box) within the chart.

### Conclusion

You should now be able to get all your charts and shapes lined up to perfection using the incremental movements of the arrow keys.  This is very useful if you read my post on Panel Charts as an alternative to Stacked Charts and want to create panel charts.  In a future post I will share more tips and shortcuts for aligning and viewing your charts.

What are some shortcuts you use to align your charts?

18

## Convert Text to Numbers – Excel Keyboard Shortcuts

Excel has a built-in feature to convert text to numbers, but it can be tricky to use when you are trying to select a long list of cells or multiple rows and columns.  In the video below I describe how to use keyboard shortcuts to accomplish this very quickly.

### Keyboard Shortcuts to Convert Text to Numbers

Here’s a quick guide for the keyboard shortcuts to select the range of cells you want to convert.

1. Select any cell that contains the Number Stored as Text error.  You will see a small green triangle in the top left corner of the cell.
_
2. Use the following keyboard shortcuts to select the range:
_
Ctrl + A – selects the entire contiguous range
Ctrl + A Twice – selects the entire sheet
Ctrl + Space bar – selects the entire column
Shift + Left/Right Arrow – selects multiple columns
Shift + Space bar – selects the entire rowShift + Up/Down Arrow – selects multiple rows
_
3. The error menu drop-down will now be at the top left or right corner of your selected range.  You can then use the following keyboard shortcut to Convert to Numbers.
_
Alt + Menu Key + C – Convert to Number

_

The Menu Key is located between the Alt and Ctrl keys on the right side of the keyboard.  If your keyboard does not have a menu key, you can use Shift +F10 as an alternate.

_

### Alternative Methods

I find the built-in feature to be the fastest, but there are other ways to accomplish this task:

• Copy a cell containing a 1 and Paste Special>Multiple on the range you want to convert.
• Use Text to Columns tool on the Data tab (ribbon).  This is limited to one column and doesn’t work on rows.
• Functions – the VALUE() function works well, but there are many ways to do this too.
• VBA/Macro – write your own code to automate the process

9

The Quick Access Toolbar (QAT) in Excel 2007 is extremely useful for creating keyboard shortcuts for items that don’t have a defined keyboard shortcut.  For example, there is no defined keyboard shortcut to Paste Values but you can easily set it up on the QAT.

The QAT is accessed through the keyboard by pressing the Alt key.  Press and release the Alt key and you will see numbers appear next to each icon on the QAT.

So Alt + [the icon number] is the keyboard shortcut for each item.  In this example, the keyboard shortcut for paste values is Alt+1.  The keyboard shortcut for the format painter is Alt+2.

There are two ways to use the keyboard shortcut.

• Press and release Alt to view the shortcut numbers next to each icon.  Then press the number of the QAT item you want to use.
• Better Option: Press and hold Alt then press the number of the QAT item.  This is much faster once you have your QAT shortcut numbers memorized.  You will notice that the numbers don’t appear when you press and hold Alt, so you have to memorize them.

I tend to move the icons around based on the task I’m working on in Excel.  If I’m going to be using the format painter a lot, I will move it to the “1” position (farthest left) while I’m using it.  This is mainly because it is easier to Press Alt+1, Alt+2, and Alt+3 on the keyboard by placing your right thumb on Alt and right index finger on the number.  The other numbers can be a bit of a stretch, depending on your keyboard.

See my post on how to setup the QAT for more details.