Tips Archives - Page 4 of 4 - Excel Campus

Archive

Category Archives for "Tips"
116

Keyboard Shortcuts to Change Font & Fill Color or Cell Style

Excel Font Fill Color Keyboard Shortcut KeysHave you been searching Google to find keyboard shortcuts to apply a font or fill color to a cell?

Well, you can stop your search… ūüôā

I have spent a lot of time researching this topic as well.  This page will hopefully help you with some answers and solutions to this question.

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.

Formatting Shortcuts Lite Excel Userform

The add-in is named “Formatting Shortcuts” and there is a¬†free¬†version available for download.

Click Here to Jump to Download Section

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

Common Formatting Mouse Actions - Excel

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.

Click Here to Jump to Download Section

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.

Formatting Shortcuts Lite Excel Userform

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.

Keyboard Shortcut Diagram Ctrl+Shift+S Yellow Fill

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.

Click Here to Jump to Download Section

Undo Your Mistakes

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.

 

Formatting Shortcuts Add-in Setup Demo

Click Here to Jump to Download Section

Features

Here are a few additional features of the add-in:

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

Click Here to Jump to Download Section

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.

    Excel Cells Styles Menu

    Click to Enlarge

    Click Here to Jump to Download Section

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.

Formatting Shortcuts Lite Ribbon Button Userform

 

Click Here to Jump to Download Section

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.

Formatting Shortcuts Excel Userform 2.4

_

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.

Formatting Shortcuts Key List

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

Formatting Shortcuts Product Comparisons 2.4

Download

Lite Version РFREE
Full Version Р$14.99 US

Add to Cart

After purchase you will be redirected to a secure download page.  You will also receive an email with a link to download the zip file.

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.

Formatting Shortcuts Zip File Contents

Online Help Pages

How to Install an Excel Add-in Guide

Formatting Shortcuts Add-in Help Page

What do you think?

I want you to love this add-in, so please leave a comment on how it works for you or how it can be improved.

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.

Video

Move Chart with Arrow Keys

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

Chart Single Click No Arrow Keys

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.

Chart Bounding Box Ctrl Click

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.

Select Multiple Charts

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
    > Shapes menu).
  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.

Move Shape Inside Chart with Arrow Keys

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.
    Ctrl Select Chart with Shape
  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.
    Ctrl Select Shape Inside 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?

134

Absolute Structured References in Excel Table Formulas

This post will explain a trick for creating absolute structured references in Excel Table formulas.

Structured Reference Tables are great for creating clean, easy to read formulas.  But creating absolute references to the columns (aka anchoring the columns) in the formula is a bit tricky.

Quick Guide

Duplicate the column references as if referring to multiple columns.  Absolute references to:

  • One column in the same or other table: table1[[column1]:[column1]]
  • One cell in the same row as the formula: table1[@[column1]:[column1]]
  • Table names must be used even if the reference and formula cell are in the same table.
  • You must drag these formulas across columns to maintain the absolute reference (copy & paste does not work).

I have developed an add-in allows you to use the F4 key on the keyboard to create absolute/relative references.  You can download the Absolute Reference Add-in here.

Video Tutorial

Continue reading

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.Number Stored as Text Error
    _
  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

Excel Convert Text to Number Keyboard Shortcut Alt+Menu Key+C_

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.

 

Keyboard Diagram Menu Key Windows_

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

Do you have a different or easier way?  Please leave a comment.