If you read this blog regularly, you know that I’m not a big fan of MS office. But most of my hate is clustered around Word and the silly WYSIWYG mentality. That said, Excel or rather spreadsheet tools in general get a free pass in my book. They can be genuinely useful for quick data tabulation and visualization. For example, when I grade exams, I like to put the scores in an excel sheet that will automatically calculate the 5 number summary and draw a nice scatter plot to show me the distribution of grades and help me figure out if I should curve or not. Granted, I could do the same thing with GnuPlot but it would take more effort and wouldn’t really improve my process in any significant way. So for that particular task the easy GUI driven app wins.
Then again, if you are doing any kind of serious data analysis, plain text is the way to go. Excel is not a database tool and it is not really an application suited to data mining like tasks – despite the fact people insist on using it that way. If you use Word and Excel for what they were intended too they actually work semi-reliably (Word less than excel due to the massive WYSIWYG fail that it is). So when you are struggling with a spreadsheet and fighting with it instead of getting work done, keep this in mind: perhaps you are using the wrong tool for the job. Perhaps you need a database here? Perhaps you should use a genuine data analysis tool. Or perhaps you just ran into one of those inexplicable random Microsoft issues that seem to happen all the time.
But I digress. I wanted this post to be about excel tricks that we all tend to pick up here or there whether we want it or not. Chances are that if you work in an office type environment, you will need to deal with at least minimal amount of Excel so share your favorite tricks in the comments and I will share mine.
I don’t know if you remember this, but a while ago I posted a neat trick that would allow you to create inline bar graphs in Excel. This was way before Excel 2007 added that feature in by default and it is sort of useless now, but still kinda interesting application of the excel formulas.
I also posted that bit about automagically adding check-boxes to a spreadsheet using a macro and that VB module that copies and pastes tables from excel to word.
Today I wanted to share two tricks that deal with conditional formatting.
Trick number one is highlighting duplicate entries in a column. Here is what you do:
- Go to the first cell in the column you want to search for duplicates and pull up the conditional formatting menu
- Change the pull-down box to “Formula Is”
- Use the following formula:
=COUNTIF($A$1:$A$20, A2)>1
- Pick appropriate formatting then grab the format painter thingy and drag it all over the other columns in range A1 to A20 (or whatever range you want)
This will apply your formatting to all duplicates – as in, every entry that appears more than once will be highlighted.
Speaking of conditional formatting, isn’t it annoying that Excel only allows you to define 3 conditions? I can sort of see why – it is all about performance. If you had 80 conditions, each would have to be evaluated every time a cell in your workbook changed which could show down the whole application down to a crawl. Still, 3 is a bit limiting. Why 3? Why not 5? Or 8?
Of course you can simulate conditional formatting using macros. If you define a Worksheet_Change subroutine for a given worksheet it will be called every time a cell is changed in that sheet. This allows you to apply conditional formatting to any given cell, according to any given criteria. Word of warning though – VBA is slow as shit, so I recommend limiting how many cells you want to check. First, start by defining a named range (I called in “MY_NAMED_RANGE”) and check if the changed cell is in that range. If it is not, then get out. Otherwise do the comparisons you need and apply formatting like so:
Private Sub Worksheet_Change(ByVal Target As Range)
If InRange(Target, Range("MY_NAMED_RANGE")) Then
For Each Cell In Target
Select Case Cell.Value
Case "foo"
Cell.Interior.ColorIndex = 3
Cell.Font.ColorIndex = 2
Case "bar"
Cell.Interior.ColorIndex = 4
Cell.Font.ColorIndex = 1
Case "baz"
Cell.Interior.ColorIndex = 6
Cell.Font.ColorIndex = 1
Case "foobar", "foobaz"
Cell.Interior.ColorIndex = 26
Cell.Font.ColorIndex = 1
Case "none"
Cell.Interior.ColorIndex = 48
Cell.Font.ColorIndex = 1
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.ColorIndex = 1
End Select
Next
End If
End Sub
If you need to look up the available ColorIndex values, I recommend checking out this reference page at mvps.org.
Ok, now it’s your turn. Do you have Excel/Open Office tricks? If yes, share them here.
No particular tricks, just stuff I’ve learned. Ever since I lost access to SAS, I’ve been using VBA to do monte carlo experiments. It isn’t as good as SAS, or as easy to visualize, but it is quick and dirty. Just last semester I learned what all the stuff in solver mean – shadow prices, allowable increases/decreases. And I’m finally proficient with pivot tables, I always could kind of mung them, but now I’m quick.
I’d forgotten about the in cell graphs, thanks for re-linking that!
I don’t use VB or anything fancy, but you’d be amazed at how many times people watch with awe when I navigate around a spreadsheet using plus arrow keys to jump to the beginning/end of rows/columns, to select, and things like to select whole columns, etc. Simple tricks can improve productivity massively. is another good one (delete cells).