Change multiple formulas in Google Sheets

Power Tools provides you with various utilities to speed up most of your daily tasks. The Formulas tool will help you change the formula type, get values instead of the formulas in the range, or perform quick calculations for all columns or rows.

Before you start

The Formulas tool also processes cells in filtered & hidden rows & columns.

How to convert formulas and auto apply functions to all selected columns

  1. Go to Extensions > Power Tools > Start in the Google Sheets menu:
    Start Power Tools.
  2. Click on the Formulas icon on the add-on sidebar:
    Click the Formulas icon on the Power Tools sidebar.
  3. Select the range of cells you want to work within and pick one of the following options:
    Convert relative cell references to absolute.

    • Convert relative to absolute
      Select this radio button to change the type of formulas in the selected range. The drop-down list lets you choose between three kinds of absolute references to convert formulas to:

      • Absolute cell reference — $A$1
      • Absolute column reference — $A1
      • Absolute row reference — A$1
    • Convert absolute to relative
      Choose this option to convert absolute references in the formulas to relative.
    • Convert formulas to values
      Get the calculation results or values instead of the formulas used in the selected range.
    • AutoSum
      This one will automatically add formulas under each selected column with numbers.

      Choose cells under all columns where you want to insert totaling formulas and pick the necessary function from the list.

      Tip. If you select a blank column to the right of your table with numbers, the tool will calculate all values in each row.
      Tip. The add-on offers the most common functions to choose from: Sum, Count, Counta, Average, Averagea, Min, Max, Product, Mode, Stdev, Var, Median.
    • Modify all selected formulas
      Pick this option to transform your calculations but keep all formulas where they are.

      Simply enter additional calculations before or after the formula in the edit field, where %formula% stands for your current formulas in the selected range:
      Multiply all formulas in the selected range by 1.5.

Click Run to change all formulas in the selected range at once according to the option you select.

Responses

Registrar says:
July 29, 2022 at 9:48 pm

I'm trying to add a formula part to many formulas. An example of one the many that need to be changed is:
=COUNTIFS($AG2:$AG,">=0",$AG2:$AG,"=0",$AG2:$AG,"=0",$AG2:$AG,"<6"))$Q$2:Q,"Yes"

Ekaterina Pechyonkina (Ablebits Team) says:
July 30, 2022 at 3:45 pm

Hello Registrar,

Thank you for contacting us. For us to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with several sheets: a copy of your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
Note. The result sheet is of great importance as it gives us a better understanding than any text description.
Thank you.

Andrew Serafim says:
January 28, 2020 at 5:55 pm

So I have a large range of cell reference formulas and I am trying to convert them all at the same time from relative to absolute.

When I select them all and then use the relative to absolute function none of them actually are being converted to absolute references.

Any help or explanation would be appreciated, otherwise I have to go in manually and adjust them all, which will take forever.

Ekaterina Pechyonkina (Ablebits Team) says:
January 29, 2020 at 7:48 am

Hi Andrew,

Thank you for contacting us. One of our agents sent you a message with the details. Please check your Inbox. Thank you.

Calder Moon says:
November 12, 2019 at 7:09 pm

Hi, so I am trying to convert *part* of the formula to absolute, but keep the rest relative.

The Equation i am working with it =IMSUM(IMPRODUCT(B2,B2),B2), and I want to convert the last B2 to absolute, without converting the other B2s. but the convert tool converts the whole formula. is there a way around this?

Katerina Bespalaya (Ablebits Team) says:
November 13, 2019 at 3:23 pm

Hi Calder,

Unfortunately, our tool can't convert only one of the cell references in your formula to absolute.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.