How to Divide One Column by Another in Google Sheets

If you haven’t used Google Sheets for advanced calculations yet, you may not be aware that you can divide one column by another. What’s more, this operation is effortless and straightforward. All you have to do is use an array formula to perform multiple calculations on more items in an array – a column or a row.

In this article, we’ll show you the fastest way to do so, as well as some other similar operations.

Step-by-Step Guide

Let’s say that you have two columns with 10 numbers in each – we’ll call them column A and column B. You now want to divide column A by column B, and you’d like to get results in column C. Here’s what you have to do:

  1. Click on the cell C1.
  2. Enter the following formula: =ARRAYFORMULA(A1:A10/B1:B10).

That’s all! The formula will now calculate the result for each pair of cells. The best thing about this formula is that you can get results for hundreds of cells in a few seconds. What would we do without Google Sheets?

Note: Before you start, make sure that the whole column C (or any other column you want to use to show results) is empty. Otherwise, the formula won’t function properly.

Google sheets how to divide one column

Can I Reverse This Operation?

If you just want to check something, but don’t need the results anymore, there’s a simple way to reverse this operation. All you have to do is erase the content of cell C1, and all results will be deleted.

However, you should know that it’s not possible to delete or edit part of the results. That’s the way array formulas work: you can either take all results or nothing. And there’s a good reason for that: editing only one of the C cells could lead to misuse of data, especially in big companies.

Can I Divide Columns Without Remainder?

If you want your results to be shown as integer numbers, that’s possible too. However, bear in mind that this method will never be as accurate as the method described above.

We’ll use the same example and divide column A by column B, and get results in column C. Here’s what you have to do to divide one column by another without remainder:

  1. Click on the cell C1.
  2. Enter the following formula: =ARRAYFORMULA(INT(A1:A10/B1:B10)).

There you have it! As you may have guessed, the prefix INT means that you only get the integer part of the number without the decimal part.

Google sheets divide one column by another

How to Multiply One Column by Another?

Some people may want to do the exact opposite and multiply cells in one column by the cells from another column. There’s an easy way to do so with one of the array formulas.

Let’s say you want to multiply column A and column B (each of them has 10 values), and you’d like the results to be shown in column C. Make sure that column C is empty and follow these steps:

  1. Click on the cell C1.
  2. Enter the following formula: =ARRAYFORMULA(A1:A10*B1:B10).

That’s it! Google Sheets will now give you results for each pair of cells in just a few seconds.

Again, you can delete the whole column C and undo the results, but you can’t delete or edit single results. To remove the entire array, just click on the cell C1 and delete its content.

How to Split Cell Content Into Separate Columns?

One of the most irritating things is when your colleagues enter multiple values into single cells. This can be a massive problem if you want to divide two values, but you can’t apply the formula mentioned above. In order to do so, you first have to split the cell content into two separate columns.

Before you do so, you have to separate the data by adding commas in between the content you want to split.

  1. Select all the cells you want to split or the whole column.
  2. Select Data from the top menu.
  3. Select Split text to columns.
  4. You’ll now have to choose separator, and you should click on Comma.

There you have it! If you don’t have time to separate content by commas, you can use the Detect automatically option. However, we don’t recommend relying on this option, as it isn’t 100% accurate.

Calculation Made Easy

Today, when many people have to operate with a large quantity of data, it’s hard to imagine what we’d do without tools like Google Sheets. In this guide, we’ve covered some of the most useful operations regarding dividing and multiplying, but there’s so much more. Whatever math problem you may be struggling with, Google Sheets has a solution for it!

Do you often use Google Sheets for calculating, or you have an even better option? Let us know in the comments section below.

Leave a Reply

Your email address will not be published. Required fields are marked *


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.