Shortcut for Do Again in Sheets

You know, I'one thousand pretty confident in my utilise of Google Sheets. I utilise a lot of its advanced features and formulas in my daily work.

But 1 matter was bugging me. Every time I created a formula and so grabbed and dragged (or double-clicked) that little box in the lesser right of the cell to have it repeat, the cells would change.

This ordinarily is slap-up, because information technology moves by a cell each time and makes my life easier.

What if I want to keep a certain cell constant in a formula?

Enter the Absolute Reference.

The accented reference allows you to lock either or both aspects of the jail cell. That is information technology can lock the column and/or therow. This is done by but calculation a "$"before the cavalcade or row. Here is how we do information technology:

• $A$1 –locks both Column and Row
• $A1– locks but the Column
• A$1 – locks just the Row

Awesome!!! Now you can elevate that piffling foursquare wherever you want and whatever part of that cell you locked won't modify while everything else will.

F4 – The Shortcut From the Gods

So, this is all well and good, merely it still means that after I have finished entering the whole formula, I have to go back into the cell and change the cell value with that dollar sign "$".

F4to the rescue. You can change the value alive here by cycling through $A$1, $A1 and A$one before continuing with your formula. While completing a formula simply,

1. Click a cell or range yous want to go a value from and y'all desire to lock or make an Absolute Reference.
2. Printing <F4> until you get the desired Absolute Reference combination.
3. Continue with the residuum of your formula.

Instance – My Fruit Binge

I really love fruit (Okay, I'm ambivalent almost fruit, only I am trying to sell the case here!)

I have a list of fruit that I have eaten in the last 60 minutes. My wife has caught me with a pile of cores and skins scattered effectually me and wants me to count how much this nutrient binge cost me in guild to make me feel bad.

Of course, I immediately run to my laptop and bring up my list of fruit I just ate. I can likewise retrieve how much each particular costs (Yep, the instance is breaking hither, stay with me).

To piece of work out the total costs of what I ate, I volition count how many of each item and multiply that by the costs of each item.

Here is my Google Canvas so far:

Let's plug in the formula to count the total number of Bananas.

Things look good so far. In prison cellG3 I have runCOUNTIFand selected the rangeC2:C13. And I simply desire information technology to count if the cell contains the text "Assistant"which I indicate by prison cellE3. Finally, in cellH3 I multiply the total number of times Banana appears (G3) by the cost of the particular (F3).

The end results:

More examples of COUNTIF

But for a lark, let'south run into how that COUNTIF formula volition piece of work if I elevate it down with that cheeky footling box in the bottom right of the cell.

As y'all tin can see things have gone a flakePear-shaped(nailed it!) here. Nosotros can see 2 pairs in the list in columnC but the formula is only counting 1 in jail cellG7.

Let's accept a expect at the formula inG7.

We tin can encounter here that therange has changed fromC2:C13 toC6:C17. That is non helpful at all. We demand to brand these into anAbsolute Reference. The fruit benchmark also moved, but we wanted information technology to come down to read Pear so that ispear-fict(Non then good that fourth dimension).

Let'south fix this up. We need to change theCOUNTIF range in cellG3to anAbsolute Reference fromC2:C13to $C$ii:$C$13.This is kinda awkward and then let's attempt that <F4> shortcut out and put in the formula once more fresh. To exercise this I did the following steps.

1. entered:  =COUNTIF(
2. selected the rangeC2:c13
3. hit the magical <F4> button one time and it created:$C$ii:$C$13
4. entered a comma:   ,
5. selected the rangeE3
6. closed the bracket: )
7. hit <enter>

Looks practiced. Nosotros'll catch that footling box down the bottom once more and drag (or double click) it downwardly and see if we get that extra pear.

Blast! Information technology worked. We now run across 2 pears counted. Check out that bottom cell. Information technology's lock in the range value and merely changes the fruit benchmark.

Simply to existpear-dantic(Hey! That was okay!) let's see how guilty we should be for our fruit binge.

$38.10? Worth information technology!

~Yagi

Postal service navigation

wardlovid1987.blogspot.com

Source: https://yagisanatode.com/2017/11/01/how-do-i-lock-certain-cells-in-a-formula-in-google-sheets/

Share this post