30

Excel’s Color Palette Compatibility Issue & Solution

In the following video I explain the difference between the color palettes and a solution for making your new workbooks look the same when opened in Excel 2003 or earlier.



Excel 2007 workbooks are Ugly in previous versions

The new theme based color palettes in Excel 2007, 2010, and 2013 are great for designing spreadsheets and charts that are visually appealing.  But did you know that those colors are being converted to the old 56 color palette when opened in a previous version of Excel?  This means your new spreadsheets could look like the following when opened by clients or colleagues using Excel 2003 or earlier versions.

Excel 2007 Table Colors Converted Earlier Version

Click to Zoom

The Previous Version Palette

Excel 2007 workbooks contain a 56 color palette that will be used when the file is opened by previous versions of Excel.  You can see this palette by opening an Excel workbook and going to: Office Button > Excel Options > Save (option in left sidebar) > Colors… button (after: Choose what colors will be seen in previous versions).

This is the default color palette in older versions of Excel, and Excel automatically converts your new theme colors to these colors.

Excel 2007 Color Palette Converted Earlier VersionFor users of previous versions to see the same colors you do, this previous version palette must be updated with the colors you've used in your workbook.

Does this sound confusing, time consuming, and frustrating? Well don't worry…


Here is your simple and free solution!

The Color Palette Conversion Tool is a simple utility that retrieves the colors you've used in your workbook and updates the previous version palette automatically.  With the click of a few buttons you will ensure that all Excel users see the same colors you do in your fonts, borders, and background fills.  This is critical for presentation purposes, and general sanity if you have Excel 2007 at work and a previous version at home.

The tool has a few advanced features that allow you to control your previous version palette colors, plan for future designs, make updates in multiple workbooks, and easily view or restore default palette colors.  The tool is just a single worksheet in a workbook that contains macros to run this process.  You can use it as a stand alone, or add in to your workbooks if you want to make frequent updates.

Here is what your spreadsheets will look like in previous versions after using the Color Palette Conversion tool.

Excel 2007 Table Colors Converted with Tool

Click to Zoom

Save time and eliminate color confusion

If you're currently going into your previous version palette and modifying colors, you know how time consuming it can be to change the colors.  You have to manually type in the RGB codes for each color you use from the new theme palette.  It's a painful process, but must be done if you want your spreadsheet colors to be universal with all Excel versions.  This tool eliminates all that work.  I explain the color palettes in more detail on the download page.  I hope you find this tool useful and please post questions or suggestions below.

Color Palette Conversion Tool Screenshot

Excel 2007-2010 Color Pallette Conversion Tool Screenshot

Click Image to Zoom

Download Page & Instructions

Excel 2007 Color Palette Conversion Tool

Color Palette Conversion Tool

YFGY29S8735D

5

Format Multiple Excel Worksheets in Multiple Workbooks

Do you spend too much time formatting the same reports in Excel?

You probably find yourself formatting the same spreadsheets in the same way daily, weekly, or monthly.  This can be a very time consuming and redundant task.  And even if it only takes you five to ten minutes to apply page layouts or format header rows; those minutes add up and you may lose some consistency if you forget something.

The Format Copier is your Solution! (and it's FREE!)

The Format Copier tool automates the process of applying page layout and formatting options to multiple sheets in multiple workbooks.  And you can store your formatted sheets, which we will call templates, to use when you have to format the same unformatted reports in the future.

How it works

The Format Copier is a workbook that contains:

  • The formatting tool
  • And your formatted worksheets (templates) that you will use to copy formatting to unformatted worksheets in other workbooks.

Format Copier Overview

With the press of a few buttons you will be able to apply your template formatting to multiple worksheets and workbooks.

Formatting Options include:

  • Page Layout Options for Printing
  • Page Breaks for Printing
  • Cell Formatting & Conditional Formatting
  • Outlines or Row & Column Groups
  • Freeze Panes
  • Hidden Rows & Columns
  • Column Widths
  • Row Heights

The Benefit: It's Easy to Use and Saves You Time

You've probably already figured this out, but the Format Copier will save you lots of time.  Especially if you're formatting the same reports over and over again.  The tool is very easy to use and even stores your formatting preferences so you don't have to remember which formatting options you use for each template.

This tool will help if you spend time doing any of the following in Excel:

  • Insert header picture, image, or logo on all or select worksheets
  • Apply the same header logo formatting on all worksheets
  • Format multiple Excel worksheets
  • Apply page setup and print settings to multiple worksheets.

Never screw up your TPS Reports again!

Ok, that's an Office Space reference… And with this tool you won't ever have to worry about messing up your reports and angering your boss.  You might have a general ledger reporting software that exports reports in an plain looking format, or doesn't format them at all.  The Format Copier will transform these reports to your specification, making them easy to read and print.  And you will produce reports that are formatted consistently, without having to dig up reports from previous months to compare.

Download Page & Instructions

Format Copier Logo

Format Copier

10

Excel Quick Access Toolbar – Keyboard Shortcuts

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.

Alt Number Key for Keyboard Shortcuts for the Quick Access Toolbar

 

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.

5

Setup the Excel Quick Access Toolbar (QAT)

The Quick Access Toolbar (QAT) in Excel 2007 is a great option for shortcuts to your most used commands.  It's faster to click on an icon in the QAT (one click) versus clicking on the ribbon tab and then the command (two clicks + mouse navigation).  And you can use keyboard shortcuts for the QAT to make things even easier.

The Setup

Setting up the QAT is simple and consists of two steps:  Adding commands and arranging the icons.

The easiest way to add commands to the toolbar is to:

  1. Navigate to the command you want to add in the ribbon.
  2. Right click on the icon and select Add to Quick Access Toolbar from the drop down menu.

QAT Add Item

The command icon will be added to the end of the QAT.

To arrange the icons:

  1. Right click anywhere on the QAT and select Customize the Quick Access Toolbar… from the drop down menu.Customize QAT drop down
  2. The Excel Options menu opens and you can move your icons to the left or right by selecting the icon in the right box and pressing the up or down arrows.
QAT Options

Click Image to Zoom

The order of the icons becomes important when you are using keyboard shortcuts to access the commands.  And I highly recommend this as a much faster way to execute commands in Excel, versus using the mouse.  You can also use the Options window (above) to add and remove commands from the toolbar.

So what icons should I add to the Quick Access Toolbar?

This will depend on what you use Excel for and which commands you use most often.  If you are doing lots of data entry and manipulation you might want to have the “Paste Values” and “Format Painter” commands at the top of your list.  Reporting and analyzing, you may want some charting and page layout commands.  The key is to have the commands you use most often located closest to the left because the keyboard shortcuts are easier to press with your thumb and index finger.

Move the QAT

The toolbar can be place above or below the ribbon by clicking the down arrow to the right of the QAT and selecting Show Below the Ribbon or Show Above the Ribbon.

Move QAT

I prefer to have it above because it reduces the total vertical height of the top toolbars, which gives you more spreadsheet real estate.  But you may find that you have so many icons that it fits better below the ribbon.  If you are a heavy mouse user (vs. keyboard shortcuts) then it is also easier to navigate to the QAT if it is below the ribbon.

Checkout my recommendations on commonly used commands for the Quick Access Toolbar.

[print_link]

1 18 19 20