Existing Excel - Column and Column Range

Locked lesson.

Upgrade

About this lesson

We can grab an entire column of data from an Excel spreadsheet.

Quick reference

Existing Excel - Column and Column Range

We can grab an entire column of data from an Excel spreadsheet.

When to use

Use this method whenever you want to grab an entire column of data from an Excel spreadsheet.

Instructions

To grab an entire column, just call it.

col_b = ws['B']

That will return a tuple with the cell objects. To access them, you'll need to loop thru that tuple:

for cell in col_b:
     print(cell.value)

You can also grab a range of columns:

col_range = ws['A':'B']

You'll also need to loop through those results to access the data individually.

for cell in col_range:
     for x in cell:
          print(x.value)

Hints & tips

  • One Column: col_b = ws['B']
  • Column Range: col_range = ws['A':'B']
Login to download
  • 00:04 Okay, in this video we want to grab an entire column.
  • 00:07 So in the last video we grabbed a slice using different cell ranges.
  • 00:11 And in fact, we grabbed a column worth of stuff there.
  • 00:14 But if we want to just grab an entire column, and we don't want to go
  • 00:17 through the trouble of creating a slice, we can just call that column.
  • 00:21 And just grab all the information from it.
  • 00:23 So that's what we're going to do in this video.
  • 00:24 So let's come over here and let's create a variable.
  • 00:26 I'm going to call this column b, and we can set this to ws our worksheet,
  • 00:30 and we can just pass in what column we want.
  • 00:33 And you do that with a capital letter.
  • 00:35 So if we save this and let's head over to our worksheet real quick, and
  • 00:39 we could see, column B is just this favorite pizza column.
  • 00:44 And you know columns in Excel generally start with letters.
  • 00:48 So, and these are capitalized.
  • 00:50 So okay, let's head back over here and
  • 00:52 print this really quickly just to see what this is returning.
  • 00:56 So we can print column b.
  • 00:57 Let's save this and run it.
  • 01:00 And you see we're getting a tuple with these Python objects for
  • 01:04 all the cells in that column.
  • 01:07 So we know how to get around this.
  • 01:09 We can do a for loop.
  • 01:10 So let's go for cell in column b, and then let's just print out the cell.
  • 01:16 And as you probably can expect, this will return a list of the Python objects.
  • 01:21 And again, if we want to just grab the value,
  • 01:24 we can then .value this guy, save it.
  • 01:28 Run it again.
  • 01:30 Now we're getting all the values from the cells in that column.
  • 01:32 So very easy way to just grab everything from a column,
  • 01:35 you don't have to worry about slicing and things like that.
  • 01:38 It's just really easy.
  • 01:39 So we can use that to grab one column.
  • 01:42 We can create a range of columns just like we created a cell range in the last video.
  • 01:46 So let's rename this column_range.
  • 01:50 And I'm just making up these variable names.
  • 01:52 You can call them anything you want, but you want them to be descriptive.
  • 01:55 So let's get column A through column B.
  • 01:59 So let's go ahead and print this and see what this looks like.
  • 02:03 That was a good idea to print these things as you go just to see what it's returning.
  • 02:07 So you don't have to memorize all this stuff, just easier that way.
  • 02:10 So let's clear the screen and run this guy.
  • 02:13 And we're getting a whole bunch of stuff.
  • 02:14 Now, if we think this through, we can see right on the outset,
  • 02:18 there's these two tuple brackets, and then at the end here, these two close.
  • 02:23 And we're grabbing two columns.
  • 02:26 So we can sort of expect there to be a couple of different tuples in there.
  • 02:30 So let's go ahead and copy this and bring it back over here.
  • 02:35 And just paste this in so we can sort of make some sense of this.
  • 02:38 So I'm going to go through here.
  • 02:40 There's a tupple.
  • 02:41 And inside of our tupple, let's just sort of break apart the things that
  • 02:46 are separated by commas really quickly.
  • 02:48 Just so we can get a better understanding of what's going on here.
  • 02:54 And so, we'll notice these are row A1 through eight, and
  • 02:59 then we have another tupple.
  • 03:01 And inside of this other tuple is the items from row B, right?
  • 03:07 So we don't have to keep going through here.
  • 03:08 We can sort of get the idea of what's going on here.
  • 03:11 So we've got a tuple and inside of that, there's two more tuples.
  • 03:14 So we need to do some looping if we want to grab all this information.
  • 03:17 So let's delete this stuff.
  • 03:20 And let's go for cell I guess in column_range.
  • 03:28 First off, let's print cell.
  • 03:31 Let's save this and run it.
  • 03:36 And here we're getting, it looks like one less tuple,
  • 03:39 which is sort of what we would expect.
  • 03:42 Right, but inside of this tuple,
  • 03:45 there's still two tuples, this one and this one, right?
  • 03:50 So now we need to loop through it again to break apart each of these.
  • 03:54 So we can do that, we can go inside of here for, and
  • 03:58 I'm just going to call this x in cell.
  • 04:01 Now let's just print out x to see what this is returning, save this and run it.
  • 04:07 And here we're seeing the Python objects of A1 through A8 and
  • 04:11 B1 through B8 which are our columns and cells, okay?
  • 04:15 Now, as always, we can just .value this if we like.
  • 04:20 Save this, let's clear the screen and run this one last time.
  • 04:24 And we get names, John through Jason, favorite pizzas,
  • 04:27 pepperoni through pineapple and that's cool.
  • 04:31 So you can use your Python to loop through these in different ways to mix these
  • 04:34 things together if you want.
  • 04:37 But this is how we can access that data.
  • 04:39 So that's column and column ranges.
  • 04:40 In the next video, we'll look at grabbing rows.

Lesson notes are only available for subscribers.

Gift this course