Search Multiple Worksheets

BY STANLEY ZAROWIN

I usually work with a spreadsheet that contains as many as 24 worksheets. When I do a search using the Find command, I have to repeat the command for each worksheet. Is there a way to command Find to search all the worksheets in one swoop?

Yes, not only can Find search all the worksheets in a file, it also can search across multiple worksheets. However, for a worksheet to be available for a search, it has to be open (as indicated by its color, as shown below). In the example below, Sheets 1, 3 and 5 are open.

“Wait a minute,” you say, “I can’t get more than one sheet open at a time.”

Here’s where a little Microsoft “sleight of hand” comes in handy. If you want all the sheets in a file to be opened, highlight the tab at either end; then, while holding down the Shift key, click the tab at the other end. To open just selected sheets, hold down the Ctrl key and click on the tabs of the sheets you want open.

Once you’ve got the target sheets opened, click on Find . To find them one at a time, click on Find Next . To find them all in one swoop, click on Find All , as shown in the screenshot below.

SPONSORED REPORT

2019 State of Financial Reporting Survey

We surveyed nearly 600 finance and accounting professionals on their month-end close and reporting processes. See the results.

VIDEO

What RPA is and how it works

Robotic process automation is like an Excel macro that can work on multiple applications, says Danielle Supkis Cheek, CPA. RPA can complete routine, repetitive tasks such as data entry, freeing up employee time from lower-level chores.