Excel allows you to format cells individually in your worksheet so you can change things like borders, cell color, cell margins, etc. Excel provides a few tools to make the formatting process quick and easy. Learn how to make a cell stand out in your worksheet with the following articles.
Tips, Tricks, and Answers
The following articles are available for the 'Formatting Cells' 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 Diagonal Borders
Borders on all sides of a cell are easy to do in Excel. You can also create diagonal borders that run right through the middle of the cell.
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 Center Across Selection with a Cell Value
One of the most common cell alignment settings I use is the one to center across a selection. If you want to vary how this setting does its work, you'll need to resort to using a macro, as described in this tip.
Automatically Changing a Cell's Background Color
It is often desired to change what is displayed in a cell based on what is in a different cell. What if what you want to change is the background color of that cell? This tip provides a couple of quick ways you can make the change you need.
Automatically Copying Formatting
It's easy to automatically set the contents of one cell to be equal to another cell. But what if you want to copy the formatting as well? This tip explains how you can automatically copy the contents and the formatting of one cell to another.
Automatically Formatting for Decimal Places
Cell contents and cell formatting are, in Excel, largely independent of each other. You can enter something in a cell and then format that cell to display the contents as you desire. What if you want the cell to automatically be formatted to show all the decimal places of a number, as entered? That's the question addressed in this tip.
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 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 Number Display Settings for Single Workbooks
How Excel displays numbers is extremely flexible, but it can also be very confusing. This tip looks at how number display is handled on a regional basis, along with some ideas for changing that handling based on a workbook-by-workbook basis.
Changing the Color of a Cell Border
Excel provides a variety of tools you can use to make your data look more presentable on the screen and on a printout. Here's how to change the color of the borders you may add to your cells.
Changing to a Non-US Date Format
Do you want to specify your months and days differently when displaying dates in your worksheets? This tip looks at how you can get the format you really want.
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.
Controlling Automatic Formatting of Dates
When you enter something into a cell, Excel tries to figure out if your entry should be formatted in a particular way. This means that Excel will try to format dates as dates. But what if you want the date formatted in a different way by default? This tip examines this particular question and provides some suggestions.
Converting Dates to Text
Need to use a date as part of a larger string of text? Here are some handy ways to go about the process.
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.
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 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.
Cycling through Colors
If you need to easily change the font colors in a group of cells, one of the esoteric commands Excel provides is the Cycle Font Colors tool. This tip explains how to make the command available and use it for your formatting.
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.
Determining If a Cell is Bold
Want to figure out if the text in a cell is bold? The best approach is to use your own user-defined function, as explained in this tip.
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 Negative Times
Excel allows you to perform math using times as operands. If you subtract a later time from an earlier time, you should end up with a negative time. If you don't see the negative time, it could be because of the way you have Excel configured.
Adding borders around cells is a common formatting task. You can make the task more intuitive by actually drawing the borders using the mouse.
Enforcing a Desired Font
If your workbooks are shared and used by a number of different people, you may end up with some formatting in those workbooks that you don't want. This tip looks at a simple way to make sure that a particular font is always used in a workbook.
Ensuring Conditional Formatting and Data Validation is Copied
If you use an Excel worksheet for entering data (a quite common task, actually), then you need to be concerned with how formatting is applied to new data that others may enter. Here are a few approaches you can use.
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.
Want to draw attention to some information in a particular cell? Make the cell flash, on and off. Here's how you can implement this type of effect.
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.
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.
Highlighting Values in a Cell
There are many ways that Excel allows you to highlight information in a cell. This tip examines a way to highlight values that are entered into cells that used to contain formulas.
Identifying Merged Cells
Merging cells is a common task when creating worksheets. Merged cells can play havoc with the normal functioning of some of Excel's tools, however. Knowing where those cells are located could be helpful so that you can determine whether to remove them or not.
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.
Locking the Background Color
You can spend a lot of time getting the formatting in your worksheets just right. If you want to protect an element of that formatting (such as a the background color), the prospect can seem a bit daunting. Here are a couple of ideas on how you can achieve the protection you desire.
Maintaining Text Formatting in a Lookup
Want to maintain the formatting used in one cell when you use formulas to reference that text in another cell? The answer is not as straightforward as you might think. This tip explores how Excel handles lookup formulas.
Making a Cell's Contents Bold within a Macro
When your macro is processing information in a worksheet, do you need to periodically make the contents of a cell bold? You can do so by adding one simple statement to your macro, as demonstrated in this tip.
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.
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.
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 Automatic Date Formatting Changes
Excel often changes the formatting of a cell based on how it parses what you are entering into that cell. This is particularly true when you are entering dates. Here are a few techniques you can use to avoid those formatting changes.
Printing a Multi-Line Footer
Add a footer to your document and you may want to make it span more than a single line. This can be easy to do, provided you remember how to "break" the lines.
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.
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.
Need to get rid of the borders around a cell? The shortcut in this tip can make quick work of this formatting task.
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.
Replacing Background Colors in Cells
Want a quick way to replace background colors in cells? It's easy to do using Find and Replace, or you can simply use the quick macro provided in this tip.
Replacing Cell Formats
Need to replace the formats applied to some cells with a different format? You can use Excel's Find and Replace tool to make the switch.
Retaining Formatting After a Paste Multiply
You can use the Paste Special feature in Excel to multiply 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.
Rounded Corners on Cells
As you are formatting a worksheet, Excel allows you to easily add borders to cells. Adding rounded corners to cells is a different story; there is no easy way to do it. This tip presents a workaround that may help you achieve the desired outcome.
Select One Cell and Make Another Cell Bold
Excel provides a number of different ways you can apply formatting to a cell based upon various dynamic conditions. One condition it doesn't allow you to check, however, is which cell is selected. Here's how you can get around that and apply formatting based upon whatever cell you have selected at the time.
Setting a Default Date Format
Enter a date into a cell, and Excel allows you to format that date in a variety of ways. Don't see the date format you want? You may have a wider number of formats available than you think you do.
Setting Cell Color Based on Numeric Values
Excel allows you to specify colors for the interior of cells in your worksheet. If you want those colors to be set automatically based on values in the cells, you will need to use a macro, such as the ones presented in this tip.
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.
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.
Specifying Font Color in a Formula
Do you need to change text color based on the result of a formula? This tip provides a couple of ways you can accomplish the task without using conditional formatting.
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.
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.
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.
Wrapping Text in Merged Cells
When you are formatting your worksheet, Excel lets you easily merge adjacent cells together. If you want to wrap the text that you place in merged cells, you may wonder how to do it. This tip provides the information you need.