Skip to content

Instantly share code, notes, and snippets.

Last active April 16, 2024 10:42
Show Gist options
  • Save loganvolkers/933af8513ed8c2268f59c85a31761a43 to your computer and use it in GitHub Desktop.
Save loganvolkers/933af8513ed8c2268f59c85a31761a43 to your computer and use it in GitHub Desktop.
Byte formatting for Google Sheets
Copy link

Brilliant - thanks!

Copy link

dzgnnl commented Mar 26, 2019

Awesome thanks!

Copy link

Very cool!

Copy link

pferrel commented Jan 16, 2020

Much appreciated!

Copy link

This is awesome, thanks!

Copy link

cosjef commented Aug 3, 2020

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

Copy link

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

Copy link

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

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

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

Thank you very much!

Copy link

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

Many thanks.

Copy link

Thank you this helped a lot.

Copy link

gorman42 commented Aug 6, 2023

Exactly what I was looking for. Works like a charm. Thank you!

Copy link

rsandros commented Aug 29, 2023

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.

[<1000000000]##0.00,," MB";[<1000000000000]##0.00,,," GB";##0.00,,,," TB"


Here's the number formatting guide:

Copy link

ns-kbhat commented Mar 6, 2024

super useful!

Copy link

ortarab commented Apr 3, 2024


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