How to Make a Price List in Excel (Step by Step Guidelines)

Displaying product information such as Unit Price, Product Name, Total Price Price List plays a pivot role, and this article will give you the scopes to make a Price List in Excel easily.

Furthermore, you will be able to download the free template of a Price List and by changing the input values you will get an updated Price List for your company.

Download Workbook


Procedures to Make a Price List in Excel

Here, we will discuss the ways of forming a Price List format thoroughly for the XYZ company. After completing all of these steps, we will be able to make a Price List for the products of this company.


Step-01: Making an Outline of Price List Template

➤ First, we need to have the basic inputs such as the Product Details in the Data sheet and the Product Details contains the Product Code, Product Name, Unit Price, and VAT of the products.

how to make a price list in Excel

➤ After that, we have created the basic outline of the Price List for “XYZ” company.

how to make a price list in Excel

➤ Now, it is time to give some basic fixed inputs in the required places. So, we have filled up the company details like its name, address, contact number, etc but you can add more information if necessary in that place.

making outline

➤ For having the date of the creation of the Price List use the TODAY function.

making outline

➤ If you want to add some more information like Discounts, you can add it below the list of prices.

making outline


Step-02: Creating Dropdown to Make a Price List in Excel

For entering the Product Code easily by selecting from a list, you can create a dropdown list like this step.

➤ Select the cells of the column Product Code where you want to have the dropdown list.
➤ Go to Data Tab >> Data Tools Group >> Data Validation Option.

how to make a price list in Excel

After that, the Data Validation dialog box will pop up
➤ Select the List option in the Allow box
➤ Type the following formula in the Source box and press OK

=Data!$B$5:$B$13

Here, Data! is the sheet name and $B$5:$B$13 is the range containing the Product Code numbers in that sheet for different products.

Dropdown list

Finally, you will get the dropdown sign in the cells of the Product Code column and now, you can choose any of the Product Codes from this list.

Dropdown list
➤ We have selected Product Code 801 in cell B9 and,

 

how to make a price list in Excel

then Product Code 807 in cell B9.

Dropdown list

Similarly, select the codes from the list for the rest of the cells.

how to make a price list in Excel


Similar Readings


Step-03: Using Formulas to Make a Price List in Excel

By using formulas, we can upgrade the Price List template easily by only giving some input values.

➤ Enter the following formula in cell C9 and drag down the Fill Handle tool.

=IFERROR(VLOOKUP(B9,Data!$B$4:$E$13,2,FALSE),"")

Here, B9 is the lookup value, Data!$B$4:$E$13 is the table array where Data! is the sheet name, 2 is the column number of the column Product Name and FALSE is for an exact match.

If sometimes VLOOKUP returns an error then IFERROR will convert it into a Blank.

how to make a price list in Excel

In this way, we are getting all of the Product Names for the corresponding Product Codes in the Product column.

using formula

Similarly, you can have the values of the Unit Prices and the VAT for the corresponding Product Codes in the Unit Price and VAT columns respectively by using the following formulas,

=IFERROR(VLOOKUP(B9,Data!$B$4:$E$13,3,FALSE),"")

using formula

=IFERROR(VLOOKUP(B9,Data!$B$4:$E$13,4,FALSE),"")

using formula

➤ Enter the total number of each product in the Qty column.

how to make a price list in Excel

➤ Use the following formula in cell F9 to get the total price of the products excluding VAT and drag down the Fill Handle tool.

=D9*E9

Here, D9 is the Quantity of the products and E9 is the Unit Price of each product.

using formula

Finally, we are getting the Total Price of each product excluding VAT in the Amount column.

using formula

Lastly, we will include the VAT with the Prices of the products to determine the Final Price.
➤ Use the following formula in cell H9 and drag down the Fill Handle tool.

=F9+F9*G9

Here, F9 is the Price before adding VAT and G9 is the amount of VAT.

using formula

Then, you will get the Final Total Price of each product in the Total Price column.

using formula

Finally, we have completed the outline of the Price List.

how to make a price list in Excel


Step-04: Saving and Resuming Price List Template

In this section, we will use two VBA codes to save the Price List and refresh the template for performing calculations again for new entries.

➤ Go to Developer Tab >> Visual Basic Option.

how to make a price list in Excel

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

VBA Code to save and resume

After that, a Module will be created.

VBA Code to save and resume

➤ Write the following code for saving the template as a PDF file

Sub savepricelist()

Set ws = Worksheets("Template")
ws.Range("A1:H22").ExportAsFixedFormat xlTypePDF, _
Filename:="C:\Users\USER\Downloads\" & "PriceList", _
openafterpublish:=False

End Sub

Here, the Template is the sheet name and A1:H22 is the range of the sheet you want to save.

how to make a price list in Excel

➤ Now, use the following code for resuming the datasheet for new entries

Sub resumeList()

Range("B9:B17").ClearContents
Range("D9:D17").ClearContents

End Sub

Here, this code will clear these ranges B9:B17 and D9:D17.

VBA Code to save and resume

Now, return to the sheet and insert two buttons for these two codes like the following way.

➤ Go to Developer Tab >> Insert Group >> Button Option

VBA Code to save and resume

Then, a plus sign will appear and drag down, and to the right side this sign.

how to make a price list in Excel

After that, you will get a button, right-click here.

VBA Code to save and resume

➤ Select the Assign Macro option here.

VBA Code to save and resume

➤ From the list of Macro names choose the savepricelist Macro name and press OK.

VBA Code to save and resume

➤ After assigning macro we have to rewrite the button name and change it into SAVE.

how to make a price list in Excel

Similarly, we have created a button RESUME by assigning the macro resumelist to it.

VBA Code to save and resume

➤ Click on the SAVE button,

VBA Code to save and resume

and you will get the following PDF file.

how to make a price list in Excel

And by clicking the RESUME button,

VBA Code to save and resume

we have removed all of the input data.

VBA Code to save and resume

➤ Select any Product Code from the dropdown list and then enter the Quantity of this product.

VBA Code to save and resume

Then, automatically you will get the rest of the information for this product Apple.

VBA Code to save and resume

After giving input for all of the Product Codes and Quantities, we are getting the following sheet.

how to make a price list in Excel

Read More: How to Make a Numbered List in Excel (8 Methods)


Conclusion

In this article, we have tried to cover the easiest ways to make a Price List in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo