Excel tips #1

If you have a list of items and want to turn them into a numbered list, there are two options, depending on what you plan to do with the data.  Especially if you are going to be sorting this data in the future.  

First – if you want the numbers to stay associated with the row that they start with, no matter how you sort the list (insert picture here) then you want to start at the top, put 1, 2, 3 in the column that will hold the data, highlight those three cells and then drag the black dot in the corner of the selection down to auto-fill the cells with the sequential numbers: 

Fullscreen_capture_132011_100938_am

Since these numbers are “hard coded” in the cells, when you sort your list, they will always stay with the row they are currently associated with.  In essence, they become an index, or a unique identifier for the row, no matter how they are sorted.  This gives you an easy way to sort by the numbered row to get all your items back into their original order.

Second – if you want the list to be sequential no matter what method you use to sort them (1 is always the first item, 2 always follows, etc.) then you’ll want to use a formula to create it.  In the cell for the first item in the list put “=<cell above>+1″. Then you can either do the “grab the black box in the bottom right corner” method above to copy down the formula in all of the cells, or you can highlight all the cells and hit “ctrl-d” which will copy down whatever is in the top cell to all highlighted cells:
Fullscreen_capture_132011_100850_am
Look for an article on Excel keyboard shortcuts (like ctrl-d, and its super useful cousin – ctrl-r) in an upcoming post.  

Do you have any excel tips or tricks?  Post them in the comments!
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s