Adding Filters and Formulas
00:00 Now that you can read and write Excel files, the next few videos are going to cover some extra features that can really add to your workflow when handling spreadsheets. In this video, you’re going to see how to add filters and formulas to your worksheets.
and then load the Amazon reviews worksheet. Then, set
sheet = workbook.active. Before you can add filters, you need to know the dimensions of the worksheet that you’d like to apply the filters to. To get this you could say
sheet.dimensions and that will tell you that the data goes from
Now that you have this information, you can say
sheet.auto_filter and then set the
.ref (reference) equal to
"A1:O100". Run that, and now this is saved within Python. So to actually see this through a workbook, you need to save it. So say
workbook.save() and then set the
filename to something like
01:06 and a new worksheet should pop up in your directory. Let’s take a look at what that looks like. So, I’ve opened it up here and you can see that all the data’s still there and there are filters so that you can start to filter rows based on what information you’re interested in.
02:00 you’ll see that you end up with quite a bit of this frozen set of strings, and you might recognize some of these from formulas within Excel. There’s things like different types of averages, standard deviation, things like that.
So, remembering the dimensions of that worksheet are from A1 to O100, let’s put something into column P. You can say
"P2"—and let’s say that you wanted to grab the average of the number of stars that were left in the reviews. So, you could type in an
"=AVERAGE()", just like you would in an Excel cell, and here, you want to grab H2 to H100 because column H is where the stars are located.
02:58 Okay, didn’t see any errors, and the new file has popped up in the directory. Going over to it, scroll over until you get to column P and there you go. In P2, here’s an average value. And notice that it’s not just the value, it’s the actual formula. So, that’s pretty cool!
03:52 This is a good way to see how many of these reviews were actually helpful to someone. You can notice here that everywhere else I’m using double quotes except for right here, where the main string is in single quotes.
04:04 This is so I can use double quotes inside of the actual formula string that goes into Excel. The formulas in Excel will only work with double quotes, so you need to make sure that anything that gets passed in has them.
Whether this means single quotes are outside, or you escape the quotes inside, just make sure that Excel sees double quotes. To make this a bit clearer, let’s actually try this out. Let’s say
"P4" and then flip the quotes around.
04:39 Okay. So now, save this, because there’s no errors. It looks like everything went well. Just save this as the same filename. Let’s go open it up! If you open this up, the Mac version shows Repaired up here and it gave me a warning that there was unreadable content. And yeah, if you go over here, P2 has the average, P3 has this =COUNTIF() formula,
05:22 It wasn’t until the file was actually open in Excel, that the error became apparent. All right! Now you should have filters and formulas down. Now, it’s time to learn how to style cells in the next video.
Become a Member to join the conversation.