Price with and without tax

Let’s start with a very simple case.

• We have a table that contains the unit selling price and the quantity purchased.
• To calculate the price excluding tax, multiply these two elements.

Then, when you recopy the formula for each product, you’ll get the following result. As you copy the formula down, the row references change automatically (but not the column references).

Calculate the price including tax

Now, let’s calculate the price including tax.

• We will use cell E1 in our formula.
• The formula is: =Price without tax + Price without tax * tax
• Or, you can simplify the formula to =Price without tax * (1 + tax).

But if you copy the formula to the other cells, there is a problem.

• Only the first formula is linked to cell E1. ☹️
• When you copy down, the references change.
• In E6, you have an error because the formula uses cell E3, which contains text.
• This is where the dollar sign is needed 😉

Shortcut to add the dollar sign

Adding dollar signs to a formula is such a common task that there is a shortcut for typing the $ symbol: the F4 key.

1. Write the beginning of your formula.
2. Select the cell you want to lock.
3. Press F4 — two dollar signs are added to the cell reference automatically.
4. Finish your formula.

Now practice it yourself

Time to put the dollar sign to work on a real case: you want to control the weight of strawberry trays, which should be 250 grams (cell E2).

Question: How do you write the formula in C2 so you can copy it to the other cells?

You now have everything you need to work it out: lock the reference to E2 with the dollar sign so it stays fixed when the formula is copied down to the other rows.

You’ve seen why the dollar sign matters, how to add it with the F4 shortcut, and how it keeps a reference fixed while the rest of a formula adapts when copied.

Continue learning

Keep building your Excel skills with the rest of this tutorial series:

• Tutorial – Excel keywords
• Tutorial – Adjusting the Size of rows and columns
• Tutorial – How to make calculations in Excel
• Tricks to Insert, Delete, Hide Rows and Columns
• Tutorial – All Excel tricks to Copy-Paste
• Tutorial – Moving cells or columns with the mouse
• Tutorial – The Numbers Format
• Tutorial – Excel Alignment text tips
• Learn the secrets of Sorting Data with Excel
• Excel Tutorial – Filtering your data
• Manipulate Text in Excel without formula
• Excel Tutorial – Creating a dropdown menu
• Learn how to use the SUM function in Excel
• Tutorial for the Excel Functions AVERAGE, MIN, and MAX