Excel Fill Series

A consultant showed me a neat Excel feature today: filling rows or columns using a rule.

You might be familiar with the auto-fill feature where you enter part of a series, e.g. 1, 2, 3, into a sequence of cells, select the cells, then use the mouse pointer to grab a corner of the selection to extend the series. But if you want to extend dates monthly, e.g. starting with 1-Jan-07, 1-Feb-07, 1-Mar-07, you would end up with a repeating series of the same dates.

Excel has a function that lets you choose different rules for filling a series of cells. To create a monthly series in a row, enter a start date in a cell, select a group of cells to fill, then choose the Edit / Fill / Series to display the Series dialog. In this dialog, select the Rows radio button, then the Date radio button, then the Month radio button, then press the OK button.

If you want to create a quarterly series, repeat the steps above, but this time enter 3 in the Step value field. Now you will get a series such 1-Jan-07, 1-Apr-07, 1-Jul-07, 1-Oct-07, 1-Jan-08, 1-Apr-08, 1-Jul-08.