Data is everywhere – from government computers to websites. Spreadsheets can help reporters find story tips in the data. The notes below introduce data analysis using a spreadsheet and cover basic calculations, ratios and other analytic tools that generate story ideas.
Formulas
All formulas and calculations begin with an equal sign = that tells Excel you’re doing maths.
Per capita =amount/population
Per 100 or per 1000 – just multiple the above formula by 100 or 1000 by using the *
Percent change = (new-base)/base or (new-old)/old
The two-step method:
• add a column to compute the difference
• add a column to compute the difference/base
Copying formulas: Excel is handy because you only need to type a formula once. Then you can copy it and Excel will adjust it based on the row number. Copy a formula by putting your mouse over the lower right corner of the square.
Click and drag the thin plus sign to the bottom.
Percentages
Percent of total: = part/whole
Sometimes you will use the same total for every row – as in the example below. So you don’t want Excel to adjust your formula. In the example below, we want to divide each row by the total. Anchor the total by using $ signs.
Functions
Excel has several special functions to do things such as total a bunch of numbers or average a bunch of numbers. In most cases, the format for those formulas is = function (start:finish). In the following example, I used SUM to add up a column of numbers.
Other functions include: = average (start:finish) = median (start:finish)
Formats
To make your numbers look like currency or percentages, highlight the numbers you want to format, then click one of these: (the two funny zero buttons on the end increase and decrease decimal points)
Sorting
First rule – save your file. Then highlight everything you want to be sorted (not titles and totals because you don’t want them mixed in with everything else).
Then go to the ‘data’ tab and click the ‘sort’ button.
You’ll get a box that lets you specify your sort parameters:
Choose the column you want to sort by. Choose ‘values’ in the second box – unless you want to do different sorting, such as by colour. In the last box chose how you want to sort – ‘ascending’ or ‘descending.’
Note the checkbox that says ‘my data has headers’. This can be useful if your spreadsheet is set up such that the first row is headers and the second row is data. If you check this box, your sort-by dropdown will give you header names. If you don’t, you’ll get the column letters.
Freezing
Sometimes spreadsheets are too long or too wide to read all your information. That’s where ‘freeze panes’ comes in handy.
Go to the ‘view’ tab and click ‘freeze panes’
You get several options:
You can freeze the top row or first column. To freeze both, click on the cell at the intersection where you want your freeze to begin and click ‘freeze panes’. Freeze panes also will freeze a column that is not the first column or freeze a row that is not the first row. To turn any of these off, go back and choose ‘unfreeze panes’.
Graphs
To make a graph, highlight both the numbers and headers of the items you want to graph. Then click the ‘insert tab’ and click on the type of chart you would like to make. Excel will step you through the process by asking questions about your chart.
For more information…click F1 in Excel for help