If you are a developer or responsible for building Excel-based addins and solutions you really owe it to yourself to come and meet the speakers, hear the latest thinking on Excel extensibility and discuss the impact to your addins and solutions of the changes that Microsoft is making to core Excel function (including any changes announced at Microsoft Ignite September 24-28).
This is a unique conference: never before have so many Excel world-class developers gathered together to share their knowledge.
For details of the sessions see here
Conference places are filling up fast: make sure you register soon!
It’s Evil Function election time: cast your votes so we can find out what the most evil Excel function is!
The Evil Function Survey
My vote has gone to INDIRECT (see my blog post here)
I will publish the results when we have got enough votes.
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!
For the first time ever some of the world’s leading authorities on Excel and spreadsheet models are coming together to share their knowledge.
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.
EarlyBird 20% discount available for registrations before December 31 2015.
Don’t miss out out on this unique Excel opportunity.
NETWORK 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
Excel User Voice
At the recent Excel Global Summit the Excel team were keen to explain how they have started using Excel User Voice to ask for and prioritise product improvement suggestions.
There are 2 important things to note about this:
- The Excel Dev Team actually read your suggestions on User Voice!
- Starting with Excel 2016 the development and ship cycle is much much faster than the traditional 3 years.
So it really is worthwhile making your suggestions to improve the product.
You get 10 votes on suggestions, and the suggestions are categorised by end-point and product area. So if you want to make a performance suggestion for Excel on Android phones you can focus down, see what other suggestions have been made in this area, and either cast a vote for an existing suggestion or make a new suggestion.
To try this out I made a suggestion (about calculation of course).
At the moment from the UI you can either calculate all open workbooks (F9 or Automatic) or a worksheet (Shift F9).
From VBA you can also calculate a range (Range.Calculate and Range.CalculateRowMajorOrder).
But you cannot calculate a single workbook.
This is really annoying when you have 2 versions of a large slow workbook open, or you have a small rapidly changing workbook open that links to a large mostly static workbook.
My suggestion is to add an additional calculation setting: Calculate Active Workbook Only and from VBA Workbook.Calculate.
So please vote for my suggestion if you think it’s a good idea!
It’s fun to argue about whether VLOOKUP or INDEX/MATCH is better, but to me that’s missing the point: they are both bad.
So I decided to design and build some better ones.
VLOOKUP INDEX/MATCH problems
Here are some of the more-frequently mentioned VLOOKUP INDEX/MATCH problems
- Slow exact match (linear search)
- Approximate sorted match is the wrong default 99.9% of the time and gives the wrong answer without warning
- Cannot do exact match on sorted data (well they can but only if they ignore sorted!)
- Numeric VLOOKUP answer column easy to break
- No built-in error handling for exact match
- VLOOKUP very inflexible
- INDEX/MATCH more flexible but still limited
MEMLOOKUP/MEMMATCH – easier and faster alternatives to VLOOKUP/MATCH
MEMLOOKUP ( Lookup_Value, Lookup_Array, Result_Col, Sort_Type, MemType_Name, Vertical_Horizontal )
The syntax is designed to make it easy to convert a VLOOKUP to MEMLOOKUP, but there are differences!
- Defaults to Exact Match on both unsorted and unsorted data
- Use either column labels or numbers
- Fast exact match on both unsorted and sorted data
- Automatic optimisation of multiple lookups within the same row
So you want more flexibility? Try the AVLOOKUP/AMATCH family of functions
It’s always tempting to cram in more function (scope creep is universal), but if the result is too many parameters then it’s a mistake. So instead there is a whole family of these lookup functions that build on the MEMLOOKUP/MEMMATCH technology to provide the ultimate in flexibility and power whilst remaining efficient.
- Lookup using any column
- Lookup using more than one column without slow concatenation
- Lookup the first, last, Nth or all results on both sorted and unsorted data
- Lookup both rows and columns (2-dimensional lookup is built-in)
- Built-in error handling for exact match
- Return multiple answer columns
- Case-sensitive lookup option
- Regex match option
Try them out for yourself
These functions are included in the 90 or so additional Excel functions built into FastExcel V3.
You can download the trial version from here.
Download FastExcel V3
If you like them then ask Microsoft to add them to the next version of Excel!
I would be delighted to tell the Excel team how I built these functions and the algorithms they use.
By the way they are written as C++ multi-threaded functions in an XLL addin for maximum performance.
As some of you may know I have been working on developing Version 3 of my FastExcel product for more years than I care to remember.
First there was the VB6 blind alley, then 64-bit and multi-threaded calculation and that ribbon thing, then I detoured into learning C++ and XLLs, then there was the inevitable scope creep, (and my consulting customers kept asking me to do things, and then there was the sailing) … you get the picture.
Anyway I finally pushed the release button on June 4 2014. Yippee!!!
The initial reaction has been great (no doubt helped by the introductory offer of 50% off).
So what is FastExcel V3?
There are 3 major products in the FastExcel V3 family, targeted at different types of use scenarios.
The Profiler gives you a comprehensive set of tools focussed on finding and prioritising calculation bottlenecks. If your spreadsheet takes more than a few seconds to calculate you need FastExcel profiler to find out and prioritize the reasons for the slow calculation
- Profiling Drill-Down Wizard
- Profile Workbook
- Profile Worksheet
- Profile Formulas
- Map Cross-References
FastExcel Manager contains tools to help you build, debug and maintain Excel workbooks.
- Name Manager Pro – an invaluable tool for managing Defined Names and Tables
- Formula Viewer/Editer – a better way of editing and debugging more complex formulas.
- Sheet Manager – Easily manage and manipulate worksheets.
- Workbook – Cleaner – Trim down any excess bloat in your workbooks
- Where-Used Maps – See where your Defined Names, Number Formats and Styles are being used
SpeedTools provides you with a state-of-the-art tool-kit to help you speed up your Excel Calculations
- Calculation timing tools for workbooks, worksheets, and ranges
- Additional calculations modes to enhance control of calculation so that you only calculate what needs to be calculated.
- 90 superfast multi-threaded functions
- Faster and more powerful Lookups and List comparisons
- Multi-condition filtering and Distinct formulas to eliminate many slow SUMPRODUCT and Array formulas
- Enhanced functions for Array handling, text, mathematics, sorting, information and logic
Some of you will know that I have been developing (for the past 2 or more years!) some fast multi-threaded Excel functions using the XLL C++ interface.
After climbing this rather steep learning curve I am now very close to the final release of these functions.
FastExcel SpeedTools is designed to be a state-of-the-art set of tools to help you speed up calculation of slow workbooks:
- New calculation methods and modes give you greater control of calculation.
- Superfast memory lookup and Compare Lists functions make handling large data volumes easier.
- You can eliminate many SUMPRODUCT and array formula bottlenecks with SpeedTools FILTER.IFS.
- Additional functions include Regular Expressions, Array Stacking, Array OR/AND, Text and Information functions
The final SpeedTools Beta test is now live and I need more Beta Testers and feedback:
So download SpeedTools Beta 3 and tell me what you think!
The best 20 Beta test feedback reports received by the end of March will get their own exclusive SpeedTools coffee mug, (and a free license of course!).
Excel 2003 users get a toolbar to choose functions and launch the Function Wizard, and Excel 2007 and later users get 2 additional groups on the Formulas tab.
You will find more information on my website and blog, including some performance comparisons with the built-in Excel functions.