The Poorhouse spends a lot of time looking at dull grey grids of numbers. It's not a hobby per se, but it happens. As per pretty much any other normal business, these numbers appear a lot in Microsoft Excel where hours upon hours of top fun can be had moving them around a bit until they sort of hint at some sort of conclusion that makes you look good. But between staring at these dire digits comes time to prepare for staring at dire digits, which leads to magical shortcuts being discovered.

Below are a few of the Poorhouse's favourite Excel time-saving tips. Few, or possibly none, of them are remotely original, but the origin has got lost in the midst of time, as far as we're concerned. Certainly the macros came from somewhere other than the Poorhouse's mind. So if any of them are actually your tips, then, massive apologies, stake your claim below.

These should work at least in Excel XP and 2003, and possibly many others.

Mini-table of contents:

1. Colour in alternate lines of a table
2. Turbo auto-fill
3. Change relative references to absolute references
4. Copy only visible cells
5. Always paste as values
6. Retrieve the colour of a cell

Colour in alternate lines of a table

Style over substance, always! Sometimes one is required to paint a table nice and stripy to make it obvious which numbers are on the same row as other numbers, and preferably in some pastel corporate colours for the big bad presentation. You could colour them all in manually, or make use of the Format Painter brush, but really that gets dull quickly when we're talking many many rows.

Instead, make use of the conditional formatting tool. Select the table than needs this linear enhancements, and go to the Format menu, then "Conditional Formatting". In the resulting dropdown, change it to "Formula Is" and enter this into the formula box:

Hit the Format button to pick the format you'd like to see the alternate rows of the table appear in, hit OK and there you go, instant colourific row-based action. Whoo!

A more complex formula to insert as conditional, if you want every 3, 4, 5 or 1000000 rows coloured in is as follows:

where you replace [N] with the how many rows should be left between each colour - e.g. =MOD(ROW(),3)<>0 would format in every 3 rows in whichever format you choose in the conditional formatting box.

Turbo auto-fill

Everyone who knows what a formula in Excel is knows also the virtues of Fill Up / Fill Down / Fill Right and of course Fill Left. These let you batter out a sequence of values or a (potentially geographically relative) formula faster than Steve the bodyguard at an out-of-control Jerry Springer show.
But who'd want to faff through the menu for that? Here's the handy shortcuts, used many times per hour in the Poorhouse's world of numbers.

Fill down: Ctrl + D
Fill right: Ctrl + R

The others, to the Poorhouse's knowledge, don't have one-touch keyboard magic like that. Luckily they're the most common. They will fill from the top / left cell selected to the end of whatever you have selected at the time. So navigate and select the cell you want to replicate, expand the selection right / down (mouse or keyboard) and hit the ctrl combo.

Dragging with the mouse is a bit lame, so use the handy keyboard selection controls. Top of the list is probably Ctrl + Shift + End which selects any "used" region from the cell you have selected down and right. Generally, this will be the limits of your list, and exactly what you'd like to fill. Wonderbar. The intracices of Excel mean that this isn't always the case, especially when you've been messing around deleting, copying and pasting all over the shop, so it pays to learn other selection shortcuts too.

Finally, the Poorhouse's favourite ever fill option - the little draggy-box on the bottom right of the selected cell. Hard to describe, so below is a pic. But first, be assured that as well as its dragging magicness allowing mouse-based filling up, down, right or left, it has good double-click functionality. Double click it and you get an automatic Fill Down for every consecutive row that has already got data in a cell to the left.

That feels like the worst explanation ever, so here's a before 'n' after. Forgive the MS Paint based art-work.

It's slightly annoying to have to revert to a mouse based manoeuvre to be sure, but its advantage is in its recognition of the fact that generally where there is a blank line, that's often where you want your fill to stop, without having to scroll around for hours missing the exact point you intended to fill down to.

Change relative references to absolute references

Often kind of related to the fill-down type stuff, you'll be aware there's a difference between absolute and relative references in Excel formulae. Write the formula "=A2" into cell A1 of a sheet. Copy it to clipboard, and paste it into cell B1. Now check what you got in the formula bar for B1: it cleverly changed to "=B2"; the cell in the same relative position to B1 as A2 is to A1. It's usually what you want, but not always.

That's why there's the $ syntax. Repeat the above, but instead of "=A2" write "=$A$2". This makes the reference "absolute". Copy and paste that bad boy to cell B1. Check the formula bar in B1, and see that that the formula has remain =$A$2 and not changed to anything B related. Awesome, but who can be bothered will all that filling out $ signs everywhere (except at the bank, of course)?

Luckily, when you're working on a formula at the point of having just typed/selected a cell reference, hitting F4 there and then adds the correct $ signs to make it an absolute reference. Press it again, and you get a single dollar reference that is absolute, but only in the dimension of the row number, still relative in terms of column. F4 again here gives you the opposite 1-way absoluteness, and F4 a final time gets rid of the dollars entirely to bring you back to the default dollar-free relative cell reference.

It works after the fact too - if you change your mind about an existing formula, you can just highlight somewhere in the cell reference part of a formula in the formula bar and press F4 to make these changes happen to a formula you entered a long time ago, in a galaxy far away. So much better than micro-mouse fiddling with "insertion points" and the like, especially on a bumpy train.

Copy only visible cells

A lot of people love their auto-filters, show and hide rows technology and the like, but it can get annoying when trying to transplant what you can see on one sheet to another one. Simple Select-the-cells, Copy, Paste into the new sheet doesn't work well, because it brings across all that numeric filth you had spent your time obscuring on the first sheet. Boo.

A solution: well, perhaps there's an easier way, but this works. After you have set up the visibleness of the sheet as you want it, go to the Edit menu, and choose "Goto". Here, choose "Special", then "Visible cells only" and hit OK. You're returned to your sheet, with everything visible selected. It looks kind of like a select-all but when you copy and paste into another document, you'll realise that it has copied literally what you could see on the spreadsheet, rather than everything hidden away in the nooks and crannies of that dusty old worksheet.

That combination is a bit of a faff with the mouse mind. So instead, the key sequence to pull that off sans pointer is:

Bit mind boggling at first, but you get used to it fast enough in the Poorhouse's experience. And if you don't, or like pointing at things, then you can a "Select visible cells" button onto a toolbar for one-click usage. It's in the Tools, Customize menu. If you pick the Commands tab, choose the Edit category and scroll down on the right, eventually you come across an icon showing 4 black rectangles that performs "Select Visible Cells". Drag it to a toolbar, and Bob's your uncle for the forseeable future.

Always paste as values

For moving numerals around those formula laden sheets, the "paste as values" command is often a big help, otherwise you'll end up pasting innapropriate and broken relative formulas (see previous but one section) all over the shop. To do it, after you've copied your data to clipboard, you go to Edit, Paste Special, As Values, OK. Keyboard-wise this is alt-E, S, V, enter. But even that's a chore if you have to do it super lots of times. Instead, the Poorhouse likes a one-click keyboard shortcut.

To perform such wonders, use the below VBA macro. Save it as part of the workbook, preferably the default template if you want to use it everywhere, and assign it to a shortcut key of your choice (be careful not to accidentally overwrite any of your other favourite keys...). To assign a keyboard shortcut to macros is easy - just use the Tools menu to select Macro, then pick Macros to open a dialogue box of your installed macros. Click the one you want to assign a shortcut key to and choose "Options". In there you can pick which key, in conjunction with the Ctrl key, should run your magic macro. Note tht sometimes when you're pasting in macros rather than recording they don't seem to appear in that box, so just type their name and the relevant buttons should enable.

Retrieve the colour of a cell

Often - from those other than sympathetic analysts - you might get a pretty report to deal with where different categories of things are shown by colour. E.g. all the bad revenue figures are red, and the good ones are green. It's a pain to work with these unless there's another way of calculating "good" vs "bad" because Excel doesn't seem to have a function for showing what colour a cell is. You therefore can't really do something like "Sum up all the red figures", or "Count how many cells have a yellow background". Excel 2007 apparently has a lot of nice new functions so maybe you can do it there, but not in the older variants to the Poorhouse's knowledge.

Enter the below user-defined function (UDF). Wham this into some code accessible to your workbook, and now all of a sudden you can solve exactly the above problem.

Within your Excel sheet you can then use it as follows:

to return the colour of the text in cell A2

or

to return the colour of the background of cell A2

When the Poorhouse says "return the colour of" he actually means returning a number representing the colour. Unless you have a spreadsheet resembling vomit with quite how many colours it uses, trial and error is often a good way to find out which colour is which number. For instance, bright yellow appears to be colour index 6 in the Poorhouse's swift test.

Once you've done this, you can use the function in the same way as any other Excel function. If you'd like cell B1 to say "Yellow" whenever cell A1 is actually filled with background yellow for instance, this would do the trick: