How to copy a formula down a column in Google Sheets

By
Anastasia Yasnitska
-
0
1042

First, there was Excel. Then Google created a more concise, technologically advanced, and usable version of spreadsheets, which is now used in a variety of professional fields, including – for printing purposes. Google Sheets works in the cloud, which is very good for users who work long hours and can sometimes forget to save progress.

The interface of Google Sheets reminds Microsoft Excel, but here it’s less overloaded and visually easier to perceive. Moreover, Google Sheets is a completely free app and doesn’t require additional subscriptions. However, to understand what tools there are, where they are and how they work, we still have to. Let’s see briefly what this version of Excel from Google offers in terms of formulas.

What is Google Sheets

Google Spreadsheets is an online tool for creating and editing spreadsheets. Google Spreadsheets can only be accessed online through a browser, but mobile versions have the ability to work without an Internet connection.

With Google Sheets, users can create various lists, perform calculations, use tables as a database, and make charts and graphs from tables. Sheets support formulas that help users perform calculations much faster. Because the service is online-only, information is saved every second. If the user makes a mistake, it can be corrected by rolling back to the correct version.

Moreover, in Google Sheets users can work together, the user just needs to access the table and set the role of the recipient (reader, commenter, or editor). On the page of the document, users can communicate in chat, in a real mode see corrections and additions in the table. Thanks to artificial intelligence, it’s possible to analyze the document in a short time and automatically complete the necessary formulas.

Read Also:

How Google Sheets Formulas work

A formula tells the Sheets app how to deal with numbers and values in a cell or group of cells. Without formulas, spreadsheets are unnecessary in principle. Formula construction includes constants, operators, references, functions, range names, parentheses containing arguments, and other formulas. To set a formula for a cell, you must activate it (place the cursor) and type equals (=). You can also enter an equal sign in the formula line. After entering the formula, press Enter. The result of the calculations will appear in the cell.

Moreover, Google recently introduced a useful feature of Google Sheets – smart formula prompts. It’ll help speed up data analysis. Previously, you had to enter formulas manually, but now when you start typing, you’ll be offered auto-completion (so it’s easy to turn =S into =SUM). But with the new feature, after you enter the = sign under the column of numbers, you’ll be prompted to immediately find their sum or arithmetic mean. If you select the sum formula and enter = in the box below, you’ll be offered only the arithmetic mean, because the system understands that you have already found the sum for these numbers.

This is hardly a technological breakthrough because entering =SUMM is not very difficult. Nevertheless, it will simplify routine tasks and allow you to work faster with simple data arrays. By default, it will be enabled automatically, to disable it you’ll need to uncheck the menu item “Tools” → “Enable formula prompts”.

What about copying a Formula down a column in Google Sheets

So, you need to copy the entire column formula into Google Sheets, but you don’t know how? In fact, there are several ways, and one of the easiest is simply to grab the fill handle and slide down to your last cell. There are other options, though, and now you will learn more about each of them:

  • Highlight the first cell in your column that includes the formula, then select the fill handle (small blue box) in the cell’s bottom-right section. The cursor turns into a crosshair when positioned correctly.
  • Drag the crosshair down to the last desired cell that will use the specified formula. Google Sheets will automatically calculate the correct formula for each row.
  • The above process uses the row formula [=SUM(B3+C3)] to auto-calculate all other selected rows within the column [=SUM(B4+C4)], [=SUM(B5+C5)], etc.

The other option is a simple double-click on the top cell to copy the formula. It’s very simple and fast, that’s how it’s done:

  • Select the first cell in the column, including the formula, then hover over the fill handle in the bottom-right corner. Do not click right away.
  • Double-click the left mouse button while on the fill handle. This process will autogenerate the formula down to the last filled cell in the column.
  • The option will stop inserting formulas when it reaches a blank row down the column. Copy the first cell, paste it into the next filled cell of the column, and replicate the steps above.

With these simple methods, you can copy the column formula into Google Sheets and use it for various tasks. This greatly simplifies the process of working with documents and tables.

How to copy a formula down a column via an Array Formula

And this method may seem more complicated, but it only seems that way. You just need to use the ArrayFormula option and the template formulas to achieve the desired result. Here are the template formulas and their description:

  • The next example uses the “addition” formula (B3+C3), but it adds a range, which goes down to cells B6 and C6. Google Sheets uses (B3+C3) and replicates it down the column (B4+C4), (B5+C5), and (B6+C6).
    • =ArrayFormula(B3:B6+C3:C6)
  • The second example calculates identical totals as the previous formula, except it replaces the “0” in cells with no characters so that it appears empty. The ISBLANK part ignores blank cells, and the characters inserted inside the brackets are what Google Sheets places into the empty cells, which get set as nothing.
    • =ARRAYFORMULA(IF(ISBLANK(B3:B+C3:C),"",IF(B3:B+C3:C=0,"",(B3:B+C3:C))))

It should be mentioned that with this method, all your cells cannot be deleted until you clear the ArrayFormaula in the topmost cell.

Read Also:

Is it possible to copy a Google Sheet in order to apply new formulas

In case you need to test some calculations and formulas on an existing table in Google Sheets and you’re afraid that you can spoil it, the best way is just to copy the table. Here’s how to do it:

  • First, open the sheet you want to duplicate.
  • Next, right-click and select “Duplicate.”
  • A new sheet gets created using the same name as your file, except it adds “Copy of” in front of it.
  • Use this sheet to test out new formulas on real-world data, etc. Make sure to have multiple copies of any critical data.

So if you have just started working and studying different types of text files, with formulas in Google Sheets and want to test some new formulas on a table, it’s better to do it on copies and choose a way that is convenient for you.

RELATED ARTICLESMORE FROM AUTHOR