Excel provides several formatting tools that allow you to make your data appear how you want. Depending on the purpose of your data, certain formatting elements like backgrounds, lines, margins, or highlighting may vary. The following articles outline what Excel allows you to format and how you should apply it.

Tips, Tricks, and Answers

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

   Adding Drop Shadows to Cells
Want to draw attention to what is in a cell? What better way than to add a drop shadow to that cell! Here's how you can do it.

   Altering the Displayed Format of Numbers to the Nearest 100
Want information in a worksheet to be formatted and displayed as rounded to a power of ten? You may be out of luck, unless you want to round to either the nearest thousand or million. Here's why.

   Automatically Copying Formatting
It's easy to automatically set the contents of one cell to be equal to another cell. But what if you want to copy the formatting as well? This tip explains how you can automatically copy the contents and the formatting of one cell to another.

   Changing Character Spacing
Excel allows you to adjust spacing between cell walls and the contents of those cells. It does not, however, allow you to adjust spacing between characters within the cell. Here's why.

   Changing Font Color
There are any number of reasons to format different cells in different colors. Excel allows you to easily change the color used to display information. This tip describes a couple of ways to change font color.

   Changing Font Size Using a Shortcut Key
Want to adjust the font size used in a cell or range of cells? It's easy to do by using the shortcut described in this tip.

   Changing the Font Size in Combo Boxes
When you add a combo box to a worksheet, Excel makes some assumptions about the best font size to use in the control. This tip explains how you can select a different font size, if you need to.

   Changing the Percent Symbol
Some symbols can be easily changed in Excel or in Windows, such as the symbols used for currency and to separate thousands in numbers. Other symbols are more difficult to change. It is this latter group into which the percent symbol falls, as illustrated in this tip.

   Conditional Page Breaks
Need to have your worksheet printout start on a new page every time a value in a column changes? There are a couple of ways you can accomplish the task, as described in this tip.

   Conditionally Formatting an Entire Row
Need to conditionally highlight an entire row based on the contents of a single cell in each row? This tip explains how you can set up the conditional format that will do the trick.

   Controlling How Excel Interprets Percentages
When entering data in a worksheet, Excel tries to figure out how your entry can best be shown on the screen. When it comes to interpreting percentages, however, you can configure the program to match your intent for the numbers you type. Here's how.

   Converting Forced Text to Numbers
If you have some numbers stored in cells that are formatted as text, you may get some surprises when you try to use those cells in some of your formulas. Here's how to force those text-formatted cells back to normal numeric-formatted cells.

   Converting From Numbers to Text
If you have a range of numeric values in your worksheet, you may want to change them from numbers to text values. Here's how you can make the switch.

   Copying Formats to a New Worksheet
Do you want to copy formats from one worksheet to another? You can do so easily by using the Format Painter. It even works on column widths and row heights.

   Copying Formatting
Excel provides a couple of different ways to copy formatting from one cell to another. Perhaps the easiest way is to use the Format Painter, as described in this tip.

   Creating a Center Across Selection Button
The ability to center text across a range of cells has long been a staple of experienced Excel users. Here's how to create a tool that can handle this type of formatting for you.

   Creating Superscript and Subscript Buttons
Want a quick way to apply superscript and subscript to selected text within a cell? This tip shows how the formatting can be done through a user form and a small set of macros.

   Custom Formats for Scientific Notation
Excel allows you to format your numeric values in a wide variety of ways. One such formatting option is to display numbers in scientific notation. Unfortunately, Excel only provides a single way to display scientific notation. Here's a way to work around that limitation, however.

   Dates with Periods
You may want Excel to format your dates using a pattern it doesn't normally use—such as using periods instead of slashes between the day, month, and year. Here's how you can get the format you want, plus some comments on making that format the default for dates.

   Defeating Automatic Date Parsing
Excel is continually trying to figure out what type of data is being stored in a cell. If it can interpret a value as a date, it will do so at the drop of a hat. Here's how to stop Excel from doing that parsing and messing up your data.

   Displaying Latitude and Longitude
If you work with geographic data, you may need a way to display latitude and longitude in a worksheet. This tip examines several different approaches you an take.

   Displaying Zeros
There are times when displaying zero values in a worksheet (especially if there are lots of them) can be distracting from the real worksheet data. Fortunately, Excel makes it easy to turn off the display of zero values so you can get at the meat of your data easier.

   Excel Applies Scientific Notation to Imported Data
Using Excel to import data from another source (such as a database) is a great approach to analyze that data. What do you do, however, when the data you import is misinterpreted by Excel? Here's a way to handle that problem.

   Flashing Cells
Want to draw attention to some information in a particular cell? Make the cell flash, on and off. Here's how you can implement this type of effect.

   Formatting Combo Box Text
If you insert objects, such as a combo box, in your worksheet, you may need a way to modify the font used in the object. The ability to make such changes depends on the type of object you are inserting.

   Formatting Raw Data
When you get a bunch of raw data into Excel from an external source, it isn't going to be formatted to your liking. The technique described in this tip allows you to copy formatting from one worksheet and apply it to all the imported data in another worksheet.

   Handling Leading Zeros in CSV Files
When dealing with files containing comma-separated values, you want to make sure that what gets imported into Excel reflects what is really in the file. If you import a file and find that Excel strips off leading zeros from what it imports, there are a number of possible reasons. This tip explains how you can track down the problem and correct it.

   Handling Negative Numbers in a Complex Custom Format
Custom formats are great for defining how a specific value in a cell should look. They aren't that great at doing complex formats that contain multiple conditions, as discussed in this tip.

   Hiding Columns Based on a Cell Value
Need to hide a given column based on the value in a particular cell? The easiest way to accomplish the task is to use a macro; several are highlighted in this tip.

   Hiding Rows Based on a Cell Value
This tip contains a macro to hide rows that contain data you don't want to see.

   Merge and Center Not Available
What are you to do if you are trying to format a worksheet, only to find out that one of the tools you need is not available? The reason could be simple; this tip looks at why the Merge and Center tool might be unavailable when working in a workbook.

   Moving Custom Formats to Number Formatting Categories
Moving your custom formats into a formatting category other than "custom" isn't something you can do in Excel. Here's why, and what you can do about it.

   No More Custom Formats Can Be Added
If you make too many formatting changes to your workbook, you could end up with a situation where you cannot make any such additional changes. This can cause a bit of panic, but understanding the error is the first step to fixing it.

   Notation for Thousands and Millions
When working with very large numbers in a worksheet, you may want the numbers to appear in a shortened notation, with an indication as to whether the number represents thousands or millions. There are a couple of ways you can utilize this type of notation, as discussed in this tip.

   Partially Blocking Social Security Numbers
Need to protect a series of Social Security Numbers in a worksheet? The techniques provided in this tip might be a good starting point for your endeavors.

   Preventing Changes to Formatting and Page Size
When you create workbooks for others to use, you might want to make sure that they can't change the formatting and paper size you've specified. This can be a bit tricky, but there are ways around the issue.

   Problems with Default Workbook and Worksheet Templates
You can create a default template for both your workbooks and worksheets. These should be placed in the xlstart folder, but what if doing so causes problems when you start Excel?

   Random Width and Height Changes
Have you ever been using a workbook, only to open it one day and find that Excel has changed the height of your rows or the width of your columns? If you've run into that problem, you may be interested in the solution presented in this tip.

   Removing All Formatting
Getting rid of formatting from a cell or group of cells can be done using several different techniques. This tip describes the techniques available to you.

   Replacing Cell Formats
Need to replace the formats applied to some cells with a different format? Those using Excel 2003 will find it easy; those using an earlier version, not so easy. Here's how to accomplish the switch.

   Saving Custom Formats
While the implementation of custom formats in Excel is not terribly robust, you can still achieve some amazing results with them. Once defined, you'll want to save those formats using the techniques described in this tip.

   Setting Text Attributes
Want to change the attributes of your text (or what Excel refers to as font styles)? Here's how to do it.

   Sorting ZIP Codes
Sorting ZIP Codes can be painless, provided all the codes are formatted the same. Here's how to do the sorting if you have different types of ZIP Codes all in the same list.

   Specifying Superscript Text
Applying different formatting to the text within a cell can seem a bit confusing. This is certainly the case when it comes to formatting some text as superscript. Here's the steps you need to follow.

   Stopping Fractions from Reducing
Enter a fraction into Excel, and you may be surprised that the program reduces the faction to its simplest form. If you want to stop fractions from being reduced, the answer lies in the formatting applied to the cell.

   Superscripts in Custom Formats
When you create custom formats for your data, Excel provides quite a few ways you can make that data look just as you want it to. Here are some ways you can use superscripts in your custom formats.

   Too Many Cell Formats
The error message "too many cell formats" can be difficult to fix. This tip describes ways you can attempt to get rid of the message.

   Unable to Format Cells
If you ever get to a situation where you can no longer format cells in a workbook, you'll realize just how important the formatting capabilities of Excel really are. You'll also realize that you need to figure out what is causing the problem; the information in this tip can provide some guidance.

   Understanding Color and Conditional Formatting Codes
When you create custom cell formats, you can include codes that allow you to set the color of a cell and that specify the conditions under which that color should be applied. This tip examines the specific codes that Excel understands for these purposes.

   Understanding Date and Time Formatting Codes
Want to apply a custom format to your dates and times? To do it effectively you need to understand the custom formatting codes that apply to this type of data.

   Understanding Number Formatting Codes
When creating custom formats, you can employ a wide range of codes to define your formatting pattern. This tip focuses on those codes used to format numbers.

   Using an Exact Number of Digits
Excel allows you to format numeric data in all sorts of ways, but specifying a number of digits independent of the decimal point is not one of the ways. If you need this very specific type of formatting, you can use some of the ideas in this tip.

   Using Fractional Number Formats
If you want information to display on the screen using fractions instead of decimals, you're in luck. Excel provides formatting that handles just that need.

   Using Strikethrough Formatting
Need a line through the middle of your text? Use strikethrough formatting, which is easy to apply using the Format Cells dialog box.

   Working with Fonts
Windows allows you to install different fonts that control how information is displayed and printed. This tip gives a high-level explanation of what fonts are and how you can use them in Excel.

   Wrapping Your Text
Want to see all the text that is in a cell, even if it is quite a bit? You need to make sure that text wrapping is turned on, then you can see it.