Excel MVP Charles "Fast" Williams just released V3 of his Fast Excel add-in. I had the opportunity to see Charles demo this product recently in Amsterdam and it blew me away. This review shows some of my favorite features of the product.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1892.
Review of FastExcel V3 (Utility from Charles Williams).
Well, I feel bad that I'm just catching up.
Earlier this spring I had a chance to go to Amsterdam to attend two events.
And one of the speakers at both of those events is Charles Williams.
Now, if you watch Mike Girvin’s videos, Mike refers to him as Charles “Fast” Williams.
Charles is an Excel MVP and Charles is good in making very slow worksheets faster.
And I learned this time, if you want to add some extra credibility, Charles was on the team that built the IBM PC.
In fact, some of his code is still in the PC that you're looking at right now.
All right now, in the past I recommended Charles, because he offered a service that… He would take your spreadsheets and make them faster.
I used this for one of my clients and this was a spreadsheet that a hundred and fifty sales reps were using, and there were, you know, a hundred copies of every spreadsheet and Charles, for a very low price, made that spreadsheet faster.
And the client was thrilled.
Fans of the podcast remember episode 1790: were about my Twitter war and Charles played a huge role in solving that problem.
But in Amsterdam, I had the chance to watch Charles demonstrate his product called FastExcel.
Now this is version 3, thousands of people are using FastExcel and I was mildly aware of it before, but I never realized just how powerful this is.
First, it's a tool that lets you speed up your spreadsheets.
And Charles will teach you, through the Help, how to look for things that are slow and when you find something that's potentially a bottleneck, you can use these tools such as Calc Range.
So this tested one version of a formula and it took 10.7 seconds.
Now, here's another version of that formula and will calculate this range.
I'll get the hat, all right, so already from 10 seconds down to 0.354.
And there's other tools here, that help you profile the workbook, the worksheet.
And the profile will help you spot the areas of the spreadsheet that are slowing things down.
But there's more: you can use the View/Edit Formulas to see your formula, automatically indent them, you can choose how to indent, is it after each separator, after each new function, after each open and closing parenthesis.
And you can see for each item, as we go through the various references here, what those values are.
This is like Evaluate Formula on steroids.
There's a Name Manager, that shows you all the names in the spreadsheet and you can even see the result of evaluating that name.
The Name Manager is amazing, look at all the different ways that you can filter the names, whether they're global or local, hidden, only the ones that have errors, only the ones that are multi-area and just a massive amount of tools.
The Spreadsheet Cleaner will look for ways to get rid of the unused areas of your spreadsheet and so much more.
Now, some of my favorite features here are, separate from FastExcel, it's called SpeedTools.
You can get SpeedTools if you buy the whole bundle.
Now, this gives you a whole bunch of new functions.
For example, under Lookups we now have advanced lookups that will let us return the first item in the list, as normal, the last item in the list or the second, or third, or fourth item in the list.
Case-sensitive lookups, memory lookups.
If you've heard of regular expressions, these are super powerful ways to find patterns of data, so maybe two digits and then three characters, and two more digits.
You can use regular expression lookups, just a great set of lookup functions.
So the Lookup is one add-on that you can buy.
Under Filter, oh my gosh, those amazing array formulas that Mike Girvin does to pull a list of distinct values.
We now have functions that will return an array of distinct values, or even just count the distinct values.
So that's another add-on.
And then all of these other groups over here, called Extras.
I mean, just simple little things, like being able to reverse an array or create a vector.
See, if you need to create the numbers 1 to 17, you can do the vector.
So let me show you a few of these.
We'll just come up with a quick series of numbers here.
I put in number 1, hold down the Ctrl key while I drag and I get 1 to 16.
If I need to reverse that… so that's under Arrays and I'm going to do the chosen function as an array formula… REVERSE.ARRAY and it takes right in, it shows us help for every single item, so here's the input area and do I want to reverse the rows?
Yes (True), reverse the columns?
Well, there's only one column, so it doesn't matter in this case.
Click OK and it reverses the number.
And of course it's a live thing, so if we change one of these numbers, the resulting array will change.
How about concatenating a range?
Usually CONCAT: you have to enter each item separated by commas, but with CONCAT.RANGE we can say we want to concatenate all of these values with a Divider of comma-space(“, “), the NumberFormat: see, right now is putting a whole bunch of number formats, we can just say it's “0” number format, if it's blank and we want to put something else in.
Do we want to start with some other text, now I'll just go like this.
And we have a concatenation of that range.
The trick of creating… using row to create a vector of numbers.
I'm going to say =VECTOR, I want to start at 1, end at 99, step of 2 and we want to generate a column of numbers, so False here, Ctrl+Shift+Enter and it generates that vector as a single array formula.
Here's examples of the COUNTDISTINCT.
So here we have: from country (FromCtry), to country (ToCtry), so there's a lot of different city pairs, right.
So counting the number of distinct combinations of those, count distinct rows, there's 420 different pairs.
But this formula: that ignores the rows, is just looking for unique value.
There's 21 cities, so 21 cities, but 420 combinations, where they appear.
Or here, check this out: that LISTDISTINCTS.COUNT will give us the list of those items.
Truly an amazing set of formulas.
Now, at the end of every one of Charles’ seminars, I'm like: I've got to get these tools, they're absolutely amazing.
While the pricing options seem confusing.
Let me try and break it down for you.
We have these different options and I can show this graphically.
So the basic, being able to time range, calculate range, calculate sheet, is $19.
To do the Profiler, where you look at your workbook and find the bottlenecks, is $89.
Those cool new Functions and the Speed Tools: they're breaking… they’re broken down to $59 for the Lookups, $59 for the Filter and $29 for all of these other minor ones, or you can buy this entire bundle for $139.
The FastExcel Manager: we can change, View/Edit Formulas, the Sheet Manager, Name Mgr Pro – that's $29.
But I think the better way to go, is to buy the entire package.
All of these items, the whole toolbar for $189 and what a deal, if you're seeing this before July 31st 2014, use this coupon code, the introductory offer, it's only $94.50 for the whole thing.
And hey, if you're worried that you start to use one of those great new functions in the Filters group or the Lookups group, Charles offers volume licensing, so depending on the size of your company, the price for that little piece of the add-in is going to fall dramatically.
I've seen a lot of add-ins for Excel, I've seen add-ins written by really smart people and they do lots of cool things, the FastExcel does things, that I've never seen done anywhere else.
Trust me, I'll be coming back to FastExcel, I can't wait for the next Dueling Excel, where we have Mike's solution and my solution, and I get to time them, to see whose is faster.
This is a product you can't live without, check it out.
Well, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1892.
Review of FastExcel V3 (Utility from Charles Williams).
Well, I feel bad that I'm just catching up.
Earlier this spring I had a chance to go to Amsterdam to attend two events.
And one of the speakers at both of those events is Charles Williams.
Now, if you watch Mike Girvin’s videos, Mike refers to him as Charles “Fast” Williams.
Charles is an Excel MVP and Charles is good in making very slow worksheets faster.
And I learned this time, if you want to add some extra credibility, Charles was on the team that built the IBM PC.
In fact, some of his code is still in the PC that you're looking at right now.
All right now, in the past I recommended Charles, because he offered a service that… He would take your spreadsheets and make them faster.
I used this for one of my clients and this was a spreadsheet that a hundred and fifty sales reps were using, and there were, you know, a hundred copies of every spreadsheet and Charles, for a very low price, made that spreadsheet faster.
And the client was thrilled.
Fans of the podcast remember episode 1790: were about my Twitter war and Charles played a huge role in solving that problem.
But in Amsterdam, I had the chance to watch Charles demonstrate his product called FastExcel.
Now this is version 3, thousands of people are using FastExcel and I was mildly aware of it before, but I never realized just how powerful this is.
First, it's a tool that lets you speed up your spreadsheets.
And Charles will teach you, through the Help, how to look for things that are slow and when you find something that's potentially a bottleneck, you can use these tools such as Calc Range.
So this tested one version of a formula and it took 10.7 seconds.
Now, here's another version of that formula and will calculate this range.
I'll get the hat, all right, so already from 10 seconds down to 0.354.
And there's other tools here, that help you profile the workbook, the worksheet.
And the profile will help you spot the areas of the spreadsheet that are slowing things down.
But there's more: you can use the View/Edit Formulas to see your formula, automatically indent them, you can choose how to indent, is it after each separator, after each new function, after each open and closing parenthesis.
And you can see for each item, as we go through the various references here, what those values are.
This is like Evaluate Formula on steroids.
There's a Name Manager, that shows you all the names in the spreadsheet and you can even see the result of evaluating that name.
The Name Manager is amazing, look at all the different ways that you can filter the names, whether they're global or local, hidden, only the ones that have errors, only the ones that are multi-area and just a massive amount of tools.
The Spreadsheet Cleaner will look for ways to get rid of the unused areas of your spreadsheet and so much more.
Now, some of my favorite features here are, separate from FastExcel, it's called SpeedTools.
You can get SpeedTools if you buy the whole bundle.
Now, this gives you a whole bunch of new functions.
For example, under Lookups we now have advanced lookups that will let us return the first item in the list, as normal, the last item in the list or the second, or third, or fourth item in the list.
Case-sensitive lookups, memory lookups.
If you've heard of regular expressions, these are super powerful ways to find patterns of data, so maybe two digits and then three characters, and two more digits.
You can use regular expression lookups, just a great set of lookup functions.
So the Lookup is one add-on that you can buy.
Under Filter, oh my gosh, those amazing array formulas that Mike Girvin does to pull a list of distinct values.
We now have functions that will return an array of distinct values, or even just count the distinct values.
So that's another add-on.
And then all of these other groups over here, called Extras.
I mean, just simple little things, like being able to reverse an array or create a vector.
See, if you need to create the numbers 1 to 17, you can do the vector.
So let me show you a few of these.
We'll just come up with a quick series of numbers here.
I put in number 1, hold down the Ctrl key while I drag and I get 1 to 16.
If I need to reverse that… so that's under Arrays and I'm going to do the chosen function as an array formula… REVERSE.ARRAY and it takes right in, it shows us help for every single item, so here's the input area and do I want to reverse the rows?
Yes (True), reverse the columns?
Well, there's only one column, so it doesn't matter in this case.
Click OK and it reverses the number.
And of course it's a live thing, so if we change one of these numbers, the resulting array will change.
How about concatenating a range?
Usually CONCAT: you have to enter each item separated by commas, but with CONCAT.RANGE we can say we want to concatenate all of these values with a Divider of comma-space(“, “), the NumberFormat: see, right now is putting a whole bunch of number formats, we can just say it's “0” number format, if it's blank and we want to put something else in.
Do we want to start with some other text, now I'll just go like this.
And we have a concatenation of that range.
The trick of creating… using row to create a vector of numbers.
I'm going to say =VECTOR, I want to start at 1, end at 99, step of 2 and we want to generate a column of numbers, so False here, Ctrl+Shift+Enter and it generates that vector as a single array formula.
Here's examples of the COUNTDISTINCT.
So here we have: from country (FromCtry), to country (ToCtry), so there's a lot of different city pairs, right.
So counting the number of distinct combinations of those, count distinct rows, there's 420 different pairs.
But this formula: that ignores the rows, is just looking for unique value.
There's 21 cities, so 21 cities, but 420 combinations, where they appear.
Or here, check this out: that LISTDISTINCTS.COUNT will give us the list of those items.
Truly an amazing set of formulas.
Now, at the end of every one of Charles’ seminars, I'm like: I've got to get these tools, they're absolutely amazing.
While the pricing options seem confusing.
Let me try and break it down for you.
We have these different options and I can show this graphically.
So the basic, being able to time range, calculate range, calculate sheet, is $19.
To do the Profiler, where you look at your workbook and find the bottlenecks, is $89.
Those cool new Functions and the Speed Tools: they're breaking… they’re broken down to $59 for the Lookups, $59 for the Filter and $29 for all of these other minor ones, or you can buy this entire bundle for $139.
The FastExcel Manager: we can change, View/Edit Formulas, the Sheet Manager, Name Mgr Pro – that's $29.
But I think the better way to go, is to buy the entire package.
All of these items, the whole toolbar for $189 and what a deal, if you're seeing this before July 31st 2014, use this coupon code, the introductory offer, it's only $94.50 for the whole thing.
And hey, if you're worried that you start to use one of those great new functions in the Filters group or the Lookups group, Charles offers volume licensing, so depending on the size of your company, the price for that little piece of the add-in is going to fall dramatically.
I've seen a lot of add-ins for Excel, I've seen add-ins written by really smart people and they do lots of cool things, the FastExcel does things, that I've never seen done anywhere else.
Trust me, I'll be coming back to FastExcel, I can't wait for the next Dueling Excel, where we have Mike's solution and my solution, and I get to time them, to see whose is faster.
This is a product you can't live without, check it out.
Well, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
