Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Byte formatting for Google Sheets
Copy link

CommanderTso commented Feb 28, 2019

Brilliant - thanks!

Copy link

dzgnnl commented Mar 26, 2019

Awesome thanks!

Copy link

mherrmann commented Jan 8, 2020

Very cool!

Copy link

pferrel commented Jan 16, 2020

Much appreciated!

Copy link

FeherMarcell commented Apr 3, 2020

This is awesome, thanks!

Copy link

cosjef commented Aug 3, 2020

How would I adjust this to also accomodate for Terabyte values?

Copy link

minkovich commented Oct 30, 2020

[<1000000000000]##0.00,,," GB";[<1000000000000000]##0.00,,,," TB";#,##0.00,,,,," PB"

Copy link

connollyst commented Nov 19, 2020

Exactly what I was looking for @minkovich, good looking out!

Copy link

Daniel15 commented Dec 8, 2020

This uses decimal bytes (1000 bytes = 1 KB), not binary bytes (1024 bytes = 1 KB). Is there something similar for binary bytes?

Copy link

varenc commented Dec 9, 2020

@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)

Copy link

Daniel15 commented Dec 9, 2020

@varenc Yeah, that's what I thought. It's really frustrating that Sheets doesn't have it as a built in number format.

Copy link

kaustubhcs commented May 18, 2021

How do you use this formatting?

Copy link

kaustubhcs commented May 19, 2021

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")))))

Copy link

varenc commented Jun 20, 2021

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:

Copy link

Smart123s commented Jul 4, 2021

Helped me out a lot. Thank you! :)

Copy link

eljeko commented Sep 14, 2021

Is what I was looking for, thank you!

Copy link

fordsho commented Sep 17, 2021

Thank you so much for this, It made my life so much easier!!

Copy link

caio-vinicius commented Sep 22, 2021

Thank you very much!

Copy link

alexstorer commented Oct 1, 2021

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"

Copy link

pkowaluk commented Apr 1, 2022

Nice work, OP. Thanks 🌟

Copy link

mcsherrylabs commented May 17, 2022

Many thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment