Disappearing Zeros in Excel
First, you need to realize that Excel sets a format for every cell. The default format depends on the information you type, as Excel tries to read your mind. Computers are not always good mind readers.
For example: When you type text in a cell, Excel sets the format of that cell to "text". When you type a number, it sets the format of that cell to a number. So, when you type in a Zip Code of "00123", Excel tells you that there are no Zeros needed and places the number "123" in the cell.
A quick fix for this is to Right-Click the cell and choose "Format Cells." In the Format Dialog Box, choose "Custom" and in the "Type" field, type the number of digits for your numbering. So, in the Example above, you would type "00000" as Zip Codes contain five digits.
Once you click "OK", your Disappearing Zeros will Reappear!
If you have a group of cells that do not contain a specific number of digits, then you will need to use a macro. Look for future advanced training on Macros for that fix.
Excel Conditional Formatting
Excel includes a powerful feature that allows you to dynamically change the formatting of individual cells based on the results being displayed in that cell. For instance, you could make the text in the cell larger and red if a result is less than a certain threshold. Likewise, you could color the background of a cell based on the result of a formula.
To take advantage of conditional formatting, follow these steps:
Enter your cell formula as you normally would.
Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.
Use the controls in the dialog box to specify the threshold or ranges you want to set for formatting to be changed.
Click on the "Format" button to edit the formatting you wish to appear when the condition is True.
Click on OK to close the Format Cells dialog box.
Click on the Add button and define more conditions (and formats), if desired.
Click on the OK button to close the Conditional Formatting dialog box.
You should now see the formatting in all of the cells that meet the condition.
Most Popular
-
Have you ever printed a document only to remember that you forgot to print that one last line? Save a tree and save some ink with this tip. ...
-
Intrenational users sometimes view things differently than here in the US. But, did you know you can change Excel to International View? ...
-
Many people are familiar with online e-mail accounts that provide access from anywhere you have Internet access. These accounts include G...