How can I add fractions to Excel, without them automatically simplifying?

Question

I enter 6/20 (six out of 20 available days) and it reduces to 3/10. But I don't want that to happen. This is a small business PTO document.

Answer 1

Hi Melissa C

Thank you for reaching out to Microsoft Q&A Forum and sharing your concern.

When you enter 6/20, Excel interprets it as a mathematical fraction and automatically reduces it to its simplest form (3/10). To prevent Excel from simplifying the fraction, you have a few options depending on how you plan to use the value.

1, Enter as Text if you simply need the fraction for display purposes as it won't work in calculations

• Format the cell as Text before typing the value, or type an apostrophe before the fraction: '6/20

2, Separate the numerator and denominator

Since this is a PTO document, separating the used days and total days into different cells seems to be the best approach for small businesses.

• For PTO tracking, a more reliable approach is to store: Used days (6) in one cell and Total days (20) in another cell
• Display them together using a formula (for example: =A1&"/"&B1)
• You can then calculate remaining days, percentages, or totals accurately without formatting issues

3, Use a custom fraction format if you want Excel to treat it as a number but keep the original denominator

This forces Excel to display fractions with a denominator of 20 while still allowing calculations.

• Select the cell
• Go to Format Cells > Custom
• Use a format like: # ??/20

Please let me know how things go on your side as your update can also assist others in the community who may be facing the same issue.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

In Excel, fractions are typically displayed in their simplest form by default. However, you can prevent Excel from automatically simplifying fractions by formatting the cell as a text before entering the fraction. Here’s how you can do it:

1. Select the Cell: Click on the cell where you want to enter the fraction.
2. Format as Text: Right-click the cell and choose Format Cells. In the Format Cells dialog, select Text and click OK.
3. Enter the Fraction: Now, when you enter 6/20, Excel will treat it as text and will not simplify it.

Alternatively, you can also precede the fraction with an apostrophe (e.g., '6/20). This tells Excel to treat the entry as text, preventing it from simplifying the fraction.

By using these methods, you can maintain the original fraction format in your Excel document without it being automatically reduced.

---

References:

• How to control and understand settings in the Format Cells dialog box in Excel