- Use formulas, format percentages and more
- Split on Google Sheets
- Formulas in Google Sheets
- Using cell references in formulas
- Examples of division formulas
- Point-and-click formula to enter
- Change formula data
- # DIV/O! Formula errors
- Calculate percentages with division formulas
Use formulas, format percentages and more
Like Excel, the Google Sheets spreadsheet program does not have a DIVIDE function. Instead, you create a formula in Google Sheets to perform division operations. Learn how to create a formula, how to deal with errors that may occur, and how to use the DIVIDE formula for percentage results.
Split on Google Sheets
Formulas in Google Sheets
To divide two numbers in Google Sheets, create a formula.
Important points to remember about worksheet formulas:
Formulas always start with an equal sign ( = ).
The equal sign always comes in the cell where you want the answer.
The division operator is the forward slash (/).
The formula is completed by pressing the key Input on keyboard.
Using cell references in formulas
You can enter numbers directly into the formula, as shown in lines 2 and 3 in the image example.
However, it is much better to enter data into table cells and then use the addresses or references to those cells in the formula, as shown on lines 4 to 6 in the example.
By using cell references (such as A2 or A5) instead of the actual data in the formula when you need to change the data later, you replace the data in the cells instead of rewriting the formula.
Formula results are automatically updated when the data changes.
Examples of division formulas
The formula in cell B4 of the example is:
divide the data in cell A2 by the data in A3 to get the answer 2.
Point-and-click formula to enter
Although you can type the formula
to cell B4 and that cell shows the correct answer 2, it is better to use pointer and click or pointer to add cell references to formulas, especially those with long formulas.
This minimizes the chance of errors when entering an invalid cell reference.
Hover over a cell with data and click it to add the cell reference to the formula.
To enter a formula:
Enter = (equal sign) in a cell B4 to start the formula.
Click on a cell A2 mouse pointer to add a reference to that cell in the formula after the equals sign.
Enter / (divider or slash) in a cell B4 after the cell reference.
Click on a cell A3 mouse pointer to add a reference to that cell in the formula after the division sign.
Press key Enter on the keyboard to complete the formula.
The answer 2 must be in cell B4, because 40 divided by 20 is 2.
Even if the answer is displayed in cell B4, when you click that cell, the formula = A2/A3 displayed in the formula bar above the worksheet.
Change formula data
To test the value of using cell references in a formula, change the number in cell A3 from 20 to 40 and press the key Enter on keyboard.
The answer in cell B2 should be automatically updated to 4 to reflect the change in the data in cell A3.
# DIV/O! Formula errors
The most common error in divide operations is the error value # DIV/O! .
This error appears when the denominator in a division formula is zero, which is not allowed in regular arithmetic.
The most likely reason for this is that the wrong cell reference was entered in the formula, or the formula was copied to another location using the fill handle, and changing cell references results in an error.
Calculate percentages with division formulas
A percentage is simply a comparison of two numbers using the division operation.
More specifically, it is a fraction or decimal that is calculated by dividing the numerator by the denominator and multiplying the result by 100.
General form of the equation:
= (numerator/denominator) * 100
When the results of a division operation (or quotient) are less than one, Google Sheets presents them as a decimal by default, as shown in the fifth line, where:
The counter is at 10.
Denominator to 20.
The coefficient is 0.5.
This result can be changed to a percentage by changing the formatting in the cell to percentage format of Auto Auto Format as shown by the 50% result shown in cell B6 of the example.
This cell contains the same formula as cell B4. The only difference is the formatting in the cell. When percent formatting is applied, the program multiplies the decimal value by 100 and adds a percent sign.