Excel Extending Equations

So I have an excel table that is multiply 1 value against several other values. It looks like this:

So I want the equations inside cells C14 to F14 to be B14*C5, B14*C6, B14*C7, B14*C8 respectively. So I can obviously do that manually but I want to learn the faster way. So I know I should use absolute reference for B14, so I can input `=\$B\$14*C5` for cell C14.

But then when I do the CTRL extend method where you put the cursor on the bottom right corner of the cell and hold CTRL while you extend the cells. The problem is since I am extending the equation in B14 horizontally to F14, it is incrementing the equation horizontally. So the equation in D14 becomes `=\$B\$14*D5` instead of `=\$B\$14*C6`.

So how exactly do I increment the equation downwards while I extend the equation horizontally?

There is an easy way to do this. Enter

= \$D\$5 * \$B13 | = \$D\$6 * \$B13 | = \$D\$7 * \$B13 | = \$D\$8 * \$B13

In the top 4 cells. Select the 4 cells and copy into the rows you want, in this case 14 and 15.

Make life easy by putting the exchange rates in a row instead of a column to match the section below that and then it will no longer be an issue.

``````exchange rates
British Pound | E.U Euro | Swiss Franc | Norwegian Krona
0.067         | 0.897    | 1.34        | 7.34
``````
• This would provide a much more coherent layout when comparing the two tables, e.g. Euro rate would be directly above Euro values. Jun 2, 2014 at 3:44
• I would also convert the ranges into Tables (Insert / Table) as these will automatically copy formulas for you and will use more robust references. Jun 2, 2014 at 3:45

Auto-fill can't do what you want. Use `VLOOKUP` with the correct mixture of absolute and non-absolute cell references and then auto-fill down and/or right will work nicely.

So in the slightly modified example below, cell C13 would contain:

``````=VLOOKUP(C\$12,\$B\$5:\$D\$8,3,0)*\$B13
``````

How does this work?

Each cell in the yellow area references the country name in the header, then does a VLOOKUP on the country in the exchange reate table, taking the value in the third column, then the usual arithmetic.

Auto-fill works because the country header is row-fixed, the USD currency amount is column-fixed and the exchange rate table is row- and column-fixed in the formula.