How to make column number in sequence even after deleting a record?

**kaley99** · 08-06-2020, 05:44 AM

Hello

any help appreciated, i'm not an excel expert use a regular PC win 10 user with the Office 2016 suite.

I know how to autofill a column with numbers in a sequence, ie.

1
2
3
4
5

Lets call this column 'Numbers'. And the next column is 'Books'. I use this to keep a book list. When I give a book away, I delete a row and it is a nuisance updating the numbers each time. They need to be sequential order to show me how many books remain. This is because a number in the 'Books' column is missing each time I delete a book.

How can I autofill a column so that if I delete a row the numbers remain sequential? So for example, if I sell Book 3 and delete this row, I want the 'Number' column to change Book 4 to Book 3 automatically.

I only need the 'Number' to automatically 'renew' itself if that makes sense.

Thanks for any advice.

**maclearyj** · 08-06-2020, 05:53 AM

Hi Kaley. You could put this formula in the cells: =ROW()

If your list of books starts in row 5 for example, make the formula =ROW()-4

However, I'm not clear on why you would want to use the number in the cell. Instead, you could use one of the counting formulas at the top of your list to count how many rows are used. Also, just as another consideration, if you ever take a copy of the list, or want to use it for anything else, you may find that the number changing for a particular book may not be a good thing.

Hope this helps.

John

**rorya** · 08-06-2020, 05:53 AM

You can use the ROW() function (subtract an offset if your data doesn't start in row 1)

**kaley99** · 08-06-2020, 06:09 AM

Hi Peter, thanks

No the 'column' list is soley for knowing how many books I have. No other reason for keeping it.

So I lose a book i delete it, and I if i get another book from somewhere, I will add it to the end of the list. The numbers themselves don't mean anything except that I know by looking at the last one, how many books I have.

**kaley99** · 08-06-2020, 06:12 AM

Thanks Rorya,
The list starts at the top from row 1. Basically, its just a method I use for knowing how many books I have. I add books at the end of the list, and delete a title when i get rid of it. Now when I delete a book, I click the row right at the start of the row in Excel, which highlights the whole row and I then delete the entire row.

Its a very simple list and i'm sure there is a simple solution.

Do I put =ROW() as a formula in all the boxes in a column? Or just in one? Sorry, but i'm a complete newbie on Excel.

**AliGW** · 08-06-2020, 06:13 AM

Why bother with it at all when you can just add a formula at the top of the list?

Assuming that column A contains your book titles:

=COUNTA(A:A)

**kaley99** · 08-06-2020, 06:19 AM

Thanks Ali,
I just tried that and i got an error. I want the first column to have the numbers. Column B has the book titles.

Where exactly do I put this forumla, and do i need to use the autofill and fill all the boxes in the column with it?

Thanks for your help.

**kaley99** · 08-06-2020, 06:43 AM

I've got it now, I understand how ROW() works.

Thank you to all who replied and helped.

**AliGW** · 08-06-2020, 07:06 AM

My point is that you don't need a column of numbers just to know how many books you have!!! You are making extra work for yourself.

If the titles are in column B, this will count them:

=COUNTA(B:B)

Place it in ANY empty cell.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.