Excel

Concatenating cells in Excel

To concatenate cells in Excel, you can either use the concatenation() function or the & symbol. If

A1 = Hello
A2 = World

then

=A1&A2

will produce

HelloWorld

and

=concatenate(A1," ",A2)

will produce

Hello World

If the concatenation is not working, then make sure that the format of A1 and A2 is "text" and the format of the cell with the concatenation function/formula is "general".

Removing characters from Excel cells

Sometimes Excel cells contain unwanted data. Removing this data can be a very tedious job. For example, I recently received a data file where the first column cells contained some numeric identifiers. The problem was that the number were typed as #123456, #123457, etc. I only needed the numbers without # symbol for my software to process this data. Since it was not possible for me to manually edit over 20000 rows, I used the following code:

Counting cells in Excel

Excel provides 4 different functions to count cells: count(), counta(), countblank(), countif()

=count(A1:A10)

Counts cells in the range which hold a value. Empty cells and cells containing formulas are not counted.

=counta(A1:A10)

Counts non-empty cells in the range. Cells containing formulas are counted.

=countblank(A1,A10)

Counts empty cells in the range.

=countif(A1:A10,"<10")

Counts cells in the range which satisfy a condition. In this example, only cells with value less than 10 are counted.

Transposing data in Excel i.e. moving data between rows and columns

The following steps show how to transpose data in excel. Transposition refers to switching between rows and columns. For example, if row 1, column1 is A, row 2, column 2 is B, the transposition will be A in row1 column1 and B will be in row2 column2.

  1. copy a dataset
  2. before you paste, right click on the first destination cell
  3. click on Paste Special
  4. select Transpose, and click on OK