Here is a great tip for formatting numbers in Excel as Gigabytes, Megabytes, Kilobytes, Bytes, etc.

Use the following formula:

```
=IF((A1>=POWER(2,30)),
TEXT((A1/POWER(2,30)),"##0.00\G"),
IF((A1>=POWER(2,20)),
TEXT((A1/POWER(2,20)),"##0.00\M"),
IF((A1>=1024),
TEXT((A1/1024),"##0.00\K"),
TEXT((A1),"##0.00\B")
)
)
)
```

The same formula will work for Gigabits, Megabits, etc. assuming you start with bits instead of bytes. If you want to convert from bits to bytes in the process, use this formula:

```
=IF((A1>=POWER(2,33)),
TEXT((A1/POWER(2,33)),"##0.00\G"),
IF((A1>=POWER(2,23)),
TEXT((A1/POWER(2,23)),"##0.00\M"),
IF((A1>=8192),
TEXT((A1/8192),"##0.00\K"),
TEXT((A1/8),"##0.00\B")
)
)
)
```

thank you…saved me hours…thanks again..

You are good

THANKYOU!

Keep in mind, those formulas will actually give you values in gibibytes (GiB), mebibytes (MiB), and kibibytes (KiB), not gigabytes (GB), megabytes (MB), and kilobytes (KB) as suggested. You might want to modify the formula with the proper abbreviations if your working with storage professionals or others to whom the difference may be important.

See http://en.wikipedia.org/wiki/Kibibyte and http://en.wikipedia.org/wiki/Binary_prefix for more information.

Amazing post!

or use following custom number format

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

Would like to get this same formula but with bytes as well. I need B, KB, MG, GB,

Here are the same functions, and more, but all are working in all spreadsheet programs, not only those supporting the TEXT() function as microsoft implement it.

http://fsteff.blogspot.com/2012/02/use-si-number-notation-in-spreadsheets.html

These formulas are a great help in formatting numbers in excel as Gigabytes, Megabytes, and Kilobytes etc. The article has been a great

help and now I have find it more easier using the website Bytes

to Megabytes Converter