MrExcel.com - Your One Stop for Excel Tips & Solutions

Document Themes in Excel 2007

October 06, 2006

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.