This ~NEW~ Excel Function is Shockingly Powerful!

Рет қаралды 219,521

Күн бұрын

Every heard of SCAN function in Excel? Today, let me show you why you need this function and how it changes the way you solve your data problems. I think SCAN is Magic ✨
You are going learn:
What is SCAN function in Excel
SCAN syntax and application
How to use SCAN to calculate running total quickly
Using SCAN to get Maximum values
SCAN with LAMBDA
Advanced SCAN usage when your data is dynamic
~WHAT IS SCAN FUNCTION~
Scan function in Excel let's us run an operation on a range and returns all the intermediate results.
For example, you can use SCAN function to calculate running total of a range.
~WHAT IS THE SYNTAX OF SCAN~
SCAN(initial_value, range or array, operation)
For example:
=SCAN(0, A1:A10, SUM)
is going to return the running to of the range A1:A10
You can also use LAMBDA functions with SCAN to create more complex calculations.
=SCAN(0, A1:A10, LAMBDA(a, b, SUM(a,b)))
will return the same values as the first SCAN example above.
⏱ Video timestamps:
==================
0:00 - What is SCAN function & Why it is important?
1:46 - Understanding how SCAN works
3:46 - Using SCAN to calculate running MAX
4:57 - Using LAMBDA functions with SCAN
6:38 - How I use SCAN to solve tricky problems
📺 Watch next
============
How to use LAMBDA function in Excel - • Excel LAMBDA() Functio...
How to use FILTER function in Excel - • I don’t use filters in...
Top 10 Essential Excel functions - • Top 10 Excel Functions...
📁 Sample Workbook:
==================
If you need a file with SCAN examples, download the scandoo.xlsx here:
chandoo.org/wp/wp-content/upl...
~
What is the favourite Excel function of a German Shepard 🐶?
LAMBda... 🐑
#Excel #functions

Пікірлер: 185

@chandoo_ Ай бұрын

🚨💡💡 Getting an error? As I understand, The SUM option is slowly rolling out to non-beta users. Try this alternative: =SCAN(0, range, LAMBDA(a,b, a+b))

@akidasharif2701 Ай бұрын

Thanks it worked

@JevVan Ай бұрын

Yes, thanks! I was struggling and the formula builder says I can only use lambda as the function. Otherwise, I get a #NAME error. No I have a reason to use lambda. Thanks!

@SanjaySinghWT Ай бұрын

I am using it but getting notification "You have entered too few arguments for this function" =SCAN(0,C2:C14,LAMBDA(C2,C3,C2+C3))

@moviesathome5329 Ай бұрын

@@JevVan did you find out how to solve the error? I had the same error with you when I use the formula (without Lambda): SCAN(0,B2:B13,sum).

@meniporat3527 Ай бұрын

@@SanjaySinghWT You forgot the function: SUM Try this: =SCAN(0,C2:C14,LAMBDA(C2,C3,SUM(C2+C3)))

@FarranLee Ай бұрын

I liked how you started the presentation with a demo of the actual function, instead of leading us through wondering what's coming. But I LOVED when you started explaining the function with pen and paper. Nice analogue humanist connection. Thank you! 🙏

@martinmcsweeney732 11 күн бұрын

YES! You said it better than I could have. I love this explanation. "just show me how it works". Thank you, Chandoo.

@AbhishekKumar-wn7et Ай бұрын

*Chandoo literally has gold standard Excel skills.*

@momotaroreincarnatednthtim6303 Ай бұрын

I wonder if he and Leila are a couple. If not i would be devastated.

@michaelcottle6270 Ай бұрын

My formula for a running total in column A is =SUM(A$1:A1) in B1, then copy it down as far as you need. Simple.

@nobodynobody1235 Ай бұрын

Lol, this is between professional vs fancy way of doing

@WiFiJeremy Ай бұрын

I used the same way for quite a long time. But don't look past the function itself. The idea here is to demonstrate the function, put it in your toolbox, and figure out how to use it later in a novel way. (I haven't yet, but I'll come up with something :-) )

@peterbartholomew7409 25 күн бұрын

@@nobodynobody1235 And which is which? I would argue that it is the dynamic array formula that is 'professional'!

@lbhibbert 23 күн бұрын

@@peterbartholomew7409 And I would argue that if originally this was done in a Table, Excel would automatically fill the formula dynamically

@Dr_Kenneth_Noisewater 11 күн бұрын

I still don’t understand the point of SCAN so why did he demo it that way and then switch to LAMBDA which made even less sense. I’m sure this function has some niche advantage but I don’t get it so I don’t think it’s gonna apply to my work.

@lucadjit Ай бұрын

The function REDUCE is also very useful. In the last example, using REDUCE instead of SCAN you can obtain directly the cumulative sum of visits for every month.

@chandoo_ Ай бұрын

Great point. You can also use SUM(CHOOSECOLS(FILTER(...),2)) to get the total directly.

@jtmh31 Ай бұрын

I tried the LAMBDA with a SUM, and it worked perfectly. Thanks!

@alnoiwangmuang8547 Күн бұрын

Very useful function. Great real scenario example. 👌

@chonzadaniel 24 күн бұрын

Wow, thanks. SCAN combined with lambda function works very well in a running decaying series. I was using a too ordinary formula to handle this. Now you made it even more easier for me! For example, one may need to monitor a total budget supporting different items, how it goes down for every expenditure incurred. SCAN with lambda function in it makes it a straight forward approach🙂

@ramoasutik6770 Ай бұрын

Thank you for such a simple Formular for running total.

@mohemmedansari8664 Ай бұрын

Appreciations for helping us to maximize value from Excel.

@gamesandexperiments8177 Ай бұрын

The way we explain real calculation on paper it's great. 👍

@AjayPrashar Ай бұрын

You are loved, Sir.

@UtuDudas 28 күн бұрын

What I love about scan and reduce is that they are able to make array formulas spill both vertically and horizontally. For example you can transpose the results of filter in the adjacent columns and at the same time make filter calculate vertically. A one cell formula with results in multiple rows and columns

@jordiprochorov6901 Ай бұрын

Thank you, Chandoo. This is fantastic. I will try at work on Monday. :)

@arbazahmad7177 Ай бұрын

Excellent.. really how easeeee is this.... Thanks for sharing 🎉...

@Doctor_OSKAR_Electrik_ 27 күн бұрын

Great content with top-notch explanations. My new favorite Excel expert!

@unnikrishnansanthosh Ай бұрын

very informative sir, thank you for sharing

@irenekend Ай бұрын

Awesome. Thanks for sharing

@Barrys_Workshop Ай бұрын

very clear explanation thank you

@AbhishekKumar-wn7et Ай бұрын

Excel is the best Data Analysis tool till date. It is the jack of all but master of none. And that is the USP of this magical tool and also the weakness.

@nadermounir8228 Ай бұрын

Very insightful Video. Thank you for your hard work

@bennysitub 27 күн бұрын

Looking forward to watching your LAMDA video. It's taking me a long time to wrap my head around LAMDA. I am sometimes successful with it, but I don't know why!

@samorah0017 Ай бұрын

This is insightful! Thanks for sharing

@TheTruthSeeker756 9 күн бұрын

Wow, LOVE the detailed the explanation!♥

@kanhaiyashaha2182 Ай бұрын

Awesome….thank you very much

@transformer6786 Ай бұрын

Good. More power to you from Pakistan.

@miragurumoorthy95 Ай бұрын

Thank you Chandoo!!so useful

@ashokanselliah9571 Ай бұрын

Excellent Sir

@geraldthorburn1123 Ай бұрын

Beautifully explained as ever, Chandoo. Gold Star.❤

@chandoo_ Ай бұрын

Glad you liked it!

@somebodypeculiar Ай бұрын

Fascinating stuff. I don't see myself ever using it, but that doesn't mean it isn't interesting.

@LLhawksley Ай бұрын

thanks, this was really interesting

@rajm5349 Ай бұрын

sir thanks for explning and if we want to know which day has the max value acrding mnth wise, then can we use day function

@vipulagarwal7320 Ай бұрын

Its key clickin' good!!!

@CaribouDataScience Ай бұрын

Good stuff!

@abdelazizallam Ай бұрын

thanks plz do video in different between lambda helper functions like scan . map reduce

@esthercraft Ай бұрын

Great video, what about alternatives to calculatr dynamic moving averages , last 3 davys 7 days monthly quarterly etc ..and best approach from product analytics perfotmance point of view to make business sense of your data

@ExcelTemplatesSolutions 21 күн бұрын

Nice thank you

@elanravidpm 10 күн бұрын

Thanks for all the Tips. Extremely useful Help needed on two topics. How do I use scan function to manage bank accounts? I have debit column, credit column and running Balance. Running balance is prev row balance + current row credit - current row debit. Can scan be used here. One another help is: for my investment tracking How can XIRR be calculated? The columns I have are Stock name, Buy date, sell date, buy value, sell value, current value [Current value is non-zero if sell value is zero, else it is zero]. With this 1. How do I get XIRR for each instrument i.e for each row 2. How do I get current XIRR overall? Thanks, Elango

@Acheiropoietos Ай бұрын

Good job Chandoo 👊🏻

@ricardoalmeida7607 Ай бұрын

Thanks

@leophrix 12 күн бұрын

Wow 😮 New learning for tonight ❤

@chandoo_ 12 күн бұрын

Wonderful!

@williamrhopkins Ай бұрын

A bit like list accumulate in power query. Functional programming. Thanks for pointing it out.

@peterbartholomew7409 25 күн бұрын

I think the functionality is pretty new to PowerQuery as well. There was a time in both PQ and DA that to calculate the next sum involved going back to the first record and summing to the current record. It may well be SCAN that came first.

@chrism9037 Ай бұрын

Amazing Chandoo!

@lorenakademar5267 Ай бұрын

Great unknown formula...from which version is available?

@nazarkamal8831 Ай бұрын

Amazing 👍👍👍👍

@tsekokhumalo3042 Ай бұрын

Genius

@IanYates82 Ай бұрын

I hadn't heard of choosecol and that spill range syntax before - handy. For a tutorial video I'd suggest naming the a & b parameters as acc & curr to make it more obvious which is which - that's how I name them when writing JS / C# etc

@chandoo_ Ай бұрын

I am glad you found that helpful. Thanks for the suggestion reg. acc, curr. As most of my viewers have no coding experience, I went with a,b to keep it simple.

@asujan Ай бұрын

thank you thanks to SCAN

@rampallyvishal8137 Ай бұрын

Super function🕵‍♂🕵‍♂🕵‍♂

@JunaidKhan-gq8nw Ай бұрын

Superb

@ivanbork4175 Ай бұрын

How nice it would be if Microsoft provide us with a list of what is new, at the end of an update. If it is there and I have missed it, please say.

@Vinu9484 Ай бұрын

Since one year I'm using this formula in Google sheets Also the byrow, bycol,reduce, map and scan too

@peterbartholomew7409 25 күн бұрын

The appeared late 2021 in Excel insiders beta.

@abuzainah1545 Ай бұрын

Thank you, @Chandoo, very amazing funtion! and Awesome too!

@pradeep2sharma Ай бұрын

Thank you so much Sir 🙏🏼, however, I'm stucked on a different problem, how to connect with you, unable to get help from the experts I am familiar with.

@sandordugalin8951 Ай бұрын

So it's FOR loop

@karelberanek1007 Ай бұрын

The third example is perfect. Scan obviously saves a lot of typing and makes it easier to read. So definitely a nice thing. The first two examples are a bit... not well explained/sold. E5: =$D5 E6: =$E5+$D6 F5: =$D5 F6: =max($F5, $D6) And then just select E6:F6 and doubleclick the bottom right corner to get it copied to the following lines. If you wanted to make this a single formula for each column then you can use if and isnumber to get the formula on row 5 to handle the fact there are no values on row above, just labels. However changing order of the rows may ruin the fun, there the SCAN would be handy too. The third case can also be solved using a formula with ifs and conditional formatting. But it would look too messy. So I am happy there is a better way! Thanks!

@chandoo_ Ай бұрын

Thank you. I agree we could do with a better / different explanation. That said, SCAN requires thinking about data in ranges and introducing that "new" idea would be a challenge if I had jumped straight to example #3.

@ivanbork4175 Ай бұрын

Amazing video as always, but I struggle with a “NAME?” error even though I have followed your instructions, “=SCAN(0;C5:C15;sum)” and yes formula separators are semicolon where I live. Can it be something with the version of Excel?

@chandoo_ Ай бұрын

Hi there... This is normal. The named shortcuts are rolling out to all users of Excel 365. Meanwhile, use this alternative syntax: =SCAN(0; C5:C15; LAMBDA(a;b; SUM(a;b)))

@Ishaytchannel 13 күн бұрын

Do you provide Excel and power bi course

@chandoo_ 13 күн бұрын

Yes, check this please - chandoo.org/wp/power-bi-course/

@michaelallen1432 Ай бұрын

You can do this with "let" You createva variable that counts the number of items in the list. Then you use that to create an array containing tjose numbers. Then you create an array where each element is the running total of the previous array. That array is what is displayed in the cell. So itvautomatically fills down.

@chandoo_ Ай бұрын

Sure.. have you tried it? Do share your formulas.

@sugurunathregal5730 Ай бұрын

please send link to down scan add on excel function

@sugurunathregal5730 Ай бұрын

my excel version has no scan fuction, please send link to download scan add on

@robh.1212 Ай бұрын

I have a table with hundreds of stock trades. I would like exel to auto generate a new unique ID for each trade as I add it to the table. I would like that ID to stick with the row that contains the trade even when I sort the table by various columns (such as date or name of stock etc.). I can get excel to auto generate a number using concate but that makes the numbers long and awkward. Is there a way to get excel to auto generate a Unique ID for each trade so that I can re-sort by ID and get them back in chronological order?

@pamelas9 21 күн бұрын

I saw another video which was about the new checkboxes and she was using NOW with iterative calculation turned on to make a timestamp when the box was checked. If the goal is a chronological sort I might try something like that.

@manthaparvatheesam Ай бұрын

I cannot get the answer as per the example. Is there anything to get the answer?

@trongnguyen75 Ай бұрын

What if you have in collumn B the years showing multiple years (example last year and current year), collumn C month, collumn D showing In the Month Downloads. Collumn D showing year to date downloads. How to calculate collumn D running total within year ? Example from 2023 Jan till 2024 march, it shows the running total in collumn D of Jan-dec 2023…and running total Jan -mar 2024. Next to that is there also a way to apply this as calculated field within pivot table ?

@chandoo_ Ай бұрын

You can use SCAN for such things too. The calculations get a bit complicated. For ex: =LET(years, COL_B, months, COL_C, values, COL_D, nums, SEQUENCE(COUNTA(values)), SCAN(0, nums, LAMBDA(a,b, IF(INDEX(months, b)=1, INDEX(values, b), INDEX(values, b)+a)))) The formula resets accumulator to first month value when month is 1. It also assumes you have only one row per year month combination. If you have daily data, you need to adjust the IF logic.

@paddle_shift Ай бұрын

To summarize, here are 6 functions that give the same results, except some functions require more keystrokes than others.

@faizanrao3574 23 күн бұрын

Need help, error function is retuning #NAME? error =SCAN(0,B2:B6,Max) =SCAN(0,B2:B6,Sum)

@kartikgada Ай бұрын

Can running total be conditional? First row is taken from a table, second row onwards it checks if number in upper cell is negative then taken from corresponding row of table, but if upper cell is positive number, then add upper cell & corresponding row of table. formula is like this in row =B2 and row 3 onwards =IF(F2

@chandoo_ Ай бұрын

You can do it. I am not sure if I got your logic right, but try this: =SCAN(0, B2:B100, LAMBDA(a, b, IF(a

@kartikgada Ай бұрын

@@chandoo_awesome. works smoothly. million thanks, for the solution and for all the knowledge that you share. so next question. my base data table comes from power query where the Column B resides. Is there any way to get same output (like above SCAN function) in power query?

@anuragkodlipet5572 Ай бұрын

Hi, by mistake Go to special is pop up in my blank excel sheet and, i am not able to get rid of it. Please help me with this issue. Am not able to work on excel

@sunnysun6567 23 күн бұрын

Chandoo, I have a question, I have a data set comes with so many repeat years , such as from 2010 to 2020 by revenue, then 2010 to 2020 by cost , then item sold … and more , how can I clean this kind of data set ?

@chandoo_ 23 күн бұрын

Use Power Query

@sunnysun6567 23 күн бұрын

@@chandoo_ I did , but the value come out with different meaning ….

@konradnowak598 21 күн бұрын

Hi, it does not function like shown in your video, according to Microsoft I had to use the lambda function!

@LorenzoMarkovian Ай бұрын

I am getting this error when I try it, #NAME? what could be the reason and how can I fix it?

@ivanxdxd 29 күн бұрын

that's beautiful. yet we still can't do for loops in excel.

@johnboffin476 Ай бұрын

Chandoo, Hello Sir, I am building a spreadsheet with 7 columns and 2500 rows. Column A is just a reference column, B is dates column which are fine, column C is with Validation drop down codes list, column D is products details with drop down, tested and working all down to 2500 rows. Column E is payments and F is Income, G is balance (Balance column work as it should) now I need your input in column E and F, If I select from column C (Codes Drop Down List) for Sales, Refund, Finance or Cash Back The entry should only allow me the income column which is column F and if selected payment from C column (Codes) it should allow me to enter in payments column. In income column I have 4 source of income and payments I have about 44 or it may grow prior (My code list and details list grows as it should). I need help using validation (Custom) in these two columns E & F, so that the entry don't get switched between two column E & F. Your help will be appreciated and thanks a lot if you could help.. (I use Excel 2000)

@Glamador Ай бұрын

For those of you familiar with the REDUCE function, you might ask what the point of SCAN is. Well, they actually do exactly the same thing! The difference? SCAN outputs an array of length of 'b', which contains the stepwise result of a REDUCE calculation. You can think of REDUCE as outputting only the last item of the SCAN output. I have not found a good use for SCAN on its own yet. My favourite use case is actually for troubleshooting REDUCE. Since it lets you "step in" to a REDUCE, you can look for mistakes and errors.

@chandoo_ Ай бұрын

Great points. I found SCAN useful in some very particular situations (like running total). I love REDUCE too.

@sureepornchongsiriluck8771 Ай бұрын

Would it work with data start with negative value instead of 0 start?

@chandoo_ Ай бұрын

It would work. If you want to Count down from a specific total, you can use that start like $100 and use negative values.

@brownj2 6 күн бұрын

Seems like a trivial function that is easily performed by adding.

@PrabhuS-qr8qn Ай бұрын

What is the advantage over using regular dynamic range using sum or max functions ?example - Sum($A$1:$A1) ?

@Rawyu Ай бұрын

I was wondering the same. I think it's something related to memory or performance and scan seems to be better.

@chandoo_ Ай бұрын

The performance would be better. Plus, as demoed later in the video, many times, you have no idea what the end point of the data is. So you don't know how far to drag or fill the formula down. SCAN helps in such situations.

@Rawyu Ай бұрын

@@chandoo_ thank you 😊

@GodAdministrator Ай бұрын

@chandoo_ hm....but it also applys to SCAN function. If you don't know the size of the range you have to adjust scan function. Why not to use smart tables with the same easy function SUM($A$2:$a3)? The same result and you can always identify which range is used to do the calculation.

@chandoo_ Ай бұрын

Please watch the full video to understand what I mean by not knowing the size. You can't have tables for calculated outputs or formula results.

@RJ_331 13 күн бұрын

Man... i feel like I'm getting so far behind with functions since my work is still using 2016😂

@ysantosh Ай бұрын

Simple explanation chandoo Anna thank u. But im getting "Invalid Name Error" while trying this in 365, any suggestions?

@chandoo_ Ай бұрын

As I understad, The SUM option is slowly rolling out to non-beta users. Try this alternative: =SCAN(0, range, LAMBDA(a,b, a+b))

@ysantosh Ай бұрын

@@chandoo_ thanks anna lambda works. Yup i think its because of SUM function

@raylopez99 Ай бұрын

Not present in Excel 2019, you get a #NAME? error. But no worries, I have "longer" ways of doing the same thing.

@DinoDelight Ай бұрын

Looks like a great function. I can't get it to work though... =scan(0,b2:b20,sum) ... won't work, I just get #NAME?, I tried MAX too. The Lambda alternstive works =scan(0,b2:b20,(lambda(a,b,a+b)) Any ideas why? I am using 365, and it is upto date

@chandoo_ Ай бұрын

No need to worry. I think the named shortcut versions are rolling out to all users of Excel 365 slowly. You might see that option working in the next few months.

@DinoDelight Ай бұрын

@chandoo_ that's great, thank you! Again great video👏

@jordiprochorov6901 Ай бұрын

I also have EXCEL 365, and it works with the alternative Try this Running Totals SCAN(0,DataTable[Column1],LAMBDA(a,b,a+b)) Max SCAN(0,DataTable[Column1],LAMBDA(a,b,MAX(a,b)))

@jordiprochorov6901 Ай бұрын

Running Totals *SCAN(0,DataTable[Column1],LAMBDA(a,b,a+b))

@jordiprochorov6901 Ай бұрын

Max *SCAN(0,DataTable[Column1],LAMBDA(a,b,MAX(a,b)))

@Axman6 Ай бұрын

All the functional programmers saying “this is new?”. I’m pretty sure this is based on work by Simon Peyton-Jones, one of the main developers of the Haskell compiler (at least before he left MS Research for Epic Games).

@chandoo_ Ай бұрын

This is "new" for millions of Excel users who have never heard about functional programming. It is liberating for those of us who know what FP is and use Excel for analysis.

@debashischandra661 27 күн бұрын

I tried several times but it is not coming with the right result. An error "Name" is correct Please help. D Chandra/Kolkata/India

@AbrarNiazi-lj2dy Ай бұрын

Technical work is not one men Shu Nature would like and nature work is like.

@radekminarik7417 Ай бұрын

SCAN Is the same like.List.Accumulate in Power Query.

@brianxyz Ай бұрын

List.Generate is like SCAN. List.Accumulate is more like REDUCE.

@chandoo_ Ай бұрын

Bingo!

@shaktarangiras8731 Ай бұрын

Thanks! To me, it seems like a Reducer function.

@maheshashu1899 Ай бұрын

Anna u look like rachinravendra Nz cricket player 😅

@timestoryx Ай бұрын

🎉🎉🎉😮

@akidasharif2701 Ай бұрын

It's not working on the excel 365

@borstenpinsel Ай бұрын

Don't watch these videos and try it out. Watch them and save them and come back to them 1 year later. You won't get access to this any earlier. If you use an enterprise version at work, it's even later (about 18 months for my company - after the "release" to the MS insider programm)

@dkgamerz7812 Ай бұрын

Most dangerous formula❤

@constantinosschinas4503 Ай бұрын

Did not understand why you had to use select column function at the last example. Sum operates on the numbers, not the dates.

@chandoo_ Ай бұрын

hmm.. watch again or see the sample file. We are adding the downloads column.

@yogendraverma9178 Ай бұрын

Getting #NAME? Error in both

@tinythings7976 Ай бұрын

Try puting formula in caps

@RichardJones73 Ай бұрын

Putting it in caps didn't help, it actually makes it go back to lower case when you press enter

@nazarkamal8831 Ай бұрын

Chandoo Anna say hi to me I am a big fan of yours

@chandoo_ Ай бұрын

Hi bro 😀

@nazarkamal8831 Ай бұрын

@@chandoo_ thanks 🙏👍 Anna

@vadimkondratiev7214 Ай бұрын

nice, but I would rather just use the usual sum, max etc funcions, dollar-freezing the begining of the search area

@chandoo_ Ай бұрын

Nothing wrong with that as long as you know what you are getting.

@georgH Ай бұрын

This is like Haskell's scan function, unfortunately, Powerquery doesn't have it...

@chandoo_ Ай бұрын

It is. Power Query also has similar ones. Check out List.Generate and List.Accumulate

@georgH Ай бұрын

@@chandoo_ Thank you for the response! Yes, I eneded up using a combination of those to do it, thank you!

@rahulahewal Ай бұрын

I am having #name error in my excel file how can I remove it.

@chandoo_ Ай бұрын

Refer to the pinned comment. This is happening for some users for whom the "shorthand" version of lambdas is not yet enabled. To fix the problem, just replace the word sum with lambda(a,b, a+b)

@rahulahewal Ай бұрын

@@chandoo_ Thanks for replying