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

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.

How to Make a Price List in Excel: with Easy Steps

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.

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

➤ 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.

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

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

Read More: How to Make a To Do List in Excel

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.

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

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.

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.

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

then Product Code 807 in cell B9.

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

Read More: How to Create List from Range in Excel

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.

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.

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

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,

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

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

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

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

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.

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

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

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

Read More: How to Make a Bulleted 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.

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

After that, a Module will be created.

➤ 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:=FalseEnd Sub

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

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

Sub resumeList()Range("B9:B17").ClearContentsRange("D9:D17").ClearContentsEnd Sub

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

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

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

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

➤ Select the Assign Macro option here.

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

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

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

➤ Click on the SAVE button,

and you will get the following PDF file.

And by clicking the RESUME button,

we have removed all of the input data.

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

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

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

Read More: How to Make a Numbered List in Excel

Download Workbook

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

• How to Create a Contact List in Excel
• How to Make a List within a Cell in Excel
• Create a Unique List in Excel Based on Criteria
• How to Generate List Based on Criteria in Excel
• How to Make Alphabetical List in Excel
• How to Make a Comma Separated List in Excel

<< Go Back to Make List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel