If you are looking for a way in Excel to auto-format fractions in a way that they shrink to smaller characters when you type them in the cells, as we do in MS Word, you are in a right place. In this article, you will learn 2 methods to make fractions smaller in Excel.
Download Practice Workbook
You can download the following practice workbook that we have used to prepare this article.
How to Display Fractions in Excel
At first, we will see how to display a fraction in Excel. Usually, we use number formatting when we need to display a fraction. To apply number formatting, just follow the steps below.
- First, select the blank cells (In this example, B2) where you want to type your fractions (In this example, we want to type ¾). Then, go to the Home tab >> Number >> Dialog Launcher button. Or, just press CTRL+1 and a Format Cells window will pop up.
- Under the Number Section, select Fraction, and then choose Up to one digit (1/4).
- Finally, cell B2 is formatted in a way that can hold a fraction of one digit numerator and one digit denominator. So, now type your fraction here (In this example, 3/4).
You can type double, or triple digits fractions by choosing Up to two digits, Up to three digits respectively. And if you want to enter a fraction like 3/14, then make the type Up to two digits. Similarly, if you wish to display a fraction like 3/123, then choose the type to Up to three digits.
2 Methods to Make Fractions Smaller in Excel
If we write fractions in smaller characters, it looks like real mathematical expressions. We haven’t any way that can make all the fractions smaller. However, the following 2 ways will come in handy enough for your real-life purpose.
To display a fraction in smaller characters, you will need to use a keyboard with a number pad.
1. Using ALT Codes for Fractions
There are certain ALT codes to type certain fractions in MS Excel. The following image shows all the ALT codes available to write fractions in a smaller look.
Now, just follow the steps below to utilize them.
- First select the cells (In this example, cell B2) that will contain the fractions in smaller characters (By applying the number formatting shown above).
- Now, press ALT+0188 or ALT+172 on your keyboard (you must type the codes with digits from the number pad of the keyboard). The ALT code is only for displaying ¼ in smaller characters. The other ALT codes for individual fractions are given already.
Finally, here is the fraction in smaller characters.
2. Use a VBA Code to Make Specified Fractions Smaller
If you are comfortable with VBA codes, then you can make some specified fractions in smaller characters. To do this, just follow the steps below.
- First, go to Developer >> click on the Visual Basic >> Insert >> Module. A module window will appear. Copy the following VBA code and paste it into this module window.
Sub MakeFractionsSmallerInExcel() Dim i As Integer, numer As String, denom As String Dim orig As String, uFrac As String For i = 1 To 63 If i = 0 Then orig = i & "/64" numer = ChrW(&H2070) denom = ChrW(&H2082) ElseIf i Mod 32 = 0 Then orig = i / 32 & "/2" numer = udfNumerator(i / 32) denom = ChrW(&H2082) ElseIf i Mod 16 = 0 Then orig = i / 16 & "/4" numer = udfNumerator(i / 16) denom = ChrW(&H2084) ElseIf i Mod 8 = 0 Then orig = i / 8 & "/8" numer = udfNumerator(i / 8) denom = ChrW(&H2088) ElseIf i Mod 4 = 0 Then orig = i / 4 & "/16" numer = udfNumerator(i / 4) denom = ChrW(&H2081) & ChrW(&H2086) ElseIf i Mod 2 = 0 Then orig = i / 2 & "/32" numer = udfNumerator(i / 2) denom = ChrW(&H2083) & ChrW(&H2082) Else orig = i & "/64" numer = udfNumerator(i) denom = ChrW(&H2086) & ChrW(&H2084) End If Application.AutoCorrect.AddReplacement orig, numer & ChrW(&H2044) & denom 'Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Chr(32) & orig 'Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = numer & ChrW(&H2044) & denom Next i End Sub Function udfNumerator(n As Integer) As String Dim it As Integer, it1 As Integer, str As String, str1 As String If n > 9 Then it = Int(n / 10) Select Case it Case 1 str = Chr(185) Case 2, 3 str = Chr(176 + it) Case 0, 4, 5, 6, 7, 8, 9 str = ChrW(&H2070 + it) End Select End If it1 = n Mod 10 Select Case it1 Case 1 str1 = Chr(185) Case 2, 3 str1 = Chr(176 + it1) Case 0, 4, 5, 6, 7, 8, 9 str1 = ChrW(&H2070 + it1) End Select udfNumerator = str & str1 End Function
Here are the specified fractions which can be typed in smaller characters after the VBA code runs.
In this tutorial, I have discussed 2 methods to make fractions smaller in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.