How can I format bytes a cell in Excel as KB, MB, GB etc?

I have a value in a cell that's in bytes. But nobody can read 728398112238. I'd rather it say 678.37GB

To write a formula to format it relatively easy (here's one: http://www.yonahruss.com/2007/02/format-excel-numbers-as-gb-mb-kb-b.html)

But is there any way to do this as a 'format'? I'd like to have the big number in the cell, but have it display as the human-readable format.

You can't really do calculations in the formatting features of Excel. You can use something like the following to do a rough estimation though:

[<500000]#,##0" B";[<500000000]#,##0,," MB";#,##0,,," GB"

Here is one that I have been using: -

[<1000000]0.00," KB";[<1000000000]0.00,," MB";0.00,,," GB"

Seems to work fine.

The above formatting approach works but only for three levels. The above used KB, MB, and GB. Here I've expanded it to six. Right-click on the cell(s) and select Format Cells. Under the Number tab, select Custom. Then in the Type: box, put the following:

[<1000]##0.00"  B";[<1000000]##0.00," KB";##0.00,," MB"

Then select OK. This covers B, KB, and MB. Then, with the same cells selected, click Home ribbon, Conditional Formatting, New Rule. Select Format only cells that contain. Then below in the rule description, Format only cells with, Cell Value, greater than or equal to, 1000000000 (that's 9 zeros.) Then click on Format, Number tab, Custom, and in the Type: box, put the following:

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

Select OK, and OK. This conditional formatting will take over only if the value is bigger than 1,000,000,000. And it will take care of the GB, TB, and PB ranges.

567.00  B
  5.67 KB
 56.70 KB
567.00 KB
  5.67 MB
 56.70 MB
567.00 MB
  5.67 GB
 56.70 GB
567.00 GB
  5.67 TB
 56.70 TB
567.00 TB
  5.67 PB
 56.70 PB

Anything bigger than PB will just show up as a bigger PB, e.g. 56,700 PB. You could add another conditional formatting to handle even bigger values, EB, and so on.

Though Excel format conditions will only display 1 of 3 conditions related to number size (they code it as "positive; negative; zero; text" but I prefer to see it as : if isnumber and true; elseif isnumber and false; elseif number; elseif is text )

so to me the best answer is David's as well as Grastveit's comment for other regional format.

Here are the ones I use depending on reports I make.

[<1000000]#,##0.00," KB";[<1000000000]#,##0.00,," MB";#,##0.00,,," GB"

[>999999999999]#,##0.00,,,," TB";[>999999999]#,##0.00,,," GB";#.##0.00,," MB"

[<1000000]# ##0,00 " KB";[<1000000000]# ##0,00  " MB";# ##0,00   " GB"

[>999999999999]# ##0,00    " TB";[>999999999]# ##0,00   " GB";# ##0,00  " MB"

Take your pick!

Slight change to make it work on my region, Europe (. as thousands separator, comma as decimal separator):

[<1000000]#.##0,00" KB";[<1000000000]#.##0,00.." MB";#.##0,00..." GB"

Still same issue on data conversion (1000 != 1024) but it does the job for me.

I don't know of a way to make it show you binary gigabytes (multiples of 102410241024) but you can make it show you decimal gigabytes using a format like:

0.00,,,"Gb"

Above formula requires a minus sign in the first line: "=IF(A1<-999500000000"

=IF(A1<-999500000000,TEXT(A1,"#,##.#0,,,"" TB"""),
IF(A1<-9995000000,TEXT(A1,"#,##.#0,,,"" GB"""),
IF(A1<-9995000,TEXT(A1,"#,##0,,"" MB"""),
IF(A1<-9995,TEXT(A1,"#,##0,"" KB"""),
IF(A1<-1000,TEXT(A1,"#,##0"" B """),
IF(A1<0,TEXT(A1,"#,##0"" B """),
IF(A1<1000,TEXT(A1,"#,##0"" B """),
IF(A1<999500,TEXT(A1,"#,##0,"" KB"""),
IF(A1<999500000,TEXT(A1,"#,##0,,"" MB"""),
IF(A1<999500000000,TEXT(A1,"#,##.#0,,,"" GB"""),
TEXT(A1,"#,##.#0,,,,"" TB""")))))))))))

And, yet another solution, is to use engineering notation. (That's like scientific notation except the exponent is always a multiple of 3.) Right-click on the cell(s) and select Format Cells. Under the Number tab, select Custom. Then in the Type: box, put the following:

##0.00E+00

Then click OK. Instead of K, M, etc, you'll have +3, +6, etc. This will work for positive and negative numbers, as well as positive and negative exponents, -3 is m, -6 is u, etc.

567.00E-06
  5.67E-03
 56.70E-03
567.00E-03
  5.67E+00
 56.70E+00
567.00E+00
  5.67E+03
 56.70E+03
567.00E+03
  5.67E+06

Less than Tera will write on GB & more than 999 GB write on TB

[<1000]0" GB";[>999]0.0," TB"

OR

[<1000]0" GB";[>=1000]0.0," TB"

Paste this next to your values(bytes) and this will automatically change it to whatever your size of value is.

=IF(G10>=1099511627776,CONCATENATE(ROUND((G10/1024/1024/1024/1024),1)," TB"),IF(G10>=1073741824,CONCATENATE(ROUND((G10/1024/1024/1024),1)," GB"),IF(G10>=1048576,CONCATENATE(ROUND((G10/1024/1024),1)," MB"),IF(G10>=1024,CONCATENATE(ROUND((G10/1024),1)," KB"),IF(G10>=1,CONCATENATE((G10)," BYTES"),0)))))

All the answers here supply values with powers of 10. Here is a format using proper SI units (multiples of 1024, i.e. Mebibytes, Gibibytes, and Tebibytes):

[>1099511627776]#.##,,,," TiB";[>1073741824]#.##,,," GiB";0.##,," MiB"

This supports MiB, GiB, and TiB showing two decimal places.

I use CDH hadoop and when I export excel report, I have two problems;

1. convert Linux date to excel date,
   For that, add an empty column next to date column lets say the top row is B4, paste below formula and drag the BLACK "+" all the way to your last day at the end of the column. Then hide the original column

   =(((B4/1000/60)/60)/24)+DATE(1970|1|1)+(-5/24)

2. Convert disk size from byte to TB, GB, and MB
   the best formula for that is this

   [>999999999999]# ##0.000,,,," TB";[>999999999]# ##0.000,,," GB";# ##0.000,," MB"

it will give you values with 3 decimals just format cells --> Custom and paste the above code there

It is a bit of a "brute force" but works ;)

=IF(E4/1000<1;CONCATENATE(E4;" bps");IF(E4/1000<1000;CONCATENATE(ROUND(E4/1000;2);" kbps");IF(E4/1000000<1000;CONCATENATE(ROUND(E4/1000000;2);" mbps");IF(E4/1000000000<1000;CONCATENATE(ROUND(E4/1000000000;2);" gbps")))))

Because nobody wrote this answer yet I thought it would be good for future thread finders. There is an easy way to do conversions in excel for bits and byte with the conversion function.

You just need to use =CONVERSION(Number or Cell, "fromDatatype", "toDatatype")

So for example =CONVERSION(5, "Gbyte", Mbyte")

You can find the doc about this under Measurement sytems -> Information https://support.microsoft.com/en-us/office/convert-function-d785bef1-808e-4aac-bdcd-666c810f9af2

Sadly there is no chart about the prefixes like M, Mi, G or Gi. But it's easy. Just the common prefix and then bit respectively byte.

Prefix	Short	bit	byte
Yotta	Y	Ybit	Ybyte
Zetta	Z	Zbit	Zbyte
Exa	E	Ebit	Ebyte
Peta	P	Pbit	Pbyte
Tera	T	Tbit	Tbyte
Giga	G	Gbit	Gbyte
Mega	M	Mbit	Mbyte
Kilo	k	kbit	kbyte
Hekto	h	hbit	hbyte
Deka	da	dabit	dabyte
Yobi	Yi	Yibit	Yibyte
Zebi	Zi	Zibit	Zibyte
Exbi	Ei	Eibit	Eibyte
Pebi	Pi	Pibit	Pibyte
Tebi	Ti	Tibit	Tibyte
Gibi	Gi	Gibit	Gibyte
Mebi	Mi	Mibit	Mibyte
Kibi	ki	kibit	kibyte

After seeing the answers here just improved on this formula to have decimal places on bigger values and cater for negative values.

=IF(A1<999500000000,TEXT(A1,"#,##.#0,,,"" TB"""),
IF(A1<-9995000000,TEXT(A1,"#,##.#0,,,"" GB"""),
IF(A1<-9995000,TEXT(A1,"#,##0,,"" MB"""),
IF(A1<-9995,TEXT(A1,"#,##0,"" KB"""),
IF(A1<-1000,TEXT(A1,"#,##0"" B """),
IF(A1<0,TEXT(A1,"#,##0"" B """),
IF(A1<1000,TEXT(A1,"#,##0"" B """),
IF(A1<999500,TEXT(A1,"#,##0,"" KB"""),
IF(A1<999500000,TEXT(A1,"#,##0,,"" MB"""),
IF(A1<999500000000,TEXT(A1,"#,##.#0,,,"" GB"""),
TEXT(A1,"#,##.#0,,,,"" TB""")))))))))))

I suspect a lot of the answers here are outdated, as I did not get the expected result from the given answer.

If you have value in KB that you would like to format according to the size, you can try the following.

---

Formula

[<1000]#" KB ";[<1000000]#0,00 " MB";0,## " GB"

---

Initial Value (in KB) => Output

952 => 952 KB

1514 => 1.51 MB

5122323 => 5.12 GB

For the exact result, I'd rather calculate it than using display format.

Assuming A1 cell has value 29773945664927.

1. Count the number of commas in B1 cell.

   =QUOTIENT(LEN(A1)-1,3)

2. Divide the value by 1024^B1 in C1 cell.

   =A1/1024^B1

3. Display unit in D1 cell.

   =SWITCH(B1, 5," PB", 4," TB", 3," GB", 2," MB",1," KB",0," B")

4. Hide B1 cell.

> screenshot

I like the last version shared but I wanted share the version for French Excel.

I have replaced QUOTIENT by a ROUND function, to have some digits after the decimal point, because that is what I need:

=SI(
   F3<1024; F3 & " B"; 
      SI(F3<(1024*1024); ARRONDI(F3/1024;2) & " KB"; 
          SI(F3<(1024*1024*1024); ARRONDI(F3/(1024*1024);2) & " MB";
               SI(F3<(1024*1024*1024*1024); ARRONDI(F3/(1024*1024*1024);2) & " GB";
                    SI(F3<(1024*1024*1024*1024*1024); ARRONDI(F3/(1024*1024*1024*1024);2)& " TB"; "H")
               ) 
           )
       )
   )

If you want to work on one cell. Lets say cell F3, you can use this:

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

This has been tried on google sheets. IF ELSE caluse might need modifications