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

Question

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.

Answer 1

I prefer calculating rather than using display format for accurate results.

TL;DR; [One Liner]

=ROUND(A1/1024^(QUOTIENT(LEN(INT(A1))-1,3)),2) & SWITCH((QUOTIENT(LEN(INT(A1))-1,3)), 5," PB", 4," TB", 3," GB", 2," MB",1," KB",0," B")

Assuming A1 cell has value 29773945664927.

1. Count the number of commas, put it into B1 cell.

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

2. Divide the value by 1024^B1, put it into C1 cell.

    =A1/1024^B1
   

3. Place the display unit separately into cell D1.

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

4. Hide B1 cell.

screenshot

Answer 2

My goto formula for this after many years of refinement is the here.

You can use the beginning variable definitions to define aspects of the conversion:

• cell defines the cell containing the bytes to format
• cell_style defines if you're using the R1C1 (rowcolumn) or the A1 (alphanumeric) cell reference style
• unit_type defines if you want the resulting output calculated to a binary or metric value

=LET(

README_,"This formula formats a byte count, rounding it to its significant digit and to two decimal places, applying the appropriate unit of measurement",

README1,"Adjust the below [variable] definitions to your need, where the viable options are (bracketed)",
README2,"[cell]       specify the individual cell address containing the byte count to format, this should be contained within double quotation marks",
README3,"[cell_style] define if you're using the R1C1 (rowcolumn) or the A1 (alphanumeric) cell reference style",
README4,"[unit_type]  define if you want the resulting output calculated to a base 2 (binary) or base 10 (metric) value",

CONSTANTS,"Do not alter these",
binary,1024,
metric,1000,
rowcolumn,0,
alphanumeric,1,

VARIABLES,"Adjust these to your need",
cell,"RC[-1]",
unit_type,binary,
cell_style,rowcolumn,

IF(N("Error if the speficied cell is not a number")+
NOT(ISNUMBER(INDIRECT(cell,cell_style))),"#MISSINGNO",

IF(unit_type=1000
+N("Calculate the result at base 10"),

IF((INDIRECT(cell,cell_style)>=1000^8),
TEXT((INDIRECT(cell,cell_style)/1000/1000/1000/1000/1000/1000/1000/1000),"#,## ????.00 \Y\B"),
IF((INDIRECT(cell,cell_style)>=1000^7),
TEXT((INDIRECT(cell,cell_style)/1000/1000/1000/1000/1000/1000/1000),"#,## ????.00 \Z\B"),
IF((INDIRECT(cell,cell_style)>=1000^6),
TEXT((INDIRECT(cell,cell_style)/1000/1000/1000/1000/1000/1000),"#,## ????.00 \E\B"),
IF((INDIRECT(cell,cell_style)>=1000^5),
TEXT((INDIRECT(cell,cell_style)/1000/1000/1000/1000/1000),"#,## ????.00 \P\B"),
IF((INDIRECT(cell,cell_style)>=1000^4),
TEXT((INDIRECT(cell,cell_style)/1000/1000/1000/1000),"#,## ????.00 \T\B"),
IF((INDIRECT(cell,cell_style)>=1000^3),
TEXT((INDIRECT(cell,cell_style)/1000/1000/1000),"#,## ????.00 \G\B"),
IF((INDIRECT(cell,cell_style)>=1000^2),
TEXT((INDIRECT(cell,cell_style)/1000/1000),"#,## ????.00 \M\B"),
IF((INDIRECT(cell,cell_style)>=1000^1),
TEXT((INDIRECT(cell,cell_style)/1000),"#,## ????.00 \K\B"),
IF(INDIRECT(cell,cell_style)=1,
"1 B",TEXT(INDIRECT(cell,cell_style),"## ????0 \B")
))))))))),

IF(unit_type=1024
+N("Calculate the result at base 2"),

IF((INDIRECT(cell,cell_style)>=POWER(2,80)),
TEXT((INDIRECT(cell,cell_style)/POWER(2,80)),"#,## ????.00 \Y\i\B"),
IF((INDIRECT(cell,cell_style)>=POWER(2,70)),
TEXT((INDIRECT(cell,cell_style)/POWER(2,70)),"#,## ????.00 \Z\i\B"),
IF((INDIRECT(cell,cell_style)>=POWER(2,60)),
TEXT((INDIRECT(cell,cell_style)/POWER(2,60)),"#,## ????.00 \E\i\B"),
IF((INDIRECT(cell,cell_style)>=POWER(2,50)),
TEXT((INDIRECT(cell,cell_style)/POWER(2,50)),"#,## ????.00 \P\i\B"),
IF((INDIRECT(cell,cell_style)>=POWER(2,40)),
TEXT((INDIRECT(cell,cell_style)/POWER(2,40)),"#,## ????.00 \T\i\B"),
IF((INDIRECT(cell,cell_style)>=POWER(2,30)),
TEXT((INDIRECT(cell,cell_style)/POWER(2,30)),"#,## ????.00 \G\i\B"),
IF((INDIRECT(cell,cell_style)>=POWER(2,20)),
TEXT((INDIRECT(cell,cell_style)/POWER(2,20)),"#,## ????.00 \M\i\B"),
IF((INDIRECT(cell,cell_style)>=1024),
TEXT((INDIRECT(cell,cell_style)/1024),"#,## ????.00 \K\i\B"),
IF(INDIRECT(cell,cell_style)=1,
"1 B",TEXT(INDIRECT(cell,cell_style),"## ????0 \B")
)))))))))))))

If you ever want to do the reverse, and derive a rough raw count of bytes from a pre-formatted value, I've got a formula for that too.

Answer 3

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

0.00,,,"GB"

Answer 4

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 =CONVERT(Number or Cell, "fromDatatype", "toDatatype")

So for example =CONVERT(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

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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"

Answer 9

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

Answer 10

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

Answer 11

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.

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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.

Answer 17

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"

Answer 18

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!

Answer 19

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

Answer 20

Here is one that I have been using: -

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

Seems to work fine.