Excel - The basics for creating sales quotes/invoices/purchase orders

March 2017

Database and associated document --------


Many sites, links, blogs, tutorials already explained and detailed:
"How to use a database (list of information) to facilitate the creation of documents like: sale_quotes, invoices, purchase or delivery orders". In most cases the explanations given are too complicated to for Excel beginners to get started. The aim of this tip tis to provide a simple and effective methodology.


1) In sheet 1 "Produits" (Products), enter line by line, all information relating to each article, the name (without duplication) of each items being in the first column of the table.
2) Use the first row to the titles of different sections: désignation, référence, marque, modèle, prix unitaire, couleur, conditionnement (description, reference, make, model, unit price, color, packaging ..)
3) Define a name for the data range, for example "MaBase" (MyDatabase).
4) Define a name for the cells in the first column of the range, for example "MyName".
5) Use "MonNom" (MyName) to create a drop down list in sheet 2 "Devis" (Sales_quote), to make sure to call only existing products, and with the correct spelling.
6) Use MaBase (MyDatabase) to enter data for that cells that will be displayed in "Devis" (Sales_quite), using the VLOOKUP function.

Refer to this file for a better understanding of the procedure:

Other uses

Other sheets from the same workbook, can be edited for other purposes: priniting invoices, collecting data in the same database...
This is how you can create descriptions (editable sheets).
They are recommended to establish records (stock, membership, identity ...)


a - For easy reference from the dropdown list, make sure that the database is sorted alphabetically.
b - New items must be added in-between the first and last rows (you may need to sort the list again).
c - Column can be added on the fly
d - Warning: in case of data update, only new additions will have the modified values, the data already collected won't be dynamically (automatically) updated!


a - To create a dropdown cell, go to the "Data" tab and the command "Data Validation" with Allow/List, and the source type "=MonNom" (=MyName).
b - A database must not contain any line or column empty, and no merged cells!

Original document published by Raymond PENTIER on CommentcaMarche.net.


Published by deri58.
This document, titled "Excel - The basics for creating sales quotes/invoices/purchase orders," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).