Get my free 3 hour Introduction to Excel 2013 course. Get 17 training videos to help you learn MS Excel 2013. Click here to get the free course: simonsezit.leadpages.net/free-excel-2013-course/
In this video, get to know the basic principles of creating pivot table in Microsoft Excel 2013.
Get the full course on Excel 2013 course here: www.simonsezit.com/courses/microsoft/learn-microsoft-excel-2013-advanced-training-tutorials/
Watch the Excel 2013 playlist: www.youtube.com/playlist
Stay in touch:
The Simon Sez IT email newsletter: bit.ly/18bMwY0
YouTube Channel: bit.ly/foiItB
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.