Susan Harkins helps a few readers find simple solutions to common Microsoft Office problems.
A solution is only easy if you know about it. This month, I helped one reader find a simple way to locate all of a Word document's section breaks. I also showed a second reader how to quickly select all of the text from the insertion point to the document's end. In addition, two readers had Excel questions. For the first user, a common Excel feature offered a quick solution. The second user needed a bit of guidance on using the COUNTIFS() function correctly; in this particular case, it meant not using COUNTIFS() at all. Let's take a closer look.
Search for objects in Word
Jim was looking for a quick method of finding section breaks in a long document (over 500 pages). Finding them all by sight would be tedious work. Fortunately, Word offers more than one search method.
If you're working with Word 2007 or 2010, you can use the Select Browse Object tool at the bottom of the vertical scroll bar. However, you might want to display your document's breaks before beginning your search -- an efficient method isn't helpful if you can't see the breaks! To display section breaks (and other formatting codes), click Show/Hide in the Paragraph group (on the Home tab).
With the breaks visible, you're ready to cycle through them using the Select Browse Object tool as follows:
- Find the tool -- it's a small circle with triangles above and below.
- Click the circle and choose the Browse by Section option (Figure A) -- or press [Ctrl]+[Alt]+[Home] to display the options.
- After selecting an appropriate object option, click either the Next or Previous arrow. The section option is the second from the right on the second row.
- Continue to click until you find the break (object) you're looking for.
This tool makes searching for several objects and formatting elements easy (Figure B):
You can easily search for several objects and formatting elements.
There are other ways to find objects, though -- choosing the right method depends on you and the situation. Sometimes, using the Find feature does the trick:
- Press [Ctrl]+[G] to display the Find dialog with the Go To tab current.
- Choose the object or element you're searching for in the Go to what list (Figure C).
- Click Next or Previous.
- Click Close when you're done.
The problem with this method is the dialog itself -- it obscures text. You can move it around, but depending on the job at hand, that might prove tedious.
The Select Browse Object isn't available in Word 2013, but you still use the Find dialog. In addition, you can use the Navigation pane. To display this pane, check the Navigation Pane option in the Show group on the View menu. Click the down arrow to the right of the Search icon to display the list of options shown in Figure D. You'll find most of the options here.
Use the Navigation pane to find objects.
Once you select an element from the Find section, Word displays the number of elements found. At this point, click the arrows shown in Figure E to move through the selected object types in the document.
Click the arrows to navigate through the selected object type.
Select from the insertion point to the end in a Word document
Bill was looking for a keyboard shortcut that would select everything from the insertion point to the end of the document. One thing Word users learn quickly is the many selection technique and keyboard shortcuts. Lucky for Bill, there's a shortcut that does exactly what he required. Simply press [Ctrl]+[Shift]+[End] to select all of the text from the insertion point to the end of the document.
Don't copy Excel conditional formats
Derek copied text from ranges where conditional formatting rules applied. When he copied such text, Excel also copied the conditional format, which he didn't want. Fortunately for Derek, leaving formatting rules behind only required an extra step or two:
- Select the conditionally formatted text and press [Ctrl]+[C] to copy the text to the Clipboard.
- Select the target cell and press [Ctrl]+[V]. When you do, Excel will display a tip icon (Figure F). You can also click the Paste drop-down in the Edit group to find the same option.
Excel will display a tip icon.
This choice removes other formatting as well, so it's a bit of a trade-off. To work more efficiently in the future, spend a little time familiarizing yourself with all of the available paste options.
Counting conditional values in the same range
Drew wanted to count blanks and text values that meant the same thing, such as "none" or "unknown" across multiple sheets. Trying COUNTIFS() didn't work. The good news is that it's simple, but sometimes the application is a bit tricky. The problem isn't sheet referencing. Rather, I suspect Drew's application of COUNTIFS() didn't work. Specifically, Drew was trying to count different values in the same range, as shown in Figure G. That won't work. The simple truth is, this function doesn't count different values or strings within the same range. (You can download the example .xls or .xlsx workbook.)
COUNTIFS() returns an unexpected result.
As you can see in Figure G, the COUNTIF() correctly counts the number of occurrences. When Drew tried to combine those counts in a COUNTIFS(), the function returned 0. COUNTIFS() criteria range argument can't be the same for each conditional value.
To count conditional values in the same range, you have two easy choices, and I've shown both in Figure H. You can add a helper COUNTIF() column and sum the results, or you can use a single formula that combines all of the COUNTIF() functions.
Add the results of multiple COUNTIF() functions.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at firstname.lastname@example.org.