CS 100 (Learn) — CS 100 (Web) — Module 06
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video, we are going to explore what is arguably one of the most advanced features of Microsoft Excel: pivot tables.
I have my data in my spreadsheet (once again we have fake company data).
I highlight the entire table, and [click on] the "insert" tab.
The first option is a pivot table.
The description from Excel says: "easily arrange and summarize complex data in a PivotTable".
All right Excel, let's see what you can do.
I click on pivot table and [Excel] automatically detected the range for the pivot table.
I want to put my pivot table in a new worksheet (I click OK) and we now have a new spreadsheet that is a pivot table.
It says: "to build the report, choose fields from the pivot table".
I might be interested in (for example) the date [so I drag the date to the rows area]. I want to look at the order [totals, so I drag order to the values area].
I have summarized for each month what my sales values are.
That is pretty useful: I can quickly see how many sales I had in each month.
Let's add some more fields to our pivot table.
Currently, we have the date and the sum of the order, so let's say I am interested in [which] province the sales were in.
I add that as a new column, and now it shows me across the top [(or the horizontal axis)] the columns and along the vertical axis we have each month.
It has now summarized all the data for the sales amounts by month [and] by province.
That helps me visualize what is going on with my data.
Let's say I am no longer interested in looking at my data by province, so I am going to remove that field.
Now I am more interested in looking at the different categories of sales that I have [by adding category to the columns].
I can see how much my sales were in autos, clothing, electronics and home: the different divisions based on month.
[Now] I am no longer interested in looking at it by month: I want to look at the whole year, so I remove the months [from the rows] and I remove the dates as well.
I want to look at the province on the rows, so now I can look for each province the sum of sales in each of the different categories.
The reason it is called a pivot table is [because] it can be rearranged: some things can be rows and some things can be columns.
[If] I want to rotate this around (or pivot the information) I can now move the categories to be on the rows and you will notice it still summarizes everything quite nicely but now I can move the provinces to be up at the top (or I can move them back down together) and I can go back and put dates across the top.
Now I can summarize my auto sales by province for each month: it automatically adds little expanding boxes [for the categories] if I am interested in summarizing the data that way.
[There are] a wide variety of different things that we have available to us.
We can add filters. so you do not want the columns to be months.
We want the filters to be the category [so we move the category field to the filters area].
Notice at the top there is [a] new area for [filtering the] category.
Let's say I am only interested in home sales, [we can use the filter to] only see home sales. [Now] I want to see home and auto, so those are home and auto sales for those provinces for those months.
The possibilities are almost endless. [There are many] different ways you can rearrange your data.
Now that I have shown you a pivot table, I am going to show you one more tool that is very similar to pivot tables and in fact has a pivot table built into it.
I have returned to my original table of data, and instead of inserting a pivot table, I am going to insert a pivot chart (I click on pivot chart).
[Once] again [Excel] auto-detected the range of data I am interested in (I [click on] enter this into a new worksheet) and it created a new pivot table and it automatically created a chart that is attached to that pivot table.
Everything I could do with a pivot table I can now do.
For example, if I want to look at the total order based on province. [It] generated a pivot table, and automatically created a pivot chart. As I modify my pivot table, the corresponding pivot chart also rearranges itself automatically.
For example, if I am interested in by province what the total sales were and for each category I now add that as an extra secondary category and everything appears on the chart as I would expect.
Excel is generating a pivot table in the background, but it is also automatically modifying a chart to reflect the data.
[It is] a very powerful tool, once you sit down and understand how it works. [It provides the ability] to visualize a large amount of data in a very convenient interface.
That [was] working with pivot charts and pivot tables.