This was inspired by a friend who asked about automatically populating a range by dragging the formula–based on numbers at the top and along the left side of the range. It’s a bit of a logic puzzle in using relative/absolute cell references, and here’s what I provided: a quick video tutorial in addition to his spreadsheet.*
Have a look and provide feedback. I can envision doing more of these but I don’t want DataScopic.net to become another how-to site filled with tips. Those sites are quite valuable and for me to get in that milieu, it’d just be redundant.
Details about the video:
- I start by multiplying 6 x $15 and dragging the formula. The results are crazy. 55 x $15≠ $47M That’s the result we get from using relative cell references.
- Around 2:20 in the video, after making the absolute cell references, I check to see if the answers make sense. Yes. 80 x $65 = $5200 (ALWAYS check to ensure the work is correct).
- 4:45 is just a fun feature of Excel: inserting images.
*NOTE: this isn’t his actual data. I made this generic example to keep his data private.