Excel 2007 Pivot Tables Full Course
- Introduction to why Pivot Tables: youtu.be/sbpeYLqlfkk
- Pivot Table data source setup: youtu.be/4A5iUlCDQbg
- Build your first Pivot Table: youtu.be/otkRpncZLO8
- Options available in Pivot Tables Excel 2007 Ribbon: youtu.be/nbf_5fNrwlk
- Values in Pivot Tables: youtu.be/0ATC-9axYrk
- Working with the Rows part of a Pivot Table: youtu.be/GTuUFKxlhzg
- Working with the columns part of a Pivot Table: youtu.be/emV8np9P-f8
- Working with the rows and columns in Pivot Tables : youtu.be/IncGzWDxfwE
- Using the Report Filter in Pivot Tables: youtu.be/H8Eq7tyFSno
- Grouping items in a pivot table- days to weeks/ months, bands of numbers: youtu.be/ky0bk4nPT3w
- Build Pivot Charts/ Graphs directly off the Pivot Table: youtu.be/iqZq9jv5pC0
- Conditional formatting on Pivot Tables: youtu.be/5IwbbDpgRgU
- Build formula directly into Pivot Tables: youtu.be/Lu37OqGiqF0
- Get individual cells out of a Pivot Table: youtu.be/jD4V53swAS4
- Linking a pivot table to a database/ external source: youtu.be/xD3JftuPDiM
- Consolidate Pivot Table data from multiple locations: youtu.be/tE4F21os4Vo
We are now going to look in a little more detail at the Values Portion-- where the data is generally kept. Just a couple of useful bits of information upfront.
As you can see we have pulled through the Bill Column and you'll see it has told us "Sum of Bill". If you want you can pull the Bill Column through many times - you'll see I've grabbed it once, grab it twice and as you can see the exact same information comes through and we'll show you later why this is useful. Also note that the Column Labels now has something called Values- if you watch what happens here on the screen if I grab that and drag it and put it underneath the month and let go - notice that the Value headers now follow after the month. If I don't like that , grab it and move it above the month, let go -- now the Value header sits here with the month underlying it. I am just going to put it back there.
Now let's see what else we can do with the Values Portion.
If you click on one of the numbers, and you right click, you'll see you get a number of options but at the bottom here there is something called Value Field Settings. If I click on it, you get the following options -- just working through it- you'll see at the top is Custom Name. If you don't like the name Excel has given it you can type you own name in. You'll also notice that at the bottom there is something called Number Format. Let's just cancel out of here. If you look at this data you may be tempted to format the data here in the normal way perhaps right click Format cells and then go and do some formatting. You need to realize that Pivot Tables stand on their own so if you try to format the cells straight into the Spreadsheet, next time you refresh that formatting may be lost. As a result the only way to format it properly is to go to Value Field Settings and Click on Number Format and you'll see the exact same dialogue box appears but now when you choose the settings you can see although you highlighted one cell and click on OK you'll see that entire Value Column gets formatted because the Pivot Table knows that you want all those items to be formatted exactly the same.
Let's just click on this one here and go back into our Value Field Settings and let's look now at the Summarised Value Field by. You'll see when we drag the Value Field into the Values item it immediately assumed that you want to sum the information in that column. However there are a number of other options eg you could count the number of items. If I click Count and I say OK you'll see it tells me how many items it found and you'll see there were 58 items which added up to the R184. If I right click again, Value Field Settings -- perhaps I want to look at the Average. I click Average and say OK you can see that what it is telling me is on average for the 58 items previously identified at 184, the average was 3.17. Let's see what the other options are -- there is a Max -- it brings through the maximum number it finds in those areas, there is a minimum, shows the minimum number. And you will see there are other options -- product (counts how many numbers it found), there is a standard deviation and a few other statistical terms.
Just going back here -- let's put it back to sum. Now let's look at the "Show Values As" Tab .
When I click on that you'll see it says Show Values As -- currently it is at Normal -- so what it is doing is just showing the normal values. If I click on this a whole bunch of options appear -- you'll see there is a difference from, percentage of, running total in, and a number of other options.
- Pivot Tables in Excel 2007: Options in the Values section- SUM, AVERAGE, COUNT, % of column and more ( Download)
- Create Excel 2007 Pivot Table and Show Average Hours ( Download)
- Excel Magic Trick 1115: PivotTable to Count How Many of Each Item There Are In a Column ( Download)
- Pivot Table Excel Tutorial ( Download)
- How to add a calculated field to a pivot table ( Download)
- Using Count, Average and Sum in Excel Pivot Tables 📊 ( Download)
- Using SUM, COUNT and AVERAGE in Excel Pivot Tables ( Download)
- Advanced Pivot Table Techniques (to achieve more in Excel) ( Download)
- How To Quickly Calculate Total Daily Averages With Pivot Tables ( Download)
- Excel Pivot Table: How To Add a Percentage Column ( Download)
- Show Text in Excel Pivot Table Values Area ( Download)
- Excel Pivot Table EXPLAINED in 10 Minutes (Productivity tips included!) ( Download)
- How to Write Formulas in Pivot Table quickly ( Download)
- Pivot Table Custom Subtotals and Calculated Fields ( Download)
- Add Extra Column In A PivotTable ( Download)
