Helping readers is one of my favorite tech-related jobs. It’s just plain fun to hear back from readers who are surprised–and often excited–to learn that a huge problem is easily solved. This month, Bogdan wants to add formatting to a single character in a specific word in a Word document, and Judy wants to highlight duplicate records in an Excel data set. Both sound like daunting tasks, but both situations have an easy-to-implement solution.

I’m using Office 2016 (desktop) on a Windows 10 64-bit system, but both solutions are compatible with earlier versions. You can download the sample .xlsx or .xls demonstration file, but you won’t need a demonstration file for the Word tip. Word 365 doesn’t support wildcards in a Replace task, so that solution won’t work in the browser edition. Excel 365 supports existing conditional formatting, so the solution will work in the browser, but you won’t be able to implement the rule in the browser.

Word: change formatting of a specific character

Bogdan wants to change the formatting of a single character in a specific word. That last part’s the clencher because changing the formatting of a single character, a single word, or even a phrase is easy using the Find and Replace feature. Changing the formatting when that character occurs only in a specific word seems much harder–but it really isn’t. The key is to change the entire word, not just the single character.

Now, let’s consider a simple example: let’s suppose you want to italicize the c in abc, but you only want to italicize the character c when it occurs in the string abc. Your document might contain dozens of cs, but you won’t want to italicize them all.

You might think wildcards are the way to go–separate the string into two components using ()s and then replace the first component with itself… let’s see that’s (ab)(c) in the Find What control and \1 and what else in the Replace With control? That route might be possible, but there’s a simpler way that won’t require testing and failure before you figure out how to make it work.

SEE: 10 all-purpose keyboard shortcuts to boost your Word efficiency (free PDF) (TechRepublic)

Copy the formatted string to the Clipboard, and then use Find and Replace to replace abc with abc. Let’s illustrate this technique on the simple sentence shown in Figure A:

  1. Enter abc somewhere in the document and italicize the c.
  2. Select the partially formatted string. Be careful not to select any spaces before or after the string; select only abc.
  3. Copy the formatted version to the Clipboard by selecting Ctrl+c
  4. Press Ctrl+h to open the Find and Replace dialog.
  5. In the Find what control, enter abc.
  6. In the Replace with control, enter ^c.
  7. Press Replace All and click OK to confirm the replacement task. (Figure B).

Figure A

Let’s italicize the c in abc using Find and Replace.

Figure B

The ^c code replaces the matching strings with contents from the Clipboard.

We didn’t italicize the c in all instances of abc; we replaced the entire string with the appropriately formatted string. Word found two occurrences because the formatted text that you copied to the Clipboard is still in the document. At this point, you can delete that string. In a simple document, this doesn’t seem important, but if abc occurred many times in a document, this simple Find and Replace task would save you a lot of time.

Excel: prevent duplicates in Excel

Finding, preventing, and deleting duplicates in an Excel data set is a huge topic. You can read about a couple of solutions in the following articles:

Judy enters study data, such as subject name and study identification. Some subjects can participate in more than one study, so repeating names and study identification values is acceptable. However, she doesn’t want to enter the same subject in the same study more than once–that constitutes a duplicate record.

Although neither article is a direct solution for Judy, she can combine the two solutions to get the results she needs. First, we’ll concatenate the fields that comprise the duplicate. Then, we’ll build a conditional format that counts the concatenated values. Later, Judy can hide the concatenated values, or not. Generally, I don’t recommend hiding anything because out-of-sight, out-of-mind, but in this case, it seems harmless enough, especially if you document your work.

SEE: Tap into the power of data validation in Excel (free PDF) (TechRepublic)

Figure C shows a simple data set with no duplicate records, yet. It’s a Table object, but you don’t need a Table for this technique to work. However, a Table extends conditional formatting to new rows, so I recommend you use a Table object for data entry tasks.

Figure C

We’ll use a simple expression and a conditional formatting rule to expose duplicate records.

The first step is to concatenate the values that comprise the duplicate. In this case, it’s all three fields, but that won’t always be the case. The expression in column E concatenates all three fields:

=B3&C3&D3

Next, we need a formula that counts the number of times a value in column E repeats–we’ll use a COUNTIF() function in the following form:

=COUNTIF(fixedvalues,movingvalue)>1

In a nutshell, fixedvalues references the concatenated values as an absolute reference and movingvalue is a relational reference to the first cell in the same column. When COUNTIF() encounters a repeat of movingvalue within fixedvalues, the function returns a value greater than 1, so the expression returns TRUE and enables the conditional format.

Now let’s put all the pieces together as follows:

  1. Select the data you want to highlight; in this case, select B3:D7.
  2. On the Home tab, click Conditional Formatting in the Styles group and choose New Rule from the dropdown list.
  3. In the resulting dialog, select the Use a formula to determine which cells to format option.
  4. In the formula field, enter the formula
    =COUNTIF($E$3:$E$7,$E3)>1
  5. Click the Format tab.
  6. Click the Fill tab, choose a color, and click OK. Figure D shows the formula and the selected format.
  7. Click OK.

Figure D

The formula and format.

Because the data set has no duplicate records (as defined by Judy’s business rule), the conditional format does nothing. Let’s add a duplicate record and see what happens! The conditional format won’t kick in until you enter all the duplicate-rule values; as soon as you enter the last value, the conditional format highlights both the original record and the new duplicate, as shown in Figure E. The Table object’s extension behavior automatically updates the conditional formatting rule’s references to include the new row.

Figure E

The conditional format highlights the duplicate records.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. 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. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

See also…