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 how to use filters function in Excel.
I have generated another fake spreadsheet with some sales data.
This data has a date, [and] a category such as home, electronic sales, auto, or clothing and an order amount.
We are looking at this big table [and] it is too much data for us to digest.
We want to start filtering out some [of the] data and look for individual sales to see if we can notice [anything] interesting.
Notice at the top I have added a name for each column.
That [name] is often referred to as the field name. [For example, the] date field, name field, or category field. This is going to be useful when we move on to pivot tables [in addition to] filters, so I encourage you to start organizing your data and using column [field] headings.
In the data tab, there is a special button called "filter", and when I click [on] filter it automatically detected the organization of the data, which is a nice feature of Excel.
Notice that [for] each column there is a little drop-down arrow that is going to let us filter out certain [components of] our data.
For example, in the province [drop-down] there [are] a couple different choices.
First, I can sort [the] data by the province in a nice, convenient way so now all of [the] data is sorted by the province.
I may want to sort by the date [to] look at my sales [sequentially].
[Perhaps] I am more interested in sorting my data by orders, [to ] look at the different order sizes.
If you want to sort [your data] this is a really convenient way of doing sorting in Excel.
The other thing we can do is apply filters.
For example, we may only be interested in the [orders from] January [April]. If I click on the little arrow [beside the date field name] you can see that there [are] a lot of things [displayed].
All [of] the months are "checked". One little trick is [to use] the "select all" check mark: [it] will "uncheck" or "check" all [of the months].
For example, I am only interested in the data from April [so I click on "April"]. Only the [sales data from] April [is displayed].
If I am only interested in April and May, [by clicking those months, entries from] both appear.
[Now] I am only interested in orders sent from April [or] May, in the province of Ontario, and I am only interested in orders that are over $1,000.
This [filter] is a little more complicated: I am going to go to [the] "number filters" [entry] (because [Excel] detects that the column [contains] numbers) and I am going to [click on] "greater than or equal to" $1,000.
Now we have all of the orders from April [or] May that were in Ontario over $1000.
The filter tool is really useful for you to help visualize and drill down and see some of the data that you have.
I am going to clear all my filters [by clicking the "clear" button on the filter tab] and now all of our data is available [visible again].
I am going to "undo" that... notice that it was hiding the data that we were not interested in: all of the data was there, it was just hiding it. That is what is meant by "filtering" ([which] is why this tool is called filter).
There are some [of the] advanced tools you can use with filter.