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.

00:13 Get started by opening up an interpreter session,

00:17 do your imports,

00:22 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 'A1' to 'O100'.

00:43 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 "sample_with_filter.xlsx",

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.

01:25 This gives you all the sorting options and everything else that a filter does in Excel. So, that’s pretty cool! And as you can see, it wasn’t that difficult to add.

01:34 If you already know the dimensions of the sheet you want to work with, you don’t even need to call .dimensions there to find out. You can just add it directly.

01:42 The next thing that you might need to know is how to add formulas to a worksheet. To do this, you need to add an extra import from openpyxl.

01:50 So, from openpyxl.utils import—and then in all-caps—FORMULAE. If you take a look at what’s in here,

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.

02:15 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:40 Just to double-check that, let’s go to the worksheet, and there’s your star_rating right here in column H.

02:49 Go ahead and assign that there, take the workbook and save it. And this time, set the filename to something like "sample_formulas.xlsx".

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:16 A user could then go in and change this if they only wanted to get a couple of these cells or take a look at different ranges. They’re not stuck with the value that you calculated using Python.

03:29 Now try a bit more complex formula. Take cell "P3", and then this time, set this equal to '=COUNTIF()' and do I2:I100, and then pass in a ">0" (greater than zero).

03:43 What this is going to do is this is going to take a look at everything in column I, which is the helpful_votes, and it’s going to count them if it’s greater than zero.

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.

04:16 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:34 Ope, missed a quote, on that.

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:04 and then P4, which had the incorrect quotes, is just blank. So with that there, I wasn’t even able to open up the file without Excel repairing it.

05:14 I just wanted to show you that so you can keep it in mind because the actual Python code didn’t show any errors when it ran, so you might think that everything’s okay.

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.