Format Excel numbers as GB, MB, KB, B

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")
               )
         )
    )
Be Sociable, Share!

10 comments for “Format Excel numbers as GB, MB, KB, B

  1. Naz Ahmed
    May 14, 2009 at 8:10 am

    thank you…saved me hours…thanks again..

    • Izzal Ahmed
      November 17, 2011 at 8:37 am

      You are good

  2. Anonymous
    October 23, 2009 at 1:46 am

    THANKYOU!

  3. Justin
    November 23, 2009 at 3:22 pm

    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.

  4. Antonio
    February 15, 2011 at 8:38 am

    Amazing post!

  5. Anunay
    September 2, 2011 at 10:19 am

    or use following custom number format

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

    • Eric
      February 16, 2017 at 4:37 pm

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

  6. Flemming Steffensen
    February 19, 2012 at 1:26 pm

    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

  7. Jounayd D
    February 11, 2015 at 2:46 am

    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

Leave a Reply

Your email address will not be published.