How can your critical data stand out in the report?

We have too much information today!  Every day, we prepare reports; summarize data into information.  Our reports can contain hundred to thousand records even they are summarized.  On the other end, our consumers may receive ten, twenty, or even up to hundred reports a day.  We have to make sure the critical data stand out in our reports; otherwise, they may not be noticed.  Conditional formatting is a popular feature to easily identify records that require attention.

Below is a very simple Excel sales report; it only has 25 records.  But even with just 25 records, it may not be so obvious where we should start looking.

image

But if we highlight the records with colors, then most people would now focus on those highlighted records; especially the red one.

image

We can format the cells with color in earlier versions of Excel; but it may take a lot of efforts.  With conditional formatting, we can highlight the report in a few mouse clicks and Excel would do the rest for us.

  • Select the range of cells that you want to apply Conditional Format.
  • Click Conditional Formatting in the Home ribbon.
  • Select Highlight Cells Rules.
  • Select Greater Than…

image

  • Greater Than dialogue would be showed.

image

  • Enter=B2” in the first box.
  • Select “Yellow Fill with Dark Yellow Text” in the second box.
  • Click OK button.

image

All the cells in the range C2..F6 would be formatted according to this condition.  I created 2 more conditional formats and you can see them all in the Conditional Formatting Rules Manager.

image

Conditional Formatting allow us to select a lot of conditions.

  • We can highlight the top 5 and bottom 5 records.

image

  • Add databars to each record.

image

  • Color the whole range.

image

  • And even icons.

image

The above demo was done using Excel 2010 but conditional formatting was introduced in Excel 2003.

Conditional formatting gives us a long list of tools to visualize the data and make sure our consumer would not miss any critical data.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

One Response to How can your critical data stand out in the report?

  1. Pingback: How much time have I spent on blogging? « Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: