New features you'd like to see in Excel

Day Weekday
2/27/1900 2
2/28/1900 3
2/29/1900 4
3/1/1900 5
3/2/1900 6

Looks like January and February 1900 are just wrong.

1 Like

Thanks for satisfying my curiosity.

All the more reason to fix it.

1 Like

I was hoping it would throw an N/A error.

2 Likes

Does xlookup work across linked files?

I keep forgetting that I shouldn’t use INDEX(MATCH()) to look up data in another file. I’m sure that folks reviewing my files get tired of the notes I end up adding to my file “open this other file, or else you will get errors”.

i would name the array if using it in other workbooks

i never tried looking up or indexing from an external workbook, but wouldn’t it be easier to link the external data on a separate tab and do your lookup there?

Generally, when I’m not pressed for time and am exercising proper foresight / building files for future re-use, I do try to do this.

However, it’s not uncommon for me to be working on something ad-hoc, realize that the spreadsheet is getting too big, and pull a tab out into its own file. Or I’ll have multiple spreadsheet windows open, and not be aware that I’m working with different files as opposed to different windows of the same file.

But the scenario that really gets me is the collection of spreadsheets that I use in maintaining the economic capital model I work on. I have one file that contains a several tables that map the relationships of products, indices, certain variables…and over time those tables sometimes gain extra columns. These tables are referenced by multiple other files. Mirroring these tables in those other files would be a pain.

I go through Index Match phases but if I haven’t used it for a while I forget the syntax. If it’s just a quick thing, I’ll look around to make sure no actuaries are watching and then do a vlookup (has to be a small dataset).

2 Likes

One thing that annoys me is that if I do a Find, the default is to do it by rows. I change it to columns but every time I shut down and start again it’s back to rows. Who searches by rows?

Also, I would love to have feature that automatically froze the top row when I opened a new file. I don’t know if I would ever not want that to be the default (easy enough to change but 90% of my spreadsheets benefit from a frozen top row).

1 Like

:rofl:

MaxIF and MinIF. I was so excited when they added AverageIF but they not add the other two I need

2 Likes

I just wish they would stop turning things into dates. Just stop it.

5 Likes

I’d like to be able to swap the selected items in filters rather than having to select all and then start again.

Why on earth would you want to work across linked files? Oh, I see by your name you are working in hell.

there seems to be no option for turning this off, like the option to turn off auto-correct

1 Like

My Excel new version defaults all new files, all files I haven’t opened in the new version, and all saved-as new-named files as Auto-Save.
I want that defaulted to NOT Auto-Save.

Yes, I know. That’s why it’s so rage-inducing.

1 Like

Actually, my name should indicate that I work in the lab of a mad geneticist outside South Park, Colorado. :slight_smile:

(Just call me Kevin.)

(Yes, I made a typo over 20 years ago, when I registered on AO.)

Po-tay-to, po-tah-to, Kevin.

For whatever reason I didn’t think I had xlookup. Then this thread made me want to go look, and I do have it! Now I need to go use it and see if I agree that it’s better than index/match, which is what I’ve been using exclusively for the past few years.