1. Introducing Charts in Excel 2010

What’s New in Excel 2010 Charts

Over the course of Excel 2007 and Excel 2010, the charting engine was completely rewritten. After 15 years of the same tired-looking charts, you can now create stunning charts with just a few mouse clicks.

The following list summarizes the new charting features in Excel 2010:

• To create a chart, you usually start with one of the seven new galleries on the ribbon’s Insert tab. The first six galleries offer column, line, pie, bar, area, and scatter charts. The remaining chart types—stock, surface, doughnut, bubble, and radar—are grouped in the Other Charts gallery. You can display the All Charts gallery that shows all 73 chart subtypes. Select All Chart Types from the bottom of any chart drop-down menu on the Insert tab.

• Your first stop after creating a chart should be the Chart Layouts and Chart Styles galleries on the Design tab. Choose a layout and a design to select from prebuilt elements and colors. If you don’t like the built-in colors, you can choose a new theme from the Page Layout ribbon, or you can head to the Format tab to change the colors for each data series.

• After choosing settings on the Design tab, you can customize individual elements of the chart by using the Layout tab. The Layout tab offers settings for the chart title, axis titles, legend, data labels, data table, axes, gridlines, plot area, chart wall, chart floor, 3-D rotation, trendline, lines, up/down bars, and error bars. In each case, a drop-down menu offers the popular choices, and a More Options choice leads to a Formatting dialog that presents all the choices.

• If you want microcontrol over the shape, fill, outline, or effects of any chart element, you can use the Format tab.

• It has always been possible to create a chart with a single keystroke—the F11 key can be used to build a default chart on a new worksheet. Excel 2010 continues to support this feature, and it also supports Alt+F1 for building a default chart embedded on the current worksheet.

• In Excel 2003, you could define custom formatting for charts by using Chart Type, Custom Types, User Defined, and Add. Excel 2010 replaces this functionality with the Manage Templates tool. Select Design, Change Chart Type, Manage Templates. The main advantage of this change is that now it is easier to move templates from one computer to another computer.

• In many galleries and formatting menus, Excel offers a Live Preview feature. You can see the effect of a change by hovering your cursor over the menu selection. You can hover over several choices until you find one that looks good, and then just click that option.

New Charting Tools and Menus

The entry point for Excel 2010 charting is the Insert tab. After you have created a chart, three new ribbon tabs appear under the Chart Tools heading: Design, Layout, and Format.

• Design offers major adjustments to the entire chart. You can switch rows/columns, choose from up to 12 built-in layouts, and choose colors and formatting effects by selecting a style.

• Layout offers formatting options for major elements of the chart such as title, legend, and data labels. Note that the options on the Layout tab are a small subset of available choices. It is not unusual to bypass the choices in Layout and head directly to the Format dialog.

• Format allows you to micro-manage individual items in the chart.

In general, the tabs progress from more general to more specific as you move from the Design tab on the left to the Format tab on the right. Microsoft pitched that you would progress from Design to Layout to Format in order to complete the chart. In practice, you might go from Insert to Design to Page Layout to Layout to Home to Format in order to finish a chart. Figure 1.1 shows the typical process.

Figure 1.1 Although Microsoft groups many chart settings in Design, Layout, and Format tabs, you will often have to visit Page Layout and Home tabs and the Format dialog box.

If you don’t like the colors used in the Chart Styles gallery on the Design tab, you can visit the Theme drop-down on the Page Layout tab to choose a new theme color for the document.

In addition, you can use many of the formatting icons on the Home tab to format titles and labels on a chart. These same icons appear on the mini toolbar when you select text within a title on a chart.

Using the Insert Tab to Select a Chart Type

As shown in Figure 1.2, the Insert tab offers seven drop-down menus in the Charts group. Each drop-down leads to a variety of chart types.

Table 1.1 describes the contents of each drop-down in the Charts group.

Figure 1.2 Five less-popular chart types are tucked under the Other Charts menu.

Table 1.1 Contents of Each Charts Group Drop-Down

Using the Expand Icon to Access a Gallery of All Chart Types

A dialog launcher icon appears in the lower-right corner of some ribbon groups. This icon usually allows you to bypass the ribbon and head straight to a legacy-style dialog box.

Figure 1.3 shows the expand icon for the Charts group. Clicking this icon leads to a dialog box that shows all 73 charting types in one place.

Figure 1.3 You can click the Dialog Launcher icon to bypass the ribbon and open a charting dialog box.

Understanding the Chart Thumbnail Icons

Figure 1.4 shows the Insert Chart dialog with all 73 built-in chart types.

Figure 1.4 There are 73 chart types available in Excel 2010.

The gallery of 73 chart types might seem like a dizzying array of charts. However, in many cases, there are four variations of a given type. When you understand how Excel uses the light and dark blue icons to show these four charting types, you can quickly choose from the various thumbnails.

For example, consider the fourth through seventh icons in the Column section of Figure 1.4.

The fourth icon in the Column group of Figure 1.4 is for a 3-D clustered column chart. In this type, series 1 and series 2 are plotted next to each other. When they are plotted with different colors, it is easy to compare the height of the similar-colored bars to see how a particular value is trending. For example, the top-left chart in Figure 1.5 shows a 3-D clustered column chart. The thumbnail icon shows a light blue element and a dark blue element next to each other. Icons for clustered charts are shown in Figure 1.6.

The fifth icon in the Column group of Figure 1.4 is for a 3-D stacked column chart. In this type of chart, the values from series 2 are added to the values for series 1. This type of chart makes it easy to compare totals of all series. The lower-left chart in Figure 1.5 shows this type of chart. Although understanding how the first series is trending is straightforward, it is much harder to understand how the third series is trending. Are the West sales for April larger or smaller than those for March? It is hard to tell in a stacked chart. The icons for stacked charts always show the dark blue series on top of the light blue series, and the heights of the blue series vary from point to point. Icons for stacked charts are shown in Figure 1.7.

Figure 1.5 Many chart subtypes offer these four variations on how the data is plotted.

Figure 1.6 Icons for clustered charts show a dark blue and a light blue element at differing heights.

The sixth icon in the Column group of Figure 1.4 is for a 3-D 100 percent stacked column chart. These charts are similar to the stacked charts in that they plot series 2 on top of series 1. However, the total height of all series is scaled so that each data point shows 100 percent. The lower-right chart in Figure 1.5 shows a 100 percent stacked chart. This type of chart illustrates which regions are contributing to the total. The icons for 100 percent charts show the dark blue series on top of the light blue series, and the heights of all bars or points are the same. Icons for 100 percent charts are shown in Figure 1.8.

Figure 1.7 Icons for stacked charts show a dark blue element on top of a light blue element. The total height of the elements differs from category to category.

Figure 1.8 Icons for 100 percent stacked charts show a dark blue element on top of a light blue element. The total height is the same for each point.

The seventh icon in the Column group of Figure 1.4 is available only for 3-D charts. In 3-D column charts, the data for series 2 is plotted behind the data for series 1. A 3-D column chart works best when there are only a few data series. A basic problem occurs when the values in series 1 are larger than all the values in a later series. The taller bars in the front of the chart obscure the later values. Because none of the 2-D chart types offer this subtype of chart, there are fewer examples of icons that plot one series in front of the other. The six icons are shown in Figure 1.9.

Figure 1.9 Icons for 3-D charts show a dark blue element behind the light blue element.

Because there are four ways to plot multiple series, you can group 46 of the 73 chart types into 14 groups of types, as shown in Figure 1.10.

Figure 1.10 The column, line, bar, and area chart subtypes are really variations of basic types.

Using Gallery Controls

The charting tools tabs contain many instances of a new Office interface element known as a gallery. A gallery control enables you to scroll through options one row at a time or click the Open Gallery button to see all the choices at one time.

For example, the Chart Layouts gallery starts by showing three of the available icons: There are three control icons on the right side of the gallery. The up- and down-arrow icons allow you to move through the gallery one row at a time (see Figure 1.11).

Figure 1.11

Figure 1.12 When you click the More icon, you can quickly see all choices in the gallery at one time.

Creating a Chart

The first step in creating a chart is to build a worksheet that contains data to chart. Many business charts are created from summary data. If your dataset contains transactional data, consider summarizing the data using either a pivot table or formulas.

In Figure 1.13, the original dataset contained detailed transactional data. To create summary data to be used in a chart, new rows were inserted at the top of the worksheet, and a summary table was created using the new SUMIFS function. The formulas in C2:E4 create conditional sums to find the total revenue for each combination of product and year.

The formula in cell C2 in Figure 1.13 is =SUMIFS($G$9:$G$571,$B$9:$B$571,$B2, $D$9:$D$571,C$1). To use SUMIFS, specify a range to be summed (in this case, the revenue in G9:G571). Then specify pairs of arguments representing a criteria range and a criteria value. The second and third arguments of the function specify that the products in B9:B571 should be compared to the product in B2. Excel sums the values in the sum range where all the criteria for that row are true.

Figure 1.13 The new SUMIFS function simplifies creating the summary that can be used for charting

For example, the $621,845 is the sum of all revenue in the dataset where both the product is B447 and the year is 2009. Similar logic is used to calculate all of the cells in C2:E4.

Selecting Contiguous Data to Chart

It is easiest to create charts when your data is in a contiguous rectangular block of cells. The left column of the dataset should contain the label for each series to be plotted. The first row of the dataset should contain values to be plotted along the category axis. The top-left cell should be blank. The rest of the cells in the dataset should contain values to be plotted.

In Figure 1.13, the products in B2:B4 will be plotted as individual series on the chart. The years in C1:E1 will be points along the category axis.

Selecting Noncontiguous Data to Chart

It is helpful, but not necessary, for your data to be in a contiguous range. For example, in Figure 1.14 you might want to create a pie chart that includes the category labels in Column B and the totals in Column F. Follow these steps to select data for creating a chart:

1. Click in cell B1 and drag to cell B4 to select the range of category labels.
2. While holding down the Ctrl key, click in cell F1 and drag down to cell F4 to add F1:F4 to the selection.
3. If you have additional series to plot, repeat step 2 for each additional series.

Figure 1.14 Selecting noncontiguous data requires a bit of dexterity, as you attempt to drag while holding down the Ctrl key.

Creating a Chart by Using the Insert Tab Icons

After you have selected the data to be included in a chart, click the Insert tab of the ribbon. Seven drop-down menus in the Charts group offer a total of 73 different chart subtypes. You can select one of the drop-down menus or click the dialog launcher icon in the lower-right corner of the group (refer back to Figure 1.3). Live Preview does not work when you are selecting a chart type from either the Ribbon or the Charts dialog. You have to choose one type and click to create a chart.

Figure 1.15 Hover over any subtype in the ribbon drop-down menus to see a description of the subtype.

Excel charts are now automatically created as embedded charts. When you choose a chart type from the Insert tab, a chart appears somewhere in the range currently visible in the window. You will likely have to move most charts after creating them. For example, in Figure 1.16, the chart has been created in an annoying location. After moving this chart, you can resize it to fit the space by clicking to select the chart and then dragging a corner handle in or out.

Figure 1.16 Excel randomly inserts a chart somewhere in the visible range of cells. You can move and resize a chart.

Creating a Chart with One Keystroke

Previous versions of Excel allowed you to create a chart by selecting the data and pressing the F11 key. In response, Excel created a default chart on a new sheet.

Excel 2010 still recognizes the F11 shortcut. However, it also includes an Alt+F1 shortcut that creates a default chart as a chart object embedded in the current worksheet. The Alt+F1 keystroke is a time saver when you need to create charts that match the Excel default. When Excel is installed, the default chart is a 2-D column chart. You can change the default chart type to fit the type of charts you create most often. Here’s how:

1. Select an existing chart. In the Design tab, click the Change Chart Type icon. If you do not have an existing chart in your workbook, click the dialog launcher icon in the Charts group of the Insert tab.
2. Click the chart subtype that is closest to the chart type you want to create.
3. In the lower-left of the dialog, click Set as Default Chart. Then click Cancel to leave the dialog box.

After you go through this procedure, press F11 or Alt+F1 to create the selected chart type instead of the column chart.

Working with Charts

After creating a chart, you may need to rearrange the data or move the chart to a new location. The topics in this section will assist you with these tasks.

Moving a Chart Within the Current Worksheet

In the following case study, you will see that Excel had an annoying habit of locating new charts near the bottom of your dataset. With a large dataset, you may need to move the chart to the proper location thousands of rows away.

There are several ways to move a chart within the current worksheet. However, some methods are faster than others. When a chart is selected, a border appears around the chart. Eight resizing handles appear in the border. To move a chart, click the border but avoid the resizing handles. You can then drag the chart to a new location.

Because it is somewhat difficult to click a thin chart border, try clicking inside the chart to drag the chart to a new location. This approach works as long as you can click on some whitespace between the plot area and the chart border. The arrows in Figure 1.17 show areas where you can click and drag to move the entire chart. There are many areas inside the chart where clicking and dragging will have a different outcome. For example, if you click on the legend and drag, the legend within the chart area is moved. In addition, if you click anywhere inside the plot area and drag, you can nudge the plot area within the constraints of the chart area.

Figure 1.17 If you can find some whitespace outside the plot area, you can click and drag the whitespace to move the entire chart to a new location on the worksheet.

Dragging to a New Location in the Visible Window Is Easy

You can drag a chart anywhere in the visible window. However, if you try to drag the chart outside of the visible window, the mouse pointer changes to a red “no” symbol. If you release the mouse while it is the no symbol, the chart boomerangs back to its original position.

Dragging Outside of the Visible Window Is Frustrating

If you need to move a chart outside of the visible window, drag so that your cell pointer is within one-half row of the edge of the window. Excel will slowly start to scroll in the appropriate direction. As with other Windows programs, you can speed up the scroll by rapidly moving your mouse left and right. However, it is difficult to keep the mouse within the one-half row tolerance while moving left and right.

Adjust the Zoom So New Location Is Within the Visible Window

You are likely to find the scrolling action to be so slow that you will not want to use the previous technique to move the chart to a new location. Another option is to use the Zoom slider to show the worksheet at a 10 percent zoom. This has the effect of putting about 375 rows in the visible window. If you need to move a chart anywhere from 50 to 375 rows, setting the zoom to 10 percent and then dragging the chart within the visible window is a fast way to go.

Cut and Paste to Move Thousands of Rows

If your chart is created at the bottom of a 50,000-row dataset and you need to move it to the top of the dataset, cut and paste may be the fastest way to go. Follow these steps to move a chart within the current worksheet using cut and paste:

1. Select a chart.
2. Press Ctrl+X to cut the chart from the worksheet.
3. Press F5 to display the Go To dialog.
4. Type the address of the cell that you want to contain the top-left corner of the chart and click OK.
5. Press Ctrl+V to paste the chart in the new location.

Instead of completing steps 4 and 5, your favorite navigation method can be used to move to the cell that should contain the upper-left corner of the chart.

Reversing the Series and Categories of a Chart

Excel follows strict rules in deciding whether rows should be series or categories. Fortunately, you can reverse this decision with a single button click.

If Excel chooses the wrong orientation for the data in a chart, you can click the Switch Row/Column icon in the Design tab. When comparing the before and after charts in Figure 1.18, you will see that the years have changed from being category labels to series labels. The products have changed from being series labels to category labels.

Figure 1.18 To reverse the orientation of data in a chart, click Switch Row/Column.

If your data has more columns of data than rows, the headings in the first row become category labels. In Figure 1.19, the eight columns of monthly data become category labels, and the three rows become series.

Figure 1.19 If your data has more columns than rows, the rows become series in the chart.

If your data has more rows than columns, the headings in the first column become category labels. In Figure 1.20, the 11 rows of city data become category labels, and the 4 columns become series.

Figure 1.20 If your data has more rows than columns, the columns become series in the chart.

If your data has exactly the same number of rows and columns, the rows become series.

Changing the Data Sequence by Using Select Data

The Select Data icon on the Design tab allows you to change the rows and columns of your dataset. The Select Data icon also allows you to resequence the order of the series. When you click this icon, the Select Data Source dialog appears.

As shown in Figure 1.21, buttons in the Legend Entries side of the Select Data Source dialog enable you to add new series, edit a series, remove a series, or change the sequence of a series. A single Edit button on the right side of the dialog allows you to edit the range used for category labels.

When you compare Figure 1.20 to Figure 1.22, you will see that the series and categories have been reversed. The order of the cities has been resequenced from alphabetic to descending order by summer temperature. The month names have been changed to season names.

To convert the chart shown in Figure 1.20 to the chart shown in Figure 1.22, use the Select Data Source dialog as follows:

Figure 1.21 You can use the Select Data Source dialog box for more control over data series.

1. Select the chart, and then select Design, Select Data. The Select Data Source dialog displays.

2. Click Switch Rows/Columns to move the city names to the left side of the dialog.

3. Click Alice Springs on the left side and then the up-arrow button until Alice Springs is the first series.

4. Click Broome and then the up-arrow button until Broome is the second series.

5. Continue resequencing the cities until they are in the desired order.

6. On the Horizontal (Category) Axis Labels side of the dialog, click the Edit button. The data initially points to B1:E1. Change the address in the Axis Labels dialog box to an array by entering the four new labels inside curly braces:

={“Summer”,”Fall”,”Winter”,”Spring”}

7. Click OK to close the Axis Labels dialog box and return to the Select Data Source dialog box.

The modified chart in Figure 1.22 shows the cities based on the warmest summer temperatures.

Figure 1.22 Control the series order by using the Select Data icon.

To watch a video of changing series order, search for MrExcel Charts 1 at YouTube.

Leave Top-Left Cell Blank

In the past, Excel tipsters would tell you to always leave the top-left cell of your dataset blank before creating a chart. This requirement has eased a bit with Excel 2010.

The old guideline was that if your series or category labels contained either dates or numeric labels, you should leave the top-left cell blank. In Excel 2010, if your labels contain values formatted as dates, there is no need to leave the top-left cell blank. In addition, if your data has been converted to a table, using the Format as Table icon on the Home tab, it is impossible to leave the top-left cell blank.

However, in a few instances your results improve if the top-left cell is left blank. For example, consider the data in A1:D4 of Figure 1.23. The years in B1:D1 are numeric. If you look at A1:D1, you have text in Column A, followed by three numbers in B:D. This is remarkably similar to the data in A2:D4. You have text in Column A, followed by numbers in B:D. If you create a chart from this dataset, Excel assumes that you do not have series labels and assumes that A1:A4 represent four category labels. This erroneously produces the top chart in the figure.

Cells A11:D14 in the figure contain exactly the same data as A1:D4, except the Region label was cleared from A11. In this case, Excel correctly sees three series and three categories.

Figure 1.23 The top data is charted incorrectly because of the Region label in cell A1. The bottom data is charted correctly because cell A11 is blank.

Moving a Chart to a Different Sheet

In Excel 2010, charts always start out as objects embedded in a worksheet. However, you might want to display a chart on its own full-page chart sheet. When this occurs, there are two options for moving a chart:

• Choose the Move Chart icon at the right edge of the Design tab.

• Right-click any whitespace near the border of the chart and choose Move Chart.

Either way, the Move Chart dialog appears, offering the options New Sheet and Object In. The Object In drop-down lists all the worksheets in the current workbook. The New Sheet option allows you to specify a name for a new sheet (see Figure 1.24).

Figure 1.24 Move a chart to a different worksheet using the Move Chart dialog.

Customizing a Chart by a Layout and Style on the Design Tab

The Design tab allows you to customize a chart with just a couple of clicks. For example, the Chart Styles gallery allows you to change the color scheme and effects for the entire chart. The Chart Layouts gallery offers professionally designed combinations of chart elements.

Choosing a Chart Layout

Depending on the chart type you have chosen, the Chart Layouts gallery offers 4 to 12 built-in combinations of chart elements. When you choose a new chart layout from the gallery, you get a predefined combination of title, layout, gridlines, and so on.

These layouts will rarely be exactly what you need. There are 780 quadrillion possible ways to configure a chart and this layout gallery offers anywhere from 4 to 12 possible layouts. The odds that your desired layout is represented exactly are slim.

Figure 1.25 A chart sheet holds one chart at full screen. There are no cells on the sheet.

However, use the thumbnails in the layout to get close to what you want. Start with the chart title. If you do not want a chart title, then choose the 4th, 7th, or 11th layout in Figure 1.26. When you move on to the Layout tab, you will have an opportunity to fine-tune the chart.

Figure 1.26 shows 11 charts created by choosing each of the different column chart layouts from the gallery.

Figure 1.26 The Chart Layouts gallery offers up to a dozen predefined layouts for the current chart type.

Choosing a Color Scheme

The Design tab is also home to the Chart Styles gallery that offers 48 variations of color and effects. The gallery has columns for each of the six accent colors, monochrome, and mixed colors.

Figure 1.27 shows the 48 thumbnails in the gallery and a sample of the effects available for accent color 2 in the Office theme.

As you move down the first four rows of the gallery, each style has a bit more extreme effect applied to the bars. The styles in Row 2 have a white border around the bars. The styles in Row 3 have a dark gradient. The styles in Row 4 have a glasslike surface on the bars. The styles in Row 5 have a dark border around the bars, combined with a light tint in the plot area. The final row has a dark background that is suitable if you are using the chart in a dark-themed PowerPoint presentation.

Figure 1.27 Choose from eight color schemes when using the Chart Styles gallery.

Modifying a Color Scheme by Changing the Theme

The six accent colors and the built-in effects are different in each of the 20 themes that ship with Office 2010. You can change to any of the 20 themes or create your own theme to access new colors and effects.

On the Page Layout tab, click the Themes drop-down to choose from the 20 built-in themes. If you choose a new theme from the Themes drop-down, Excel applies a new color and set of effects to all the charts in the current workbook. If you want to change only the colors or effects, use the Colors or Effects drop-down in the Themes group.

In Figure 1.28, you can see that the Opulent theme applies a jeweled effect to the columns.

Keep the following in mind when changing a chart’s theme:

• Excel, Word, and PowerPoint offer the same 20 built-in themes. If you are building a document that contains elements from more than one Office 2010 product, you should apply the same theme in all the documents.

Figure 1.28 Applying a new theme changes the color and built-in effects.

• Changing the theme affects all graphic elements in the workbook. While your Excel workbook might contain mostly numbers and one or two charts, it could make sense to change the theme to apply new colors to the chart. In Excel 2010, changing the theme also affects any SmartArt graphics in the workbook.

• If you want the theme to have an impact on the fonts in your workbook, use the Cell Styles drop-down in the Home tab to format your cells.

• The Effects drop-down on the Page Layout tab contains a lot of subtle information encoded in the thumbnails. See the “Choose Effects for a Custom Theme from an Existing Theme” section, later in this chapter, for details.

Create Your Own Theme

You might want to develop a special theme. This is fairly easy to do: You basically need to select two fonts and six accent colors. For example, suppose you want to create a theme to match your company’s color scheme. The hardest part is probably finding six colors to represent your company, because most company logos use only two or three colors. The following subsections describe how to create a new theme and suggest resources for choosing complementary colors for your company colors.

Choose Effects for a Custom Theme from an Existing Theme

Unless you plan to edit XML files, you should reuse built-in effects from an existing theme for your new theme. The thumbnails in the Effects drop-down offer very subtle clues about the types of effects used in each theme.

The formatting galleries generally range from simple effects in the top row, moderate effects in the middle rows, and extreme effects in the bottom row. For example, if you open the Shape Styles gallery on the Format tab, the first row is simple, the fourth row is moderate, and the sixth row is extreme.

When you open the Effects drop-down on the Page Layout tab, you will see three shapes for each theme: a circle, arrow, and rectangle. The circle provides an indication of the effects used in simple formats such as from row 1 of the Shape Styles Gallery. The arrow provides an indication of the effects used in moderate formats such as from Row 4 of the Shape Styles Gallery. The rectangle provides an indication of the effects used in extreme formats such as in Row 6 of the gallery.

Figure 1.29 shows the Effects drop-down in grayscale. Please open this in your computer so that you can actually see the effects in color.

For example:

• The circle in the Civic and Equity themes show a faint line. This indicates that shapes with a simple format applied will have fainter lines when using this theme. The circles in the Module and Concourse theme have an interior reflection or glow.

• The arrow in the Trek theme has a gradient that starts out light at the top of the arrow and becomes darker at the bottom of the arrow. If you apply a moderate effect to a chart and use the Trek theme, the shapes in the chart will have a similar gradient.

Figure 1.29 The circle, arrow, and rectangle apply to simple, moderate, and intense layouts, respectively.

• The rectangle in the Paper theme has a dark texture applied. The rectangle in the Metro theme has a glass or jeweled affect applied. The rectangle in the Equity theme has a thin white outline around a dark rectangle. If you format your chart with extreme effects, the shapes will have an appearance similar to the rectangle in each thumbnail.

Although you can access dialog boxes to control the font and colors in your theme, choose 1 of the 20 built-in effects as a starting point for your new custom theme. Choose any theme from the Effects drop-down to start building your custom theme.

Understanding RGB Color Codes

Colors on computer monitors are described as a mix of red (R), green (G), and blue (B). Each color channel is assigned a value from 0 to 255. For example, a color of R=255, G=0, B=0 is a bright red. As you add more blue, the red shifts toward a pink or violet color. A color of R=255, G=0, B=128 is a pinkish violet color. A color of R=0, G=0, B=0 is black. A color of R=255, G=255, B=255 is white. You can create 16.7 million different colors by using combinations of red, green, and blue.

To see a pertinent example of how this works, open your company’s home page in a browser. Then, from the View menu in Internet Explorer, select Source, or, in Firefox, select View, Page Source.

You should now see the web page’s underlying HTML code. You can find the colors used in the page by searching for a pound sign. A web page specifies colors in hexadecimal format, using a pound sign followed by six characters, such as #4F81BD.

Although every web page uses the hexadecimal notation for describing colors, Microsoft Excel’s theme specification instead needs the RGB values for the color. As described in the following section, it is fairly easy to convert between the two.

Converting from Hexadecimal to RGB

Hexadecimal is a numbering system that has digits 0 through 9 and A through F. Including 0, there are 16 digits in the hexadecimal numbering system. In the decimal system, a 2-digit number can represent 10×10 different combinations. There are 100 numbers, from 00 to 99. In a hex system, a 2-digit number can represent 16×16 different numbers—that is, 256 numbers, from 0 to 255.

In the #123456 nomenclature, the # sign indicates that the number is in hexadecimal. The first two digits are the hex representation of the red value. The next two digits are the hex representation of the green value. The next two digits are the hex representation of the blue value.

If you don’t have Photoshop or another tool that converts from a hex color to an RGB value for you automatically, you can use functions in Excel to do the conversion. For example, the worksheet in Figure 1.30 converts from a hex color in Cell B1 to the RGB values in B7:B9:

• The formulas in B2:B4 use the MID function to extract each pair of numbers from the color code. The formula for cell B2 is shown in cell C2.

• The formulas in B7:B9 use the HEX2DEC function to convert the two-digit hex number to decimal.

Figure 1.30 This quick Excel worksheet converts from a six-digit hex color code to decimal RGB values.

For example, to represent the color #FF9108 in Excel, you would use R=255, G=145, B=8.

Finding Complementary Colors

If you look at your company’s logo and website, you can probably identify two or three colors to use in the theme. You need to come up with a total of six accent colors for a theme.

Follow these steps to find complementary colors:

1. Start with a hex representation of one of your logo colors.
2. Open http://colorschemedesigner.com in a browser.
3. A color wheel appears on the left. Below and to the right of the color wheel, click the RGB value to enter a new value.
4. In the window that pops up, enter the portion of the color code after the pound sign, such as FF9108.
5. Click each of the six color wheels icons across the top to review mono, complement, triad, and so on. In the Triad view, the website shows your original color, three others, and three variations of each, as shown in Figure 1.31.
6. Hover over any color to see a ToolTip showing the hex code for that color.

Figure 1.31 This web page suggests colors that complement your logo colors.

Specifying a Theme’s Colors

To specify new theme colors, you follow these steps:

1. Select Page Layout, Colors, Create New Theme Colors. The Create New Theme Colors dialog appears (see Figure 1.32).
2. To change the first accent color, select the drop-down next to Accent 1. The color chooser appears.
3. From the bottom of the color chooser drop-down, select More Colors. The Colors dialog appears.
4. On the Custom tab of the Colors dialog, enter values for red, green, and blue, as shown in Figure 1.33. The New color block shows the color for the values you entered. Click OK to accept the new color.
5. Repeat steps 2–4 for each of the accent colors.
6. If you want to change the colors for Hyperlink, Followed Hyperlink, and Text, repeats steps 2–4 for any of those.
7. In the Name box, give the theme a name, such as your company name.
8. Click Preview to see the theme applied to your workbook.
9. Click Save to accept the theme.

Figure 1.32 The 12 colors in the current theme are shown here.

Figure 1.33 Specify the RGB values for the first color.

Specify Theme Fonts

Follow these steps to specify new theme fonts:

1. Select Page Layout, Fonts, Create New Theme Fonts. The Create New Theme Fonts dialog appears, as shown in Figure 1.34. Remember that a font theme contains a heading font and a body font.

Figure 1.34 A theme is composed of two fonts.

2. Select a font from the Heading Font drop-down. If a custom font is used in your company’s logo, using it might be appropriate.

3. Select a font that is easy to read from the Body Font drop-down. Avoid stylized fonts for body copy.

4. Give the theme a name. It is okay to reuse the same name from the color theme.

5. Click Save to accept the theme changes.

Saving a Custom Theme

To reuse a theme in other workbooks, you must save it. From the Page Layout tab, select Themes, Themes, Save Current Theme, as shown in Figure 1.35.

Figure 1.35 The option to save a theme is at the bottom of the Themes drop-down.

By default, themes are stored in the Document Themes folder. This folder is in %AppData%MicrosoftTemplatesDocument Themes.

Give your theme a useful name and click Save.

Using a Custom Theme on a New Document

After saving a custom theme with your company colors, fonts, and effects, the theme will be available to all workbooks, documents, and slideshows on your computer. The next time you access the Themes drop-down, your custom themes will appear first in the menu, under a heading of Custom.

Excel automatically generates a thumbnail showing the letter A in your headline font, the letter a in the body text font, and the six accent colors used in the theme.

After you save a theme and open PowerPoint 2010 or Word 2010, the new custom themes will be available in those applications, too.

Sharing a Theme with Others

If you want to share a theme with others, send them the .thmx file from %AppData%MicrosoftTemplatesDocument Themes.

Using %AppData% in Windows Explorer is a shortcut to the application data folder in your operating system. On a Windows XP machine, this might be C:Documents and Settingsyour nameApplication Data. On a Windows Vista machine, this might be C:UsersYour NameApp DataRoaming. If you just type $AppData%, Windows Explorer will navigate to the proper folder.

The people you share the theme with can either copy the .thmx file to their equivalent folder or save the .thmx file to their desktop and use the Browse for Themes option by selecting Page Layout, Themes, Themes, Browse for Themes.

Next Steps

Chapter 2, “Customizing Charts,” describes how to use the Layout tab of the ribbon to toggle on or off individual elements of the chart. It also describes how you can micromanage individual elements using the Format ribbon.