# How to Extend Column Equations in Excel

Microsoft Excel spreadsheets frequently use similar formulas along columns. For example, you might need to keep a running total or tabulate a list of product inventories. Repetitively typing each formula quickly becomes tedious, but Excel offers several solutions to quickly extend column formulas to a selected range of cells.

## Absolute and Relative References

Formatting a cell with absolute or relative references only has an effect when you copy the cell's formula. Excel automatically adjusts relative cell references to reflect the copied formula's new position. For example, if cell C1 multiplied cells A1 and B1, the formula would subsequently multiply A2 by B2 when copied to cell C2. To prevent this change, format the formula with absolute references, which are designated by dollar signs. In the prior example, the formula "=A1*B1" would be formatted as "=$A$1*$B$1."

## Copying Formulas

Excel offers two effective ways to copy existing cells and their formulas. By clicking and dragging the lower-right corner of a cell, you automatically extend the formula to all the cells you highlight. If you need to copy the formula to several thousand cells, this method might be frustrating. Instead, you could copy the cell, hold "Shift" and then click the last cell in the range. When you elect to paste the copied cell, it will extend from the original cell to the last cell in the range.

## Paste Special

Right-clicking a selected range gives you the option to select Paste Special. This feature gives you advanced control over pasting selected cells, such as electing to only paste results, absent any formulas. When you choose to paste formulas, Excel still alters relative cell references to reflect their new positions, but absolute references remain static. Alternatively, choosing Formulas and Number Formats ensures the formulas are pasted and the results use the source cell's number format.

## Arrays

Arrays let you enter formulas into a range of cells simultaneously, such as selecting cells C1 through C20 and then entering a formula in the top formula bar. To format the formula as an array, press "Ctrl," "Shift" and "Enter" simultaneously after finishing the formula. By default, array formulas don't change to reflect the copied position unless you specify this by entering a range for a single variable. For example, highlighting C1 through C20 and entering the formula "=A1*B1" (without quotes) as an array enters the same formula in all highlighted cells, whereas entering "=A1:A20*B1:B20" multiplies columns A and B in each respective row. An added benefit of arrays is you can't accidentally delete a formula from a single cell contained in an array; instead, you would have to delete the entire array.

References

Writer Bio

C. Taylor embarked on a professional writing career in 2009 and frequently writes about technology, science, business, finance, martial arts and the great outdoors. He writes for both online and offline publications, including the Journal of Asian Martial Arts, Samsung, Radio Shack, Motley Fool, Chron, Synonym and more. He received a Master of Science degree in wildlife biology from Clemson University and a Bachelor of Arts in biological sciences at College of Charleston. He also holds minors in statistics, physics and visual arts.