Today we are going to discuss about Custom Cell Format in Excel. There is much to cover on this topic, so I will guide you through some basic formatting options.
The format cells dialog box contains 6 tabs:
Number
Alignment
Font
Border
Fill
Protection
There are several ways to access the Format Cell option, and I will demonstrate all of them.
Method-1:
Select a cell or range of cells to be formatted and use shortcut key CTRL + 1 to open Format Cell dialog box.
Method-2:
Within the Home Tab,
there are three groups: Font, Alignment, and Number.
In the bottom right corner of each group, a diagonal down arrow button is present.
Clicking any of these arrows will take us directly to the corresponding section in the format cells dialog box.
Method-3:
In the Home Tab, within the Cells group, there's a drop-down button labeled Format. Click this drop-down to open the format options pane, where you'll find the Format Cells option at the bottom.
Method-4:
Choose a cell or range of cells, then right-click and choose the format cells option.
Please be aware:
In any formatting, the underlying number used for calculations will remain unchanged; we can only adjust the format to display it as desired.
Display Dates as Year:
To format dates by year, highlight the cell range and access the format cells dialog box.
Under the Number tab, choose Custom from the Category list.
Enter the format "yyyy" in the Type box as demonstrated below.
A sample of our formatting is visible above the type box.
To display the last two digits of the year, use "yy" format.
You can also use "e" in place of "yyyy" to show the full year.
Display Dates as Month:
Apply the format "mm" to obtain the two-digit representation of the month number.
Use the format "mmm" to receive the abbreviated month name.
For example: "Jan", "Feb".
Utilize the format "mmmm" to get the complete month name.
For example: "January", "February".
Display Dates as Day:
Use the "dd" format to achieve the two-digit form of the day number.
Employ the "ddd" format to acquire the short form of the day name.
For instance: "Sun", "Mon".
Apply the "dddd" format to obtain the full day name.
For instance: "Sunday", "Monday".
Change font color based on condition:
We can dynamically alter the font color based on a threshold value.
Here is the format we need to enter in Type box,
[Green][>80];[Yellow][>70];[Red]
If the mark is greater than 80 font color is changed to green,
If the mark is greater than 70 font color is changed to yellow,
if it is less than 70 color of the font will be red.
The choices for color names are limited, so we can use color numbers like color10, color20, and so forth instead.
We can assign a group name based on the condition instead of using color, as illustrated below.
[>80]"Good";[>70]"Average";[Red]"Poor"
If the score exceeds 80, it will display as Good.
If the score exceeds 70, it will display as Average.
If it is below 70, it will display as Poor.
Repeat characters based on column width:
In type box, use below format to add spaces between dollar symbol and number.
$* #
Here, we use space after "*", which means fill the gap between dollar symbol and number with spaces.
Number of spaces is decided by the column width, if we increase the column width, gap will also increase.
If we replace spaces with "-", the output will appear as shown below.
Comments