Pivot tables enable you to summarize and reorganize your data dynamically—but you can’t summarize just any data set. In most cases, your data needs to be laid out as a data list.
- [Instructor] Pivot tables enable you to summarize and reorganize your data dynamically. But you can't summarize just any data set using a pivot table. In most cases your data needs to be laid out as a data list. In this movie, I will describe what the data list needs to look like and my sample file is 01 01 data, which you can find in the chapter one folder of the exercise files collection. This worksheet contains a data list and you can see that it has six columns. And the column headers are in row one.
Those are year, month, quarter, project, budgeted and actual. And budgeted and actual refer to labor for the project that is mentioned in the project column. Each row within the table after the header row contains a particular fact about the data set. If you look at row two, we'll see that we have data for January of 2016. Happens to fall in quarter number one for project mt22841. And then we have the budgeted and actual labor costs.
The next row in the table, row three, contains the values for February of the same year and also for the same project. Row 14, if we go down, contains data for January of 2016 again but in this case it's for another project, su13214. If we continue scrolling down, we'll see data for other projects in 2016. And then, starting with row 15 in the worksheet, we'll see data for January of 2017 and we start our list of months and projects again.
And the list terminates after row 97 where we have the values for December 2017 for project lv89103. As I was scrolling down through the list, you probably noticed that there are no blank rows and that's very important. If Google Sheets encounters a blank row anywhere in your list, it will figure that the list has ended and it will miss any data that follows. So it's okay to have block cells but you cannot have any blank rows in the list.
It's also important that you have no extraneous data around the list. So you can see here I'm at the bottom of the list that the left side runs up against the row headers and also we have an empty set of cells below the list. And if I scroll all the way back up, you can see that there's nothing in column G that would confuse the pivot table creation engine. The data's by itself and it's pretty clear that it should go into the pivot table.
And finally, you can see that I formatted the headers in row one so they are distinct from the actual contents of the rows. Sheets is pretty good about determining what headers are and aren't. The biggest hint is that they are in the first row of a data set. But it doesn't hurt from a visual perspective to change the formatting. My standard change is to format the headers in bold and also centered in their cells and have just regular formatting for all of the values below that.
Once you have your data source arranged as a data list, you can create your pivot table.
- Arranging data for use in a pivot table
- Editing the data source
- Creating a calculated field
- Sorting values
- Filtering a pivot table by rule, selection, or filter field