MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Replace Formatting

October 20, 2017 - by Bill Jelen

Replace Formatting

In Excel, you can mix formatting in a cell. Some words can be bold and some can be red. But if you do a Find & Replace, the formatting will be lost. Here is one way to deal with that.

Technique 6

Change Formatting of Words Within Excel

If you have sentences of text in Excel, it is possible to select one word while in Edit mode and change the color of that word. But globally change the color of all occurrences of the word in Excel is tedious. Instead, paste the data to Word and press Ctrl + H. Change dog to dog. Click More>> and then Format, Font. Choose Red. Click Replace All. Copy from Word and paste back to Excel.

Replace Formatting
Replace Formatting
The Result
The Result

Watch Video

  • Changing all occurences of "dog" in Excel to red and bold.
  • Today's trick is from Katie Sullivan on the Word team
  • There are a few cases where Microsoft Word can do things better than Excel
  • You want to change all occurences of the word dog to red & bold
  • Copy from Excel.
  • Paste Special to Word as Values
  • Put the insertion point before the pasted text
  • Ctrl + H for Find & Replace
  • Click More... to get to the format button
  • While on the Replace With, click Format, Font, Red, Bold
  • Replace All
  • Copy from Word
  • Paste to Excel

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2053 bold red dog hey welcome
  • back to MrExcel NetCast I am
  • podcasting this entire book click the
  • eye on the top right hand corner to get
  • to the playlist alright our goal today
  • is to change every occurrence of the
  • word dog to be bold and red oh this is
  • going to be horrible in regular Excel we
  • would have to double click the cell
  • select those characters and then read
  • and bold like that would take forever so
  • this is another one of those things
  • thanks to katie sullivan and the word
  • team paste a word next I want to paste
  • as text so there we go ctrl H replace
  • dog with dog but in this one the second
  • one we're going to click more and in
  • more while the pointers here go into
  • format so format fonts change the font
  • color to red change the style to bold
  • click OK and then we click replace all
  • perfect alright so everything is now
  • fixed copy the data from word ctrl c go
  • back to excel paste in place and we have
  • done the impossible this tip and a lot
  • more tips are in this book click the eye
  • on the top right hand corner to get to
  • that book again our goal here is to
  • replace the formatting of one particular
  • word using find and replace Excel can't
  • do that Microsoft Word can so copy from
  • Excel paste special to word as values
  • ctrl H click the more button to get to
  • the format and then while you're in that
  • replace with click format font red bold
  • replace all copy from word paste back to
  • excel and you're done hey thanks for
  • stopping by we'll see you next time for
  • another NetCast from MrExcel

Download File

Download the sample file here: Podcast2053.xlsm

Title Photo: Mr Incognito / Pixabay

Bill Jelen is the author / co-author of
Microsoft Excel 2019 VBA and Macros

Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful.