Microsoft Excel 2007 to 2013

Create a Worksheet Template

Creating a worksheet template can really save you a lot of time. Templates are especially useful if you find yourself having to create the same spreadsheet over and over. For example, if a spreadsheet has sheets for each month of year it becomes a bit if a chore if you have to type out the heading and formula for each sheet. Instead, you only need to do it once. Then you can save it as a template. If you need a new month, you can then Insert your template. Here's how.

 

Open up a new workbook, with all three blank sheets at the bottom. Now delete two of the three sheets (If you have Excel 2013 you don't need to do this as there's only oneworkbook by default):

When you have deleted two sheets, the bottom left of your screen should look like this:

Now create the spreadsheet you want to use as a template. In the image below, we've just set up a simple spreadsheet with Week headings at the top and some labels down the left:

Notice how none of the data is filled in for the weeks. The reason there are zeros for the Weekly Totals and the Item Totals is because we have the formulas in place but no data for the weeks. Once it is saved as a template and inserted into a new workbook, then the data can be added. We won't have to add the formulas because they are already in place.

Once you have your spreadsheet looking the way you want it, click the Office button then Save in Excel 2007. In Excel 2010, click File > Save. In Excel 2013,click File >Save, then under Save As, select Computer. Under the Computer heading, click the Browse icon.

When the Save As dialogue box appears change the Save as type area at the bottom to Excel template. Type a name for your template:

Before clicking Save, notice the file name now ends in xltx, and that it is being saved to a Templates folder inside Excel (Custom Office Templates in later versions).

Click the Save button to save your template.

To use your template, close the current worksheet. Create a new blank workbook. Right click a sheet name at the bottom and select Insert from the menu that appears:

The Insert dialogue box should appear. Your template should be on the list, in the Generalsection:

Select your template, and click OK. Your template spreadsheet should then be inserted into your new workbook. You can delete any sheets your don't need, and rename the template. If you need a new sheet based on your template, right- click a sheet and select Insert from the menu again.

Create a basic chart

A basic chart in Excel.

In Excel 2007 you can make a chart in about 10 seconds, which you'll see how to do in just a bit.

After you create a chart, you can easily add new elements to it. For example, you can add chart titles to add more information to the chart, or change how chart elements are laid out.

In this lesson you'll also learn how the text and numbers from a worksheet become the contents of a chart, and you'll learn a few other chart odds and ends.

Create your chart

Create a chart by clicking a chart type in the Charts group on the Insert tab.

Here's a worksheet that shows how many cases of Northwind Traders Tea were sold by each of three salespeople in each of three months. You need a chart that shows how each salesperson compares against the others, month by month for the first quarter of the year.

1. Select the data that you want to chart, including the column titles (January, February, March) and the row labels (the salesperson names).

2. Then click the Insert tab, and in the Charts group, click the Column button. You could select another chart type, but column charts are commonly used to compare items and will get your point across.

3. After you click Column, you'll see a number of column chart types to choose from. Click Clustered Column, the first column chart in the 2-D Column list. A ScreenTip displays the chart type name when you rest the pointer over any chart type.

The ScreenTip also provides a description of the chart type and gives you information about when to use each one.

That's it, you've created a chart in about 10 seconds.

Tip     If you want to change the chart type after you create your chart, click inside the chart. On the Design tab under Chart Tools, in the Type group, click Change Chart Type, and select another chart type.

How worksheet data appears in the chart

In this chart, the data from each worksheet cell is a column. Row titles (salespeople names) are the chart legend text on the right, and column titles (months of the year) are at the bottom of the chart.

As you can see at once in this column chart, Cencini (represented by the middle column for each month) sold the most tea in January and February, but she was outdone by Giussani in March.

Data for each salesperson appears in three separate columns, one for each month. The height of each chart is proportional to the value in the cell that it represents. The chart shows you how the salespeople stack up against each other, month by month.

Each row of salesperson data has a different color in the chart. The chart legend, created from the row titles in the worksheet (the salesperson names), tells which color represents the data for each salesperson. Giussani data, for example, is the darkest blue, and is the left-most column for each month.

The column titles from the worksheet, January, February, and March, are now at the bottom of the chart. On the left side of the chart, Excel has created a scale of numbers to help you to interpret the column heights.

Tip     Any changes that you make to the worksheet data after the chart is created are instantly shown in the chart.

Add chart titles

Chart titles give descriptive information to your charts.

It's a good idea to add descriptive titles to your chart, so that readers don't have to guess what the chart is about.

You can give a title to the chart itself, as well as to the chart axes, which measure and describe the chart data. This chart has two axes. On the left side is the vertical axis (also known as the value or y axis). This axis is the scale of numbers by which you can interpret the column heights. The months of the year at the bottom are on the horizontal axis (also known as the category or x axis).

A quick way to add chart titles is to click the chart to select it and then go to the Chart Layouts group on theDesign tab.

Click the More button   to see all the layouts. Each option shows different layouts that change the way chart elements are laid out.

The picture shows Layout 9, which adds placeholders for a chart title and axes titles. You type the titles directly in the chart.

1. The title for this chart is Northwind Traders Tea, the name of the product.

2. The title for the vertical axis on the left is Cases Sold.3. The title for the horizontal axis at the bottom is First Quarter

Sales.

You'll see how to type the titles in the practice at the end of the lesson. You'll also have a chance to try out other layouts to see how to change the way various chart elements such as the legend and titles are laid out.

Tip     Another way to enter titles is on the Layout tab, in the Labels group. There you can add titles by clickingChart Titles and Axis Titles.

Customize your chart

A customized chart.

After you create your chart, you can customize it to give it a more professional design. You can change the look of your chart by selecting a new chart style, which quickly changes the chart colors. You can format chart titles to change them from plain to fancy. And there are many different formatting options you can apply to individual columns to make them stand out.

At the end of this lesson, you'll learn how to add your chart to a Microsoft Office PowerPoint 2007 slide show so that you can present it to one and all.

Change the look of your chart

Change the look of your chart by using a chart style to change colors.

When you first create your chart, it's in a standard color. By using a chart style, you can apply different colors to a chart in just seconds.

Click in the chart. Then on the Design tab, in the Chart

Styles group, click the More button   to see all the choices, and then click the style you want. Some of the styles change just the color of the columns. Others change the color and add an outline around the columns, while other styles add color to the plot area (the area bounded by the chart axes), and some styles add color to the chart area (the entire chart).

If you don't see what you want in the Chart Styles group, you can get other color choices by selecting a differenttheme. Click the Page Layout tab and then click Colors in the Themes group. When you rest the pointer over a color, the color is shown in a temporary preview on the chart, which is different from what happens when you look at a chart style. You see the color's effect before you apply it, saving you the step of undoing it if you don't like it. Click the one you like to apply it to the chart.

Important     Unlike a chart style, the colors from a theme will be applied to other elements you might add to the worksheet. For example a table, or a cell style such as a heading, will take on the colors of the theme applied to the chart.

Format titles

Add a color to your chart and axis titles.

If you'd like to make the chart or axis titles stand out more, that's easy to do, too. On the Format tab, in theWordArt Styles group, there are many ways to work with the titles. In the picture, a text fill, one of the options in the group, has been added to change the color.

To use a text fill, first click in a title area to select it. Then click

the arrow on Text Fill   in the WordArt Stylesgroup. Rest the pointer over any of the colors to see the changes in the title. When you see a color you like, select it.Text Fill also includes options to apply a gradient or a texture to a title.

Other options in the WordArt Styles group include Text Outline and Text Effects, which include Shadow,Reflection, and Glow effects.

To make font changes, such as making the font larger or smaller, or to change the font face, click Home, and then go to the Font group. Or you can make the same formatting changes by using the Mini toolbar. The toolbar appears in a faded fashion after you select the title text. Point at the toolbar and it becomes solid, and then you can select a formatting option.

Format individual columns

Columns formatted with shadows behind them.

There is still more that you can do with the format of the columns in your chart. In the picture, a shadow effect has been added to each of the columns (an offset diagonal shadow is behind each column).

To do this, you click on one of Giussani's columns. That will select all three columns for Giussani (known as a series).

On the Format tab, in the Shape Styles group, you click the arrow on Shape Effects, point to Shadow, and then rest the pointer on the different shadow styles in the list. You can see a preview of the shadows as you rest the pointer on each style. When you see one you like, select it.

Next, you click on one of Cencini's columns to select all three of those columns and follow the same steps. Then do the

same for Kotas. All this takes only a moment or so to do. You'll have a chance to try it in the practice.

There are more options in Shape Styles that you can choose from. For example, click Shape Fill, where you can add a gradient or a texture to the columns. Click Shape Outline to add an outline around the columns. And Shape Effects offers more than shadows. For example you can add bevel effects and soft edges to columns, or even make columns glow.

You can also apply effects to other areas of the chart, such as the plot area (the area bounded by the axes). You'll see how in the practice.

Quick reference card

See also

Here is a list:

Demo: Create charts in Excel 2007 Available chart types Use charts and graphs in your presentation

Create a chart

1. Select the data that you want to chart.2. Click the Insert tab, in the Charts group, select the chart

type for the chart you want to create, and then click the chart subtype.

Tip     To change the chart type after the chart is created, click inside the chart. On the Design tab, in the Typegroup, click Change Chart Type, and select another chart type.

Chart Tools

After your chart is inserted, the Chart Tools appear, with three tabs: Design, Layout, and Format. On these tabs you'll find the commands you need to work with charts. When you complete the chart, click outside the chart. TheChart Tools go away. To get them back, click inside the chart. Then the tabs reappear.

Change the chart view

After you create a chart, you can make your chart compare data in more than one way. Click in the chart. On theDesign tab, in the Data group, click Switch Row/Column.

Change the chart layout

You can add chart and axis titles, add a data table, delete gridlines, or add data labels quickly by changing the chart layout. Click in the chart. Then on the Design tab, in

the Chart Layouts group, click the More button   to see all the layouts. Click the one you want.

Another way to apply layouts is to click the Layout tab and make selections individually in the Labels and Axesgroups.

Change the look of a chart

To change chart colors, click in the chart. Then on the Design tab, in the Chart Styles group, click

the More button  to see all available colors. Then click the ones you want.

If you don't see colors you want, you can get other colors by clicking the Page Layout tab, and then clicking Colorsin the Themes group. When you rest the pointer over a theme, the theme is shown in a temporary preview on the chart. You see the theme's effect before you apply it, saving you the step of undoing it if you don't like it. Click the one you like to apply it to the chart.

Important The colors from the theme will be applied to other elements you add to the worksheet, such as tables, or cell styles.

Format chart titles

There are a number of options if you want to add formatting to chart or axis titles.

1. First, select a title.2. Then, on the Format tab, in the WordArt Styles group, click

the arrow beside Text Fill to add a color. You can also pick a gradient, or a texture. Or you can click the arrow beside Text Outline or Text Effects in theWordArt Styles group to add visual style to titles.

To make font changes, such as making the font larger or smaller, or to change the font face, click the Hometab, and then go to the Font group. Or you can make the same formatting changes by using the Mini toolbar. The toolbar appears in a faded fashion after you select text. If you point at the toolbar it becomes solid, and then you can select formatting options.

More formatting options

1. Select a data series (in a column chart, for example, that means that you would click one column to select all the columns for that person or region).

If you have any trouble selecting a series, click the Format tab, and then go to the Current Selection group. Click the arrow in the box at the top of the group, look for the name of the series you want, and then select it in the list.

2. On the Format tab, in the Shape Styles group, you can for example, click the arrow on Shape Effects, point to Shadow, and then rest the pointer on the different shadow styles in the list. You can see a preview of the shadows as you rest the pointer on each style. When you see one you like, select it.

Other options in the Shape Styles group are Shape Fill, where you can add a Gradient or a Texture to the column and Shape Outline.

Create and use a chart template

1. Click in the chart you want to save as a template.2. On the Design tab, in the Type group, click Save As

Template.3. In the Save Chart Template dialog box, in the File

name box, type a name for the template.4. To use the template, select data for the chart. On

the Insert tab, in the Chart group, click any chart type.5. Click All Chart Types. In the Change Chart Type dialog

box, click Templates. Select the template.

Add a chart to PowerPoint 2007

1. Copy the chart in Excel 2007.

2. Open PowerPoint 2007. Paste the chart on the slide you want it on.

The chart will automatically be updated in PowerPoint if it is revised in Excel unless you decide otherwise. You can select options on the Paste Options button in the lower right corner of the slide when you paste the chart in PowerPoint.