• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Excel Tips / Entering Fractions in Excel

Entering Fractions in Excel

Entering fractions in Excel can be confusing. When a fraction is entered, Excel converts the value to a date format e.g. 1/2 is stored as 01-Feb.

This is frustrating and without storing the value correctly as a fraction, any formulas dependent upon this value will not work correctly.

To enter a fraction in Excel, type a 0 and a space followed by the fraction. Excel then stores the number as a fraction and reduces it to the smallest possible denominator. So if you type 0 2/16, Excel stores it as 1/8.

Now with a fraction stored correctly the cell can be referred to within formulas without error. Further formatting can also be applied to the cell through the Format Cells dialogue box.

Formatting fractions in Excel

If you have entered a number as a decimal, this can easily be converted to a fraction by using the Number format list. If you enter the number 0.25, or it is returned as the result of a formula, it can be formatted as a fraction to get 1/4.

Number formatting list

Watch the Video

Related Posts:

  • Live currency exchange rate in Excel thumbnail
    LIVE Currency Exchange Rate in Excel
  • Copy a formula down in Excel
    How to Copy a Formula Down in Excel
  • Case sensitive XLOOKUP feature
    Case Sensitive Lookup with XLOOKUP in Excel
  • Compare dates in Excel feature
    How to Compare Dates in Excel

Reader Interactions

Comments

  1. Ted Young says

    22 October 2012 at 3:43 am

    I was having a major problem copying fractions from a webpage and pasting into excel (2003) with out them being converted in to date.
    My solution is a bit tricky, but works great.
    1) Determine which column(s) will hold the fractional values.
    2) Format the entire column(s) as Text.
    3) Paste in the data as unicode text (paste special)
    4) Select all newly pasted fractions and format as fraction of your choice OR with fractions selected do step 5
    5) run the following macro. Formats cells as 2 digit denominators unless changed. Code follows:
    Public Const VK_F2 = 113
    Public Const VK_ENTER = 13
    Public Const KEYEVENTF_EXTENDEDKEY = &H1
    Public Const KEYEVENTF_KEYUP = &H2
    Public Declare Sub keybd_event Lib “user32.dll” (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
    Sub FIX_FRAC()
    ‘Ted Young 10/21/2012
    For Each cell In Selection
    cell.Value = WorksheetFunction.Trim(cell.Value)
    cell.NumberFormat = “# ??/??”
    Next cell
    Call F2_ENTER
    End Sub
    Sub F2_ENTER()
    For Each cell In Selection
    keybd_event VK_F2, 0, 0, 0
    keybd_event VK_F2, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_ENTER, 0, 0, 0
    keybd_event VK_ENTER, 0, KEYEVENTF_KEYUP, 0
    Next cell
    End Sub

    Reply
  2. Ted Young says

    22 October 2012 at 4:07 am

    As an afterthought, when you get to step 4 of my solution, you’ll find the formatting to fraction doesn’t quite “take” (excel will right-hand align numbers). You would have to open each cell containing a fraction by double clicking or hitting F2, then close it by clicking another cell or hitting “Enter”.
    Just run the macro – it does it all for you.
    BTW (for VBA coders), the F2_ENTER subroutine is another workaround of mine because I found that VBA “Sendkeys” no longer works on most machines running Vista or Windows 7 because of UAC (User Account Controls).
    The “keybd_event” method is 6 times as much code as “Sendkeys” is, but it works. I did find I had to call it at the end of the subroutine or I would get some unpredicable results. As a stand-alone subroutine it’s fine.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·