Excel Sales Report(Part 2)- Formulas
After importing the data from the previous article, I must create a flat table that contains all of my data so that I will be able to pivot the data later. Based on the objectives, I know that I will need to add columns for sales, product, supplier, and region.
To add the Sales column, click in the cell next to Units and type the title “Sales” and press Enter. The column should automatically add a new column named Sales that is formatted as the rest of the table
To calculate the Sales amount I need to “lookup” the price from the Product table (not shown), using the VLOOKUP formula. Note — XLOOKUP can also be used, and is arguably a better option, but I will save that for another post.
Once I have the above result, I must add the Units into the formula (see image below) and the result is the Sales amount.
Note — It is good practice to format the numbers. Right-click the cells to format and select Format Cells on the bottom of the pop up. I selected Number from the category list, decreased the decimals to zero, and checked the ‘use comma separator’ box.
The result after formatting:
The same steps were used for the remaining columns.
The completed table
The information is now in one place and ready to be analyzed using pivot tables.
