Custom format for displaying bytes as kb, mb, gb or tb.
Response to a few places on the internet: https://productforums.google.com/forum/#!topic/docs/x_T_N-yRUYg And here: https://stackoverflow.com/questions/1533811/how-can-i-format-bytes-a-cell-in-excel-as-kb-mb-gb-etc
Here is one that I have been using:
[<1000000]0.00," KB";[<1000000000]0.00,," MB";0.00,,," GB"
@Daniel15, I don't believe that's possible with just a custom number format. You can only cut digits off and not calculate new values. There's certainly more manual ways to format bytes correctly though. (but this is extra-nice because the formatted cells value is still treated like a number)
@varenc Yeah, that's what I thought. It's really frustrating that Sheets doesn't have it as a built in number format.
How do you use this formatting?
Created a better one based on IF ELSE ladder.
The cell this will format is F3
=IF(F3<(1024), F3 & " B", IF(F3<(1024*1024), QUOTIENT(F3,1024) & " KB", IF(F3<(1024*1024*1024), QUOTIENT(F3,(1024*1024)) & " MB", IF(F3<(1024*1024*1024*1024), QUOTIENT(F3,(1024*1024*1024)) & " GB", IF(F3<(1024*1024*1024*1024*1024), QUOTIENT(F3,(1024*1024*1024*1024)) & " TB", "H")))))
kaustubhcs that works as a formula, and is nice for using actual binary bytes, but there's advantages to using a custom number format instead of a formula! (no secondary cell, copied values are still the original byte count, etc).
Google's very sparse docs on number formats: https://developers.google.com/sheets/api/guides/formats#meta_instructions
Helped me out a lot. Thank you! :)
Is what I was looking for, thank you!
Thank you so much for this, It made my life so much easier!!
Thank you very much!
This is very useful! I can only get it to format 3 options, so if you want the MB/GB/TB version instead of the KB/MB/GB version, this looks like it works:
[<1000000000]0.00,," MB";[<1000000000000]0.00,,," GB";0.00,,,," TB"
Nice work, OP. Thanks 🌟
Many thanks.
Thank you this helped a lot.
Exactly what I was looking for. Works like a charm. Thank you!
I solved this for nicely formatted binary bytes. Assuming you have raw bytes in field F4, use the formula below to multiply each value by the correct number of 1000/1024, and then use the suggested number format to display it as MB/GB/TB. No If statement needed. If you need to do math on it, just use the raw bytes that you started with, not after the 1000/1024 conversion.
=F4*10^(3*floor(log(F4,2)/10))/2^(10*floor(log(F4,2)/10))
[<1000000000]##0.00,," MB";[<1000000000000]##0.00,,," GB";##0.00,,,," TB"
Here's the number formatting guide: https://developers.google.com/sheets/api/guides/formats
super useful!
Briliant!
For those working with exa bytes, here is a modified @kaustubhcs' formula working all the way to zetta bytes. It also prints two decimal places. Replace B2 with your cell.
Base 1024
=IF(abs(B2)<1024, B2 & " B",
IF(abs(B2)<pow(1024, 2), ROUND(DIVIDE(B2,pow(1024, 1)),2) & " KiB",
IF(abs(B2)<pow(1024, 3), ROUND(DIVIDE(B2,pow(1024, 2)),2) & " MiB",
IF(abs(B2)<pow(1024, 4), ROUND(DIVIDE(B2,pow(1024, 3)),2) & " GiB",
IF(abs(B2)<pow(1024, 5), ROUND(DIVIDE(B2,pow(1024, 4)),2) & " TiB",
IF(abs(B2)<pow(1024, 6), ROUND(DIVIDE(B2,pow(1024, 5)),2) & " PiB",
IF(abs(B2)<pow(1024, 7), ROUND(DIVIDE(B2,pow(1024, 6)),2) & " EiB",
IF(abs(B2)<pow(1024, 8), ROUND(DIVIDE(B2,pow(1024, 7)),2) & " ZiB",
"Input is not a number"))))))))
Base 1000
=IF(abs(B2)<1000, B2 & " B",
IF(abs(B2)<pow(1000, 2), ROUND(DIVIDE(B2,pow(1000, 1)),2) & " KB",
IF(abs(B2)<pow(1000, 3), ROUND(DIVIDE(B2,pow(1000, 2)),2) & " MB",
IF(abs(B2)<pow(1000, 4), ROUND(DIVIDE(B2,pow(1000, 3)),2) & " GB",
IF(abs(B2)<pow(1000, 5), ROUND(DIVIDE(B2,pow(1000, 4)),2) & " TB",
IF(abs(B2)<pow(1000, 6), ROUND(DIVIDE(B2,pow(1000, 5)),2) & " PB",
IF(abs(B2)<pow(1000, 7), ROUND(DIVIDE(B2,pow(1000, 6)),2) & " EB",
IF(abs(B2)<pow(1000, 8), ROUND(DIVIDE(B2,pow(1000, 7)),2) & " ZB",
"Input is not a number"))))))))
This uses decimal bytes (1000 bytes = 1 KB), not binary bytes (1024 bytes = 1 KB). Is there something similar for binary bytes?