Pages
▼
Tuesday, 12 November 2013
How to search across all tabs in an Excel workbook spreadsheet
Tweet the information in this post.
1. Excel sheets
2. Google sheets
1. Excel sheets
Now that I know how to do this I can't believe the time I've spent repeating the process individually, searching within each tab. Shameful.
Thanks to @Richard_Black and @marklardner for explaining this.
When you've got your spreadsheet open and want to look for the existence of a search-string (I was looking for Computer Science or Computing, so used comput as that would find both) do something a bit like the following.
PC users - Ctrl+F brings up the Find menu
Mac users - Command+F brings up the Find menu
1. Ctrl+F or Command+F to bring up the Find menu
It looks like this on my version
2. Click on the Options button and it will look like this- where it says 'Within: Sheet' (the default setting), change this to Workbook. Then you can bounce through the tabs pressing enter (identical to clicking 'Find Next') as you go.
2. Google sheets
Not that different, see picture series below.
Wait for the sheet to load, if you don't and press Ctrl+F you're searching 'in browser' and you want to be searching 'in sheet(s)' so have patience.
Once loaded press Ctrl+F and you'll see a dialog box [1] appear near the top right of your page (if you see one appear bottom left of your browser window [1a] refresh the page and wait patiently, that's the wrong one). Click on the three vertical dots to bring up the options and choose from the options.
Type in your word or phrase of interest and select the 'This sheet' option [2], to adjust it...
...to 'All sheets' [3] so that you search across the whole document, not just the current tab. Once you've entered a search term the 'Find' link will become active, keep clicking it to bounce through the results.
When it's found everything it can look out for this notice, in [4].
Edit: 30 July 2017
Someone from Portugal was super keen for me to tell you about VLOOKUP. I've no idea what it is (no need either so please don't tell me) but here is Microsoft's own page about it - they wrote the software so I assume they know what they're talking about.
13 comments:
Comment policy: I enthusiastically welcome corrections and I entertain polite disagreement ;) Because of the nature of this blog it attracts a LOT - 5 a day at the moment - of spam comments (I write about spam practices,misleading marketing and unevidenced quackery) and so I'm more likely to post a pasted version of your comment, removing any hyperlinks.
Comments written in ALL CAPS LOCK will be deleted and I won't publish any pro-homeopathy comments, that ship has sailed I'm afraid (it's nonsense).
thank you exactly what i was looking for!
ReplyDeleteYou're welcome - glad to hear it :)
DeleteVery helpful and straight to the point!
DeleteThanks very much, glad you liked it. I was delighted to find how straightforward it is and use the facility several times a week, almost every time I have a spreadsheet open.
DeleteOMG i have been trying to find how to do this for months!! Thank you!
DeleteI'm glad the post will save you time and effort in future :)
DeleteThanks a lot
ReplyDeleteThanks a lot.. really helped!!
ReplyDeleteThanks a lot..
ReplyDeleteSaved my day!!!! Thanks!!!
ReplyDeleteYou saved my day!!! Thanks!
ReplyDeleteGlad to hear people are still finding this, and finding it useful. Funnily enough I've used it a few times myself today.
ReplyDeleteSolute you for this simple and value-able time saving tip. <3)
ReplyDelete