29

How to write a fraction in Excel. I want to write it exactly as shown in the picture.

Is what I want possible?

enter image description here

  • Did you try format number fraction? – yass Mar 22 '17 at 10:55
  • 5
    exactly means with the proper horizontal line? If yes, then answer is no, you can't get it in a cell, but you can get it in an equation (insert tab - equation) – Máté Juhász Mar 22 '17 at 10:58
  • I removed the [equation-editor] tag since the OP did not specify they were using it, nor ask for assistance with it. Adding it changes the OP's question to what we think they want. Let the OP clarify. – CharlieRB Mar 22 '17 at 14:06
  • 4
    Do you want to use the fraction in any calculations too? Or just want to display the fraction in a cell/image/whatever? – BruceWayne Mar 22 '17 at 22:53
  • 1
    This may be of interest: Styled Fractions in Windows – Jeeped Mar 25 '17 at 20:19
43
  • In Excel go to Insert → Object.
  • From the list select "Microsoft Equation 3.0".
  • Select the equation that suits your needs. In this case:

enter image description here

  • Type 2, press TAB key to move the cursor and then 3.

This is my result:

enter image description here

  • 9
    since office 2010 (or 2007, not sure because it was too long since the last time I used them) you don't use MS equation anymore. Equation tool is embedded into all applications (unlike MS equation which is an optional part) – phuclv Mar 22 '17 at 15:34
  • 1
    @LưuVĩnhPhúc in all office 365 apps, the insert -> equation is present next to the insert -> symbols. – Tim Mar 22 '17 at 21:13
  • @Tim That's exactly what I mean. I've never seen office 365 but I think it's similar to other offline offices. Probably you haven't used office 97 or 2000? They don't have equation feature included but you must install a separate Microsoft equation. This answer is about insert > objects > MS equation 3.0, not about insert > equation – phuclv Mar 23 '17 at 2:48
  • 1
    @Tim it has always been named Excel since version 2.0 in 1987. I still remember Excel 97 even has a hidden racing game in it. – phuclv Mar 23 '17 at 8:57
  • 2
    @LưuVĩnhPhúc: Actually, since Excel version 1.0 in 1985. – Lightness Races in Orbit Mar 23 '17 at 12:35
18

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.

enter image description here

  • 2
    Also you could (using the image here as an example) merge cells C2 and C3 then vertically center the text in the merged cell, to have your text line up with the fraction line. – Jason C Mar 23 '17 at 6:23
18

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.

Equation editor

Equation in a text box

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.

  • you can type things like (x+y)/3<space> and it'll convert to the fraction for you automatically, without the parentheses – phuclv Mar 24 '17 at 2:09
9

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.

  • 2
    Can you elaborate how to use this in Excel? – CharlieRB Mar 22 '17 at 14:02
  • Sorry, no. I could tell you how to use it in LibreOffice, but I'm at a rare Linux-only workplace! – nigel222 Mar 22 '17 at 14:06
  • 1
    For the purpose of improving clarity of your answer, can you at some point elaborate? The idea here is to answer the question related to what the OP has asked it (in this case Excel). To do so sometimes requires us to provide instructions how to implement suggested answer. – CharlieRB Mar 22 '17 at 14:10
  • 1
    To enter unicodes (again Libreoffice but probably general) you do ctrl/shift/u followed by the hex digits of the unicode in question. – nigel222 Mar 22 '17 at 14:33
  • 2
    In Excel (Windows), go to ribbon Insert > Symbol > Subset "Number Forms" or scroll down > Select from ⅓ to ⅞. See support.office.com. Note that these do not look exactly as the OP requested. – Nigel Touch Mar 22 '17 at 20:03
8

Another possibility is using 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 ⁴⁵/₈₉, ⁷⁄₂₃

  • Good, also great for encouraging people to update their software when it throws small boxes of nonsense all around their screen :) – Sampo Sarrala Mar 24 '17 at 13:40
7

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.

Display and not use as a value

If you want to simply make a cell display a fraction

enter image description here

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.

Display fraction and use as a value

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.

  • If you want to use codes for the special characters, then, (for codes up to 254), you can enter them into Excel (Windows) by holding down the [ALT] key and typing the code preceded by 0. eg [ALT] + 0190 will type in ¾ Note: this will not provide a fraction useful for an equation!) – Kev Mar 24 '17 at 6:47
1

In Excel 2007 and later, you can insert an equation by clicking on Equation in the Insert tab:

enter image description here

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:

enter image description here

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.

  • isn't this already answered by Pokechu22 above? – phuclv Mar 24 '17 at 2:03
0

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:

WholeNumberspacebarNumerator/DenominatorENTER

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.

enter image description here

That should give you this result:

enter image description here

  • 1
    "I want to write it exactly as shown in the picture." – Máté Juhász Feb 7 '18 at 21:21
  • The OP never answered your own question to clarify if it needed to be the horizontal line. This answer will help people that want to know how to enter fractions displayed as fractions and still usable in excel, so I'm keeping it here. – kayleeFrye_onDeck Feb 7 '18 at 21:24
0

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:

Result

  • 1
    Just wanna remind you to post your result. – var firstName Aug 15 '17 at 16:36
  • you use only one "—", so how about wider fractions like 23/45 or 145/236? – phuclv Aug 20 '18 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.