Wyn Hopkins
My feedback
-
6 votes4 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Wyn Hopkins
supported this idea
·
-
14 votes
-
22 votes10 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Wyn Hopkins
supported this idea
·
Wyn Hopkins
commented
Essential to really take full advantage of Dynamic Arrays
-
87 votes8 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →Wyn Hopkins
supported this idea
·
-
251 votes27 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →Wyn Hopkins
commented
Plus.... a Get Parameter button that avoids the formula firewall...
Sorry to cross post, but folks who like this idea may well like this one
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/32691571-add-get-parameter-button-to-power-query-and-avoidWyn Hopkins
commented
If I could edit my suggestion I would. Let's call it what it is... POWER features. Adding a Publish to Power BI button would be great too. Some good options listed in Sameer's post a few items below this one
Wyn Hopkins
commented
OK, so let's call it the POWER tab (see image)
Wyn Hopkins
commented
Hi Bill, the name is tricky, and I'm sure there's probably a better name.
I'm looking at it from who it's aimed at / why have a separate ribbon. The reason is awareness for those users who simply miss out because these new features are hidden away.
How do we capture their attention, what resonates at a general level? I'm not particularly set on "Reporting" as a name but it does sort of capture why someone should look at this ribbon.
If your job involves reporting and you're not using Tables, Power Query, Pivot Tables and Power Pivot then you're missing out.
To me reporting is something dynamic and interactive that requires regular refreshing / updating.
Wyn Hopkins
commented
Sergei, definite room for improvement on my hacked together version. The Power BI publisher features I'd like to see on there too. Plus I'm sure the Excel team will have some usage stats on most popular Get From button - personally I'm always using from Excel or From Folder, but others will be different.
Calling it out as an awesome set of features is the key to me (plus grouping together the whole data analysis / reporting story into a cohesive place).
Wyn Hopkins
commented
Plus the Power BI Publisher buttons would be great. Although I'd rename them as Power BI Connector :)
Wyn Hopkins
shared this idea
·
-
41 votes3 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →Wyn Hopkins
commented
I'm on board with this
Let also add VAR and RETURN and be able to Right - Click add measures to a Measures Table.
Wyn Hopkins
supported this idea
·
-
785 votes213 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
Thanks to Graham for starting this conversation. If you would also like Excel to maintain named range references and structured table references in the “applies to” field for Conditional Formatting rules, please add your comments and vote this one up. We will prioritize accordingly.
Thanks,
Steve (MS Excel)Wyn Hopkins
supported this idea
·
-
746 votes125 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Thanks for the votes! Based on your feedback/votes we’ve just announced the new XLOOKUP function. You can read more about it here: https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376
Wyn Hopkins
commented
Hey Roy, good news.... multi criteria lookups work in the new Dynamic Array world....
=XLOOKUP( K3&L3, tblOne[FirstName]&tblOne[LastName], tblOne[Output 1]:tblOne[Output 4])
For everyone else here's the link to Bill Jelen's suggested -IFNA handler (which I alluded to in the original suggestion for GET MATCH (Optional Value if no Match). I'm all for making formulas easier to write and more accessible to a wider audience, and I think this common use case would handle that.
Wyn Hopkins
commented
All hail XLOOKUP !!!
Wyn Hopkins
commented
Thanks Roy
Wyn Hopkins
commented
Wow, I’ve not been getting alerts on updates to this so apologies for my lack of response. I’ll double down on my efforts of promoting this now. P.S. thanks Mark and Greg
Wyn Hopkins
commented
Tom, I agree with you that INDEX MATCH MATCH is really powerful and I am by no means suggesting we get rid of it nor VLOOKUP. Having worked with and trained hundreds and hundreds of Excel users I just want something simple that beginners will understand. Most users just need an exact version of INDEXMATCH without having the complexity of a formula embedded inside another formula.
Wyn Hopkins
commented
Here's how GETMATCH would work (exactly the same as INDEX MATCH except the order is changed and there are not 2 formula invoived).
=GETMATCH(A1, B1:B10, C1:C10, "No Match")
or horizontally
=GETMATCH( A1, B1:H1, B2:H2, "Match Not Found")
Wyn Hopkins
commented
Anil, it’s a simpler version of INDEX MATCH. A single formula that’s easier to understand and write and therefore accessible to more users. Ideally it would have the iferror wrapper built in also to make things even easier.
Wyn Hopkins
commented
Re VLOOKUP and table parameters, that's pretty much how INDEX MATCH works already. That's another benefit highlighted of why INDEXMATCH is more useful than VLOOKUP.
Wyn Hopkins
commented
After testing this idea out with around 200 Excel course attendees over the last year 2 things have become clearer. 1. People really like this idea. 2 the name GETMATCH might be better named LOOKUPMATCH in that it then captures elements of familiarity to the VLOOKUP AND INDEX MATCH users.
The sole reason (in my view) that the majority of users use VLOOKUP is that INDEX MATCH is the "wrong way round". Fix that with this suggestion and we'll make Excel even better
Wyn Hopkins
commented
No VBA required, existing functionality already exists, just the order of parameters need changing from this
=IFERROR ( INDEX ( GetColumn ), MATCH (ReferenceToMatch, MatchColumn,0)), "TEXT IF ERROR")
Wyn Hopkins
commented
Hi Anthony, I see what your saying but that's not in keeping with my hope of making a simpler version of INDEX MATCH. My suggestion is targetted at the 95% of users who need single column (occasionally row) lookups.
Wyn Hopkins
commented
Great to be up to number 5 in the rankings, please encourage others to vote.
I've also pulled together a video showing how INDEX MATCH currently works so well with tables but I end up using an autocorrect trick in the absence of this requested new formulahttps://www.youtube.com/watch?v=cT6K8Gz97PE&feature=youtu.be
Wyn Hopkins
commented
Mike, I agree about the fact that this will also provide a simpler alternative to HLOOKUP. Unfortunately I am blocked from making any changes to my suggestion now that people are voting for it (Thanks everyone by the way!). I can't even change my spelling mistake :)
Wyn Hopkins
commented
Hi Excel Team, any feedback for us on this? Thanks
Wyn Hopkins
commented
Great to make it to page 1 of the ideas. Please encourage others to vote to move this into the top 10
Wyn Hopkins
commented
Good suggestion Corey, a MATCHIFS could be a good option. I would prefer the ability to select the lookup value first as creating the formula flows better and avoids that Sheet1! annoyance when you jump back to a sheet while creating a formula. However, I can see the logic in consistency with SUMIFS and COUNTIFS
Wyn Hopkins
commented
I would really like a simple and safe formula that does not require a match type. I.e exact match only. Speed is not an issue for 90% of users, simplicity is. I agree another formula similar to GetMatch with a match type would be useful but that's not what I'm proposing or voting for here
Wyn Hopkins
commented
While variants of formulas can serve experienced users brilliantly we still need basic safe and simple formulas for the general Excel user population.
That's who my suggestion is aimed at. Keep it simple with one formula with one purpose.
Simple to use, simple to review / auditWyn Hopkins
commented
Hi Harlan, I like the idea of a multiple criteria lookup function and I think there are a few suggestions for this elsewhere on the UserVoice.
I think both our approaches are the same just the syntax is re-arranged?
The simpler the better, the more options that are provided to users the more daunting most users find it and the more likely they end up with an incorrect formula.
My suggestion of having an optional value if no match again does away with the need for an IFERROR wrapper.
Wyn Hopkins
commented
Harlan, if you use index match then any column can be the key field. That's why I prefer that approach to VLOOKUP. This suggested formula would make that even simpler.
Wyn Hopkins
commented
Daniel, one extra thought is you could rename the columns as part of the query to make them more meaningful (either within MSQuery or using PowerQuery)
Wyn Hopkins
commented
Daniel, sounds like you're using a sensible approach for your situation
Wyn Hopkins
commented
Hi Daniel, that does make the formula easier to understand after the fact. I'd also fully recommend converting your source data to Tables wherever possible as INDEX MATCH and Tables are brilliant together giving you that meaningful formula and none of the overhead of having to set up named ranges or expand the ranges as more data is added.
Wyn Hopkins
commented
Scott, I agree that once people see it they prefer to use it, but as an Excel developer and trainer I regularly see first hand how most excel users struggle with it
I developed this trick to help them, but we shouldn't need a trick. https://www.linkedin.com/pulse/stop-using-vlookup-wyn-hopkins
Wyn Hopkins
commented
You're right Kenneth, I tried to delete the old one but wasn't able to. Having read various other suggrstions on VLOOKUP andI NDEX MATCH (a lot of which are very good suggestions for the more advanced Excel users) I think we need to step back and take a look at how a regular Excel user sees formulas and try and make as user friendly a one as possible, hence my slight revision to to my earlier terminology
Wyn Hopkins
shared this idea
·
Wyn Hopkins
commented
I was hoping for something simple that the vast majority of Excel Users could understand instantly.
Wyn Hopkins
commented
=Extract(LookupValue,LookupColumn/Row,OutputColumn/Row,optional Iferror Value)
-
10 votes2 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →Wyn Hopkins
commented
And on right-click in the Pivot Table itself would be awesome
Wyn Hopkins
supported this idea
·
-
220 votes29 comments · Excel for Windows (Desktop Application) » Performance · Flag idea as inappropriate… · Admin →
Good news! We have released a few fixes to Insiders to address this issue. If you are on Insiders, please let us know if the fix has worked for you.
The fixes should be in build 11809.10000 and onwards.
-Joe McDaid [Excel Team]
Wyn Hopkins
supported this idea
·
Wyn Hopkins
commented
For those of you experiencing this, my video here explains one of the causes and how to avoid it...
https://youtu.be/eNmfHsBIyIY -
118 votes23 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Wyn Hopkins
supported this idea
·
Wyn Hopkins
commented
Agreed
-
43 votes4 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →Wyn Hopkins
commented
Good idea
Wyn Hopkins
supported this idea
·
-
68 votes14 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →Wyn Hopkins
supported this idea
·
-
16 votes3 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →Wyn Hopkins
supported this idea
·
-
62 votes6 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →Wyn Hopkins
shared this idea
·
-
20 votes5 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →Wyn Hopkins
supported this idea
·
Wyn Hopkins
commented
Totally agree, these things may seem minor but when you use them a lot it really detracts from the efficiency of development
-
124 votes10 comments · Excel for Windows (Desktop Application) » Tables, Sorting and Filtering · Flag idea as inappropriate… · Admin →Wyn Hopkins
commented
Great tip Jon, it appears to work if the top cell is formatted differently in any way (background, font etc).
Wyn Hopkins
commented
Yes to the table name box and everything else Jan Karel said
Wyn Hopkins
shared this idea
·
-
159 votes7 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →
Thanks for logging this one, Marco, and for encouraging the community to vote.
I’m adding this to the list of Pivot table enhancements… more votes will boost the priority of getting it in the product, thanks!
ash
[MS XL]Wyn Hopkins
commented
I've now come across a client where this would be really useful
Wyn Hopkins
supported this idea
·
-
31 votes8 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →Wyn Hopkins
commented
Many keyboards have a right click key next to the space bar. Press that followed by v to paste as values.
Alternatively you can press Ctrl with V then Ctrl then V
-
2 votes0 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →Wyn Hopkins
shared this idea
·
Mynda Treacy's video demo's the issue nicely here
https://youtu.be/2ViMm-wuM3U?t=561