Lesson 3 – Dragging Formulas and Absolutes (How to efficiently apply one formula to hundreds of cells in a matter of seconds)

Table of Contents

1. Introduction
2. Relative Dragging
3. Absolute Dragging
4. Conclusion

Introduction

So far, we learned how to enter formulas into Google Sheets. However, we only learned how to apply a formula to one single cell, not a whole column of cells. This is why today we will teach you about the dragging feature in Google Sheets. This allows you to drag a formula, and apply it to many different cells. Imagine having to enter a formula 50 times for 50 separate cells. Now, you can do that by just dragging a formula down.

Before starting this article, if you feel unsure about referencing cells, you may want to refresh on that lesson here, as well as our lesson on the Google Sheets Coordinate System.

Relative Dragging

Let’s look at an example, where we could use relative dragging to help us build our spreadsheet much more efficiently. Here we have a spreadsheet, showing the types of pens a store sells, their price, and how many they’ve sold. 

What we want to do is multiply the price, by the quantity bought, to see how much each pen earns the store. By doing so, the store could see which type of pens bring them the most revenue (a useful use of spreadsheets). Let’s go through this in steps.

Step 1: Enter the formula for one row, in the top rightmost cell.

Here we will use the formula skills you learned in Lesson 1.

Step 2: Click enter to submit the formula into the cell, and have the result calculated.

Step 3: Drag your cursor to the bottom right of the cell you just entered, and click and hold the little blue box in the corner. Your cursor should become a crosshair

Step 4: Drag your cursor down for all the cells you want the formula to apply to.

Step 5: Let go of your cursor, and see the formula be applied to the rest of the cells.

As you can see, through these easy and simple steps, you can easily apply a formula to many cells in seconds. How does it work though?

By dragging vertically down, Google Sheets adds one to each cell referenced in a cell. For example, if you enter “=B4” in a cell, and drag it down one row, it will become “=B5.” This is how Google Sheets applies the formulas when dragging down. You can reference the visual below to see how it works from the inside.

Now, what happens if we do not want part of a formula to change when we drag it. For example, if we were to multiply a value by a constant. In order to do this, we would use something called absolutes, to specify what we do not want to change when dragging. 

Absolute Dragging

There are many cases in which we want cells to not change when dragging. For the above example, maybe there is a sales tax that you want to apply to each sale. That sales tax would be constant for every single type of pen. Before going over an example, let’s go over how to make cells stay the same, as well as the separate ways to do this.

Google Sheets has defaulted the “$” to be set as the absolute symbol. Applying this symbol before a cell letter or number will make it stay constant, no matter how you drag it. However, it becomes a bit more complicated. We have made a table below, showing different ways you can put dollar signs, and what their effect is when dragging.

=B4	Letter changes when dragged horizontally, number changes when dragged vertically
=$B4	Letter does not change when dragged horizontally, number still changes
=B$4	Letter changes when dragged horizontally, number does not change when dragged horizontally
=$B$4	Both letter and number stay fixed when dragged

Let us use a variation of the colored pens example, in order to better display this use of absolutes.

In this example, the only difference is that there is a sales tax of 5.0% that should be applied to each purchase. In this case, the 5% is a constant value, that we do not want to change when dragging. This is how the spreadsheet looks. Now, how would we apply the absolutes so that the cell referenced will always be “E1”, and never change? Here is how:

Step 1: Enter the formula you want. Here we are doing the normal price of the pens, added to the sales tax (which is the normal price, times the 5%)

Step 2: Apply the absolutes to the part of the formula you want. We will turn E1 into $E$1, as we do not want that cell to change as we drag it. We do not absolute any other parts of the formula, as we want those parts to change as you drag it

Side note: Referencing the cell as “E$1” would have the same effect, as we are only dragging it vertically

Step 3: Enter and drag the formula down to apply it to all the cells you want

Step 4: Double click any of the cells to see how all the values have changed in the cell, except the portion you absolutted.

Similar to the relative dragging example, reference the visual below to see how the formulas copied over when dragging. As you may notice, all the cells without the dollar signs add by 1, each row they are dragged down. However, the value with the dollar signs stays constant, no matter how much it is dragged.

If you are still confused about dragging, we found some resources that may further help. This link here goes to a thread on the Google support website, discussing absolutes and what they do.

Conclusion

This is one of the most important skills you can learn when it comes to Spreadsheets. By being able to use dragging in a smart and efficient way, you take tedious work that could take hours, to be finished in mere seconds. Hopefully you will be able to use some of these skills you learned today to create some amazing spreadsheets on your own time.

Due to how important of a skill this is, we highly recommend watching our commentated video down below. We hope you learned a lot through today’s lesson and are able to apply the skills learned here to your own spreadsheets. Lastly, as always, happy spreadsheeting! 🙂