Topic: Formatting

The following are additional topics related to the category listed above. A bracketed number after the topic indicates how many articles are related to that subject.

Tips, Tricks, and Answers

The following articles are available. 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Automatically Copying Formatting You can easily copy the contents of one cell to another using a formula. There is no way to similarly copy formatting from one cell to another. There are a couple of workarounds you can use, relying either on macros or on the Camera tool. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changes in Excel 2007 Color System Everyone knows that there have been some major changes in the user interface used by Excel 2007. There have been some big changes “under the hood,” as well. One of those changes is in how Excel handles colors—and the change can make it confusing and difficult to work with workbooks developed in older versions of Excel. Microsoft Excel versions: 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Conditional Page Breaks When printing a report from Excel, it is not unusual to want to start a new page when something changes in your data. Using the Subtotals feature of Excel, you can easily have your report paginated on any field in your data. If you prefer, you can instead use the macro provided in this tip to insert the page breaks necessary for your printout. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Conditionally Formatting an Entire Row Conditional formatting is a great tool, allowing you to adjust formatting dynamically based on the contents of your worksheet. Sometimes it can be confusing to put together a conditional format that affects more than just a single cell, as in the case of a format to be applied to an entire row. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Controlling How Excel Interprets Percentages Need to instruct Excel in how it should interpret the percentage values you enter into cells? You can do so by making a couple of quick configuration changes in the program. Microsoft Excel versions: 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Copying Formatting Use Format Painter to copy formatting in your spreadsheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Creating a Center Across Selection Button The Merge and Center tool is used to do just what it says—merge cells and center their contents. If you want to center information across cells without merging, then you’ll love the short macro presented in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Defeating Automatic Date Parsing When importing information into a worksheet, you might be puzzled to see that some of the information ends up being treated as dates, even when it shouldn’t be. This can be frustrating, but there are things you can do to make sure your data is treated as you expect. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Displaying Zeros How to direct Excel to not show zero sums in a worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Easy Value Hiding Custom formats are a powerful (and often underused) feature of Excel. You can even create a custom format, described in this tip, that allows you to hide the information in a cell. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Fitting Your Information Excel has a few ways to size the width of columns in a worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Flashing Cells Want to make information in a cell blink on and off? There is no Excel formatting feature that allows this, but you can create your own flashing by using styles and macros together. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Formatting Raw Data Formatting raw data into consistent reports on an ongoing basis is easy with this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Hiding Columns Based on a Cell Value Want to hide the information in a particular column based on the value stored in a specific cell in your worksheet? It’s relatively easy to do if you have a macro do the work for you. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Hiding Rows Based on a Cell Value This tip contains a macro to hide rows that contain data you don't want to see. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Problems with Custom Views If you get an error when you try to use one of your custom views, it could be due to the protection you have applied to the worksheets. This tip explains why this may cause a problem and what you can do about it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Putting Cell Contents in Footers Do you need the contents of a particular cell to always appear in the footer of a worksheet? You can take care of this using a macro, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Random Width and Height Changes Does your workbook exhibit seemingly random changes to column width and row height? The answer could be simple or not so simple, but you can overcome any unwanted changes by a simple macro-based solution. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Removing All Formatting Need to remove the formatting from a range of cells? There are a number of different approaches you can take, from using the Format Painter to adding a new tool to your toolbar. Each approach is fully described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Setting Text Attributes Using the toolbar and the format menu to set the text attributes in an Excel workbook. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Sorting ZIP Codes If you have both five- and nine-digit ZIP Codes in a column, you may have noticed that they don’t sort properly. The problem is related to how Excel treats the data, either numerically (five-digit codes) or as text (nine-digit codes). This tip explains how you can get the desired sorting results. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Stopping Fractions from Reducing Excel can display fractions just as easily as it can display other types of formatting. When you enter a fraction Excel translates it into a “reduced” version that may not be exactly the format you want. Here’s how to work around that problem. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Superscripts in Custom Formats The Format | Cells option is not available while you are creating a custom format. This means you need to be more creative when doing your formats; this tip explains a few options that you have. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Understanding Date and Time Formatting Codes Dates, which are stored internally by Excel as numbers, can be formatted in a wide variety of ways. There are built-in formats for dates, but you can also create custom formats that display the dates in any way you want. This tip explains the codes you can use for creating custom formats for dates and times. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Understanding Number Formatting Codes A description of the symbol and meaning of Excel's number formatting codes. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using Fractional Number Formats One of the formats you can use to display information is to show fractions, such as 12 1/4 (instead of 12.25). This tip explains how to implement this type of formatting and some of the things you should watch for. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Working with Fonts Excel has the ability to use any font Windows can use. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Wrapping Your Text Using Wrap Text on the formatting menu to ensure all the text in a cell is displayed. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

More Topics

Tips, Tricks, and Answers

An Amazing Resource!

Helpful Links

Great Info!

RSS Feeds