Working with Pivot Tables in Excel 2013 - Part 1 - Learn Excel Training Tutorial

In this video, get to know the basic principles of creating pivot table in Microsoft Excel 2013.

Stay in touch:

If you enjoyed the video, please give a "thumbs up" and subscribe to the channel -)

Welcome back to our course on Excel 2013 Advanced. In this section we’re going to start to look at pivot tables. Now it’s a little bit tricky really when we start on pivot tables here because I’m sure that some of you will have used pivot tables before, at least in a basic form. On the other hand, understanding some of the basics of pivot tables if you haven’t used them before is absolutely essential even if what we’re going to do is go into some of the more advanced aspects of their use.

So what I’m going to do is to start with a relatively straightforward case study with some fairly straightforward data and develop a pivot table and a pivot chart based on this straightforward data before we move into some of the more advanced aspects and in particular some of the aspects that have been introduced in this and other recent versions of Excel.

There are a few features of Microsoft Excel that really are considered to be its greatest strengths and amongst these are pivot tables, pivot charts, and the associated tools and techniques. They’ve been in Excel for several versions now. By no means they are new and they’ve continually been improved particularly in recent versions, but the fundamental idea of pivot tables has been around for some time and has been one of its great strengths for some time.

The first thing to explain to you is not what a pivot table is. I’ll come back to that in a little while. I’m going to start by explaining situations in which you would use pivot tables because that will help to explain not only why you use them but what they are.
We use pivot tables for what we call transactional data. So that’s records of things that have happened normally in some kind of sequence. Now that doesn’t necessarily mean that there is some kind of dependency between these items, but it does mean that they are similar events or similar measurements that have a certain set of properties in common.

Now the case study we’re going to start with is a very straightforward one and it’s to do with sales in convenient stores. And in a number of convenient stores in the U.S.A. we’ve recorded individual sales over a long period of time. I’ve actually got a lot of data in the workbook that we’re using for this example, many thousands of transactions and each transaction is a row on this sheet.

So taking that row, for example, we’ve got a date for the transaction. We’ve got a branch. It’s the Denver, Colorado branch. We’ve got a value, $1. And the department in which the sale was made. It’s breads. Now if I go through all of this data and as I say there are many thousands of lines of data, you’ll see that I’m dealing with a few branches over a period of time, not a very long period of time. But there are hundreds of sales each day and they’re all categorized in exactly the same way.

So first of all, what we’re dealing with is rows in a worksheet or some worksheets which each represent some kind of transaction. It’s not necessarily a sale but just something that happens some event, something that was measured.

Now one of the problems you always get with very large amounts of data is finding some meaningful ways of analyzing that data or presenting that data. So for example, given this straightforward convenient store sales data how might we want to analyze it? Supposing we wanted to show, for instance, how the sales in the stores that we’ve got varied over a period of time. Maybe we would plot the total sales for each store for each month in the time period where we’ve made the measurements or maybe for each week.

Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.

[WalterLiddy]

Thanks - this is very helpful.  All the rude people complaining that it doesn't cut immediately to the bit they're wondering about need to realize that these videos are clearly intended for people with no prior knowledge.  If you want a more advanced instruction, fine, go find it.  But there's no sense in the poster eliminating the viewers who need the help most by assuming they already know what YOU may happen to know.  Nothing he's saying is irrelevant.  He's not wasting time.  He's just being clear and methodical.  Finally, if you have the gall to complain about free instruction that you'd have to pay for a course to receive otherwise, then you don't deserve to have it catered to your preferences.

[BrucknerMotet]

I like the pace at which the instructor speaks. He strides along at a nice easy gait like a walking partner who isn't too slow and isn't too fast.  Just right for an itinerant holding court as we proceed along towards understanding.  And the presentation is well-conceived. For example, when he says he won't start with a definition of "pivot table, " that might throw some listeners who expect definitions up front.  But he gives a reasonable and good explanation of why he isn't starting with the definition.  Spot on. 

[shreek]

Thank you! Your tutorial has made a novice like me very comfortable with Pivots! Thank You again..

[Grefcon]

Skip to 9:30 for what you need to know, Thank you for the video.

[abhiasiani]

Excellent video. Thank you Simon. To the gentlemen complaining, point out the irrelavant points Simon is talking here. He has set it up considering even the guys who are pretty much new to the world of excel. You guys might be scholars, but give them some space too...

[MsRuTgErSz]

I wish all Excel how-to videos were like this one! Well done!

[soontobeaqueen]

i don't think he speaks too much. The clarifications are needed. Good job!

[syu]

This 4-part series on pivot table is excellent.  Very clear.  Thanks.

[fanofdejr]

Thank you for providing a great tutorial for various Excel features. You've done well in explaining much of how these features work while doing so in a simple way that pretty much anyone can understand without a condescending tone.

You definitely have a great way of presenting information with such a welcoming & encouraging approach that invites the listener to learn.

Thank You

[gabrielmortensen]

I really like how you explained the basics and why

[MoreBurgerKingMoreJoy]

<= Typical American... impatient, always pressed for time, forward. Excellent information! Presentation is professional, a little dry, wandering, but highly instructive and informational non-the-less! I want to learn this, and you are obviously an expert, but its an endurance run staying focused due to your tone, energy, and speed.

[MrLeo]

Skip to 9:30 anything before that is blathering

[tkdwoman]

Thank you for explaining this so clearly!

[NjabuloHadebe]

Thanks. for sharing everything i need to know regarding Pivot Tables. The first 9 minutes was worth it.

[ChristopherHuckins]

I will be studying this all weekend. Thank you for your excellent work. 

[marvinguiao]

I love free education and this is one of them. I just enrolled to the free course and recommended it to a friend. Thank you for posting this Mr. Simon.

[wasimbader]

HI Simon, Thank you very much, I am learning Data Analysis including Excel one of the course work, YOUR VIDEOS has opened my horizon beyond in learning and gave me the confidence to produce excellent reports and analysis on real cases.
Using Excel with SQL and using Pivot table and V lookup, you made my life easy. Your teachings and professional insight in Excel is Excellent. Thumbs UP>
Thank you.

[yallondaka]

Good Video. Explains nicely the "Pivot" in Pivot Tables

[ryantravers]

Oh my god. A person could die of thirst before you share anything useful.

[qqq]

thanks for the uploads, these are great ;)