As we come to the end of 2012 and our "Don't Fear The Spreadsheet" series... the ultimate question appears, as Tyler asks:"What is a Pivot Table?" Let me save you some time -- Watch This!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
And for more information about Pivot Tables, see "Pivot Table Data Crunching: Microsoft Excel 2010" by Bill Jelen and Michael Alexander! Pivot Table Data Crunching: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
And for more information about Pivot Tables, see "Pivot Table Data Crunching: Microsoft Excel 2010" by Bill Jelen and Michael Alexander! Pivot Table Data Crunching: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Don't Fear the Spreadsheet podcast, episode number 18.
What's a Pivot Table?
Well, hey! Welcome back to the "Don't fear the spreadsheet, podcast.
I'm Bill Jelen from MrExcel.
Here with today's question is from Tyler Nash.
Tyler: Really! Think that my boss would marry pivot tables if she could, what is a pivot table?
MrExcel: A pivot table is the world's most powerful feature in all of Excel.
When pivot tables came around a long time ago back in the 1990's.
All of a sudden it became possible to do incredible data analyses with just a few clicks.
So, here's a few hundred rows of data and let's say that we need a summary of regions, going across the top.
So east central and west and we want to see total revenue for each region.
By maybe, month or year.
This is incredibly easy with a pivot table.
We start on the...
Choose one cell in the data set, insert pivot table.
Okay!
And we said we wanted regions across the top, right.
So, that's we'll drag that field to column labels and there's a list of our regions, and then we'll take the date field and drag it to the row labels.
That's down the side, there's all of our dates and then revenue, there in the values area.
All right! Now, because our original data was daily dates, this shows up as daily dates.
Watch this, pivot tables offer something called group field.
So, I choose one of those dates, group field and I can roll this up to years.
I'll click [ ok ] and I have 2014, 2015 or I can say that I want to see it by years and months.
And now, I see the totals by years and months.
A strange bug here, they don't give us a total for 2014, but that's easy to fix, under field settings, we just changed that to this one, 2014.
Field settings change that to automatic instead of none and we are good to go.
There's so much more you can do with pivot tables.
You can...
You know, see this for a specific customer.
Let's say insert a slicer, for customer click [ ok ], and we can here, let's make this a little bit better.
Choose a few customers and see the totals for those customers or just Walmart, and see the total for that customer.
Your manager says, well, wait a second.
September tell me all of the sales from Walmart in July of 2015.
Just double click that cell and you get a new report with everything in July 2015.
That's interesting, it didn't respect the slicer.
Oh, that's a bug maybe they did it on purpose, but just pivot tables are an amazing, amazing feature that let's you create one-page summaries, out of thousands or even a million rows of data.
Well, hey pivot tables are one of the last topics in the book.
We just introduced them there.
This is the world's beginiest book on Excel.
This book makes Excel for dummies, look like it was written for Rocket scientists.
Don't Fear the Spreadsheet podcast, episode number 18.
What's a Pivot Table?
Well, hey! Welcome back to the "Don't fear the spreadsheet, podcast.
I'm Bill Jelen from MrExcel.
Here with today's question is from Tyler Nash.
Tyler: Really! Think that my boss would marry pivot tables if she could, what is a pivot table?
MrExcel: A pivot table is the world's most powerful feature in all of Excel.
When pivot tables came around a long time ago back in the 1990's.
All of a sudden it became possible to do incredible data analyses with just a few clicks.
So, here's a few hundred rows of data and let's say that we need a summary of regions, going across the top.
So east central and west and we want to see total revenue for each region.
By maybe, month or year.
This is incredibly easy with a pivot table.
We start on the...
Choose one cell in the data set, insert pivot table.
Okay!
And we said we wanted regions across the top, right.
So, that's we'll drag that field to column labels and there's a list of our regions, and then we'll take the date field and drag it to the row labels.
That's down the side, there's all of our dates and then revenue, there in the values area.
All right! Now, because our original data was daily dates, this shows up as daily dates.
Watch this, pivot tables offer something called group field.
So, I choose one of those dates, group field and I can roll this up to years.
I'll click [ ok ] and I have 2014, 2015 or I can say that I want to see it by years and months.
And now, I see the totals by years and months.
A strange bug here, they don't give us a total for 2014, but that's easy to fix, under field settings, we just changed that to this one, 2014.
Field settings change that to automatic instead of none and we are good to go.
There's so much more you can do with pivot tables.
You can...
You know, see this for a specific customer.
Let's say insert a slicer, for customer click [ ok ], and we can here, let's make this a little bit better.
Choose a few customers and see the totals for those customers or just Walmart, and see the total for that customer.
Your manager says, well, wait a second.
September tell me all of the sales from Walmart in July of 2015.
Just double click that cell and you get a new report with everything in July 2015.
That's interesting, it didn't respect the slicer.
Oh, that's a bug maybe they did it on purpose, but just pivot tables are an amazing, amazing feature that let's you create one-page summaries, out of thousands or even a million rows of data.
Well, hey pivot tables are one of the last topics in the book.
We just introduced them there.
This is the world's beginiest book on Excel.
This book makes Excel for dummies, look like it was written for Rocket scientists.
