The basic percentage formula in Calc

To calculate a percentage in LibreOffice Calc, you divide the part by the whole and multiply by 100. If you want Calc to do this for you, the formula is =A1/B1*100, where A1 is the part and B1 is the whole. Type this into any cell and Calc will show you the result as a number.

You can also format the result as a percentage without multiplying by 100. Type =A1/B1 into a cell, then right-click the cell and choose Format Cells. Click the Numbers tab, select Percent from the Category list, and click OK. Calc will display the decimal as a percentage automatically.

The second method is cleaner because you do not have to remember to multiply by 100, and if you later use that cell in another calculation, Calc treats it as a decimal rather than a whole number.

Key Takeaways

  • The formula =A1/B1 calculates a percentage when you format the cell as Percent in the Format Cells dialog.
  • The formula =A1/B1*100 gives you the percentage as a plain number without formatting.
  • You can explore a percentage formula to a whole column at once by selecting the cell with the formula and dragging the fill handle down.
  • Percentage formulas work the same way whether your numbers are in the same sheet or in different sheets — just reference the other sheet by name.

Calculating what percentage one number is of another

This is the most common percentage task. Say you have sales data: column A shows individual sales amounts, and column B shows total sales for the month. You want to know what percentage each sale is of the total.

Put your formula in column C. In cell C1, type =A1/B1. Then format column C as a percentage. Select the entire column C by clicking the column header, right-click, choose Format Cells, click the Numbers tab, select Percent, and click OK. Now C1 will show the percentage.

To copy this formula down to all your rows, click C1, then position your cursor at the small square in the bottom-right corner of the cell (the fill handle). Drag it down to the last row with data. Calc will adjust the row numbers automatically — C2 will calculate A2/B2, C3 will calculate A3/B3, and so on.

Finding the percentage increase or decrease between two numbers

If you need to know how much a number has grown or shrunk, the formula is =(New Value - Old Value) / Old Value. In Calc, this looks like =(A2-A1)/A1 if A1 is the starting number and A2 is the ending number.

Format this cell as a percentage the same way: right-click, Format Cells, Numbers tab, Percent. A positive result means growth. A negative result means decline. If your old value is zero, Calc will show an error because you cannot divide by zero.

Calculating the actual amount when you know the percentage

Sometimes you know the percentage and need to find the amount. For example, you know that 15% of your budget is allocated to marketing, and your total budget is $50,000. How much is 15% of $50,000?

The formula is =Percentage * Whole. In Calc, if your percentage is in A1 (as 0.15 or formatted as 15%) and your whole amount is in B1, type =A1*B1. If your percentage is written as a plain number like 15 instead of 0.15, use =A1/100*B1 instead.

Working with percentages across multiple sheets

If your data is split across different sheets, you can still calculate percentages. Reference another sheet by typing the sheet name, then a period, then the cell. For example, if you want to divide a value in Sheet1 cell A1 by a value in Sheet2 cell B5, type =Sheet1.A1/Sheet2.B5.

Right-click the sheet tabs at the bottom of your window to see all available sheets. The sheet name must match exactly, including capital letters. If the sheet name has spaces, put it in single quotes: ='Sales Data'.A1.

Avoiding common percentage mistakes

The most frequent error is multiplying by 100 when you have already formatted the cell as a percentage. If you type =A1/B1*100 and then format as Percent, Calc will show 5000% instead of 50%. Use one method or the other, not both.

Another mistake is forgetting to use absolute references when you want to divide multiple cells by the same total. If your total is always in B10 but you are calculating percentages for rows 1 through 9, use =A1/$B$10 instead of =A1/B10. The dollar signs tell Calc to always use B10, even when you copy the formula down. Without them, the formula will change to B11, B12, and so on as you copy it.

Displaying percentages with a specific number of decimal places

By default, Calc shows percentages with two decimal places. If you want more or fewer, right-click the cell, choose Format Cells, click the Numbers tab, select Percent, and change the number in the Decimal places field. Type 0 for whole numbers, 1 for one decimal place, or 3 for three decimal places.

You can also select multiple cells at once and format them all the same way. Click the first cell, hold Ctrl, and click each additional cell. Then right-click and format as before.

Frequently Asked Questions

What is the difference between =A1/B1 and =A1/B1*100?

Both calculate the same percentage, but they display it differently. =A1/B1 gives you a decimal (0.5), which you then format as Percent to show 50%. =A1/B1*100 gives you the number 50 without any formatting. The first method is cleaner because Calc treats the result as a true percentage in other calculations.

How do I calculate what 20% of a number is?

Multiply the number by 0.20. If the number is in cell A1, type =A1*0.20. If you want to reference a cell that contains 20 instead of 0.20, use =A1*(B1/100) where B1 contains 20.

Can I copy a percentage formula to a whole column at once?

Yes. Click the cell with your formula, then drag the small square at the bottom-right corner of the cell down to the last row you need. Calc will adjust the row numbers in the formula automatically for each row.

What does the error #DIV/0! mean?

It means you are trying to divide by zero, which is impossible. Check that the cell you are dividing by (the denominator) actually contains a number and is not empty or zero.

How do I show a percentage with no decimal places?

Right-click the cell, choose Format Cells, click the Numbers tab, select Percent, and change Decimal places to 0. Click OK.