Change the decimals in your spreadsheet to display as fractions. Excel offers options for halves, quarters, etc., but Episode 346 shows you how to create a custom format to display in thirty-secondths or even five-hundred-eighty-eighths.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel podcast, I'm Bill Jelen.
Kind of a crazy thing here in Excel that I never really realized was there.
Did you know that it's possible to take numbers that are displayed as decimals, and instead display them as fractions?
If you go to Format Cells on the Number tab, there is a Fraction category.
And in the Fraction category you can choose that you want it displayed in fractions up to 1 digit, 2 digits, 3 digits, halves, quarters, 8’s, 16’s, 10’s, or 100’s.
So let's take these numbers, these are all tread depth numbers, I know that they're probably in 32’s.
So I'll choose two digits, and there it converts everything to two digits.
Now, they don't specifically say it, but if we go into the Custom Number tab, we can actually change the format to say that we want it displayed in 32’s, and it will display all of the numbers in 32’s for us.
Let me show you quickly here, I have some decimal numbers in column A, and I copied those two columns B, C, and D, formatted the first one to the nearest one digit fraction.
So, this fraction here, which is actually 277/588, they round to the nearest one digit fraction, which is 1/2, if I allow two digits I get 8/17, quite a lot, three digits get the entire fraction of 277/588.
Pretty cool, this is great!
I have kids that are in middle school, they could certainly use this for reducing fractions in their math class.
Or, if you're dealing some old tables where the stock traders were dealing in fractions or tread wear, or any kind of industry where you're dealing in fractions.
You can use Format Cells to create a fraction format, and display your data in Excel in fractions.
Hey, thanks for stopping by, we'll see you next time for another podcast from MrExcel!
Kind of a crazy thing here in Excel that I never really realized was there.
Did you know that it's possible to take numbers that are displayed as decimals, and instead display them as fractions?
If you go to Format Cells on the Number tab, there is a Fraction category.
And in the Fraction category you can choose that you want it displayed in fractions up to 1 digit, 2 digits, 3 digits, halves, quarters, 8’s, 16’s, 10’s, or 100’s.
So let's take these numbers, these are all tread depth numbers, I know that they're probably in 32’s.
So I'll choose two digits, and there it converts everything to two digits.
Now, they don't specifically say it, but if we go into the Custom Number tab, we can actually change the format to say that we want it displayed in 32’s, and it will display all of the numbers in 32’s for us.
Let me show you quickly here, I have some decimal numbers in column A, and I copied those two columns B, C, and D, formatted the first one to the nearest one digit fraction.
So, this fraction here, which is actually 277/588, they round to the nearest one digit fraction, which is 1/2, if I allow two digits I get 8/17, quite a lot, three digits get the entire fraction of 277/588.
Pretty cool, this is great!
I have kids that are in middle school, they could certainly use this for reducing fractions in their math class.
Or, if you're dealing some old tables where the stock traders were dealing in fractions or tread wear, or any kind of industry where you're dealing in fractions.
You can use Format Cells to create a fraction format, and display your data in Excel in fractions.
Hey, thanks for stopping by, we'll see you next time for another podcast from MrExcel!
