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

Step 1 – Making an Outline of the Price List Template

We’ll start with the following dataset that contains various product details, including product codes and names, their prices and applicable VAT.

• We have created the basic outline of the Price List for the company.

• We have inserted the company details like the name, address, and contact number.

• Use the TODAY function for the cell that says “Date”.

• 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 2 – Creating a Dropdown to Make a Price List in Excel

• Select the cells of the column Product Code where you want to have the dropdown list.
• Go to the Data tab and select Data Validation.

• The Data Validation dialog box will pop up.
• Select the List option in the Allow box.
• Use 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.

• You will get the dropdown sign in the cells of the Product Code column and can choose any of the Product Codes from the list.

• We have selected a few Product Codes:

• Select the codes from the list for the rest of the cells.

Read More: How to Create List from Range in Excel

Step 3 – Using Formulas to Make a Price List in Excel

• 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. IFERROR makes sure that the cells remain blank if the value is not found.

• This fills the Product Name column.

You can get the values of Unit Prices and 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’s the finalized column for the sample.

• Use the following formula in cell H9 and drag down the Fill Handle tool.

• Here are the results.

• We have completed the outline of the Price List.

Step 4 – Saving and Resuming the Price List Template

• Go to Developer tab and select Visual Basic.

• The Visual Basic Editor will open.
• Go to the Insert tab and select Module.

• A Module will be created.

• Insert the following code that saves the sheet in a PDF.

Sub savepricelist()

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

End Sub

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

• Use the following code for resuming the datasheet for new entries:

Sub resumeList()

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

End Sub

This code will clear ranges B9:B17 and D9:D17.

• Return to the sheet and insert two buttons for these two macros (Developer >> Insert >> Button in Form Controls).

• When you click on the button option, the cursor changes to a plus sign. Click and drag to draw the button.

• Right-click on the button.

• Select the Assign Macro option.

• From the list of Macro names, choose savepricelist and press OK.

• Rewrite the button name and change it into SAVE.

• We have created a button RESUME by assigning the macro resumelist to it.

• Click on the SAVE button.

• You will get the following PDF file.

• Click the RESUME button.

• This removes all data.

• Input the necessary values again.

• You will get the rest of the information.

• Here’s a new sample sheet for a different day.

Read More: How to Make a Numbered List in Excel

Download the Free Template

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 a Comma Separated List in Excel

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