Excel: mixed reference - how it works

The Excel spreadsheet program is an integral part of every office. In this article you will learn what a "mixed reference" is and how you can use this function.

Mixed reference in Excel - what is it?

• A function in Excel can refer to one or more cells. A distinction must be made between absolute reference, relative reference and mixed reference.
• With relative reference, the cell that the function refers to changes when it is dragged to other fields. The absolute reference is the exact opposite: Even if a formula is dragged into another cell, it always refers to the original cell. A mixed reference, on the other hand, is a middle ground between the two variants.

Use mixed reference in Excel - that's how it's done

In short, you need the mixed reference if you include two cells in your calculation, but only one of them should change when the formula is dragged onto a new line. This sounds a bit complicated at first, but can be explained using a simple example:

• Let's say you work in a company and want to calculate how much bonus each employee gets. The bonus rate is shown in the screenshot on the right in cell B3, the employees should receive this share of sales as a bonus.
• The calculation of the bonuses is "sales times bonus rate". To avoid having to re-enter this formula every time, you can write the invoice on the first line and then pull it down at the bottom right corner.
• However, Excel automatically adjusts the cells in the formula to the current row. This is helpful for the field with sales, as this is different on every line. However, the bonus rate is always in the same cell. Therefore mark the field with the bonus rate in your formula and press [F4]. So the field is set absolutely. This means that the formula will always refer to this cell. The mixed reference is now created.

In the next practical tip you will learn how to calculate times in Excel.