Microsoft Excel is one of my favourite computer games. I find it endlessly playable. I even chose my dissertation topic with an eye on its potential for spreadsheet-based entertainment. But apparently not everyone feels the same way. Or perhaps they've just not had the opportunity to put in the same hours as I have, and never worked out how to get past that pesky end-of-level baddie in the Hall of Tortured Souls. So it was suggested to me on Twitter that I might cobble together a few Excel tips by way of a blog post.
I've been toying with Excel for about twelve years, from doing the accounts for a business I used to run to managing huge tournaments of a Formula One computer game, and most recently I racked up some stupidly big files while data-mining my way to a dissertation on catalogue search-term analysis. By and large, the skills I've picked up are more pertinent to data extraction than to simple accounting and record keeping (I am very much guilty of using Excel as a database, not least because I've always found it considerably more friendly than Access), but hopefully some of the stuff I'll mention here will be of some use. Some of it, on the other hand, will be baffling, and there will be the odd bit of egg-sucking to balance that out. I don't profess to know the full intricacies of Excel: I'm still very much learning myself. But over the course of the last twelve years, and particularly the last twelve months, here's some things which I've discovered and thought, if only I'd known that before:
EDIT: I appreciate that this list is a bit of a "wow, look at all the cool stuff" kind of mess, so I've also produced this little exercise that you can work through.
EDIT: I appreciate that this list is a bit of a "wow, look at all the cool stuff" kind of mess, so I've also produced this little exercise that you can work through.
A fistful of Dollars
=$A$1+B1
Suppose we want to add the figure in column A to the figure in column B. We can type =A1+B1, and ta-da, there's our answer. And we can then drag that cell down and expand the formula for each row of the column: =A2+B2, =A3+B3 etc. All very lovely. But suppose we wanted to add all the figures in column B to a single number in column A (i.e. =A1+B1, =A1+B2, =A1+B3...), we can't just drag. We could copy and paste into each cell, and then edit the reference accordingly, but we don't have to. We can use a fixed cell-reference instead. We fix the reference by adding a $ to the front of whichever coordinate(s) we want to maintain. $A$1 is a constant reference to cell A1, $A1 will fix to column A but return the relative row, and A$1 will fix to row 1 but will return a relative column.
A Colour Licence
Adding colour to a spreadsheet, be it cell colour or font colour, can be very helpful as a means of highlighting important data. It's also useful in so much as we can sort or filter by these colours. This can be particularly handy when used in conjunction with...
Conditional Formatting
On the Home menu, this is a useful tool for at-a-glance analysis and makes your spreadsheet very pretty in the process. As you add content and insert extra rows into your data, it's easy to get lost in overlapping conditionality, so it might be worth cleaning out any unused conditionality now and again using the Manage Rules dialogue.
Text to Columns
Ideal for imported data, but also handy for quickly splitting cells, the Text to Columns feature lives on the Data menu, and breaks up text into columns, either based on a fixed width or on a delimiting character of your choice.
Merging Cells
A pretty spreadsheet is a happy spreadsheet, and the Merge option on the Home menu is a button I use often enough to have it duplicated on the Quick Access Toolbar (something well worth customizing if you use Excel a lot).
Transposition and Other Pasty Plays
Perhaps we don't want our stuff arranged in columns. Perhaps we'd rather it were in rows. Well we can copy what we've got and then Paste Special, selecting Transpose in the dialogue. There's a load of other paste options in there too; particularly useful are Values (pastes the results rather than the formulae) and Format. We can also paste onto an existing array and perform a mathematical transformation in the process by using the Operation options.
Cleaning up the Content
=TRIM(A1)
=VALUE(A1)
TRIM will remove any unnecessary space characters in a text string so that there is only one space between each word (" ducks and donkeys " becomes "ducks and donkeys"). VALUE will ensure that anything that looks like a number is treated as a number rather than as text. Both are useful for cleaning up data before we start playing.
Concatenation
=CONCATENATE(A1," ",B1)
=A1&" "&B1
Got some bits of data in different cells that you'd really like to be in the same cell? =CONCATENATE(A1," ",B1) will paste together the contents of A1 and B1, with a space character between (thanks to the " "). To save on columns (and typing), you can even concatenate on the fly within formulae by using &: =A1&B1; =A1/(B1&C1) (the latter example relies somewhat on B1 and C1 being numbers).
Substitution
=SUBSTITUTE(A1," ","")
SUBSTITUTE is our search and replace mechanism. If we want all the 10s in column A to become 15s, we can =SUBSTITUTE(A1,10,15) etc. More complicated search and replace exercises (including particularly useful format replacements) can be done under ctrl-H (it should be pointed out that Excel's Find and Replace dialogue has a curious blind-spot on the "~" character, as I discovered to my annoyance during my dissertation). I've also been known to make search/replace alterations in Word (Word's ability to replace punctuation is useful when preparing material before importing into Excel).
Filter Tips
We can locate similar data using the Filter function on the Data menu. Filters are useful for comparing and working on related data within the larger set. We can filter by colour formatting and by various permutations of text content, as well as by cell values as a whole.
A Friendly Sort
We can order our data alphabetically, numerically, or by colour using the Sort function on the Data menu. If you're worried about messing up the order of your spreadsheet, add a new column and fill it with ascending numbers (you can auto-fill an ascending sequence in several ways; here's two: 1) type 1 in the first cell, 2 in the second, highlight both, and drag down to auto-fill the sequence; 2) click the bottom cell of an empty array, press Shift and Page Up together repeatedly to highlight all the way to the top, and use the Fill Series option from the Home menu). Now you can sort by this column to return to the previous order.
Macro Economics
Macros are your friends. They may seem daunting but they will save you so much time. You can set up a macro in the Developer menu. Just click on Record Macro, give it a name and a keyboard shortcut, carry out some laborious, mundane task that you're fed up of repeating (like formatting a cell or setting up an elaborate sort routine) and then stop recording. Remember to select Use Relative References first if you want your macro to operate relative to the highlighted cell rather than in a fixed location. Two important notes to bear in mind: 1) when you save your spreadsheet, you'll have to save it as macro-enabled if you want to be able to use the macros again; 2) (and this is especially important) you can't undo changes made by a macro, so save your spreadsheet before running one. These inconveniences aside (and the latter point is quite an inconvenience), macros are great.
Graphic Design
Excel's good, but it has one horrible flaw: its graphs are a pain to format. I would that this were not the case, because it is capable of making some lovely graphs if one has time to spare formatting every single data series or typing out label-sets for scattergrams. To explain how to get the most of charts and chart formatting requires a blog post of its own. But we can plot some pretty things if we put our minds to it. We can scatter-plot coordinates to map locations, or use a surface chart to generate a relief, and afterwards we can feel smug about our achievements, happy in the knowledge that we have triumphed in the face of adversity.
A Domesday map of Strafforth plotted using Excel.
Doing your Sums
=SUM(A1:A10)
Simple but useful, this adds up all the cells from A1 through to A10. Beats typing A1+A2+... Current versions of Excel also give the sum of a selected array of cells (along with the count and arithmetic mean) in the display at the bottom right of the screen (very handy for quick reference purposes).
What's in a Name?
The Name Manager on the Formulas menu allows us to give shorthand names to data arrays. If we find we're referring to A1:A10 a lot, we can go to the Name Manager and give this array the alias "a", allowing us to write =SUM(a) instead of =SUM(A1:A10). This is especially useful for more complicated arrays.
If...
=IF(A1=B1,"Oh, wow! A1=B1!","Meh...")
A straightforward conditional formula. We can nest up to eight IFs, i.e. =IF(A1<B1,B1,IF(A1=B1,B1)) which gives us the largest value of either A1 or B1. Many of the things we can achieve with IF can be done in a simpler way with other commands such as MAX and...
Choose Life
=CHOOSE(A1,"1st","2nd","3rd")
Untangling Knots
It's easy to get lost in formulae. Fortunately, the Formulas menu has some useful tools including Show Formulas which does what it sounds like it might, and Trace Precedents / Trace Dependents which draw lots of coloured arrows all over the place to show the trail of your data that you might more easily unpick your knitting.
Coming to terms with ones Errors
=IFERROR(A1,0)
=ERROR.TYPE(A1)
Fed up of pesky errors such as #VALUE! or #DIV/0! messing up your spreadsheet? Stick in an IFERROR and it will clean any error away and replace it with whatever you prefer (in the above case, "0"). If you want to differentiate between different error types, you can use ERROR.TYPE to return the numerical value for the error: #NULL!=1, #DIV/0!=2, #VALUE!=3, #REF!=4, #NAME?=5, #NUM!=6, #N/A=7 and #GETTING_DATA=8.
Big Ones, Small Ones, Some as Big as Your Head
=LARGE(A1:A10,1)
=MAX(A1:A10)
=SMALL(A1:A10,1)
=MIN(A1:A10)
=RANK(B1,A1:A10,0)
In the above examples, the paired formulae produce the same result: =LARGE(A1:A10,1) and =MAX(A1:A10) both find the biggest number in the array (A1:A10), for instance. But LARGE and SMALL can also find the nth largest or smallest number. If we wanted the third largest number in an array we'd use =LARGE(A1:A10,3).
RANK is a related function. In the example given, we are finding the numerical position of the value B1 among the values in the array A1:A10. The "0" indicates that we are ranking in descending order ("1" would be ascending). So if A1:A10 were the numbers 1 to 10, and B1 was 1, this formula will give us a value of "1" (i.e. "1st"), and the same result would occur if the values of A1:A10 were 10 to 1 or any jumble of those numbers.
=LEN(A1)
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1
LEN is a simple little function: on its own it will give us the number of characters in a cell. There is no equivalent tool for measuring the number of words, but we can use the longer formula above, employing TRIM (to get rid of any extraneous spaces) and SUBSTITUTE (removing all the spaces) to effectively do a space-character count, and hence (by adding the 1) the cell's wordcount.
=COUNT(A1:A20)
=COUNTA(A1:A20)
=COUNTBLANK(A1:A20)
=COUNTIF(A1:A20,"hello")
=COUNTIFS(A1:A20,"hello",B1:B20,"world")
Here are a handful of ways of counting cells. COUNT counts cells that contain any number, COUNTA counts cells that contain anything at all, COUNTBLANK counts cells that contain nothing at all, and COUNTIF counts cells that match a defined value ("hello" in the example above). There's also COUNTIFS, which will count entries fulfilling multiple criteria: in this case, "hello" and "world" in adjacent cells of a two-column range.
String Theory
String Theory
=LEFT(A1,5)
=MID(A1,1,5)
=RIGHT(A1,5)
=MID(A1,LEN(A1)-4,5)
The above pairs of formulae return the same content as each other: the first pair will give the first five characters in a cell, the second pair will give the last five. This is useful for data extraction, but we can do more with MID if we feel compelled. We can, for instance, further cut down on any IF / CHOICE programming by creating a reference string. For example, we could convert a number in cell A1 to a letter using a reference string such as "abcdefghijklmnopqrstuvwxyz" in B1: =MID($B$1,A1,1). We can reverse this process using:
Advanced String Theory
=FIND(A1,$B$1)
Keeping the "abcdefghijklmnopqrstuvwxyz" string at B1, but with letters in A1, this formula returns the numeric position, so "a" (and indeed "abc") = 1, "z" = 26 etc. FIND is case sensitive, so we might also choose to make use of...
Case Sensitive
=UPPER(A1)
=LOWER(A1)
These functions standardize the case of a text. There's also PROPER Which Capitalizes Everything. On a related note:
Code Selfish
=CODE(A1)
=CHAR(A1)
CODE operates using an internal text string: the Unicode character reference. It takes the first character in a given cell and converts it to the decimal version of the Unicode reference for that character, so "0"=48, "A"=65, "a"=97. The process can be reverse using the CHAR function. If we start messing around with Unicode references, we might also find the following useful:
Hex Enduction Hour
=DEC2HEX(A1)
=HEX2DEC(A1)
These functions convert a decimal number to a hexadecimal one and vice-versa. There's also some binary functions. It saves having to do complicated base conversions using maths. Speaking of different bases:
The Trouble with Time
=TIME(A1,B1,C1)
=HOUR(A1)
=MINUTE(A1)
=SECOND(A1)
=QUOTIENT(A1,1)
=(MOD(A1,1))*100
Time, with its pesky division into 60s, is a troublesome beast, especially if we're processing imported data. Excel treats time as a part of a larger date, and as a fixed point rather than a duration. Both these details can prove problematic. The best approach I've found is to keep separate any hours, minutes and seconds in different columns, and then combine them using the TIME function above (I then give it a pretty custom cell format: "hh:mm'ss""). The process can be reversed using the HOUR, MINUTE and SECOND functions. If a time has been entered as a decimal (e.g. 1.3 rather than 01:30) we can separate the numerator and denominator using the QUOTIENT and MOD functions (multiplying the latter by 100 to get "30" rather than "0.3"). This latter trick applies for any decimal expression, of course. We can also play about with these various functions to create decimalised times if required.
Blinda Data
=DATEVALUE(A1)
This time function is particularly handy for extracting Excel-friendly date values from imported text. Suppose cell A1 contains "21st May: Went swimming", we can do something like: =DATEVALUE(LEFT(SUBSTITUTE(A1,"st",""),6)) to shave off the "st" and work our magic on "21 May". We can then format the result as a date in the cell format options. DATEVALUE ignores blank space, so if we're doing a batch of diary entries of the same format we can safely replace the colons with a lot of spaces and up the character limit for extraction so as to include dates of a different character length.
=NOW()
Today's date and time. Handy for date-maths.
Then Then
=ROMAN(40)
A spreadsheet based pun there for you all. x
No comments:
Post a Comment