Focus video player for keyboard shortcuts
Auto
- HD
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Using Excel formulas in our Python code is easy!
Exercise files
Download this lesson’s related exercise files.
Using Excel Formulas60.2 KB Using Excel Formulas - Solution
55.5 KB
Quick reference
Using Excel Formulas
Using Excel formulas in our Python code is easy!
When to use
Use this whenever you need to pass an Excel formula from your Python code to your Excel spreadsheet.
Instructions
# Find the sum of the cells from B2 to B5
ws['B8'] = "=SUM(B2:B5)"
Hints & tips
- ws['B8'] = "=SUM(B2:B5)"
- 00:04 Okay, in this video I want to talk about using formulas in an Excel Spreadsheet.
- 00:07 So if you've used Excel Spreadsheets in the past you have likely used formulas.
- 00:12 And by formulas, I mean, adding up the sum of a certain number of cell,
- 00:17 finding the average of a certain number of cells, things like that.
- 00:21 So I've created a new file called widgets.py.
- 00:24 And I've got our basic starter code here, and we're loading a spreadsheet called
- 00:28 widgets and setting the active worksheet and then we're saving it as widgets2.
- 00:32 So this widget spreadsheet, we can go ahead and open it here,
- 00:37 it's just a very basic spreadsheet.
- 00:40 And I've got pretending we're a widget company, we're selling blue widgets and
- 00:43 red widgets and green and purple and yellow widgets.
- 00:46 And this is the number sold.
- 00:47 So if we wanted to, for instance, find out how many total widgets we sold,
- 00:52 we might use a formula that looks something like this.
- 00:55 Where we create the sum of all of these things and we see it's 2300, right?
- 01:01 We might want to find the average, we can do a formula for
- 01:05 the average, whatever you want to do averages 460.
- 01:09 You probably used to doing formulas in an Excel spreadsheets.
- 01:11 So how do we do this with Python, well,
- 01:13 that's what we're going to look at in this video.
- 01:14 So, let's go ahead and close this and head back over to our code, and
- 01:19 to use formulas with our Python code, we just write the formula right here.
- 01:23 And then just pass it into a cell like we would pass anything else.
- 01:28 So, for instance, if we wanted to put something in B8,
- 01:33 well, we already know how to do that.
- 01:36 We would set ws and then B8 to = and
- 01:40 then we could put, something.
- 01:44 Now, if we save this and ran it, and opened widgets2,
- 01:48 we would see "Something" and "B8".
- 01:51 Let's, go ahead and do that real quick just to make sure, so
- 01:54 let's go python widgets.py.
- 01:59 And now we can come over here and open widgets2, and we see sure enough,
- 02:02 there's something listed right there.
- 02:04 So that we can close this and head back over here.
- 02:08 So, instead of passing something to use formulas,
- 02:11 you actually just pass the formula inside these quotation marks.
- 02:15 So if we wanted the sum, Of,
- 02:20 and let's take a look at our widgets again.
- 02:23 So say if we want to B2 to B6, we would just pass
- 02:29 B2 to B6, just like you would normally.
- 02:33 So if we save this and before we run this, I want to just very quickly show you,
- 02:38 if we were doing this in the Excel spreadsheet,
- 02:41 it would be sum and then we would just highlight all of these.
- 02:44 Now, if we look right here, this is the exact formula, right?
- 02:48 Sum in fact, I can copy this, if we head back over here and
- 02:52 paste it in, you'll notice it's the same exact thing, right?
- 02:57 So we're just, whatever formula you want to use in your Excel spreadsheet,
- 03:00 you can just pass it in with these variables.
- 03:02 So, okay, let's go ahead and save this and run it, And
- 03:06 now if we open up our widgets spreadsheet,
- 03:09 we see the formula has been passed and the output is 2300, and that works.
- 03:15 If we wanted to get really fancy we could put something in A8,
- 03:21 right, though we could just copy this, and
- 03:26 let's say, A8, it's a Total, right?
- 03:30 We save this and run it, and then open up our widgets2 spreadsheet,
- 03:35 we see it says Total, and we have the Total here.
- 03:38 So, really simple way to pass any sort of formula that you want into your
- 03:42 spreadsheet using Python.
- 03:43 So in the next video, we're going to look at grabbing cell ranges.
Lesson notes are only available for subscribers.