Create Mixed References

Creating Mixed References

If you’ve used them before or watched our video on Absolute References, you know that you can use them to tell Excel to repeatedly use a specific cell in a series of pasted formulas or formulas copied from cell to cell. This allows you to apply, for example, a sales tax percentage, stored in a single cell, and apply it to every item purchased, as shown in this example:

To calculate the tax for each line item purchased on this invoice, I’m going to make cell G2, where the sales tax percentage is stored, an absolute reference – I do this by pressing the F4 key on my keyboard when I add that cell to the formula.

As you can see, after I pasted the formula down the column for the remaining line items, each iteration of the formula has gone back to cell G2, despite the formulas updating to use the next line item down, one purchased item at a time.

Now, to show you how to use what’s known as a Mixed Reference, we have a worksheet with quantity discounts for a handful of products. We’ll use Mixed References in two different ways – once to force the calculations to refer to cells in a particular column, and once to make them reference values in a particular row.

Let’s demonstrate:

For the first item, to calculate a 5% discount for purchasing from 10 to 20 items, I type:

= D5 and type a dollar sign in front of the D, to make the column letter absolute. This is our first mixed reference because we’re not making the row number absolute – we only want to stay in column D to get the unit price, as we’ll be filling horizontally – staying in the same row – after completing the calculation.

Then I type a minus sign.

Then I type an opening parenthesis, and click in cell D5 again, adding a dollar sign in front of the D once more.

Next, I type an asterisk, to multiply D5 by the 5% discount in cell E4.

To make the row number in cell E4 absolute, so that we stay in row 4 for all 3 discount levels, I type a dollar sign in front of the 4 in cell E4. I could also press the F4 key twice, which cycles from a full absolute reference to a mixed one.

This mixed reference allows the formula to be pasted horizontally and apply each of the three discounts, one after the other, to the unit price.

Then I close the parentheses and press enter. The price with a 5% discount is displayed.

Now to use this mixed reference for the rest of the first item’s pricing, I drag the fill handle across the row, through cells F5 and G5, and the 10% and 20% discounts are calculated, based on the unit price in cell D5 – as shown as I display each of the pasted formulas.

To now calculate all 3 prices for each product, I select all of the prices in cells E5, F5, and G5, and use the fill handle to drag down through row 11.

When I release the mouse, all of the prices for all 7 products are calculated.

As I double-click on them to demonstrate how the mixed references worked, you can see by the colored shading in the cells that each of the calculations are correct – each unit price was multiplied by each of the 3 discounts, row by row, across all three columns – thanks to mixed references.