I created this (blog?) for myself so I can keep on top of Excel. I am going to add to it throughout the year when I learn new functions.

Excel might not look like the friendliest of Microsoft programmes but it is important for every journalist to understand.

I think of Excel or Google sheets (the equivalent of Excel on Google drive) as a giant calculator.

**Creating formulas**

Each cell in Excel can contain a calculation or ** formula**. When entering your formula, start by typing the = (equals) sign then the rest.

You can make simple calculations in Excel like addition, subtraction, division, and multiplication. But you can also calculate averages, medians, and ratios.

If you put in a colon then it will highlight all numbers in between (ex: b2:b7)

To get a percentage then multiply by 100 or use the percentage symbol

Ratios is just the division of two cells. Use when talking about something being **x **times as big as **y**

In this example, in my data journalism class, we calculated the ratio of EU claimants by population.

Averages (or mean) is just adding up all the numbers and dividing the result by how many numbers there are.

To calculate an average type =AVERAGE and then the numbers of the selected lists

For example =AVERAGE(A2:A7)

The same goes for calculating a median: =median(value1:value2)

handy formulas |
Example |

=Average | =Average(value1:value2) |

=median | =median(value1:value2) |

**Applying the same formula to the same column and paste options**

Move mouse over bottom right to right to copy function and apply it to equivalent rows/columns

Use $ to lock one cell while cycling through others

For the same exercise, we used the $ sign to lock the result of EU claimants per UK population

Paste is Excel has different functions:

- paste special takes the final result of a formula and pastes it somewhere else
- paste format: replicates the format of something else

Just right click on the cell you want to paste and select Paste Special.

**Calculating a Percentage point change:**

Percentage point change is the unit for the difference of two percentages i.e going from 40% to 44% is a 4 percentage point increase

To calculate a percentage point change in excel we divide the new by the old. This tell us what percentage the new number is of the old one.

Now we need to find out how our new figure compares to 100%. In order to find this out we need to ask ourselves what is 100% minus our new percentage.

In decimal terms, 100% is 1.0, so we type 1- in front of our previous calculation.

Note: Important to put the first bit in brackets so we ensure that it is calculated separately.

If we find a downward change then we will need to calculate the entire thing by (-1) to reflect that change

This is an example we did for my data class:

unemployed | population | rate | |

2010 | 50 | 100 | 50.00% |

2015 | 55 | 100 | 55.00% |

percentage point change | 5.00% | ||

percentage change | 10% |

To practice these Excel basic skills, try one of the Guardian’s (link to name) data sets! http://www.theguardian.com/news/datablog/interactive/2013/jan/14/all-our-datasets-index

## 2 Replies to “My Excel tips for journalists”