Skip to content

Instantly share code, notes, and snippets.

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

cosjef commented Aug 3, 2020

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

@minkovich
Copy link

minkovich commented Oct 30, 2020

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

@connollyst
Copy link

connollyst commented Nov 19, 2020

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

@Daniel15
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?

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

@Daniel15
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.

@kaustubhcs
Copy link

kaustubhcs commented May 18, 2021

How do you use this formatting?

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

@varenc
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: https://developers.google.com/sheets/api/guides/formats#meta_instructions

@Smart123s
Copy link

Smart123s commented Jul 4, 2021

Helped me out a lot. Thank you! :)

@eljeko
Copy link

eljeko commented Sep 14, 2021

Is what I was looking for, thank you!

@fordsho
Copy link

fordsho commented Sep 17, 2021

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

@caio-vinicius
Copy link

caio-vinicius commented Sep 22, 2021

Thank you very much!

@alexstorer
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"

@pkowaluk
Copy link

pkowaluk commented Apr 1, 2022

Nice work, OP. Thanks 🌟

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