Mixed references can be used to set up formulas that can be copied across rows or columns without the need for manual editing. In some cases (3rd example above) they can be used to create a reference that will expand when copied.
Mixed references are a common feature in well-designed worksheets. They are harder to set up, but they make formulas much easier to enter. In addition, they significantly reduce errors since they allow the same formula to be copied to many cells without manual edits.
In the example shown the formula in E5 is:
This formula is carefully constructed with two mixed references so that it can be copied across the range E5:G7 without manual changes. The reference to $C5 has the column locked to make sure the formula continues to pick up price from column C as it's copied. The reference to E$4 has the row locked so that as the formula is copied down from row 5 to row 7, the formula will continue to pick up the percentage value in row 4.
A relative reference in Excel is a pointer to a cell or range of cells. For example, a relative reference to cell A1 looks like this: = A1 A relative addresses will change when copied to other location in a worksheet because it describes the "offset...
An absolute reference in Excel refers to a reference that is "locked" so that rows and columns won't change when copied. Unlike a relative reference , an absolute reference refers to an actual fixed location on a worksheet. To create an absolute...
An expanding reference (or expanding range) in Excel defines a range that expands as a formula is copied down or across cells. This is done by "mixing" absolute and relative references – making the first cell an absolute reference and the last cell...
In this video, we take a speed run through 23 tips you can use to save time with Excel formulas today. We try hard to make our videos quick and to the point, so this is a rapid-fire list, with more than 20 tips in about 10 minutes. We trust that you can rewind the video as needed :)