Joys! Another post on the intricate un-wonders of Microsoft Excel. As all those who are unlucky enough to be Excel fiends know, there is enough about it that is a right royal pain in the ass. Anything that can make working with it a little easier has surely got to be welcome. Especially if it's free, given it's not always easy to get employers to actually give you anything involving £, and it feels morally wrong to make a personal investment into the mysterious lands of numbers in squares.

With that in mind, here's the Poorhouse's current top 3 free downloadable addons for others who spend double-digit hours a day inside this most gridular of programs. All guaranteed working for at least Excel 2003, and probably other versions too.

Name Manager Utility: from JKP Application Development Services. Now, any Excel user worth their salt must be familiar with range names. This of course it the delightful spreadsheet best practice that lets you type "=sales * profit" rather than =A6*B6. The only problem is that if you want to do anything vaguely clever after you defined the name - like edit, search, hide, evaluate or audit them to find out what they stand for, which are not used or refer to errors in the sheet - you can't. Not easily, anyway.

Chuck this bad-boy into your Excel addins, and suddenly life becomes all that much easier. It even handles switching between global and local scoped variables without issue, for you professional name-callers.

Moving onto the wonderful world of charts. The Poorhouse is all about the trend curves, honey. But there are some weird oddities and omissions from the standard Excel charts, that make one think that Excel was designed by people who find thrills in staring at 1000x1000 cells of unreadable numeric data than a nice straight line showing in one glance that you're about to go bust.

One handy addon is J-Walk charting tools from John Walkenbach's "The Spreadsheet Page".

This gives you a nice Jwalk option in the the chart menu. Open that, and a little dialogue box appears with the following tools:

• Data Labels - Lets you specify a worksheet range for the data labels for a chart series. Amazingly, Excel does not provide this capability!
• Chart Size - Lets you specify an exact size for a chart, or lets you make all charts the same size.
• Export - Lets you save charts as GIF, JPG, TIF, or PNG file.
• Picture - Converts a chart to a picture (color or grayscale)
• Text Size - Freezes the size of all text items in a chart. When the chart is resized, the text elements will not change size.
• Chart Report - Generates a summary report for all charts, or a detailed report for a single chart.

By far the most useful tool, in the Poorhouse's opinion, here is Data Labels. This lets you automatically label datapoints on your chart with any custom text from any other range on the spreadsheet. Normally, yes, you can label the columns of a chart automatically with their values or their categories, but that's about it, without fiddly, tedious manual editing. Here you can insert anything that you can put on a worksheet onto the charts as data labels for any series, which of course includes formula results. Wunderbar.

Now on to Clean Charts from Juice Analytics. Back on the subject of charts, anyone who has the misfortune to sit through a corporate Powerpoint and somehow managed to keep their eyes open will understand just how god-damn awful they look in the hands of a basic, or lazy, user. Heaven knows why Microsoft decided to make the presentation and colours so obviously poor, but they did. The godawful default grey background alone is enough to cause migrainery.

Sure, a lot of the presentation is configurable, but if you're pumping out 10s of charts a day, who can be bothered to go through and individually remove the mess? Although, as a quick aside, don't forget the semi-secret reasonably wonderful paste chart format option (select a nice chart, Copy, select a nasty chart, Paste Special, Formats - or of course making your own user defined chart formats to begin with). But even that's a nuisance to repeat, and doesn't always have great results if your 100 charts aren't all of the same type. So forget that for the minute.

This add-in adds a Clean Charts option to the Format menu, that can either act on 1 specific chart, or all on the sheet. When pressed, it goes through the charts of your choice removing the worst of the pointless gunk so those lovely data patterns are actually made visible.

Here's what they, truthfully, say it does:

• Removes "chart-junk" (the contrast-reducing light grey background on most Excel charts, extraneous lines)
• Formats the axes with easy to read numeric formats (22000 becomes "22k")
• Changes series colors to an optimally chosen set that are designed for maximum contrast and readability
• Removes 3D from the chart. 3D charts introduce distortions that make it hard for people to understand your numbers.
• Fixes axis scaling problems.
• Fixes font and marker sizes to make them readable if you have resized your chart

A couple of enhancements in the world of the Poorhouse would be to remove the huge gap between columns on those sort of charts, not remove the % format from the axes at times, and abolish the legend if there is only 1 series - but given the addin code is all available freely, you can VBA-program such extra features in to your heart's delights.

A before 'n' after shot is available here too, for your viewing pleasure. If you don't like the after more than the before, well, you're an idiot.

Aside from that, the Poorhouse currently just rocks 'n' rolls with a couple of the more analytical tools provided free with Excel, but often not turned on. Tools -> Addins is the menu for you if you need to enable them.

The Analysis Toolpak, and the Solver, are both pretty handy add-ins, if you're doing statistical or scenario shenanigans of a type some would say was rather more suited to specialist packages, but don't have the afore-mentioned specialist packages. The pure statisticians probably cry a bit at the thought of using these Excel tools, but for us down and dirty "business users", they are blatantly accurate enough and save untold time. It's not like the management are actually going to understand the results anyway, is it?

Last but not least, is the treasured Poorhouse custom toolbar; a mix of the more handy but by default non-visible functions splatted onto a nice easy to reach toolbar, and bits of self-made and/or stolen VBA.

It's nothing special, but out of interest - because it of course is fascinating - they are:

• Scenario changer
• Camera tool
• Unprotect all sheets
• Protect all sheets
• Trace Precedents
• Trace Dependents
• Remove All Arrows
• Select Visible Cells
• AutoFilter
• Show All
• The Variable Width columns VBA code from here
• A chart cleaner, the Poorhouse got irritated enough to make himself before the above-mentioned nicer "Clean Charts" tool was made available from Juice.
• ...and a smattering of non-visible custom Excel code to find the colour of a cell, always paste values & align axes on 2-axis charts