Archive for the ‘User Conf’ Category

Excel User Conf London 7-8 Oct 09

September 29, 2009

The next UK Excel User Conference will take place next week at Microsoft London.

Its two days, Wednesday and Thursday.

Its a re-run of the April event for those that missed out that time

Fuller details including an agenda are here.

If you are going I look forward to seeing you there.



October 2009 Excel User Conference

August 9, 2009

We will be running the second 2009 Excel User conference, at Microsoft London again.

More details soon (like the date)



UK Excel User Conference 2009

December 6, 2008

London, April 1st and 2nd.

Nick Hodge has all the details here

1 or 2 days (up to you) of quality Excel content delivered by Experts, and all for FREE.

Microsoft have kindly offered to host the event at their London event venue.

See you there



Conference Feedback

December 16, 2007

Finally got all those feedback forms entered and analysed.

In most areas the results were either 4’s or 5’s (out of 5) so that is very encouraging. Basically the presenters and the content were very well received. As this is the core reason for attending its great that the attendees were so pleased.

The main areas for improvement were around pre-conference admin and information. Part of the issue here was the last minute nature of the event – we only had about 6 weeks from confirmed date/venue till event. As we are already planning for next year, this stuff should be much better organised.

Thanks a lot to all those who took the time to fill out the forms, they will help us make the event even better next time.

As usual leave any other feedback or suggestions for 2008 somewhere on this site and we will find them and consider them.

If you didn’t come this time, but are considering 2008, then you’ll be pleased to hear this years delegates rated the event so highly. We hope (and plan) to do even better next year so watch this space for dates and venues.



Conference Report Day 2

December 10, 2007

Friday: Conference – Advanced, Add-ons – Intermediate.
Again the feedback was very positive all round.
The first session was Andys session on Excel Charting.
Andy took us through a range of different ways to present information on charts. In each case explaining the logic behind it as well as the actual steps to create the chart. Although the demo was on 2003, he explained where Excel 2007 was different.
I learnt a load of stuff in Andys talk last year, and I learnt a load of new stuff this time around.
Next up was my Excel VBA design tips
This covered a range of areas from IDE setup to design, class, named ranges etc. I demoed some of the hard to maintain code I have worked with in the past. The session was a bit of a challenge as I had pretty much lost my voice, but I think I scraped through. (apologies for the squeaking!)
After dinner Charles went through UDF performance
Charles had an example of performance improvement step by step from someone he had helped from a newsgroup posting. Sadly I can’t remember the numbers, but it was big from 20 second to 0.04 or something. He also took us through some of the underlying issues like the VBA call overhead, and ways to minimise that. He had also created several new UDFs to demonstrate how a bit of domain knowledge can enable you to create functions that perform better than Excel native ones. He also discussed some of the issues in creating and deploying professional UDFs.
Last slot of the day was Nick on Data Interchange
Nick took us through some of the many ways of connecting Excel up to external data sources. One of the big features in Office 2007 is Sharepoint integration and Nick demoed some of the ways they use that at his firm. By choosing the right structure in Excel 2007 (which seemed to be tables, tables, tables – now what was the question?) exposing and synchronising data with external sources (eg sharepoint lists) is almost trivial.

The addon classes were Charles on Optimising Calculation speed and Bob on VBA Tips and Techniques.

The day finished with some general questions and answers, mainly about which pub to go to before our planned Chinese meal. (which was excellent btw)

One point worth mentioning was during a couple of sessions I spotted presenters and delegates huddled around laptops in the foyer. There aren’t many opportunities to get such one to one focussed advice from the top folks in the country, this is surely one of the hidden benefits of conferences such as this. We are wondering about formalising this somehow for the 2008 event – any suggestions as to how are welcome.

Conference Report Day 1

December 7, 2007

Thursday – Conference Intermediate, Add-ons – Advanced.

Overall the delegate feedback was very positive about the venue, the content, and presenters.

The first session was my Pivot table slot.

I went through from basic intro to pivots, through some of the features I have found useful on client assignments. And features I tend to avoid, and why, and what I do instead. That included a 10 minute intro to the most useful parts of Access. I did a very brief demo of using some simple macro recorder stuff to synchronise multiple pivot tables, and ran out of time before punishing everyone with some of my gruesome real world examples.

Next up was Patrick with auditing spreadsheets

He gave us some insight into the work of a spreadsheet auditor, in particular the things to look out for, or sniff out. Poor practice being likened to a bad smell. He gave a useful overview of some of the available tools, both free and those that cost. We had a good discussion about names and external links, and plenty of other ‘quality’ practices. Patrick was also generous enough to offer a copy of his excellent book Spreadsheet Check and Control as a draw prize. So thats one delegate going back to the office armed with the knowledge to improve theri corporate spreadsheet quality.

(fortunately Patrick had left to catch his flight before he saw my random name picker fail rather embarrassingly with a #REF! error – whoops)

We then went off for dinner/lunch, for some more spreadsheet chat, this time over a buffet. I defy anyone to suggest there wasn’t enough food!

Bob got the graveyard shift after the food to talk about Visual Impact.

Considering the tube map as an presentation example was inspired, especially when alternative inferior examples were compared. Bob had plenty of excellent advice for clarifying the message, I particularly liked the talk (and demos) of removing ‘ink’ from a diagram to leave only what is genuinely important. In another section Bob went through and highlighted some of the weak informational content in many of Excel’s inbuilt chart types.

Nick closed the formal session for the day with a look at what new in 2007.

There are some great new features in Excel 2007, and Nick did an excellent job of demoing a selection of the most useful. It was especially useful that Nick has moved his whole firm over to 2007 – he has the insight that only real world use can provide.

We then had a short informal questions from the floor session, and a prize draw for Patrick’s book and some wonderful wooden puzzles Keith had brought.

It was then I realised what it was about conferences that bothered me – the dead spot between the last session and food. I had sorted the food no bother but had nothing arranged for the hour or so beforehand. Obviously we were going to a pub, but which?

We found a pub (eventually), then had a great meal at Pizza Express and those that were up for it went off for more drinks. They suffered on Friday!

Although the social aspect is a hard sell to the budget holder, in many ways it is one of the most important activities. Many a useful snippet is picked up in the pub, and by getting to know people better you can be more at ease asking questions. Its often easier to approach someone in the pub with a question than infront of 20 other people who may be more keen to get off and get their dinner.

Conference resources

December 7, 2007

If you attended the 2007 UK Excel User Conference you should have received an email from me detailing where you can access the supporting slides, documents, workbooks and code for all the sessions we ran.

If you didn’t get the email give me a shout and I’ll send it on. You can also leave a comment here if that’s easier. Break up your email address though if you don’t want to get spammed to oblivion.

If you didn’t attend then you missed out, but don’t worry there will be another event in September 2008 where you can make amends. In the meantime if there is something you would really like included next time around leave a comment on the 2008 suggestions page.

Conference social

November 27, 2007

Here is the plan for the social:

• Wednesday 28th November – pre-conference

Fountain Inn – 7pm Local pub serving Belgian beer, traditional food and pizzas.  Free WiFi.
12 Regent Street

• Thursday 29th November

Pizza Express – 6pm Great pizzas and nice beers.
7a Jesus Lane

• Friday 30th November

Ugly Duckling – 6.30pm Excellent Chinese cuisine in Cambridge’s historic centre.
12 St Johns Street

• Saturday 1st December

Saffron Brassiere – 6.30pm Indian restaurant serving authentic dishes.
36-28 Hills Road

Here is a map (click to open in a new window to print/see properly):


And here is the Word doc we’ll be giving out on Thurs. excel-user-conferencesocial.doc

Any problems let me know



Returning a RANGE from a UDF

November 22, 2007

[From Charles: ]

I always wanted to write a UDF that returned a Range rather than the usual things like numbers. It sounded like a cool idea, and so when I was writing the FSUMIFS UDF for my talk at the forthcoming Cambridge UK Excel Users Conference I realised that this was my chance!

(FSUMIFS is a VBA UDF that handles multiple condition SUMs faster than SUMPRODUCT)

I could write a UDF that returned a range (well OK actually for error handling reasons it returns a variant containing a Range Object), and then the user could embed it in any other Excel function that handled a range (SUM, MEDIAN, INDEX, STDEV, MIN, MAX, SMALL …) without needing a special variation of FSUMIFS for each case.

I wrote the FSUMIFS version first, and then did the FRANGEIFS version.
Of course in a lot of cases the returned Range is a multi-area range created by a loop using UNION, which has some disadvantages:
– UNION is quite slow
– Some Excel functions like OFFSET do not work with multi-area ranges.

It all works well and the performance of the current beta version in almost every case so far except for small ranges is substantially (typically 5 to 30 times, up to 100 times in extreme cases) faster than the equivalent ubiquitous SUMPRODUCT formula.

BUT, having done all this work, I am now wondering why return a multi-area range rather when you could just return a (single-area) array (ARRAYIFS)?

ARRAYIFS would probably work just as well if not better (would avoid all those slow UNIONs and problems with function that don’t like multi-area ranges).
What do you think the pros and cons of returning Arrays rather than Ranges from UDFs are?

Charles Williams

Pivot data

November 19, 2007

I’m busily working on getting some moderately rich data for the Thursday pivot table session. I already have the data, its just a case of cleaning any potentially identifying info out of it.

Anyway I got an email from a fellow Eusprigger who had heard I was doing a pivot table slot and can he have my data, as his examples are ‘boring’. Well I’m not sure my data is that exciting but I do believe it has enough to demonstrate some of the exciting features of pivot tables.

I think its easier to see the value pivot tables can bring once you have seen them in action on your own data, or producing your own reports. Certainly when I have built them for clients, they seem to need little further persuasion of the value.

The biggest win is when you need multiple reports off the same basic data. A classic example would be reporting monthly, quarterly, and annually (calender year and/or financial year). With pivot tables reliable, robust reports take seconds including testing.

When quoting clients for work, if its going to be pivot table based I rarely budget more than a day or so for reporting. If its not pivots the estimate could be several weeks.

I’ll make the slides, workbooks, databases and code available after the session for anyone to download from the either here or the Codematic website, or both. If you want the chat that goes with, you’ll have to come along on Thursday.