Excel VBA Masterclass Ljubljana

Great news for anyone who would like to combine visiting the beautiful city of Ljubljana and learn about Excel VBA coding techniques!

On Novermber 5th, 2018 I’ll be teaching the Excel VBA Masterclass in Ljubljana.

In short I’ll be addressing:

  • Classes in VBA
    • Classes and Objects
    • Instantiating a class
    • Methods, properties, Events
    • Event classes
    • Instantiating an object from a class
    • Classes of classes
  • Userforms
    • Design
    • Events, methods, properties
    • Making a userform sizable
    • Splitter bars
    • Using in an Object oriented way
  • Efficient coding in VBA (2 sessions, one theoretic one and one hands-on lab)
    • Measuring performance
    • Break the rules
    • Fast VBA algorithms
    • Micro-optimization
    • Efficiently exchanging information with Excel

There are still some seats available, so hurry up and register soon!

Jan Karel Pieterse

jkp-ads.com

PS: There are two other very interesting masterclasses on that same day, so if you are not into VBA (I can’t imagine you aren’t) there are alternatives!

The Amsterdam Excel Summit 2017

Hi Excel lovers,

Wanted to draw your attention to our fourth

Amsterdam Excel Summit

April 18 and 19, 2017

We’ve worked hard to get an exciting line-up of speakers presenting on a wide variety of Excel subjects during our two-day conference.

So if you’re an Excel power-user, this is one of those one-of-a-kind conferences you cannot afford to miss!

Registration is now open

Hope to see you in Amsterdam on April 18 and 19 2017!

Jan Karel Pieterse, Tony de Jonker

topexcelclass.com

The Amsterdam Excel Summit 2016 open for registration

SpandoekLicht

Hi Everyone!

We’ve opened registration for our third annual

Amsterdam Excel Summit.

May 26, 2016

Join us in  Amsterdam to learn how to Excel from our Experts (all MVPs):
Jon Peltier, Bob Umlas,  Roger Govier, Henk Vlootman, Oz du Soleil, Tony de Jonker,  Jan Karel Pieterse.

Tentative program

Attend this comprehensive training event and you will:

  • Improve your Power Query skills
  • Learn how to Customize the ribbon for your workbooks and add-ins.
  • Get insight how to Build Excel models based on ranges and positions.
  • Understand how to create involved Array Formulas
  • Get advice on Best practices in Power pivot.
  • See how to use VBA to customize charts
  • Receive Tips & Tricks, documentation and lots of valuable files

The Excel Charting And Dashboard Masterclass

May 27th 2016

The Amsterdam Excel summit also features a post-conference training. Attend this one-day masterclass and:

Excel MVP and charting Guru Jon Peltier teaches you how to visualize your numerical information in the most effective way.
Excel MVP and financial expert at najlepsze strony bukmacherskie Tony de Jonker and communication &  visualization expert David Hoppe unveil the secrets of creating powerful and flexible dashboards.

So head over to our website to register or to signup to our mailing list so we can keep you posted!

Regards,

Jan Karel Pieterse

topexcelcass.com

jkp-ads.com

Announcing Excel Summit South: Auckland Sydney Melbourne in March 2016

We_Header

It has taken me 2 years to put this series of Excel conferences in Australia and New Zealand together.

Now please help me spread the word!

 

 

MVP_horizontal_Smallpwclogo

MS

Dev-Team

 

 

For the first time ever some of the world’s leading authorities on Excel and spreadsheet models are coming together to share their knowledge.

 

Speakers

 

 

 

If you use, rely on, tell stories with, worry about, or operate in the advanced areas of Excel, then there’s a track designed just for you.

EXTEND YOUR SKILLS

This is a unique opportunity to:

  • Learn from six of the world’s leading Excel MVP’s as they discuss the Excel topics most useful to you.
  • Hear industry leading speakers from around the world give you the latest views on Financial Modelling best practices, standards and spreadsheet risk.
  • Shape the future of Excel: Interact with members of the Microsoft Excel Dev Team as you explore with them the future of Excel.
  • Choose the sessions that best suit your needs from 23 masterclass sessions over two days of twin tracks for modellers and analysts.

INTERACT WITH THE EXPERTS AND MEMBERS OF THE EXCEL DEV TEAM

Use your opportunities, including two Panel discussions and Q&A sessions, throughout the two days to ask questions and discuss with the Excel MVPs and the industry experts.
The Excel Dev Team members will use this opportunity to learn from their customers, understand how you use Excel and get feedback on your Excel experience.

EXCEL SUMMIT SOUTH PLACES ARE LIMITED: REGISTER NOW

EarlyBird 20% discount available for registrations before December 31 2015.
Don’t miss out out on this unique Excel opportunity.

ModeloffSmallNETWORK AND ENJOY AT THE MODELOFF EVENING MEETUP EVENT

The ModelOff Meetup event  delivers plenty of opportunities to mingle, learn from your peers, talk to the speakers and have fun.

PROUDLY SUPPORTED BY

Sponsor_Logos

The Amsterdam Excel Summit Last-minute discount

Hi everyone,

Our event is coming real soon now and we’re very much looking forward to it. We have outstanding speakers and excellent content, so everything is lined up to make this a superb Excel event.

To entice the undecisive Excel lovers to make up their minds and subscribe after all, we decided to make it even more attractive to attend.

As of March 27st, 2015 we offer a € 200 last-minute discount per attendee for both days and € 100 for one day!

Register now at http://topexcelclass.com/index.php/amsterdam-excel-summit/registration/ and meet us on April 13th and 14th in Amsterdam.

Regards,

Jan Karel Pieterse

www.jkp-ads.com

The case for corporate Excel training investment

Howdy folks. Jeff here again, with my musings on the kinds of things you might put in a business case for a corporate Excel training program.

I think corporate-wide Excel training is too valuable to leave to a Learning and Development team and/or to chance (assuming an organization is large enough to have an L&D function in the first place).

  • L&D likely don’t know that much about how business users are misusing Excel, so how can they offer generic training at arms-length to remedy said misuse? At the same time, they must support numerous departments, with training being just one aspect of what they do (the other is meetings. Very important meetings, I’m told) and with Excel being just one of many programs that users need training in. So L&D simply can’t specialize in Excel training to a level that’s really going to make a difference at the coal face.
  • The efficiency dividend from training accrues to the units of the people being trained. So units themselves are directly incentivized to invest if it will get them a more optimal outcome…regardless of whether fostering increased human capital falls within that unit’s core job/mandate or not.

So instead of business units saying “It’s not our job to train…it’s theirs” I think they need to be thinking “We’d rather someone else do this, but we’re prepared to fill in the gaps ourselves if it helps our people to solve their business problems in ways that increase quality, improve productivity, and provide higher returns on investment.”

But what would a corporate Excel initiative look like? And how would you sell it to an organization that isn’t aware of the problems with the status quo?

I thought I’d have a crack at putting together a generic business case that addresses these questions. Not put together with any specific organization in mind…rather, this is something gleaned from practically every organization I’ve ever worked with/for, as well as from my time spend moderating a few Excel help groups and such forth.

Love to hear about suggested rewrites, omissions, etc in the comments.
 

Executive Summary

  • We believe that additional investment in Microsoft Excel-based training will help users solve their business problems in ways that increase quality, improve productivity, and provide a good return on investment, while decreasing some key data-related business risks.
  • Consequently we propose to instigate a training program above and beyond that currently offered by Learning and Development that is primarily focused on educating users on how Excel’s deep feature set can be better utilized to solve common business issues while increasing data integrity, utilizing approaches that users would otherwise be unlikely to be exposed to; and highlighting routine practices that currently get in the way of increased efficiency.
  • Given the large number of users involved, one-on-one diagnosis and training covering all users is simply not possible. Consequently this initiative primarily revolves around increasing the ability of users to self-diagnose issues and inefficiencies with their existing spreadsheet applications and practices, and to educate them on how to access resources and knowledge that will greatly assist them to self-remedy issues wherever possible. Given the resources available for this initiative, raising awareness of current bad practices and alternatives is possibly the biggest efficiency gain and risk-reducing opportunity that we can offer, at the least cost.
  • Given Excel is the tool by which many people realize/express their commercial analysis, data visualization, business processes, and logic, we believe that in teaching people to use the tool itself better we can also foster increased competence in these fundamental skills/attributes.

Problem Statement

Currently our Excel user base largely consists of users largely at the basic level, with a much smaller contingent of intermediate users. Almost all uses are largely self-taught, often with little exposure to the vast wealth of constantly evolving best-practice resources that are available both offline and online. Consequently, despite being motivated to use Excel as efficiently as they can, even intermediate users barely scratch the surface of Excel’s productivity tools. At the same time, because some of Excel’s features are so very easy to use, those same features are also easily overused to the point of misuse. Consequently:

  • The majority of users spend much more time manually formatting and manipulating data than they need to, often with little or no backwards auditability.
  • Sometimes our approaches to analytical problems involve much more complexity than a more suitable approach, with little or no additional benefit or confidence eventuating as a result.
  • Many of our business processes hosted within Excel spreadsheets are convoluted and unclear. ‘Mission-critical’ spreadsheets are often bloated, difficult to audit, and subject to catastrophic spreadsheet failure – possibly without users realizing.
  • Modelling practices and spreadsheet practices across the organisation are inconsistent, and there is little or no peer review or development in place to ensure people use the Excel tool smartly, wisely, and efficiently.
  • Best practices and learning are not perpetuated in a formalized way throughout the organization. The emphasis of our expert users currently remains fixing things that are broken, rather than using education to avoid bad practices in the first place.

While our Learning and Development (L&D) unit offer a number of appropriately pitched courses in place focusing on new/basic users, these are functionality-centric, rather than being business-specific. Consequently, such courses often don’t expose users to alternative practices. At the same time, L&D staff are unlikely to be fully cognizant of the vast amount of quality free resources available online, as well as some paid offerings that may prove more cost effective than the traditional course vendors that we have previously used.

As a result –

  • Most people keep using the very small subset of Excel’s functionality that they know about as a hammer on problems that aren’t nails but screws
  • We don’t insist on nor foster nor seek to measure incremental improvement of our analyst’s skill-set.
  • We allow users to build mission-critical applications every day in Excel with little or no discipline .

The status quo incurs a very real opportunity cost to the organization, given that –

  • Advanced users can often do something in a fraction of the time that it takes an intermediate user;
  • The automation capability of Excel is staggering;
  • It’s not actually that hard to turn basic users into intermediate ones, and intermediate users into advanced ones, so that they can do things in a fraction of the time they currently do, if not automate it completely.

Desired state

We propose to train our analysts to become not just better Excel users, but also more effective analysts. Note that this initiative isn’t just about the use of Excel itself, but related disciplines such as effective data visualization, business process design, and logic. Given Excel is the tool by which people express their analysis, data visualization, and logic, then if we teach people to use the tool better, in doing so we will also give them some pointers in thinking harder about what they are using the tool to achieve.

The desired state that we seek to arrive at would be demonstrated by the following outcomes:

  • Our people would leverage more fully off Excel’s rich feature-set in order to achieve better analytical outcomes with much less effort. At the same time, they should be more fully cognizant of common and potentially avoidable spreadsheet design flaws and pitfalls.
  • Our people would better judge the appropriate amount of time and precision required for any given analytical task. They will be less likely to over-analyse/over-build, and more cognizant of the diminishing returns that often accompany increased complexity of approach.
  • Mission-critical spreadsheets/templates would be more robust, simpler to maintain, and user-friendly, as well as easier for successors to understand and maintain. This should result in lessened business risk, via decreased risk of incorrect models and catastrophic spreadsheet failure; Increased model auditability; and a more consistent approach to spreadsheet development and use across teams.
  • Once our people better realize the capability of the Excel application, they begin to actively look for other efficiency opportunities where they can leverage their new Excel skills, and share their approaches.

Approach

This initiative is largely focused on increasing human capital – which historically has been more the domain of the organization’s Learning and Development team rather than our own unit. However, we propose entering into this space due to the following factors –

  • Reduction of L&D’s technical training capacity;
  • The opportunity cost of not realizing some fairly low-hanging efficiency dividends;
  • The cost of procuring external training, and the risk that such training would not have the long-term positive impact on current practices that we seek;
  • The increasing time pressures and demands on staff – meaning increased barriers to utilizing external trainers from both a financial and a time perspective; and
  • The fact that we already have a strong interest in developing training materials that encompasses Excel, Modelling, VBA, and Business Process Improvement.

The primary outcomes this initiative will deliver are –

  1. Increased efficient/effective use of one of our main business productivity tools – Microsoft Excel – and greater awareness of current sub-optimal processes and approaches.
  2. Education of users to self-identify efficiency/risk issues with existing spreadsheet-based processes/approaches and give them avenues and resources to address these issues.
  3. Facilitation of increased peer-to-peer learning and peer-review opportunities between Excel users throughout the wider organization.

In doing this, the initiative will take a multi-pronged approach:

  1. Remedial mitigation of mission-critical spreadsheets/processes
  2. ‘Best Practice’ efficiency/effectiveness education
  3. Peer-to-peer user group
  4. Identification, creation, and dissemination of useful resources/templates
  5. Evaluation of additional tools/external training opportunities

These are covered in more detail below.

1. Remedial mitigation of mission-critical spreadsheets/processes.

We will work directly on selected mission-critical spreadsheets and spreadsheet-bases business processes in conjunction with business owners to identify design issues and prioritise remedial action.

2. ‘Best Practice’ efficiency/effectiveness education

We will deliver multiple training sessions/workshops on best-practices covering modelling, spreadsheet risk mitigation, and using the Excel application more effectively and efficiently. This will also draw on ‘lessons learned’ from the above step. This is critical given that several of the spreadsheets prioritized for investigation in the preceding step were constructed within the last 6 months. This highlighting that we have an on-going issue, and not just a problem with legacy spreadsheets.

Sessions will cover –

  • How recent versions of Excel have extended its capability from mere spread-sheeting into business intelligence.
  • Data organisation: Many things in Excel go from challenging to easy simply by changing how source data is organized.
  • Understanding how Excel calculates, and avoiding calculation bottlenecks and overhead by wisely choosing/using formulas for efficiency.
  • Leveraging pivot tables for aggregation and reporting; and utilising Excel’s dynamic table functionality to cut down on complexity.
  • Using advanced functionality to augment user interfaces by incorporating slicers, advanced filtering, VBA (aka macros), and dynamic SQL queries (for simplified data retrieval and processing) into spreadsheets/models.
  • Conditional formatting and data validation techniques that can help to better validate user input.
  • Tools (both free and commercial) that can help users to work more efficiently in the Excel environment.
  • Troubleshooting and streamlining existing models that users have inherited.
  • How users can get free help, advice and inputs from online resources and forums.
  • Modelling/analysis best practices.
  • Data visualization best practices.
  • Spreadsheet development best practices, including case studies covering lessons learned from recent work.

Session attendees will also have access to associated hand-outs/workbooks with supporting content and links to further in-depth resources. Each session will be repeated multiple times on a rolling basis to facilitate/encourage maximum patronage. Managers will be encouraged to actively promote attendance at these sessions and potentially include them in staff development plans if appropriate.

3. Peer-to-peer user group/help forum.

We will set up and facilitate an internal Excel User Group – potentially with a supporting software forum/message board. This will be used to –

  • Provide peer-to-peer learning and support opportunities. A wider peer-to-peer support network that extends beyond just our unit alone will provide much wider coverage than me alone can offer.
  • Identify/evaluate mission-critical spreadsheets across our unit that potentially impose significant business risk and/or administrative burden, and provide owners with some options to restructure/re-engineer them accordingly.
  • Provide attendees with more hands-on exposure to tools/tricks that they can use to re-engineer their own spreadsheets in need. Encouraging all users to utilize the skills of identified expert users on a consultancy basis for advice and peer review as needed – perhaps via the peer-to-peer-based user group outlined above.

4. Identification, creation, and dissemination of useful resources/templates

We will identify/create a useful repository of quality free training resources (including construction of additional in-house resources and ready-to-use macros/templates where warranted) – that supports further in-depth professional development and the same time reduces our dependency on paid external courses. This will draw heavily on the large amount of free resources and training materials available online that equal and in many cases surpass paid external training content in terms of providing learning outcomes.

We will publish a weekly ‘Productivity Hack’ article on the organizational intranet home page suitable for users of all levels. These articles may also reference a number of the outstanding productivity resources published each week on the Internet by the worldwide Excel Development/Training community (including blog posts, technical articles, training videos et cetera).

5. Evaluation of additional tools/external training opportunities

We will work with IT to evaluate new additions to Excel such as PowerPivot – a free extension to Excel that allows users can crunch, filter, and sort millions of records with very little overhead, as well as incorporate multiple data sources easily into PivotTable-based analysis, using an interface they are already familiar with. PowerPivot extends the capability of Excel to the point that it might reduce the need for some of the other apps we currently use to amalgamate discrete data sources, such as SAS.

We will also offer our assistance to Learning and Development to help them identify/rate any external training providers they will use going forwards.