Custom Formats

Excel allows you to apply a variety of formatting to your cells to make them appear how you like, but it can be tiresome to apply each element of the formatting individually. Check out the following articles for a few things you can do in Excel to save and apply custom formatting.

Tips, Tricks, and Answers

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

   Adding a Custom Format to those Offered by Excel
Adding a custom format to Excel is easy. Having that custom format appear in all your workbooks is a different story entirely.

   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.

   Can't Set Custom Format in VBA
You can, in a macro, specify a custom format for a range of cells. If the custom format doesn't seem to "stick" (so to speak), then something else in your workbook could be causing the issue.

   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.

   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.

   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.

   Displaying Numbers as Spaced Pairs
Custom formats can be very powerful in Excel, but not every formatting scenario can be addressed through the use of a single custom format. This tip looks at one such scenario that involves displaying numbers as spaced pairs.

   Easy Value Hiding
Want a quick and easy way to hide the information in a cell? You can do it with a simple three-character custom format.

   Getting Rid of Leading Zeros in a Number Format
Excel, by default, displays numbers with a leading zero, if they are less than 1. Here's how you can get rid of those leading zeros if you don't want to see them.

   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.

   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.

   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.

   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.

   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.

   Using a Custom Format to Add Dashes
Want some dashes automatically added in values you display in a cell? It may be trickier to develop a custom format than you thought, particularly if the values are alphanumeric.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.