Excel Junkies Unite for a Day of Tea, Coffee, and Some Exciting Pivot Tables
Mark you calendars for April - UK Excel User Group Meeting
Wednesday 1st April 2009 and Thursday 2nd April 2009
Microsoft London (Cardinal Place)
100 Victoria Street
London SW1E 5JL
Tel: 0870 60 10 100
An exciting opportunity to see presentations by some of the country’s most renowned experts on Microsoft Excel. (See bios). The topics covered will range from intermediate topics through to VBA and data interchange, with pre-submitted question and answer sessions on both days. The detailed agenda is listed below and most important the event is TOTALLY FREE!
NOTE: All sessions will likely have content using Excel 2007 but all speakers are fully conversant with versions prior to that and sessions will use examples in versions from Excel 97. Many subjects of course are fundamentally unchanged with version.
To book your place now simply email your name(s), address(es), contact telephone number(s) and email address(es), together with the day(s) you wish to attend to bookings@excelusergroup.org.
Don’t delay, we expect space to be very limited, so get your name down now!
Agenda Wednesday 1st April 2009
Time |
Event |
Speaker |
9:15am - 9:45am |
Registration & Coffee |
|
9:45am - 10:00am |
Welcome and Housekeeping |
|
10:00am - 10:45am |
||
10:45am - 11:45am |
||
11:45am - 12:15pm |
Coffee |
|
12:15pm - 1:15pm |
||
1:15pm - 2:00pm |
Lunch |
|
2:00pm - 2:45pm |
TBA |
Microsoft |
2:45pm - 3:30pm |
||
3:30pm - 4:00pm |
Tea & Coffee |
|
4:00pm - 5:00pm |
||
5:00pm - 5:30pm |
All |
|
5:30pm – Late |
Drinks, Dinner and Socialising |
All (Inc Delegates) |
Cont’d…
Agenda Thursday 2nd April 2009
Time |
Event |
Speaker |
8:30am - 9:00am |
Registration & Coffee |
|
9:00am - 9:15am |
Welcome and Housekeeping |
|
9:15am - 10:00am |
||
10:00am - 11:00am |
||
11:00am - 11:30am |
Coffee |
|
11:30am - 12:45pm |
Information - Visualising Data |
|
12:45pm - 1:30pm |
Lunch |
|
1:30pm - 2:15pm |
||
2:15pm - 3:15pm |
||
3:15pm - 3:45pm |
Tea & Coffee |
|
3:45pm - 4:30pm |
All |
|
4:30pm |
Close |
Session Descriptions
Questions and Answer Sessions
A brand new departure for our events which gives the opportunity for delegates to have ‘real world answers to real world questions’. We hope this will be as lively and informative as the formal sessions with a touch of, “I have that problem every day”, from more than just the questioning delegate. We are really excited about this section, as it is what most of the speakers do every day in the online communities and it gives them the opportunity to do it in person
Working smart with the Excel Grid
Move your data and calculations into, around and out of Excel faster and
easier by applying the following powerful, but simple techniques.
· Working with the 'special' groups of cells to save time and effort.
· Getting the most from Paste special to build complex models quickly and
easily.
· Moving and viewing data quickly using filters and advanced filters.
· Leveraging smart Edit replace to make large changes quickly.
Intermediate Functions
… going beyond simple IFs and SUMs
Lookup functions
· Straight lookups
· More complex lookups
· Dynamic formulae
· Dynamic functions
Text Functions
· Text scanning
· Text manipulation
· Matching an instance of a character
Conditional Functions
· Single criteria functions
· Multiple criteria functions
Database Functions
· Using database functions for conditional counting/summing
Analysis ToolPak ATP
· What are they?
· Pros & Cons
Using Functions
· Conditional Formatting
· Names
· Filtering
Building Formulae
· Combining functions
· Absolute/Relative
· Adding comments
· Error handling
· Debugging
What’s in a Name?
Discover the benefits, extend the power and learn the disadvantages of using Defined Names, one of Excel’s most powerful features.
I will use the free Name Manager add-in, developed by myself and Jan Karel Pieterse, to discuss many of the different flavours of Excel's defined names.
· Defining and using Names
· Global/Local/Universal names
· Hidden/Unhidden
· Relative/Fixed
· Cell/Range/Row/Column References
· Formulae/Constants/Arrays/XLM
· Dynamic/External/3-D/Multi-Area
· Implicit Referencing
· Names & Excel Calculation
· Benefits & Disadvantages of using Names
Charting tips and tricks
An introduction to charting with Excel.
· Data layout
· The chart wizard
· Building your own combination charts
· Axis tricks and tips
· Creating new chart styles
Pivot Tables
First there was Coal, then there was Electricity and then there was Sliced Bread. For the most exciting thing since then, come and learn about Pivot Tables.
This will be an introduction to the hidden gem in Excel which simplifies number crunching and data analysis. No formulae to type, no VBA code to write just a question of a few mouse clicks.
The session will be based on Excel 2003 and, time permitting, a brief look at the extra features brought in with Excel 2007 and will cover :-
· Normalising data
· Defining data ranges with
o Dynamic Named Ranges
o Lists and Tables
· Creating Pivot Tables
· Modifying Pivot Tables
· Grouping Data
· Formatting Pivot Tables
· Filtering
· Updating Pivot Tables
· Calculated Fields in Pivot Tables
· Pivot Charts
· Using External Data Sources
Data Exchange
Particularly with the new data features of excel 2007 and it’s many extra rows and columns. Excel will be used more and more as a database, (for good or bad).
This session will demonstrate how to move data, from virtually any source into Excel 2007. These include
· Text Files
· Access
· SQL Server
· Sharepoint
· Technologies including OLEDB and ODBC (including working with worksheet parameters)
Intro to VBA
Whatever your level of VBA this session will give you ideas on how to get going, or where to go next. The quick wins code will be freely available for all delegates to take away and use as they wish.
· Intro to the development environment Setting up the IDE for professional coding Understanding data types and how they relate to the grid
· The fundamental components of programming.
· Understanding and using events
· VBA quick wins - simple but useful snippets of VBA code, and useful developer tools.
Information – Visualising Data
Introduction
· Meet the presenters
- Presentation objectives
Visual Impact
- Importance of visualising data - Data is important, but so is the presentation of that data, and thought should be given as to how it is presented to maximise the visual impact of that data
- Effective charts - Charts are the most effective and immediate form of visualising data within Excel, and with some thought and effort, can be very effective
Dashboarding
- What is a dashboard? - Grouping different sets of information, using various presentation techniques, to provide a targeted information view
- Some charting techniques - Dashboards commonly use charts and charting techniques, some familiar, some not so
Do’s and Don’ts of Dashboards
- Good dashboards - To be effective, dashboards need to carefully designed, with thought given to what information is required, and how to present it
- Bad dashboards - Too many dashboards achieve style over substance, losing their impact by bad design
Conclusion
- Sum up - Emphasise the importance of planning, design and understanding to meet the objectives of using Visualisation to extract information from data
- Resources - Some details of books and websites for further reading
Arrays & Array Functions
How to use arrays and array functions to develop complex Excel worksheet formulae; tools to build, test and implement such formulae.
What are arrays?
· Range arrays
· Array constants
What are array functions?
· Excel’s built-in array functions
Array formulae
· Excel array functions
· Using arrays within non-array formulae
· Variable
· Extending the boundaries
Pros & Cons
· Flexibility
· Maintainability
· Performance implications
Arrays & UDFs
· Passing arrays
· Returning arrays
Speeding up Excel.
Slow calculating workbooks? Difficulty controlling Excel calculation? Excel won’t calculate?
You can speed up most slow-calculating workbooks by a factor of 10.
Don't miss this unique chance to attend the master-class on Excel Calculation speed!
· Understanding Recalculation and Full Calculation, dependencies, volatile functions, “Calculate” in status bar.
- The different Excel calculation methods (Automatic, Manual, Worksheet, Range) and how to control them.
- Saving and Restoring Calculation properties, what happens when you open a workbook.
- Calculation differences in Excel versions 2000-2007
- Finding, measuring and prioritising Calculation bottlenecks
- Four golden rules for speeding up calculations, with examples
- Tips and examples for optimising Calculation bottlenecks
- Why array formulae can be slow & how to speed them up
- Why VBA UDFs can be slow and how to speed them up
- How many formulae is too many?
Speakers Bios
Andy Pope
Andy works in London for a data processing bureau as the Programming Manager. He spend his days building automated reporting systems using VBA in Excel and PowerPoint.
He spends his spare time online answering Excel questions in newsgroups and forums, also maintaining his own website at www.andypope.info which provides information on all things about Excel.
Since 2004 he has be recognised by Microsoft as an Excel MVP for his contributions to the Excel community.
Charles Williams
https://www.DecisionModels.com
Charles founded Decision Models as a full-time Excel/VBA/Access consultancy in 1996 after a wide-ranging career with IBM.
He regularly contributes to the Excel Newsgroups in his special areas of Excel expertise:
- Improving Excel Calculation Performance
- VBA User Defined Functions
- Defined Names
Charles is the author of FastExcel, the widely used Excel performance toolset and profiler, and co-author of Name Manager, one of the most powerful and widely used free Excel Addins. He is also the author of the Microsoft white paper “Improving Performance in Excel 2007”.
Simon Murphy
An experienced Excel/VBA/.net developer with an MSc in Software Development and a strong financial reporting/analysis background.
MCSD in VB, MCSD.net in C#.
Equally comfortable integrating Office with other technologies such as SQL Server and web and intranet services.
Member of the Microsoft Office Developer Advisory Council steering the development of Microsoft Office 14 developer features.
Active contributor to Excel-L developers list and Microsoft.Public.Excel newsgroups.
International presenter on spreadsheet design, development and quality.
Bob Phillips
Bob has been in IT for over 30 years now, starting as an Assembler programmer, progressing through all of the development roles, programmer, analyst, designer, and project management.
Most of his career has been in large enterprise credit risk systems, building systems to provide a degree of control over the activities of the deal makers, managing risk in the hundreds of billions GBP.
In 2005, after too many years of playing the corporate political game, Bob decided to branch out, becoming a freelance consultant. At first, most of his work was in the credit risk system area, but gradually moved to taking on more Microsoft Office applications; to the point where the MS work is now the majority of his commissions.
Bob is also a Microsoft Excel MVP; in his 4th year of this award. He doesn’t have a particular speciality when it comes to Excel, other than to argue strongly the case for designing an application, whether that be for a full-blown VBA application, or a small spreadsheet.
Bob was a technical reviewer for Ty Anderson’s Apress publication, ‘Office 2003 Programming Real World Applications’, and as well as being an inveterate responder in many online forums, Bob claims some notoriety for one particular web page, an analysis of the SUMPRODUCT function.
Roger Govier
After 20 years as Commercial Director of a small software company, Roger set up his own IT Consultancy business, Technology 4 U in 1998.
Most of his work in recent times has centred upon solutions using Excel and VBA, and he is a regular contributor to the Microsoft Excel newsgroups. Roger was awarded MVP status in July 2007, and re-awarded in July 2008.
He was the Technical Reviewer for 2 books on Pivot Tables, written by Debra Dalgleish
“Beginning Pivot Tables in Excel 2007”
“Excel 2007 Pivot Tables Recipes – A problem Solution Approach”
Nick Hodge
Nick makes his living outside the computer industry as managing Director of Northern Tool + Equipment (UK) Ltd.
He has been annually awarded MVP status by Microsoft since 1999 and continues to contribute to the Excel community through the Microsoft Public Newsgroups and via his sites at www.excelusergroup.org and www.nickhodge.co.uk
He has recently been technical editor of a new book on customising the new ribbon in Excel 2007 written by several other MVPs.