About charts

Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. By using the Microsoft Office Chart Component, you can create dynamic, interactive charts that you can make available on a Web site for viewing in a browser. When the data for a chart changes and the Web page that contains the chart is refreshed, the chart automatically updates to reflect the latest data.

Chart and spreadsheet on a Web page

Charts can use data that you enter into a data sheet, existing data from database tables and queries, or spreadsheets and PivotTable lists that you create with Microsoft Office Web Components. When your data is based on a PivotTable list or database table or query, you can add fields to organize the data, and then rearrange fields as well as sort and filter the data.

Show Before you begin

Determine whether users can work with a chart interactively Before you begin designing a chart, you should consider your design program and whether your users have the required browser and access to the Microsoft Office Web Components to work with a chart interactively.

To work with charts, users must have the Office Web Components installed. They can do this by installing Microsoft Office 2003 or, if their company has an Office 2003 site license, by downloading the Office Web Components from their corporate intranet. Users must also have Microsoft Internet Explorer version 5.01 SP2 or later to work with a chart interactively, unless you're creating a chart for use in a Visual Basic for Applications (VBA) project.

Users whose systems do not meet the above requirements can view charts, but they cannot work with charts interactively.

Choose a design program The design program is the program you use to create or modify charts. Different design programs provide different capabilities. For example, you can use the extensive charting features of Microsoft Excel to create a chart and save or publish it as either a static chart (a picture) or an interactive chart. You can use the tools in Microsoft FrontPage to create dynamic Web pages that include charts and then publish the page to a Web site. Or you can use Microsoft Access to take advantage of its automatic database connection features. You can open the Web page that contains the chart and modify the chart in FrontPage or Access. You cannot use Excel to open and modify a chart that was published on or as a Web page.

Show Preparing the source data you want to use in your chart

You can enter data for a chart in a chart data sheet, or use data from a database, a Microsoft Office Spreadsheet Component, or a Microsoft Office PivotTable Component.

If you want to chart data from a spreadsheet or PivotTable list, you must first open a Web page that contains one of these components, or you must create a new control with the data you want to use on a Web page.

If you want to chart data from a database query, you should create the query first. For example, if you have a table that contains yearly sales figures for five years, and you want to chart sales figures for only one of those years, you can run a query that returns the sales figures for the year you want and then use the query as the source data for the chart.

Show Design programs you can use to create charts

You can create and modify charts for the Web in several programs. You can create the chart in one program, and later use a different program to modify the chart or the Web page that it's on. Your choice of design program depends on the amount of design or administrative work you want to do on the Web page itself, the type of source data you want to use, and your own familiarity with different design programs.

Show Microsoft Excel

If your source data originates on a worksheet, or if you want to try different chart types and adjust the source data you make available for your chart, Excel is an excellent design environment. When you publish a chart from Excel, the source data you specify is automatically attached to the chart and published on the same page as an interactive spreadsheet or PivotTable list.

After you've published a chart from Excel, you can modify the original chart in Excel (if you saved the .xls file) and republish it, but you can't modify the published chart in Excel. You can, however, open the Web page with the published chart in FrontPage or in data access page Design view in Microsoft Access, and then modify the chart, the Web page, or both.

Microsoft Excel is available in Microsoft Office 2003.

Show Microsoft FrontPage

You can create and modify charts for the Web in FrontPage. You can also use the extensive Web page editing and administrative features of FrontPage to add other controls, graphics, themes, and more to your Web page, and then make the page part of your Web site.

Microsoft FrontPage is available in Microsoft Office 2003.

Show Microsoft Access

For creating Web pages, Access provides special features that operate on and display data from databases. You can use Access to establish a connection between an Access database and the source data you want for a chart, and then use the automatic features in data access page Design view to attach the data to the chart. For example, you can drop a table on a Web page by using the field list, and then create a chart on the Web page that uses the data in the table. You can also establish connections between charts and other controls that display and manipulate data from databases on a Web page. Use data access page Design view in Access when you want to create charts for users who have Internet Explorer 5.01 SP2 or later.

Microsoft Access is available in Microsoft Office 2003.

Show Microsoft Visual Basic

If you are a programmer and you need a more comprehensive programming environment than that provided by the Microsoft Script Editor, Excel, or Access, you can create charts by using the Visual Basic programming language. For more information about programming a chart for the Web, see About getting help for charts.

Show Creating a chart

After you have prepared the data you want to use in your chart, follow the instructions in your design program to place the Chart Component on your Web page.

When you place a chart on your Web page, the Commands and Options dialog box for the chart is displayed. In this dialog box, you can select the chart type you want to use, select the data you want to use, and, depending on the type of data you're using, choose the way you want to display the data.

Show Customizing your chart

After you have created a chart, you can use the Commands and Options dialog box or the chart toolbar to change chart types, divide data among multiple charts in the same component space, change formatting options, add labels and titles, change the font text and style, and more.

The Command and Options dialog box includes command buttons and options for formatting the appearance of your chart. You can change, for example, the color of data markers, the placement of a legend, or the spacing of values on an axis. The options in this dialog box are distributed among several sections called tabs. The tabs that are available change depending on what part of the chart is currently selected. For example, when you select a chart legend, only tabs related to the legend— such as General, Format, and Border/Fill— are available. When you select an axis, tabs such as Line/Marker, Axis, and Scale are displayed.

Provide custom instructions You can add descriptive text or other information about the chart just by typing it on the Web page.

Scripts Features and capabilities of the Chart Component can be accessed and controlled with scripts. If you are familiar with writing scripts for Web pages in a language such as Visual Basic Scripting Edition (VBScript) or JavaScript, you can add scripts to the chart to change the chart appearance or provide information to the user in response to user input in the browser.

Your design program might provide facilities for writing scripts. For example, Microsoft Office programs provide the Microsoft Script Editor. For information about writing scripts, see Help in your design program. For information about accessing features and capabilities of a chart from a script, see Programming Help for the Chart Component.

Show Testing and publishing

Check the chart in the browser Before making the chart available to your users, you should test it in the browser. Microsoft Internet Explorer 5.01 SP2 or later is recommended for working with charts. To interact with a chart, users should also have a Microsoft Office 2003 license. Users with Internet Explorer version 5.01 SP2 or later and an Office 2003 license can also view and interact with the chart. If your users have different browser versions, you might want to test the chart in each version to make sure the chart is displayed properly and that it updates information properly.

Publish the Web page When you are satisfied with the results, make your Web page available to your users. See Help in your design program for information about publishing Web pages, or consult with your Webmaster or network administrator.


Browsers that support charts

To work with interactive charts in Microsoft Internet Explorer 5.01 SP2 and later, users must have the Microsoft Office Web Components installed and an appropriate Office 2003 license. Users who do not have an Office 2003 license can install the components and view them on a Web page, but they cannot interact with them. Learn more about installing and licensing components.

Following are the levels of browser support available when users have the Office Web Components installed and an Office 2003 license.

Show Microsoft Internet Explorer 5.01 SP2 and later

Users have access to all chart features that are accessible from a browser. Changes that users make, such as formatting and layout changes, are retained when users leave a chart and browse back to it later in the same browser session.

Show Other browsers

Users cannot view charts in earlier versions of Microsoft Internet Explorer and in other browsers. A chart either appears as an outlined box on the Web page or does not appear at all.


About Microsoft Office Web Components

Microsoft Office Web Components is a set of ActiveX controls installed with Microsoft Office 2003. With Office Web Components installed on your computer, you can interactively use a spreadsheet, a PivotTable list, and a chart on a Web page in Microsoft Internet Explorer 5.01 SP2 or later. If you want to use the components on a data access page in Microsoft Access, you should have Internet Explorer 5.01 SP2 or later.

If Office Web Components is not installed on your computer but you have an Office 2003 site license that permits intranet distribution, you can configure the components so that users will be prompted to download Office Web Components from your corporate intranet. This allows users at your site to use a data access page without having Office 2003 software installed on their computers.

For information about how to configure Office Web Components, see the Microsoft Office 2003 Resource Kit.


About installing, licensing, and distributing Office Web Components

When you design a Web page using Microsoft Office Web Components, any user with an Office 2003 license can interact with the components in the browser to the level of interactivity you provide. That is, a user with an appropriate license can make changes to data in a spreadsheet, change formatting, drag fields in a chart or PivotTable List, and so on, as long as you didn't protect these options at design time. Users with an Office 2003 license can also create, design, and modify components in a design environment such as Microsoft FrontPage, or Microsoft Access.

Users who do not have Office 2003 licenses can view the components and the data in them, and can print the view of the components, but they cannot interact with the components or manipulate them in a design environment. This means that if you distribute a Web page that uses components, users who have Office 2003 licenses will have access to all functionality provided, but users without a license can only view the data and information you've provided.

Show Installing the Office Web Components

Microsoft Office Web Components are installed with Microsoft Office 2003 and Microsoft Office applications, or they can be installed separately from an installation point provided by the Web page designer.

When you distribute a Web page that uses components, users who do not have the components installed will be prompted to install the components, provided the Web page designer configures the components to do so. The Web page designer must also provide an installation point for the components and a pointer to that location on the component installation page. For more information, see the Microsoft Office 2003 Resource Kit.

Once the Office Web Components are installed, users who have access to an Office 2003 license will be able to interact with and make changes to the components. Users without licenses will be able to view and print the components and the data in them, but they cannot interact with or make changes to them.

Show Details on licensing and functionality

Office 2003 installed on computer Users have full functionality and interactivity with components, including run-time and design-time capabilities.

Office 2003 application installed on computer Users can interact with the component in design mode in that application only (not in the browser or in other applications).

Office 2003 site license (user doesn't have Office 2003 installed on computer, but user's organization has an enterprise or site license agreement) Users have full functionality and interactivity with components, including run-time and design-time capabilities. However, the Web page designer must provide a location from which components can be downloaded, and must reference the site license in a license package file (.lpk) that is associated with one or more Web pages. You use the License Package Authoring Tool to create an appropriate license file for pages. For more information, see MSDN Online.

No Office 2003, Office 2003 applications, or site licenses Users can view and print the components in view-only mode, but they cannot interact with the components or use the design capabilities.


About View-only mode for Office Web Components

If you do not have a Microsoft Office 2003 software license installed on your computer or accessed via a Microsoft Office 2003 application, you can view a Microsoft Office Web Component— a Spreadsheet Component, Chart Component, or PivotTable Component— on a Web page, but you cannot interact with it.

To be able to interact with and use full functionality of the component you are viewing, you must install Microsoft Office 2003 or have access to an Office 2003 license. If you are in a corporate or group environment, check with your network administrator for assistance.

When you are in View-only mode, you can:

  • View data.
  • Print.
  • Select and scroll.
  • Use hyperlinks.
  • Resize a component.
  • Select sheets in the Spreadsheet Component.
  • Expand or collapse members in a PivotTable list.
  • Refresh data in a PivotTable list.
  • Use the About, Help, and Refresh commands. Help topics that deal with interactivity (such as formatting cells, dragging fields, sorting, filtering, and so on) do not apply.

All commands except About, Help, and Refresh are unavailable in View-only mode.


About getting help for charts

Some of the content in this topic may not be applicable to some languages.

Show Getting help in other design programs

When you are working in a design program, you can get help designing a chart in the following ways:

  • Design program Help For information about the commands and features available in the design program to work with charts, see Help for the design program. For example, in Microsoft Access, click the Office Assistant, or click Microsoft Access Help Button image.

    Microsoft Excel provides both general Help about saving Excel data for use on Web pages and specific information about saving charts for use on Web pages. To access this Help in Excel, click the Office Assistant, or click Microsoft Excel Help Button image.

  • Chart design-time Help If you have access to the Commands and Options dialog box in another design program, you have access to the Help topics you are viewing now. In a chart that you have created or activated in a design program, open the Commands and Options dialog box, click the General tab, and then click Help Button image, or right-click the chart and click Help on the shortcut menu.

Show Getting help writing scripts and programs that use charts for the Web

You can use scripts and programs to automate charts, or you can include charts in run-time programs other than Web browsers— programs that you develop in a programming environment such as Microsoft Visual Basic.

Writing scripts Microsoft Office programs provide the Microsoft Script Editor to help you write scripts. For example, you can use the Script Editor from data access page Design view in Microsoft Access to customize a chart. For information about running the Script Editor, see Help in your Office design program. In the Script Editor, you can display additional Help about using its features and writing scripts.

Object model Help For Help developing a program or script to work with a chart list, you can display information about the object model, properties, and methods specific to charts. This object model Help is installed whenever you install the Microsoft Office Web Components, of which the Chart Component is one. Depending on the design program you're using, you can access this Help in the following ways:

  • Help in Microsoft Visual Basic In Visual Basic or Visual Basic for Applications (VBA), you can create a reference to the Microsoft Office Web Components library. You will then have access to Help through the object browser or by selecting a keyword and pressing F1. For information about creating references and using object model Help, see Help in VBA.
  • Help in other environments If you are using another development environment, locate and double-click the Help file Owcvba11.chm.

Show Getting and providing help in the browser

Help available from the browser For users who have Microsoft Internet Explorer version 5.01 SP2 or later and the Microsoft Office Web Components, general Help specific to charts is automatically available. This run-time Help is separate from the Help available from the browser's Help menu.

Help available from the chart The run-time chart Help topics provide general information for all users about using charts in the browser— such as how to use the toolbar and the context menu, and how to work with the field list and fields when a chart is based on a PivotTable list or database table or query.

The run-time chart Help does not include information about working with the Commands and Options dialog box, so it is not the same as the chart design Help topics you are reading now. Depending on the skills of your users and the type of work they want to do, you might want to supplement this run-time Help by providing custom instructions on the Web page for your chart, or provide a hyperlink to another Web page that has instructions on it.

Other Office Web Components, such as the PivotTable Component and Spreadsheet Component, also have their own separate Help available in the browser.

To see what Help is provided in the browser, create a chart on a Web page, open the Web page in Internet Explorer, right-click the chart, and then click Help Button image on the shortcut menu. In the Help window, you can view a table of contents and search the topic text. The Office Assistant is not available in the browser to answer questions.


Keyboard shortcuts

For information about activating and deactivating a chart by using the keyboard, see Help for your design program.

Show Keys for selecting items in a chart

RIGHT ARROW
Select the next item in the chart
LEFT ARROW
Select the previous item in the chart
DOWN ARROW
Select the next group of items
UP ARROW
Select the previous group of items

Show Keys for working with commands and options

ALT+ENTER
Display the Commands and Options dialog box
ALT+F4
Close the Commands and Options dialog box
The TAB key
When the Commands and Options dialog box is active, select the next item on the active tab
Right Arrow
When a tab in the Commands and Options dialog box is active, select next tab
Left arrow
When a tab in the Commands and Options dialog box is active, select previous tab
Down arrow
Display a list or palette when button that contains a list or palette is selected
SHIFT+F10
Display the shortcut menu
Underlined letter
Carry out a command on the shortcut menu
ESC
Close the shortcut menu without carrying out a command
CTRL+Z
Undo an action in the chart
DELETE
Delete the currently selected item

Show Keys for working with a data sheet

ENTER
Enter data, and move cell selection down one cell
The TAB key
Enter data, and move cell selection one to the right
Arrow keys
Move one cell to the left, right, up or down

Show Keys for working with fields

ALT+DOWN ARROW
Open the list for the currently selected field
The TAB key
Alternately move to the most recently selected item, the OK button, and the Cancel button in the drop-down list for a field
Arrow keys
Move to the next item in the drop-down list for a field
SPACEBAR
Select or clear the check box for the current item in the drop-down list for a field
ENTER
Close the drop-down list for a field and apply any changes you made
ESC
Close the drop-down list for a field without applying your changes

Show Keys for working with the field list

CTRL+L
Display the field list, or activate it if it's already displayed
Arrow keys
Move to the next item in the field list
SHIFT+UP ARROW
Move to the previous item and include it in the selection
SHIFT+DOWN ARROW
Move to the next item and include it in the selection
CTRL+UP ARROW
Move to the previous item, but don't include the item in the selection
CTRL+DOWN ARROW
Move to the next item, but don't include the item in the selection
CTRL+SPACEBAR
Remove the item from the selection, if the item that has focus is included in the selection, and vice versa
PLUS SIGN (numeric pad)
Expand the current item in the field list to display its contents. Or expand Totals to display the available total fields.
MINUS SIGN (numeric pad)
Collapse the current item in the field list to hide its contents. Or collapse Totals to hide the available total fields.
The TAB key
Alternately move to the most recently selected item, the Add to button, and the list next to the Add to button in the field list
ALT+DOWN ARROW
Open the drop-down list next to the Add to button in the field list. Use the arrow keys to move to the next item in the list, and then press ENTER to select an item.
ENTER
Add the highlighted field in the field list to the area in the PivotTable list that's displayed in the Add to list
ALT+F4
Close the field list

Show Keys for working with Help

Show Keys for working in the Help window

F6
Switch between the navigation pane and topic pane
ALT+O
Display the Options menu to access any Help toolbar command
ALT+SPACEBAR
Display the program Control menu
ALT+F4
Close the Help window

Show Keys for working in the navigation pane

CTRL+TAB
Switch to the other tab
ENTER
Open or close a selected book, or open a selected Help topic
DOWN ARROW
Select the next book or Help topic
UP ARROW
Select the previous book or Help topic
SHIFT+F10
Display shortcut menu

Show Keys for working in the topic pane

ALT+RIGHT ARROW
Go forward to a Help topic you viewed previously
ALT+LEFT ARROW
Go back to a Help topic you viewed previously
The TAB key
Select the next hidden text or hyperlink, or Show All or Hide All at the top of a topic
SHIFT+TAB
Select the previous hidden text or hyperlink
ENTER
Perform the action for the selected Show All, Hide All, hidden text, or hyperlink
UP ARROW or DOWN ARROW
Scroll toward the beginning or end of a Help topic
PAGE UP or PAGE DOWN
Scroll toward the beginning or end of a Help topic in larger increments
HOME or END
Go to the beginning or end of a Help topic
CTRL+P
Print the current Help topic
CTRL+A
Select the entire Help topic
CTRL+C
Copy selection to the Clipboard
SHIFT+F10
Display shortcut menu


Print a chart

To print a chart, you must print the entire Web page.

  • To print a chart from the design window, see Help in your design program for specific instructions about printing a Web page.

  • To print a chart from the browser, click Print on the Microsoft Internet Explorer File menu.

Note If the chart is wider or longer than the printed page, the areas that don't fit on the page are not printed.


About chart tools, commands, and the field list

Show The toolbar

The toolbar at the top of the chart provides a set of commands for working with the chart display and chart data. For example, you can use the toolbar to change the chart type, add or remove a legend, change series or category data to reflect data in rows or columns, and more. It is most useful when your chart is based on data from a PivotTable list or database table or query, and you want to sort, filter, or change the display of data in a chart.

You can use the toolbar while designing the chart, and users can also use the toolbar to work with the chart at run time (such as when a chart is displayed in the browser). To see the command name of each button, rest the pointer on the button.

Show The Commands and Options dialog box

The Commands and Options dialog box includes commands to change formatting options, select parts of the chart, group and ungroup series so that you can combine charts, and more. The dialog box also includes some commands that aren't present on the toolbar or shortcut menus. To display the dialog box, either click Commands and Options Button image on the toolbar or right-click the chart and click Commands and Options on the shortcut menu.

The commands and properties in the Commands and Options dialog box are distributed among several tabs such as General (where you can select parts of the chart, add or remove a legend or chart title, and change some chart formatting options). Other tabs are displayed depending on what part of the chart is selected. For example, if you select a data series, tabs are displayed that relate to data series, such as Border/Fill, Type, and Conditional Format. When you select an axis, the Format, Line/Marker, Axis, and Scale tabs are displayed. If you can't locate the command you are looking for, make sure you have selected the correct item in the chart that corresponds to that command.

Show The field list

The field list is available from the chart toolbar when your chart is based on database data or on a PivotTable list. Use the field list to view the list of fields that are available from the source data and to add fields to the category, series, filter, and data areas of a chart. In the field list, the fields that are not bold are available but not yet displayed in the chart. The bold fields are already displayed in the chart. You can scroll and click the expand indicators ( Plus box or Minus box boxes) to show or hide additional fields.

Show The shortcut menu

When you right-click an active chart, the shortcut menu displays commands to show or hide the toolbar, the Commands and Options dialog box, and— when the chart is based on a PivotTable list or database data— the field list and drop areas. This menu also contains the Data command, which allows you to change the source data for the chart.

Show Run-time access to the toolbar, field list, shortcut menu, and Commands and Options dialog box

The Commands and Options dialog box is not available during run time by default. Help for the Commands and Options dialog box is also not available at run time. You can enable the Commands and Options dialog box at run time if you choose, but you should provide your own instructions for using it on the Web page.

The toolbar is available at run time, but only if users specifically select it or if you enable it to display on the chart by default. The field list is available when the chart is based on database data or a PivotTable list, and if you enable it in the Commands and Options dialog box.

The shortcut menu is available, but commands that do not apply at run time are unavailable.


Show or hide the chart toolbar

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Right-click the chart, and then click Toolbar on the shortcut menu.


Show or hide the Commands and Options dialog box

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Do one of the following:
    • To display the Commands and Options dialog box, right-click the chart, and then click Commands and Options on the shortcut menu.
    • To hide the Commands and Options dialog box, click Close Button image in the upper right corner of the dialog box.


Use the Commands and Options dialog box

If you leave the Commands and Options dialog box displayed while you select different parts of the chart, the available tabs and their contents change to reflect your current selection.

  1. Display the Commands and Options dialog box by right-clicking the chart and then clicking Commands and Options on the shortcut menu.
  2. Select the part of the chart that you want to modify by clicking once to select a group (such as a data series) and clicking a second time to select an individual item in a group (such as a data marker within the series). You can also select the part you want to modify in the Select list on the General tab of the Commands and Options dialog box.
  3. Click the tab in the Commands and Options dialog box that contains the options you want to modify, and then make changes.
  4. If you don't see tabs displayed for the options you want to modify, you might need to select a different item in the chart.


Show or hide drop areas

When the drop areas are hidden, you cannot add or drag fields in the chart.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Right-click the chart, and then click Drop Areas on the shortcut menu.

Note The Drop Areas menu item is not available unless the chart is based on database data or a PivotTable list.


About source data for charts

When you create a chart, you can base it on existing data or provide data at the time of creation. When you base the chart on existing data, the source data must be available for use, and in some cases it must exist on the same Web page where you want to put your chart.

Charts can use data from the following sources.

Show The chart data sheet

If you want to enter data at the time you create the chart, use the chart data sheet. The data sheet consists of rows and columns, and you can copy and paste information within it. When you enter data in the data sheet, the chart automatically updates to reflect the data. Each series row or column is highlighted with a different color in the data sheet; each color corresponds to the color of the associated data marker in the chart, making it easy to distinguish between series.

Show A Spreadsheet Component

The Spreadsheet Component is a Microsoft Office Web Component that you can use to display calculations in a spreadsheet on a Web page. Your users can use and change the data in the spreadsheet in their Web browsers. When data in the spreadsheet is changed, the chart that uses it is updated. The spreadsheet and the chart must be on the same Web page.

You can create a spreadsheet in any design program that supports the Microsoft Office Web Components, such as Microsoft Access or Microsoft FrontPage. If you already have data in a worksheet in Microsoft Excel, you can publish that data as an interactive spreadsheet. You can also create a chart that uses the worksheet data in Excel, and when you publish the chart as an interactive chart, an interactive spreadsheet is also published. For more information about publishing spreadsheet controls, see Help for your design program.

Show A PivotTable Component

The PivotTable Component is an Office Web Component that you can use to present data from a database or other source on a Web page. Your users can interact with the PivotTable list in their Web browsers.

When you create a chart based on a PivotTable list, the chart can be filtered and sorted, and its layout rearranged as in the PivotTable list. When data in the PivotTable list is changed, the chart is automatically updated, and when data in a chart based on a PivotTable list is manipulated, the PivotTable list is automatically updated to match. For example, if you remove a field from a chart, the same field is removed from the PivotTable list. The PivotTable list and the chart must be on the same Web page.

Before creating a chart based on a PivotTable list, you must create, design, and access the necessary data for the PivotTable list. You can create a PivotTable list in a design program that supports the Microsoft Office Web Components, such as data access page Design view in Microsoft Access, or you can publish a PivotTable list from Microsoft Excel. For more information about creating a PivotTable list, see Help for your design program.

Show A table or query in a database

You can base a chart on data from any table, query, or view that you have access to from within your design program. For instance, you can use data in a table or query in a Microsoft Access database or an Access project connected to a Microsoft SQL Server database. When the data is updated and you refresh the page the chart is on, the chart updates. You can also add, remove, sort, and filter fields in the chart to organize the data in different ways.

Depending on the type of database you want to access and the design program you're using, you might need to set up a data source or other means of access to the data, and then set up access permissions. A data source is a stored piece of information, sometimes in the form of a text file, that contains the name of the database driver and other pertinent information that's used to establish a connection and retrieve data. For information about how to access data from a database, see Help for your design program.

Show A data source control

A data source control is a control used by some programs to connect to data. For example, when you place a database form on a data access page in Microsoft Access, a data source control is used to connect the data from the database to the Web page. When you base a chart on such a control, the connection information is already established. For more information about using a data source control, see Help for your design program.


About accessing a data source in a different domain

The Chart Component attempts to access data in another domain, but fails due to one of the following reasons:

  • In Internet Explorer, on the Security Settings dialog box, under Miscellaneous, Access data sources across domains is set to either Disable or Prompt and when prompted, you clicked Cancel.
  • The site being accessed by the control is not included in the list of trusted sites.

To enable access across domains, do the following:

Show Enable prompting

  1. In Internet Explorer, on the Tools menu, click Internet Options. On the Security tab, select the appropriate Web content zone, and click Custom Level. Under Miscellaneous, set Access data sources across domains to Prompt.
  2. When prompted, if you trust the data provider, click OK to allow the Chart Component to access data from the specified domain.

Show Add the site to the list of trusted sites

If the site being accessed is always trustworthy, add the site to the list of trusted sites.

  1. In Internet Explorer, on the Tools menu, click Internet Options. On the Security tab, select Trusted sites as the Web content zone, and click Sites.
  2. In the Trusted sites dialog box, enter the site details, and click OK.


About making connections to external data sources more secure

When you define a connection to an external data source for a chart component on a Web page, the authoring environment stores this information as an unencrypted (plain text) connection string in the HTML source. As a result, a user opening the page in a browser can easily view the HTML source for the page and read the connection string, which may include a user name and password.

To prevent unauthorized access using information from the connection string, if the data source supports this mode of authentication, use Windows Authentication (also referred to as a Trusted Connection and Integrated Security), which uses the current user's Microsoft Windows account to connect to an external data source. Using Windows Authentication to connect to an external data source has the following advantages:

  • The page designer doesn't have to enter a user name or password to connect to the data source, so that information cannot be exposed in the HTML source of the page.
  • The user of the page doesn't have to enter a user name or password to connect to the data source when opening the page, so that information cannot be compromised while it's being sent to the server.
  • Only a Windows user account that is configured in the security system for the data source will be allowed to connect to that data source.

As an example, to connect to Microsoft SQL Server using Windows Authentication, use the following procedure when defining a connection.

Important Before you can use Windows Authentication to connect to a Microsoft SQL Server database, a server administrator must configure the server to use this mode of authentication, and must grant your Windows user account (or a group of which your user account is a member) login access. Additionally, the server administrator should provide your account with the minimum level of permissions to the tables or stored procedures required for your chart.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Right-click on the chart and select Commands and Options.
  3. In the Commands and Options dialog box, select Data from a database table or query.
  4. Click Connection.
  5. On the Data Details tab, click Edit.
  6. On the Select Data Source dialog box, double-click New SQL Server Connection.odc.
  7. In the Data Connection Wizard, specify the name of the server in Server name.
  8. Under Log on credentials, click Use Windows Authentication.
  9. Click Next, and then follow the directions in remaining screens of the Data Connection Wizard to specify the data to retrieve.

Note Other data sources that support Windows Authentication may present different options in the Data Connection Wizard. For example, to connect to an Oracle database by using Windows Authentication, you must enter only a forward slash (/) in the User Name box. For more information, see the documentation for the data source provider you are using.

If Windows Authentication is not available for the data source you want to connect to, you must enter a user name and password to connect to the data source. Because this user name and password might be viewed when the chart is on a Web page, do not use this mode of authentication to connect to sensitive data from a chart on a Web page. Even if you are not concerned about unauthorized users viewing your data, you should connect only with a user account that has limited permissions on the data source. For example, when connecting to a SQL Server database using a user name and password, do not use the SA account or any other account that has elevated permissions, because an unauthorized user might be able to use this account and password to access other data on the server.

Note If you use the Data Retrieval Service for Microsoft SQL Server (or use a Data Retrieval Service Connections (.uxdc) file that is defined to use that data retrieval service) to connect to an external data source, and you do not use Windows Authentication, the user name and password used to connect to the data source are not saved in the Web page. When you use this method to connect to an external data source, users of the Web Page that hosts your chart will be prompted to enter their logon information.


Change source data for a chart

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Right-click the chart, and click Data on the shortcut menu.
  3. On the Data Source tab, select the new data source, and then set options for the data source.

Security Note Under some circumstances, a user can view the information used to connect to an external data source. This may represent a security risk. For more information, see About making connections to external data sources more secure.


Elements of a chart

ShowSeries, categories, and data representation

Example of chart elements that represent data

Callout 1 Gridline

Callout 2 Data series

Callout 3 Data marker

Callout 4 Category

Callout 5 Plot area

Callout 6 Legend key

Callout 7 Series name

Callout 8 Legend

Gridline Lines you can add to a chart that make it easier to view and evaluate data. Gridlines extend from the tick marks on an axis across the plot area.

Data series, series name (legend entry) A group of related data points plotted in a chart. Each data series has a unique color or pattern and is represented in the legend by the legend key and series name (legend entry). In the preceding chart example, Projected and Actual are data series. You can plot one or more data series in most charts.

Data marker Used to represent a data point, which is an individual value plotted in a chart. Related data points constitute a data series. Examples of data markers include bars, dots, and slices.

Category A group of related data points made up of one data point from each data series in the chart. For most charts, categories are plotted along the category (x) axis, which is usually horizontal. However, in some charts the category (x) axis is vertical, as in a bar chart. In the preceding chart example, Qtr1, Qtr2, Qtr3, and Qtr4 are categories, each made up of one point from the Projected series and one point from the Actual series.

Plot area The area in a chart that's bounded by the axes and includes all the data series.

Legend key Images in a legend that show the patterns and colors assigned to the data series or categories in a chart. Legend keys appear to the left of legend entries.

Legend A key to the patterns or colors assigned to the data series or categories in a chart.

ShowAxes

Axes in a chart

Callout 1 Value (y) axis title

Callout 2 Value (y) axis tick mark labels

Callout 3 Value (y) axis

Callout 4 Gridline

Callout 5 Minor tick mark

Callout 6 Category axis title

Callout 7 Category (x) axis tick mark labels

Callout 8 Category (x) axis

Value (y) axis title A title that is usually used to describe what the value axis measures.

Value (y) axis tick mark labels Also known as value axis labels. These labels describe the values of measurement on the value axis.

Value (y) axis A line that borders one side of the plot area, providing a frame of reference for measurement or comparison in a chart. For most charts, data values are plotted along the value (y) axis, which is usually vertical. However, in some charts the value (y) axis is horizontal, as in a bar chart. Some charts have data values plotted on both axes, as in an xy (scatter) chart.

Gridline Lines you can add to a chart that make it easier to view and evaluate data. Gridlines extend from the tick marks on an axis across the plot area.

Major and minor tick marks Small lines of measurement that intersect an axis, similar to divisions on a ruler. Major tick marks appear at each major unit of measure on the axis scale, and minor tick marks appear at each minor unit of measure. Both types can be positioned inside or outside of the axis, or they can be positioned to cross the axis.

Category (x) axis title A title that is usually used to describe what is plotted on the category axis.

Category (x) axis tick mark labels Also known as category labels. These labels show the names of the categories on the category axis.

Category (x) axis A line that borders one side of the plot area, providing a frame of reference for measurement or comparison in a chart. Categories are groups of data points made up of one data point from each data series in the chart. For most charts, categories are plotted along the category (x) axis, which is usually horizontal. However, in some charts the category (x) axis is vertical, as in a bar chart. Some charts do not have a category axis, as in an xy (scatter) chart.

ShowTitles and labels

Chart titles and labels

Callout 1 Chart workspace title (title for all charts in workspace)

Callout 2 Chart title (title for single chart)

Callout 3 Value axis title

Callout 4 Category axis title

Callout 5 Category label

Callout 6 Data label

Callout 7 Value axis label

Callout 8 Chart workspace legend (legend for all charts in workspace)

Callout 9 Chart legend (legend for single chart)

Chart workspace title A title that describes all charts when you have multiple charts in the workspace. This title can be positioned above, below, to the left, or to the right of the chart workspace.

Chart title The title that describes a single chart. This title can be positioned above, below, to the left, or to the right of the chart.

Value axis title A title that is usually used to describe what the value axis measures.

Category axis title A title that is usually used to describe what is plotted on the category axis.

Category label Also known as a tick mark label. These labels show the names of categories on the category axis. A category is a group of related data points, made up of one data point from each data series in the chart.

Data label A label that provides additional information about a data marker, which represents a single data point or value. Depending on the chart type, data labels can show values, names of data series or categories, percentages, or a combination of these.

Value axis label Also known as a tick mark label. These labels describe the values of measurement on the value axis.

Chart workspace legend A key to the patterns or colors assigned to the data series or categories in all charts in a multiple-chart workspace.

Chart legend A key to the patterns or colors assigned to the data series or categories in a single chart.

ShowFields

When you create a chart based on a database or PivotTable list, the chart has fields that you can move, filter, and sort like a PivotTable list.

Chart with fields

Callout 1 Filter field

Callout 2 MultiChart field

Callout 3 Data field

Callout 4 Series field

Callout 5 Category field

Filter field A field that you use to filter data by specific items. In the previous example, the Year filter field displays data for all years for which there are records. To display data for a single year, you can click the arrow next to Year and select the year.

MultiChart field A field you use to display separate charts for each item in the field. In the example, the Salesperson field is filtered to show two salespeople: Dodsworth and Suyama. Because the field is in the MultiChart area, two separate charts are displayed for each.

Data field A field from the underlying source data that provides values to compare or measure. In the example, Sum of Sales is a data field that summarizes sales in each region for each type of product. The first category data marker in the chart for Dodsworth (E) reaches a little over 200 on the value (y) axis. This amount is the sum of Dodsworth's sales for products in the East region. Depending on the source data you use for the report, you can change the summary function from Sum to Average, Max, Min, or another calculation.

Series field A field that you assign to a series orientation in a chart. The items in the field provide the individual data series. In the example, Type is a series field.

Category field A field from the source data that is assigned to a category orientation in a chart. A category field provides the individual categories for which data points are charted. In the example, Region is a category field.


Examples of chart types

Show Area

An area chart emphasizes the magnitude of change over time. By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole.

In this example, an area chart emphasizes increased sales in Washington and illustrates the contribution of each state to total sales.

Show Column

A column chart shows data changes over a period of time or illustrates comparisons among items. To emphasize variation over time, categories are organized horizontally and values are organized vertically.

Stacked column charts show the relationship of individual items to the whole.

Stacked column chart

Show Bar

A bar chart illustrates comparisons between individual items. To focus on comparing values and to place less emphasis on time, categories are organized vertically and values are organized horizontally.

Stacked bar charts show the relationship of individual items to the whole.

Stacked bar chart

Show Line

A line chart shows trends in data at equal intervals.

Show SmoothLine

A smooth line chart shows trends in data at equal intervals, smoothed to show the estimated data between intervals.

Smooth line chart

Show Pie

A pie chart shows the proportional size of items that make up a data series to the sum of the items. It always shows only one data series and is useful when you want to emphasize a significant element.

You can also create a stacked pie chart, which is a pie chart with more than one series.

Stacked pie chart

Show Doughnut

Like a pie chart, a doughnut chart shows the relationship of parts to a whole, but a doughnut chart is made up of rings rather than slices, and can contain more than one data series. Each ring of the doughnut chart represents a data series.

Show Stock

Two stock chart types are often used to illustrate stock prices: open-high-low-close and high-low-close. These charts can also be used for scientific data— for example, to indicate high-low-average temperatures. The chart in the example is a high-low-close chart.

Data to be charted and stock chart

Show XY (Scatter)

An XY (Scatter) chart either shows the relationships among the numeric values in several data series or plots two groups of numbers as one series of xy coordinates. This chart shows uneven intervals— or clusters— of data and is commonly used for scientific data.

When you arrange your data, place the x values in one row or column with corresponding y values in the adjacent row or column in an x-y x-y arrangement.

xy (scatter) chart

Show Bubble

A bubble chart is a type of xy (scatter) chart. The size of the data marker indicates the value of a third variable.

To arrange your data, place the x values in one row or column and then enter the corresponding y values and bubble sizes in the adjacent rows or columns.

Data to be charted

Show Radar

In a radar chart, each category has its own value axis that radiates from the center point. Lines connect all the values in the same series.

A radar chart compares the aggregate values of several data series. In this chart, the data series that covers the most area, Brand A, represents the brand with the highest vitamin content.

Show Polar

A polar chart compares the relationship of angles and distances. The chart in this example illustrates elevation angles for two heights of a radio antenna.


Create a chart

The specific steps for creating a chart depend on the program you are using to design your Web page. For more information, see Help for that program. If your design program Help does not provide a procedure for creating components such as charts, follow these steps:

  1. In your design program, open or create the database, project, or Web page that contains the data you want to use in your chart.
  2. Activate the page where you want to put the chart.
  3. By using the design program's procedure for adding a control to a Web page, designate the location and initial size of the chart component on the Web page.
  4. If the Commands and Options dialog box is not displayed, click the chart to display it.
  5. Specify the data you want to use for the chart in the Data Source tab by selecting Data typed into a data sheet, Data from a database table or query, or Data from the following Web page item.
  6. If you select Data from the following Web page item, you must also select the Web page item (such as a spreadsheet, PivotTable list, or data source control) you want to use in the list. Only items that exist on the Web page where you're creating the chart will be shown in the list.
  7. Specify data settings for one of the following:

    Show Data typed into a data sheet

    1. Under Set details for the data type, click Data Sheet.
    2. Select a cell on the data sheet, and then enter the data for each series in cells under each column labeled Series n, or for each category in rows labeled Category n.
      • You can enter category or series labels in the first column or first row of the data sheet.
      • As you enter data, press ENTER to move down a column or the TAB key to move across columns. Press DELETE to remove data. The rows and columns will automatically expand or contract to accommodate more or less data.
      • As you enter data, the chart automatically uses the default column chart type to represent the data.
      • If you want to change series to be in rows and categories to be in columns, click By Row-By Column Button image.

    Show Data from a database table or query

    1. Under Set details for the data type, click Connection.
    2. Enter a connection string in the Connection box, or click Edit to browse to or enter information for a data source.
    3. Depending on the design program you are using, clicking Edit displays the Select Data Source dialog box or the Data Link Properties dialog box.

      Show Select Data Source dialog box

      1. Locate the data source you want to use in the Look in box, and double-click it in the list.
      2. If the data source you want to use is not available, click New Source and follow the instructions in the Data Connection Wizard to create a new connection file for your data.

      Show Data Link Properties dialog box

      • Enter connection information for your data source on the Connection tab. For help on options, click Help at the bottom of the dialog box.
    4. Select Data member, table, view, or cube name and select the data member, table, view or cube name from the list or select Command text or SQL and type the connection string you want to use.
    5. Under Plot details, select whether series should be in rows or columns by clicking Series By Rows/Columns Button image.
    6. If you want to use direct record values from your data, such as when you have created a query that calculates totals on your server, and you want the chart to use those totals rather than calculate its own totals on the client, select the Plot detail records check box.
    7. Add data from the field list to the chart drop areas.

      Show How?

      1. On the chart toolbar, click Field List Button image.
      2. Use the scroll bars and the expand indicators ( Plus box and Minus box boxes) to find the field you want to add to the chart.
      3. Click the field and drag it to the appropriate drop area on the chart: the Drop Series Fields Here area, the Drop Category Fields Here area, the Drop Data Fields Here area, or the Drop Filter Fields Here area.

        You can add multiple fields to each location.

      Note As an alternative to dragging the field, select the area to which you want to add a field (areas are listed at the bottom of the field list), and then click Add to. If Add to becomes unavailable when you select the area where you want to put the field, the field you selected might not be available for use as a category, series, data, or filter field.

    Show Data from a Web page item: Spreadsheet

    1. Under Set details for the data type, click Ranges.
    2. Do one of the following:

      Show Specify all the data at one time

      1. Under Set ranges in one step, enter the references of the data range you want to chart, separated by a colon (:) in the Data range box. For example, if you want to use data in a continuous range from cell A1 through cell G5 in the spreadsheet, type A1:G5 in the Data range box.
      2. Next to Series in, click Rows or Columns to specify the location of your series data.
      3. Click OK.

      Show Specify one series of data at a time

      1. Under Series, click Add.
      2. If you want to give the series a descriptive name, replace the default name in the Series name box with the name you want.
      3. In the Category Labels box, type the cell references to the category labels for that series. For example, if the series you are adding is in row A2:G2, and the category labels are in cells A1 through G1, type A1:G1.
      4. In the Values box, type the range reference for the values for that series. For example, if the series data is in cells A2 through G2 on the spreadsheet, type A2:G2.
      5. Repeat for each series of data you want to add.

    Show Data from a Web page item: PivotTable list

    1. Under Set details for the data type, click Pivot Details.

      Because the chart uses the data connected in the PivotTable list, Connection options are not available.

    2. Under Plot details, select whether series should be in rows or columns by clicking Series By Rows/Columns Button image.
    3. If you want to use direct record values from your data, such as when you have created a query that calculates totals on your server, and you want the chart to use those totals rather than calculate its own totals on the client, select the Plot detail records check box.
    4. If you want, you can add, change, or remove fields in the chart by clicking Field list Button image on the chart toolbar.

    Show Data from a Web page item: Data source control

    1. Under Set details for the data type, click Data Source Details.
    2. In the Data member, table, view, or cube name list, click the item that contains the data you want to use in your chart.
    3. Under Plot details, select whether series should be in rows or columns by clicking Series By Rows/By Columns Button image.
    4. If you want to use direct record values from your data, such as when you have created a query that calculates totals on your server, and you want the chart to use those totals rather than calculate its own totals on the client, select the Plot detail records check box.
    5. If you want, you can add, change, or remove fields in the chart by clicking Field list Button image on the chart toolbar.
  8. Click the Type tab, click the type of chart you want to use from the list, and then click the chart sub-type you want to use from the display on the right.
  9. Click the chart or an item in the chart to make options available in the Commands and Options dialog box. Different options will be displayed in the dialog box depending on what is selected in the chart. For example, when you select the entire chart, the General tab becomes available (in which you can add a legend or data labels, or make other changes based on the chart type). When you select an axis, the Format, Line/Marker, Axis, and Scale tabs are displayed.


Work with a data sheet

If you created your chart by typing data into a data sheet, you can use these procedures to work with data sheets.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. On the toolbar, click Commands and Options Button image, and then click the General tab.
  3. Under General commands, click Chart Workspace in the Select list.
  4. Click the Data Sheet tab.
  5. Do one or more of the following:

    Show Enter data

    Note You can enter category or series labels in the first column or first row of the data sheet.

    1. Select a cell in the data sheet.
    2. Type the data you want to enter in the cell.
    3. Press ENTER to enter the data and move the cell selection down, or press the TAB key to enter the data and move the cell selection to the right.

    Show Clear the data sheet

    • Click Clear Data Sheet Button image.
    All data is cleared from the data sheet and from the chart. This action cannot be undone.

    Show Change category rows to series rows, or series rows to category rows

    • Click By Row - By Column Button image.

    Show Copy and paste data

    1. Select the data you want to copy, either in the data sheet or in another program.
    2. Click Copy Button image.
    3. Select the cell in the upper-left corner of where you want to paste the data.
    4. Click Paste Button image.

    Note Pasted data will overwrite existing content in cells.


Create a combination chart

A combination chart uses two or more chart types to emphasize that the chart contains different kinds of information. The chart in the following example shows one data series (Actual) as a column chart type and the other (Projected) as a line chart.

Combination chart

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.

  2. Determine which series you want to be a different chart type.

  3. Do one of the following:

    ShowChange the chart type for a series

    1. Click the series for which you want to change the chart type.

    2. On the toolbar, click Chart Type Button image.
    3. In the list, click the chart type you want, and then click the subtype you want.

    ShowChange the chart type for a series group and add axes

    1. On the chart toolbar, click Commands and Options Button image, and then click the General tab.

    2. Under General commands, select Chart Workspace in the Select list.

    3. Click the Series Groups tab.

    4. Under Series groups, in the Select one or more series box, select the series for which you want to change the chart type.

    5. In the Operation list, click New group.

    6. If you want to display a secondary axis to reflect a different scale for a series group, click the group in the Groups box under Add axis, and then select a position for the second axis in the Axis position box. For example, if your primary axis is on the left side of the chart and you want a second axis on the right side of the chart, click Right in the Axis position box and then click Add.

    7. In the chart, select the series you grouped so that you can change it to a different chart type.

    8. Click the Type tab.

    9. Click the type of chart you want to use in the list, and then click the subtype in the box to the left.

Note   Not all chart types can be combined. For example, you cannot combine 2-D and 3-D chart types, nor can you combine bar and column charts, or column and pie charts.


Create multiple charts in a chart workspace

When your chart is based on data in a PivotTable list or in a database table or query, you can create multiple charts in the same chart workspace to make it easy to compare different aspects of your data. The following example shows multiple charts that compare sales data by using individual charts for each salesperson. The charts in the example also use the same scale, which is optional.

Multiple charts in one chart workspace

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. On the toolbar, click Commands and Options Button image, and then click the General tab.
  3. Under General commands, select Chart Workspace in the Select list.
  4. Under Add, click Plot multiple charts Button image.
  5. If you want to use the same axis scale for all charts, click Multiple charts use same scale Button image.
  6. Click Field List Button image on the toolbar, and then use the scroll bars and the expand indicators ( Plus box and Minus box boxes) to find the field you want to add to the chart.
  7. Click the field and drag it to the drop area on the chart that says Drop MultiChart Fields Here.

    You can add multiple fields to the MultiChart area.


Change the display of multiple charts

When you have multiple charts in a chart workspace, you can change whether they are displayed horizontally or vertically, and you can change the number of charts displayed in each row or column.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. On the toolbar, click Commands and Options Button image, and then click the General tab.
  3. In the Select box, click Chart Workspace.
  4. Under Chart workspace, in the Chart layout list, click Horizontal or Vertical.

    If you select Automatic, the charts will be displayed horizontally or vertically depending on the size and shape of the workspace.

  5. If you want to limit the number of charts across and down the chart workspace, type the maximum number of charts you want displayed in the Maximum charts per row/col box.

    Note This option is available only when you click Horizontal or Vertical in the Chart layout list.


About using a chart in the browser

Show Working in a browser vs. working in a design program

The Microsoft Office Chart Component lets you create a chart in a design program for run-time access in a Web browser or other run-time environment. Microsoft Excel, Microsoft FrontPage, and Microsoft Access are design programs that you can use to create and modify charts.

The run-time features discussed in this topic are available to users who have the Microsoft Office Web Components installed. They can do this by installing Microsoft Office 2003 or, if their company has an Office 2003 site license, by downloading the Office Web Components from their corporate intranet. Users should also have a Web browser that fully supports interactive charts, such as Internet Explorer 5.01 SP2 or later. If you plan to create charts for use in a program other than a Web browser, such as a Microsoft Visual Basic form to be viewed as part of a custom solution, see Help for your design program to find out how your users can access and interact with the chart.

Show What users can do in the browser

In the browser at run time, users whose systems meet the necessary requirements can view the chart you've created. They can see the chart automatically update as changes are made to the data reflected in the chart, as well as interact with the chart to the level you allow. For instance, if you create a chart that uses stock prices, and the data is updated to reflect the most recent price, users will see the chart change to reflect the new price. If you allow the chart toolbar to be shown, users can also change the chart type, change some formatting, and— depending on the data type— show, hide, or rearrange data in the chart.

Changing the data reflected in a chart Users can change the data reflected in a chart, depending on the type of source data and level of access you give them. For instance, you can provide a spreadsheet and corresponding chart on a Web page and allow users to change some but not all of the data in the spreadsheet. When the user changes the data in the spreadsheet, the chart is updated. Or, if your chart is based on a PivotTable list or a database table or query, and you display fields in the chart, users can move, rearrange, filter, and sort the fields directly in the chart.

Changing the chart You can give users access to the chart toolbar or the Commands and Options dialog box, allowing them to make many of the same changes that you can make in the design program. For example, users could change the chart type or any type of formatting in the chart. Alternatively, you can disable use of the Commands and Options dialog box or toolbar, and instead write scripts to allow only changes that you choose. For more information about using scripts and programming a chart for the Web, see About getting help for charts.

How the browser version affects what users can do To interact with a chart means that you can change the chart's source data and see the chart update. To interact with a chart, users need Microsoft Internet Explorer version 5.01 SP2 or later. It's recommended that users install Internet Explorer 5.01 SP2 or later to take full advantage of the interactive capabilities of the chart and its source data.

Help for the user in the browser Browser users have access to a run-time version of chart Help, which includes topics about default run-time capabilities such as changing the chart type, hiding or showing the legend, sorting, filtering, and moving fields. The run-time version of Help does not include information on using the Commands and Options dialog box. If you modify the capabilities allowed at run time, you should provide assistance or instructions on the Web page.

Users also have access to system Help topics in the browser, and any Help content that's provided with the data source for the chart. For example, if a chart uses data from a PivotTable list, users can view the PivotTable Component Help to learn how to change the data in the PivotTable list.


About restricting the use of a chart in the browser

Some chart features are available only when you're working in the design program. Other features are available both while designing the chart and while using it in a browser. However, as a designer, you can restrict the availability of these features in the browser by changing property settings.

Regardless of the capabilities you have made available to the user in a browser, whether users can take advantage of those capabilities depends on what browser they are running and whether they have the appropriate license. To take full advantage of the capabilities of charts, users need Microsoft Internet Explorer version 5.01 SP2 or later and an Office 2003 license.

Show Capabilities that are not available in a browser

In the browser, users cannot change information about the source database, database fields, and source data when the chart is based on a database or PivotTable list. Only the designer of a chart can specify what source database to use and what subset of the data to make available as the source data in the chart.

In the browser, users can display only the data you've made available; if a password is required to access the database, users must enter the password to display the data. Users can refresh the chart in the browser to reflect any changes in the source data, but they cannot access the source database or make changes to the data it stores.

Show Capabilities that are usually available in the browser

If their browser fully supports charts, users can make many of the same changes to the chart in the browser that you can make in the design program, provided you give them the ability to do so. For instance, you can allow or prevent display of the chart toolbar or the Commands and Options dialog box.

Subject to any restrictions you set when you design the chart, users can add or move fields from the field list; sort, filter, and group data in a chart based on a database or PivotTable list; make changes to data when a chart is based on a spreadsheet or datasheet; change formatting, chart type, and more— just as you can in the design program.

Show Restrictions that you can set

To prevent users from making changes to a chart or accessing portions of the source data that you don't want to make available, you can prevent them from doing the following:

  • Accessing the Commands and Options dialog box
  • Grouping items
  • Filtering data
  • Using selection marks
You can make further restrictions by using scripting; for more information, see how to get programming help for charts.


Restrict the use of a chart in the browser

When you change a setting to prevent a user from doing a specific task in a chart, you will not be able to do that task in the design program unless you change the setting again.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.

  2. Select the chart workspace by clicking the blank area outside the plot area but within the chart boundaries.

  3. On the toolbar, click Commands and Options Button image, and then click the Show/Hide tab.

  4. Under Let users view, do one of the following:

    Show Prevent adding, changing, or removing filters

    • Clear the Allow pivot filtering check box.

    Show Prevent grouping or changing the layout

    • Clear the Allow pivot grouping check box.

    Show Prevent display of selection marks

    • Clear the Selection marks check box.

    Show Prevent access to the Commands and Options dialog box

    • Clear the Commands and Options dialog box check box.


Show or hide passive alerts

A passive alert indicates that your chart contains an error. The alert appears as an exclamation point at the bottom left corner of the chart, and when you click it, information about the alert is displayed. Passive alerts appear in error situations such as when a texture file cannot be found or when a database connection fails.

  1. On the toolbar, click Commands and Options Button image, and then click the General tab.
  2. Under General commands, click Chart Workspace in the Select box.
  3. Click the Show/Hide tab.
  4. Under Errors, select or clear the Show passive alerts check box.


Show or hide ScreenTips

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. On the toolbar, click Commands and Options Button image, and then click the General tab.
  3. Under General commands, click Chart Workspace in the Select box.
  4. Click the Show/Hide tab.
  5. Under Show by default, select or clear the ScreenTips check box.


About adding and removing fields

When you add data to a chart that is based on database data or on a PivotTable list, you select fields from the source data for display in the chart.

Show Viewing the fields available to the chart

Use the chart field list to view the list of fields that are available from the source data and to add fields to the category, series, filter, and data areas of the chart. In the field list, a fieldset contains one or more fields. The fields or fieldsets that are not bold are available to be displayed in the chart. Fieldsets that are bold contain one or more fields that are already displayed. You can scroll and click the expand indicators ( Plus box or Minus box boxes) to show or hide additional fields in each fieldset. If you display multiple fields from the same fieldset, the fields must be displayed in the same drop area.

If your chart is based on source data from an OLAP database, such as one created by using Microsoft SQL Server OLAP Services, you'll notice that some fields can be moved only to the series or category areas. Other fields, listed under Totals, can be used only as data fields.

Show Adding fields to the category or series area

Use category and series fields to summarize and compare data. These fields display the unique items of data within a field as either a series (related data points) or category (related groups that consist of one point from each series).

Category and series fields in chart

Callout 1 Series field, shown with legend. The names within the legend are items within the field.

Callout 2 Category field. The names shown across the bottom of the x axis are items within the category field.

Show Adding fields to the data area

Data fields provide the values to be summarized in the chart. When you add a field to the data area, the values from the field are displayed as data markers for the category and series items.

Data field in chart

Callout 1 Data field

Callout 2 Data markers that represent data points

Show Adding fields to the filter area

A filter field is similar to a page field in a Microsoft Excel PivotTable report. Use filter fields to confine the view to a particular part of the available data. When you select an item in a filter field, data is displayed and calculated only for that item. For example, if you add a Year filter field, you can have the chart display data for all years or for just one year at a time.

Chart with filter field

Callout 1 Filter field

Callout 2 Filter field item that is currently displayed

Callout 3 Data markers for the selected year

Show Adding total fields

You can create new data fields by adding total fields to the chart. Total fields provide summarized data for use as data fields. For example, if your source data includes a Sales field that shows the amount of each sale, you could add a Sum of Sales total field to calculate the total sales, or change a total field to a Count of Sales field to display how many units were sold.

Show Removing fields

To eliminate data that you don't want to see or summarize, you can remove fields from the chart. A field that you remove is no longer displayed in the chart, but it remains available in the field list for viewing.

Show Moving fields

After you add a field to a chart, you can move it to other areas. Moving a field changes the layout of the chart and the way the data is displayed.

Show Impact of adding and removing fields on filtering

Filter settings are retained when you remove a field and add it back later.


Add, change, or remove a field

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Do one or more of the following:

Show Add a field

  1. On the chart toolbar, click Field List Button image.

  2. Use the scroll bars and the expand indicators ( Plus box and Minus box boxes) to find the field you want to add to the chart.

  3. Click the field and drag it to the appropriate drop area on the chart.

    You can add multiple fields to each location.

    Notes

    • As an alternative to dragging the field, in the field list select the area to which you want to add a field (areas are listed at the bottom of the field list), and then click Add to. If Add to becomes unavailable when you select the area where you want to put the field, the field you selected might not be available for use as a category, series, data, or filter field.

    • If your chart is based on source data from an OLAP database, only the fields that are not listed under Totals in the field list can be used as category, series, or filter fields.

    • You can add more than one field to an area on the chart. The placement of fields on the chart determines the hierarchical order in which the data is displayed. Outer fields can be expanded or collapsed to show more or less information in inner fields.

    • The MultiChart Area is available only if your chart is based on data from a PivotTable list or database table or query, and you have selected Plot multiple charts Button image on the General tab of the Commands and Options dialog box.

Show Change the summary function for a data field

  1. Click the data field that contains the values you want to summarize.

  2. On the chart toolbar, click AutoCalc Button image, and then click the summary function you want.

Note The AutoCalc button is unavailable when the source data for the chart does not allow you to add total fields— for example, when the source data is from an OLAP database.

Show Remove a field

When you remove a field from a chart, data from the field is no longer displayed, but the field remains available in the field list for viewing.

  1. Click the field label of the field that you want to remove from the chart.

  2. Drag the field toward the outside of the chart until the pointer changes to Pointer with red X.


Move a field in a chart

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. If the drop areas for the chart are not displayed, show them by right-clicking the chart and then clicking Drop Areas on the shortcut menu.
  3. In the chart, click the field you want to move, and then drag it until the pointer changes to represent the area where you want to move it.
    This pointer | Represents this area
    Pointer for series area | Series area
    Pointer for category area | Category area
    Pointer for filter area | Filter area
    Pointer for data area | Data area
    Pointer for multichart area | MultiChart area
  4. Release the mouse button.

Note The pointers shown above apply only when you're moving fields within the chart, not when you're adding fields from the field list.


About changing the layout of fields in a chart

When a chart is based on data from a database or PivotTable list, you can control how the data is shown by moving fields and customizing the layout. A different layout lets you compare values for different elements in your data in ways that are meaningful for your audience. When you make the chart available to browser users, they can make further changes to the initial layout.

Show Using the drop areas

You change the layout of a chart by moving the fields to predefined drop areas within the chart workspace. To do this, drop areas must be shown in the chart.

Drop areas are displayed differently depending on the chart type. For example, a series drop area is not displayed for a pie chart because pie charts consist of only one series. If you plot multiple charts, you will see a drop area for multi-chart fields. This type of drop area is not displayed for single charts.

After you move fields to the drop areas so that the drop-area captions are covered up, you can still drag additional fields to the areas.

Show Moving fields to the category and series areas

When you move a field to the series area, the unique items of data within the field are displayed as data series in the chart. These series are represented by colored data markers, and their names appear in the chart legend.

When you move a field to the category area, the unique items of data are displayed as categories, or related groups of data. Each category consists of one point from each data series. Category labels usually appear across the x axis of the chart, although this can vary depending on the type of chart you are using.

Show Moving fields to the data area

Data fields provide the values to be summarized in the chart. When you move a field to the data area, the values from the field are used as the data being measured in the chart.

Show Moving fields to the filter area

A filter field is similar to a page field in a Microsoft Excel PivotTable report. Filter fields allow you to confine the view to a particular part of the available data. For example, when you move a Product field to the filter area, you can have the chart display category and series values for one product at a time.

Show Moving fields to the MultiChart area

When your chart is based on data from a PivotTable list or from a database table or query, you can plot multiple charts. When you move a field to the MultiChart area, items in that field become separate charts. For example, if you move the Salesperson field to the MultiChart area, a chart is created based on data for each salesperson in that field. In the following example, the Salesperson field is in the MultiChart area, but it's filtered so it displays individual charts for Buchanan and Davolio.

Filtered Salesperson field in MultiChart area

Show Moving the category or series fields to inner or outer levels

When a chart contains multiple series or category fields, the fields that are closest to the data are referred to as inner fields. The other fields are outer fields. In the following example, Year is the outer field and Salesperson is the inner field. The inner field items are displayed as salespeople's names, and the outer field items are displayed as the years 1997 and 1998.

Multiple fields in a chart

Callout 1 Inner field items

Callout 2 Outer field items

You can expand and collapse multiple fields to show more or less information in a particular field. For example, you can collapse the outer field (Year) in the example so that the inner field items are no longer displayed.

Chart with collapsed field

Show Adding and removing fields

The layout of a chart does not have to include all fields that are available from the source data. If more data is available, you can add fields to the chart. For example, if the chart summarizes sales revenue, and the source data also includes sales quantities, you might add the Quantity field as a data field to summarize both revenue and quantity of products sold. You can also remove fields from the chart layout that you no longer want to see.

Show Restrictions on moving and adding fields

If you work with a chart that is based on source data from an OLAP database, such as one created with Microsoft SQL Server OLAP Services, you'll notice that some fields can be moved only to the series, category, and filter areas, and that fields in the data area cannot be moved to other areas.

Show Impact of changing layout on filtering

Filter settings are retained when you move or remove a field. This means that when you move a series or category field to the filter area and back, previously hidden items are again hidden. If you remove a field and later add the field back to the layout, the same items are again hidden.

Show Restricting use of a chart in the browser

You can restrict the user's ability to change the layout of the chart by preventing fields from being added and moved at run time.


Select a chart item

  1. In the design window, make sure the chart is activated. See Help in your design program for specific information on how to activate a chart.

    Note To activate a chart you must click it either once or twice, depending on your design program. For example, in data access pages you must click once to select the chart (white selection handles will appear on its border) and click a second a time to activate the chart (a hatched border will appear around the chart).

  2. Do one of the following:

    Show Select an item by using the mouse

    • Click once to select a group of items, such as a group of data markers in a data series.
    • If you want to select an individual item within a group, such as an individual data marker within a data series, click once to select the group and then click a second time on the individual item you want to select.

    Show Select an item by using the Commands and Options dialog box

    1. Right-click the chart, and then click Commands and Options.
    2. Click the General tab.
    3. In the Select box, click the chart item you want. For example, to select the first value axis on the chart, click Value Axis 1 in the list.


Change the chart type

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.

  2. Do one of the following:

    • To change the chart type for the entire chart, select the chart workspace by clicking the blank area outside the plot area but within the chart boundaries.

    • To change the chart type for an individual data series, select the data series.

      Note When you change the chart type for an individual series, you are creating a combination chart. Not all chart types can be combined. For example, you cannot combine 2-D and 3-D chart types, nor can you combine bar and column charts, or column and pie charts.

  3. On the toolbar, click Chart Type Button image.
  4. In the list, click the chart type you want, and then click the subtype you want.


Add, remove, or change axes

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Do one or more of the following:

    Show Add an axis

    1. Click the chart workspace (the blank area between the plot area and the chart boundary).
    2. On the toolbar, click Commands and Options Button image, and then click the Series Groups tab.
    3. Under Series groups, in the Select one or more series box, select the group of series for which you want to add an axis.
    4. If you want the axis to reflect a different series, you can group, ungroup, or merge series with other groups.
    5. Under Add axis, in the Groups box, select the group that you want to add the axis for, and then in the Axis position box, select where you want the axis to be displayed.
    6. Click Add.

    Show Remove an axis

    1. Click the axis you want to remove.
    2. Press DELETE.

    Show Change the position of an axis

    1. Click the axis you want to change.
    2. On the toolbar, click Commands and Options Button image, and then click the Format tab.
    3. Under Position, in the Position box, select the option you want.

    Show Add, change, or remove a title on an axis

    Show Add a title

    1. Click the axis to which you want to add a title.

    2. On the toolbar, click Commands and Options Button image, and then click the General tab.

    3. Click Add Title Button image.

    Show Remove a title

    1. Click the title you want to remove.

    2. Press DELETE.

    Show Change a title

    1. Click the title you want to change.

    2. On the toolbar, click Commands and Options Button image.

    3. Do one or more of the following:

      Show Change the text format

      1. Click the Format tab.

      2. Select the options you want under Text format.

      Show Change the title contents

      1. Click the Format tab.

      2. Under Caption, type a new title in the Caption box.

      Show Change the border or fill color

      1. Click the Border/Fill tab.

      2. Select the options you want under Border and under Fill.

    Show Change the font or size of the axis value text

    1. Click the axis text that you want to change.
    2. On the toolbar, click Commands and Options, and then click the Format tab.
    3. Under Text format, select the options you want.

    Show Change the spacing of tick marks and labels on the category axis

    Each category on the category axis is identified by a label and separated from other categories by tick marks. You can change the intervals at which labels and tick marks appear.

    1. Click the category axis you want to change.

    2. On the toolbar, click Commands and Options Button image, and then click the Scale tab.

    3. Under Scale, do one or more of the following:
      • Type a number in the Labels spacing box to specify the frequency at which category labels must be labeled. For example, type 1 to label every category, type 2 to label every other category, and so on.
      • Type a number in the Tick mark spacing box to specify the number of categories you want between each pair of tick marks. For example, type 1 to have one category between each tick mark; or type 2 to have two categories between each tick mark.

    Show Change where the category axis crosses the value axis

    1. Click the category axis you want to change.
    2. On the toolbar, click Commands and Options Button image, and then click the Scale tab.

    3. In the Crosses with list under Crossing Axis, click the axis that you want to cross the category axis.

    4. If you want to specify where the crossing axis should cross the category axis, select the Custom check box and then type a value in the box next to it.

    Show Change a timescale category axis

    When your category data is made up of numeric dates, the chart automatically uses a timescale axis. The timescale category axis displays dates in chronological order at specific intervals even if the dates in your data are not in order.

    1. Click the category axis you want to change.
    2. On the toolbar, click Commands and Options Button image, and then click the Scale tab.
    3. Under Timescale, click an option in the Timescale box:

      None Use a category axis that uses your dates as text labels rather than a timescale axis.

      Auto The chart uses a timescale axis and determines the most appropriate Group size (that is, group dates by days, months, quarters, and so on) based on the axis scale.

      Manual The chart uses a timescale axis, but you determine the most appropriate Group size (that is, group dates by days, months, quarters, and so on) for the axis based on your data.

    4. If you chose Auto or Manual in the previous step, select how you want dates displayed and totaled in the remaining options under Timescale.

    Show Reverse the direction of values on an axis

    1. Click the axis for which you want to reverse the direction of values.

    2. On the toolbar, click Commands and Options Button image, and then click the Scale tab.

    3. Under Order, select the Show values in reverse order check box.

    Show Split values in an axis scale

    When you have very low and very high values in your chart, you can split the value axis (or timescale axis) so that all of the values are shown clearly. For example, if your values range from 0 to 950, with values falling between 0 and 100 and between 750 and 950, you can split the axis so that it has two scales: The bottom scale ranges from 0 to 100, and the top scale ranges from 750 to 950, thus eliminating unnecessary chart space between 100 and 750.

    Chart with split axis

    1. Click the value axis that you want to split.
    2. On the toolbar, click Commands and Options Button image, and then click the Axis tab.
    3. Select the Split axis check box, type a value in the Split begins at box (which marks the end of the first scale), and then type a value in the Split ends at box (which marks the beginning of the second scale).

    Show Change the line weight or color of the axis

    1. Click the axis line you want to change.
    2. On the toolbar, click Commands and Options Button image, and then click the Line/Marker tab.
    3. Under Line, select the options you want.

    Show Change the number format of the axis values

    1. Select the value axis for which you want to change number formatting.
    2. On the toolbar, click Commands and Options Button image, and then click the Format tab.
    3. Under Text format, in the Number box, click the format you want.

    Show Change the scale of a value axis

    The scale specifies the range of values on an axis, the way the axis values are displayed, the intervals at which the values occur, and the point at which one axis crosses another.

    1. Click the value axis you want to change.
    2. On the toolbar, click Commands and Options Button image, and then click the Scale tab.
    3. Do one or more of the following:
      • Under Crossing Axis, select in the Crosses with box the axis you want to cross the value axis. If you want to specify where the crossing axis should cross the value axis, click Custom, and then type a value in the Custom box.
      • To specify either or both the highest and lowest values you want to appear on the axis, select the Custom max and/or Custom min check boxes, and then type the appropriate values in the adjacent boxes.
      • If you want to specify the intervals between major and/or minor tick marks and gridlines, select the Custom major unit and/or Custom minor unit check boxes, and then type the appropriate values in the adjacent boxes to specify the intervals you want.
      • To recalculate values on the axis based on a power of 10, 2, or e, select the Logarithmic scale check box, and then click the power you want to use in the Use base list.


Add, remove, or change titles

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Do one or more of the following:

Show Add a title

  1. Click the item in the chart to which you want to add a title: the chart workspace or a chart axis.

  2. On the toolbar, click Commands and Options Button image, and then click the General tab.

  3. Click Add Title Button image.

Show Remove a title

  1. Click the title you want to remove.

  2. Press DELETE.

Show Change a title

  1. Click the title you want to change.

  2. On the toolbar, click Commands and Options Button image.

  3. Do one or more of the following:

    Show Change the text format

    1. Click the Format tab.

    2. Select the options you want under Text format.

    Show Change the title position

    1. Click the Format tab.

    2. Under Position, in the Position box, click Top, Bottom, Left, or Right.

    Show Change the title contents

    1. Click the Format tab.

    2. Under Caption, type a new title in the Caption box.

    Show Change the border or fill color

    1. Click the Border/Fill tab.

    2. Select the options you want under Border and under Fill.


Add, remove, or change a legend

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.

  2. Do one of the following:

    Show Add or remove a legend

    1. Select the chart workspace by clicking the blank area outside the plot area but within the chart boundaries.

    2. On the toolbar, click Show/Hide Legend Button image.

    Show Change a legend

    1. Click the legend you want to change.
    2. On the toolbar, click Commands and Options Button image.
    3. Do one of the following:

    Show Change the legend position

    1. Click the Format tab.

    2. Under Position, in the Position box, click Top, Bottom, Left, or Right.

    Show Change the border weight or color and background color

    1. Click the Border/Fill tab.

    2. Under Border, select the Color, Weight, or Dash options you want.

    3. Under Fill, select the Fill Type and Color you want.

    Show Change the font or size of text

    1. Click the Format tab.

    2. Under Text format, select the options you want.


Add, remove, or change data labels

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Do one or more of the following:

    Show Add data labels

    The chart type associated with the selected data series or data point determines the type of data labels that can be added.

    1. Select the data series to which you want to add data labels.

    2. On the toolbar, click Commands and Options Button image, and then click the General tab.

    3. Click Add Data Label Button image.

    Note You cannot add a data label to a single data point. However, after adding data labels to a series, you can select and delete individual data labels that you do not want to display. To select an individual data label, click once on a data label to select all the labels in the series, and then click a second time on the individual data label you want to delete.

    Show Remove data labels

    1. Click a data label in the set of data labels you want to remove.

    2. Press DELETE.

    Show Change data labels

    Show Change the contents of data labels

    1. Click a data label in the set of data labels you want to change.
    2. On the toolbar, click Commands and Options Button image, and then click the Data Labels tab.
    3. Under Data Label, select one or more of the following that you want shown in the data label: Category name, Value, Series name, Bubble size (available for bubble charts only), or Percentage.
    4. If you select more than one of the above items to show in your data labels, you can separate each with a comma or space, or put each on a separate line. To do so, click the option you want in the Separator list.

    Show Change the text format of data labels

    1. Click a data label in the set of data labels you want to change.
    2. On the toolbar, click Commands and Options Button image, and then click the Format tab.
    3. Select the options you want under Text format.

    Show Change the position of data labels

    1. Click a data label in the set of data labels you want to change.
    2. On the toolbar, click Commands and Options Button image, and then click the Data Labels tab.
    3. Under Data Label, click the location in the Position list where you want the data labels to appear in relationship to the data markers. For example, click Inside Base to show data labels inside and at the bottom of data markers in a standard column chart.

    Show Change the border or fill for data labels

    1. Click a data label in the set of data labels you want to change.
    2. On the toolbar, click Commands and Options Button image, and then click the Border/Fill tab.
    3. Select the options you want under Border and under Fill.


Show, hide, or change gridlines

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Do one or more of the following:

Show Show or hide gridlines

  1. Click the axis for which you want to show or hide gridlines.

  2. On the toolbar, click Commands and Options Button image, and then click the Axis tab.

  3. Under Ticks and Gridlines, select or clear either or both the Major gridlines or Minor gridlines check boxes.

Show Change gridlines

  1. Click the gridlines you want to change.

  2. On the toolbar, click Commands and Options Button image, and then click the Line/Marker tab.

  3. Under Line, select the options you want.


Change the text size and font for chart items

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the axis title, data labels, R-squared label and equation label, legend text, chart title, or other chart text that you want to change.
  3. On the toolbar, click Commands and Options Button image, and then click the Format tab.
  4. Under Text format, select the options you want.


Change line color, weight, and style in chart items

You can change the line weight and color of series lines, axis lines, gridlines, trendlines, error bars, and other types of lines in a chart.

  1. Click the line you want to change.
  2. On the toolbar, click Commands and Options Button image, and then click the Line/Marker tab.
  3. Under Line, select the options you want.


Change borders, fills, and background colors

You can change the border weight and color and the background color for the chart workspace, the plot area, the legend, a single data marker, a series of data markers, titles, and other chart items that support filled areas.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Select the chart area or item you want to change.
  3. On the toolbar, click Commands and Options Button image, and then click the Border/Fill tab.
  4. Do one of the following:
    • To change the border color, weight, or style, select the options you want under Border.
    • To change the background color or fill, select an option under Fill in the Fill Type list (Solid color, Pattern, Gradient, or Picture/Texture) and then select the options you want that pertain to that fill type. For example, if you select Gradient, you can then choose the number of colors in the gradient and the gradient style, or you can choose a preset gradient.


Rotate or flip the plot area

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.

  2. Click the plot area that you want to change.
  3. On the toolbar, click Commands and Options Button image, and then click the General tab.
  4. Do one of the following:
    • To flip the plot area horizontally or vertically, click Flip Horizontal Button image or Flip Vertical Button image.

    • To rotate the plot area clockwise or counterclockwise in 90-degree increments, click Rotate Clockwise Button image or Rotate Counterclockwise Button image.


Pull out slices in pie or doughnut charts

Pie chart with slice pulled out and doughtnut chart with rings pulled apart

In doughnut and stacked pie charts, you can pull out slices in the outer ring only.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.

  2. Do one of the following:

    • To pull out all slices or rings, click the chart workspace (the blank area between the plot area and the chart boundary).

    • To pull out one slice or ring, click the data slice or ring once to select the data series it is in, and then click a second time to select the individual slice or ring.

  3. On the toolbar, click Commands and Options Button image, and then click the General tab.
  4. Under Chart, in the Explosion box, type a number to represent— as a percentage of the radius— the distance between the point of the slice or slices and the center of the doughnut or pie.


Rotate the position of the slices in a doughnut or pie chart

Example of rotated slices in pie chart

The order in which data series are plotted in pie and doughnut charts is determined by the order of the source data. Use this procedure to rotate slices within the 360 degrees of the pie or doughnut circle.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.

  2. On the toolbar, click Commands and Options Button image, and then click the General tab.
  3. In the Select list, click Chart Workspace.
  4. Under Chart, in the Angle of first slice box, type a number between 0 and 360 to specify the angle at which you want the first slice to appear.


Change the overlap or spacing of data markers in bar and column charts

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.

  2. Click the chart workspace (the blank area between the plot area and the chart boundary).

  3. On the toolbar, click Commands and Options Button image, and then click the General tab.

  4. Do one of the following:

    • To change the overlap of the data markers in each category, type a number between –100 and 100 in the Overlap box to represent the percentage of overlap you want. For example, at 100, bars or columns overlap each other, while at –100, a bar or column width of space is displayed between each. At 0, bars or columns are adjacent to each other.

    • To change the spacing between categories of data markers, type a number between 0 and 500 in the Gap width box to represent the percentage of gap width you want. For example, at 0, data markers touch; at 100, there is one bar or column width of space between each.


About data series and categories

Data series are represented by names in the legend and appear as like-colored groups of data markers on your chart. One point from each data series makes up a category. Categories, then, are groups of data points with similar characteristics. The values for categories and data series can come from either the rows or columns of your data.

For example, suppose your data looks like this:

Data in a spreadsheet

If you want to compare unit sales in Europe categorized by quarters, your series would be in columns and your categories in rows, and the chart would look like this:

Chart

If you specify that your series are in rows, instead, the chart would use Europe as the category, and each quarter would make up a series, like this:

Chart


Change series to categories or categories to series

To change the emphasis on which aspects of data you want to compare, you can change which parts of your data appear as categories and which appear as series. The following example emphasizes comparison of countries (as categories).

Chart with years as data series

The following example emphasizes comparison of years (as categories).

Chart with countries as data series

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Do one of the following:
    • If your chart is based on data from a PivotTable list or external data, click By Row/Column Button image on the toolbar.
    • If your chart is based on data in a data sheet, right-click the chart, click Data on the shortcut menu, click the Data Sheet tab, then click By Row - By Column Button image.


Change the orientation and position of the category axis label

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the label for the category axis.
  3. On the toolbar, click Commands and Options Button image, and then click the Format tab.
  4. To change the orientation or the location of the axis label, click the drop down list adjacent to Orientation or Position respectively.
  5. Select the option you want.


Map chart colors to distinguish ranges of data

You can format data markers so that graduated or solid colors represent values or ranges of data. For example, the following chart shows locations of water samples and corresponding pH measurements distinguished by graduated colors, where higher percentages of pH levels are represented by increasingly lighter colors.

Chart with mapped colors

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the data series you want to format.
  3. On the toolbar, click Commands and Options Button image, and then click the Conditional Format tab.
  4. Select the Conditionally color data points check box.
  5. In the Style list, click the style of conditional formatting you want.
    • Three Point Gradient Uses three graduated colors to distinguish between the smallest value and zero (or the value you indicate) and between zero (or the value you indicate) and the largest value.
    • Two Point Gradient Uses two graduated colors to distinguish between smallest and largest values.
    • Two Segment Solid Uses two solid colors to distinguish between values smaller than zero (or the value you indicate) and larger than zero (or the value you indicate).
    • Bottom Values Uses a solid color to distinguish the smallest values in a range.
    • Top Values Uses a solid color to distinguish the largest values in a range.
  6. Next to Colors, choose the Beginning Color, Middle Color, or End Color (where applicable) to represent value ranges.
  7. If you want to indicate a value on which to base your conditional format, type that value in the Value box and select the % check box if appropriate. For example, to format the top 90% of values, type 90 in the value box and then select the % check box.
  8. If your chart is based on data from a database or a PivotTable list, you can specify additional values on which to base conditional formatting. For example, if you plot sales data for products in different regions, you can format the data markers based on an additional data set such as profit amount. To do this, clear the Conditional format based on data value field check box, and then add the data field you want to use to the Format Data drop area that appears on the chart.


Add a picture or textured fill to a chart

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Select the chart area or item to which you want to add a picture— for example, the chart workspace, the plot area, or a data marker.
  3. On the toolbar, click Commands and Options Button image, and then click the Border/Fill tab.
  4. Under Fill, in the Fill Type list, click Picture/Texture.
  5. Do one of the following:

    Show Add your own picture

    1. Click URL and type the location and name of the image you want to use.

      For location, you can use a Web address starting with a valid protocol such as:

      http://www.microsoft.com/images/image_name.gif

      or a universal naming convention (UNC) path such as:

      \\server\folder\image_name.gif

      or a path to a file on your hard drive, such as:

      C:\image_image.gif

    2. Press ENTER.
    3. Next to Format, click an option for how you want the picture to appear in the chart.
      • Tile Tiles the image to fill the selected space.
      • Stretch Stretches the image to fill the selected space.
      • Stack Stacks the image to fill the selected space.
      • Stack scale Stacks the image according to the number of value axis units you specify in the units box. For example, if a data marker in a column chart measures $6,000 on the value axis, and you specify units to represent $1,000, six images would be stacked in the data marker.
      • Stretch plot Stretches the image across the entire chart, but fills only the selected item or items.
    4. If you chose Stack scale, type the units you want each image to fill in the units box, and then press ENTER.

    Show Add a built-in texture

    • Click Preset and click the texture you want to use in the Preset list.


Change the view of a 3-D chart

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. On the toolbar, click Commands and Options Button image, and then click the General tab.
  3. Under General commands, click Chart Workspace in the Select box.
  4. Click the 3D View tab.
  5. Click the Projection mode you want:

    Perspective Button image Chart objects are represented in relative distance and positions as they might appear to the eye.

    Orthographic Button image Chart objects are represented as if they all appear on the same plane, on a surface that is perpendicular to both the view and the lines of projection.

  6. Make the changes you want by dragging the sliders for each option. For example, to rotate the lighting source for the chart, drag the rotation slider Slider image under Lighting.

Notes

  • To return to the default view of the chart, click Default Button image next to Default view.
  • To make the height ratio or depth ratio proportionate, select the Height Ratio or Depth Ratio check box.


Number formats

Some of the content in this topic may not be applicable to some languages.

The Microsoft Office Chart Component uses the regional settings in Microsoft Windows Control Panel to determine how number, currency, date, and time formats are displayed in charts, both in the design program and at run time in the browser. For example, these settings determine whether years are displayed with two digits or four digits. See Windows Help for information about these formats.

In Microsoft Visual Basic or in a script, you can assign custom number formats to the data. For more information, see Help for writing scripts and programs that use charts for the Web.

The following number formats are available in the Number list under Text format on the Format tab in the Commands and Options dialog box, which is available when an item such as an axis value or data label is selected on the chart.

Format | Effects
General | Displays a number with no thousands separator, and preceded by a minus sign if negative. For example, –36403. Displays up to 11 digits, or 10 digits with a decimal point, rounding additional digits to the right of the decimal point. Displays numbers with more than 11 digits to the left of the decimal point in scientific format. Displays numbers right aligned and text left aligned.
General Date | Displays a serial number as a date or time, or both. For example, 36403.24583 appears as 8/31/99 05:54 AM. The decimal part of the number is the time. If a number has no decimal part, the format displays only a date. For example, 35920 appears as 5/5/98. If a number has only a decimal part, the format displays only a time. For example, .77 appears as 6:28 PM.
Long Date | Displays a date by using the Windows Control Panel regional setting for the long date format. An example is Thursday, January 25, 2001.
Medium Date | Displays a date according to the Windows Control Panel regional setting for the month and date separator, and displays two digits for the year. For example, Jan/25/01.
Short Date | Displays a date by using the Windows Control Panel regional setting for the short date format. For example, 1/25/01.
Long Time | Displays time by using the Windows Control Panel regional setting for the long time format. For example, 8:45:36 PM.
Medium Time | Displays time in 12-hour format, including the AM or PM designator and not including seconds. For example, 8:45 PM.
Short Time | Displays time in 24-hour format, not including the AM or PM designator and not including seconds. For example, 8:45 PM appears as 20:45.
Currency | Displays a number by using the Windows Control Panel regional setting for currency, with a currency symbol and a thousands separator, if appropriate, and two decimal places. For example, $2,532.75.
Euro Currency | Displays a number as euro currency, with a thousands separator and two decimal places. The euro sign is placed before or after the number according to the regional settings for currency in Windows Control Panel. For example, with the default United States English regional settings, Euro 12.35.
Fixed | Displays a number with two decimal places. For example, 68.3 appears as 68.30.
Standard | Displays a number with two decimal places, using the thousands separator specified in the list separator setting in the Windows Control Panel regional settings. For example, 1800 appears as 1,800.00.
Percent | Displays a number multiplied by 100, with two decimal places and including a percent sign. For example, 0.8914 appears as 89.14%.
Scientific | Displays a number in exponential notation. For example, 1.25E+10.
Yes/No | Displays No if the number is 0, and displays Yes for any other number.
True/False | Displays False if the number is 0, and displays True for any other number.
On/Off | Displays Off if the number is 0, and displays On for any other number.


Change number formatting

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Select the item in the chart for which you want to change number formatting, such as an axis or data labels.
  3. On the toolbar, click Commands and Options Button image, and then click the Format tab.
  4. Under Text format, in the Number box, click the format you want.

Note You cannot change the number format for category axis labels.


About using dates in charts

When you create a chart from data that uses dates, and the dates make up the category (x) axis in the chart, you can use a timescale category axis to represent the period of time between the dates.

Display of dates The timescale category axis displays dates in chronological order at specific intervals, even if the dates are not arranged in those intervals in your data.

The timescale group size (days, weeks, months, quarters, or years) is initially set by the chart according to the range of dates in your data. If the group size is initially set at months, for example, you can change the group size to quarters for a different perspective. When you do this, the chart automatically totals the points in each group so it can plot one point per group, like in the following example where the points in each quarter are summed, and the summed value is plotted. You can change the summary function used to create group totals.

Data for chart

Chart with timescale axis

Timescale charts and times You can't create timescale charts from data that is measured at intervals of hours, minutes, or seconds. Only days, weeks, months, quarters, and years are considered dates in timescale charts.

Chart types that can use a timescale axis Timescale axes are available on stock charts and on 2-D and 3-D line, column, bar, and area charts, except when these charts have multiple-level category labels or in charts that use fields.

Note You cannot have a timescale axis if the dates in your chart appear in the legend. You can change the way data is plotted in the chart so that the dates appear on the category axis instead.


About trendlines

Trendlines are used to graphically display trends in data and to analyze problems of prediction. Such analysis is also called regression analysis. For example, the following chart uses a simple linear trendline across four quarters to clearly show a trend toward rising revenue.

Chart with linear trendline

Chart types that support trendlines You can add trendlines to data series in column, bar, line, xy (scatter), bubble , area, stock, and polar charts. If you change a chart to a type that does not support the associated trendline, you lose the trendlines.

Show Using the R-squared value to determine the best trendline

When you want to add a trendline to a chart, you can choose any of the five different trend/regression types. The type of data you have determines the type of trendline you should use.

Trendline reliability and the R-squared value A trendline is most reliable when its R-squared value is at or near 1. The R-squared value, also known as the coefficient of determination, is an indicator that ranges in value from 0 to 1 and reveals how closely the estimated values for the trendline correspond to your actual data. When you fit a trendline to your data, its R-squared value is automatically calculated. You can display this value on your chart.

Show Linear trendlines

A linear trendline is a best-fit straight line that is used with simple linear data sets. Your data is linear if the pattern in its data points resembles a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate.

In the following example, a linear trendline clearly shows that refrigerator sales have consistently risen over a 13-year period. Notice that the R-squared value is 0.9036, which is a good fit of the line to the data.

Show Logarithmic trendlines

A logarithmic trendline is a best-fit curved line that is most useful when the rate of change in the data increases or decreases quickly and then levels out. A logarithmic trendline can use negative and positive values.

The following example uses a logarithmic trendline to illustrate predicted population growth of animals in a fixed-space area, where population leveled out as space for the animals decreased. Note that the R-squared value is 0.9407, which is a relatively good fit of the line to the data.

Show Polynomial trendlines

A polynomial trendline is a curved line that is used when data fluctuates. It is useful, for example, for analyzing gains and losses over a large data set. The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. An Order 2 polynomial trendline generally has only one hill or valley. Order 3 generally has one or two hills or valleys. Order 4 generally has up to three.

The following example shows an Order 2 polynomial trendline (one hill) to illustrate the relationship between speed and gasoline consumption. Notice that the R-squared value is 0.9474, which is a good fit of the line to the data.

Show Power trendlines

A power trendline is a curved line that is best used with data sets that compare measurements that increase at a specific rate— for example, the acceleration of a race car at one-second intervals. Create a power trendline when your data contains values greater than 0 (zero). If you create a power trendline by using data that contains 0 or negative values, you will see the words "Trendline Error" in the equation label on the chart.

In the following example, acceleration data is shown by plotting distance in meters by seconds. The power trendline clearly demonstrates the increasing acceleration. Note that the R-squared value is 0.9923, which is a nearly perfect fit of the line to the data.

Show Exponential trendlines

An exponential trendline is a curved line that is most useful when data values rise or fall at increasingly higher rates. Create an exponential trendline when your data contains values greater than 0 (zero). If you create an exponential trendline by using data that contains 0 or negative values, you will see the words "Trendline Error" in the equation label on the chart.

In the following example, an exponential trendline is used to illustrate the decreasing amount of carbon 14 in an object as it ages. Note that the R-squared value is 1, which means the line fits the data perfectly.

Show Moving average

A moving average trendline smoothes out fluctuations in data to show a pattern or trend more clearly. A moving average uses a specific number of data points (set by the Period option), averages them, and uses the average value as a point in the line. If Period is set to 2, for example, the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, and so on.

In the following example, a moving average trendline shows a pattern in number of homes sold over a 26-week period.

Moving average trendline


Equations for calculating trendlines

Show Linear

Calculates the least squares fit for a line represented by the following equation:

where m is the slope and b is the intercept.

Show Polynomial

Calculates the least squares fit through points by using the following equation:

where b and Variable are constants.

Show Logarithmic

Calculates the least squares fit through points by using the following equation:

where c and b are constants, and ln is the natural logarithm function.

Show Exponential

Calculates the least squares fit through points by using the following equation:

where c and b are constants, and e is the base of the natural logarithm.

Show Power

Calculates the least squares fit through points by using the following equation:

where c and b are constants.

Show Moving average

Note The number of points in a moving average trendline equals the total number of points in the series less the number you specify for the period.

Show R-squared value

The R-squared value, also known as the coefficient of determination, is an indicator that ranges in value from 0 to 1 and reveals how closely the estimated values for the trendline correspond to your actual data. A trendline is most reliable when its R-squared value is at or near 1. When you fit a trendline to your data, the Microsoft Office Chart Component automatically calculates the R-squared value for the trendline by using the following equation:

Note The R-squared value you can display with a trendline is not an adjusted R-squared value. For logarithmic, power, and exponential trendlines, the Microsoft Office Chart Component uses a transformed regression model.


Add a trendline to a data series

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the data series to which you want to add a trendline.
  3. On the chart toolbar, click Commands and Options Button image, and then click the General tab.
  4. Under Add, click Add trendline Button image.

Note A linear trendline, its equation, and its R-squared value are placed on the series. Other trendline types include logarithmic, polynomial, power, exponential, and moving average.


Change a trendline

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the trendline you want to change.
  3. On the chart toolbar, click Commands and Options Button image, and then click the Trendline tab.
  4. Do one or more of the following:
    • To change the trendline type, click the arrow next to the Type box and select a type in the list.

    • To change the order of a polynomial trendline, click the arrow next to the Order box and select from the list the number that you want to be the highest power for the independent variable.

    • To change the trendline name, which appears in the legend, click the Custom Name check box under Name, and then type a new name in the box to the right.

    • To show or hide the equation you use to calculate the trendline, select or clear the Display equation check box.

    • To show or hide the R-squared value, select or clear the Display R-squared value check box.


Delete a trendline

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the trendline you want to delete.
  3. Press DELETE.


Show or hide a trendline equation or R-squared value

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the trendline for which you want to show or hide the trendline equation or R-squared value.
  3. On the chart toolbar, click Commands and Options Button image, and then click the Trendline tab.
  4. Under Equation, select or clear the Display equation check box or the Display R-squared value check box or both.


About error bars

Error bars graphically express potential error amounts relative to each data marker in a data series. For example, you could show 5 percent positive and negative potential error amounts in the results of a scientific experiment:

Chart with error bars

Chart types that support error bars You can add error bars to data series in column, bar, line, xy (scatter), bubble, area, radar, stock, and polar charts. For xy (scatter), polar, and bubble charts, you can display error bars for the x-values or the y-values. If you change a chart to a type that does not support the associated error bars, you lose the error bars.


Equations for calculating the error amount in error bars

Show Standard deviation

where:

s = series number

i = point number in series s

m = number of series for point y in chart

n = number of points in each series

Variable = data value of series s and the ith point

Variable = total number of data values in all series

M = arithmetic mean

Show Standard error

where:

s = series number

i = point number in series s

m = number of series for point y in chart

n = number of points in each series

yis = data value of series s and the ith point

ny = total number of data values in all series


Add error bars to a data series

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the data series to which you want to add error bars.
  3. On the chart toolbar, click Commands and Options Button image, and then click the General tab.
  4. Under Add, click Add Errorbar Button image.


Change error bars

When you select an error bar, any changes you make affect all the error bars in its associated data series.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click any error bar on your chart.
  3. On the chart toolbar, click Commands and Options Button image, and then click the Error Bars tab.
  4. Do one or more of the following:
    • To show or hide end caps on the error bars, click the arrow next to Display Button image, and then click the type you want.

    • To select a different type of error bar (positive or negative or both), click the arrow next to Marker Button image, and then click the type you want.

    • To change the method used to determine the error amount, click the method you want in the Error calculation list, and then type the number you want in the Error box or the Percentage box.

    • To change the direction of the error bars so that they calculate based on x-values or y-values for xy (scatter) charts, polar charts, and bubble charts, click X or Y in the Direction list.


Delete error bars

You cannot delete an individual error bar. When you delete one error bar, all error bars for the entire data series are deleted.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click any error bar in the series from which you want to delete error bars.
  3. Press DELETE.


About filtering data in a chart

You can use the filter feature to find specific data values or all data that matches a value. You can filter when designing the chart, and users can filter when viewing the chart in the browser, unless you restrict access to filtering in the browser.

Show Filtering techniques

Filtering a field (autofiltering) You can filter fields in the category, series, and filter areas. When you filter a field, you select one or more items of data in the field that you want to view, and hide the other items.

Filtering a field is particularly helpful when you have a large amount of source data but you want to focus on specific areas. When you filter a field, you can display the data for a single item, or you can select some items to display and other items to hide. You can filter on more than one field at a time to further narrow the focus.

Chart with category field

Chart with category field filtered

Callout 1 Category field Region showing all items (not filtered)

Callout 2 Category field Region filtered to show South and West region items

Conditional filtering You can apply a filter to series or points to show the top or bottom n items based on a total. For example, you can filter for the three cities that generated the most sales or the five products that are least profitable. Instead of n items, you can also choose to filter for a certain percentage of items. For example, you can filter for the top 25% or bottom 25% of salespeople based on the number of orders handled. If there are 40 salespeople, after the filter is applied, you will see data for 10 people.

Show Combining multiple filters

Filters are additive. Each filter you apply is added to the ones you already have in effect.

Show Removing and reapplying a filter

You can display all data for all fields by turning off autofiltering. However, the filter settings are retained, so that when you turn autofiltering back on, the same data that was previously displayed or hidden is again displayed or hidden.

Filter settings are retained when you move fields to change the layout. Also, if you remove a field and later add the field back to the layout, the same items are again hidden.

If you don't want to retain your filter selections, make sure the AutoFilter button Button image is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.

Show Identifying the filters that are currently in effect

When items in a field are hidden by autofiltering, the drop-down arrow Field arrow in the field label is blue. Click the blue arrow Blue arrow to see the filter that is in effect.

Show Restricting filtering in the browser

If you don't want users to change how you've filtered the data, you can make filtering unavailable to users at run time.


Filter a field in a chart

Show Filter a field in the category, series, or filter area (autofiltering)

If the filter area does not have a field, you can either add a new field or move a field from another area to the filter area.
  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the arrow Field arrow for the field that contains the items of data that you want to filter.
  3. Select the check boxes for the items you want to display, and clear the check boxes for the items you want to hide.

Notes

  • To display or hide all of the data for a field, select or clear the (All) check box. A shaded check box indicates that some items are hidden.
  • When placed in the category, series, or filter area, a field always supports multiple selection. A field in the filter area can support either single or multiple selection.

Show Filter a series or point for the top or bottom n values, or for a percentage (conditional filtering)

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Select the field, series, or point that has the values you want to filter.
  3. On the toolbar, click Commands and Options Field arrow, and then click the Filter and Group tab.
  4. In the Display the box, click Top or Bottom.
  5. Set Items to a number in the list, or type the value you want.
  6. To show n number of items, clear the % check box. To show n% of items, select the % check box.
  7. Set Based on to a value in the list.


Remove filters to display all data in a chart

When items in a field are hidden by filtering a field, the arrow Field arrow in the field label is blue.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Do one of the following:

    Show Remove all filters

    • Click the AutoFilter button Button image on the toolbar so that it is not selected.

    Note If you click AutoFilter Button image again to turn filtering back on, the previously set filters will be reapplied.

    Show Remove an autofilter from a field

    1. Click the blue arrow Blue arrow of the field from which you want to remove the filter.
    2. Select the (All) check box and click OK.

    Show Remove a conditional filter

    1. Select the series or point from which you want to remove conditional filtering.
    2. Click Show Top/Bottom Items Button image on the toolbar, and then click Show All.

Notes

  • If you don't want to retain your filters, make sure the AutoFilter button is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.
  • You can prevent users from removing filters in the browser by clearing the Allow pivot filtering check box on the Show/Hide tab of the Commands and Options dialog box.


Allow multiple or single selection for a field in the filter area

You can specify whether a field will allow single or multiple selection when in the filter area. In single-selection mode, selecting an item automatically clears the previous selection. In multiple-selection mode, when you select an item, previously selected items remain selected.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Select the field for which you want to change the setting. The field should not yet be in the filter area, but in one of the other areas in the chart, such as a series or category area.
  3. On the toolbar, click Commands and Options Button image, and then click the Filter and Group tab.
  4. Select or clear the Allow selecting multiple items when in filter area check box.

    When the field is moved to the filter area, it will reflect the new setting.

Notes

  • By default, multiple selection is enabled.
  • This setting applies only when the field is in the filter area. If you move the field to a category or series area, the field will always support multiple selection.


Expand or collapse items in fields

You can expand or collapse items in series fields or category fields to show or hide more detailed information for a particular item. Expanding is similar to drilling except items on the same level are not hidden. In the example, West is expanded to show detailed information for West, but other regions are still displayed without detailed information. Collapsing will remove the detailed information for that item, and return the chart to the state it was prior to expanding.

Charts with expanded and collapsed fields

Callout 1 Chart that shows sales in each region.

Callout 2 Chart with West expanded to show sales per product in the West region.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the field that contains the item or items you want to expand or collapse.
  3. On the toolbar, click Expand Button image or Collapse Button image.


Drill into or out of data in fields

You can drill into an item in an outer category field to focus on detailed information for that particular item, or you can drill out to remove detailed information for that item. Drilling is similar to expanding, except that when you drill into an item, other items on the same level are hidden.

In the example, the region North is drilled into, showing detail information for the North region and hiding all information for other regions. When you drill out of an item you drilled into, the detailed information is removed, and items on the same level are shown again.

Charts with drilled information

Callout 1 Chart that shows sales information for products by region.

Callout 2 Chart that shows North region item drilled into to show sales information for each type of product in the North region only. Other regions are hidden.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Click the item on the category axis that you want to drill into or drill out of.
  3. On the toolbar, click Drill into Button image or Drill out Button image.


Sort data in a chart

You can sort category and series data in a chart that is based on a PivotTable list, database table, or query.

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Do one of the following:
    • If you want to sort numerical data in a series, click the series or a series point within the series.
    • If you want to sort items in a field alphabetically, click the category or series field, or click a category axis label or legend entry.
  3. Do one of the following:
    • Click Sort Ascending Button image or Sort Descending Button image on the toolbar.
    • If you have a stacked bar, stacked area, or stacked column chart, and you want to sort numerically based on the height of the entire stacked value rather than just one series, right-click the series or point, point to Sort, and then click Ascending By Total or Descending By Total.


Group or ungroup data in a chart

  1. In the design window, make sure the chart is activated. For instructions, see Help for your design program.
  2. Select the category or series field whose items you want to group or ungroup.
  3. On the toolbar, click Commands and Options Button image, and then click the Filter and Group tab.
  4. Do one of the following:

    Show Group data

    1. Under Grouping, set Group items by to a value other than (No grouping).
    2. Set Interval to the value you want. The type of interval you can specify for a field depends on the data type of the field.
      • Numeric fields You can group in intervals of any integer, such as 1, 2, 5, 100, and so on. For example, you can group the EmployeeID field into groups of 1–100, 101–200, and so on.
      • Date and time fields You can group in intervals such as days, weeks, and quarters. For example, you can group the values in the ShippedDate field into months to show data for orders shipped in January, orders shipped in February, and so on.
      • Text fields You can group based on the first n characters of the individual items. For example, you can group the LastName field by first letter to create groups such as A, B, and so on.
    3. To specify the value at which grouping begins or ends for numeric intervals, select either or both of the Start at or End at check boxes, and then enter a value in the adjacent text box.

    Show Ungroup data

    • Set Group items by to (No grouping).


Summary functions available for chart data fields

The following table lists the summary functions provided by the AutoCalc Button image command used to summarize data fields.

Function | Description
Sum | Calculates the sum of the values.
Count | Tallies the number of values. Empty values, such as zero-length strings (strings that contain no characters), are included in the count, but null values (values that indicate missing or unknown data) are not included.
Min | Returns the smallest value in the field being summarized.
Max | Returns the largest value in the field being summarized.
Average | Returns the average of the values in the field being summarized.
Standard Deviation | Returns the standard deviation of the values in the field being summarized, assuming the values are a sample of a larger population.
Standard Deviation Population | Returns the standard deviation of the values in the field being summarized, assuming the values are the entire population.
Variance | Returns the variance of the values in the field being summarized, assuming the values are a sample of a larger population.
Variance Population | Returns the variance of the values in the field being summarized, assuming the values are the entire population.

Note The summary functions available for a field depend on the type of data in the field. For example, if a field contains text, you can use only the Count function for that field.


Troubleshoot charts

Show I can't find the command or option I want.

If you cannot locate the command or option you want in the Commands and Options dialog box, it could be because you don't have the necessary item selected in the chart. To make sure you have the correct item selected, click the General tab in the Commands and Options dialog box, and then in the Select box, click the chart item you want to work with. Options for that chart item will then appear in the Commands and Options dialog box.

Show An exclamation point appears in the bottom left corner of my chart.

A passive alert indicates that your chart contains an error. The alert appears as an exclamation point at the bottom left corner of the chart. Passive alerts appear in error situations such as when a texture file cannot be found or when a database connection fails. Click the exclamation icon to see more information about the error.

Show I can't move or resize chart items with the mouse.

Chart items (such as the legend, plot area, data labels, and titles) can't be moved or resized with the mouse. However, chart items such as the legend or some axes can be moved by using the Position or Placement settings on the appropriate tabs in the Commands and Options dialog box. Other settings in the Commands and Options dialog box allow you to rotate or flip the plot area of a chart, pull out slices in pie or doughnut charts, resize the font used in labels, titles, and the legend, and resize the data markers in some types of charts. Other chart items are resized automatically when the size of the chart changes.

Show I can't add text to a chart.

You can add titles to a chart and to the axes in a chart. You can also add data labels to data markers. You can't add free-floating text to the chart. If necessary, add text to the Web page that contains the chart.

Show Text is missing along the horizontal (x) axis of the chart.

There might not be enough room in the chart to display all of the axis labels. If some of the category names aren't visible along the horizontal axis of the chart, try the following:

  • Enlarge the chart. See Help in your design program to learn how to select and resize the chart control.
  • Use a smaller font size for text on the horizontal (x) axis.
  • Shorten the category names in the source data.

Show My xy (scatter) chart does not use the right values along the horizontal (x) axis.

If your category (x) axis displays numbers such as 1, 2, 3, 4 instead of the values you want, you might have created a line chart instead of an xy (scatter) chart. With the chart active in the design program, click the chart, click Chart Type Button image on the toolbar, click XY (Scatter) in the list on the Type tab, and then click the sub-type you want to use from the display on the right. If you want lines to connect the data markers, click one of the subtypes that has lines.

Show I published a chart from Excel, and it has more data in it now than it did in Excel.

In Microsoft Excel, you can specify that a chart plot visible cells only. When this setting is enabled, the chart does not reflect data in hidden rows or columns within the data range that you use to create the chart. When you publish a chart, however, all data is reflected in the chart.

If you do not want the chart to reflect hidden data, you should unhide the rows and columns in the worksheet and move the data out of the data range you are using to create the chart before you publish the chart. See Excel Help for information about moving data in a worksheet. To change your chart after publishing, you can unhide and delete the rows or columns that have the data you do not want shown in the spreadsheet that is published with the chart. For information about deleting rows or columns, see Spreadsheet Component Help.

Show I published a chart from Excel, but it contains no data when I view it in a design program or in the browser.

To publish a chart to the Web from Microsoft Excel, you must include both x and y values in an xy (scatter) chart, and you must include x, y, and bubble size values in a bubble chart. Excel supplies default values if x values are not included in your data selection. These values appear in the chart in Excel, but they are not retained when you publish the chart as a Web page.

If you didn't include all necessary values in your chart, type them in Excel into a column that's adjacent to the chart's original data, re-create the chart, and then republish it.

Show When I attempt to display or refresh a chart on Microsoft Internet Explorer, I get prompted with the message “This page is accessing information that is not under its control. This poses a security risk. Do you want to continue?”

You have specified a data source for the chart on a different domain on the Web. If you do not want to be prompted, you can change the way you are accessing data across domains. For more information, see About accessing a data source on a different domain.

Show I have a blank box on the Web page where my chart should be.

In a browser You can only view and interact with charts for the Web in Microsoft Internet Explorer 5.01 SP2 and later.

In a design program You can view and modify published charts in Microsoft Office FrontPage 2003, Microsoft Office Access 2003, and Microsoft Visual Basic. Your chart might be represented by a blank box in other design programs.

Show Users can't display or interact with my chart in the browser.

Install the Microsoft Office Web Components To view and work with charts for the Web, users must have the Office Web Components installed. They can do this by installing Microsoft Office 2003 or, if their company has a Microsoft Office 2003 site license, they can download the components from an intranet.

Check the browser version To interact with a chart, users also need Microsoft Internet Explorer version 5.01 SP2 or later. It's recommended that users have Internet Explorer 5.01 SP2 or later to take full advantage of the interactive capabilities of the chart and its source data. To learn more about the level of interactivity with the chart's source data in different browser versions, see the Help for the source data.

Show My chart for the Web does not update in the browser.

If you published a chart from Microsoft Excel, you might not have chosen to publish the chart with interactive functionality. If you publish without interactive functionality, the chart displayed in the browser is static and is not connected to data.

To publish an interactive chart, open the original workbook in Excel, select the chart, and then click Save as Web Page on the File menu. In the Save As dialog box, click Selection: Chart and select the Add interactivity check box.

Show Clicking the Edit button in my Web browser opens my Web page in the wrong program.

Which programs are available when you click the Edit button in the Web browser depends on how the page was published.

If clicking the Edit button in your Web browser opens a program that you do not want to use, close the program, start the program you want to use, and then open the Web page from there.

Show My 3-dimensional charts are displayed as 2-dimensional charts.

If you are using a version of Microsoft Windows prior to Windows XP, you may not have Microsoft DirectX installed. Install DirectX from Microsoft.com Download Center to display 3-dimensional charts.