[excel] 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.

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

For the exact result, I'd rather calculate it, but 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

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.

Here is one that I have been using: -

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

Seems to work fine.

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.

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.

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

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

0.00,,,"Gb"

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"

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

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!

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

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