How to Excel (with Excel) – Academic Kaizen

Legal academics have a well-known aversion to numbers. But Excel – for all its faults – can be a huge help when managing large amounts of data, be it numbers or words. I have used Excel to analyse cases, build tables and charts, and manage all sorts of data.

We spoke with Christina Ward, Liaison Librarian (Research) at Melbourne Law School, about essential tips for using Excel for academic research. Our tips:

To swap rows and columns in a table, copy the table, select “paste special” and click transpose.
To reverse rows or columns in a table, try this trick.
To manage data, the sort and filter functions are incredibly useful. Only want to see rows that have data? No drama. Only want to analyse successful cases that claimed both unfair dismissal and age discrimination? Done.
Formulas are your friend. There are cheat sheets all over the internet to help find the right formula. I use the “SUM” (add) function all the time, but other useful formulas are:
- =DAYS – count the days between two dates
- =COUNT and =COUNTA – count the total number of digits or text; COUNT counts numbers and formulas; COUNTA counts everything
- =COUNTBLANK – count the number of blank cells in a column or range
- =COUNTIF – count the number of cells in a range that meet a specific condition
- =MAX and =MIN
- =TRIM(text) – remove extra spaces from text
- =SUBSTITUTE – replace text
In a large spreadsheet, hide and unhide rows or columns to make data more easily navigable. And/or freeze headers so you can infinitely scroll down.
With large amounts of text in a cell, “wrap text” can help show all the text at once. Then adjust the row height accordingly, or use the Format > Autofit Row Height option to automatically adjust to the amount of text in the cell.
Rather than repetitively entering data that has some sort of pattern, use “autofill” – begin the series, then select the lower-right part of the last cell. When you see a plus sign (+), click and drag down to select all the cells you need to fill.
Increase or reduce the number of decimal places in numbers with the Increase Decimal or Decrease Decimal button in the Home tab. Huge time saver when tidying up a table.
Need to compare data or find duplicates? Use conditional formatting: there is a tutorial here. But beware trailing spaces, which will need to be TRIMmed to find all duplicates.
Need to send lots of personalised emails or letters to research respondents or students? Use mail merge to turn a list of names into tailored correspondence.
You can import data from a website into excel: there is a tutorial here. We had mixed success using this in practice; it depends on the website and how the data is presented. When the data imported is ‘stacked’ in one column, though, this video shows a simple way to unstack data. You could then use text to columns to split the data up into different cells.
Excel even has a basic mapping function.
And, to make a line break in a cell, use Alt+Enter.

For collaborative work, Google Sheets has most of the functionality of Excel, except macros.