Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Maintaining Text Formatting in a Lookup.
by Allen Wyatt
(last updated May 17, 2019)
When you enter text into a worksheet, you can format that text in either of two ways: You can format all the text in the cell, or you can format just some of the text in the cell. Formatting all the text should be familiar to all readers, as it is quite common to make the contents of a cell bold or change its font.
Formatting some of the text within a cell may not be done as often, but it is just as easy to do as formatting the entire cell. All you need to do is, while editing the cell contents, select the text you want to format and then apply the desired formatting properties. For instance, you could make some portion of the text bold or some of it underlined. You can also change the font used by certain characters in a cell, which is often done to insert symbols in a cell or to use foreign-language characters within your regular text.
While formatting can make your information more understandable, and may in fact be required for your particular worksheet, there are some drawbacks. One of the biggest drawbacks is when you use formulaic references to the formatted text. Excel will only copy the bare contents of the referenced cell, not the formatting applied to text within that cell.
As an example, suppose you have a cell that contains Greek letters interspersed within the text of a cell, and you reference that cell in a formula such as the following:
Excel dutifully copies the contents of the cell, returning as the results of the formula, but it does not copy the formatting of the cell being referenced. This behavior is not limited to lookup functions, either; Excel behaves this way even with simple formulas, such as =B7.
There is no way around this drawback, short of writing a rather complicated macro that copies formats as well as content. Macros that copy cell formatting have been covered in past issues of ExcelTips; macros that copy individual character formatting within a cell are complex enough that they are beyond the scope of ExcelTips itself.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2318) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Maintaining Text Formatting in a Lookup.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When editing information in a cell, you may need to know the result of a portion of your formula. The shortcut described ...Discover More
If you need to switch between viewing formulas and viewing the results of those formulas, you'll love the keyboard ...Discover More
Given a range of cells, you may at some time want to calculate the sum of only the largest values in that range. Here is ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.