How to Customize for a GREAT-Looking Excel Chart

This comprehensive tutorial will show you how to customize a Microsoft Excel chart in a smart, efficient manner. We also share hints and tips not found elsewhere.

The charting feature got a complete makeover beginning with Excel 2007. The information in this tutorial applies to Microsoft Excel 2007 and 2010.

If you like video-based introduction, check out Excel 2010 Tutorial for Beginners. We have this course (over 9 hours) and find it very well done. You can watch some chapters online. Now, before we begin, here are a couple of hints:


Hint 1: If you have trouble reversing a change you made from a menu, click the "Undo" icon located in the upper left corner of the Excel window.

Hint 2: If you are new to Excel charts, first read Beginner's Guide to Creating Excel Charts.

Step 1: Getting Ready

sections of an Excel column or bar chart

1. Become Familiar with Chart Terminology

Study the chart above to familiarize yourself with chart or graph terms. This will help you as you proceed through this tutorial.

2. Select a Color Theme

Every workbook has a "Color Theme" and chart colors are based upon the theme. All of Excel's pre-built styles are also based upon the Theme, so change the Color Theme first!

To view available Themes, click the Page Layout tab and on the "Themes" group, click Colors. Hover the cursor over each theme to view its colors on your chart. Click a theme to select it.

3. Select a Font Theme

Every Excel workbook has a default Font Theme also. If you want a different font for the majority of your chart text, change the theme now so all of the pre-built layouts and styles will display with your desired font.

Hint: DON'T change an element's font until the end of the customization process as clicking on a pre-built style changes the font back to the default.

To set the Font Theme, click the Page Layout tab and on the "Themes" group, click Fonts. As your cursor hovers over a theme, your chart will display the theme fonts. Click on a Font Theme to select it.

Step 2: Determine the Overall Chart Layout and Style

the Design tab

The first 3 customizing options for the chart layout are found on the Design ribbon.

1. Switch the Rows and Columns

picture of same chart with axes switched picture of sample column chart

An Excel chart is drawn from selected rows and columns of data, and whichever has the most entries is charted on the X-axis (Y-axis for Bar Charts). In the first chart at right, the 4 quarters of the year display on the X-axis. We could tell Excel, however, to display the product types on the X-axis instead.

To make this switch, select the chart and click the Switch Row/Column icon on the Design ribbon. In the second chart, the product type now displays on the X-axis.

2. Select a Pre-Built Chart Layout

Excel provides pre-built Chart Layouts that contain different arrangements of your chart's elements.

To view the layouts, select the chart and locate the "Chart Layout" section of the Design ribbon. On the right edge of the section are 3 arrows allowing you to view the layouts in different ways. Click on a layout to see its effect on your chart. Hint: To return to the original layout, click the last layout on the dropdown menu.

picture of the dropdown menu of styles from which to choose

3. Select a Pre-built Chart Style

Using the colors in your workbook's Color Theme, Excel provides nearly 50 Chart Styles that recolor your chart differently. Some styles contain stylized borders or backgrounds.

To view available Styles, select the chart, click the Design tab, and find the "Chart Styles" group. On the right edge of the group are 3 vertically-aligned arrows. Click the bottom arrow to see the entire menu (see image). Then click on a style to see its effect on your chart. Hint: To return to the original style, click the second style on the top row.

Author: Keynote Support

Step 3: Adding Optional Chart Elements

layout tab

Five optional chart elements can be added via the "Layout" ribbon: a picture, prebuilt shape, text box, data table, and data labels. To style these added elements, go to the next section.

1. Insert a Picture, Shape, or Text Box on the Chart

Perhaps you would like to add comments, a logo, or a built-in shape to your chart. These options are available in the Insert group of the "Layout tab."

Picture: click the Picture icon. Select the picture from your hard drive and click Insert. Resize the image by moving its borders with the cursor. Images can be formatted manually via the right-click menu.

Text box and Shape: After clicking the Text Box or Shape icon, click in the chart and draw a box to hold the item. Enter your text in the text box. To customize, click on the item's border and select "Format Shape" from the right-click menu.

picture of a chart with a data table

2. Display a Data Table

A Data Table contains the rows and columns of data upon which the chart is based. The chart at right contains a Data Table.

Click the Data Table icon on the Layout ribbon and select the Data Table's placement from the options provided. Click "More Options" to customize.

3. Display Data Labels

Numeric values displayed on the chart series (e.g. columns) are called Data Labels. See the large chart at the beginning of this tutorial for an example. You can add Data Labels to all of the series in the chart in one step, or add Labels to individual Series. However, styling the labels must be done per individual series - which can be time-consuming!

To add Data Labels to all series, select the chart, click the Layout tab, click the Data Labels icon, and choose a position. Or click on a single series to add and position Data Labels for just that series.

To manually style a data series labels, click on one of the labels and select "Format Data Labels" from the right-click menu, or click the Data Labels tab on the ribbon and click "More Options."

Step 4: Formatting / Styling Individual Chart Elements

Listed below are hints and tips for customizing individual chart elements. Each element can be styled manually, but each element also has a Shape Style. So first let's learn what a "Shape Style" is, and then talk about the individual elements that can be customized.

picture of the format tab

Defining Shape Styles

New with version 2007, Excel provides a large selection of pre-built Shape Styles, based on the Color Theme, for each chart element. Found on the Format tab as shown above, Shape Styles contain variations of colors and effects for background, borders, and text.

the Design tab

How Do I Select a Shape Style?

First, select the chart element. To do so, either: 1) click on the element in the chart, or 2) click the Format tab, locate the "Current Selection" group, click the arrow on the top line, and choose an element. Regardless of the method, the selected element will display at the top of the "Current Selection" area.

To select a Shape Style, click the bottom arrow next to the right-most "Abc" and view the entire menu of Shape Styles (see image at right). Hover the cursor over a style and watch the chart element change! Click on a Shape Style to select it.


1. The Data Series (Columns, Bars, or Lines)

Styling Data Series is time-consuming as each series must be customized individually; hence the importance of choosing a Color Theme and Color Style in the beginning.

To style a Data Series, click on a member of the series and select a Shape Style, or right-click for the format menu. To style one column, click on it twice.

2. The Chart Area

The Chart Area surrounds the Plot Area and extends to the edges of the chart. It can be filled with a solid color, gradient, image, or pre-built texture. Hint: Customize the chart's border via the Chart Area.

Select the Chart Area, preview the Shape Styles, and click the "Shape Effect" icon to style a border with cool features such as Glow and Soft Edges. Style manually via the right-click menu.

layout tab

3. The Plot Area

The Plot Area, behind the gridlines, can have a customized background and border. Access the formatting menu via right-click, or by clicking the Plot Area icon on the Layout ribbon.

4. Gridlines

Click the Gridlines icon from the Axes section of the Layout tab. For each axis you can display no gridlines, Major Gridlines, Minor Gridlines, or both. Shape Styles are available, but the "More Options" menu provides additional styling options, such as caps and end arrows (depending on the chart type).

5. Axes

Clicking the Axes icon allows us to position or remove an axis; and possibly remove labels and tick marks, and customize number increments. Shape Styles are available, but the "More Options" menu provides additional styling options, such as text alignment and direction.

6. Text Labels

The first 3 buttons on the Labels section of the Layout ribbon provide for positioning and styling of the Chart Title, the Axes Titles, and the Legend. Before clicking "More Options," preview the Shape Styles for these text labels, and look at the other styling options on the Shapes Styles section of the ribbon.

Author: Keynote Support

Step 5: Fonts, WordArt, and Final Tweaking

Now we are ready for the finishing touches ... the final tweaking of our chart's layout and design.

1. Change Fonts

You selected the Font Theme in Step 1. But if you'd like to change fonts for a chart element, click on the element, right-click, and click Font.

For larger text, consider styling with WordArt - located on the Format tab next to the Shape Styles section. Select the text and select a "Quick Style;" or use the 3 vertically-aligned arrows on the right side of the WordArt group to fill, outline, and apply a special effect.

2. Moving and Resizing Chart Elements

Some elements can be moved, such as the Plot Area, Title, and Legend. Select the element, and when the cursor turns into a crosshairs, click and drag the element.

To resize an element, hover the cursor over one of its sides until it turns into a two-sided arrow. Then click and drag the item's border.

Resources

For a good online resource that provides some very detailed information about charts, go to Peltier Technical Services.

If you prefer video learning, check out the video training course Microsoft Excel - Charts in Detail by Excel expert Guy Vaccaro. We've reviewed this course and find it well done and a good value. And various chapters are availble online for free.

If "book learnin'" is your cup of tea, we've reviewed the small selection of books available and show two below that are the most highly rated.

Excel 2010 Charts and Graphs manual
Excel Dashboards and Reports manual

We hope this article has been helpful. Cheers!


Return to the top