SOLVED

How do I have Excel treat each file separately - for search, undo, etc.

Brass Contributor

Excel treats all files I have open as one. By that I mean there is one search box that works across all files open. If I want to search for different things in different files, I have to keep changing what I am searching. Same with undo. It undoes across files, which can be very annoying. How do I get it to treat each file individually? I would think this would be the default or that there would be a switch but I can't find one. 

16 Replies

@djc2002us 

 

If you click on the "Search" icon (the magnifying glass) and then on the little arrow in the box itself, it gives this dialog box, with the choice of search in sheet OR search in workbook.

 

There's nothing about searching in all open workbooks (i.e., in all open files)...

 

mathetes_0-1596473332311.png

 

And, frankly, it just doesn't make sense that it wouldn't be operating as you're suggesting it should. So I'm wondering whether you're doing something else, doing your search and undo commands at a system level, somehow.......

 

Could you describe a bit more how you're going about these tasks?

@djc2002us 

Search and select
This option is located on the top right of the Start menu

In the options you can also select the worksheet.

 

Find and select cells that meet specific conditions

https://support.microsoft.com/en-gb/office/find-and-select-cells-that-meet-specific-conditions-2d686...

 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

Only now do I see that it is 365 ... believe that mathetes answer is more suitable.
Reading through doesn't hurt either :)
Anyway, I wish you a lot of fun and a pleasant day.

Nikolino

Let me clarify. I have (for simplicity) opened two files. I perform a search in the first file. I then click on the

other file and the same search box (and search term) appears. It fact, when I flip from one file to the other, the search box stays in focus and is on top of the file I select. But, the files (and tabs within each file) are unrelated so the search box should be unique for each file not shared between them (and all other files). When I use Word, the search window is unique for each file, but not in Excel. How do I make it unique for each file?

@mathetes 

@djc2002us 

 

OK, I think I see what you're referring to. It's NOT the little magnifying glass "search" tool. What you're referring to is the Edit....Find.... tool. And, yes, it does come up with the same term if I open it first in one spreadsheet and then another. My guess as to the "Why" is that this is a capability that operates at the more abstract "Excel as a whole" level; it's NOT spreadsheet specific, whereas the search window is.

 

(In fact, I can see why some users might find it convenient---since Find & Replace also operates the same way, if the occasion were to arise where every occasion of Mary had to be changed to Merry, it would be useful not to be required to enter those instructions repeatedly.)

 

mathetes_0-1596490014793.png

If it doesn't work that way in Word, I can see why the inconsistency would raise the question in your mind. But I certainly hope it's not something that is keeping you awake at night. If it is, look around for the following connection and submit a suggestion.

mathetes_1-1596490299148.png

 

Thanks, I will make a suggestion but it is very annoying and I wonder why no one else has complained. While it might make some sense to replace all across files, although I have never tried it. Even worse is when you undo and it does it across files. That is very bad. 

 

@mathetes 

@djc2002us 

Here a small solution with VBA is a bit more cumbersome, but it does its job. Maybe it will help you as a preliminary solution.

 

Best regards,
Nikolino
I know I don't know anything (Socrates)

 

@djc2002us 

 

You wrote: Thanks, I will make a suggestion but it is very annoying and I wonder why no one else has complained. 

 

From the way you have described your situation, it very much sounds as if you may be doing something that is in itself quite unusual. I am a relatively heavy user of Excel, and I do use Find on occasion, Find and Replace more frequently but still only occasionally. I use Undo on occasion as well, but only to undo one or two operations.

 

Illustrating the fact that there are often multiple ways within Excel to accomplish any given task, @NikolinoDE  has supplied you with a possible VBA solution for the Find/Search activity. He himself acknowledges, in an understated kind of way, that it might be "a bit more cumbersome."

 

I've already pointed you to the actual Search function, accessed via the magnifying glass icon in the upper right hand corner of your Excel window.

 

But would you be willing to describe what it is you're searching for when you do these searches. I ask because I wonder if there might be yet another way to accomplish the task. I wonder, for example, if you're generally searching for text or formulas or functions, or numbers? Are you working in Excel files that consist of lots of numerical data, is it predominately text, an even mix? Etc.

 

No obligation on your part to divulge anything, but seriously, thin about the possibility that you are doing somethings that most users of Excel don't do; that could be why others haven't complained about it. And also, there may be other ways to accomplish your objectives....

 

@djc2002us  Oh it gets better!!!  Here is a bug I reported a while ago and still exists.

Open 2 workbooks

in workbook 1 do a search for something in it (anything that exists or type something and then use ctrl-f to find it)

then click directly into the formula bar of workbook 2 

type something in and press enter

and watch it go into workbook 1   

It is a little confusing to follow but do the above steps exactly and you will see how bizarre it is.

Basically I was working in a workbook needing to make updates from another workbook.  I would search and find the cell/information I needed and when I would go to type the new info into the new workbook it was going into the wrong workbook.  This seems like a pretty normal thing I can't believe more people haven't had this issue.  In any case, discovered and reported this bug years ago and still hasn't been fixed.

I am not sure the VBA helps in this situation. I basically have two totally different files made up of a mix of text and numbers (almost no formulas as they are really just relational databases in spreadsheet form). But, I need to find text and numbers in each but even though they are separate files, Microsoft Excel in its infinite wisdom says that if you click on Find and Replace in either spreadsheet, you are doing it for every spreadsheet you have open (ditto for undo). So, every time I move between spreadsheets the last thing I searched for is what I searched in the other spreadsheet. And, when I want to undo it does it across spreadsheets as though they were one even though they are separate files! I guess I could try to put these into an Access database but I am less familiar with that than Excel and it seems like overkill for my applications. 

 

@mathetes 

@djc2002us  I'm sorry but I'm going to repeat what @mathetes  said in a different way, what are you trying to do?  I understand your frustration with the shared Find and shared Undo but not sure how exactly that is causing problems with your workflow/how you are using Excel.  My only guess is that you are searching for all cases of term A in workbook A and term B in workbook B and each time you find an A you need to find the next/corresponding B.  My point is if you give us those details we might be able to actually help you.  There are pivot tables and power queries that might help (you mentioned you are using it like a database), or VBA.

Well, as I have said, the files are totally independent. Sometimes I search in one file and sometimes in the other but for totally different things. In one I search for a name and the next a number. 

1. Search for Maria in file 1.

2. Search for 1900 in file 2.

3. Search for the next Maria in file 1. Oh, but I can't as the search field is now 1900 so I have to retype in Maria.

Clearer? Why is the search across files and not file by file, at least as an option? Excel seems to assume the files must be related to one another but they don't need to be. Sometimes people multi-task, hard to believe. 

@mtarler 

@djc2002us 

 

And both @mtarler and I are trying to get underneath (or behind) the specific steps you're doing here--we get those--to what is the larger context. You've described that these are separate spreadsheets, but you've mentioned an intriguing fact-- I basically have two totally different files made up of a mix of text and numbers (almost no formulas as they are really just relational databases in spreadsheet form) -- which is intriguing on a number of bases. To have created a relational database, in and of itself, reveals you as definitely not a neophyte.

 

It also would sound as if (given the reference to 1900 as a number you might search for) that you might be involved in some form of research in historical data. That's purely speculative on my part. My main point is that it really does sound as if you are using Excel in ways that are not typical. There's absolutely nothing wrong with that; it is capable of handling this kind of thing. But it does seem to be the case that you are doing far more searching than most users of Excel engage in.

 

So multi-tasking isn't the issue. It's the nature of the tasks themselves that seems to lead to your frustration.

 

Now, if it's really a relational database in spreadsheet form, and if it's the two files that together constitute that relational database, then it leads me to wonder--still not knowing the full nature of the task (i.e., the larger context, the content or nature of the data)--whether Access mightn't be a better solution.

Or Evernote

 

But anyway, all that we're suggesting is that your task itself is the thing (or appears to be, to be more tentative here) that's creating what is for you a fairly unusual--dare I say "unique"--frustration. And if you would be willing to describe that bigger context, it's possible we could either suggest ways that Excel could accommodate it as is, or suggest alternative software solutions.

best response confirmed by djc2002us (Brass Contributor)
Solution

I thought I had wrapped this up by saying that the option to start independent instances of Excel by using the /X startup option is my solution. This makes each file independent with separate undo and find/replace boxes. But, I did not see that this morning when MS asked me to indicate the best solution. @mathetes 

@djc2002us  That might be because they either don't allow or discourage marking your own response to a thread you started as the best solution.  That said your point about using the /X option is a good one.  Here is a "step more".  Either create a new shortcut or modify the shortcut you already use to launch Excel (for example the short cut used by the start menu) to include that "/X" option.  (Note you may need administrator permission). To create a new shortcut either copy and existing shortcut or right click and drag the application icon to the location you want it and select 'Create shortcut'.  I then recommend you give it a new name like "Excel - New Window" to distinguish it from the original shortcut.

Then to modify the shortcut:

Right click the shortcut and click 'Properties'. 

Then in the field that says "Target" add the /X after the end of the existing ""

It should look something like this but your path might be different: "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" /X

Finally move, add or copy that shortcut to where ever you want to easily open Excel in a new window.

 

1 best response

Accepted Solutions
best response confirmed by djc2002us (Brass Contributor)
Solution

I thought I had wrapped this up by saying that the option to start independent instances of Excel by using the /X startup option is my solution. This makes each file independent with separate undo and find/replace boxes. But, I did not see that this morning when MS asked me to indicate the best solution. @mathetes 

View solution in original post