Calculate the Remaining Percentage from a Fixed Total in Excel
Question details
Calculate a Remaining percentage by subtracting the total of entered allocation percentages from a fixed total, such as 100%.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Several percentage allocation cells must add up to a fixed total, and the remaining allocation needs to change automatically when values are edited.
- Observed behavior
- The Remaining cell should display the unused percentage of the fixed total after all allocation cells are summed.
Place the fixed total, such as 100%, in its own cell and confirm that all allocation values are formatted consistently as percentages. Identify the allocation range that should be included before entering the Remaining formula.
Subtract the allocation total from the fixed percentage total
Use this formula when your allocations are in a continuous range and the Remaining cell must update automatically.
The Remaining value is the fixed total minus the sum of every allocation percentage. Use cell references rather than typing values into the formula so Excel recalculates the result whenever an allocation changes.
Click a separate total cell, such as B1, type 100%, and press Enter.
Type the individual allocation percentages in their cells, for example B2 through B5.
Click the Remaining cell, such as B6, enter =B1-SUM(B2:B5), and press Enter.
Change any value in B2:B5 and confirm that the value in B6 changes to show the unused percentage.

Use separate cells when allocation percentages are not adjacent
Use this approach when the allocation values are located in different parts of the worksheet.
Calculate a Dynamic Remaining Percentage in WPS Spreadsheets
WPS Spreadsheets uses familiar Excel-style formulas, so you can subtract the sum of your allocations from a fixed 100% total and keep the Remaining percentage updated automatically.
- 1. Open the worksheet in WPS Spreadsheets: Open WPS Office, select Spreadsheets, and open your Excel workbook or create a new sheet.
- 2. Set the total percentage: Click a total cell such as B1, type 100%, and press Enter.
- 3. Enter the allocation values: Enter your assigned percentages in a range such as B2:B5.
- 4. Create the Remaining calculation: Click the Remaining cell, type =B1-SUM(B2:B5), and press Enter.

Frequently Asked Questions
What formula calculates the percentage remaining from 100%?
If 100% is in B1 and your allocations are in B2:B5, use =B1-SUM(B2:B5) in the Remaining cell.
Why does my Remaining percentage show a negative value?
A negative result means the allocation cells total more than the fixed total. Check the values in the SUM range and make sure they do not exceed 100%.
Can I use a number instead of a percentage for the fixed total?
Yes. If your total is 100 rather than 100%, enter 100 in the total cell and use the same subtraction formula. Keep all allocation cells in the same number format.
How do I exclude one percentage cell from the Remaining calculation?
Edit the SUM range in the formula. For example, change =B1-SUM(B2:B5) to =B1-SUM(B2:B4) to exclude B5.
Will the formula work in WPS Spreadsheets?
Yes. WPS Spreadsheets supports formulas such as =B1-SUM(B2:B5) and is compatible with Excel .xlsx and .xls workbooks.




