Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > How to Use Excel 2010 Pivot Tables and Charts – Part 2

How to Use Excel 2010 Pivot Tables and Charts – Part 2

Microsoft Excel 2010 contains numerous commands and options which are useful for customizing and analyzing Pivot Charts and Pivot Tables. The contextual Design, Layout, Format and Analyze tabs allow users to select chart types and styles as well as add data labels, while the Field List allows users to categorize fields and filtered sub-groups according to specific field variables.

Excel 2010 features Slice functionality, allowing users to access multiple customizable subsets of data, as well as a Sigma drop zone, which allows users to employ analytical equations. The Column Labels drop zone is useful for creating graphical chart structures.

Watch the free video here, transcripts for the entire video follow:


Facebook Linkedin Twitter

Do you need to learn Microsoft Excel? Get 35 hours of Microsoft Excel training – click here

Video transcripts:

Hello and welcome back. In this section, we’re going to continue looking at out straightforward example of sales in a convenient store or a number of convenient stores and what we’re going to do first is just to look at formatting the Pivot Chart that we created before. First thing I’m going to do though is to change the data selection. I’ve actually now got a number of places I can that. I can do it here; I can click on department on the chart and filter from there. So if I remove Select All and just select breads, flat bread, oatmeal, and snacks, click on OK, and as you saw before, everything is updated. The selection shown in the Pivot Table and the Chart are all selected. The Sum of Value I have here is still a percentage of total sales, but of course, this is the percentage of total sales for the selected departments. So, just over 50% of the total sales in these four departments is down to breads.

 

Excel 2013 training

 

Now, let’s once again take a look at formatting the Pivot Chart itself. We’ve seen ways of selecting data for it and if you want to actually format it, we may want to use it in a report; you may want to move it within your workbook. It’s very often the case that it’s a good idea to get the Field List out of the way while you work on it. I mentioned before that usually if you click within Table or Chart, the Field List will normally reappear. But actually if you’ve deliberately closed it, you need to open it quite deliberately as well. There are two main ways of doing that. If you currently have the Chart selected, then on the Analyze tab there is a button that says Field List, click that and the Field List is shown. And close it again. If you’re within the Pivot Table, then you have a Pivot Table Tools Group with Options and Design in it. If you click in Options, over on the right there’s a Field List button there that shows the Field List for you. So, I’m going to close this again and now let’s start working on this chart.

With the chart selected we get the group of tabs up here – Design, Layout, Format, Analyzer. Let’s go to the Design tab. I’m going to change the Chart Type. I’m going to change it to a Pie Chart. I’m going to change it to a 3-D Pie Chart; click on OK. It all works exactly the same as it did before and I can even go into my Filter here and say decide, well I don’t really want snacks included, click on Ok and everything is updated.

Now there are certain types of chart you can’t use as Pivot Charts, including Scatter Charts and Bubble Charts. But for the purposes of analysis they’re not the sort of charts we’d be using anyway, so that’s not really a restriction.

So, let’s change the Title here. We’re going to change it to “Sales by Product,” okay. And we’re going to remove the Legend.

And finally, we’ll add some Data Labels. So, let’s go for More Data Label Options. We’re going to put the Category name and the Value and let’s not have leader lines. Click on Close. There we are. So flatbread and oatmeal are fine, let’s move breads just over to here and there we are, “Sales by Product.” That’s pretty good.

Now, of course, we can still use usual facilities to increase the size of the chart, for instance. Excel may move some of the Labels to less convenient places, but that’s fine. And we can, back in Design, we can choose from available Chart Styles. So we could go for something like this. And the one thing you may have noticed, which is different, is this, Sum of Value up here. Now this is one of the buttons that the Pivot Chart and Table mechanism in Excel 2010 puts on the charts. If you go to the Analyze tab there is a button here, Field Buttons and if you click the top of that to switch off the Field Buttons, then any of those buttons will disappear. You can, of course, put them back on again if you need to.

So, now we are going to return to the original Field List and we’re now going to go to the next stage with this Pivot Table because we’re going to introduce one of the other variables. We’re going to introduce Branch. So, in the Field List click on Branch. Excel 2010 works out that Branch should be one of the Axis fields. It’s not going to try to do any kind of calculation on it as it did with values. And what you can see has happened here is that for our selected group of departments, so starting with breads, flatbread, oatmeal, snacks; it has then Categorized or Sub-categorized by Branch of Store those percentages. So, for instance, for breads, of the 56.63% of our total that is contributable to breads, this is now broken down by our five stores.

 

Microsoft Office 2013 training online

 

Now at this point you can probably start to see the power of Pivot Tables and let’s now do a few things to show how easy it is to change this particular slice of the data that we’re looking at. Let’s go down, first of all, to the sigma drop zone down here. We selected earlier on here as our Value Field settings, we changed to Sum shown as a percentage of the grand total. Let’s just return it to Sum as it was before, click on OK, and we’ve now got a total value of sales for each of those. The other thing we can do down here, if you look at the Axis Fields Department and Branch, I can actually click on Branch and drag it above department and watch now what happens. We now have the Branches as our first level and then within that the Departments, the types of food stuffs within the Branches, and obviously everything else is updated in line with this.

So, of course, I can filter on Branch now. So I could Undo Select All and say just look at Boston and Chicago. Again, everything is updated accordingly. Now there’s one other very important thing now and that is that as we look at the chart here, we can see that it affectively we have eight values. That’s four different departments, two different branches, and the structure relating these. Although it’s shown in the way that the axis is marked, doesn’t really reflect the nested nature of this information. It’s as though these are eight separate departments.

Now, of course, there may be situations in which that is exactly the way that we want the data to look. But if we actually wanted a structure whereby it’s got a more two dimensional feel, the clue to what has happened here by Default with Excel 2010 is that both Branch and Department are shown as Row Labels. And in fact you can see they are Row Labels. They are structured, there are as we move these around, departments within branches, but they’re both Row Labels. If we wanted to get a two dimensional structure all we need to do is to take department and drop it in the Column Labels drop zone. What happens then is that we get a completely different look to our data because now the columns are the departments, the food types, and the rows are our two branches that are shown. Both of them are Filtered, so on the Column Labels, that is the department types, we could perhaps put in hot teas and on the Row Labels, that is the branches, we could include another branch, so we could put Denver in there. And, of course, we have this type of Clustered Column Chart representing all of that data and looking pretty similar to the Clustered Column Charts that we looked at before.

The next thing we’re going to do is a very important feature of Pivot Tables and Charts, but it needs to be treated with a little bit of care. We have then our Branches as rows and we have our Departments as columns. What I’m going to do is to remove Departments altogether.

So, to get rid of Department, I can just double click on it to bring up the Menu and I can just say Remove Field and that takes it out of the Legend Fields drop zone. So all I have now are my three branches with the total sales for each. What I’m now going to do though is I’m going to add Date. Now, this is a bit of a dangerous thing to do with a very, very large amount of data because the dates are by day and there are over 30,000 transactions, but Excel 2010 copes with it okay. And if I now drag Branch over to the Legend Fields, that’s the column ones and I’ve now got something a little bit closer to a readable chart. Now the problem is with daily data that’s extremely difficult to follow. So what I really want to do is to report by say month, and that’s what we’re going to do next.

Now, one of the differences when you’re dealing with dates, for example, is that although you have a Filter here, so if I click on the Filter, the Filter covers every day over a long period of time and it still only does whole days. So, I could do as I did with Branch and Department at different times, and select a subset, but what it doesn’t do is to total up by date to larger time denominations like month. The way that I do that is I go to the Options tab within the Pivot Table Tools and there’s a function there, Group Field. And if I click on Group Field, Excel 2010 recognizes it as a Date Field. It gives me my Minimum starting date as a Default ending at as another Default. I can change those if I wanted to, but it also lets me group. And I’m going to group this data by months. My choices are everything from seconds to years, but months will I think will be good. Click on OK and what you now see is much better. I’ve got a by month list of sales for my three selected branches with the total value of sales and I also have the Clustered Column Chart to go with it. So, that’s how we do grouping. Now, dates are very often the types of field we’ll use for groupings and this is a pretty clever feature of Excel 2010’s Pivot Tables and Charts, which can really help in reporting a lot of data over a long period of time.

So, we’ve covered many more of the basic features of Pivot Tables and Charts and we’re going to return to them again in the next section where we’re going to step up again and look at some of the more advanced features, including the use of Filters and Slices. So I’ll see you then.

 

Excel 2013 Pivot table training

 

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

START LEARNING How to Use Excel 2010 Pivot Tables and Charts - Part 2 ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

Navigating the Microsoft Project 2016 Workspace

The Publisher 2013 Interface: Navigating the Ribbon

Get the Most Out of Microsoft Teams (1 Hour Recorded Webinar)

How to Sort a Pivot Table in Excel? 6 Best Methods

Introduction to SharePoint Online – Recorded Webinar

Designing Better Spreadsheets in Excel – Excel Tips & Tricks

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)