What every VA needs to know about dollar signs in Excel formula

VAs: Create Traffic Driving Infographics for Your Clients

If you're a virtual assistant that creates blog posts and helps get traffic to your client websites, then you'll love this Infographic Training Course.

I'll show you how to create traffic pulling infographics using Microsoft Office tools. When you do this training you'll wonder how you've not done this sooner!

Subscribe to be the first to know when it's released.

P.S.  You'll love the emails you'll receive and there will be great deals on additional training for you. You won't be spammed and your privacy is protected.

What every VA needs to know about dollar signs in Excel formula

You have just been sent a spreadsheet. It has been set up for you and you are eager to get to work filling in the blanks and completing the calculations. But what’s this? You suddenly notice in some of the formulas there are some Dollar $ signs lurking. What do they mean? Why are they there? What should you do?

Don’t Panic

These dollar signs are a secret code for Excel. They tell Excel to fix a Cell Reference. Huh? What do you mean? Fix a Cell Reference? What is a Cell Reference and why does it need to be fixed? Is it broken?

What those Dollar Signs mean

When working in Excel and creating formula in a column you may well be struck by the fact that you need to complete the same formula in every row. So in the image above I need to complete the formula in cell E2 in the other cells E3 E4 E5 E6 and E7.

Now that is not so difficult in this example, there are not many in this list. However, if you had a list of even plus that is going to be very time consuming not to mention frustrating.

Is there a faster way?

You know I am going to say yes, now don’t you?

When filling a column (or a row for that matter) you can use a handy feature called Autofill see  Virtual Assistants – why Autofill in Excel will save you hours.

Autofill is a fabulous tool and it copies exactly the referencing in the cell you start from – in this case it would copy =D2*J1 and in the row below it will be D3*J1 – or rather it should.

However, what actually happens is in each case the reference to J1 gets changed as well. So, in the cell below you would see D3*J2. In the cell known as J2 there is actually no value. So, the result in cell E3 will also be 0.

This is where the dollars come in

To make the reference to cell J1 copy throughout the column – we need a way of telling Excel to fix the reference. We want every cell in Column D to refer back to cell J1 not to J2 J3 J4 etc.

Enter the F4 key.

The F4 key – one of the function keys on the top row of your keyboard, adds two dollar signs to the cell reference you are currently adding to the formula.

The first time you press it, you get $J$1 this fixes the cell reference absolutely to cell J1 and is known as an Absolute Cell Reference.

The second time you press it you get J$1 – this fixes the reference to just the row – so when you drag across several columns the column will change but the row stays fixed.

The third time you press is you get $J1 – this fixes the reference to the column – so when you drag down the row will adjust but the column stays the same.

The fourth time you press it, the dollar signs are removed completely.

Would you like to learn how to put this into practice?

To learn how to build your formula properly and what happens when you need to add subtract divide and multiply, plus what order Excel will perform calculations in, and how to use Absolute Cell references with the F4 key, then you need to take my course – Essential Excel Skills for Virtual Assistants.

In this course you will learn how to set up your worksheet in seconds, create calculations and formulas with flair, format everything so that it looks clean and professional (and easy to understand!) and make sure the whole thing works without a worry!

What are you waiting for? Head over there now.

Essential Excel Skills for Virtual Assistants 

The first 1o to sign up will qualify for a 20% discount — just enter the coupon code 20%F4 at checkout.

 

 

About the Author Shelley Fishel

I will help you be more productive and earn more money as a virtual assistant. As a trainer with 20 years experience, you'll love how you'll get between 4-8 hours a week back when you use Microsoft tools to their fullest potential. I'm also the author of several best-selling books on Microsoft Word, PowerPoint, Excel and Outlook, published at www.bookboon.com . Subscribe and get more productive!

follow me on:

Leave a Comment: