Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Excel 2010, how can I extend a formula down (updating cell references) and avoid overwriting the background and fonts of the cells below it?

For example, if I have the following Excel spreadsheet

Example Spreadsheet

How could I extend the formula in $B$1 down through rows 2, 3, and 4 without changing their background color to light blue, and making sure that the reference to A1 in the formula gets updated to A2, A3 and so on?

share|improve this question
up vote 2 down vote accepted

Well, there is indeed another option:

enter image description here

And when you pick 'Fill without Formatting', your formulas will be copied without the background being copied over.

[Note for formulas, you won't get "Fill Series", I just got the picture from google =P]

I still prefer using the paste formulas only, since I can easily put it in the quick access toolbar and use Alt + [Some number] as shortcut.

share|improve this answer
  • Right click on the cell containing the formula (in this case, B1) and select Copy
  • Select the range you want to extend the formula to (in this case, $B$2:$B$4)
  • Right click on the selected range, and click on Formula option under Paste Options

    Paste Options

Note: this is just the way I found to do it. If anyone knows a better/easier way, please feel free to post it as an answer, and I will accept your answer.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .