Office challenge: Where'd my Data Validation lists go?

Test your troubleshooting skills in Excel and learn the answer to last week's Word formatting challenge.

A few days ago, a client called me in a panic -- a sheet had lost its Data Validation drop-down arrows. That meant the lists were gone too! They were easy to reclaim, fortunately. If these drop-down lists suddenly disappeared from one of your sheets, what would you do? Last week we asked… How would you change all the highlighted text in a Word document to another color? Several of you responded with the Find And Replace feature, which works as follows:

  1. Using the Highlight tool on the Formatting toolbar, choose the new color. In Word 2007, you'll find Highlight in the Font group on the Home tab.
  2. Choose Replace from the Edit menu or press [Ctrl]+H. In Word 2007, the Editing group is on the Home tab.
  3. Click inside the Find What box.
  4. Choose Highlight from the Format drop-down list. (Click More if the Format button isn't visible.)
  5. Click inside the Replace With box.
  6. Choose Highlight from the Format list again.
  7. Click Replace All.
Carlsburger was the first to respond -- —thank you! Vijaykumardave and Caslon1 also responded with the same solution. Anita.Spry and Eancrum offered a different, but similarly easy solution:

  1. Choose Find from the Edit toolbar.
  2. Choose Highlight from the Format drop-down list.
  3. Check the Highlight All Items Found In option.
  4. Click Find All.
  5. Change the Highlight!
I admit, I didn't think of this route, but it's certainly useful, especially if you just want to view the highlighted (or formatted) text rather than or before you change it. Dsjones offered another great solution that works for text formatted with the same style:

  1. Double-click highlighted text.
  2. Choose Styles And Formatting from the Format menu.
  3. Click Select All.
  4. Apply the new highlight.
Mark Dobie offered some pretty sophisticated code for changing colors. I haven't tried it yet, but it looks interesting. Thanks everyone!


Here's an easy highlight trick for removing all highlighting at once: Select the entire document by pressing [Ctrl]+A. Then, choose None from the Highlight drop-down list.