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.

   Applying Table Formats
Want to format your data tables in a hurry? It's easy to do if you use the built-in table formatter provided in Excel.

   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 Currency Formatting for a Single Workbook
Currency is formatted differently in different corners of the world. Most formatting uses periods and commas to indicate decimal values and groups of thousands, but different places use those characters differently. You may want to apply the techniques introduced in this tip.

   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 Formatting with Data Imported from Access
If you want to apply a conditional format to data imported into Excel from Access, you may run into some difficulties related to how that data is interpreted. Here's some ways around the difficulties.

   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.

   Deleting Unwanted Styles
Custom styles can be a great help in formatting a worksheet. You may, at some point, want to get rid of all the custom styles within a given workbook. Here's a quick macro that can make that task easy.

   Differentiating a Header Row
When you use the sorting tool, Excel tries to automatically figure out if your data includes a header row or not. Here are some ways you can help it make the right determination.

   Formatted Dates Appear Differently on Different Systems
When you format a date in a specific manner, you may be surprised to see that the format changes when you open the workbook on a different system. There is a reason for this change, as described in this tip.

   Getting Rid of Negative Zero Amounts
Have you ever seen a worksheet in which some zero values have a negative sign in front of them? There's a reason for this, as explained in this tip.

   Hash Marks Displayed Instead of Cell Contents
Have you ever entered information in a cell only for it to appear as hash marks? This tip explains why this happens, how you can fix it, and how to avoid it in the future.

   Losing Formatting
When you save a workbook, you expect Excel to remember the formatting you applied in the worksheets in that workbook. If Excel loses the formatting, there are a couple of things you should check.

   Matching Formatting when Concatenating
Convert a numeric value to text and you may be surprised by how Excel displays the value. Here's a run-down on exactly what Excel does in the conversion and how you can modify the way the conversion is done.

   Mimicking Small Caps in Excel
Word provides a much wider range of formatting tools and options than you can find in Excel. One example is when it comes to small caps; they just aren't available in Excel like they are in Word. This tip addresses this missing formatting option and provides a couple of ways you can address it.

   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.

   Pasting Numeric Values in Other Programs
When you paste information from Excel into other programs, you may get more than you actually want. It is not unusual for the paste to include formatting characters such as dollar signs and commas. Here are some ideas for getting rid of those characters and pasting just the values into the other program.

   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.

   Saving a Custom Table Style
When you format your data as a table, Excel allows you to apply a style to that table. You can even create your own table styles. The ability to reuse those table styles in the future depends on if you save them in an Excel template.

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

   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 is great for copying formatting from one cell to another. If you don't want to grab the mouse to use the Format Painter, you'll like the shortcuts described in this tip.

   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.

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

   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
Excel provides a variety of underlining styles you can use when you need to underline information within a cell. Here's what those styles are and how you can apply them.

   Using a Two-Character Day of the Week in a Date Format
Excel provides quite a bit of flexibility in how you can format dates. Even so, some dates simply cannot be formatted through the use of custom formats. This tip looks at one such date format and provides a couple of ways you can still get the format you desire.

   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.