Microsoft Excel Tutorial.
When you go to File, Open, there is a Search box at the top. This search box often surprises me by offering files that are years old. I always wondered how it is working. My friends on the Excel team pointed me to Theo Lorrain-Hale, a Project Manager who worked on the search box.
In this episode, I share tips for where the search box is actually searching.
When you go to File, Open, there is a Search box at the top. This search box often surprises me by offering files that are years old. I always wondered how it is working. My friends on the Excel team pointed me to Theo Lorrain-Hale, a Project Manager who worked on the search box.
In this episode, I share tips for where the search box is actually searching.
Transcript of the video:
I did some digging and I figured out a few cool tricks about the search box that you get when you go to File, Open.
I'm talking about this thing when you go to File, Open and it goes back a few years.
There's a blog post from 2018 that talks about it, but it doesn't really give a lot of details. The reason I've been intrigued by this is I usually start here and sometimes, I'm just amazed.
It finds things that aren't in the Recent Files.
Sometimes it finds things that are more than a year old, and I always wondered, how?
Where is that looking?
So I went to the Excel team and they eventually found Theo Lorrain-Hale, a Microsoft PM who actually built the feature, and Theo was nice enough to give me some tips on how it's working.
All right, so there's three different sources of files that Excel uses.
First, the Recommended Files, Most Recently Used, and then Shared with Me.
So they take that list and build it into a superset of files in any of those three places.
Then, whatever you search for, they parse into words, and his interesting example is if the search term is Mythesis#10, the hash sign there is enough to make it be four words, "my" "thesis", the hash sign, and 10.
They then filter the list of these to be the things that have all of the words from number two, all right?
So when we take a look at the sources, Most Recently Used, we see that all the time.
That's the big list in File, Open, but Recommended Files, that's not something we see all the time.
You have to go to File and then Home and this list here, Recommended for You.
So it's things that I might have edited recently or other people have shared with me or someone created a task for me, so Recommended for You.
Then Shared with Me is down here, see.
This is where, because I don't do a lot of collaborating, so it's rare that I'm sharing a workbook, this is where those old files are coming.
Check this out.
Here's a file, CELL ALIGNMENT from 7/29/2020, even a file from 2019, 2018, and this is just because I'm not collaborating a lot.
I have some really old files in Shared with Me.
So yesterday, I created a bunch of files that, in the file name, it has either the word kayak or thesis or sometimes kayak, and thesis.
Let's just see how it's working.
We go to File Open.
If I would just search for Kayak, I'm getting 1, 2, 3, 4, 5 choices, and let's change this to thesis, all right?
So they find six files.
Interesting that like here in CamelCase, ThesisKayaking, but even if it's all just lowercase and thesis is somewhere in the middle, they're finding it.
They're doing a partial search.
If it appears anywhere in the file name, it shows up.
So we have 1, 2, 3, 6 of those.
Then let's search for a thesis kayak, right?
Now we're going to get just the two that appear in both lists.
ThesisKayaking and Thesis_On_Kayaking manages to come back.
The one interesting thing here is if you know it was about kayaking or canoeing and you're not sure which, if you would put it in both kayak and canoe, canoe is going to eliminate all of the kayak choices.
So it has to match everything that's up there.
So in my assessment of what's happening here, it's because I don't collaborate enough that those Shared with Me files are bringing old results, which is great, but sometimes, it's not in any of those three lists.
So I'm going to come here to Browse and up here I'm going to use that search box and search for kayak like that, and you see that we have Thesis_On_Kayaking, but we also have ToDo workbook that must have kayak somewhere in there.
KayakRackPlans was one that didn't come back in the other list.
Good_Titles_And_Words, it's strange that that has kayak somewhere in it.
This CSV file has Kayak in it.
So it's interesting that this search box is definitely different and more comprehensive than the other search box.
The other interesting thing that Theo pointed out and that he was happy about is that for files that are stored locally on our hard drive, all of this algorithm happens locally here on the hard drive.
None of it's sent to a server.
None of it is a Microsoft 365 search thing.
So for people who are worried about privacy or government agencies, you can safely use this File, Open, Search box, and nothing is sent up to the server.
It's all handled locally.
All right, so there you go.
It's something that I've been wondering about for a while.
We just had the MVP Summit this week, so three days of hearing from the Excel team, and as you're sitting there listening to all these great things that are coming, it's like, "I never documented what I found out about the Search box".
So many thanks to Theo Lorrain-Hale for taking his time to explain how the search box works.
It might help you the next time you're using it.
All right, well, hey, thanks for stopping by.
We'll see you next time for another Netcast from MrExcel.
If you like these videos, please down below, like, subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below.
I'm talking about this thing when you go to File, Open and it goes back a few years.
There's a blog post from 2018 that talks about it, but it doesn't really give a lot of details. The reason I've been intrigued by this is I usually start here and sometimes, I'm just amazed.
It finds things that aren't in the Recent Files.
Sometimes it finds things that are more than a year old, and I always wondered, how?
Where is that looking?
So I went to the Excel team and they eventually found Theo Lorrain-Hale, a Microsoft PM who actually built the feature, and Theo was nice enough to give me some tips on how it's working.
All right, so there's three different sources of files that Excel uses.
First, the Recommended Files, Most Recently Used, and then Shared with Me.
So they take that list and build it into a superset of files in any of those three places.
Then, whatever you search for, they parse into words, and his interesting example is if the search term is Mythesis#10, the hash sign there is enough to make it be four words, "my" "thesis", the hash sign, and 10.
They then filter the list of these to be the things that have all of the words from number two, all right?
So when we take a look at the sources, Most Recently Used, we see that all the time.
That's the big list in File, Open, but Recommended Files, that's not something we see all the time.
You have to go to File and then Home and this list here, Recommended for You.
So it's things that I might have edited recently or other people have shared with me or someone created a task for me, so Recommended for You.
Then Shared with Me is down here, see.
This is where, because I don't do a lot of collaborating, so it's rare that I'm sharing a workbook, this is where those old files are coming.
Check this out.
Here's a file, CELL ALIGNMENT from 7/29/2020, even a file from 2019, 2018, and this is just because I'm not collaborating a lot.
I have some really old files in Shared with Me.
So yesterday, I created a bunch of files that, in the file name, it has either the word kayak or thesis or sometimes kayak, and thesis.
Let's just see how it's working.
We go to File Open.
If I would just search for Kayak, I'm getting 1, 2, 3, 4, 5 choices, and let's change this to thesis, all right?
So they find six files.
Interesting that like here in CamelCase, ThesisKayaking, but even if it's all just lowercase and thesis is somewhere in the middle, they're finding it.
They're doing a partial search.
If it appears anywhere in the file name, it shows up.
So we have 1, 2, 3, 6 of those.
Then let's search for a thesis kayak, right?
Now we're going to get just the two that appear in both lists.
ThesisKayaking and Thesis_On_Kayaking manages to come back.
The one interesting thing here is if you know it was about kayaking or canoeing and you're not sure which, if you would put it in both kayak and canoe, canoe is going to eliminate all of the kayak choices.
So it has to match everything that's up there.
So in my assessment of what's happening here, it's because I don't collaborate enough that those Shared with Me files are bringing old results, which is great, but sometimes, it's not in any of those three lists.
So I'm going to come here to Browse and up here I'm going to use that search box and search for kayak like that, and you see that we have Thesis_On_Kayaking, but we also have ToDo workbook that must have kayak somewhere in there.
KayakRackPlans was one that didn't come back in the other list.
Good_Titles_And_Words, it's strange that that has kayak somewhere in it.
This CSV file has Kayak in it.
So it's interesting that this search box is definitely different and more comprehensive than the other search box.
The other interesting thing that Theo pointed out and that he was happy about is that for files that are stored locally on our hard drive, all of this algorithm happens locally here on the hard drive.
None of it's sent to a server.
None of it is a Microsoft 365 search thing.
So for people who are worried about privacy or government agencies, you can safely use this File, Open, Search box, and nothing is sent up to the server.
It's all handled locally.
All right, so there you go.
It's something that I've been wondering about for a while.
We just had the MVP Summit this week, so three days of hearing from the Excel team, and as you're sitting there listening to all these great things that are coming, it's like, "I never documented what I found out about the Search box".
So many thanks to Theo Lorrain-Hale for taking his time to explain how the search box works.
It might help you the next time you're using it.
All right, well, hey, thanks for stopping by.
We'll see you next time for another Netcast from MrExcel.
If you like these videos, please down below, like, subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below.
