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:

  1. thank you exactly what i was looking for!

    ReplyDelete
    Replies
    1. You're welcome - glad to hear it :)

      Delete
    2. Very helpful and straight to the point!

      Delete
    3. Thanks 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.

      Delete
    4. OMG i have been trying to find how to do this for months!! Thank you!

      Delete
    5. I'm glad the post will save you time and effort in future :)

      Delete
  2. Glad to hear people are still finding this, and finding it useful. Funnily enough I've used it a few times myself today.

    ReplyDelete
  3. Solute you for this simple and value-able time saving tip. <3)

    ReplyDelete

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).