Excel Tricks that will let you stand out from the crowd – Part Three

One of the simplest tools in Excel is the dollar sign. When you are using a lot of tables, or repeating a lot of formulas, the dollar sign comes in real handy.

AD: Earn money by doing online surveys!!!

excel1

The dollar sign in the cell name as above tells Excel not to shift when the cell moves. The below table shows the four different ways of writing this. Each one has different functionality.

excel2

So if we copy the formulas down the left hand column of each table:

excel3

Notice that the cells with the dollar sign in front of the one keep a value of Q. That happens because the dollar sign is telling Excel not to advance down past row one.

AD: 101 Secrets of an Excel addict

In the same way, the dollar sign in front of the A tells Excel not to advance across past column A:

excel4

So you can see that, depending on where the dollar sign is, Excel will “lock” the row/column when you copy the formula down/across.

The completed table above will look like this:

excel5

excel6

The most useful of these formulas is the double dollar sign: =$A$1. If you wanted to multiply an array of values by a single number in one cell, the double dollar formula will be the solution.

Another Excel tip done!

Thanks to Business Insider for the great lesson!

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s