Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        Apply a formula to a column in Google Sheets

        Learn how to insert a formula in Google Sheets for an entire column with ARRAYFORMULA. Keep blanks empty, preserve zeros, and check new rows automatically.

        A person practicing on a laptop in a quiet workspace

        To insert a formula in Google Sheets for an entire column, enter an ARRAYFORMULA in the first result cell and use input ranges that continue down the column. Keep the output cells below the formula empty so the results can expand automatically. Add blank-input checks if you want incomplete rows to stay blank without hiding valid zero results.

        One formula for the whole column

        ARRAYFORMULA lets one formula process a set of values and return results across multiple cells. That set of values is called an array. For an order sheet, one formula can multiply each row's units by that row's unit price, producing a separate order total for every complete row.

        The example uses Google Sheets in a browser with a US locale and commas between function arguments. You'll enter the formula in C2, below the heading, rather than in C1. The input ranges start at row 2 and continue through the sheet's current rows.

        • Use an expanding array formula when every row follows the same calculation.
        • Use copied formulas when individual rows need different calculations or manual replacements.
        • Use SUM when you want to combine values into one total, rather than return one result per row.

        A copyable order calculation

        The order calculation multiplies units by unit price only when both inputs are present. A numeric zero counts as a present value, so an order with zero units should display zero rather than disappear.

        Open a blank sheet and follow the complete exercise below. Copy only the tab-separated data into A1, keeping the blank row between the zero-unit order and the incomplete order. Before entering the formula, check that the incomplete order's units are in A5.

        Exercise: Calculate an order total for every complete row.
        Platform: Google Sheets in a browser, US locale.
        Rule: Units and unit price must both be present.
        A numeric zero is a present value, not a missing input.
        
        Starting state: Open a blank sheet.
        Paste the following tab-separated data into A1.
        The empty row represents blank row 4.
        
        Units	Unit price	Order total
        2	12	
        0	8	
        		
        3		
        
        Check the starting cells:
        A2: 2    B2: 12
        A3: 0    B3: 8
        A4: empty    B4: empty
        A5: 3    B5: empty
        
        Leave C2:C empty before entering the formula.
        Select C2, enter the formula below, and press Enter:
        =ARRAYFORMULA(IF((A2:A="")+(B2:B=""),"",A2:A*B2:B))
        
        Expected results:
        C1: Order total
        C2: 24   (2 * 12)
        C3: 0    (0 * 8)
        C4: blank because both inputs are missing
        C5: blank because B5 is missing
        
        New-row variation:
        Leave the formula in C2 unchanged.
        Enter 4 in A6 and 7 in B6.
        Expected C6: 28   (4 * 7)
        C2:C5 must stay unchanged.
        
        Recovery check:
        Select C2 and confirm the original formula is present.
        Select C6 and confirm the displayed result is 28.
        Don't enter a separate formula in C6.

        The formula belongs only in C2. Its results occupy the cells below C2, including cells that display blanks. Those blank-looking output cells aren't available for separate manual entries while the expanding formula controls them.

        For another opportunity to work with a column formula, try ARRAYFORMULA: one formula for a whole column.

        Practice what this post teaches

        Blank inputs and valid zeros

        The blank-input checks distinguish missing information from a genuine numeric zero. Without those checks, multiplication can turn empty inputs into zero results, making an incomplete order look like a completed calculation.

        IF chooses between two results: an empty text value, which looks blank, or the multiplication. Each comparison checks whether an input is empty. The plus sign combines the two true-or-false tests numerically: either missing input produces a nonzero condition, which IF treats as true.

        When neither input is missing, both tests are false, so IF returns the multiplication. ARRAYFORMULA applies that logic to matching rows: units from A2 pair with the price from B2, units from A3 pair with the price from B3, and so on.

        • C3 displays 0 because both inputs exist and zero units multiplied by the price equals zero.
        • C4 looks blank because both input cells are empty.
        • C5 looks blank because the units exist but the price is missing.

        The new-row test checks a different property: automatic coverage. After you enter the new inputs in A6 and B6 as specified in the exercise, C6 should display 28 without another formula. Open-ended input ranges include that row even though the row was empty when you entered the original formula.

        Separate formulas for editable rows

        Copied formulas are the alternative when you need to edit individual row calculations independently. Each destination cell gets its own formula, so replacing one row doesn't require replacing the calculation for the entire column.

        To compare the methods without changing the worked exercise, open another blank sheet. Enter a normal row formula in the first result cell, select that cell and its intended destination cells, and choose Edit > Fill > Down.

        Separate-sheet comparison: Copy a formula down.
        Platform: Google Sheets in a browser, US locale.
        Rule: Multiply units by unit price for each complete row.
        
        In another blank sheet, paste into A1:
        Units	Unit price	Order total
        2	12	
        4	7	
        
        Enter in C2:
        =A2*B2
        
        Select C2:C3, then choose Edit > Fill > Down.
        
        Check C2:
        Formula: =A2*B2
        Result: 24
        
        Check C3:
        Formula: =A3*B3
        Result: 28

        The copied formula uses relative references: cell references that move with the formula's destination. Copying the formula down one row changes its references from row 2 to row 3. Copying into a fixed selection doesn't, by itself, cover future rows outside that selection.

        If moving references are unfamiliar, practice Formulas and cell references before using copied formulas in a larger sheet.

        Blocked results and practical limits

        An expansion error usually means something occupies a cell where the array needs to place a result. Read the error message to identify the blocking cell, then inspect that cell rather than deleting the whole output column.

        Move any needed content to another column before clearing a blocker. A cell can look blank while containing a space or a formula that returns empty text. Select the reported cell and inspect the formula bar; visual inspection of the grid isn't enough.

        For a circular dependency error, check that the formula's input ranges don't include its output column. A circular dependency occurs when a calculation depends on its own result. In the worked exercise, the inputs belong in columns A and B, and the output belongs in column C.

        If the formula appears as text instead of calculating, select C2 and check for a leading apostrophe. Remove the apostrophe if present. If the cell uses Plain text formatting, change its number format to Automatic, then re-enter the original formula from the exercise.

        The method assumes numeric inputs; it doesn't validate order data. A space isn't a missing value under these checks, and nonnumeric text can cause a multiplication error. Keep units and prices numeric, and investigate unexpected input values rather than hiding every error.

        Open-ended ranges are convenient, but many formulas processing whole columns can make a large sheet slower. Bounded ranges reduce the cells being processed, but you'll need to extend those ranges deliberately when your data grows. For the worked exercise, keep the open-ended ranges so the new-row check continues to work.

        Common questions

        How do I apply a formula to new rows automatically in Google Sheets? Enter an ARRAYFORMULA in the first result cell and use open-ended input ranges. Keep the output range clear so results can expand into later rows without copying another formula.

        Why does ARRAYFORMULA show zeros in otherwise blank rows? Multiplication can treat empty inputs as zero. Check the input cells for missing values before multiplying, rather than hiding all zero results, because some zeros are valid.

        Why does Google Sheets say an array result couldn't expand? A value, space, or formula may be blocking the output range. Read the error message, inspect the named cell, and move needed content elsewhere before clearing the blocker.

        Can I edit one result inside an ARRAYFORMULA column? You can't replace an individual result while the expanding formula controls that cell. Change the source inputs, adjust the controlling formula, or use separate copied formulas when row-level overrides are necessary.

        How can I display formulas instead of results in Google Sheets? Choose View > Show > Formulas to display formulas in the sheet. To inspect only the controlling array formula, select C2 in the worked exercise and read the formula bar; the expanded results below C2 aren't separate formulas.

        Practice what this post teaches

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.