ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Creating, Configuring and Using a Pivot Chart in Excel 2007 and Excel 2010

Updated on July 13, 2013

Why you should use pivot charts instead of normal charts in Excel 2007 and Excel 2010

Welcome to my hub on pivot charts. The Pivot chart allows you to create graphs or charts that are both versatile and extremely flexible.

  • They are perfect for producing charts from dynamic data that changes frequently (such as daily sales totals) as well as
  • For large data sets where you will be required to produce large numbers of chart or graphs quickly
  • It is straightforward and easy to change an existing pivot chart to display different data

This flexibility and versatility of pivot charts will save you time you would otherwise spend recreating charts from scratch!

Example of a pivot chart showing the associated pivot table in Excel 2007 and Excel 2010.
Example of a pivot chart showing the associated pivot table in Excel 2007 and Excel 2010. | Source

Pivot charts use a pivot table as the source of the data to be included in the chart. This is in turn linked to the original data source. So every time the original data is updated the chart is also automatically updated. You can use pivot tables to also perform statistical analysis on the data in the table using functions such as AVERAGE, SUM, MIN, and MAX etc). I have a hub that introduces the pivot table and covers creating and configuring them as well as how to use filters and sorting with them.

http://robbiecwilson.hubpages.com/hub/Creating-Pivot-Tables-in-Excel-2007

In today’s hub we will investigate how to:

  • Create a pivot chart
  • Refresh the data displayed in a pivot chart
  • Changing the data displayed in your Pivot Chart

Creating a Pivot Chart in Excel 2007 and Excel 2010

When you create a pivot chart, Excel will automatically create a pivot table unless you opt to create a pivot chart from an existing one.

The process of creating the chart is identical in either case. We will create one, which will illustrate the top five salesmen (in terms of Sales), for January from raw data to show the entire process in case you are not familiar with pivot tables.

  • The first step is to click anywhere in the data range you plan to chart
  • Next, click on the PivotTable button and select Pivot Chart. The button can be found on the Insert tab within the Tables group
  • Excel will automatically expand the selection to the entire data range. If this is not what you want, adjust the Table/Range in the Select a table or range section
  • Now choose whether you want to place the chart in a New Worksheet, or select a Location in the Existing Worksheet

Defining the range and the position of the new Pivot Chart in Excel 2007 and Excel 2010.
Defining the range and the position of the new Pivot Chart in Excel 2007 and Excel 2010. | Source

The PivotChart Filter Pane will now open.

The PivotChart Filter Pane in Excel 2007 and Excel 2010.
The PivotChart Filter Pane in Excel 2007 and Excel 2010. | Source
  • You can safely close it (if you leave it open, it simply displays the selections you make in the pivot table) and you will see the PivotTable Field list open.
  • We are interested in Salesmen and Sales, so we tick both of those
  • To narrow this down to the top five salesmen, we now click on Salesmen and select the drop down arrow

PivotTable Field showing the drop down box used for sorting and filtering in a pivot table in Excel 2007 and Excel 2010.
PivotTable Field showing the drop down box used for sorting and filtering in a pivot table in Excel 2007 and Excel 2010. | Source
  • Now select Value Filters and then Top 10 and adjust it to just show the top 5
  • The final thing we need to do is to sort the top five from highest to lowest on the chart. To do this, we go back to the drop down list for Salesmen as we did above, although this time we select More Sort Options
  • Click Descending (Z to A) by and choose Sum of Sales, then click OK

The chart is almost complete. You can also see from the figure below that Excel also creates a pivot table.

Initial pivot chart created by Excel 2007 and Excel 2010.
Initial pivot chart created by Excel 2007 and Excel 2010. | Source

Now all we need to do is to clean up the chart and it is complete. If you would like to learn more about creating and configuring charts and graphs, I have a hub that covers this in great detail which can be found here:

http://robbiecwilson.hubpages.com/hub/Creating-charts-and-graphs-in-Excel-2007

Fully configured pivot chart, created in Excel 2007 and Excel 2010.
Fully configured pivot chart, created in Excel 2007 and Excel 2010. | Source

Changing the data displayed in your Pivot Chart using Excel 2007 and Excel 2010

The real power of the pivot chart lies in how easy it is to change the data that your chart displays. Imagine if my manager calls me and says that she needs a chart showing the bottom five salesmen in terms of expenses (highest equals worst in this case, I can easily do that:

  • First, I select the chart
  • Second, I clear the tick next to Sales and select Expenses
  • Finally, I repeat the filters and sorting I used above to sort them biggest to smallest and filter them by the top five
  • So in no time at all, we have the chart that my manager asked for!

Example of how easy it is to switch the data displayed in a pivot chart in Excel 2007 and Excel 2010.
Example of how easy it is to switch the data displayed in a pivot chart in Excel 2007 and Excel 2010. | Source

Note: The existing formatting within your chart (Chart title as well as any other changes to the appearance of your chart or graph) will remain the same when you change the data displayed. However, any filters you apply to the data (for example, displaying the top five items) will be reset and have to be re-applied when you change the data you wish to display.

Refreshing the data in your Pivot Chart in Excel 2007 and Excel 2010

Along with being able to change the data displayed in your pivot chart quickly and easily, the second major advantage of pivot charts is how easily they work with dynamic or frequently changing data.

In our example, we have been working with monthly sales data from our team of salesmen. Every day, they enter in their sales for the day and every day we create a new chart. When using normal charts, you would have to recreate it. However, with a pivot chart, you just have to refresh the chart. You can see below that the sales data for the 13th is now in.

Updated data for the 13th of January about to be included in our updated pivot chart in Excel 2007 and Excel 2010.
Updated data for the 13th of January about to be included in our updated pivot chart in Excel 2007 and Excel 2010. | Source

To refresh the data in your pivot chart:

  • Right click anywhere on your pivot table or pivot chart and select Refresh Data

This pulls the latest data from your data source for your pivot table and refreshes both the pivot table and the pivot chart automatically.

Conclusion

Pivot charts are as flexible and versatile as pivot tables. They allow you to:

  • Easily create multiple charts simply and quickly from a large data source.
  • They work particularly well with dynamic data as we saw in the example above
  • You can also change what the chart displays very easily and quickly utilising the same formatting as the previous chart

I do hope you enjoyed reading my introduction to pivot charts as much as I enjoyed writing it.

I also have a number of other hubs on aspects of Excel, covering everything from Conditional Formatting to sharing and protecting documents. These can be accessed via my index hub on Excel below.

The hub also covers how I successfully transitioned from the traditional menus in Excel 2003 to the ribbon that is used in Excel 2007 and Excel 2010 as well as outlining my other Excel 2007 / 2010 hubs which can be found here

http://robbiecwilson.hubpages.com/hub/How-to-adjust-to-Excel-2007-from-previous-versions-as-well-as-step-by-step-guides-to-many-functions-in-Excel-2007

Many thanks for reading, please feel free to leave any comments you may have below.

working

This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://corp.maven.io/privacy-policy

Show Details
Necessary
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
Features
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Marketing
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Statistics
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
ClickscoThis is a data management platform studying reader behavior (Privacy Policy)