Have you ever had a list of files with accompanying file sizes that you wanted to use in some sort of calculation such as adding up the file sizes, averaging them, using them in some sort of chart or other calculation? The way we usually read file sizes is with the appropriate size abbreviation such as B (bytes), KB (kilobytes), MB (megabytes), etc. However, this makes it tricky to work with in an Excel spreadsheet. This tutorial will show you how to format numbers as file sizes.
Why Format Numbers as File Sizes
If you wanted to have a spreadsheet that lists out file sizes like 10 MB, you may enter 10 MB in a cell. If you ever wanted to do anything with that information, such as add up file sizes, or anything else, the letters “MB” in that cell would make it difficult to do. On the other hand, if you only had a number in the cell that was formatted to display the appropriate file size abbreviation after the number you would be able to perform any calculations you wanted with that data.
For purposes of this example, I am going to assume that all the numbers we are working with are in bytes and we want to display them in KB, MB, or GB as appropriate.
Also, for reference here is a guide to convert bytes to KB, MB or GB.
- 1,000 bytes = 1 kilobyte
- 1,000,000 bytes = 1 megabyte
- 1,000,000,000 bytes = 1 gigabyte
Click on a cell that contains the file size in bytes. Then from the Home Tab, Number Group, select More Number Formats from the drop down menu.
Next, you will be on the Format Cells window. From here you can select “Custom” from the left hand side under Category, and enter the following custom format in the box that says “Type”.
Once you have entered the format, click OK.
This formatting will display the bytes in a more readable format by adding the appropriate KB, MB, or GB ending to the number.
**UPDATE** Thanks to Dennis in the comments, who pointed out that this is the American format. Other countries format numbers with different decimal and thousands separators than us here in America. If you use a comma (,) instead of a period (.) as the decimal marker you can simply replace the period with a comma in between the zeros in the code above. So 0.00 would become 0,00 in your case.
For example, if you entered 1000 in a cell, this custom format will display 1.00 KB. 1000000 would display 1.00 MB, etc.
You can use the same concept for other data sizes, but I chose some of the more common formats for purposes of this example. If you are unsure what conversion to use for your example, you can always do a Google search for the conversion you are looking for. For example a search for "1 terabyte in bytes", would tell you it is equivalent to 1000000000000 bytes. Using the dropdowns on the search results page you can jump between formats as well.
Here are some more helpful hints for other formatting tricks in Excel.