I am sure I am not alone in that I use EXCEL a ton in my work. Of course over time you learn various little tricks and hints. Here is one of my useful, I have actually set it up as a custom toolbar button.
In a table it is often useful, for readability, to shade every other line. Instead of selecting every other row and then formatting it just do this:
Select the area you wish to highlight every oter row (including the rows you don’t wish to highlight).
2; Go to ‘Conditional Formatting’ in the Formatting menu.
Select ‘Formula Is’ and type in: =mod(row(),2)=1
Click the ‘Formats…’ button and select the ‘Patterns’ tab to select the color shading you want.
It’s not a trick per se, but vlookup is one of my favorite functions. I write a lot of custom financial reports and sometimes people want data together that I just can’t get out in one pass. So a lot of the time I end up building two reports and combining the data in Excel.
You can’t do that with a cell that’s going to be treated as a number. However, it’s often useful (e.g., when you are entering serial numbers). I need to do it as a librarian to enter ISBNs and Dewey numbers. The trick is to format the cell as text. (Use Format – Cells from the menu, or Ctrl+1). Then the initial zeroes stay.
There are two different ways to do this, depending on your needs.
Start by typing an apostrophe, e.g. '0123 The probelm with this is while the cell now says “0123” it is no longer a number. Some earlier versions of Excel won’t treat this as a number any more.
Format the cell (Format->Cells, Number tab, Custom category) as “00000” This will ensure that whatever you type will be (in this case) six characters wide, with leading zeros. (e.g. 000123, or 001234)
Wow, GO, you have no idea how useful that little trick will be for me! I keep the stats for my Friday poker game, and every week I update a spreadsheet and post it online – I’ve been doing the “select every other row” thing for months!
Something I learned a couple of years ago, and that I use for the poker stats, is how to take a screenshot of a spreadsheet:[ol]
[li]Select whatever rows/columsn you want to take a picture of.[/li][li]Hold down the SHIFT key and click the “Edit” menu.[/li][li]Click the “Copy Picture” menu item.[/li][/ol]
You’ll get a dialog box that gives you a couple of options: I always choose “As shown on screen” for the appearance and “Bitmap” for the format. Then I open the PhotoEditor application that comes with Windows, and do a “Paste as new image.”
The only other thing I know probably isn’t new to most people: if you’re in a cell and you press CTRL+’ (the single quote next to the ENTER key) it will copy the contents of the cell above it.
I’ve not used the apostrophe trick with numbers, though I have with text strings starting with “=”, that would otherwise be treated as formulae, and with other strings that Excel assumes are dates. It certainly would work, but the advantage of formatting as text is that you can do that with a whole column that’s going to contain some cells that have numbers starting with 0 – so you only have to do it once, not cell by cell.
I’ve recently discovered array formulas and the wonderous ctrl-shift-enter function. The versality of excel expanded tremendously after I learned these things. For instance, you can use an array formula to calculate the average of cells fitting multiple criteria. You can also count cells fitting multiple criteria, and a number of other things. OMG, array formulas have helped me so much!
Tonight I’m going to sit down and finally learn myself to do a pivot table. I hear pivot tables are even better than array formulas.
Other cool things: the “CONCATENATE” function. You use this if you want to combine two columns together. For example, let’s say in one column you have area codes and in the next column, you have the 7-digit telephone number. You would use “concatenate” to merge these columns so that you have 10 digits.
You use the “text to column” thingy under Data to do the opposite, i.e. break one column into two.
Lots of people don’t know that you can make a cell reference “fixed” by using a $ sign in the formula.
I use the find and replace function a lot. Say you have a column where each cell has a similar formula, but with different, non-sequential cell references. You realize you meant to type “AVERAGE” instead of “SUM” in the formulas. Just highlight the column, press ctrl-F, then “replace all” SUM with AVERAGE. This will save you from having to retype formulas individually. By being a little clever, you can also insert and delete things using this technique.
Freezing panes can be a sanity-saver if you’ve got a huge spreadsheet.
There is one thing that a friend taught me years ago that I find I still use. Let’s say you have two columns of data and you want to see if, in every row, the two cells match each other. Then you want to see that out of all the rows, how many don’t match. Let’s say that column A has the first set of data, and column B has the second set. In cell C1, type:
=if(A1=B1,0,1)
If they match, a zero comes up, if not, a one comes up. Then sum at the bottom and see how many non-matches there are in your list.
Nothing fancy, but damn if it doesn’t come in handy.
A lot of people don’t know that you can create a boolean argument that by itself will return TRUE or FALSE, but when used in a formula acts as 1 and 0.
Not really Excel specific, since it works in most windows applications, but nifty regardless.
The good old ‘ctrl’ ‘mousewheel’ zoom shortcut.
You have no idea how many work meetings I have to sit through where the presenter wastes time in Excel using the pull down zoom menu, typing 150%, deciding it isn’t right, retyping 160%, still not quite right, retyping 157%, nah…still not right…and so on. It’s like getting drilled at the dentist. When I’ve showed people the trick, they’ve been floored with joy, practically naming me their kids’ godfather in thanks.
I keep the stats for my Friday poker game, and every week I update a spreadsheet and post it online – I’ve been doing the “select every other row” thing for months!