I want a networkdays formula to run the full length of a very large worksheet.?
There must be a quicker way than entering the formula in the first cell then dragging it 30 or so thousand rows to the end of my worksheet.
Any suggestions?
There are navigation shortcuts in Excel/Office…
Tip: If you double click in the bottom right corner of a cell, Excel will autofill the column provided that the cell immediately to the left is not blank.
The solution to your problem…
copy the formula you wish to replicate…
select the cells you want to paste it into…
press Enter.
TO SELECT THE CELLS…
left click in the first cell you wish to paste…
Ctrl and the down arrow will take you to the end of the column (assuming the column is blank).
Ctrl Shift and the down arrow will select the intervening cells.
Ctrl and End would take you to the bottom right extent of your sheet (what you’ve used).
Do it again to get to the bottom of the whole spreadsheet.
Ctrl Shift End will select from where you started down to the end of your sheet, or a second time to the end of the whole sheet.
If you keep Ctrl and Shift depressed, you can move the end of your selected area.
I’ve copied this bit from the link, but there’s more!
Select the entire column.
CTRL+SPACEBAR
Select the entire row.
SHIFT+SPACEBAR
Select the entire worksheet. If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
CTRL+A
With multiple cells selected, select only the active cell.
SHIFT+BACKSPACE
Selects the entire worksheet. If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet. When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet
CTRL+SHIFT+SPACEBAR
Hope this helps!
August 14th, 2009 at 2:00 am
There are navigation shortcuts in Excel/Office…
Tip: If you double click in the bottom right corner of a cell, Excel will autofill the column provided that the cell immediately to the left is not blank.
The solution to your problem…
copy the formula you wish to replicate…
select the cells you want to paste it into…
press Enter.
TO SELECT THE CELLS…
left click in the first cell you wish to paste…
Ctrl and the down arrow will take you to the end of the column (assuming the column is blank).
Ctrl Shift and the down arrow will select the intervening cells.
Ctrl and End would take you to the bottom right extent of your sheet (what you’ve used).
Do it again to get to the bottom of the whole spreadsheet.
Ctrl Shift End will select from where you started down to the end of your sheet, or a second time to the end of the whole sheet.
If you keep Ctrl and Shift depressed, you can move the end of your selected area.
I’ve copied this bit from the link, but there’s more!
Select the entire column.
CTRL+SPACEBAR
Select the entire row.
SHIFT+SPACEBAR
Select the entire worksheet. If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
CTRL+A
With multiple cells selected, select only the active cell.
SHIFT+BACKSPACE
Selects the entire worksheet. If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet. When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet
CTRL+SHIFT+SPACEBAR
Hope this helps!
References :
http://office.microsoft.com/en-us/excel/HP052037811033.aspx
August 14th, 2009 at 2:25 am
If you have a thousand rows or so I would suggest that your spreadsheet is rather on the large size and that you could probably improve on it somehow. However, even with a thousand or so rows, replicating it down takes only a couple of seconds as the mouse gathers speed. Alternatively, try using an absolute reference in one cell so that the others automatically change.
References :