Tips, Tricks, and Answers
The following articles are available for the 'Cell Formatting' topic. Click the article''s title (shown in bold) to see the associated article.
Accurate Font Sizes
Need to use some bizarre font size in your worksheet? Not a problem, provided it is a full or half point size.
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.
Adjusting Cell Margins for More White Space
Is the information in your cells too jammed up? Here are some ways you can add some white space around that information so it is more readable.
Adjusting Row Height for Your Text
Want Excel to automatically adjust the height of a worksheet row when it wraps text within the cell? It's easy to do, relying upon two settings.
Adjusting Row Height when Wrapping Text
If you have some cells merged in a worksheet, and you wrap text within that merged cell, Excel won't automatically resize the height of the row as it would with unmerged cells. This tip describes how you can work around this limitation.
Automatically Breaking Text
Want to convert the text in a cell so that it wraps after every word? You could edit the cell and press Alt+Enter after each word, but there's an easier way using a formula.
Better Use of Heading Space
If your column headings are too large to work well in your worksheet, why not turn them a bit? Here's how.
Changing Cell Colors
If you need to change the color with which a particular cell is filled, the easier method is to use the Fill Color tool, as described in this tip. That isn't the only way to fill cells, however.
Changing Cell Patterns
You can shade your cells by filling them with a pattern. Here's how to select the pattern you want used.
Changing Font Face and Size Conditionally
Conditional formatting does not allow you to change the typeface and font size used in a cell. You can write your own macro to do the formatting change, however.
Changing Font Sizes
Want to change the size of the font within a worksheet? Excel allows you to choose from a list of sizes, as well as define your own.
Changing Fonts in Multiple Workbooks
If you need to change fonts used in a lot of different workbooks, the task can be daunting, if you need to do it manually. This tip looks at a macro you can use to make the task more automatic.
Changing the Default Font
If you don't like the font that Excel uses, by default, in a workbook, you can change it. Here's how.
Checking All Cell Formatting in VBA
When your macro checks the formatting used for a cell, it needs to be careful that the type of formatting being checked is taken into account. Here's a discussion of why this is important.
Coloring Cells with Formulas
Easily seeing where all the formulas are in your worksheet can be handy. Here are some ideas on different ways you can color the formula-containing cells so that they stand out from the other cells.
Conditionally Highlighting Cells Containing Formulas
Excel’s conditional formatting feature allows you to create formats that are based on a wide variety of criteria. If you want to highlight cells containing formulas, you can use conditional formatting in conjunction with a short macro.
Creating 3-D Formatting for a Cell
The formatting capabilities provided by Excel are quite diverse. This tip examines how you can use those capabilities to make the contents of a particular cell “pop” off the page.
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.
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.
Determining Font Formatting
If you need to determine the font applied to a particular cell, you'll need to use a macro. This tip presents several macro-based solutions that allow you to return both the font name and font size applied to a cell.
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.
Easy Value Hiding
Want a quick and easy way to hid the information in a cell? You can do it with a simple three-character custom format.
Exporting Latitude and Longitude
A handy way to store latitude and longitude values in Excel is to treat them as regular time values. When it comes around to exporting those values for use with other programs, you'll appreciate the information in this tip.
Filling a Cell
One way you can format a cell is so that its contents are repeated over and over again for the entire width of the cell. It's simple to do using the formatting technique highlighted in this tip.
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.
If you want to format currency values so that Excel uses periods between groups of thousands and commas as a decimal separator, there are a few ways you can get what you want. Here's a discussion of the approaches you can use.
Formatting for Hundredths of Seconds
When you display a time in a cell, Excel normally displays just the hours, minutes, and seconds. If you want to display the time with more precision, you'll need to create a custom format, as described in this tip.
Formatting Subtotal Rows
Excel automatically formats subtotals for you. But what if you want to change the default to something more suitable for your worksheet? Here's how to change the formatting of subtotals whether you use them sparingly or frequently.
Formulas Don't Calculate as Formulas
Enter a formula (starting with an equal sign) and you may be surprised if Excel doesn’t calculate the formula. Here’s a good candidate for what that may happen and what you can do about it.
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.
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.
Hiding Individual Cells
Hiding information in one or more cells can be a challenge. This tip presents several different techniques that can help you keep information from being displayed.
Indenting Cell Contents
Excel allows you to apply several types of alignments to cells. One type of alignment allows you to indent cell contents from the left or right. Here's how to do it.
Cells in a worksheet defined by the intersection of rows and columns. If you adjust row height and column width just right, you can form your cells into perfect squares. The technique in this tips makes it easy to accomplish.
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.
No New Fonts Error
Sometimes Excel does things that may appear just plain wacky. This particular tip deals with an issue that could crop up when printing an Excel workbook and certain conditions are met.
Number Formatting Shortcuts
Keyboard shortcuts can save time and make developing a workbook much easier. Here's how to apply the most common of Excel's number formats with a simple keystroke.
Professional Looking Fractions
Professional typesetting has, in many ways, spoiled us. One way this is evident is in the preference we show for making fractions look, well, more like fractions. Here's a quick explanation of why you can't get professional-looking fractions in Excel.
Referencing External Cell Colors
If you want to reference cell colors external to your current workbook, there is no way to do it using Excel functions. You can, however, create your own macro that will do the referencing for you.
Repeating Cell Contents
Want to repeat cell contents over and over again within a single cell? Excel provides two ways you can duplicate the content.
Retaining Formatting After a Paste Multiply
You can use the Paste Special feature in Excel to multiple the values in a range of cells. If you don't want Excel to mess up the formatting of those cells, then there is one additional step you need to remember.
Returning a Worksheet Name
Need to know the name of the current worksheet? You can use the CELL function as the basis for finding this information and putting it in a cell.
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 Horizontal Alignment
You can horizontally align the information in a cell in any of eight different ways. This tip explains not only how to do the alignment, but also the meaning of the eight ways you can align information.
Setting Orientation of Cell Values
Need the contents of a cell to be shown in a direction different than normal? Excel makes it easy to have your content appear at any angle from 90 to -90 degrees.
Setting the Default Font Size for Comment Balloons
Straining your eyes to see the comments in a document? You can modify the size of the default font used for the comments, but it involves making a change in Windows, not Word.
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.
Shortcut to Merge Cells
Need to merge a bunch of cells together on a regular basis? You'll love the two macros in this tip which can make short work of merging.
Shrinking Cell Contents
Need to cram a bunch of text all on a single line in a cell? You can do it with one of the lesser-known settings in Excel.
Turning Off Names
You can use some of the tools in Excel to convert cell references in formulas into names. Converting back (from names into cell references) is not so easy, requiring manually editing the formulas.
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 Cell Indenting
Formatting a cell could, if you desire, also include the indentation of information within the cell. This tip examines what it means to indent information and how you can format the indentation you desire.
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.
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 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.
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.
Using Custom Number Formats
Most formatting needs are met by using the predefined formatting options in Excel. The program also allows you to move beyond the predetermined, however, and create custom formats. This tip shows you how.
When is Currency Not Currency?
One of the tools available for formatting cells is one called "Currency." This can be misleading, as it doesn’t really apply the Currency format. This tip explains what is really going on.