Category Archives for "Formatting"

Format cells to hide numbers, but still use them for calculation

Excel , Formatting

An easy way to hide numeric contents, but still have it available for calculation is to give them a custom format of nothing, There are four custom formats, each separated with a semi-colon (;). <POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT> For example, this formatting, #,##0.00_);[Red](#,##0.00);0.00;”Comment: “@ will show positive numbers in black with two decimals and a blank space at […]

Continue reading

Disguising Numbers on Entry So They Can’t be Read

Excel , Formatting

You’ve seen how numbers are hidden when they are entered in password fields. You can do the same thing in Excel. The number will still be in the cell, but it is shown as the character you define. For example, the custom numeric format, *N Displays the letter N for each digit entered. You can […]

Continue reading

Creating User Prompts and Help with Custom Formats

Excel , Formatting

Including text with numbers is a handy trick you can use to warn users of incorrect entries or to prompt them with a help message. Use the “text” part of custom formats to do this. (You can also do this with the Data Validation command, but using custom formats allows the option of displaying text […]

Continue reading

Formatting Custom Numbers in Colors

Excel , Formatting

You can specify colors in any of the custom number segments. The valid numbers are, [Black] [White] [Red] [Green] [Blue} [Yellow] [Magenta] [Cyan] [color#] (# is any numeric color from 0 to 56 on the standard color palette)

Continue reading

Formatting a Zip Code

Excel , Formatting

To format a Zip Code, select the cells, then right-click and choose Format Cells. In the Format Cells dialog box, select Special in the Category list, then select either the Zip Code or Zip Code + 4 format and choose Ok.

Continue reading

Formatting a Social Security Number

Excel , Formatting

To format a Social Security number, select the cells, then right-click and choose Format Cells. In the Format Cells dialog box, select Special in the Category list, then select Social Security Number and choose Ok.

Continue reading

Formatting an In-Country or International Telephone Number

Excel , Formatting

To format a telephone number, select the cells, then right-click and choose Format Cells. In the Format Cells dialog box, select Special in the Category list, then select Phone Number and choose Ok. If you want to create a format for international dialing, then create a custom format with this pattern, International Direct Dialing + […]

Continue reading

Fitting Long Numbers into Narrow Cells

Excel , Formatting , Formulas

Doesn’t it drive you crazy when a number doesn’t fit in a cell and making the cell wider messes up the beautiful layout you’ve created in another part of the worksheet? Arrgh! Here’s a work around that will solve this problem. Turn the “too wide” number into text so it pushes through the side of […]

Continue reading

Use TEXT() and & to Combine Text, Numbers, and Dates

Formatting , Formulas

A great way to make your charts and dashboards more usable and include dynamic titles, legends, and text is with a combination of the TEXT() and concatenation. Here are some examples, On of our readers, Joe, added a dynamic title to the scrolling project timeline described in Creating Dynamic Excel Timelines that Scroll.  His cell […]

Continue reading