Filling a large range in Excel with a series [Video]

Keyboard and mouse shortcuts

Let’s assume you need to fill a vertical range with all the whole numbers from 1 to 100. There are lots of ways to do this, but I think the keyboard could be the quickest.

Here’s a video of the various techniques.

My preferred solution

In cell A1 type 1 and hold Ctrl down and press Enter – that keeps the active cell on A1.

Press in sequence Alt E I S

That opens the Series dialog. (Its an old school Excel 2003 shortcut)

Then hold down the Alt key and press C and O.

That selects Columns in the dialog and then actives the Stop Value box. Type 100 and press Enter.

Job done.

You could also use the mouse to select and navigate within the Series dialog.

Fill Series

You can also access the Series dialog via the Home ribbon (far right-hand side).

Dates

This technique can also be used for dates. Let’s assume you wanted to enter all the dates for 2018.

In Cell A1 type 1/1/2018 – press Ctrl + Enter.

Press in sequence Alt E I S.

Then hold down the Alt key and press C and O.

Type 31/12/18 and press Enter – job done.

Using the mouse

(Hat tip to Wyn Hopkins MVP of Access Analytic for recently sharing this on LinkedIn)

There is a mouse trick to access the Series dialog.

This requires dragging the Fill Handle (the small cross at bottom right corner of the cell) with the RIGHT mouse button.

Type 1 in cell and press Ctrl + Enter.

Use the right mouse button to drag the Fill Handle down to A2 and then back up to A1 and release the mouse.

A shortcut menu menu appears and Series is the last option.

Follow the same steps as mentioned above to create to series to 100 with the Series dialog.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.