Writing Draggable Formulas
The Importance of Patterns
In this class, you will learn to tackle interesting business problems that have mathematical foundations. Math is powerful because it helps us notice and capitalize on patterns. Here's a familiar example: if one sandwich costs $8, how much do you pay for four sandwiches? We can answer that question by recognizing the pattern (1 for $8, 2 for $16, 3 for $24, etc.) and realizing that multiplication is the tool for extending the pattern.
Why the Most Important Character in Excel is the $
It may not be why you think.
To do calculations in spreadsheets, we write formulas. For example, a formula might contain an instruction to add up all the values in a column. Let's say you have 10 columns with expenses, one column for each week. If you want to find the total expenses for each of the 10 weeks, you should be able to write the formula for the first column and "drag" it to the other columns. Dragging (or copying) a formula implies that there is a pattern: you want to do a similar calculation with different values.
Writing formulas so they can be dragged is the key skill for using Excel efficiently. If you are using spreadsheets to solve real problems, you will use this skill every day. It will save you a tremendous amount of time, it will reduce errors in your spreadsheets, and it will greatly expand the set of things you can do efficiently.
So why is $ the most important character in Excel? The $ helps you control which cells are referenced in your formula when you drag it.
I wanted to find a very short video that shows how to use the $. The one below is only a minute and a half and gives a quick and accurate demonstration of the $. The video is a little grainy, both in audio and visually (especially the first ten seconds), but it is nice and short. If you want more lessons on how to use the $, try searching for the term absolute references in Excel. In this context, absolute means that the cell reference doesn't change when you drag the formula.
Exercises: Picking a Product and a Price
This short module gives you a chance to practice writing draggable formulas.
- Download the Excel file here.
- Look at these exercises to see if you get the use of the $ in writing draggable formulas.
- The exercises refer to this video:
Tip: if you aren't going to drag a formula, you don't need to use a $. If you are going to drag it, then for each place it could go (before each column letter and each row number), there are three possibilities: you NEED to use it, you need to NOT use it, or it doesn't matter whether or not you use it.