number

Excel: How to Count the Number of Rows in a Table



Sharing buttons:

hi this is Mike with the second tutorial

in a four part series about counting

here I have a list of tutorials on the

excel trainer website and I'd like to

know how many tutorials I have now in

the previous video I showed you how to

use the count a function and the problem

with that function is that it doesn't

update as the number of rows updates so

as I add a new tutorial what I would do

is insert a new row at Row 3 because I

always want the newest tutorial to be at

the top

I then put the name of the tutorial in

here and I would want it to update the

result to show me that new tutorial and

as I said the count a function won't do

that if you're not familiar with the

count a function go and have a look at

the first tutorial in this little series

to help solve this problem what I'm

going to do is convert this range which

goes from A to 2d 81 I'm going to

convert this to a table to do that in

this particular version of Excel and

this is Excel 2013 I go to the insert

tab well I first of all make sure that

my cursor is somewhere within the range

that I want to convert and then go to

insert and choose table and what it does

is it looks at where my cursor is it

looks at all the cells that surround my

cursor and it works out what it thinks

is the range that I want to use and it's

it selected a 2 to D 81 which is correct

if for some reason that was incorrect

I can just go and edit these create

table box here my table does have

headers because I want it to treat the

data on Row 2 as headings so I click on

OK and what it's done is it's converted

that range to a table now in Excel a

table

is simply a block of data that's treated

as an independent unit I'm not going to

discuss all the benefits of a table here

but you can see that as soon as I create

the table what Excel does is it formats

it by making the headings

then every alternate row is shaded in a

different shade different color and it

also adds filter drop-down arrows to the

title row row one is still blank now if

I do want to change the style I've got

all these styles here that I can choose

so if I wanted a different color scheme

I can I can choose a different color

scheme let's let's go for excel green I

can turn off the filter arrows if I want

to and I can also rename the table every

table is given a name and this table has

given the name table too now that's

because there was at some point in this

file a table one so I'm going to rename

this table and I'm going to call it

tutorials so anytime my cursor is in any

cell in this table it will give me the

name of the table up here as long as I'm

on the design tab if I move outside the

table the design tab disappears I want

to put into a1 a formula that will tell

me how many rows are in this table

because that equates to how many

tutorials I have now as i said my

tutorials go down from Row 3 to row 81

start off with an equal sign and I'm

going to use the function Rose which

counts the number of rows within a given

reference or array which is basically a

range or a block but instead of

specifying a range I'm just going to

space

by the name of the table tutorials

closed brackets and enter and it comes

up with 79 which is the correct answer

now let's go and insert a row at Row 3

and now it shows me 80 that's the only

downside in that it's counting the

number of rows in the table

not counting the number of cells that

have something in but I'm going to

assume that every row because it's to do

with a tutorial I don't want any blank

cells in there so my new tutorial going

to change that so instead of it just

saying 80 it says 80 tutorials and to do

that I'll click up in the formula bar

type an ampersand because I want to

combine elements together type in a

speech mark or quote mark whatever you

want to call it space and then the word

tutorials now it moves it to the left

because it is text it's no longer a

number but you can see what it's doing

it's saying tell me how many rows there

are in the table called tutorials and

join that to the word tutorials I've

been careful to make sure there's a

space after the opening speech mark but

before the tea and that's what gives me

the space character there between the 80

and the tutorials I'll scroll down to

the bottom of this table and scroll

across and because this is a table I get

a little marker the bottom right hand

corner of the bottom cell and so hover

my mouse over it mouse pointer becomes a

double-headed arrow

then I can just drag my mouse down and

I'll extend this table over two

additional rows so the table now goes to

row 84 let us move back to the top move

back to column a and we've now got 82

tutorials of course there aren't 82

tutorials but there are 82 rows in this

table so now I know how many tutorials I

have and whenever I add another one the

formula updates what I want to do now is

count how many videos I have as you can

see not every tutorial includes a video

but I'll show you how to do that in the

next tutorial