Posts Tagged ‘Conditional Formatting’

UntitledWe have earlier discussed how to hide errors in a worksheet in this post. Today I’m going address another similar problem that is very commonly faced. There are various ways to tackle it and there is no right or wrong about it. I’m sharing what I prefer to use (because I find it very simple and easy to implement).

Suppose I have a data table in my dashboard that is referencing to another range. My table has 10 rows (A2:D11) that reference range Z2:AC11. As long as there is data in Z2:AC11, everything is fine.

TableNow suppose that I have data only till row 4. In this case, my table shows 0’s which looks ugly and I’d rather prefer those cells to be blank. How do I solve this problem?

table2 (more…)


UntitledToday, I’m sharing a basic but nonetheless a neat trick to make your reports more presentable. I’ll take an example to illustrate my point.

I have Life Expectancy data for various countries for years 2011 and 2012.  The fourth column is calculated using data in B & C columns. The number of rows of data can change, so I have put formula in extra rows. The problem then is that the rows that don’t have data return #DIV! error in column D as below. (more…)

There are numerous Project Management tools available in the market which make life easier while managing huge projects. But for simpler projects, you don’t need all that jazz, just a simple project plan template is all that is required. Well, you can use conditional formatting to create simple Gantt Charts. Here’s how:

Step 1: Create your list of tasks and the decide the proposed dates against them. On the top row, put down the dates (one in each column) like this:

Task List

Task List


Conditional Formatting is an extremely powerful feature in Excel 2007 and above that can be used not only to beautify spreadsheets but also to highlight things based on some pre-defined rules. Excel provides a lot of built-in rules that mostly work on cell values and are easy to apply. What I’ll be covering in this series is less common applications of conditional formatting. Since it’s a very vast topic (like custom charts), it’s going to be another series.  (more…)