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
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)
- 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:
|percentage point change||5.00%|
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