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.

  1. 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.
how to do ratios

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)

 

  1. 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

 

dollar sign

 

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.

paste functions

  1. 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

 

Cristina is a candidate for an MA in Interactive Journalism at City University London and a junior reporter. Cristina was born in Costa Rica but raised in Washington D.C. She's fluent in Spanish, French, English, and Portuguese. Her language skills come in handy when reporting on the hispanic community in the U.S or translating a story into a different language for a wire service. Over her college career, Cristina interned at NPR for Morning Edition and wrote for community newspapers in Fairfax County.Since graduating with a bachelor's degree in Political Science and Film and Media Studies, Cristina has worked at KQED - the NPR affiliate in Northern California - worked on stories for the Mission Local and Homemade News Podcast. Thus, she's worked in radio, print and social media. Cristina is now living in London.

2 COMMENTS

Leave a Reply