New Feature: Playlist Center! Pick a topic and let our playlists guide the way—like a learning mixtape.

Start learning with our library of video tutorials taught by experts. Get started

Excel 2013 Essential Training
Illustration by

Manipulating PivotTable data


From:

Excel 2013 Essential Training

with Dennis Taylor

Video: Manipulating PivotTable data

One of the best features of a Pivot Table is the ease with which you can manipulate the fields. We're looking at a worksheet called Manipulating, it's in the Workbook 13-PivotTables and we're looking at a Pivot Table that was created earlier and we're thinking of changing the layout of the fields as we see them here. The Pivot Table fields list is off to the right. Sometimes this is anchored, you can click and drag its title and place it above the data if you wish or closer to the data and also make it larger this way. Again, as we look at the Pivot Table field list Product, Items, Salesperson, that's the layout we see to the left.
Expand all | Collapse all
  1. 1m 6s
    1. Welcome
      43s
    2. Using the exercise files
      23s
  2. 29m 37s
    1. What is Excel used for?
      1m 49s
    2. Using the menu system
      4m 30s
    3. The Quick Access Toolbar
      4m 41s
    4. The structure of a worksheet or workbook
      3m 41s
    5. Using the Formula bar
      1m 43s
    6. Using the Status bar
      2m 24s
    7. Navigation and mouse pointers
      2m 20s
    8. Shortcut menus and the Mini toolbar
      3m 24s
    9. Using the built-in help
      2m 54s
    10. Creating new files
      2m 11s
  3. 24m 1s
    1. Exploring data entry and editing techniques
      4m 41s
    2. Entering data with AutoFill
      4m 6s
    3. Working with dates and times
      3m 32s
    4. Using Undo and Redo
      4m 50s
    5. Adding comments
      2m 55s
    6. Using Save or Save As
      3m 57s
  4. 30m 7s
    1. Creating simple formulas: Totals and averages
      5m 25s
    2. Copying a formula for adjacent cells
      2m 54s
    3. Calculating year-to-date profits
      3m 9s
    4. Creating a percentage-increase formula
      4m 7s
    5. Working with relative, absolute, and mixed references
      4m 7s
    6. Using SUM and AVERAGE
      3m 25s
    7. Using other common functions
      7m 0s
  5. 46m 7s
    1. Exploring font styles and effects
      4m 7s
    2. Adjusting row heights and column widths
      3m 37s
    3. Working with alignment and Wrap Text
      4m 2s
    4. Designing borders
      3m 26s
    5. Exploring numeric and special formatting
      5m 36s
    6. Formatting numbers and dates
      4m 31s
    7. Conditional formatting
      4m 21s
    8. Creating and using tables
      9m 59s
    9. Inserting shapes, arrows, and other visual features
      6m 28s
  6. 20m 40s
    1. Inserting and deleting rows and columns
      4m 52s
    2. Hiding and unhiding rows and columns
      4m 2s
    3. Moving, copying, and inserting data
      5m 42s
    4. Finding and replacing data
      6m 4s
  7. 17m 51s
    1. Exploring the Page Layout tab and view
      7m 20s
    2. Previewing page breaks
      4m 56s
    3. Working with Page Setup and printing controls
      5m 35s
  8. 30m 30s
    1. Creating charts
      4m 36s
    2. Exploring chart types
      7m 47s
    3. Formatting charts
      5m 42s
    4. Working with axes, labels, gridlines, and other chart elements
      5m 35s
    5. Creating in-cell charts with sparklines
      6m 50s
  9. 12m 49s
    1. Freezing and unfreezing panes
      2m 39s
    2. Splitting screens horizontally and vertically
      4m 48s
    3. Showing necessary information with the Outlining feature
      5m 22s
  10. 23m 0s
    1. Displaying multiple worksheets and workbooks
      4m 17s
    2. Renaming, inserting, and deleting sheets
      2m 23s
    3. Moving, copying, and grouping sheets
      3m 39s
    4. Using formulas to link worksheets and workbooks
      6m 1s
    5. Locating and maintaining links
      6m 40s
  11. 20m 25s
    1. Using IF functions and relational operators
      3m 43s
    2. Getting approximate table data with the VLOOKUP function
      7m 6s
    3. Getting exact table data with the VLOOKUP function
      4m 42s
    4. Using the COUNTIF family of functions
      4m 54s
  12. 23m 50s
    1. Unlocking cells and protecting worksheets
      7m 50s
    2. Protecting workbooks
      2m 40s
    3. Assigning passwords to workbooks
      4m 41s
    4. Sharing workbooks
      4m 7s
    5. Tracking changes
      4m 32s
  13. 28m 32s
    1. Sorting data
      6m 9s
    2. Inserting subtotals in a sorted list
      8m 25s
    3. Using filters
      6m 16s
    4. Splitting data into multiple columns
      5m 4s
    5. Removing duplicate records
      2m 38s
  14. 35m 2s
    1. Creating PivotTables
      8m 36s
    2. Manipulating PivotTable data
      9m 47s
    3. Grouping by date and time
      6m 0s
    4. Grouping by other factors
      2m 33s
    5. Using slicers to clarify and manipulate fields
      4m 7s
    6. Using PivotCharts
      3m 59s
  15. 23m 29s
    1. Using Goal Seek
      6m 8s
    2. Using Solver
      6m 34s
    3. Using Scenario Manager
      6m 11s
    4. Using Data Tables
      4m 36s
  16. 24m 31s
    1. Definition and examples
      6m 48s
    2. Creating a simple macro
      7m 0s
    3. Running a macro
      10m 43s
  17. 29s
    1. Next steps
      29s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
please wait ...
Excel 2013 Essential Training
6h 32m Appropriate for all Jan 29, 2013

Viewers: in countries Watching now:

Whether you're a novice or an expert wanting to refresh your skillset with Microsoft Excel, this course covers all the basics you need to start entering your data and building organized workbooks. Author Dennis Taylor teaches you how to enter and organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of your data, and build charts and PivotTables. Other lessons cover the powerful IF, VLOOKUP, and COUNTIF family of functions; the Goal Seek, Solver, and other data analysis tools; and how to automate many of these tasks with macros.

Topics include:
  • What is Excel and what is it used for?
  • Using the menus
  • Working with dates and times
  • Creating simple formulas
  • Formatting fonts, row and column sizes, borders, and more
  • Inserting shapes, arrows, and other graphics
  • Adding and deleting rows and columns
  • Hiding data
  • Moving, copying, and pasting
  • Sorting and filtering data
  • Printing your worksheet
  • Securing your workbooks
  • Tracking changes
Subjects:
Business Charts + Graphs Spreadsheets Teacher Tools Education Student Tools
Software:
Excel Office Office 365
Author:
Dennis Taylor

Manipulating PivotTable data

One of the best features of a Pivot Table is the ease with which you can manipulate the fields. We're looking at a worksheet called Manipulating, it's in the Workbook 13-PivotTables and we're looking at a Pivot Table that was created earlier and we're thinking of changing the layout of the fields as we see them here. The Pivot Table fields list is off to the right. Sometimes this is anchored, you can click and drag its title and place it above the data if you wish or closer to the data and also make it larger this way. Again, as we look at the Pivot Table field list Product, Items, Salesperson, that's the layout we see to the left.

Imagine if you're making a presentation and the discussion about what we're seeing here has pretty much run it's course and the question that has come up, could we see this breakout by customer, possibly customer and region or customer and salesperson. So sometimes we want to make a change. What if I like to see Product down the left-hand side? I'm not interested in Salesperson for the moment, so I'll take Salesperson out of the Row Labels area. You can do it one of two ways. You can click and drag and remove it this way, another way is simply to uncheck the box above.

For the moment we've got a pretty small Pivot Table, not very valuable. But let's place Product in the Row Labels area and then let's see who are the buyers of this product, perhaps we'll bring in Customer and put that in the Column Labels area and now we see this layout. We have the same total that we saw before, 7997 but a different layout. We don't have to worry about formulas, we still don't see any formulas here but we can easily change the appearance of this and another thought might have occurred to you, what if a few years ago you created a list like this, perhaps it was done manually and you like to recreate that list and in that list the customer's names were down the left-hand side, in that portion that we refer to as the Row Labels area and the Product was across the top, in that area we refer to here as the Column Labels area.

So if you were to think of the data this way and you'd like to flip the data, so to speak, or transpose it, the term pivot comes to mind. We're going to pivot the data from the upper left-hand corner and actually that's not a single action, it's two actions. We're simply going to reverse the order of Customer and Product and that means dragging these two fields to their opposite location and in no particular order. So I'm going to drag Customer from the Column Labels area into the Row Labels area and we see an interim view there that occasionally might be okay.

Let's drag Product to the Column Labels area and there is that layout. And as we do this at different times we might want to click in the upper left-hand corner and Double-Click our boundary to adjust the column widths. Based on what we've seen so far, you might imagine that a Pivot Table is mostly about comparing two fields. Well it often is, but certainly it isn't limited to that. If we want to show this breakout here also by Salesperson, we could bring that field back into list. Because that's a text field, if we'd click the box for Salesperson, it will automatically appear in the Row Labels area.

If we want to go to Column Labels area we have to drag it there. So I'm just going to check the box for Salesperson and we see the list here. Now that's not very compact, that doesn't mean it's bad. But if we were to print this list, we can see that it's certainly lengthier than one we've been seeing but 70 rows is not outrageous, that's a reasonable amount of information and a lot of detail too. If we change the order of Customer and Salesperson, not exactly an instinctive idea, but let's do it anyway, watch what happens to our list. Currently as we view the Pivot Table, Customer is to the left of Salesperson.

As we look at the Pivot Table field list, we see Customer above Salesperson, so let's drag Customer downward and now we see the display this way. A different way of viewing the data and it's up to you to decide which you like best; you can change your mind at different times too. You might also be seeing as we do this, for example subtotals. You may or may not want those. If you don't, go to the DESIGN tab, Subtotals, Do Not Show Subtotals, we see the list this way. And once again we might want to switch back and forth the order of Salesperson and Customer just to see how it's looking.

And at different times you want to see a list a certain way, you can see it another way. All the while we're manipulating a Pivot Table; there is no possibility in any way of altering the original data. Think of the Pivot Table as a separate entity and we can change the layout any number of different ways. There might even be times when we say, you know I don't want to see this salesperson here but I want it nearby so I can get to it quickly. We can drag Salesperson into what's called a Filters area, right there. Recognize that in the Pivot Table now, we have an upper portion here, this is the Filters area.

The advantage here might be we only want to see data about one person or maybe two of our salespersons, click the drop arrow there and maybe we just want to see how well Emily has been doing, there is Emily only, her sales, 632 items sold altogether. And you've got other options up here as well, there is a choice for selecting multiple items, let's say we also want to see in our list here John Lucas and now we are seeing jus the data for those two people. So the Filters area has a role to play as well.

If we no longer need to see this differentiation, we could just possibly remove Salesperson from the list completely or possibly bring it back into the Row Labels area. Now there's another aspect of Pivot Tables that does surprise people. It's almost as if we have created a chart here in the sense that we've created something different, it's a different view of the data, it's certainly based on data in a different worksheet in this example, but what happens if the source data changes? So as we look at this list here, remember the total is 7997, let's suppose our list here is showing the Salesperson and the Product.

So I'm going to remove Customer, this time dragging it off, click the box for Salesperson, we see our salespersons here and I don't know if you remember but a few minutes ago when I actually removed it from here, we were using a filter and now that filter is still there. So if we look at Salesperson over on left-hand side and click the filter arrow there, there we might want to now Select All of the names there, click OK. In this list our total 7997, our top seller here is Harlan Vaughn 1068.

I want to show you what happens here and this might be a surprise if you haven't worked with Pivot Tables very much. I'm going back to the source data. It's on the sheet called Creating. I'm going to make a change to one of the records. We've got an update here and actually 12 items were sold here. Again, remember our original list is 7997. If this is going to be 12 that list is going to go to 8000. So I'm going to change this to 12, that will have the impact in the cell to the right which is calculating a total, you would expect that to change and surely our Pivot Table will have changed, right? If we go to Manipulating, it still says 1068; we still have the same total as before.

So what's happening here? In Pivot Tables, even though they are based on source data on another worksheet possibly or maybe on this worksheet, Pivot Tables don't change when the source data changes. You have to make that happen. And so with the active cell within the Pivot Table, go to the ANALYZE tab in the Ribbon and choose Refresh. Also there is a keystroke shortcut, Alt+F5. And what has happened, it's now 8000 and this number here which had been 1068 is now 1071.

So refreshing your data is something you need to do as you work with Pivot Table data. And there is another aspect to this as well as you work with Pivot Tables. Suppose you're having a meeting here and you're looking at the numbers and for whatever reason, one number jumps out at this person to that person, somebody wants to see the detail behind one of these. Who are the 19 customers here who purchased the bamboo coffee table or Icelita Kelly was the Salesperson? Double-Click, what's happened here, we're in a brand new worksheet and we're seeing here a list, these items here do total, 19 there they are.

This represents the source of that number, in other words what we're seeing here and let's adjust the column to make it a little bit more readable. This is data gathered from the source list and placed on a new worksheet in table format and so this represents the 19 that I Double-Clicked on. Here are the 19 items that were sold. And so as we go back to the Pivot Table right here, when I Double-Clicked 19, suddenly it's as if we go back to the original data we find these 19 items and we see them on a new worksheet, that's called drill down and we can Double-Click on any of the numbers in this list here and get the same effect if we're interested in that.

Now, if we make changes to our source data and then update this Pivot Table, this sheet isn't getting updated and so it could be incorrect. So when you do create these lists, keep in mind that you might want to get rid of them pretty quickly. But if you're not changing the data that much, they're going to be accurate until you make changes. If you no longer need the sheet, simply Right-Click and delete it. But it is a great feature called drill down and sometimes you'll want to do that. So we can see as we work with Pivot Tables we change our minds sometimes about the appearance of it, what it is we're looking at, we have the ability to move these fields back and forth.

Sometimes we're just looking for a better display; sometimes we're really looking to analyze the numbers in greater depth. The capability to manipulate and change the appearance of a Pivot Table is one of its greatest strengths.

There are currently no FAQs about Excel 2013 Essential Training.

 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

join now Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Excel 2013 Essential Training.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Notes cannot be added for locked videos.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.