You can combine two percentages in a single Excel cell by using the CONCATENATE function or the ampersand operator to merge text strings, while ensuring the underlying values remain numeric for further calculations. This approach allows you to display formatted results like "50%" and "75%" together as "50% + 75%" without losing the ability to sum them later.
Understanding Percentage Storage in Excel
Before combining percentages, it is crucial to understand how Excel stores them internally. Excel treats percentages as decimal numbers between 0 and 1. For example, 50% is stored as 0.5, and 75% is stored as 0.75. When you apply percentage formatting to a cell, Excel only changes the visual appearance, not the actual value. This distinction is vital when writing formulas because you are often working with the decimal equivalents rather than the displayed text.
If you attempt to add two cells containing percentages directly, such as
=A1+B1, Excel will correctly sum the decimals. However, if you want to display them side-by-side in one cell, you need to convert these numeric values into text strings that include the percent symbol. This process involves both calculation and text manipulation.
Method 1: Using the Ampersand Operator
The most straightforward way to combine text and numbers in Excel is using the ampersand (
&) operator. This method is efficient and requires no complex functions. Suppose you have two percentages in cells A1 and B1. To display them together in cell C1, you would use a formula that references these cells and inserts descriptive text.
For instance, if A1 contains 0.5 (displayed as 50%) and B1 contains 0.75 (displayed as 75%), you might want the result to read "Total: 50% & 75%". The formula would look like this:
=A1&" "&B1
However, this basic concatenation might result in "0.5 0.75" if the cells are not pre-formatted as percentages. To ensure the output always shows the percent sign regardless of the source cell's format, you can multiply by 100 and append the "%" character manually, though this is less elegant than using number formatting. A better approach is to rely on the source cells being properly formatted. If you want more control over the display, you can use the TEXT function.
Method 2: Using the TEXT Function for Precision
When you need precise control over how the numbers appear within the combined string, the TEXT function is superior. It allows you to specify exactly how many decimal places or symbols should be shown. This is particularly useful when your source data might vary in precision or when you want to force a specific look.
To combine two percentages from A1 and B1 with a plus sign between them, use the following formula:
=TEXT(A1,"0%")&" + "&TEXT(B1,"0%")
Here,
"0%" is the format code. It tells Excel to display the number as a percentage with zero decimal places. If you wanted one decimal place, you would use
"0.0%". This method ensures that even if A1 is 0.504, it will display as "50%" in the final combined cell, provided you stick to the integer format code. This technique is robust because it decouples the display format from the underlying cell formatting, making your report consistent even if source data changes.
Method 3: Concatenating Formulas Directly
Sometimes, you do not have the percentages in separate cells but need to calculate them on the fly and then combine them. For example, if you want to show the success rate of two different events calculated from raw counts. Let's say Event 1 has 8 successes out of 10 trials, and Event 2 has 15 successes out of 20 trials. You can calculate the percentages and combine them in one go.
The formula would be:
=TEXT(8/10,"0%")&" vs "&TEXT(15/20,"0%")
This results in "80% vs 75%". This approach is highly dynamic. If the raw numbers change, the combined cell updates automatically. It eliminates the need for intermediate helper columns, keeping your spreadsheet clean. This is especially helpful in dashboards where space is limited.
Maintaining Calculability After Combination
A common pitfall of combining cells is that the resulting cell becomes text. You cannot perform mathematical operations on text strings. If you need to sum the combined percentages later, this method fails. In such cases, consider using a helper column. Display the combined text in one column for readability, but keep the raw numeric values in adjacent columns for calculations.
Alternatively, if you only need to display the sum, you can simply add the cells:
=A1+B1. But if you need to show both individual components and their relationship, text combination is the only visual option. Be aware that once combined, the cell is no longer a number. If you need to reference this combined value in other formulas, you must extract the numbers again using complex parsing functions, which is generally discouraged. Instead, design your sheet so that calculation cells and display cells are distinct.
For more advanced techniques involving dynamic content, you might find our guide on
How to Use Conditional Formatting with XLOOKUP Formula in Excel (2021) Without VBA useful for enhancing the presentation of your data based on these combined values.
Frequently Asked Questions
Can I add the combined text cells to get a total?
No, once percentages are combined into a text string using concatenation, the cell value becomes text. Excel cannot perform arithmetic operations on text. You must keep the original numeric values in separate cells to calculate sums or averages.
How do I remove the percent sign if I only want the decimal?
If you want to display the decimal equivalent without the percent sign, change the format code in the TEXT function. For example, use
=TEXT(A1,"0.00") instead of
"0%". This displays 0.5 as "0.50" rather than "50%".
What if my percentages have different decimal places?
The TEXT function allows you to standardize the output. By specifying a fixed format code like
"0.0%", all percentages will display with one decimal place, regardless of whether the source value was 0.5 or 0.555. This ensures a uniform appearance in your combined cell.
Is there a way to combine percentages without losing the ability to sum them?
Not directly in the same cell. The nature of text concatenation prevents mathematical summation. To maintain calculability, use separate columns for the raw data and the combined display. You can then sum the raw data columns while displaying the combined text elsewhere.
How can I handle errors when combining percentages?
If a source cell contains an error (like #DIV/0!), the entire concatenated string will return an error. Wrap your references in the IFERROR function to handle this gracefully. For example:
=IFERROR(TEXT(A1,"0%"),"N/A")&" + "&IFERROR(TEXT(B1,"0%"),"N/A").