How to Create Shipping Cost Calculator in Excel: 3 Methods

Method 1 – Gather Data for Shipping Cost per Unit with Tax

The dataset contains the shipping details of a shipping corporation if anyone wants any shipment for his/her products. The following dataset has four columns titled Weight of Item in Kg, Shipping Cost Per kg, amount of Tax, and Net Shipping Cost/Kg. We have yet to say that I have been working with the Microsoft Excel 365 version for this post. You are free to use any other edition that will provide you with the most convenience.

sample dataset to calculate cost of shipment

Method 2 – Calculate Net Shipping Cost per Kg Considering Taxes

Select cell E5.
Write down the following formula.

=C5+C5*D5

Press the Enter key from the keyboard.
Utilize the AutoFill tool to the entire column to find the Net Shipping Cost per Kg.

Method 3 – Determine the Total Shipping Cost

Insert a Consignment Shipping Weight in cell E13. You can provide any number according to the product weight in Kg. We provided 21 kg.
Click cell E14.
Insert the formula mentioned below. We used the VLOOKUP formula here.

=VLOOKUP(E13,B5:E11,4,1)*E13

Formula Breakdown:

E13 is the consignment shipping weight for which you are likely to calculate the shipping cost.
Then, range B5:E11 is the table array where the shipping cost is given for a particular amount of consignment weight. Additionally, it provides net shipping cost/kg including the amount of tax.
Next, 4 indicates the column index number from which the value will be returned. Here, I have used 4 to select the Net Shipping Cost/Kg column.
The last argument then determines whether the function will return an exact match or an approximate match. Here, 1 indicates an exact match.
Multiplying the shipment weight provides the total shipping cost with tax as $319.

Hit the Enter button to find the total shipping cost including tax.

Final Output

You will get your desired output. The output will show you the shipping cost for a particular weight. It includes the tax. It is the total shipping cost according to the weight of your product.

Things to Remember

First of all, you can choose any weight and corresponding cost per Kg according to your company regulations.
Then, the Tax amount may vary for various countries or regions. So, put the amount according to your regional rules and regulations.
Next, you can change the dataset according to your preference.
Afterward, you can add extra charges if needed.
At last, you have to change the Consignment Shipping Weight to get the Total Cost according to your items. However, this calculator will always show you the net cost for your consignment.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Shipping Cost Calculator.xlsx

<< Go Back to Cost Calculator | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!