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

A_one_line_formula.md

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

B_about.md

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.

How to use it

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

Why use it

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.

Experiment live with the formula

Google Sheets experimental table

Scripts available on the Internet

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.

Decimal units

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

Binary units

• 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

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.

Why do we have to do this?

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.

C_example_results_table.md

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

Awesome, thank you!

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.