How to format numbers as file sizes in Excel

Excel’s number formatting doesn’t support file sizes by default. However, it allows you to program your own formatting style. In this guide, we're going to show you how to format numbers as file sizes in Excel.

Download Workbook

The number formatting is one the most useful features of Excel. Essentially, you can customize how a number in a cell be displayed. Currency signs and thousand-decimal delimiters are popular examples. Along with default number formatting types like currency, date, or ZIP code, you can create your own custom formatting, or even tie them to conditions. For the purposes of this tutorial, we are going to be using a condition to show the correct unit for the corresponding size like 1KB for 1000, 1MB for 1000000, etc.

Example

Let’s see how this works on an example.

Select the cell(s) you want to apply a number formatting.
Press Ctrl + 1 or right-click and select Format Cells.
Make sure the Number tab is selected.
Select Custom for Category.
Enter a custom format into Type [<1000]##0.00" B";[<1000000]##0.00," KB";##0.00,," MB"
Click OK to apply.

Number formatting only supports byte (B), kilobyte (KB), and megabyte (MB) units. However, further options like GB, TB or PB are left out (There is a three condition limit in Excel). Let’s see what the code means.

[<1000]##0.00" B";[<1000000]##0.00," KB";##0.00,," MB"

This number formatting code tells Excel to display numbers less than 1000 with 2 decimals and a “B” character at the end. The rule also states that numbers between 1000 and 1000000 to round to 1000 and finish with “KB”. The numbers greater than 1000000 get “MB”. Each condition is separated by a semi colon character (;).

You can learn more about number formatting codes in Number Formatting in Excel – All You Need to Know

Due to the three-condition limitation, you need to choose 3 file size units that fit your data. Alternatively, you can overcome this limitation by using conditional formatting.

Using conditional formatting to format numbers as file sizes

Thanks to the conditional formatting feature, you can change the number format of a cell based on its value. Thus, you can use a different custom number formatting which includes GB, TB and PB if the cell value is greater than 1,000,000,000.

[<1000000000000]##0.00,,," GB";[<1000000000000000]##0.00,,,," TB";#,##0.00,,,,," PB"

Follow the steps to add a conditional formatting to format numbers as file sizes.