If office apps can be said to be a triumvirate, with the word processor being familiar to nearly all writers, the spreadsheet and presentation apps form the other two branches. In this month’s Technophobia we concern ourselves with the latter.
Most people believe spreadsheets are about numbers. A more precise definition is that spreadsheets are about all kinds of data, including words. Even more precisely spreadsheets are about the tabulation of data, which is to say data in tables.
Imagine you’re standing in front of a blackboard. Draw consecutive horizontal lines, and then consecutive vertical lines across them until you end up with a grid. This is a basic spreadsheet – a readymade and very large table into which you can enter any amount of data. Each square of the spreadsheet, into which you write numbers or words, is called a cell.
In one vertical column of the spreadsheet you could enter the price of each item of stationery you buy, for example. Then underneath the list you could tally-up the total cost in order to show your accountant.
You could do this in a word processor – all you’re really doing is creating a list, after all – but spreadsheets have magical abilities. For example, with the stationery list you don’t have to tally up the list in your head. In the cell where you want the total to appear, you could type what’s called a formula to add-up everything. The total will then appear automatically. Crucially, changing the value of the any of the cells – if envelopes increase in price, for example – will mean the total updates itself automatically and instantly.
You might also create another formula in a separate cell to calculate the monthly or yearly cost of stationery by simply multiplying the total by 4, or 12. Again, this will automatically update if any data is changed.
All spreadsheet apps are, of course, massively more powerful than this. But that’s true of all apps nowadays, and it doesn’t preclude humbler uses.
Let’s explore real-life examples. There are two spreadsheets I use all the time. The first lists my freelance writing income for the tax year. It’s extremely simple and contains only four columns: the date the money was received, the source of the payment, the amount, and a cell into which I type notes, such as if there were bank charges. I copy and paste the data straight from my online banking website.
At the very bottom I keep a tally of the total money I’ve earned, which updates automatically each time I input a new payment. I also use a cell alongside this to give myself an idea of what tax is likely to be payable. My accountant suggests I set aside a third of my income for this purpose, so the formula in that cell simply shows 0.33 of the total. I could easily create a more sophisticated formula to take into account that year’s personal allowance limit and the actual tax rate for my income bracket. If I wanted to be really clever I could make the formula take into account all tax brackets, and update automatically should I earn more than £32,000 from my writing. However, this would be wildly unrealistic.
The second spreadsheet I use all the time is my freelance log, by which I track work I’ve written and submitted, and also the payments I should be receiving. Let’s look at how to create one of these now.
You probably already have a spreadsheet app if you’ve paid for Microsoft Word. Microsoft’s example is called Excel but, as always, the free and very high-quality LibreOffice office suite (www.libreoffice.org) contains what you need in the form of an app called Calc.
Fire up either Excel or Calc and you’ll automatically create a fresh spreadsheet. If offered a series of templates instead, click to create a Blank Workbook.
Note the edges of the spreadsheet, where you’ll see the columns are labelled with letters (A, B, C etc.) and the rows are labelled with numbers. Like a Chess board, this is how individual cells are identified. The cell at the top left is A:1, for example. The one beneath this is A:2.
The first task is to create headings. These are only for your benefit. In other words, the spreadsheet has no kind of intelligence, although it does need to know what kind of data is in each cell and it normally works this out automatically. Type a number, for example, and that cell becomes a number cell. Type a date and that cell becomes a date cell. Excel needs to know what kind of data is in each cell so it knows how to work with it.
Back to the headings. Type the first at the very top left, in cell A1. You can create any headings you desire to fit with your style of submission, but here are the ones I use: publication title, due date, title of piece, publication number, invoice number, invoice date, amount, and payment received date.
I usually make the headings bold type so I don’t miss them, and this can be done in the same way as within a word processor by highlighting the text and clicking the B button on the Home ribbon toolbar.
You might notice an annoyance. Cells often aren’t big enough to contain what you type into them. The solution is to expand the column (or row, if required) by hovering the mouse over the A, B, C heading until it’s covering the border of the next cell. Then can click and drag. This might sound a little confusing so I created an animated example: goo.gl/tmCJLm.
The invoice numbering system is up to you, of course, and should fit with any existing system you use. I usually pre-fill this column with sequential invoice numbers so each submission is automatically assigned one. And spreadsheet apps contain a clever feature to help do this.
Let’s say you’re up to invoice number 123 in your accounting system. Therefore, the next invoice you’ll send-out will be number 124. Type this under the Invoice Number heading. Then beneath this type 125, and beneath that type 126. Highlight all three cells by clicking and dragging, and then drag down the small square at the bottom right of the highlight. See what happens? The numbers automatically continue counting up. Again, if this sounds confusing see the following animation showing it in action: goo.gl/WRFSIy.
And that’s all the setup you need for your spreadsheet! From now on you can simply enter the information beneath the headings each time you send off a submission.
Note what happens when you type a date into a cell. The spreadsheet will probably autoformat it – 15 October 2016 will be changed to 31-Oct-16, or maybe 31/10/16. This is simply the spreadsheet app recognising the cell as containing a date, so that it knows how to deal with it in future.
However, the spreadsheet won’t realise when you type currency amounts: if you type £100, for example, it will think it’s text data, rather than a number. This causes havoc if the cell is part of a formula because it’s ignored, so any sum gives the wrong total. The trick is to highlight any cell that contains currency (or that will do so in future), and then click the currency button on the Home ribbon toolbar. The icon for this is usually a pile of coins, or perhaps currency symbols. Again, here’s an example of this in action: goo.gl/un515c.
Let’s say you want a cell to show how much money you’re owed across all of your submissions. Click a cell to the right of the main headings. In the formula toolbar which appears just beneath the ribbon near the top of the screen, click the fx button. This will pop-up a window showing formulas, but don’t worry about this. You need SUM and hopefully this will be already highlighted in the list, so just click OK. If it isn’t, type SUM, and then click Go followed by OK.
Next, highlight all the cells you want to add-up by clicking and dragging. Here you can cheat by selecting the entire column by clicking its column heading on the A, B, C row. Again, here’s an animated example of this whole procedure: goo.gl/NflnIG.
As always, I’ve run out of space this month with much left to say. One key spreadsheet trick is sorting data. You might choose to sort your submissions list by due date, for example, so that those due soon are listed at the top. The trick is to use something called a spreadsheet filter. There are many guides out there that you’ll find via Google.