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

User · Answer

Less than Tera will write on GB  amp  more than 999 GB write on TB    lt 1000 0  GB    999 0 0   TB   OR    lt 1000 0  GB     1000 0 0   TB

User · Answer

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    gt 999999999999     0 000      TB    gt 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

User · Answer

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     lt 1000   0 00   B    lt 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     lt 1000000000000   0 00     GB    lt 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

User · Answer

Paste this next to your values bytes  and this will automatically change it to whatever your size of value is    IF G10 gt  1099511627776 CONCATENATE ROUND  G10 1024 1024 1024 1024  1    TB   IF G10 gt  1073741824 CONCATENATE ROUND  G10 1024 1024 1024  1    GB   IF G10 gt  1048576 CONCATENATE ROUND  G10 1024 1024  1    MB   IF G10 gt  1024 CONCATENATE ROUND  G10 1024  1    KB   IF G10 gt  1 CONCATENATE  G10    BYTES   0

User · Answer

Here is one that I have been using  -    lt 1000000 0 00   KB    lt 1000000000 0 00    MB  0 00     GB    Seems to work fine

User · Answer

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    lt 1000    KB     lt 1000000  0 00   MB  0       GB     Initial Value  in KB     Output  952    952 KB  1514    1 51 MB  5122323    5 12 GB

User · Answer

For the exact result  I d rather calculate it  but using display format   Assuming A1 cell has value 29773945664927    Count the number of commas in B1 cell    QUOTIENT LEN A1 -1 3  Divide the value by 1024 B1 in C1 cell    A1 1024 B1 Display unit in D1 cell    SWITCH B1  5   PB   4   TB   3   GB   2   MB  1   KB  0   B   Hide B1 cell       screenshot

User · Answer

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     lt 1000000     0 00   KB    lt 1000000000     0 00    MB      0 00     GB     gt 999999999999     0 00      TB    gt 999999999     0 00     GB      0 00    MB     lt 1000000     0 00   KB    lt 1000000000     0 00    MB      0 00     GB     gt 999999999999     0 00      TB    gt 999999999     0 00     GB      0 00    MB    Take your pick

User · Answer

After seeing the answers here just improved on this formula to have decimal places on bigger values and cater for negative values    IF A1 lt 999500000000 TEXT A1        0      TB      IF A1 lt -9995000000 TEXT A1        0      GB      IF A1 lt -9995000 TEXT A1      0     MB      IF A1 lt -9995 TEXT A1      0    KB      IF A1 lt -1000 TEXT A1      0   B       IF A1 lt 0 TEXT A1      0   B       IF A1 lt 1000 TEXT A1      0   B       IF A1 lt 999500 TEXT A1      0    KB      IF A1 lt 999500000 TEXT A1      0     MB      IF A1 lt 999500000000 TEXT A1        0      GB      TEXT A1        0       TB

User · Answer

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

User · Answer

Above formula requires a minus sign in the first line    IF A1 lt -999500000000     IF A1 lt -999500000000 TEXT A1        0      TB      IF A1 lt -9995000000 TEXT A1        0      GB      IF A1 lt -9995000 TEXT A1      0     MB      IF A1 lt -9995 TEXT A1      0    KB      IF A1 lt -1000 TEXT A1      0   B       IF A1 lt 0 TEXT A1      0   B       IF A1 lt 1000 TEXT A1      0   B       IF A1 lt 999500 TEXT A1      0    KB      IF A1 lt 999500000 TEXT A1      0     MB      IF A1 lt 999500000000 TEXT A1        0      GB      TEXT A1        0       TB

User · Answer

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     lt 500000     0  B    lt 500000000     0    MB      0     GB

User · Answer

Slight change to make it work on my region  Europe    as thousands separator  comma as decimal separator      lt 1000000     0 00  KB    lt 1000000000     0 00    MB      0 00     GB    Still same issue on data conversion  1000    1024  but it does the job for me

User · Answer

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     gt 1099511627776          quot  TiB quot    gt 1073741824         quot  GiB quot  0      quot  MiB quot   This supports MiB  GiB  and TiB showing two decimal places

User · Answer

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

User · Answer

It is a bit of a  brute force  but works      IF E4 1000 lt 1 CONCATENATE E4   bps   IF E4 1000 lt 1000 CONCATENATE ROUND E4 1000 2    kbps   IF E4 1000000 lt 1000 CONCATENATE ROUND E4 1000000 2    mbps   IF E4 1000000000 lt 1000 CONCATENATE ROUND E4 1000000000 2    gbps

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

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

Examples related to excel

Examples related to formatting