intro to cleaning data

How spreadsheets work

Spreadsheets with rows and rows of data can be intimidating. But when you break them down to their essential parts they are really quite simple. Let's look at a blank spreadsheet: 

empty excel spreadsheet

Cells are arranged in columns and rows. All columns are identified by a letter and rows are identified by a number. This creates a map that IDs every cell in the spreadsheet. Cell A1 is in the top left corner and cell B2 is down and to the right. 

This is critically important and what makes spreadsheets powerful tools. If we know the ID of a cell or range of cells, we can manipulate values without having to manually type them in. 

 

Exercise 1

Type 2 in cell A1. Next, go to cell C2 and type =A1 and hit Return. The result is that cell C2 contains the number 2.  

basic formula

The equal ( = ) sign is special and tells Excel that instead of containing text or a number, this cell contains a set of instructions (usually called a formula or function).  Notice that cell A1 is highlighted and the formula appears in the Formula Bar. You can write the formula in either place.

 

Exercise 2: Simple math

We can use formulas to perform simple math.

Type 2 in cell A2. Next, go to cell C3 and type =A1+A2 and hit Return. Notice that cell C3 contains the number 4  

simple math formula in excel

Replace the addition ( + ) symbol with the minus ( - ) symbol and the result is 0. Replace with the multiplication symbol ( * - asterisk) and the division symbol ( /  - forward slash) to see more results.

 

Exercise 3: Calculate percentage, percent change

You can also use this to perform more complicated math. If you want to calculate a percentage click on cell C4 and type =A2/A1*100 and the result is 100. This translates to =smaller number/larger number *100 (to move the decimal two places).

calculate percentage in excel

Change the value of cell A1 to 4 and the result in cell C4 updates to 50. All the previous formulas update as well.

Percent change is a very useful formula to know.

Click on cell C5 and type =(A2-A1)/A1*100. The result is 100 percent. This translates to =(new value-old value)/old value*100 (to move the decimal two places).

calculate percent change in excel

When values are in parenthesis, they are calculated before being fed into the formula. So in this case the result of (A2-A1) equals -2. -2 is then divided by A1 (4) and then multiplied by 100 for a result of -50%.