I admit that my initial comment was over the top. For that reason I'll edit it. However, it irks me when people are misled on what they can do with current versions of Excel. Got several of my colleagues excited for no reason. Downloading Insider isn't much of a solution as it takes away files' transferability.
@@Agelastic_LuCi Understandable. The couple of features not released yet are coming very soon so you won't have to wait long. You'll want to check the release schedule for your users to see if it is monthly or 6 months.
The latest version of Office apps has moved the search box to the top middle area on the screen. It is the same in Outlook and other apps. You'll see the change once the new versions are released.
What if I want to know in which county belongs such city. Example: which county belongs La Palma and in the new column named county I would put Orange County; which county Cerritos belongs and it would say Los Angeles, which county belongs Corona, it would put on the county column Corona.
If you are referring to the Geography data type it does not have county data available. You would have to get a list of counties and cities for California and then could do an XLOOKUP to that table. You might also look at the Excel Get Data from the Web video. If there is a website with that table information you could pull it into Excel from the web and then do a lookup.
@@SeleTraining I forgot to tell you that when I type the city name in the City column I want the County Name field column gets updated automatically without me seaking the county name and type the county name. I always have to stop doing the entry and open google asking "in which county is Oxnard" and it would say Ventura County which I know by heart that Oxnard city belongs to Ventura, but many cities I don't know. Thank you.
Xlookup is climatic. I am new here and I just subscribed and liked, your programs are amazing. Still however, I wonder when microsoft will develop a lookup function that returns "nth" terms without resorting to complex/compound formulas. Its always nerve racking. Can you help with something simple please?
It will be available in all Office 365 versions but has not been released to the public yet. Some versions of Office are updated monthly and some are 6 months. So once it is released you may still have to wait for the update to your computer. It is in final beta so it should be out soon.
I have home edition, but have XLOOKUP. In the backstage view, go to Account, then change your Office Level to Office Insider, and change from Monthly to Targeted. Then Update Now. If your Office doesn't update with XLOOKUP, then update again the next day until you get it.
The limit on parameters for all Excel functions is actually 254. Since there are 2 for each in this example that limits it to 127, less the one other parameter used in the function.
XLOOKUP only returns the first value found in the list. There are optional parameters to find close matches or last entry but it does not return multiple entries.
Can you help me with this: to count IIF function I want to count the rows where it shows either on TST1 column or on the IGRA1 column shows the result 2. It will show how many patients did the test either test1 or the IGRA1 that shows the negative result=2. The number 2 is set up on the table as short number integer. I want to count the rows not the value 2+2. I tried this function =count(IIF([TST1]=2 OR [IGRA1]=2,0)) and does not work, also I want to count all the rows only in TST1 thus =count(IIF([TST1]=2,0)) but it does not work, I filter to check if the result matches, so either the filter is not working or the formula. I put these results on the report header area and in the details shows all the data where I filter to see if the formulas are counting properly.
Assuming TST1 is column A and IGRA1 is column B you can use =COUNTIFS(A2:A8,"=2",B2:B8,"=2"). This would show the count of those that are 2 in both columns. If you want the count of those that are not 2 in both columns then you can use =COUNT(A2:A8)-COUNTIFS(A2:A8,"=2",B2:B8,"=2").
You could enter the stock symbol but the database they use will have to have the stock symbol in order to get the price and other attributes. I suspect in time Microsoft will add to the Data Types. perhaps the Canadian stocks will be added too.
A lot are dual listed and come up with the American. There is a way as sometimes it comes up with some sort of search menu on the right to pick from a list but I can’t reproduce this search bar very often.
How clever you are. Thank you very much. I am currently trying to change hundreds of date entries from the 00.00.0000 format to 00/00/0000. I still have Office 2010 installed. Could you perhaps recommend a quick solution?
@@SeleTraining You are a genius!!!! I can't remember how many people I have asked for solutions to this challenge and how many hours I have spent, manually revising single cells. You have saved me so much time. Thank you VERY much. There is just one caveat. Whilst I can now convert the 00.00.0000 format by creating a 'sister column' to the right (to insert the formula), deleting the original column (00.00.0000) will make the formula column ineffective. Hope I can express myself clearly here(?)
You can highlight the new column, right-click and choose copy, then right click again and choose Paste Special. Select values from the menu. It will convert the formulas into values so they are permanent.
Only XLOOKUP and Natural Language Query have not been released to the public and those will be coming soon. For others that have been released you may be on the monthly or 6 month release versions of Office depending on how yours is setup. In Excel go to File-Account and look at the About Excel area to see which version you have. This video was done with version 1912 Office Insider.
Thanks for the Video, I do only have the Stock data type available. I cannot see Geography. I re-installed Excel to check if that was a bug it I still only have access to Stock and not Geography. I am confused
@@SeleTraining I honestly do not know either. I tried on both my windows version and then my Mac version. It is the same on both. Maybe it has something to do with my subscription?
Some are in Excel 2019 but most of these have been recently released so they are only available in Office 365. The desktop versions will take awhile to get those new features.
It appears that when you start Excel it tries to connect to the servers for each Data Type. If there is any problem with the connection the data type will be missing. See office-watch.com/2019/fix-excel-when-stock-and-geography-data-types-go-missing/.
I have a insider version so I see the latest option earlier but all of these features are available now in Office 365 except Natural Language Query and XLOOKUP which are released soon.
I don't have Data Types inmy Excel 365 and as far as I could find out, it's because my Excel is in German. IF anybody knows how I could get them, I'd be really grateful.
What about Sally in row 7, 15 & 17 Most of the NEW functions are very specialised in data that is limited. Not impressed with the last couple of "UPGRADES"!
@@SeleTraining agree.. Me personally, as an excel enthusiastic look for lesser key strokes.. I like your presentation of features in this video.. Cheers
You have always been able to do a VLookup to the left in Excel in your example at 5:10 use: =VLOOKUP("A1",CHOOSE({2,1},$A$2:$AB$18,$C$2:$C$18),2,0) It is described here: chandoo.org/wp/formula-forensics-no-028/ Hui...
I see most of the new functions except for Xlookup and Ideas. Any idea from which release does they start being available to non Insiders (currently installed with 1902)
@@SeleTraining Quite frustrating to see all this new exiting fixtures and being stuck with old versions. What makes it worst is that there are mixing of fixtures already available with others that are for Insiders only. So it's hard to have the visibility as when something will end up being effective. I'm especially waiting for Dynamic Arrays, but I'm not clear as when it could become available to the semi annual channel
@@didierseverac Microsoft does make it confusing. With Office 365 Business Premium you can get the monthly release rather than waiting for 6 months with the Pro Plus version.
@@SeleTraining The problem is with the perspective plot, not the video. Bars towards the center appear shorter. Bars at the center can’t be assessed. To me it’s a gimmick.
