Topic: Cell Formatting

The following articles are available. Click the article's title (shown in bold) to see the associated article.

Accurate Font Sizes Excel allows you to quickly select different font sizes in a workbook. This is most flexibly done by using the Font tab of the Format Cells dialog box. As this tip explains, you can set virtually any font size desired, in half-point increments. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Adding a Custom Format to those Offered by Excel Custom formats in Excel provide a way for you to specify exactly how you want data to look in a cell. You can define custom formats in a workbook, and Excel remembers what they are so you can use them again. Using them in other workbooks is a bit trickier, as you find out in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Adjusting Cell Margins for More White Space There are multiple ways that you can adjust the margins within a cell to allow more white space. This tip examines the most common methods you can try. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Adjusting Row Height for Your Text If you have a lot of text in a cell or group of cells, you can have Excel automatically wrap the text within the cell and adjust the row height. This is a great way to make sure that all your text is visible. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Adjusting Row Height when Wrapping Text If you have some cells merged in a worksheet, and you wrap text within that merged cell, Excel won’t automatically resize the height of the row as it would with unmerged cells. This tip describes how you can work around this limitation. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Automatically Breaking Text Putting each line of a text string on its own line is easy if you know the trick. This tip explains how you can “break” text so that each word appears on its own line within a cell. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Better Use of Heading Space If your column headings are too large to work well in your worksheet, try this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Cannot Format Cells There are some known problems importing a file from Access 2002 into Excel 2000 and Excel 97 that will not allow a user to format cells the imported data is placed in. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changing Cell Colors There are two ways that you can fill a cell with a color. The first is to use the Fill Color tool from the Formatting toolbar, and the other is to use the Patterns tab of the Format Cells dialog box. Both methods are described in detail in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changing Cell Patterns Cell patterns are a great way to highlight a cell and its contents. You set a pattern (or change an existing one) by using the Patterns tab of the Format Cells dialog box, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changing Font Face and Size Conditionally Conditional formatting does not allow you to change the typeface or font size used in a cell. Instead, you need to use a macro to accomplish your formatting goals. It’s easier to do than you think it is by using the information in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changing Font Sizes Did you know that Excel can display any font size from 1 to 409 points in size? What’s a point, you ask? This tip covers all the details so you can make your data just the size you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changing the Default Font Using the Options menu to change the default font in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Checking All Cell Formatting in VBA Excel allows two different types of formatting in a cell: explicit formatting and conditional formatting. When you check formatting in a macro, it is the explicit formatting that is checked. This tip provides some guidance on how you can check conditional formatting, as well. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Coloring Cells with Formulas Want a quick way to tell where all the formulas are in a worksheet? In a few quick steps you can color all those cells. This tip explains how to do it manually, as well as providing a macro to automate the process. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Conditionally Highlighting Cells Containing Formulas You can use conditional formatting for all sorts of things, including the highlighting of cells that contain formulas. This tip explains how you can do this, with the assistance of a short user-defined function. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Copying Headers and Footers Got a set of headers and footers in one worksheet that you want copied to another worksheet? This tip explains the various approaches you can use to perform the copy and get just the results you want. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Creating 3-D Formatting for a Cell You can use the basic formatting capabilities of Excel to simulate a 3-D look. All you need to do is use some patterns and borders that trick your eye into seeing depth that isn’t really there. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Creating Two-Line Custom Formats Formatting cells is easy in Excel; you can even create your own custom formats. Normally those formats result in data that appears on a single line in a cell, but there is a way to create two-line custom formats, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Decimal Tab Alignment Microsoft Word allows you to set up all sorts of tab stops, including decimal tabs. Excel provides no way for you to do this same type of formatting, but there are ways to approximate decimal tab stops. This tip discusses some techniques you can use. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Displaying Negative Percentages in Red Do you use percentages in your worksheets? How about negative percentages? It isn’t immediately clear how you can display negative percentages in red, but Excel actually provides two methods. This tip discusses them both. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Exporting Latitude and Longitude Latitude and longitude are expressed in hours, minutes, and seconds, which means that Excel can store them as time values. This may cause problems when you need to export the latitude and longitude data for use with other programs. This tip explains how you can get the output from Excel that you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Filling a Cell Do you want to repeat the same character or group of characters for the entire width of a cell? Excel provides an easy way to do this by using the Alignment tab of the Format Cells dialog box. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Formatted Data Appears Differently On Different Systems If you notice that dates don’t look the same when a workbook is opened on different systems, it could be because of the way you have the cells formatted. This tip explains why this difference occurs and provides some solutions you can apply. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Formatting Currency Formatting how currency is displayed in Excel is normally controlled by settings in Windows. This tip discusses a couple of other ways you can format currency without changing your Windows settings. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Formatting for Hundredths of Seconds Want to record times down to the hundredth of a second in Excel? You can do so if you know how to create a custom format that displays the time properly. This tip explains how to handle the formatting you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Formatting Subtotal Rows Using Data | Subtotals you can add different types of subtotals to a data table. These subtotals are normally shown in bold, but you may want to apply other types of formatting. This tip provides several different approaches you can take toward that formatting. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Formulas Don't Calculate as Formulas You are typing along in your workbook, and you notice that a formula you enter is not being processed right by Excel. What do you do? You apply the information in this tip to ensure that the formula is no longer treated simply as text. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Getting Rid of Negative Zero Amounts Have you ever done some calculations, only to see a value appear that is a zero with a negative sign in front of it? This is a result of your display formatting and the precision on Excel’s calculations. You can get rid of such seemingly erroneous values, however. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Hiding Individual Cells Got some information in a worksheet that you need to hide so it doesn’t show up on a printout? It’s easy to hide rows and column, but less obvious is how you go about hiding information in individual cells. This tip discusses several different approaches you can take to hiding the information in specific cells. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Indenting Cell Contents You can align information in a cell in just about any manner you can imagine. This tip explains how you can indent cell contents from either the left or the right side of the cell. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Making Squares Use the following macro to make your Excel cells a set square shape. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Matching Formatting when Concatenating You can use a couple of different methods to use formulas to put text strings together. When doing so, you may notice that the results of the concatenations may not be exactly what you expected. This tip examines one cause and provides several different ways you can get just the results you want. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

No New Fonts Error Do you see a message saying something about “no new fonts” when you try to print a worksheet? You may have fallen victim to an obscure error in Excel, described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Number Formatting Shortcuts If you are looking for a quick way to format numeric values in your cells, you can’t beat the shortcuts in this tip. Presented for your formatting convenience are seven shortcuts that help you apply the most common types of formatting. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Professional Looking Fractions If you prefer more professional-looking fractions than what is produced when you type a number, a slash, and another number, then your options are rather limited in Excel. This tip describes those options and the drawbacks of using them. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Referencing External Cell Colors If you want to reference cell colors external to your current workbook, there is no way to do it using Excel functions. You can, however, create your own macro that will do the referencing for you. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Repeating Cell Contents Do you want to repeat the contents of a cell throughout the entire width of a cell? There are two ways you can do it in Excel—one method formulaic and the other through formatting. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Retaining Formatting After a Paste Multiply Paste Special is a powerful tool that allows you to modify large amounts of data in a worksheet. If you don’t use the tool properly, however, you can end up messing up the formatting in the worksheet. This tip explains why this occurs and how you can preserve the formatting you want to keep. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Returning a Worksheet Name It can be handy to reference the name of a worksheet tab directly in a cell of the worksheet. This tip explains a couple of ways you can accomplish this nifty feat. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Setting Horizontal Alignment You can horizontally align the information in a cell in any of eight different ways. This tip explains not only how to do the alignment, but also the meaning of the eight ways you can align information. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Setting Orientation of Cell Values Would you like the information in a cell (or group of cells) to be turned so it is not horizontal? Excel allows you to adjust cell formatting so that text is at almost any orientation you’d like. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Setting the Default Font Size for Comment Balloons Straining your eyes to see the comments in a document? You can modify the size of the default font used for the comments, but it involves making a change in Windows, not Word. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Setting Vertical Alignment One of the ways that Excel allows you to format cells is by adjusting the vertical alignment of the cell’s data. Depending on your version of Excel, there are either four or five alignment types you can use. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Shortcut Key for Format Painter The Format Painter tool is very handy when you want to quickly duplicate formatting form one place to another. The tool virtually requires the use of the mouse, however, as there is no built-in shortcut to use it. This tip describes some of the ways you can use the Format Painter or copy formatting by using the keyboard only. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Shrinking Cell Contents Part of the formatting that Excel allows you to apply to a cell is the ability to “shrink” text so that it fits a single line. This tip examines how to apply this attribute and the effects it can have. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Specifying Superscript Text One of the text attributes you can apply either to entire cells or to selections of characters within a cell is the superscript attribute. This tip explains how to apply this type of formatting. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Turning Off Names You can use some of the tools in Excel to convert cell references in formulas into names. Converting back (from names into cell references) is not so easy, requiring manually editing the formulas. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Underlining Excel provides several different types of underlines you can use in formatting your data. This tip describes how you can produce two different types of underlines by using the Underline tool. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Understanding Cell Indenting Enter text information in to a cell, and Excel normally butts it up next to the left edge of that cell. You can adjust the placement of the data relative to the edge of the cell by indenting the information. This tip explains how this is done. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Understanding Monospace Fonts There are two types of fonts available in Windows (and Excel): proportional and monospace. This tip explains the latter type of font and some of the font considerations you’ll need to keep in mind for Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Understanding Underlines Need to underline your data? Excel provides five different underline settings you can apply right away. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using a Custom Format to Add Dashes Numeric formatting is very flexible in Excel, but that is not the case for formatting text. You can’t use custom formats to control how text is displayed. Instead, you need to use formulas and functions to modify the composition of the text, as discussed in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using Copy and Paste for Formatting The Paste Special dialog box allows you to paste virtually any part of a selected cell or group of cells. This tip explains how you can use the dialog box to copy and paste formatting, without pasting anything else. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using Custom Number Formats Using the formatting menu to create custom number formats for use in Excel worksheets. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

When is Currency Not Currency? One of the tools available for formatting cells is one called “Currency.” This can be misleading, as it doesn’t really apply the Currency format. This tip explains what is really going on. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Tips, Tricks, and Answers

Fast Answers!

Helpful Links

Great Info!

RSS Feeds