VT's Blog

The Blog of Viral Tarpara A Smart 27 year old's thoughts on Windows, Cloud Computing, Collaboration, and Productivity

Excel Junkies Unite for a Day of Tea, Coffee, and Some Exciting Pivot Tables

Excel Junkies Unite for a Day of Tea, Coffee, and Some Exciting Pivot Tables

  • Comments 1
  • Likes

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

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…

Agenda Thursday 2nd April 2009

Time

Event

Speaker

8:30am - 9:00am

Registration & Coffee

 

9:00am - 9:15am

Welcome and Housekeeping

Nick Hodge

9:15am - 10:00am

Data exchange

Nick Hodge

10:00am - 11:00am

VBA - It's in everyone's reach

Simon Murphy

11:00am - 11:30am

Coffee

 

11:30am - 12:45pm

Information - Visualising Data

Andy Pope & Bob Phillips

12:45pm - 1:30pm

Lunch

 

1:30pm - 2:15pm

Arrays and Array Functions

Bob Phillips

2:15pm - 3:15pm

Speeding up Excel

Charles Williams

3:15pm - 3:45pm

Tea & Coffee

 

3:45pm - 4:30pm

Pre-submitted Q&A

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

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”.

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

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.

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.

Comments
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment