Quick tip – Human readable folder sizes in Excel

From the school of There’s Probably a Better Way, But…

I was taking the output of SysInternals’ du.exe utility and wanted to email it to a colleague, but the folder sizes were in KB. Since most of them were many Gigabytes in size, I wanted a quick way to convert them to the appropriate size in GB, MB, or KB.

I copied the du.exe size column and pasted it into Excel. Then I created the following formula:

=IF(E6>(2^20),TEXT(E6/(2^20),"0.0") & " GB","smaller")

…where E6 is the cell reference. Also note that du.exe’s output was in KB, so my test is one order of magnitude smaller than it would need to be if I was formatting a size in bytes. (i.e., 2^20 is 1 MB).

I then nested another similar if() function with a final formatting option to get my final formula:

=IF(E6>(2^20),TEXT(E6/(2^20),"0.0") & " GB",IF(E6>(2^10),TEXT(E6/(2^10),"0.0") & " MB",TEXT(E6,"0.0") & " KB"))

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.