Tag Archives: Excel

Flying solo: Using your records to price jobs and make business decisions

This article by Sue Littleford, for our regular Flying Solo column in member newsletter The Edit, looks at how to use Excel’s filter function to help you decide how much you’d like to be paid and how long you need to do the work. It also considers some other decisions you may want your records to help you with.

The Going Solo Toolkit contains a spreadsheet for you to record your work (available to CIEP members only). There’s an older, simpler work record available, and I daresay many people have devised their own record-keeping system.

Everything you could ever want to know about calculating a price for a job is covered by the CIEP guide Pricing a Project: How to prepare a professional quotation by Melanie Thompson. So, this article is going to look at how to extract the information from records to help you arrive at a decision on how much you’d want to be paid, and how long you need to do the work, and then take a look at some other decisions you may want your records to help you with.

You already know that you need to record the work you do for your tax return, and you already know that if you record – or calculate – the right detail you can use that record to inform your estimating and quotes for jobs, both in time and in money.

But I know that many people struggle a bit with using Excel, so how on earth do you get the information back out of the spreadsheet? The more data you have, the more useful your records should be, but the more data you have, the harder it can be to see at a glance what you want to find out.

The filter

I’m going to talk about just one tool in Excel: the filter. Once you get to grips with it – and that won’t take long – you can use that same tool over and over, layering it up, even, to get an analysis out of your records of whatever information you’re focused on.

You can, of course, sort your rows of data in order, just as you can in Word, but the filter doesn’t disturb the original order of your spreadsheet, and enables you to do what amounts to multiple sorting, so it’s more powerful and less bothersome, which is why I use it so much.

I’m going to make a big assumption and show examples based on the Work Record spreadsheet from the toolkit. The principles are exactly the same, as long as you keep your records in Excel. If you keep your records in Word or on paper, you won’t have access to these features. Perhaps this article will encourage you to give Excel a try!

The screenshots here are taken on a PC running Office 365. For simplicity’s sake, I’ve hidden a few columns.

If you use a Mac, the principles involved are still relevant to you, and I’ve linked at the end to some YouTube videos, and Microsoft articles, on filtering for both Macs and PCs.

Right, to business.

The filter is your best friend when it comes to interrogating your spreadsheet. It’s in the Editing group on the Home tab, and it looks like this: Click on it, and you’ll see this:

The filter lets you select all records in a column with a particular value, but you can be a bit fancy about the values you use.

Let’s keep it simple for now.

The handiest way is to make the filter available on every column in your spreadsheet. You can do it the really easy way, and just click at the top left corner where the row labels meet the column labels, where the little triangle is:

Then click on Sort and Filter, then Filter, as you can see in the first two screenshots. A little down arrow will appear at the top of each column (you can’t filter by rows), and they look like this:Click on any of those down arrows and you’ll see you get a list of the unique entries in that column – and if you’re looking at dates, you’ll get a family-tree-like menu with checkboxes, so you can select by year, by month or by day.

Now you’re cooking!

Select an item, and your spreadsheet will now show you only the rows that match that selection. So you can filter by client, by type of work, by fee earned, by imprint, by word count, by rate per hour …

Coming to a price – or evaluating a proffered fee

Suppose you’re asked to do a copyedit of 75,000 words. To get a feel for how long that takes you, and how much you’ll want to charge, you may decide to filter your records to jobs in the range 70,000–80,000 words.

This is the starting view of the spreadsheet I mocked up for this article:Clicking on the little down arrow at the head of the word count column will give you a list of the unique values in that column, sorted in ascending order:

Now you have a choice of how to proceed. You could simply uncheck the (Select All) box, then either click every value between 70,020 and 79,989 words, or you could click on the Number Filters option, just above it.

That gives you more options: above average, below average, Top 10 … but let’s keep to the immediate job in hand. You want to filter on the jobs you’ve already done, between 70,000 and 80,000 words. So click on the Greater Than option:type 70000 in the first box, keep the And radio button selected and then use the short drop-down box beneath to select ‘is less than’ and type in 80000:and click OK. The box will disappear and you’re left with a list of rows of data where the word count falls between those two values. As you can see, the original row order is preserved.You can stack up filters, so can filter down to just proofreading jobs or just copyediting jobs, or just developmental editing jobs, or what have you. Because I’m currently assessing a copyediting job, that’s what I’m going to pick.And that shrinks the list further, to:Now you can easily see what you’ve earned before doing copyediting for that kind of word count. You can see jobs ranging from 48 to 1,021 notes. What about the job you’re assessing? Where does it fall on this spectrum? You can see that jobs had few tables, but up to 52 figures, and you had a mixture of Harvard and short-title referencing. You can see how many days overall the project took, how many hours, what kind of speed you were achieving and whether you had a large number of authors or just one.

If you work in fiction, or in some trade nonfiction, you may have no notes, no references, and quite possibly no tables or figures. But you would still see your editing or proofreading speed, what you got paid and how complex the job was.

Whether you use just one filter, or whether you stack them, you do need to turn them all off once you’ve finished, or risk being really confused next time you open the spreadsheet, with a chunk of your data apparently missing (I speak from experience!). You can see which column(s) a filter is active on as it changes from the simple down arrow you’ve already seen to this, a down arrow alongside the filter symbol: 

You can see it in situ on the type of work and word count columns. Click on each active one and select the ‘Clear filter from …’ option, or to clear all the filters in one action, go back to the original Sort and Filter button and click on it, then either on Filter (to toggle it off) or on the option below, Clear.

Establishing a probable duration

Using these same principles, you can estimate how long the job is likely to take you. In the last screenshot of the filtered spreadsheet, you can see the words per hour range from 1,111 to 1,743. You still have the information about complexity – referencing, notes, artwork – so how does that stack up against the job you’re evaluating, or quoting on? Are you likely to be nearer the lower or upper end of this range?

When you’ve worked out how many hours you’ll probably need for the job, you can then see when and whether the job will fit in your schedule, assuming that the quality of the raw manuscript isn’t significantly different from the manuscripts you’ve already edited or proofread. That’s a big assumption, so think about that, too, when you’re looking at the sample to provide a quote, or the full manuscript to decide whether the fee being offered will be enough.

At the left-hand side of the spreadsheet, you have the number of elapsed days that the previous jobs took. That will also help you decide whether you can take the job on: how well does it fit in your schedule? And what else is happening at that time? Do you have some other commitments in your diary? Did you plan on taking some time off?

Incidentally, I’ve personalised my copy of the spreadsheet and include a column for days worked, too, that I take from the time-log for the job. I can then tell whether I was all-hands-to-the-pump or whether I was working at a more sedate pace. For the first job in the screenshot, 29 days elapsed from starting work to finishing it. But did I actually work 15 days, 20 days, 29 days of the 29? That will fine-tune the information and help me decide whether I can fit the proffered job in the timescale wanted.

I always give myself plenty of wiggle room when working for indie authors, particularly, and when necessary I negotiate with corporate clients on the deadline as well as the fee. Life happens, as the saying goes, so you want to have the time to do the work at a sensible and safe pace, and have some time in your back pocket for contingencies.

The information stored in your spreadsheet can support your quotations and evaluations, if you actively use it.

Who is my worst payer?

This is a question you should ask yourself at least once a year. And when you’ve found out who it is, in an ideal world you would fire them and make room for better-paying clients. You may have a sound reason to stick with a low-paying client, of course. Perhaps it’s work for a cause you want to support. Perhaps the work is really interesting, the deadlines are sensible and the client is a delight to work with. Then aim for the next worst-paying and fire them, instead! Don’t burn your bridges, though; just be ‘too busy’ the next time they ask so it’s open to you to accept work from them another time, if you need to or just want to.

Always take a look at the latest version of the CIEP’s suggested minimum rates, to inform your thinking.

But the first step is finding out who is your least-good payer.

The Work Record spreadsheet in the Going Solo Toolkit gives you two ways of looking at your earnings: per hour and per thousand words.

First, if you offer more than one service, you’ll want to filter by service: copyediting, developmental editing, project management or proofreading, for example. There’s no point comparing oranges with apples.

We’ve already seen that putting a filter on a column and then opening up the filter gives you the unique values in that column, listed in ascending order. That may be all you need. Select the lowest value – or two, or three or more – and see who those clients are. Or filter on numerical values, less than £x per hour, or less than £x per thousand words.

There’s an ultra-quick way of taking a look at how much your clients vary, though. Click on the top of the column to select it, and look in the status bar at the foot of the spreadsheet, towards the right side. If I select the £ per hour column in the spreadsheet I mocked up for this article, I see this:That shows me that my rate per hour runs on a spectrum from £21.98 to £38.11, via an average of £30.28. If you do this on a spreadsheet of your own, and don’t get these values showing up, then right-click on the status bar and start ticking some of the options in the pop-up box:The ones you want are in the third section from the bottom of this list, from ‘Average’ to ‘Sum’.

Am I happy to be earning £21.98 per hour? What’s the minimum I want to accept from now on? Let’s say £27.50 for the sake of demonstration.

So on the £ per hour column, I set the number filter to ‘less than 27.5’, in the same way as before, and I get this result:

I can see that the three clients who paid me less than £27.50 per hour are all different, and for only one job each. Two of the three worst payers per hour were for proofreading. But look at that difference in £ per 000 words: from £12.88 to £17.68, although the lowest rate per 000 words paid more per hour than the job below it. I can see that the good £ per 000 words job had a lot of references and notes, which would have slowed me down, and yes, that’s reflected in the words per hour column. Do I want to fire Barbara Seville? Let’s take another look at the data and filter just the jobs for that publisher, turning off the filter on the £ per 000 words column to get everything for that publisher:Now I can see that low fee in context. That low rate is looking like an anomaly, and all the other jobs I’ve done for them paid more than my line in the sand of £27.50 per hour. Maybe I’ll keep accepting work from Barbara Seville, but keep an eye on how the fees work out.

The worst payer of all was Barry Island Press, so let’s look at them in context. Clear the checkbox for Barbara Seville from the filter and tick Barry Island Press:I’ve only done two jobs for them, but I can see that their proofreading rate is a lot less than their copyediting rate, although it’s hovering just a few pennies above the suggested minimum hourly rate (as at the time of this writing). Maybe my decision will be to decline any proofreading from them, but keep on copyediting for them – or maybe I’ll decide to ask for an increased fee, if I otherwise like working for them, and see what happens.

Play around with the filters: in the numerical filters, the above average and below average are useful for deciding who you want to work for less, and who you want to work for more often. The Top Ten option allows you to pick a number other than ten, and the bottom as well as the top of the pile – you could easily find your top three earning jobs, your bottom three earning jobs, or ten jobs, actually up to 500 in either direction, but I suppose ‘Top Ten’ is snappier than the ‘Top or Bottom up to 500’.

Additional help

There are, as you may expect, YouTube videos and other resources on how filtering works. Here’s a small selection:

For PCs

  1. Microsoft’s instructions on how to apply filters: https://support.microsoft.com/en-us/office/filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e#ID0EAACAAA=Windows
  2. This video is by a Microsoft employee: youtube.com/watch?v=BtiVbY7lhqw
  3. Here’s another guide on filtering, which includes keyboard shortcuts if you prefer those to all-mouse: youtube.com/watch?v=wMlTDXPEjag
  4. And if you’re very new to spreadsheets, here’s a beginner’s YouTube video: youtube.com/watch?v=k1VUZEVuDJ8

For Macs

  1. Microsoft’s instructions on how to apply filters: https://support.microsoft.com/en-us/office/filter-a-list-of-data-8ec38534-e2f1-41d0-b8bb-e3f5fcad95a0?ui=en-US&rs=en-US&ad=US
  2. Some intermediate features, including filters, are covered in this video (if you want to skip to filters, they show up at 19:34): youtube.com/watch?v=Z9sKEjHaIm4
  3. And if you’re very new to spreadsheets, here’s a beginner’s YouTube video: youtube.com/watch?v=znqfM4ligew

About Sue Littleford

Sue LittlefordSue Littleford is the author of the CIEP guide Going Solo, now in its second edition. She went solo with her own freelance copyediting business, Apt Words, in March 2007 and specialises in scholarly humanities and social sciences.

 

 

About the CIEP

The Chartered Institute of Editing and Proofreading (CIEP) is a non-profit body promoting excellence in English language editing. We set and demonstrate editorial standards, and we are a community, training hub and support network for editorial professionals – the people who work to make text accurate, clear and fit for purpose.

Find out more about:

 

Photo credits: numbers by Mika Baumeister on Unsplash.

Posted by Abi Saffrey, CIEP blog coordinator.

The views expressed here do not necessarily reflect those of the CIEP.

What’s e-new? 21 for 2021

By Andy Coulson

In keeping with the theme of ‘Practical tools to boost your business’, I’ve come up with a list of 21 tech tips for 2021. These are all things I’ve tried and tested myself.

1. Have a system

This is the single most important tip of the lot. Find a system to organise and manage your work that works for you, and use it. It doesn’t matter if it’s a notebook and pen or a complex program. Keeping good records and knowing what you have done and what you still have to do saves you time and effort (and stress!). Some of the tools below will help with that.

Word

2. Learn Word

I suspect that for most of us, Word is our most-used tool. Within this there are lots of really useful smaller tools that can help you edit faster and more efficiently. Tools like styles, templates and wildcard Find and Replace can all help. Take some time to learn how to use these – the CIEP offers a ‘Word for Practical Editing’ course that covers them. Also, look into customising Word’s various autocorrect and autoformat options, which can save you a lot of time and heartache.

3. Macros

While we’re on the subject of Word, macros are another timesaver. These allow you to automate certain actions, so a couple of keystrokes can run a series of actions that would normally take multiple keystrokes. Karen Cox and Paul Beverley both gave excellent presentations on the subject at this year’s CIEP conference. Paul’s ‘Starting Macros but Slowly’ video on YouTube is a good place to start.

4. PerfectIt

Staying with Word, PerfectIt is an add-on that allows you to check consistency and apply style rules to documents. It comes with a number of good style files, but you will benefit from investing time in learning how to edit and customise these. Daniel Heuman also gave a useful presentation on customising PerfectIt at the CIEP conference. Intelligent Editing’s website, the PerfectIt Users Facebook group and the CIEP forums are also good resources.

Office

5. Learn Excel

I’m guessing you bought Microsoft Office to get Word? But it also means you have access to other programs that you may not be using to their full extent. Take Excel – it lets you do lots of things involving numbers or data. You can manage your accounts, keep lists of jobs and record the time and quantity of work involved. Maya Berger’s presentation at this year’s CIEP conference was a great introduction to this. Excel’s in-built help with formulas walks you through using them, allowing you to experiment.

6. OneDrive

With Office you also get access to Microsoft’s OneDrive online cloud storage. Some of my clients have expressed concerns about the terms and conditions on other cloud storage services, but so far OneDrive seems to be a broadly accepted solution. I use this to back up all my files, and it has an easy-to-use share feature that allows you to share a folder with a client. This is a reliable way to return finished files – simply right-click on the file or folder, click ‘Share’ and then send the file or copy a link and send that via email.

7. Windows App Explorer/Mac App Store

Just as on your phone or tablet, the major computer operating systems now tend to have an App Store. It is well worth having a poke around in these, as there are often some little gems available.

8. OneNote

One such gem (although it can be a real Marmite application!) is OneNote. This is available as an app or as part of the Microsoft 365 subscription. It is a digital note-taking app that allows you to capture freeform notes. On a tablet it supports freehand drawings, too. I’ve used it as an alternative to a pad for making notes as I work.

Clipboard tools and text expanders

9. ClipX

ClipX is a small, free clipboard expander. It lets you access a list of your most recent clipboard entries and reuse them. It also supports add-ins, one such being Stickies, which is an editable list of sticky items that you can paste in. I use that for common tags or comments when I have to tag a file.

10. TextExpander

Text expanders take a short key sequence and expand them into a longer piece of text, eg typing ‘\pfa’ might expand into ‘please find attached the requested file’. TextExpander works across the programs on your computer and allows you to build a library of replacements and retain formatting.

11. Phrase Express

Phrase Express is an alternative to TextExpander. It seems to be aimed at corporates with multiple users, but is still very usable on a single machine.

Time

12. toggl track

The recently rebranded toggl track is probably the best-known time tracker. The website, browser plug-in and app allow quick and easy time tracking. You can group these by project and customer, and break projects into smaller steps (eg by chapter). The free version is very usable, and the paid plans can also handle billing.

13. Harvest

Harvest is another time-tracking app, but is more focused on billing. It has a free trial period, but after that it is paid for.

14. RescueTime

RescueTime tracks your time in the background and allows you to see where you have spent the time on your computer and your phone. It is intended to help you manage your computer time and see where your time-wasters are. I find it helpful as a back-up to toggl track. If I forget to start a timer, RescueTime can tell me I spent five hours on Word, for example.

15. Pomodoro

Pomodoro is a productivity technique that involves breaking your work up into chunks (typically 25 minutes) with short breaks between. This is supposed to help with focus, but I use it from time to time just to remind myself to get up and move. There are many good apps to support this, but look out for ones where you can adjust the times to suit you. Search for Pomodoro timers in your search engine or app store.

16. Break timers

Break timers encourage you to step away from the screen for a bit. There are many options available. I like Workrave, which is free, configurable and features sheep.

Dictionaries

17. Lexico/OED

We all need a dictionary, and the OED recently created a free version called Lexico. It uses the same interface as the full subscription product, so it remains easy to use. (The full product is often available through your local library.) You can also access other dictionaries online for free, such as Collins and Merriam-Webster.

PDFs

18. PDFCandy

I’ve talked about PDFCandy before. I think it creates the cleanest, most accurate Word files from a PDF of any of the tools I’ve tried, even with multi-column magazine layouts. The web-based tool is free to use.

Organisation tools

19. Todoist

If you like using to-do lists to organise yourself, then Todoist is my favourite tool to manage these. It will work on a computer or on a phone or tablet (and will sync across platforms) and has both free and paid for versions.

20. ClickUp

ClickUp is my organisation tool of choice. It has quite a steep learning curve because it is very flexible. It enables you to capture information about your jobs and organise and present that in a lot of different ways. I use it mainly for tracking progress, record keeping and planning my availability.

21. Explore!

Many of the tools mentioned are free, have a free version or allow a trial period. It is worth experimenting with tools to see if they can improve your system. Always try and run them together with your existing, tried-and-trusted system while you test them out.

Andy Coulson is a reformed engineer and primary teacher, and a Professional Member of CIEP. He is a copyeditor and proofreader specialising In STEM subjects and odd formats like LaTeX.

 

 


‘What’s e-new?’ was a regular column in the SfEP’s magazine for members, Editing Matters. The column has moved onto the blog until its new home on the CIEP website is ready.

Members can browse the Editing Matters back catalogue through the Members’ Area.


Photo credits: partially open laptop by Tianyi Ma; hourglass by NeONBRAND, both on Unsplash

Posted by Abi Saffrey, CIEP blog coordinator.

The views expressed here do not necessarily reflect those of the CIEP.