Mother in law: Excel skills

At my appraisal in 2010 I asked for training on using Excel. I didn’t get it. I have had 10 appraisals since, at two different firms, and have asked for the same training at most of them. Each firm I have worked at has used Word for completion statements, bills and estate accounts and it really does my head in. The staff then use a normal calculator to do the adding and subtracting, which as we all know means that if you put in one digit wrong you have to start from scratch. I use my A level Maths scientific calculator so at least I have a bit of a screen and a delete button, but still it is a bit ridiculous when we could use Excel and have the sums done for us. At my first firm my secretary used the space bar alone to make different columns.

I still haven’t had the training, and as I’ve said before I do get annoyed at this lack of wider training in our profession. I asked on Twitter whether anyone would be interested in a blog with Excel tips, expecting that perhaps this blogger had turned into the most boring lawyer in the world, but later that day my laptop spontaneously combusted with all the other lawyers expressing their views to me on Excel. Most said that they would like some tips in words of one syllable. I will say that the 21 year old who spent Christmas Day 2004 getting wasted on Bondi Beach is dying inside that it has come to this, and I wonder if you should take a good look at yourselves too, but I am here to help.

Actually, to be honest, because I haven’t had the above-mentioned training, my secret weapon is here to help. I don’t know what my husband does for a living - even despite him doing it from our house for the last year - but it definitely involves Excel. Usually when I walk in on him he is picking pretty colours for the numbers. It pays the mortgage so I’m fine with it, and it means that only one of us needs to buy books about vicarious trauma in our profession. This blog is therefore written by Mother In Law and Husband Who Has To Hear A Lot About Law.

These are just some simple tips. They will be too simple for some people, but hopefully not too complicated for anyone. If you are like me and can remember the lyrics to all Taylor Swift songs including the speaking bits but can’t remember that Ctrl+Z means “undo”, I suggest you print this off to keep and eventually some of it should stick. For me, memorising just one or two of these points would help a lot.

Quick Shortcuts

1. Starting with some real generic basics, in case you are still using a quill, Ctrl+X,Ctrl+C and Ctrl+V mean cut, copy and paste.
2. Ctrl+B, Ctrl+U and Ctrl+I makes your text bold, underlined or italic.
3. Enter takes you down a row, and Shift+Enter takes you back up a row.
4. Ctrl+Z and Ctrl+Y are undo and redo.
5. Ctrl+F (“find”) allows you to type in a word for Excel to search the document for. Say for example you wanted to search for the word “bill” in a big set of estate accounts. Ctrl+H is similar but it allows you to replace that word with another. So, if you wanted to replace the word “bill” with “invoice” you could do it this way. Be careful though, because if your clients’ name is Bill Jones and you click “replace all” he will now be called Invoice Jones. If the word you choose to replace is part of another word, be even more careful. A copy editor told me once that someone decided to change the word “pants” to “trousers” by clicking “replace all” and they ended up with particitrousers.
6. Shift+ Space/Ctrl+Space selects an entire row/column respectively.
7. Once you have selected them, Ctrl+9 or Ctrl+0 will hide your rows/columns. Hiding is useful if you have too many rows or columns to work with on your screen. You can also freeze rows or columns by selecting the cell below/to the right of the row/column you want to freeze and going to View > Freeze Panes > Freeze Panes.
   
   Working with the Cells
   
   
8. You generally need to select cells in order to do anything with them. If you click the grey square in the top left corner (between “1” and “A”) you will select the whole workbook.
9. If you right-click and go to “Format Cells” there are options under “Number” for changing the numbers to a currency, with or without a £ or other sign. Under “Alignment” there are a number of options including “wrap text” which means that your words go onto more than one line with the row becoming deeper rather than disappearing off to the right.
10. You can copy this formatting to other cells by going to Home > Clipboard > Format Painter and using the brush to paint the other cells.
11. A Twitter user asked me to explain how to embed another document into a cell.  Obviously I knew how to do this. Go to Insert > Text > Object > Create from File then you can browse for your file. This can result in your spreadsheet becoming a very large file so you may prefer to insert a link to the document instead.
12. You can sort your data by alphabet, number, date and so on. This is extremely useful, one example being if you were preparing a chronology as you could enter the events as you thought of them or found them on your file and then sort them into date order after. Select the cells to be sorted and go to Data > Sort & Filter and then pick how you want them to be sorted.
    
    Basic Formulas and Functions
    
    
13. The advantage of using a formula or function (please don’t ask me to explain the difference) is that when the figures change, the calculations will automatically change for you.
14. The easiest of these is the Autosum button. It is the Greek symbol that looks like a pointy E (I went to a comprehensive school.) Select the cells you want added up and the next cell in the row or column, click the symbol and it will give you the total in that next cell.
15. In fact, whenever you select cells the total of them appears on the bottom right of the screen.
16. For what I am about to say next, you need to know that every cell has a name made up of the column letter and the row number eg A1 or H12. You should also know that in order to write out a formula, you can either write out those names or you can click on the cell and the name will appear in the formula you are writing.
    
    On the basis that most of us will be adding, subtracting, or maybe adding VAT:
    a. To add or subtract certain cells from others, click in the cell where you want the answer and type (for example) =A1+A2-D3. The formula should always start with an equals symbol.
    b. You can use a colon to refer to a range of cells. The formula =SUM(A1:A10) will add up the cells A1 to A10.
    c. The symbol * means multiply and / means divide. To get the 20% VAT figure on the number in cell B3, the formula would be =B3*20%.

There are a thousand other things my husband wants to show you all but I’m going to stop before I buy myself some Billabong shorts and start to rethink my life choices. I hope it has been helpful and do stay tuned for my next blog when no doubt I will be writing about another riveting subject of which I have very superficial knowledge.

*Some facts and identities have been altered in the above article