Excel

You all know basic Excel functions. You can either build them by selecting a function from the ribbon or you can start by typing an equal (=) sign. Here’s a basic one to apply a tax to a dollar amount.

Column A is the amount.

Column B is the tax (rounded to the nearest cent).

Column C is the total taxed amount.

Column F contains the current tax rate.

Now we’ll enter the tax and total with tax formulas in the first row.

 

BONUS TIP: You can quickly “fill down” or “fill right” by selecting a range and then hitting Ctrl-D to fill down the values or Ctrl-R to fill right. This saves the time of having to copy the cells first and then making the selection and then pasting. This is the same as dragging the bottom right cell handle down, but I find that somewhat challenging to grab with trackpads). Sorry Mac users – for some reason, Excel doesn’t have these shortcuts (at least the last time I used Excel on a Mac).

OK, that’s done, but what the heck happened here? Why are all of those ZERO?

Taking a look at one of the incorrect values in the table, I can see that the reference to the tax rate is no longer F2. Why? That’s because a fill down or copy/paste down will always increment the rows or columns relative to the first item selected/copied. The first number in the formula is what we want (A5). But we want to always keep F2 as the tax rate. How do we do that?

Well, for one, Google. You just need to know what to ask because there are millions of articles on this subject. Always try to choose the articles from microsoft.com before other articles for accuracy.

Or just read on.

You can anchor the value.

In your formula, simply type in dollar signs ($) in front of the letter number reference to the tax rate. In this case, $F$2.

Now fill down and voila!

Making sure this is what we want, you can select a cell further down and confirm that the reference to the total and the tax rate is correct and you’re good to move on to your next task!

BONUS TIP: While entering the formula, and while the tax rate reference is selected, you can press F4 to turn on the anchors (dollar signs). In fact, using multiple F4 keypresses, you can cycle through anchoring only the row or the column or both or none. Try it and see!