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 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
        • 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 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
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • 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 Create Pivot Tables in Microsoft Excel 2013 – Part 2

How to Create Pivot Tables in Microsoft Excel 2013 – Part 2

Excel 2013 contains numerous options and new features for the Pivot Table functionality. Pivot Tables can be created through the Create Pivot Table Dialogue, and the existing tables from which a Pivot Table emerges are created, likewise, through the Create Table Dialogue. Users build up Pivot Tables by selecting data fields and dropping them into the Fields Panel’s Drop Zone. Excel 2013 includes a Group Field option that enables the grouping of dates into weekly or monthly groups. Arranging and formatting Pivot Tables is relatively straightforward in Excel, utilizing simple drag and drop functionality.

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


Facebook Linkedin Twitter

Do you need to learn Microsoft Excel 2013? Get 19 hours of Microsoft Excel 2013 training – click here.

Video transcripts:

Hello again and welcome back to our course on Excel 2013 Advanced. In the previous section we created our first pivot table and in this section we’re going to expand on the options when you’re creating pivot tables and we’ll be looking at some of the new features in Excel 2013 that can let you get to really good pivot tables very quickly.

So in the previous section we very quickly created a pivot table. Generally speaking when you’re creating pivot tables it’s usually best to create a pivot table from a table rather than a range of data. And the main reason for this is that if you subsequently update the data and as the data you’re dealing with is probably transactional it’s entirely possible that you will want to add to that data later on. You’re actually better off defining a table containing the current data, building the pivot table on the basis of that, and then when you do subsequently add data the amount of work you need to do to update the pivot table is greatly reduced. So let’s start by creating a table from the data that we’ve got here.

 

Learn microsoft Excel 2013

 

So let me just click within the table of data and then insert table and as you should know by now we come up with the Create Table Dialogue. Excel 2013 suggests the range for the table. There are in fact 34,109 rows of data in this data set and as you can tell there it’s detected that the table has headers. What I’m going to do is I’m not going to include all 34,109 rows of data. What I’m going to do is just include the first 20,000 rows and then what we’re going to do is to extend the table later on just to show how easy it is to update the pivot table when we get new, additional data. So let’s go on that basis now. Click on OK. The table is selected and I’m going to call that table Store Sales. So there we are.

Now I’m inside my table of data and I’m going to go to the Insert Tab and I’m going to insert a pivot table pretty much from scratch. So click on insert pivot table and what I get is a Create Pivot Table Dialogue. Now first of all I’m required to select a table or range. If I’ve got an existing table, which of course I have, Store Sales, that’s the one that it defaults to. I’ve all ready got the cursor inside there. That’s why Store Sales comes up. If I wanted to select a different range of course I could. And as you can see from the Option buttons there, there is an alternative option which is Use an external data source. And an external data source could include a connection. We setup connections earlier in the course and of course you can create a pivot table from one of those connections you’ve setup earlier on. For example, it’s very straightforward to create a pivot table using a connection to an Access data source. For the moment let’s stick with this Store Sales table.

Then I have options of either put it on a new worksheet or use an existing worksheet. Now I’m going to put this on a new worksheet. Sometimes there are advantages in putting pivot tables, charts, etcetera on the same worksheet but once you’ve got the pivot table itself I find that having the raw data right in front of you can be a little bit distracting and you actually get more space to work with if you put it on a new worksheet anyway. So I’m going to stick with the new worksheet option, click on OK, and we have basically the bare bones of a pivot table.

Now there’s a couple of things to notice here straightaway. One of them is you may want to rename the sheet here with the pivot table on it, which I’m going to do right now. And also note that what you can see here marked as pivot table 3 is actually a placeholder for a pivot table that hasn’t been built yet. And it won’t be built until we select one or two of the fields here from the pivot table fields panel on the right. Incidentally although that panel seems to be firmly fixed on the right there you can actually move the panel around. You can grab it by its heading and move it around and sometimes when you’re dealing with a pivot table and maybe it needs to be pretty big, you might find it convenient to just pull that away from its right hand side location there and move it to a more convenient location. To pull it back again push it over to the right and then it snaps back into place.

Now as you’ll have realized from the previous section we actually build the pivot table by selecting fields from our data and moving them into these drop zones at the bottom of the pivot table fields panel on the right. Some things happen by default. If for example you select a date type field it will automatically be added to the rows drop zone. So let me just select date there. It automatically goes into that zone. Similarly if I check a numeric field like value here that’s automatically added to the values drop zone and becomes a sum of values. The assumption being that in doing this pivot table analysis you’re looking at totals from a number of transactions. And as you can see with the selection I’ve made there basically my pivot table has got a first column which shows dates, so individual dates starting 24th April 2012 and going forward from there. And then the second column is the sum of values, i.e., the sum of sales for each of those days summed up over all of the branches and all of the departments. And how I would further build my analysis of the store sales is to introduce branches and departments. So let’s see how that works with a specific example.

Let’s suppose I want to include branch in my pivot table. If I select branch, which is basically a text field, what will happen is branch automatically gets added to the rows drop zone. And note because of the sequence there I’ve got date first and then branch. So on the 24th April 2012 broken down by the four branches for which I have sales on that date, they are the sums of values of the sales; similarly 25th of April, 26th of April, and so on. To change the order of the data fields in the rows column all I need to do is to grab one, branch in this case, drag it up. Watch what happens to the analysis now. Now my analysis begins with branch and then I have all the days for the Boston branch and then right down to the end of the period in question then I start the Chicago branch. So the sequence of data fields in the rows drop zone determines the sequence in which those data fields appear in the pivot table on the left.

Pivot tables very often involve reporting, including dates. And particularly as we’re often dealing with straightforward transactional data, dates are very often part of that data and it’s usually the case, particularly if you’ve got data over a long period of time that you wouldn’t want to present the data by individual days like this. You’d almost certainly want to group the dates by weeks or months or quarters or years or whatever. So let’s now look at grouping this date data.

On the pivot tables Analyze Tab there is a Group button and if I click on the drop down on the Group button one of the options is Group Field. That shows me the start date for this date field, April 24th and the end date, August 19, 2012. And it allows me to specify grouping. Now at the moment by default it’s suggesting grouping by months but in fact I can group by multiple criteria. I’m not restricted to grouping my month. So let’s suppose I wanted to group by months and quarters. I’ve got them both selected, click on OK, watch what happens. Now what I get is a sort of two level grouping. I’ve got the quarters grouping divided into months and then I can use these Expand and Collapse buttons here to hide the details if I want to.

 

Simon Sez IT MS Access 2013 training course

 

Now so far this pivot table is still pretty straightforward and hopefully you find it very easy to understand what’s going on. Let’s now make a couple of further changes to it. One of the things I’m going to do is to move branch over to columns. And what I’ve got now is a crosstab pivot table where I’ve got dates for the rows, bear in mind I’ve got quarters and dates as two levels of field in the rows, and then the branches are the columns. And of course within the date rows I’ve got the expand facility here where I can expand a particular quarter to show the figures for the individual months.

Now at any point I can introduce additional data fields. So for instance, let’s suppose I now want to put department in. As soon as I check department by default department because it’s a text field will be added as a row in the table. So I’ve got departments here and departments are below date. So if I expand quarters I’ll actually find below the dates that I’ve got a breakdown for each month. Bear in mind I’m grouped by quarters and months. I’ve got a breakdown by departments.

And I’m sure you can work this out by now. If I wanted to promote department to the top of the rows drop zone I’ve now got an arrangement whereby I’ve got individual departments and then within each department I’ve got the relevant quarters in which we’ve recorded sales in that department. And similarly if I wanted to put departments over as a column and branches back as a row and so on. It’s extremely straightforward to change all of those things just by dragging and dropping within the drop zones.

Now for the rest of this section I’d like to look at formatting what’s in a pivot table. Formatting is very important because we very often use pivot tables as presentational tools so the presentation is all important. In the body of this pivot table of course the numbers we’ve got are basically currency amounts. If I click on one of them, note the screen tip that I get tells me, Sum of value. Value 1192.25, row Boston, May. So it’s telling me which row I’m in. Note that I’ve removed the quarters for simplicity, just to illustrate what we’re going to do next. And then the column I’m on is flatbread. Sum of value is the actual field value that I’m looking at here so that should be a currency amount.

If you click on Analyze in the pivot table tools, in the active field group one of the options is field settings and these field settings apply to the field that’s currently selected. So that will be sum of values. Click on field settings and we get a Value Field Settings Dialogue. Now I could at this point customize the name if I wanted to. And I could also change what I’m doing with that sum. At the moment it’s a sum. I could change it to a count or an average or a max or a min or whatever. Now if I click on number format I can actually do some number formatting. And what I really want this to be is a currency field and I’m going to go for two decimal places. So I want these sum of values to be currency fields with two decimal places. Click on OK, click on OK, and now look at what an improvement that makes to my pivot table.

Just a word of warning there. Don’t be tempted to say select some cells here and do format cells in the way that you would conventionally on a worksheet. The way that we’ve just defined that currency format for sum of values using field settings for the active field, make sure that as the values related to this pivot table change, so if we introduce more departments, new branches, change the dates that we have data for, and so on, then that definition will be used throughout. No matter how we change the source data then the sum of values there will always be formatted in that currency way. If you select a specific range of cells and do the formatting then if the size and shape of the pivot tables changes then the formatting you’ve done will no longer correspond to the size and shape of the new pivot table. So make sure you do it as a definition for field settings for the active field in the way that I did just now.

And one other aspect of the presentational side of a pivot table, the form we’re looking at now is called a compact form. If you look at the Boston figures, for example, the total for Boston here, for each of the departments, let’s say coffee, is shown at the top. So 562.15 is the total. There are only sales there in May. Similarly down for Chicago, etcetera. Now it’s not always best to have totals at the top. Some situations the totals at the top works, some it doesn’t. But there are a number of other ways of presenting a table and if you go to the Design Tab and click on Report Layout you can choose from a number of quite useful options. One of them, for example, is Show in tabular form. Now if you select show in tabular form you get those totals at the bottom, so you get Boston and then a Boston total row; Chicago and then a Chicago total row. And then look again at the Boston figures here. We’ve got Boston, April, May, June, July, August, etcetera. If you wanted to have Boston in each of these cells here, which might be particularly useful if you’ve got many months say, you’ve got many categories, many values, and things move out of view when you can’t see headings and so on one of the other options on the Report Layout button here is to for Repeat all item labels. And what happens in a situation like this is in this case the branch name get repeated. That can be very useful, as I say, if things would otherwise move out of view.

Now there are other settings there on the Report Layout button that I think it’s good for you to experiment with but that’s it for now on the presentational side of pivot tables. In the next section we’re going to look at filtering in pivot tables and in particular we’re going to look at slices, so please join me for that.

 

advanced Microsoft Excel 2013 training course by Simon Sez IT

 

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 Create Pivot Tables in Microsoft Excel 2013 – Part 2 ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

How to Setup Appointments and Meetings in Microsoft Outlook 2013

How to Receive Payments in QuickBooks 2019

The Basics of Formulas and Functions in Excel 2010

Create Purchase Orders in QuickBooks 2019

Basics of Formulas and Functions in Microsoft Excel 2016

What-If Analysis in Excel – Recorded Excel Live Class

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)