Themes in Office 2007

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Document Themes in Excel 2007

Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada.

The tip in this show is from Learn Excel from MrExcel.

One of the cool new features in Excel 2007 is the concept of document themes. A theme is a collection of colors, fonts, and shape effects. When you choose a new theme, you get a new look for your document. Word, PowerPoint and Excel all start with the same 20 themes, so you can make sure that your documents look similar from one application to the next.

Here is a document in the standard Office Theme.

Select a new theme from the dropdown, and colors, fonts, and effects all change. This is the Metro theme.

If you would like, you can choose only the colors from a theme.

There are also dropdowns to select only fonts or effects. The effects is initially a bit hard to figure out. Each effect thumbnail has three shapes in the thumbnail. The circle is meant to show the effect applied to simple styles. The arrow is for moderate styles. The rectangle is for intense styles.

When you look in the Shape Styles dropdown, styles in Row 1 are considered simple. Styles in Row 4 are moderate. Styles in Row 6 are intense.

Creating a New Theme You can design a custom theme.

First choose an existing theme with effects that you like
Next, in the Colors dropdown, choose Create New Theme Colors… You will select 4 text colors and six accent colors.
In the Font dropdown, choose 2 fonts - one for headlines and one for body text.
In the Themes dropdown, choose Save Current Theme.

This figure shows the spreadsheet in the custom MrExcel theme.

end up in the next cell. By keeping your hands on the numeric keypad, you can enter the numbers faster.

Someone suggested an improvement to this technique. Pre-select the range where you will be entering the data. The advantage is that when you get to the last column and press Enter, Excel will jump to the beginning of the next row. In the image below, pressing Enter will move you to cell B6. Ctrl+Drag the Fill Handle
I've shown the Fill Handle trick many times on the show. Enter Monday in A1. If you select cell A1, there is a square dot in the lower right corner of the cell. This dot is the Fill Handle. Click the fill handle and drag either down or to the right. Excel will fill in Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday. If you drag for more than 7 cells, Excel will start over again on Monday.

Excel is really good. It can extend all of these series automatically:

Monday - Tuesday, Wednesday, Thursday, Friday, etc.
Jan - Feb, Mar, Apr, etc.
January - February, March, etc.
Q1 - Q2, Q3, Q4 etc.
Qtr 1 - Qtr 2, Qtr 3, Qtr 4, Qtr 1, etc.
1st period - 2nd period, 3rd period, 4th period, etc.
Oct 23 2006 - Oct 24 2006, Oct 25 2006, etc.

Since Excel can do ALL of these amazing series, what would you expect if you enter 1 and drag the fill handle?

You might expect you would get 1, 2, 3, ….

But you really get 1, 1, 1, 1, 1, ….

The book talks about a convoluted method. Enter 1 in A1. Enter 2 in A2. Select A1:A2. Drag the fill handle. There is a better way.

Simply enter 1 in A1. Ctrl+Drag the fill handle. Excel will fill in 1, 2, 3. Holding down the Ctrl seems to override the normal behavior of the fill handle. Someone in a seminar said that they would like to enter a date, drag the date, and have Excel keep the date the same. If you hold down Ctrl while dragging the fill handle, Excel will override the normal behavior (incrementing the date) and give you the same date in all cells.

For the BEST TV show on technology, check out Call for Help.

This tip was originally published on October 6, 2006 and aired on TechTV in Canada and Australia on September 26, 2006.

The permanent URL for this page is http://www.mrexcel.com/tip132.shtml.

If you are looking for show notes from another episode, visit my complete list of TechTV appearances.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.