Highline Excel Class 20: Pivot Tables 20 Examples

[dahhwits40]

Good work, thanks bud!

[aareeeya]

thank you so much for the video.. Its totally worth it!

[cajnewt]

Thanks for the video.

[ExcelIsFun]

@cajnewt , you are welcome!

[gaotianyun]

Great video. Thank you!

[ExcelIsFun]

You are welcome!

[barbdwyre]

Excellent!

[ExcelIsFun]

@barbdwyre , I am glad that you liked it, but did you mean:

EXCELlent?

[vnklinger2003]

Mr Exxcel.. is it wise to utilize the same date for multiple pivots within the same workbook? Thankjs

[ExcelIsFun]

@vnklinger2003 , it is fine to have multiple Pivots.

[sirchang000]

Thank you for uploading this.. it was also very helpful and thoughtful of you to upload the worksheets!! Keep up the wonderful work!!

[ExcelIsFun]

@sirchang000 , link:

people.highline.edu/mgirvin/ex­celisfun.htm

[MofoFanclub]

Brilliant series mate! Well done.

[ExcelIsFun]

I am glad that you like it!

[highonhayek]

ExcelIsFun fans,

How would you set year as the column values and month as the row values in 2003? In other words, how do I accomplish (8:30) in Excel 2003?

Thanks,

HoH

[ExcelIsFun]

I do not really understand what you are asking, but to group by year or month in any Excel version, drop date in PivotTable, right-click date, point to Group.

[laelcox]

Fabulous and comprehensive. I like the depth.

[ExcelIsFun]

You are welcome!

[Nkhanum]

Fantastic explanation!

[ExcelIsFun]

I am glad that it helped!

[nordyfan83]

Extremely helpful videos-thanks! I'm creating a multiple-sheet pivot table and perhaps that is the issue. When click finish I actually end up with a table but in the "Pivot Table Field List" all I get as options are: Row, Column, Value and Page 1...what am I doing wrong?

[ExcelIsFun]

That is what happens when you consolidate. I do not have any videos on that. The best book for the hows and whys of consolidation is the Bill Mr excel Jelen Book about PT.

[scorpi009]

hey. the video was so helpful. thanks a lot. but i was wondering if u hv some kind of exercises so that i can practice. thanks for ur help

[ExcelIsFun]

Yes, I do have lessons. In order to send the link I will have to e-mail you.

[Nkhanum]

@ExcelIsFun It would be a great deal of help if I can also get the lessons to practice.

Nadia02@msn.com

[ExcelIsFun]

watch this video to see how to download:

youtube[dot]com/ExcelIsFun

[reinafaire]

This is awesome. Thanks for doing this video.

[ExcelIsFun]

You are welcome!

[jerryneyk]

Cannot thank you enough for these EXCELLENT and helpful videos!

[ExcelIsFun]

You are welcome!

[Harvey3101]

Great video. I think pivot tables are one of the most important thing in excel, and you have explained it ver well. After three times looking it I think I understand most of it.

[ExcelIsFun]

I am glad that the video helps!

[acemaj]

shhhpooooop!

[LuvSpongy]

Excellent tutorial

[ExcelIsFun]

I am glad that you like the video!

[kmsmith316]

How would i get the sum and also the average in the same table? Thanks.

[ExcelIsFun]

Drop the value field into the Value area 2 times. Then change the function on one to AVERAGE and one to SUM.

I will make a video about this soon.

[whallie]

Thank you for the useful tricks Mike.

How would you sort the Product field into say two categories (ex "Yanaki" and "non Yanaki")

I thought of using filters to do that but I cannot insert the same field twice in the pivot table. I need to calculate the difference between "Yanaki" sales and "non Yanaki" sales.

Thanks in advance

[ExcelIsFun]

YouTube won't let me post the answer 9synatx error). I sent e-mail to you.

[dhattest]

Thanks for all the great videos. I am learning a lot! On this video, I tried to create a pivot table with my own table, but the headers did show up when I dragged and dropped the fields into the pivot field area. Only the data are displayed in the columns and rows, but no headers. What am I not doing? Thanks again.

[ExcelIsFun]

I am not sure what the problem is. Try dragging the data to the middle, instead of the columns and rows. Then drag the categories to the columns and rows. Does your table have field names?

[dhattest]

So I have a table with 5 columns and headers and 20 rows of data. Following your video, I highlighted a cell inside the table, selected Insert "Pivot Table". I have 2007 Excel. The "Create Pivot Table" dialog box comes up and in the Select a table range textbox, the Table name "Table1" is automatically populated but there is no range unlike what your dialog is showing. When I drag the fields over, no headers show up, the data becomes the headers for columns and rows.

What am I missing?

[ExcelIsFun]

I am not sure. Send me an e-mail at YouTube, then I can send e-mail address and i will take a look.

[TheSoccerfan022]

I'm not adding anything extra to what Excel is suggesting for the range. The way I am getting around this is to copy from your workbook to my own and it seems to be working that way. Thank you again for these truly amazing videos. I originally tried to learn the advanced features by using a book and it was not going well. Your videos have made everything so much easier to learn. God bless you!

[ExcelIsFun]

To learn how to use the excelisfun channel at YouTube, search for 'excelisfun', then click on the Channel link, then watch the videos that automatically plays - it shows how to search and find playlists.

[TheSoccerfan022]

Fantastic videos! I am having a problem with the pivot tables. I downloaded the workbook and whenever I try to create a pivot table I get the error msg "Data Source Reference is Not Valid." I had the same thing happen yesterday with another one of your workbooks. I am doing exactly as you say to click a cell within the table and selecting Insert Pivot Table and letting the computer choose the range of cells. Do you know what I am doing wrong? Thanks!

[ExcelIsFun]

That error happens when the syntax of the range is not correct. For example, in this video, the correct range is: PivotTable!$B$21:$K$230 Put if you accidentally type a period at the end like this: PivotTable!$B$21:$K$230. That error would come up. Or any other deviation such as: PivotTable!$B$21:$K$230` or PivotTable!$B$21:$K$ I do not know what the specific cause is in your case, but that specific error message is common. Try it again 10 times... post back if want

[whallie]

@TheSoccerfan022 : I had this problem as well. I tried to be fancy and create a pivot from a text file (so that when the text file is updated, the pivot table is updated as well) I had the data imported on Sheet1 then I switched to Sheet2 then tried to create the pivot. That generated the error.

Try creating the pivot while on Sheet1 (imported data) you'll have the opportunity to click on "create on another sheet" AND rename the range with the name of the imported file (see Name Manager)

[timelessstudios]

Great videos! Here's my dilemma analyzing stocks. I am need to get the average daily volume between highs and lows of a stock price. Example: Stock movement = $19 low on 7/10/10, $21 high on 7/25, new low of $17 on 8/5 and new high on 8/17 of $23. I need to figure average daily volume of shares traded between each high and low. Help please!!! Can't figure this out. Thanks!!

[ExcelIsFun]

I do not know how to do it with PivotTables. Try posting your question to this site:

mrexcel[dot]com/forum

[aman1245singh]

Thanks again for the wonderful video. God bless you sir. :)

[ExcelIsFun]

You are welcome! Thanks for the 5 star rating!

[Jahangeer691]

Very informative. Thanks a lot.

[ExcelIsFun]

You are welcome!

[PyxisCervisiam]

for some reason this is related to a Craig Ferguson video^^

[ExcelIsFun]

cool!

[yeewilton]

Hi Mike,

Since Excel 2010 is out now. I was wondering if you could do a video on Power Pivot and where do you suggest that we get a data set of 10 million rows?

Thanks,

Wilton

[ExcelIsFun]

I will do it soon. But why wait. bjele123 at YouTube (Mr Excel) already has 20 incredible videos about Power Pivot!!!

[Jocote44]

let's say that I have the sales for jan,feb, march and april on a pivot table. how do I add a column to calculate the average of the sales on another column?

[ExcelIsFun]

Try the 11:00 minute mark of this video.

[Thahzeeb]

Thanks Mike for the quick reply. I ll just try that out.

[Thahzeeb]

Hi Mike,

Great video!

I have made my pivot table look like the excel 2003 style (drag and drop), but everytime I open an excel workbook, the pivot table is in the excel 2003 style. Is there a way to have it look like the excel 2007 style by default ?

[ExcelIsFun]

If your extension is .xls it always shows up like Excel 2003. If the extension is .xlsm or .xlsx it shows up like Excel 2007. That is all I know.

[chinchonchinchon]

Pivot Tables indeed save businesses and other organizations thousands of hours in office work.

Amazing invention.

[chinchonchinchon]

Indeed, Pivot Tables save businesses, and other organizations thousands of man hours work with just a few simple clicks.

Amazing invention.

[shutkainbm]

Help, how do I download spreadsheets? I do not see anything to click on. Thanks

[shutkainbm]

I still can't figure out what I am supposed to click on to download the spreadsheets. Could you please slow down when you tell what to click on. I do not see anything to click on here

[ExcelIsFun]

Search for and then watch this video:

excelisfun Search for Excel Videos, Playlists & Download Workbooks

[154song]

Dear Mike,

When I tries to group by date in a pivot table, excel 2007, and did the same steps showing in this video. There are only two options, rows or columns under the group function. I did not see Auto or By under grouping. Wondering why? Thanks,

[ExcelIsFun]

The only thing that I can think of is that you built the table with the Consolidation feature and it that case you could have only columns and rows...

[lmshadow2007]

Parabéns, as suas dicas são incríveis..

[aqixinzz]

I love you, because you make me love the Excel.

[ExcelIsFun]

Woo Hoo! Since love is the most important thing in the universe and Excel is the most important skill to get a job - Love, Love, Love!!

[nicoyanicoya]

LOVE LOVE LOVE all ur videos , helpful for all of us in business who were not exposed to excel in this manner!

[ExcelIsFun]

I am glad that the videos are helpful!!!

[GeneralApathyNonFan]

Dear Mr. ExcelIsFun,

How does one get to teh Calculated Field in Excel 2003?

Happy Turkey Day!

General Apathy

[ExcelIsFun]

I don't remember any more and I will not be back in my office with my computer that has Excel 2003, or amny of my 30+ Excel books. In the mean time, you should post your question to the Mr Excel Message Board:

mrexcel[dot]com

[tongzilla]

Can you pls help me with deleting a field in a pivot table in 2004, cuz I can't drag it out like you do in 2007. Thanks.

[ExcelIsFun]

I do not now how to use a MAC or Excel 2004 for the Mac.

[ttoribio13]

Does grouping by date work in 2003? When I try to group by date, excel tells me that I "Cannot Group That Selection".

Thanks!

[ExcelIsFun]

Yes it does work it 2003. That Error message means that you have a foreign piece of data in the original column of data. For example, if you have dates and one of the records has text in the date field, you will get that error. You have to check all data. One way to check is to sort the column if the date column/field is sorted Z-A, then the date stored as text will come to the top.

[oscillian]

Your teaching style is awesome! Educational, whitty and broadminded.

I teach Excel to various people in the steel industry, and I'm starting to implement e-learning on the Adobe Connect Pro platform, setting up asynchronous learning modules and hosting live classes. What software do you use for your clips? Camtasia? The sound of your voice is excellent! Is it a regular headset or are you using a pro mic?

Greetings from Sweden! /Jon

"A student should always be able to rewind the teacher"

[ExcelIsFun]

I use "Camtasia". Camtasia is great because when you buy the program, it comes with "how to videos"! I don't know what mic I use. It is cheap. I just bought it without looking. I have use three different ones - all are just $19.95 cheap-os.

[ExcelIsFun]

It is exciting that you are doing on-line. I find it fun to make videos!

[redballyou]

thank you I enjoyed watching this, +subscribe

[ExcelIsFun]

You are welcome!

[morecambeboy]

i am struggling with some of these in my MBA, but after i saw the videos i am getting it over.

Thanks to you Mike.

[ExcelIsFun]

I am glad that the videos helped with you MBA! I am happy to help!

[ShunmasBeeablo]

Hi Mike !

Another anazing vid from you...Cheerz

[ExcelIsFun]

Dear ShunmasBeeablo,

I am glad that you liked it!

--excelisfun

[Dave51262]

I have just started using pivot tables and this really helped make it clear.  I learned lots of new things. Thanks again, you are the best!

[ExcelIsFun]

Dear Dave51262,

Yes, Pivot Tables are so easy compared to similar methods for summarizing with multiple criteria.

--excelisfun

[Thunderbird2014]

Excellent video.  This is powerful stuff. 5 stars!

[ExcelIsFun]

Dear Thunderbird2014,

I am glad that you find it useful!

--excelisfun

[beaniehoke1]

I just went form excel 2003 to 2007. I never used pivot tables. After watching this video, I will use them. Great video. My favorite video is conditional formatting and now pivot tables are number 2.

[ExcelIsFun]

Dear beaniehoke1,

I agree; PivotTables and Conditional formatting are powerful and great fun!

--excelisfun