Need to convert a report or a chart to display in thousands or millions? Episode 555 shows two different methods to achieve this result.
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 netcast, I'm Bill Jelen.
Now on Friday’s netcast I was talking about a rather complex custom number format using all the different zones; made me remember one of my favorite formatting tricks is when you have numbers that are large for example numbers and thousands come as taking up a lot of space, and you'd rather just show the numbers and thousands we can use format cells and normally we'd go to the number tab and say we want to show the thousands separator maybe zero decimal places and of course that puts the comma in for us, but then my favorite thing to do is to go to the custom type and at the end of the number format want to type one comma and a K ; the comma says divide that number by a thousand when we display it the K puts an actual k there, click OK, now instead of 100,000,289 we see 103 k.
I used to use the same trick for my charts let's see I have a chart here where the numbers are actually in millions and I would right click the value access, choose format access and change the custom number type the way that I just showed, but a better way to do that this is better than what's in the book is to go to the scale tab and there's a setting they're called display units we can actually change that to thousands, Millions, billions, or trillions, in this case probably millions as a way to go and we get a little tag up here saying that the data is in millions and now instead of wasting all those zeroes we just get a nice concise number 150, 200 and 250 million.
so a couple of different ways to format numbers and thousands one using the custom number format, comma, k at the end, every comma divides by a thousand so if you needed to put in millions with two commas and then the M abbreviation unfortunately has to be in quotes so comma, comma, quote, M quote, or if you're in a chart just right-click and use the scaling option, I'm just show it in hundreds, thousands, millions, billions, or trillions.
Hey thank you for stopping by; we'll see you next time for another netcast for MrExcel.
Now on Friday’s netcast I was talking about a rather complex custom number format using all the different zones; made me remember one of my favorite formatting tricks is when you have numbers that are large for example numbers and thousands come as taking up a lot of space, and you'd rather just show the numbers and thousands we can use format cells and normally we'd go to the number tab and say we want to show the thousands separator maybe zero decimal places and of course that puts the comma in for us, but then my favorite thing to do is to go to the custom type and at the end of the number format want to type one comma and a K ; the comma says divide that number by a thousand when we display it the K puts an actual k there, click OK, now instead of 100,000,289 we see 103 k.
I used to use the same trick for my charts let's see I have a chart here where the numbers are actually in millions and I would right click the value access, choose format access and change the custom number type the way that I just showed, but a better way to do that this is better than what's in the book is to go to the scale tab and there's a setting they're called display units we can actually change that to thousands, Millions, billions, or trillions, in this case probably millions as a way to go and we get a little tag up here saying that the data is in millions and now instead of wasting all those zeroes we just get a nice concise number 150, 200 and 250 million.
so a couple of different ways to format numbers and thousands one using the custom number format, comma, k at the end, every comma divides by a thousand so if you needed to put in millions with two commas and then the M abbreviation unfortunately has to be in quotes so comma, comma, quote, M quote, or if you're in a chart just right-click and use the scaling option, I'm just show it in hundreds, thousands, millions, billions, or trillions.
Hey thank you for stopping by; we'll see you next time for another netcast for MrExcel.
