[MUSIC]
>> Hello everyone, and welcome to
this Insider episode on Excel Performance Improvements.
I'm Prash Shirolkar, Excel Program Manager for Excel Performance,
and I have Excel MVP Charles Williams,
from the United Kingdom. Hello, Charles.
>> Hi, Prash. Great to be here.
>> We are here today to talk to you about this new wave
of Excel Performance Improvements in modern Excel M365.
Especially, we're going to talk about those
that are much faster than Excel 2010.
Why? Because we are committed to making Excel
M365 the new goal standard in Excel performance.
Charles, it's been a while since we talked.
Was it since Speedy Lookups?
>> Yeah, I think so. Speedy lookups are great.
They really hit one of the major pain points for people.
The large VLookups were really a pain.
The Speedy lookup stuff, it solves that.
One of the other main pain points is things like
SUMIFS and COUNTIFS, Aggregation Functions.
>> I'm glad you mentioned that
because this wave that we're pushing out this particular fall,
we actually have Aggregation Functions like SUMIFS,
COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS,
and their singular counterparts.
By the way, have you had a chance to try them
out in our Insider Bills?
>> Yeah, I always play around with everything that
comes out in Insider, as you know.
>> Awesome.
>> I'd like to show you what the difference is
between 2010 and 365 Beta.
We're going to start with 2010 and SUMIFS.
We've got 30,000 SUMIFS, 10,300 rows.
It's doing quite a lot of work.
>> You're running them side-by-side, correct,
on the same machine? Wonderful.
>> Yeah, same machine. It's my Surface Laptop 2.
Let's make that calculation and
see how long that takes.
Should be about couple of seconds, 2.49 seconds.
>> Okay.
>> But let's see what happens if we run it in an Insider Fast.
Click, we're off.
0.8 of a second.
>> Wow.
>> That's quite a significant improvement.
>> Yeah, that is very good to see. Thanks, Charles.
>> Now, the other interesting one is COUNTIFS.
I'll just change over to the COUNTIFS files.
>> First, you showed us SUMIFS,
now you have another demo on COUNTIFS, is that correct?
>> Yeah.
>> Cool. I'm glad you're playing out with
all the functions that we've sped up.
>> There's something really interesting about COUNTIFS.
It's using formulas that are using full column references.
Now usually, a lot of people do that.
I'm not a great fan of full column references myself,
but it's interesting to see what the difference is.
>> Full column references mean you have
all the cells to about one million cells in that column,
so you have four columns of about four million cells?
>> Yeah. We got 500 formulas.
It's looking for a one in column A,
two in column B, three in column C,
and four in column D. There is 39 of
them scattered around there somewhere, who knows where.
Let's run it in Excel 2010.
This one takes slightly longer.
It's 4.6 seconds.
>> Okay.
>> That's quite slow actually for 500 formulas.
Then let's run the same thing in Insider Fast.
It runs so fast,
I don't have time to talk about it.
>> Wow, that was instantaneous.
>> 0.09 seconds.
>> Wow, that is what,
six times, five to six times at least?
Wow, five to 600 times.
Oh, my goodness. That is amazing.
>> If you're using full column references,
you really need to switch to this system.
It makes a huge difference.
Now you have obviously done something serious under
the covers to cope with full column references.
>> Yeah, great point. In the past,
these functions would do what is called a linear scan.
They would essentially scan the range that you
have provided as part of expression to go look and compare,
and whether the expression matches or not.
Now what we do is we create
an internal cached index for
the range that you have provided in the expression,
and that is a fast cache that allows us to
find the appropriate values that match in the expression.
Also, what happens is when you run these aggregations,
if you reuse the same range,
we are able to reuse the cache because it allows us,
the data is available, we can reuse it.
That also allows us to gain the extra time,
as opposed to constantly just scanning
through the range provided in the expression.
That's where the savings are coming from.
>> The more formulas you have referencing the same range,
the faster it's going to get.
>> Yes. The more cells you have in the range,
the more noticeable you will see the difference
between 2010 and M365.
>> Cool.
>> Now that we've talked about aggregation functions,
how about the other improvement, a real-time data?
Now, our argument, I do understand
that it's more of a enterprise scenario,
tends to be because these are used to get,
for example, real-time stock updates from the stock market.
That said, have you had a chance to try it out,
had some fun with this particular improvement?
>> Yeah. You know me, Prash.
I always play around with anything that's new.
>> Now Charles, it looks like this is involved
with stock updates as well.
Is this like portfolios that you have?
I can see those tabs about portfolios.
>> Yeah, we got a set of portfolios so we're reading stock prices.
>> Got it.
>> We're going to pull 80,000 stock updates.
We'll start 2010 rolling.
You can see the speed is going,
getting around two-and-a-half thousand updates a second,
which is not too bad,
but it's taking a while to get that.
We start this one going.
You can see the difference in speed.
It's going to overtake it. It's finished.
>> Wow. That is cool.
>> It's just under three-and-a-half seconds.
Poor old 2010 is still trundling away.
I guess Prash, you've done something here.
I think it's to do with the multi-threading the calculation?
>> Absolutely. Great point, Charles.
Yeah, we've harnessed couple of things.
What we have done is we have sped up the ability for
calculation and processing of the data that's
coming through from the real-time data server.
It's two fold. One is we've
improved the underlying data structures and memory
to pipe this data from com servers into to a calc engine.
Then what we've done is, we have made
real-time data thread-safe which allows
us to leverage running
it on all the threads in multi-threaded recalc.
In the past, it was only running in a single-thread of recalc.
Multi-thread recalc, we have to pause after running
the calculations and then run
real-time data on single-threaded recalc.
Now, this pausing doesn't happen,
which also is time savings
that customers can experience with speed-up.
>> So that means the more cause you have on your machine,
the faster it'll run, right?
>> Yes, absolutely.
It's not just the
multi-threaded but it's also some of the bottlenecks
that we've eliminated while processing the data from the pipeline.
It's great to see that both combined are providing
a much larger impact together.
>> Yeah. Very impressive.
>> Thank you, Charles, and thank you very much for
sharing this with our customers.
Much appreciated.
Folks, as you've just seen,
Excel M365 is making a compelling case to
be the new gold standard in Excel Performance over Excel 2010.
In addition to aggregation functions and real-time data,
read our blog that provides even more information about what all
we have to offer as part of this wave of
performance improvements this fall of 2020.
Don't take our word for it, try it out.
These wave of performance improvements are available in
the 2007 update of the monthly channel
or the 2009 update of the semi-annual target channel.
Send us feedback, we'd love to hear from you.
Send us a frown,
send us a smile.
[MUSIC]
>> Once again, we'd love to hear from you,
and thank you very much for viewing this video,
and thank you very much Charles,
for sharing your experience,
sharing your demos with our customers. Much appreciated.
>> It's been fun.
[MUSIC]