The ELBoS Blog: Microsoft Office - EXCEL

Have you ever needed to number consectutively in Excel, insert the days of the week or month? This post will teach you a WONDERFUL short cut to keep you from having to type all that mumbo jumbo every time you need it.

Open up a new EXCEL document.

Type the word Monday in cell A1 and hit enter (notice if you don't capitalize it, it automatically does it for you.)

In the lower right hand corner of the selected cell, there's a black square - this is called the Fill Handle.

Hover over the Fill Handle and your mouse pointer will change into a plus sign as shown

Now click your mouse button down and hold as you drag down the column. As you are dragging down, you will see a box pop up next to your mouse pointer that shows you what you will end at when you release your mouse button on that cell.

You are not doing it correctly if it looks like this

Difference being the WRONG way has a dark black bolded line around the cells as the CORRECT way has a lighter, transparent lined-like box while selecting.

The cells get filled in will the consecutive info without having to type it all in.

If you pull all the way down to row 13, it will stop on Saturday.

Do you notice now there's a little box in the lower right corner that looks like this?

This is the Auto Fill Options box**.

**NOTE - the Auto Fill Options will NOT appear unless you have selected for it to show. In Excel 2007, go to the top left corner and select the Office Button. Select Excel Options at the very bottom of the menu screen that pops up (as shown below)

Then you are going to select the Advanced tab - you will see a section titled Cut, Copy and Paste. Then check mark Show Paste Options Button. Select OK. (In earlier version I believe you go to Tools, Options, Edit Tab then check mark Show Past Options button)

The Auto Fill Options box will not appear though until you drag down to at least one cell as so

The list of options varies according to the type of data you are working with. In this example you will see the following:

The Fill Series is already pre-selected in our example and will do the fill in of all the days of the week consecutively.

The main menu will always at least show:

Copy Cells will change every cell into whatever the first cell of the selected group is - which in this case would be Monday.
Fill Formating Only will take any formatting you did to the first cell and copy into all the following cells you have selected (will not copy text)
Fill Without Formatting will only fill all the selected cells with text copying the exact text in the first cell into the following selected cells.

Lets say you did not want to pull all the way down to row 13 , ending on Saturday.

You can delete each one individually
Select all the ones you want to delete (either by dragging a box around them all or click selecting by holding down the CTRL key and clicking sperately) and hit the delete key
OR you can reverse the drag down you did by selecting on the Fill Handle in the lower left cornet of the cell again and this time dragging upwards.

Now if you are wanting to do any kind of numbering order, you cannot just start with one number. You have to fill in at least 2 to 3 rows so Excel knows the kind of incrementing you are wanting to do. For example.

If you are wanting to number 1, 2, 3, 4, 5 and so on, you need to start with 1 in the first cell and 2 in the next cell below it as so:

Select BOTH cells then use the Fill Handle and you will get this outcome

If you only type 1 and NOT 2, you will ONLY get this outcome

Which this is good if you want to repeat one cell over and over again.
This can be done by selecting the key combination of Control [CTRL] plus the D key.

Select the cell underneath the cell you want to copy as shown then hit the key combination
OR by selecting the cell with the text in it and including all the cells below it you are wanting it to copy to (this is not by using the Fill Handle method)