Conditional Formatting in Excel – 5 Tips to make you a Rockstar

How to be excel conditional formatting rockstarExcel conditional formatting is a hidden and powerful gem that when used well, can change the outlook of your project report / sales budget / project plan or analytical outputs from bunch of raw data in default fonts to something truly professional and good looking. Better still, you don’t even need to be a guru or excel pro to achieve dramatic results. All you need is some coffee and this post to learn some cool conditional formatting tricks.

So you got your coffee mug? well, lets start!

The 5 tricks we are going to learn are,
1. Highlighting alternative rows / columns in tables
2. No-nonsense project plans / Gantt charts
3. Extreme In cell graphs
4. Highlight mistakes, errors, omissions, repetitions
5. Create intuitive dashboards

If you are new to Excel Conditional Formatting, please read the Conditional Formatting Basics article before proceeding.

I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star

1. Highlighting alternative rows / columns in tables:

Using MS Excel conditional formatting to change background color of alternative rows or columns
Often when you present data in a large table it looks monotonous and is difficult to read. This is because your eyes start interpreting the data as grid instead of some important numbers. To break this you try highlighting or changing the background colour of alternative rows / columns. But how would you do this if you have rather large table and it keeps changing. The trick lies in Conditional Formatting. (Of course you can use the built-in auto format feature, but we all know how the default settings of various Microsoft products are like).

  • First select data part of the table you want to format.
  • Go to Conditional formatting dialog (Menu > Format > Conditional Formatting)
  • Change the “cell value is” to “formula is” (YES, you can base your formatting outcome on formulas instead of cell values)
  • Now, if you want to highlight alternative rows, the formula can go something like this,
    which means, whenever row() of the current cell is even, to change the colouring to odd rows, you just need to put =MOD(ROW(),2)=1 as formula
    Also, if you want to highlight alternative columns instead of rows you can use the column() formula.
    What if you want to change background colour of every 3rd row instead, just use =MOD(ROW(),3)=0 instead. Just use your imagination.

  • Set the format as you like, in my case I have used yellow colour. When you are done, the dialog should look something like this:
    Excel Conditional Formatting dialog box, entering formulas to set the format
  • Click OK.
  • Congratulations, you have mastered a conditional formatting trick now :)
2. Creating a quick project plan / Gantt chart using conditional formatting:

How to create Microsoft excel based gantt chart / project plan
Project plans / Gantt charts are everyday activity in most of our lives. Creating a simple and snazzy project plan template in excel is not a difficult job, using conditional formatting a bit of formulas you can do it no time.

  • First create a table structure like shown above, with columns like Activity, start and end day, day 1, 2,3, etc…
  • Now, whenever a day falls between start and end day for a corresponding activity, we need to highlight that row. For that we need to identify whether a day falls between start and end. We can do that with the below formulas,
    =IF(AND(F$8>=$D9, F$8<=$E9),"1","")
    Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell.

  • Next, whenever the cell value is 1, we will just fill the cell with a favourite colour and change the font to same colour, so that we don’t see anything but a highlighted cell, better still, whenever you change the start or end dates, the colour will change automatically. This will be done by conditional formatting like below:
    Excel Conditional Formatting Dailog, highlight a cell
  • Congratulations, you have mastered the art of creating excel Gantt charts now
3. Extreme In-cell Graphs:

In cell graphing is a nifty trick that basically uses REPT() function (used to repeat a string, character given number of times) to generate bar-charts with in a cell. You can apply conditional formatting on top of them to give the charts a good effect. Here is a sample:
Excel Condtional Formatting along with In-cell Graphs

The above is a table of visits to Pointy Harried Dilbert ;) in the month of January 2008. As you can see I have highlighted (by changing the font colour to red and making it bold) for the cells that have more than average number of visits in the month. I am not going to tell you how to do it, it is your home work :)

4. Highlight mistakes / errors / omissions / repetitions using conditional formatting:

Conditional formatting errors
Often we will do highly monotonous job like typing data in a sheet. Since the work is monotonous you tend to make mistakes, omit a few or repeat something etc. This can be avoided by conditional formatting. I use this trick whenever I am typing something or pasting a formula over a rather large range of cells (for eg. vlookup on annual revenue data of all your accounts, could run in to thousands of rows across multiple states /regions etc.).

Lets see how you can highlight a cell when it has an error:

  • First select the cells that you want to search for errors
  • Next go to menu > format > conditional formatting and mention the formula as: =is error() (see below)
    Microsoft Excel conditional formatting dialog box
  • In the same way you track repetitions, a simple countif() would do the magic for you, or Omissions (again a countif())
  • That it, you have learned how to save tons of time by letting excel do the job for you. Sit back and sip that coffee before it gets cold.
5. Creating dash boards using excel conditional formatting:

As I said before you can use conditional formatting to create intuitive sales reports or analytics outputs. Like the one shown here,
dash board how to using excel

Here is how you can do it:

  • Copy your data table to a new table.
  • Empty the data part and replace it with formula that can go like this (I am using the above table format to write these formulas, may change for your data)
    =ROUND(C10,0) & " " & IF(C9 Essentially, what we are doing is, whenever the cell value is more than its predecessor in the data table we are appending the symbol a–² (go to menu > insert > symbols and look for the above one) etc.

  • Next, conditionally change the colour of cell to red / green / blue or pink (if you want ;) ) and you are done
  • Show it to your boss, bask in the glory :)





As acquisition closes, what lies ahead for Googorola?

Google has completed its $12.5 billion purchase of device maker Motorola Mobility in a deal that poses new challenges for the Internet’s most powerful company as it tries to shape the future of mobile computing.

The deal closed Tuesday, nine months after Google Inc. made a surprise announcement that it wanted to expand into the hardware business with the most expensive and riskiest acquisition in its 14-year history. The purchase pushes Google deeper into the cellphone business, a market it entered four years ago with the debut of its Android software, now the chief challenger to Apple Inc.’s iPhones.

In Motorola, Google gets a cellphone pioneer that has struggled in recent years. Motorola hasn’t produced a mass-market hit since it introduced the Razr cellphone in 2005. Once the No. 2 cellphone maker, Motorola now ranks eighth with 2 percent of the worldwide market share, according to Gartner.

As had been expected, Google CEO Larry Page immediately named one of his top lieutenants, Dennis Woodside, as Motorola’s CEO. He replaces Sanjay Jha, 49, who will stay on just long enough to assist in the ownership change.

Woodside, 43, has spent the past three years immersed in online advertising as president of Google’s America region, which accounted for $17.5 billion of Google’s revenue last year. Motorola Mobility Holdings Inc. booked $13.1 billion in revenue during its final year as an independent company.

Nevertheless, Woodside’s background in online advertising is likely to raise questions about whether he is the best choice to oversee a company that specializes in making smartphones, tablet computers and cable-TV boxes.

“It’s a bit concerning because online advertising is quite different than the hardware business,” Gartner Inc. analyst Carolina Milanesi said. “Google is so focused on advertising that it doesn’t consider that kind of thing.”

Google depends on digital ads for 96 percent of its revenue, which totaled $38 billion last year.

In a statement, Page praised Woodside as an outstanding leader who has “been phenomenal at building teams and delivering on some of Google’s biggest bets.”

The takeover became possible only after government regulators were satisfied that the acquisition wouldn’t stifle competition in the smartphone market. China removed the final regulatory hurdle by granting its approval Saturday. Regulators in the U.S. and Europe had cleared the deal three months ago.

Google wants Motorola largely for its trove of 17,000 cellphone patents, which the search company can use to defend Android phones against lawsuits accusing them of copying key features from the iPhone.

But in recent months, Google has been signaling that it has been drawing up more ambitious plans for the newly acquired hardware business.

Macquarie Securities analyst Benjamin Schachter believes Google is particularly interested in developing a snazzier tablet computer powered by its Android software to compete against Apple’s hot-selling iPad and Inc.’s Kindle Fire.

Owning a handset and tablet manufacturer will also allow Google to exert more control over how Android runs on the devices. That has been difficult for Google to do because it gives away Android to other hardware manufacturers, which can tweak the software to suit their own agenda.

In moving beyond its expertise in search and software into manufacturing a wide range of equipment, Google will test its ability to keep Android partners, shareholders and employees happy.

Google will have to reassure its Android partners such as Samsung Electronics Co. and HTC Corp. that Motorola’s devices won’t get souped-up versions of the software or receive other preferential treatment.

If it appears Google is favoring Motorola, manufacturers might consider building their own mobile operating system or defect to Microsoft Corp.’s Windows software, which is getting a major facelift this year.

“This gives Google a chance to develop and showcase a ‘next generation’ device for mobile computing,” said N. Venkat Venkatraman, a Boston University professor specializing in technology and management. “But it could also create a complex issue for Google. How do you balance the desire to create something that consumers love without upsetting the rest of the Android ecosystem?”

Milanesi suspects Google might also try to design a Motorola smartphone that caters to the needs of companies and government agencies.

“Like almost everything Google does, I think they will try a lot of different things and then do whatever is best for them,” Milanesi said.

Signaling its intention to experiment, Google said it has created an “advanced technology and projects group” at Motorola. It will be run by Regina Dugan, a former director of the U.S. Defense Advanced Research Projects Agency, or DARPA, which specializes in coming up with national security innovations. DARPA was how the Internet got its start more than four decades ago.

In a statement Tuesday, Motorola spokeswoman Jennifer Weyrauch-Erickson said the plan under Google’s ownership is to make “fewer, but bigger launches.” She said Woodside wasn’t available for an interview.

Motorola’s cable-TV boxes could provide Google with a springboard for delivering more of its services, including advertising, to living rooms. However, cable companies control the market for set-top boxes, and they resist any intrusion into their realm.

Google also will likely have to do some hand-holding with investors who have been worried about Motorola’s troubles eroding Google’s hefty profit margins.

“If it looks like Motorola is just a lab or toy for Google, investors are going to be asking themselves whether the company is spreading itself too thin,” Venkatraman said.

As its line of smartphones has waned in popularity, Motorola has suffered losses totaling $1.7 billion during the past three years. Google has earned $25 billion over the same stretch.

Page already has decided to operate Motorola separately partly because of the contrasting fortunes of the two companies. That will make it easier for investors to track how the different lines of business are faring. For now, Motorola will continue to have its headquarters in Libertyville, Ill., far from Google’s Silicon Valley home in Mountain View, Calif.

Google shares fell $13.17 or more than 2 percent, to close Tuesday at $600.94.

Turning around Motorola will likely require layoffs, a painful process that belies Google’s carefully cultivated image as a cuddly employer.

Google laid off about 300 people in 2008 after it paid $3.2 billion to acquire online advertising service DoubleClick Inc., which was previously the biggest deal in the company’s history. The cutbacks represented about one-quarter of the workforce that Google inherited from DoubleClick. If Google imposes a similar reduction on Motorola’s 20,500-employee payroll, it would translate into about 5,000 layoffs.

Taking on so many new employees also raises the risk of cultural clashes with the 33,000 people already working at Google.

Motorola Mobility is one half of the old Motorola Inc. It split at the beginning of last year. The other half, Motorola Solutions Inc., is still independent. It sells police radios, barcode scanners and other products aimed at government and corporate customers.