Excel: Formatting

Description: There are many format options available which we are not aware. As there are lots of format options, this section is not going to explain all options. It will give an example where the usage of a bad format resulted in wrong business data.

Scenario: We have the start date and end date of a project. We need to calculate the project duration in hours. The formula used is simple and placed the cell format as hh:mm. The duration between 15th April 8 AM and 15th April 5PM is calculated correctly [9 hrs]. But the duration between 15th April 8 AM and 16th April 5PM is also calculated as 9 hours, which is wrong. Heredue to the wrong formatting, formula is avoiding the date, it considering only the time.



Now let us see the correct format option. Noticed the formula used is same. Only difference is the cell format where it changed from hh:mm to [hh]:mm.

Excel: Conditional Formatting

Description: Make the business data more appealing using color or icon combinations. Old versions of Excel are having a limitation of maximum 3 conditions. In Excel 2007, we can have more conditions. Different options available are:-

Data Bars – Represent the data along with a graphical notation of the data.
Color Scales – Represent the value using color ranges
Icon Sets: - suitable for specific range of values like grades, ranks, etc.
Top/Bottom Rules: - Useful when we want to highlight Top 10 values or bottom 10% items or the items above/below average.
Highlight Cells Rule: - Useful when we need to apply the style using complex conditions.