Filling in an Entire Column in Excel
C. TAYLOR
TECH
Excel 2013's ability to replicate data can save you a considerable amount of tedious labor when filling repetitive values or formulas in a spreadsheet. By using relative references in your formulas, simply copying the formula down the entire column adjusts the replicated formulas to reference cells relative to their positions. Excel also offers a Flash Fill feature that recognizes an entered pattern and fills the column without having to construct a formula at all.
1 Copying Values or Formulas
2 Enter the value or formula or at the top of the column
Enter the value or formula at the top of the column where you need the data copied.
3 Use relative references
Use relative references to enable formulas to automatically change. As an example, the formula "=B1+C1" in cell A1 changes to "=B2+C2" when copied to cell A2. Use absolute references, marked by a dollar sign, to prevent references from changing. In the example, entering "=B2+$C$2" enables the first reference to change, but the second portion of the formula will always reference cell C2.
4 Click the first cell
Click the first cell again to select it.
5 Double-click the cell's fill handle
Double-click the cell's fill handle, indicated by a small square at the lower right corner of the selected cell, to automatically fill data down the entire column. Copying stops when Excel reaches the end of the data column, assuming no gaps in the data exist. Alternatively, click and drag the fill handle to manually select where data is copied. To copy the formula down a long column with multiple gaps, press "Ctrl-C" to copy the cell, hold "Shift" as you click the last cell in the column, and then press "Ctrl-V" to paste the values or formulas.
6 Using Flash Fill
7 Enter the text value
Enter the text value in the first cell of the column. As an example, if you had a list of first and last names in columns B and C, you might enter "John Doe" in cell A1.
8 Enter the second value
Enter the second value in the next cell of the column. As an example, you might enter "Jane Smith." Excel automatically suggests the data for the rest of the column.
9 Enter to accept Excel's
Press "Enter" to accept Excel's suggestion. Copying stops when Excel reaches the end of the data series or a gap appears in the sequence. If you accidentally reject the suggestion, select the second cell again, and then click "Home," "Fill" and "Flash Fill." The Fill option is located in the Editing group and has a square icon with a down arrow inside.
- To copy data down all available cells in the column, select the first cell, press "Ctrl-C," click the column header to select the entire column, and then press "Ctrl-V." However, doing so forces Excel to replicate a massive number of calculations, so expect heavy delays when using complex formulas.
Related Articles
