Format Cells| custom format | number format

                                         Format Cells 

Format Cells dialogue Box

 

As we have already discussed in my previous post how to activate Format Cell dialogue box. Mouse Right Click on the required cell one can activate Format Cell options. Even under Home tab there is Drop Down Combo box. So I ignore that aspect completely. Today we will discuss on two important tabs of Format Cell dialogue box.

1. Number Tab
2. Protection Tab

Number Tab

General Format

General Tab basically stands for what you entered in the cell. It is general.It has no specific format.

Number Format

Number category displays the number format. We can change the any number to a particular format.
Say 5652548 seven digit number is entered in a cell. We can convert the number as 56,52,548.00 by selecting the option Number from category, with decimal places to 2 and tick check box for 1000 separator. The number will be displayed in the cell as 56,52,548.00. Further you can highlight the type of negative number format. Since the above example is a positive number we will not see the negative number impact.
Can you write & display a number beginning with 0?
Easiest method.....Yes, put ' beforehand. It will display the same entered number. In this procedure the number is converted into text and aligned to left.
We will discuss different method in custom format option.

Currency Format

Currency format is same as number format. Only exception is you can put currency symbol beforehand. Even you can disable currency symbol.

Accounting Format

Accounting format is also similar to Currency Format.
But this Accounting format aligns the currency symbol in a column.But the result is same as Currency Format.

Date Format 

Very important format that is used and leads to numerous discrepancies too. 

By default excel interprets 1900 date system. It starts counting from 1st January 1900. So when you enter a date value excel converts the date value to number format that is the day elapsed from 1st January 1900. There are different subcategories of date display.Under Home tab there is a drop down box of different formats.

Time Format 

 

Lets understand this format with a simple example.
1. Write 14:26 in a cell.
2. Right click and click format cell option and select the time format.
3. Highlight specific time format (say 1.30.55 pm)
4. Result is 2.26.00 pm

Remember that time format reads ':' not '.'

Percentage Format

This format most of the time is misinterpreted. Say if you want to fill the cell with 3%. If you type 3 and change the format to percentage then the result will be 300%.
Instead 3 write .03 then the format will be 3%. Again 8.37 will be 837.00%. Instead .0837 will be 8.37%. Actually percentage format multiplies 100 after decimal. And that is the math.

Fraction Format

Use the fraction format to use numbers in fraction rather than in decimals. Lets understand this format with an instance. 

Scientific Format

Certain range of numbers excel automatically format those number as exponent. In the formula bar you can see the actual number. So far excel can interpret up to 11 digit number. There will be no difference in calculation. Except format will be in exponential. Tip: You can display more than 11 digit number by putting ' beforehand. Number will be displayed without exponent.

Text Format 

Text format cells are treated as text even when a number is in the cell. The cell is displayed exactly as entered. Text format displays from left to right. Where as number displays from right to left. Text format is left aligned and number format is right aligned.

Custom Format 

This is the high end formatting option. But one thing to remember, format only change the display and underline number will be same. And any computation will not differ.

Basis and basics of number format
 1. Positives #,##.00 commas with two decimal places
 2. Negatives (#,##.00) commas with two decimal places
 3. Zeros 0.00 Signifies zero
 4. Text Red@ Displays text in red or simply @ converts the number into text
When formatting always watch the display in the 'Sample space above.

Number formats with code

Hope above number formats serve ones purpose.

Protection Tab

 
This is to remember that locked cells can only be protected or unlocked cells can't be protected. Here protection signifies no alteration. Now some cells can be protected and some cells can be unprotected and at the discretion of the user. By default all the cells in a sheet is locked.
Detailed workflow........

Under Review tab you can find Protect sheet function.