Finally catching up to Google Sheets on dynamic arrays. One of the only reasons I keep going back to Google Sheets for misc tasks is that it's dead simple to manipulate data into dashboards compared to Excel, that and the docs are more accessible, and scripting is in JS instead of VBA.
Hi Jon.. as usual.. excellent video. These new dynamic array functions are very exciting. I can't wait to dive into them when they hit the general population. Thanks for the intro and insights as to what is on the horizon. Thumbs up!
I've been wanting COUNTUNIQUE ever since I first started in Excel. Now, I assume it will be as easy as COUNTA(UNIQUE(range)). Can't wait to try it out!
These are awesome functions, look forward to seeing those in 365. Sadly I suspect that the glacial enterprise world that is stuck on old versions will be waiting years to see these functions
you all probably dont care but does any of you know a method to get back into an Instagram account..? I was dumb forgot the login password. I love any tips you can offer me
Really good function..i want to apply the same function in my sheet however i am not able to apply the same formula as i am not getting the option..how would i get this...
Do you have a video on how to create drop downs from with drop downs? So basically I need to create a drop down with eg 10 options, but then some of those need to expand out to another list of up to 20 options. I can create the initial drop down but can’t expand from there.
Hey Jon...nice job!!! Is it that Excel developers are following Google Sheets developers or the other way around? Most of these updates are already available in Google Sheets from a long time ago. Please let me know what you think. Thank you.
Thanks Vigneshwaran! Yeah 2019 is going to be a bit confusing. That is a stand alone (perpetual) license that will mostly be used by enterprises that don't want to be on Office 365 subscription. Office 365 subscribers will continue to get the most recent updates. I think MS is trying to get away from the year versions, and just make Office 365, but releasing 2019 makes it confusing! :-)
Will this create compatibility issues, if used in VBA with, for example, Evaluate, and that file is then opened on an older version of Excel, I wonder..?
So frustrating that over a year later and this still is not available, at least to my works version of excel. Pains me to think how much longer I have to wait for this AND Xlookup. I can only hope it will be available by the time i retire in 2060 but I am not getting my hopes up. ;) I should stop watching these new features in excel as they my hopes up way to early haha
Hi Jose, Great question! Yes, we can use the Spill Range in a combobox. Currently you will just need to create a named range with the spill ref notation to the range (A4#). Then use the named range ref for the source of the combobox. The combobox does not directly read the spill ref notation yet, so the named range is a bit of a workaround, but it does work today. I'm sure this will be updated in the future so other objects like controls and charts can read the spill ref notation. Either way, it's really cool that it all works for dynamic ranges in other objects like charts, data validation, and controls.
Great question! The dynamic arrays will only be available in Office 365. They will NOT be available in Excel 2019. Office/Excel 2019 will not be getting recent updates that Office 365 receives. This is on purpose as some enterprise clients/users don't want to receive updates that could break compatibility or have temporary bugs. I'll do another video on the difference between Office 365 and Office 2019 to explain further. But the short answer is that you will want to be on Office 365 to get the latest and greatest features. I hope that helps. Thanks!
Great content! I have a question. I'm keeping YTD sales, by month, in a chart. Once the sales crosses a certain threshold (ie $50,000), which usually happens mid month, we must show this split: sales before the threshold and sales after. For example, in the month of April, if I crossed the threshold, I would need to show sales in April before reaching $50k, and after $50k. How would i get the YTD sales to split, while showing pre-/post- threshold sales in that month?
Hi Ganesh, Currently the features have only been released to subscribers on the free Insiders Fast channel. I explain more about it at the following link. www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/#coming-soon I hope that helps.
1) I use excel, 2) I also use a software by apple called filemaker. Filemaker has had these functionalities since 2010 or maybe earlier. Excel is just a software that's being used more commonly by all. I also ended up using filemaker by accident and realised excel is only 1% of filemaker in friendliness and even google sheets has more friendly friendly functionality than microsoft. 3) though im using office 365 business premium, because its more commonly used. Im shifting more and more to filemaker and google sheets with a G suite subscription. the IMPORTRANGE functionality has been in google since ages, that too for free. And works like magic, what excel is attempting in its ENGINE now, is what google has been doing since the times of Jesus Christ. 4) just sharing so you all can know too.
How true, Paramveer! And sad, too! I also had to shift to google sheets because of formulas like IMPORTRANGE, FILTER and UNIQUE - I couldn't believe Microsoft had not implemented these before google.
Succes Lotus best part is these formulas still don’t work in excel for everyone, they only work when you are working on your hard drive with the file and you obviously cannot collaborate or work in teams, When you try using these formulas with your team ONLINE they don’t work, data validation gets corrupted, and the sheet goes haywire.
@@paramveerssachdeva These formulas are only for insiders, to try - they're not implemented in the stable versions of excel... What's more, the FILTER formula in excel is just a weak version of the one in Sheets, as it's much more simple and it doesn't accept multiple conditions, like the one in Sheets.
I have my office 365, but this =unique() function doesn't seem to work for me for some reason...Can anyone help please? (It does say my version is up to date.)
Thanks Jon! Great formula indeed is on the way. Just wondering....in a big global world, if I'll use those new formulas and will send it to someone else with older excel version it will not work. Isn't it? Sadly, the captebilities issues between excel verions are killing this great program. How come microsoft don't think of some kind of solution to make bridge beyween new and older verions?
That's correct. The new functions are not going to work in older versions of Excel. I believe a lot of changes have to happen behind the scenes with the calc engine to make these work, and Microsoft probably isn't going to update older versions. They do have a forward looking solution though, which is Office 365. With Office 365 we will no longer have year versions like 2013, 2016, 2019, etc. There will just be one main version of Excel and everyone will be able to get the updates. You or your IT admin can control how frequently you get the updates. Some companies are slower to roll out the new builds, as they have to do their own internal testing first. However, features like this might push users/employees to demand the newer updates sooner. And believe it or not, some companies don't want the new features and prefer to stick with the older versions that they feel are reliable. So, the main point is that it is impossible to please everyone. :-) However, we are in a transition period right now with Office 365. A lot of companies are adopting it, and new features like this will increase that demand. Hopefully in the future we won't have to worry about compatibility issues as more people move to 365. This migration can be slow for organizations, so it could realistically take 5-10 years for this to happen. That is Microsoft's long term solution to this issue. I hope that helps. :-)
@@daviddarby3738 With Office 365 you will still be downloading and installing the Desktop version of Excel for Windows or Mac. These desktop versions still support VBA macros. Nothing has changed there, and Microsoft has actually extended the object model to include support for the new dynamic arrays in VBA. I hope that helps.
@@ExcelCampus Thanks for that. Trying to keep up with the new changes. I understand Excel can now be used as a live real time network database using One Drive rather than "MySQL for Excel". Doe's that include the desktop version?
Hi Dimitris, These features will be coming to Office 365. Currently, if you have an Office 365 subscription you are downloading Excel 2016 for Windows and they will be available there. I hope that helps.
Hi Yograj, Currently the features have only been released to subscribers on the free Insiders Fast channel. I explain more about it at the following link. www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/#coming-soon
HEY I HOPE YOU KEEPING WELL, I NEED SOME HELP FOR MY WEEKLY TIME TABLE... EXAMPLE; FOR RAW, IF I PUT 1 ON B1 IS THERE ANYWAY EXCEL FILL THE REST FOR ME UNTIL H1... SO IF I PUT 1 ON B1 AND PRESS ENTER C1 APPEAR 2 D1 3 , E1 4, F1 5, G1 6 H1 7, AND SAME AGAIN IF I PUT 8 ON B1 AND EXCEL AUTOMATICALLY FILL THERE REST NUMBER FOR ME UNTIL 14. AND IF I PUT B1 15 AND SAME AGAIN FILL THE REST NUMBER FOR ME, I DONT KNOW IF THERE IS ANY FORMULA FOR THIS... HOPE YOU HELP ME... THANKS ....
Finally catching up to Google Sheets on dynamic arrays. One of the only reasons I keep going back to Google Sheets for misc tasks is that it's dead simple to manipulate data into dashboards compared to Excel, that and the docs are more accessible, and scripting is in JS instead of VBA.
This is such a neat implementation and faster way. Thanks for tutorial
Great video/audio quality, explanations and speed. Very helpful videos. Thanks and congrats on your channel.
Hi Jon.. as usual.. excellent video. These new dynamic array functions are very exciting. I can't wait to dive into them when they hit the general population. Thanks for the intro and insights as to what is on the horizon. Thumbs up!
These new functions are changing the world of calculations.
I've been wanting COUNTUNIQUE ever since I first started in Excel. Now, I assume it will be as easy as COUNTA(UNIQUE(range)). Can't wait to try it out!
That has just helped me save hours on preparing a template spreadsheet. Thank you for a clear explanation and to excel for building such helpful tools
Glad it was helpful! 😀
Amazing features indeed. Thanks Jon for sharing with us these features. We are most grateful!
Thanks Francis! :-)
Wish these were available years ago... will have saved a lot of work! :-)
good information!
Super Duper, Jon! And thanks so much for sharing these new functions. Can't wait!!!Also, thanks for the link to Bill's book.
Thanks Ken! I appreciate your support. :-)
So excited!!! I can’t wait to test multi level dependent drop down lists with the filter function, have you tested it yet?
Great formule sir
thanks for the video! array formulas will not be a problem anymore...
Yes! This is much easier! :-)
Great video. Thanks a lot 😊 Saved it to my playlist.
Thanks Jon. I can't wait to have them in office 365 si i will start playing about with them. :)
Can't wait for you to get them John, and see what everyone comes up with. Lots to learn and explore.
New Dynamic Array Functions are awesome!
Oh yeah! :-) Thanks Adam!
Just make my life a paradisr. Thanks a lot
Game changing!
YES!!!
Thx, unfortunate, we cannot share with persons not subscripted to inside? so back gto square 1. Love the formulas though and your tuts ofcourse.
Thanks for the video, it's really helpful!
Thanks Mike! :-)
Thanks for the new tips, it will be of great help and will make it much easier!
Thanks Luciano! :-)
These are awesome functions, look forward to seeing those in 365.
Sadly I suspect that the glacial enterprise world that is stuck on old versions will be waiting years to see these functions
you all probably dont care but does any of you know a method to get back into an Instagram account..?
I was dumb forgot the login password. I love any tips you can offer me
@Judson Braylen instablaster ;)
Very cool but you can actually copy the entire column and do "remove duplicate". That would work too.
Great job of explaining it... Thank You! :)
This is very useful! Thank you for the video!
Really good function..i want to apply the same function in my sheet however i am not able to apply the same formula as i am not getting the option..how would i get this...
Watch the whole video. The answer is in it.
Really Very Helpful. Nice & Cool Functions...
Do you have a video on how to create drop downs from with drop downs?
So basically I need to create a drop down with eg 10 options, but then some of those need to expand out to another list of up to 20 options.
I can create the initial drop down but can’t expand from there.
Thanks Jon!,Very useful.
I hope the VBA will have some new features as well. No update since office 2007.
Hey Jon...nice job!!! Is it that Excel developers are following Google Sheets developers or the other way around? Most of these updates are already available in Google Sheets from a long time ago. Please let me know what you think. Thank you.
As usual your content is awesome. Jon I excepting a video about office 2019 from you.
Thanks Vigneshwaran! Yeah 2019 is going to be a bit confusing. That is a stand alone (perpetual) license that will mostly be used by enterprises that don't want to be on Office 365 subscription. Office 365 subscribers will continue to get the most recent updates. I think MS is trying to get away from the year versions, and just make Office 365, but releasing 2019 makes it confusing! :-)
Hope there is new function can create dependant dropdown list using this unique function
Will this create compatibility issues, if used in VBA with, for example, Evaluate, and that file is then opened on an older version of Excel, I wonder..?
Instead of the old MS crl+ shift array formula what i simply do is run a pivot on the list. Put it in pivot rows and paste special values.
How do we get this updated array ability? Is it a service pack type update?
So frustrating that over a year later and this still is not available, at least to my works version of excel. Pains me to think how much longer I have to wait for this AND Xlookup. I can only hope it will be available by the time i retire in 2060 but I am not getting my hopes up. ;)
I should stop watching these new features in excel as they my hopes up way to early haha
Wow. Just wooooooow.
I completely agree Martin! :-)
Thank you! I hope this is available for Mac soon.
Cool! Really cool stuff!!
I agree! :-) Thanks Wonjin!
Finally, I am really hate CTRL+SHIFT+ENTER formula..
Very useful video and thechniques!!
Is possible to apply this same Unique List tecnique in Combo boxes?
Hi Jose, Great question! Yes, we can use the Spill Range in a combobox. Currently you will just need to create a named range with the spill ref notation to the range (A4#). Then use the named range ref for the source of the combobox. The combobox does not directly read the spill ref notation yet, so the named range is a bit of a workaround, but it does work today. I'm sure this will be updated in the future so other objects like controls and charts can read the spill ref notation. Either way, it's really cool that it all works for dynamic ranges in other objects like charts, data validation, and controls.
Thank you for this help
Sir,
Place make video missing serial this cell. Not another sheet,row&columns .
thanks
Are these features available in office 365 ? Currently am using office 365.
Wow, I understand this is in Office 365,
Are we expecting these functions in office 2019 as well?
Great question! The dynamic arrays will only be available in Office 365. They will NOT be available in Excel 2019. Office/Excel 2019 will not be getting recent updates that Office 365 receives. This is on purpose as some enterprise clients/users don't want to receive updates that could break compatibility or have temporary bugs. I'll do another video on the difference between Office 365 and Office 2019 to explain further. But the short answer is that you will want to be on Office 365 to get the latest and greatest features. I hope that helps. Thanks!
@@ExcelCampus So, I am on Office 365 (4/29/19) and still do not have UNIQUE or the other "NEW STUFF". Tell me how, please.
i’m currently subscriber to 365 , when i’ll b getting those?
Do you know what version of the desktop app these are being rolled out in?
Great content! I have a question. I'm keeping YTD sales, by month, in a chart. Once the sales crosses a certain threshold (ie $50,000), which usually happens mid month, we must show this split: sales before the threshold and sales after. For example, in the month of April, if I crossed the threshold, I would need to show sales in April before reaching $50k, and after $50k. How would i get the YTD sales to split, while showing pre-/post- threshold sales in that month?
Sir unique formula is not showing in my excel 2016, Kindly suggest how can I add this function
Wonderful! Thanks Jon. I'm using Excel 2016, how can do I use this functions?
Hi Ganesh, Currently the features have only been released to subscribers on the free Insiders Fast channel. I explain more about it at the following link. www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/#coming-soon
I hope that helps.
Too bad my excel doesn’t seem to have these formulas. Mine is 365 too but I can only find it working in web excel 😢
1) I use excel,
2) I also use a software by apple called filemaker. Filemaker has had these functionalities since 2010 or maybe earlier. Excel is just a software that's being used more commonly by all. I also ended up using filemaker by accident and realised excel is only 1% of filemaker in friendliness and even google sheets has more friendly friendly functionality than microsoft.
3) though im using office 365 business premium, because its more commonly used. Im shifting more and more to filemaker and google sheets with a G suite subscription.
the IMPORTRANGE functionality has been in google since ages, that too for free. And works like magic, what excel is attempting in its ENGINE now, is what google has been doing since the times of Jesus Christ.
4) just sharing so you all can know too.
How true, Paramveer! And sad, too! I also had to shift to google sheets because of formulas like IMPORTRANGE, FILTER and UNIQUE - I couldn't believe Microsoft had not implemented these before google.
Succes Lotus best part is these formulas still don’t work in excel for everyone, they only work when you are working on your hard drive with the file and you obviously cannot collaborate or work in teams,
When you try using these formulas with your team ONLINE they don’t work, data validation gets corrupted, and the sheet goes haywire.
@@paramveerssachdeva These formulas are only for insiders, to try - they're not implemented in the stable versions of excel... What's more, the FILTER formula in excel is just a weak version of the one in Sheets, as it's much more simple and it doesn't accept multiple conditions, like the one in Sheets.
I have my office 365, but this =unique() function doesn't seem to work for me for some reason...Can anyone help please? (It does say my version is up to date.)
Thanks Jon!
Great formula indeed is on the way.
Just wondering....in a big global world, if I'll use those new formulas and will send it to someone else with older excel version it will not work.
Isn't it?
Sadly, the captebilities issues between excel verions are killing this great program.
How come microsoft don't think of some kind of solution to make bridge beyween new and older verions?
That's correct. The new functions are not going to work in older versions of Excel. I believe a lot of changes have to happen behind the scenes with the calc engine to make these work, and Microsoft probably isn't going to update older versions. They do have a forward looking solution though, which is Office 365. With Office 365 we will no longer have year versions like 2013, 2016, 2019, etc. There will just be one main version of Excel and everyone will be able to get the updates.
You or your IT admin can control how frequently you get the updates. Some companies are slower to roll out the new builds, as they have to do their own internal testing first. However, features like this might push users/employees to demand the newer updates sooner.
And believe it or not, some companies don't want the new features and prefer to stick with the older versions that they feel are reliable. So, the main point is that it is impossible to please everyone. :-)
However, we are in a transition period right now with Office 365. A lot of companies are adopting it, and new features like this will increase that demand. Hopefully in the future we won't have to worry about compatibility issues as more people move to 365. This migration can be slow for organizations, so it could realistically take 5-10 years for this to happen. That is Microsoft's long term solution to this issue.
I hope that helps. :-)
@@ExcelCampus
Thanks a lot Jon
365 will never be the final solution without full macro support.
@@daviddarby3738 With Office 365 you will still be downloading and installing the Desktop version of Excel for Windows or Mac. These desktop versions still support VBA macros. Nothing has changed there, and Microsoft has actually extended the object model to include support for the new dynamic arrays in VBA. I hope that helps.
@@ExcelCampus Thanks for that. Trying to keep up with the new changes.
I understand Excel can now be used as a live real time network database using One Drive rather than "MySQL for Excel". Doe's that include the desktop version?
When using "Sumif" with a range for criteria I'm getting a spill range of my numbers as opposed to a sum of the numbers. Any ideas??
It's good
I am using home version but i can not find this formular
Are these features in Office 365 available? or only in 2019 version?
Hi Dimitris,
These features will be coming to Office 365. Currently, if you have an Office 365 subscription you are downloading Excel 2016 for Windows and they will be available there. I hope that helps.
how to insert dynamic array functions to excel if they have not existed in formulas list
How to add this functions to excel 2016, I have already updated my office 365.
how can I add these functions to my MO Professional Plus 2016?
Great..
Love it
I have Office 2019 and I have not found this function
Then how to update excel so i can use this unique formula?
Dear sir kindly share how I add this feature in excel 2016
Mahesh Yadav latest version required
I do a lot of work in Google Sheets. Guess what, it already does this.
I love how people are going bonkers over these 'new' features for Excel while Google Sheets has had them since day one.
Google sheets does this since many years...
my office 2016 don't have "unique" formula.........what to do about it??
Upgrade to the new released office or making use of PivotTable
Hi Yograj, Currently the features have only been released to subscribers on the free Insiders Fast channel. I explain more about it at the following link. www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/#coming-soon
why does my excel 2019 not have 'sort',' unique' functions? but It has 'ifs' function?
The Dynamic Array Functions are only available on Microsoft/Office 365. They will not be available on Excel 2019. Sorry for the late reply.
👍👍👍👍👍👍👍👍👍👍
HEY I HOPE YOU KEEPING WELL, I NEED SOME HELP FOR MY WEEKLY TIME TABLE...
EXAMPLE;
FOR RAW,
IF I PUT 1 ON B1 IS THERE ANYWAY EXCEL FILL THE REST FOR ME UNTIL H1...
SO IF I PUT 1 ON B1 AND PRESS ENTER C1 APPEAR 2 D1 3 , E1 4, F1 5, G1 6 H1 7,
AND SAME AGAIN IF I PUT 8 ON B1 AND EXCEL AUTOMATICALLY FILL THERE REST NUMBER FOR ME UNTIL 14.
AND IF I PUT B1 15 AND SAME AGAIN FILL THE REST NUMBER FOR ME,
I DONT KNOW IF THERE IS ANY FORMULA FOR THIS...
HOPE YOU HELP ME...
THANKS
....
I didn't understand
Why these functions are not in Office 2019?
it is an its giving me a huge headache
Thank God, I hate array formulas.