Thread: Auto-fill a column without dragging cell down

I feel daft for having to ask this, but is it possible to automatically have a formula fill a column?

Each day I have to open a file containing 20,000ish records and need to sort it in Excel (the file changes daily). I have a formula (thanks to Bryan) that will sort my file how I want it but it needs to be populated to all the rows in a table. The only way I know to do this is to put the mouse on the little square thing in the bottom right of the top cell and drag it down so that it auto-fills (and changes the row references). Is there a way of pressing a couple of buttons and having it fill down until it finds no more data in a row (or even better no more data at all as there are blank rows every 1500 records in my data)?

Originally Posted by tas666

I feel daft for having to ask this, but is it possible to automatically have a formula fill a column?

Each day I have to open a file containing 20,000ish records and need to sort it in Excel (the file changes daily). I have a formula (thanks to Bryan) that will sort my file how I want it but it needs to be populated to all the rows in a table. The only way I know to do this is to put the mouse on the little square thing in the bottom right of the top cell and drag it down so that it auto-fills (and changes the row references). Is there a way of pressing a couple of buttons and having it fill down until it finds no more data in a row (or even better no more data at all as there are blank rows every 1500 records in my data)?

Decide on the range for the formula, and select that range (ie, type A1:A999 in the Name box and press enter), enter the formula as though for the first cell of the range, and CTRL + Enter

---

Originally Posted by tas666

I feel daft for having to ask this, but is it possible to automatically have a formula fill a column?

Each day I have to open a file containing 20,000ish records and need to sort it in Excel (the file changes daily). I have a formula (thanks to Bryan) that will sort my file how I want it but it needs to be populated to all the rows in a table. The only way I know to do this is to put the mouse on the little square thing in the bottom right of the top cell and drag it down so that it auto-fills (and changes the row references). Is there a way of pressing a couple of buttons and having it fill down until it finds no more data in a row (or even better no more data at all as there are blank rows every 1500 records in my data)?

after entering formula in one cell, copy that cell and press Ctrl+down arrow, it will select all blank cells plus a cell from which data starts, keeping Ctrl key pressed press up arrow once and then press Ctrl+V to paste that formula.

Regards.