How to write a fraction in Excel. I want to write it exactly as shown in the picture.
Is what I want possible?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityHow to write a fraction in Excel. I want to write it exactly as shown in the picture.
Is what I want possible?
Insert → Object
.This is my result:
Inside of a text box (Insert→Text Box), but not inside of a cell unfortunately, you can use Alt+= (or "New Equation" on the insert tab). This lets you freely type an equation (or use the ribbon's editor); typing x/y
will automatically reformat it to a fraction, or you can manually insert a fraction and type into it.
I recommend this over Insert→Object because this type of equation uses a newer, cleaner editor (and you don't need to wait for the objects list to load). Neither option can be used within a cell, so this one is preferable.
(x+y)/3<space>
and it'll convert to the fraction for you automatically, without the parentheses
– phuclv
Mar 24 '17 at 2:09
If you don't want to mess with the equation editor, and all you are looking to do is display the fraction, you can simply use regular cells with a border between the upper and lower cell. Then resize the cells to format it the way you like.
A third possibility: common fractions such as ⅓(1/3), ⅔(2/3), ¾(3/4), ⅝(5/8).. have Unicode representations, see http://unicodefractions.com/ . If it's these you need, rather than arbitrary representations with any numerator and denominator, the answer may lie with substring and string concatenation functions to generate the label or formatting you want.
Another possibility is to use Unicode subscripts and superscripts if you want arbitrary values, not only common fractions like ⅔, ¼, ⅝...
The numerator can be written using the superscript numbers ⁰¹²³⁴⁵⁶⁷⁸⁹
and the denominator can be written with subscript numbers ₀₁₂₃₄₅₆₇₈₉
. The delimiter between them can be solidus /
or fraction slash ⁄
depending on your taste
For example ⁴⁵/₈₉, ⁷⁄₂₃
As you can see the fraction slash has the advantage of better kerning/ligature/font substitution support and the numbers really overlap each other
If you want to show a vertical fraction then simply use the horizontal box-drawing characters ─
for the horizontal line. This way the line is always in the exact middle of the 2 numbers and multiple lines connect to each other continuously instead of a dashed line
123
─────
45678
The OP hasn't indicated if there is any need to actually use the fraction in the spreadsheet, or if it is simply a need to display a fraction.
If you want to simply make a cell display a fraction
Then type Ctrl+U followed by 153 followed by Ctrl+U followed by Alt+Enter followed by 71
Although this will display as a fraction, the cell value will be the string 15371, so not suitable for a formula where you want the actual value of the fraction.
You can get Excel to display a value as a fraction, however the format does not show a horizontal line, but rather uses 153/71
To get Excel to show a number as a fraction, format the cell to have a custom number format as follows:
0/###
Be aware that the number of # symbols will limit the magnitude of the denominator. If you set the format to be 0/# then 153/71 will display rounded to 13/6 however the actual cell value accuracy will remain and the cell can be referred to in a formula.
─
works much better. Also you should use it with center alignment
– phuclv
Sep 1 '19 at 1:57
In Excel 2007 and later, you can insert an equation by clicking on Equation in the Insert tab:
When you've done that, you will get a place to type an equation. You can either use the "Fraction" button in the "Equation tools" tab (which shows up automatically when you insert an equation, or if you type a fraction of the type 2/3
and then press Enter it will automatically convert to a fraction. This is the result I got:
The equation can be moved around like an image so you can put it where you want.
If you're using an older version than Excel 2007, User552853's answer explains how to do this in older versions of Excel.
If you're okay with your fractions not using strictly a horizontal line (OP was asked but never replied), this is how you enter fractions to be used as values within a cell in Excel:
WholeNumber
spacebarNumerator
/Denominator
ENTER
So if you wanted to enter 1/12th, you'd do,
0 1/12
and in excel it would display as:
1/12
And the value would be available for computation as 0.0833333333333333
For one more example, if your fraction was 2 and 7/38ths, you'd enter it as 2 7/38
I added some visuals below in case that helps more.
That should give you this result:
You can use a Macro to do that:
Sub Fraction()
ActiveCell.FormulaR1C1 = Range("b3") & Chr(10) & "—" & Chr(10) & Range("c3")
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
This is the result I got:
You need to use the ALT button. I use it constantly in my workbooks, but it is limited to the basic fractions, although you can do much more with it too. Once you get the hang of it, it becomes second nature and it looks much more professional. So 1/4 = Alt + 0188 (while holding down the alt button) | 1/2 = ALT + 0189 | 3/4 = ALT + 0190. The end result looks like this 1¾, not 13/4. Check this site https://usefulshortcuts.com/downloads/ALT-Codes.pdf Hope this helps as much as it helped me. I spent a long time struggling with this same problem.