Podcast update… what, why no iTunes

For those of you who don’t know who Big Bob Phillips is (which lets be honest, is no one reading this blog), he’s an Excel MVP, the host of Excel Do Dynamic Does, an avid cricket enthusiast, and one presumes a fan of the late, great George Best, or Bestie as he was also know. Anyway this completely unnecessary detail is provided to basically flesh out what is in fact a very boring story. Bob pointed out that the last podcast was not showing up in iTunes.

In fact what has happened is that the wordpress plug in I used for podcasting has become compatible with the current version of wordpress. The result was the RSS feed did not get updated, hopefully this will now be fixed, as I have installed another plug in, so this is a repost to try and get the RSS feed sorted out, sorry…

Excel Workbook Size Reduction

As you work and work on a spreadsheet it has a tendency to grow in size. This is caused by adding more data, formulas and charts etc, but as file size grows workbooks can become unstable. I’m not sure what the current thinking is, but workbooks over 100mb always used to worry me. I know some folks will have bigger ones that are stable, but it’s just a rough guide.

Anyway it turns out that it’s not only the data, formulas and charts that effect work book size, but other, possibly less obvious things too.

Luckily there are some steps we can take to help reduce workbook size, making them more stable and reducing the file size on disk – think e-mail.

The effect will differ massively depending on the workbook you start with, but for illustration, this shows what happen to a workbook after applying each method in isolation.

What are the options?

  • Zip the file – and remember if you’re trying to get the file under a certain size, maybe to get it through a corporate firewall, other zip applications are about, which can give smaller file sizes than the built in windows one.
  • Used Range – Clearing up the used range means that empty cell don’t get sotred in the file, Excel is not great at clearing up it’s owe file structure, good news is its easy to fix, see here.
  • Removing any VBA code from the file can help reduce the file size – it’s not always possible, but if it is, maybe you could try it! Also watch out of userform with massive graphic files embed in them – classic!
  • Remove Formatting. Having lots of complex formatting is a key driver for file size, removing unnecessary or overly complex formatting can drastically reduce the size of you files.
  • Rob Bovey’s code clearer, strips out VBA models can re imports them, this can help clean them up – if you been working on a bit VBA project, this can help – big time!

So there you go, a few ideas about how to reduce file size, maybe these will help someone.

The MIE Podcasts – Rob Collie

I reckon 2010 is going to be a good year. It’s getting off to a great start here on MIE, with a podcast from Rob Collie of Microsoft. Rob, for those of you who don’t know, is a leading light in the world of powerpivot, and run  powerpivotpro.com, a blog devoted to powerpivot and BI.

If you haven’t head about powerpivot yet, have a listen and find out more, if you’ve been playing with it, I think you’ll find this quite interesting.

Hockey Hero - Rob Collie

Get listening here:

[podcast]http://mie.jellycast.com/pod/20091218 Rob Collie on Power Pivot.mp3[/podcast]

Feel free to leave some comments with any feedback about this podcast, and also any ideas for future podcasts, thanks.

Side Note:

I believe that we will need to find a suitable acronym for poweropivot, suggestions? pvp, pp, pptv, pvt??? Important stuff!

On the use of Tabs

Tabs are everywhere. The most common complaint is that they allow lazy UI design. It’s easy to see why that argument has come about, but tabs are undeniably useful, to break things up, or as a grouping control. For my money, here’s the 101.

Excel’s option dialog is awash with tabs, 13 all told. I’ve been using Excel for years, I still can’t remember where all the options are, I know there in there somewhere, but often times I’ll have to do a bit of hunting. Not clever!


MS Project is better, the tabs are all the same size which is an improvement, but still look at how many there are – 3 rows no less!

Firefox uses pictures, which I like, but its real win is that there’re only 7 tabs - on one line. Ok, so Firefox has fewer features than Excel or MSP. Still that’s not the point, the point is the number is manageable – having everything on one line is “Log2(N-1)”* times easier to consume than multiple line.

Best of all I like tabs down the side. I don’t think they work in all cases, but for something like options, down the side is good. Office 2007+ has swapped to this design in a lot of its dialogs. I don’t love its implementation, but the ideas good. Visual studio does it well. There are tabs across the top, so another “sub set” would be confusing, down the side makes sense. UI design should be about making sense! Also it’s really well done. Look how the selected tab stands out, and is associated to the content on the RHS.

So over all I would suggest,
1. Keep the number of tabs down, no more than 6
2. Only one row
3. Use images if you like
4. Use RHS tabs where you can – Sadly VBA’s tabs down the RHS are quite poor.

These are just my unqualified thoughts of course, you’re welcome to yours, and feel free to express them in the comments!

*1. I’m guessing, that is to say it’s my option that it’s easier, and 2; I’m postulating that the difficulty of finding the right tab follows something like Hick’s Law.

Time/Task recording systems

I was asked to report on the time I was spending on different tasks each day. As quick as a flash, I was reminded of Dick’s Excel tool for doing this.

DicksTool

Not too shabby for sure, but these days I’m not always in Excel, and although I could extend Dick’s code, it doesn’t do everything out of the box. So I took a look around, I didn’t look very far to be fair, but I quickly found Rachota. It’s a light weight Java app, its not too bad, but I found it slightly awkward to use. For example to change the tasks you have to jump around the dialog a bit. The dialogs a little ugly, and there’s not much in the way of reporting. Overall a step up from Dicks addin, but still not something I could use meaningfully.

Rachota

My next, and I’m happy to say final, stop was the mighty; Grindstone.  Firstly a bonus point for the name. It’s a .Net app (looking at the grid control 2+). Itlooks nice and is well layout -not perfect mind. I would have liked to see the bottom controls at the top or in the middle of the dialog – better still, if I could place them where I wanted, but no big deal.

Grindstone

At least you can work down the dialog logically. Reporting is available out of the app, it will fill out a grid control which, inexplicably, you can't copy out of, but you can export to XML. It also has the ability to calculate invoices, based on a rate table, but I have not used this myself.

Over all a lovely little application!