Be it Microsoft Excel or Google Sheets, spreadsheets have become a very important aspect of our day-to-day work. To start using spreadsheets, one of the first things to understand is how basic spreadsheet modeling works. In this tutorial, we will use basic calculations in a spreadsheet in a way that resembles a real-life situation, and learn how to calculate profit from sales using spreadsheets.

Required Tools

The tutorial is specifically made for any version of Microsoft Excel. The same steps should work with Google Sheets with some minor changes.

Formulas / Functions Used

This tutorial uses basic Division, Multiplication, Addition and Subtraction formulas.

Calculating Profit from Sales Using Spreadsheets

Let’s assume, we’re working with the numbers from a bookstore. We have the names of the books, costs, prices, and the number of units sold for the month. We have to calculate the profit. 

Step 1: Calculate the Margin for each book

Firstly, we have to calculate the margin for each book. To do that, let us click on the cell G4 and write the formula =E4-D4 which will give us the difference between the price and cost for the first book. Once we have the margin for the first book, will copy the formula using Ctrl+C, select the range G5:G17, and paste the formula using Ctrl+V. We can also copy the formula in G4 by pointing at the tiny square in the lower-right corner of G4, and after observing the cursor change to a thin black crosshair, pressing the left mouse button and dragging the formula to the range G5:G17. We will see that the margin for each of the books has appeared in the corresponding cells of column G.

Step 2: Calculating Profit for each book

After that, let’s put the borders for the range and name the column ‘Margin’. Now, we already have the margin for each of the books. To calculate the profit, we have to multiply the margin by the number of units sold. So at H4, we will put the formula =G4*F4 and we will see that the total profit made from the first book will appear on H4. We will have to copy the formula to the range H5:H17 in the same way as earlier, and the total profit for each of the books will appear in the corresponding cells of column H.

Step 3: Calculating the Total Profit

We will add borders to the range and name the column ‘Profit’. We now have a complete table with all the information we need. To calculate the total profit for the month, we just have to add the profits for the individual books.

To do that, we can simply use the formula =SUM(H4:H17) in cell H18 and the total profit will appear on that cell. We can also use the ‘AutoSum’ button from the top to calculate the sum. To do that, we simply need to click on the cell H18, and then click on ‘AutoSum’ from the top menu.

Bonus

We could skip step 1 and calculate the profit directly in column G. To do that we will use the formula =(E4-D4)*F4 in cell G4, and copy the formula to range G5:G14. 

Then we can calculate the total profit in the same way as earlier.

Download the Practice File

Learn More

Here are some similar tutorials that you may find interest in:

• How to calculate how much a business owes its suppliers
• How to predict the number of customers a business will have in 10 years
• How to determine the price of a product to ensure profit maximization
• How to calculate the grades of a student from the marks that they obtained

Conclusion

So here’s how to calculate profit from sales using a spreadsheet. If you have any queries regarding this or anything related to spreadsheets, feel free to let us know in the comments or through the Contact page.