Excel mixed cell references such as =A$1 and =$A1 is one of the most misunderstood features of Excel. While this is a complex subject, this lesson clearly and simply explains how to use mixed cell references in the context of a real-world business problem (creating a multiple-currency pricelist).

As with all video lessons from The Smart Method®, there's a sample file download link at the beginning of the video to enable you to work along with the lesson.

⌚ Timestamps
0:00​ Sample file download instructions, download link, and discussion of sample file.
0:10 Introduction warning that this subject can be confusing for many students.
1:37 Sample worksheet for this lesson explained (and the problem this lesson will solve).
2:05 Enter a simple (relative reference) formula to convert a US Dollar price to GB Pounds.
2:42 Format cells to show only whole numbers.
3:02 Use Formulas-Show Formulas to show formulas in cells rather than values.
3:25 AutoFill the formula created earlier to discover an error caused by the relative reference.
4:01 Convert the formula created earlier to use an absolute reference for the currency exchange rate.
4;59 Autofill the new mixed reference formula revealing an error for EUR and other currencies.
5:28 Adjust the mixed reference formula so that it will AutoFill correctly for all currencies.
8:03 AutoFill the new mixed reference formula both horizontally and vertically.
8:50 Audit the values in each cell to confirm that correct values are being calculated.
9:42 Demonstration of how to use the F4 key to quickly create a mixed cell reference.
11:14 Link to next lesson, course session playlist and to download sample file set.

• 3-13: Understand mixed cell references (A&1, $A1). ( Download)
• MS Excel - Cell Reference ( Download)
• Cell Reference Basics in #Excel ( Download)
• How To Use Relative & Absolute Cell References In Excel ( Download)
• Excel 2019/365: Formulas: Mixed cell references ( Download)
• Mixed Reference in Excel ( Download)
• Absolute & Relative referencing in Excel ($)- understand all versions ( Download)
• 3-12: Understand Excel absolute ($A$1)and relative (A1) cell references ( Download)
• Binary Addition and Subtraction With Negative Numbers, 2's Complements & Signed Magnitude ( Download)
• Change number in date format | Excel formulas | Text Function ( Download)
• Cell References in Excel - Mixed Reference #shorts ( Download)
• How to use mixed cell reference in formula | Microsoft excel intermediate | tutorial 3 2021 ( Download)
• Cell Reference and Its Types in Excel | Lecture 30 | Cell reference in excel ( Download)
• How to use the VLOOKUP function in Excel ( Download)
• What is Cell References and its Types | Easy Excel ( Download)