Tag Archives: Excel

Understanding Cell References In Excel

One of the commonly misunderstood things about using formulas within Excel is writing cell references. By this, I mean, many people don’t know when to and when not to use the dollar sign before a column name or row number.

The dollar sign is used to make the identifier after it stay static no matter where you copy the formula to. Let’s use the following example table:

A B C D
1 First Name Last Name First Last Last, First
2 Jarid Vurnelson
3 Chelsea Marten
4 Rian Zammroz
5 Susannah Maisol
6 Curran M’Cally
7 Ed Orkitt
8 Megan Pintur
9 Lucie Rosenbergur
10 Gail Wiffin
11 Kris Wess

As you can see, we have two columns to fill out, and as you have probably guessed, we are going to use formulas to do so.

Absolute Columns & Absolute Rows

If you don’t want the column nor the row to change relative to where the formula is copied to, you should place a dollar sign in front of both the column name and the row number. For example, if cell A2 has a formula of “=$A$1” and the cell is copied to cell G6, the formula in this cell will be “=$A$1”.

Let’s try the following just to see what happens when we use dollar signs both before the column name and row number:

  1. Enter =CONCATENATE($A$2," ",$B$2) into cell C2.
  2. Make sure cell C2 is selected and then use the CTRL + C key combination to copy the cell.
  3. Select cells C3 to C11 and then use the CTRL + V key combination to paste the formula into all of these cells.

Notice that cell C2 shows the value that we are looking for, but cells C3 to C11 don’t because they are duplicates of what is found in C2. The value didn’t change as we copied to formula to other cells because of the dollar sign appearing both in front of the column name and the row number. While it is true that we always want the column that is referenced to stay the same if we copy the formula to other columns, we don’t want the row number to remain as 2 in those cases.

Absolute Columns & Relative Rows

If you don’t want the column to change, but you want the row to change relative to the place to where the formula is copied, you should place a dollar sign in front of the column name and not the row number. For example, if cell A2 has a formula of “=$A1” and the cell is copied to cell G6, the formula in this cell will be “=$A7”.

This is what we want to do. Let’s try it out:

  1. Enter =CONCATENATE($A2," ",$B2) into cell C2.
  2. Make sure cell C2 is selected and then use the CTRL + C key combination to copy the cell.
  3. Select cells C3 to C11 and then use the CTRL + V key combination to paste the formula into all of these cells.

Now you should see everyone’s full name in column C. In fact, in our case, we will want to use absolute references for columns and relative references for rows in the next formula as well:

  1. Enter =CONCATENATE($B2,", ",$A2) into cell D2.
  2. Make sure cell D2 is selected and then use the CTRL + C key combination to copy the cell.
  3. Select cells D3 to D11 and then use the CTRL + V key combination to paste the formula into all of these cells.

Now, if you did everything correctly, you should have the following:

A B C D
1 First Name Last Name First Last Last, First
2 Jarid Vurnelson Jarid Vurnelson Vurnelson, Jarid
3 Chelsea Marten Chelsea Marten Marten, Chelsea
4 Rian Zammroz Rian Zammroz Zammroz, Rian
5 Susannah Maisol Susannah Maisol Maisol, Susannah
6 Curran M’Cally Curran M’Cally M’Cally, Curran
7 Edd Orkitt Edd Orkut Orkut, Edd
8 Megan Pintur Megan Pintur Pintur, Megan
9 Lucie Rosenbergur Lucie Rosenbergur Rosenbergur, Lucie
10 Gail Wiffin Gail Wiffin Wiffin, Gail
11 Kris Wess Kris Wess Wess, Kris

Relative Columns & Absolute Rows

If you need the column to change relative to where the formula is copied to, but you want the same row to alway be referenced, you would need to place the dollar sign only in front of the row number. For example, if cell A2 has a formula of “=A$1” and the cell is copied to cell G6, the formula in this cell will be “=G$1”.

Relative Columns & Relative Rows

If you need the column and row to change relative to where the formula is copied to, you should not use any dollar signs for the cell reference. For example, if cell A2 has a formula of “=A1” and the cell is copied to cell G6, the formula in this cell will be “=G7”.

Using COUNTIF In Excel

Why would you use the COUNTIF function? This is a question that you may wonder at times. First of all, you could use it to figure out how many cells in a range match the specified criteria. You could also use it to determine whether or not a value in one cell is found in a range of other cells. The following is an example table which will be used to accomplish both of these tasks:

A B C D
1 Patient Name Age Relative Name Relative Is Patient
2 Chris West 22 Jim West
3 Beth Coope 24 Elizabeth Coope
4 Dan Yoder 17 Vladimir Yoder
5 Susie Queue 19 Beth Coope
6 Matthew Franklin 18 Jeffrey Franklin
7 Vladimir Yoder 25 Victoria Yoder
8
9 Under 18:
10 At least 18:

As you can see, cells D2 to D7 should indicate whether or not the patient is also a relative. To do this, do the following:

  1. Enter =COUNTIF($A$2:$A$7,$C2)>0 into cell D2.
  2. Select cell D2 and use the CTRL + C key combination to copy the cell.
  3. Select cells D3 to D7 and use the CTRL + V key combination to paste the relative formula into these cells.

Now the table should look like the following:

A B C D
1 Patient Name Age Relative Name Relative Is Patient
2 Chris West 22 Jim West FALSE
3 Beth Coope 24 Elizabeth Coope FALSE
4 Dan Yoder 17 Vladimir Yoder TRUE
5 Susie Queue 19 Beth Coope TRUE
6 Matthew Franklin 18 Jeffrey Franklin FALSE
7 Vladimir Yoder 25 Victoria Yoder FALSE
8
9 Under 18:
10 At least 18:

This formula starts off by counting the number of times the value in the same row and in column C was found in the range of A2 to A7. Next, the cell determines whether or not the count of these matches is greater than 0. If the count is greater than zero, the cell evaluates to TRUE. If the count is not greater than zero, the cell evaluates to FALSE.

Now it is time to figure out the count those under 18, compared to those who are at least 18:

  1. Enter =COUNTIF($B$2:$B$7,"<18") into cell B9.
  2. Enter =COUNTIF($B$2:$B$7,">=18") into cell B10.

Now the table should look like the following:

A B C D
1 Patient Name Age Relative Name Relative Is Patient
2 Chris West 22 Jim West FALSE
3 Beth Coope 24 Elizabeth Coope FALSE
4 Dan Yoder 17 Vladimir Yoder TRUE
5 Susie Queue 19 Beth Coope TRUE
6 Matthew Franklin 18 Jeffrey Franklin FALSE
7 Vladimir Yoder 25 Victoria Yoder FALSE
8
9 Under 18: 1
10 At least 18: 5

The formula in cell B9 simply counts how many cells in the range B2 to B7 are less than 18. The formula in cell B10 counts how many cells in the range B2 to B7 are greater than or equal to 18.

Excel – Batch Convert XLS To CSV

Update!!!

A newer HTML application (HTA) has just been created which accomplishes the same thing but in a more customizable manner. Click here to read the post.

Original Post

A few days ago, someone asked me to help them convert a large number of XLS files to CSV. Since, while working for Bristol-Myers Squibb, I became very familiar with writing macros and JScripts to work with Excel, I decided to write a JScript to accomplish the task. Basically, all you have to do is put the script inside of the same folder as all of the Excel files and double-click it to run it. The script uses Excel to open all of the files and convert each worksheet of each XLS file to a CSV. The naming convention for the CSV files is as follows:
{file_name}-{worksheet_name}.csv

Here is the script:
Excel – XLS To CSV Converter

If you examine the script, you will notice that it uses jPaq for user interaction.  All of the code, except for the jPaq because it is the minifieid version, is pretty well documented.  You may do whatever you want to the script. All I ask is that, if you re-use the code, you give credit where it is due. :)