Cell Formatting

Tips, Tricks, and Answers

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

Accurate Font Sizes
Need to use some bizarre font size in your worksheet? Not a problem, provided it is a full or half point size.

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.

Adjusting Cell Margins for More White Space
Is the information in your cells too jammed up? Here are some ways you can add some white space around that information so it is more readable.

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.

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.

Automatically Breaking Text
Want to convert the text in a cell so that it wraps after every word? You could edit the cell and press Alt+Enter after each word, but there's an easier way using a formula.

Better Use of Heading Space
If your column headings are too large to work well in your worksheet, try this tip.

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.

Changing Cell Colors
If you need to change the color with which a particular cell is filled, the easier method is to use the Fill Color tool, as described in this tip. That isn't the only way to fill cells, however.

Changing Cell Patterns
You can shade your cells by filling them with a pattern. Here's how to select the pattern you want used.

Changing Font Face and Size Conditionally
Conditional formatting does not allow you to change the typeface and font size used in a cell. You can write your own macro to do the formatting change, however.

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.

Changing the Default Font
Using the Options menu to change the default font in Excel.

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.

Coloring Cells with Formulas
Easily seeing where all the formulas are in your worksheet can be handy. Here are some ideas on different ways you can color the formula-containing cells so that they stand out from the other cells.

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.

Copying Headers and Footers
Need to copy headers and footers from one worksheet to another? How about from one workbook to another? Here are some techniques you can use to make the copying easier.

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.

Creating Two-Line Custom Formats
Creating custom formats is a very powerful way to display information exactly as you want it to appear. Most custom formats result in information being displayed on a single line in a cell. You can, however, create a format that actually displays information on two lines.

Decimal Tab Alignment
If you are familiar with decimal tabs in Word, you may wonder if you can set the same sort of alignment in Excel. The short answer is that you can't, but you can approximate the behavior of decimal tabs.

Displaying Negative Percentages in Red
Excel includes quite a few different formats you can use for the information in a worksheet. One format that isn't as easy to set up is for negative percentages. This tip explains the two ways you can format those percentages so they appear red, just like you want.

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.

Filling a Cell
One way you can format a cell is so that its contents are repeated over and over again for the entire width of the cell. It's simple to do using the formatting technique highlighted in this tip.

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.

Formatting Currency
If you want to format currency values so that Excel uses periods between groups of thousands and commas as a decimal separator, there are a few ways you can get what you want. Here's a discussion of the approaches you can use.

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.

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.

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.

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.

Hiding Individual Cells
Hiding information in one or more cells can be a challenge. This tip presents several different techniques that can help you keep information from being displayed.

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.

Making Squares
Use the following macro to make your Excel cells a set square shape.

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.

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.

Number Formatting Shortcuts
Keyboard shortcuts can save time and make developing a workbook much easier. Here's how to apply the most common of Excel's number formats with a simple keystroke.

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.

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.

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.

Retaining Formatting After a Paste Multiply
You can use the Paste Special feature in Excel to multiple the values in a range of cells. If you don't want Excel to mess up the formatting of those cells, then there is one additional step you need to remember.

Returning a Worksheet Name
Need to know the name of the current worksheet? You can use the CELL function as the basis for finding this information and putting it in a cell.

Setting Cell Width and Height Using the Keyboard
Hate to take your hands off the keyboard? Here's a couple of ways you can reject the mouse and still adjust the height and width of a cell.

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.

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.

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.

Setting Vertical Alignment
Excel allows you to adjust not only the horizontal alignment of values in a cell, but also the vertical alignment. This tip shows how.

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.

Shrinking Cell Contents
Need to cram a bunch of text all on a single line in a cell? You can do it with one of the lesser-known settings in Excel.

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.

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.

Underlining
Want a quick way to add some underlines to your cell values? It's easy using the shortcuts provided in this tip.

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.

Understanding Monospace Fonts
Information in a worksheet needs to be displayed using fonts. If you understand the two different types of fonts available on your system, you can pick the best font to display your information.

Understanding Underlines
Need to underline your data? Excel provides five different underline settings you can apply right away.

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.

Using Copy and Paste for Formatting
Want to copy formatting from one cell and paste it into another cell? It's easy to do if you use the Paste Special feature of Excel.

Using Custom Number Formats
Using the formatting menu to create custom number formats for use in Excel worksheets.

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.