=ROUND( A2/(1024)^(FLOOR(log(A2)/log(1024))), 2) & " " & SWITCH( FLOOR( log(A2) / log(1024) ) ,0,"Bytes",1,"KiB",2,"MiB",3,"GiB",4,"TiB")

This formula can be used in Google Sheets to take a value in bytes and then represent it in a human-readable format with binary units (KiB, MiB, GiB, and so on).

You need to use a helper cell for this to work. This means you need to have the bytes in one cell, and your formatted units in another cell.

1. Copy the formula above
2. Paste the formula into your helper cell
3. Replace every mention of A2 with the appropriate cell

| |A|B| |-|-|-|-| |1|bytes value|formula| |2|2048|2 KiB|

If your sheet contains data concerning file sizes, systems and other information storage, then representing it in binary units (or IEC units) is absolutely essential and the most correct format for that use case.

If you represent your Byte amounts in decimal format (KB, MB, etc), the values are arguably incorrect in the context of these systems. A lot of confusion stems from using the letters 'KB' to represent binary units which should be denominated as 'KiB' instead. See https://en.wikipedia.org/wiki/Binary_prefix#Inconsistent_use_of_units for more background on this topic.

Example table showing differences between the two numbering systems.

Google Sheets experimental table

There are a number of scripts, formulas and snippets available on StackOverflow, Google discussion forums and other sites, but the majority of them represent data sizes in decimal instead of binary.

• https://gist.github.com/loganvolkers/933af8513ed8c2268f59c85a31761a43

• https://stackoverflow.com/questions/15900485/correct-way-to-convert-size-in-bytes-to-kb-mb-gb-in-javascript/18650828#18650828 – I sourced the math for this formula from this, and the below Google Apps script

It is also possible to use a JavaScript function directly in Sheets which will take your bytes, perform the same math and will produce a text string with KiB on the end.

A custom Google Apps Script: FORMATBYTES()

You can copy the contents of this function, choose File > New > Script file, and paste it there.

That should enable you to use FORMATBYTES() as a function immediately in your Sheets document. This is cleaner than pasting an entire formula.

Downside: this function is noticeably slower. Google Sheets appears to need about a second to retrieve the script and process its value. This means when your Bytes values update, the cell will show "Loading..." for a second and then finally give the value.

It was sufficiently long enough to annoy me to create the above formula. To me, there was also nothing special about the math behind the conversion that mean that Sheets, a program designed for calculations, could not handle.

Google sorely needs to add KiB, MiB and so on, as actual units available as Number formats. This would mean that a user could type bytes into one cell and format that same cell to receive KiB immediately in that same cell. This absence of functionality is astonishing given the wide range of other scientific units that are available.

It also should add these units as part of its CONVERT() function.