Formatting in exel

Formatting in excel is used to present data in more presentable form so that it is easily readable.

NOTE: In excel, by default numbers are right aligned and string values are left aligned.

TABLE 1

• USING PREDEFINED FORMATS

1. Formatting Date-

STEP 1-Select the column you want to format. In this case it is Date

STEP 2-Go to Home Tab->Number

Here you will see two formats i.e. Short date and Long date,

You can select any format. I have selected Long Format.

2. Formatting Salary

Step 1 is same as above.

Step 2- Number->Select Currency. You can use “,” separator to make is more readable from the option displayed on NUMBER tab.

There are many predefined formats given in “Number Format”. You can try each of them.

• CUSTOM FORMATTING

DATE

We can use custom formatting to customize the data according to our needs.

Let us consider the same Table i.e. Table 1

For Ex. We want to show the date in (“January 01’2018”)

STEP 1- Home->Number->More Number Formats->Custom

STEP 2- In the Type section, you can write your own format

Some Rules:

For month

If you write

mm – 01 (You will get the month number)

mmm – Jan (You will get the abbreviated month)

mmmm – January (Full month)

For Date

dd – 01 (you will get the date)

ddd – Mon (You will get the abbreviated day)

dddd – Monday (Full day)

For YEAR

y – 18 (year will be displayed)

yyy – 2018 (complete year will be displayed)

Using the above rules – “mmmm dd’yyy”

STEP 3 - After clicking OK

 

OUTPUT

CURRENCY

STEP 1- Home->Number->More Number Formats->Custom

STEP 2- In the Type section, you can write your own format

STEP 3- Example Format “$0.00”

Some Rules:

“0” – Zero represents the number itself

“.” – Dot will specify decimal point & number of zeroes after decimal represent, number of places we want after the decimal.

For number format we can use “0” – it will represent the number itself & all type of formatting can be done in it as we have done in currency

OUTPUT

NUMBER

For number format we can use “0” – it will represent the number itself & all type of formatting can be done in it as we have done in currency.

• CONDITIONAL FORMATTING

Conditional formatting is used for highlighting important stuff in the data. Excel provide us with default conditional formats as well as custom options

PREDEFINED FORMATS

STEP 1 – Home->Styles->Conditional Formatting. In this tab, we have several options. Let’s have a look on some of them.

TABLE 2

1.Highlight cell rules

In this tab we have options like Greater than, Less than, between etcetera.

Q1. Suppose we want to highlight that cell which has age greater than 29

STEP 1- Select Age column

STEP 1 - Home->Styles->Conditional Formatting->Greater Than

STEP 2 – Enter the desired value

STEP 3 – Press OK

OUTPUT

TOP/BOTTOM RULES

Q2. Suppose we want to highlight top 2 values in Salary column

• By default, it will highlight 10 values.

STEP 1 – Select the column you want to apply formatting on, in this case (Salary)

STEP 2 - Home->Styles->Conditional Formatting->Top 10 items

STEP 3 – Enter the desired value

STEP 4 – Press OK

DATA BARS

It is used to show the data in bars, to make it more appealing

Q3. Suppose we want to show Salary in the form of bars.

STEP 1 – Select the column you want to apply formatting on, in this case (Salary)

STEP 2 - Home->Styles->Conditional Formatting->Data bars

STEP 3 – Enter the desired value

STEP 4 – Press OK

OUTPUT

It takes the largest value as a reference to display other values

COLOR SCALES

3-Color scales calculate the 50^th percentile i.e. the cell with the minimum value is colored red, the cell that contains middle value is colored yellow and the cell with the maximum value is colored green.

ICON SETS

It can be applied in the same way as color scales.