How to Convert Kb to GB in Excel: 6 Best Ways

Do you want to learn unit conversion for file size, especially converting Kb to GB for data analysis? This Excel tutorial will help you all the way!

Often, you face Excel worksheets filled with different data units for files or storage devices. If you plan to use any online unit conversion tool to convert Kb to GB for thousands of rows one at a time, it’ll take years.

In this article, I introduce various methods to change the values of Kb (kilobits) to GB (gigabytes) using formulas and scripts so you can convert thousands of rows in a flash.

Why Do You Need to Know Kb to GB Conversion in Excel?

1. Kilobits is a tiny unit so when you express file size in this unit, you need to accommodate many digits in a cell. Instead, convert the same to gigabytes for better visualization.
2. If the given dataset contains different units of file size, converting all to gigabytes will create a clear pattern of the data.
3. You can also easily estimate data transfer time for a file by converting Kb to GB.
4. Converting kilobits to gigabytes helps analyze network traffic data, such as internet usage statistics, facilitating insights into network performance and resource allocation.

Now, get ready to try out several ways to convert Kb to GB in Excel for faster file size analysis without using any third-party unit converter tool.

Convert Kb to GB in Excel Using a Conversion Factor

The conversion factor for Kb to GB is 800,00,00 in numbers and 8.00E+06 in scientific notation. If you divide the Kb values by the above factor, you get GB.

However, in Excel, the resulting value shows up in scientific notation. So, you must use the TRIM function to express the values in decimal numbers like 0.000128 for 1,024 Kb. Find below the steps and formula:

1. On your Excel worksheet, enter 8.00E+06 in any blank cell and type Conversion Factor as the header name.
2. Beside the Kb column header (column B) create a new column header for GB (column C).
3. In the first cell below the new column, C2, type the following formula and hit Enter:

=TRIM(B2/8000000)

4. Drag the fill handle until the last cell of the column to populate converted values of Kb in the adjacent row.

Using the CONVERT Function

The CONVERT function in Excel can convert various units including bits to bytes. So, you can use this function along with the mathematical operator division to convert Kb to GB in Excel. Here’s how this method works out:

1. Create a new column named File Size in GB to the right side of the File Size in Kb column.
2. Below the new column header, the cell C2, copy and paste the following formula and press the Enter key:

=TRIM(CONVERT(B2,"bit","byte")/1000/1000)

3. Highlight C2 and drag the fill handle in the bottom right corner of the cell until the end to convert hundreds of Kb values to GB in seconds.

Use this POWER Function to Convert Kb to GB in Excel

This method uses the POWER function to raise 1,024 to the power of 2 (1,024^2) and then divides the kilobit value by that result to convert it to gigabits. Then, divide gigabits (Gb) by 8 to get gigabytes (GB).

1. Besides the column where you’ve got all Kb values, create another column for GB.
2. Now, copy and paste the following formula below the new column header:

=TRIM(B2/POWER(1000,2)/8)

3. Press the Enter key to get the calculated value.
4. Use the fill handle to apply the formula across the column to get GB values.

The above formula, B2 is the cell reference for the Kb value. Change this reference according to your own dataset in Excel.

Use a VBA Script to Change Kb to GB in Excel

If you know a little bit about VBA scripting in Excel, you can automate the process of converting Kb to GB in Excel. For this purpose, you can use a simple VBA script. Find below the script as well as the steps to execute the script:

1. Create the following columns to the right of the Kb values column:
   • Scientific Notation in column C
   • File Size in GB in column D
2. Press Alt + F11 to bring up the Excel VBA Editor tool.

3. In the VBA Editor, click Insert and choose Module.
4. Into the new Module, copy and paste this VBA script:

Sub KbtoGBUsingVBA()
    Dim rngSource As Range
    Dim rngResult As Range
    Dim rngTrim As Range
    Dim cell As Range
    
    ' Set the source range
    Set rngSource = Range("B2:B6")
    
    ' Set the result range
    Set rngResult = Range("C2:C6")
    
    ' Set the trim range
    Set rngTrim = Range("D2:D6")
    
    ' Loop through each cell in the source range
    For Each cell In rngSource
        ' Perform the division and put the result in the corresponding cell in the result range
        rngResult.Value = cell.Value / 8000000
        ' Move to the next cell in the result range
        Set rngResult = rngResult.Offset(1)
    Next cell
    
    ' Append the TRIM function to the values in the trim range
    rngTrim.Formula = "=TRIM(C2:C6)"
End Sub

5. In this VBA code, you should make the following adjustments according to your own Excel worksheet:
   • Range("B2:B6"): This is the cell range reference containing the Kb values.
   • Range("C2:C6"): Excel populates Kb to GB values in scientific notation in this range. Change the cell range accordingly, and increase the range if needed.
   • Range("D2:D6"): In this cell range, you get Kb to GB values in numbers instead of scientific notation.
6. Click Save and close the VBA Editor.

7. Now, press Alt + F8 together to open the Macro dialog box and choose the KbtoGBUsingVBA macro.
8. Hit the Run button to execute the macro.

Column D or the one you set in your case, shows the converted values. You can hide column B to declutter the Excel worksheet.

Convert Kb to GB When Importing Data Via Power Query

Are you importing a large dataset containing file size values in Kb (kilobits) and you need to convert that into GB in Excel? You can cut down some clicks and steps by performing the conversion when importing the dataset. For that, you can use the Excel Power Query tool. Here are the steps to use Power Query and the formula to convert the values:

1. On an Excel worksheet, click Data and then select Get Data and choose a medium to import databases. For example, from Azure SQL Database.
2. As soon as you choose the database, the Power Query tool pops up in a new window over the Excel worksheet.

3. There, click the Add Column tab and click Custom Column.
4. On the Custom Column wizard, set a column name like File Size in GB.
5. Then, copy and paste the following formula into the Formula section of the Custom Colum wizard:

=[File Size in Kb]/8000000

6. Click OK and you should see a new column on Power Query showing the GB values for respective Kb values.
7. Click the File tab and hit Close & Load to export the data as is to a new worksheet in your Excel workbook.

How to Use Office Scripts to Change Kb to GB in Excel

At the time of writing, you can’t use VBA scripts on Excel for the web app. So, how can you automate the Kb to GB unit conversion task on the Excel web app?

If you got Microsoft 365 subscription, you can use the Excel Automate or Office Scripts feature. This is an advanced automation tool and is also available on Excel 365 desktop app. Here’s how to use this latest feature to transform Kb to GB in Excel:

1. Suppose, you’ve got an Excel worksheet as per the image shown above and you need the converted values under column C in cells C2 to C6.

2. Click the Automate tab on the Excel ribbon and click New Script.
3. Copy and paste the following script inside the Code Editor panel on the right side of the worksheet:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C1:C2 on selectedSheet
	selectedSheet.getRange("C1:C2").setFormulasLocal([["File Size in GB"],["=TRIM(B2/1000/1000/8)"]]);
	// Paste to range C3:C6 on selectedSheet from range C2 on selectedSheet
	selectedSheet.getRange("C3:C6").copyFrom(selectedSheet.getRange("C2"), ExcelScript.RangeCopyType.all, false, false);
}

4. Click Save script and then hit the Run button.
5. Excel Automate will create a new column File Size in GB in column C and populate the values between the cell range C2:C6.

If the Kb values are in a different column in your Excel worksheet, modify the input cell address B2 in the ["=TRIM(B2/1000/1000/8)"] code to an appropriate cell address. This should be the starting row of the Kb values, like A2, C2, D2, etc.

Suppose, column C is occupied with other data. You want the results under a different column like column D. Then replace all the occurrences of column C in the above code. For example, change codes like C1:C2 to D1:D2, C3:C6 to D3:D6, and C2 to D2, and so on.

Also, don’t forget to stretch the cell range from C3:C6 to C3:C100 if there are that many rows or more according to your Excel worksheet.

The script automates the task of dividing Kb values by 1,000 to get megabits. Then, divide megabits by 1,000 to get gigabits. Finally, divide gigabits by 8 to get Gigabytes.

Conclusions

If you find unit conversion like converting kilobits (kb) to gigabytes (GB) a hectic task, utilize the methods explained so far.

When you need to do the conversion for one or two worksheets with a few rows of data, use the methods based on the conversion factor, POWER function, or CONVERT function.

Alternatively, if the dataset is quite large and there are a few thousand rows for Kb values, you should use the VBA script or Power Query-based method.

Office Scripts-based method is also good for a large dataset and offers better automation options. But, you may only use that if you’ve got the necessary Microsoft 365 plan.

Have ever needed to convert between Kb and Gb in your Excel sheets? Let me know in the comments!