Formatting

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
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.

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.

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.

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.

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.

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.

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
Use Format Painter to copy formatting in your spreadsheet.

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.

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
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.

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
How to direct Excel to not show zero sums in a worksheet.

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.

Fitting Your Information
Excel has a few ways to size the width of columns in a worksheet.

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.

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
Formatting raw data into consistent reports on an ongoing basis is easy with this tip.

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.

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.

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.

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 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.

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?

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.

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.

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.

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
Using the toolbar and the format menu to set the text attributes in an Excel workbook.

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.

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
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.

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 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.

Understanding Number Formatting Codes
A description of the symbol and meaning of Excel's number formatting codes.

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
Excel has the ability to use any font Windows can use.

Wrapping Your Text
Using Wrap Text on the formatting menu to ensure all the text in a cell is displayed.

 

More Information

The following are additional topics related to the subject of 'Formatting'. A bracketed number after the topic indicates how many articles are related to that subject.