=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")
-
-
Save seb26/e3837b36ddb5c4b945e3ef40c4e48b19 to your computer and use it in GitHub Desktop.
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.
- Copy the formula above
- Paste the formula into your helper cell
- 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 often representing it in binary units (or IEC units) is the most correct format for that use case. However, it depends on the operating system or filesystem which may be relevant.
For example, if your spreadsheet contains records of file transfers made on macOS, it will be more accurate and closer to Finder's approximations if you represent in decimal format (i.e. not using this formula). This is because macOS since 2009's Snow Leopard has used the decimal format to display sizes. Conversely, Windows will display file sizes in binary format, but labelled as 'KB' rather than 'KiB'. So if your use case predominately involves Windows systems, it may make sense to use this formula.
A lot of confusion stems from using the units 'KB', 'MB', and so on, to incorrectly represent binary units, which should be denominated as 'KiB' instead. Further confusion stems from whether to use decimal or binary units in the first place. See https://en.wikipedia.org/wiki/Binary_prefix for more background on this topic.
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://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 achieve the same effect but with the math written in JavaScript.
A custom Google Apps Script: FORMATBYTES()
You can copy the contents of this function, choose File > New > Script file, and paste it in.
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.
Bytes | Formula: =A2/(1024)^(FLOOR(log(A2)/log(1024))) | FLOOR(log(A2)/log(1024) | =SWITCH( FLOOR( log(A2) / log(1024) ) ,0,"Bytes",1,"KiB",2,"MiB",3,"GiB",4,"TiB") | All in one | All in one rounded to 2 decimal |
---|---|---|---|---|---|
512 | 512 | 0 | Bytes | 512 Bytes | 512 Bytes |
1024 | 1 | 1 | KiB | 1 KiB | 1 KiB |
2048 | 2 | 1 | KiB | 2 KiB | 2 KiB |
4096 | 4 | 1 | KiB | 4 KiB | 4 KiB |
1,048,576 | 1 | 2 | MiB | 1 MiB | 1 MiB |
36,068,183 | 34.39729977 | 2 | MiB | 34.3972997665405 MiB | 34.4 MiB |
148,503,136 | 141.6236267 | 2 | MiB | 141.623626708984 MiB | 141.62 MiB |
1,377,849,741 | 1.283222568 | 3 | GiB | 1.28322256822139 GiB | 1.28 GiB |
4,242,623,877 | 3.951251392 | 3 | GiB | 3.95125139225274 GiB | 3.95 GiB |
24,935,584,458 | 23.22307272 | 3 | GiB | 23.2230727169663 GiB | 23.22 GiB |
27,298,486,746 | 25.42369696 | 3 | GiB | 25.4236969593912 GiB | 25.42 GiB |
27,298,486,746 | 25.42369696 | 3 | GiB | 25.4236969593912 GiB | 25.42 GiB |
Super useful! This doesn't accommodate negative sizes (perhaps for a file that has shrunk). Changing to
=ROUND( A2/(1024)^(FLOOR(log(ABS(A2))/log(1024))), 2) & " " & SWITCH( FLOOR( log(ABS(A2)) / log(1024) ) ,0,"Bytes",1,"KiB",2,"MiB",3,"GiB",4,"TiB")
(throwing ABS
into the logarithms) takes care of that.
Awesome, thank you!