Mark you calendars for April - UK Excel User Group Meeting
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!
Time
Event
Speaker
9:15am - 9:45am
Registration & Coffee
9:45am - 10:00am
Welcome and Housekeeping
Nick Hodge
10:00am - 10:45am
Working smart with the Excel Grid
Simon Murphy
10:45am - 11:45am
Intermediate Functions
Bob Phillips
11:45am - 12:15pm
Coffee
12:15pm - 1:15pm
What's in a name?
Charles Williams
1:15pm - 2:00pm
Lunch
2:00pm - 2:45pm
TBA
Microsoft
2:45pm - 3:30pm
Tips and tricks in charting
Andy Pope
3:30pm - 4:00pm
Tea & Coffee
4:00pm - 5:00pm
Pivot Tables
Roger Govier
5:00pm - 5:30pm
Pre- submitted Q&A
All
5:30pm – Late
Drinks, Dinner and Socialising
All (Inc Delegates)
Cont’d…
8:30am - 9:00am
9:00am - 9:15am
9:15am - 10:00am
Data exchange
10:00am - 11:00am
VBA - It's in everyone's reach
11:00am - 11:30am
11:30am - 12:45pm
Information - Visualising Data
Andy Pope & Bob Phillips
12:45pm - 1:30pm
1:30pm - 2:15pm
Arrays and Array Functions
2:15pm - 3:15pm
Speeding up Excel
3:15pm - 3:45pm
3:45pm - 4:30pm
Pre-submitted Q&A
4:30pm
Close
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
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.
… going beyond simple IFs and SUMs
· 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
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
An introduction to charting with Excel.
· Data layout
· The chart wizard
· Building your own combination charts
· Axis tricks and tips
· Creating new chart styles
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
· Updating Pivot Tables
· Calculated Fields in Pivot Tables
· Pivot Charts
· Using External Data Sources
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)
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.
Introduction
· Meet the presenters
Visual Impact
Dashboarding
Do’s and Don’ts of Dashboards
Conclusion
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
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.
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.
http://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”.
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.
http://www.xldynamic.com
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.
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 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.