All articles with 'Microsoft Excel Conditional Formatting' Tag

Compare 2 sets of data by letter or word & highlight mismatches [vba]

Published on Dec 15, 2014 in VBA Macros
Compare 2 sets of data by letter or word & highlight mismatches [vba]

We analysts like to compare. If you ever want to keep an analyst busy, just give her 2-3 options. She won’t return to your desk until the cows come home. My wife uses this trick all the time. Picture this:

[In late 2013]
Me: I want to buy a new phone
She: Do you want Nexus 5 or Galaxy S5 or iPhone 5s?

Its late 2014 and I am not done comparing.

So today, let’s talk about an interesting comparison scenario.

Comparing by letter or word

See above demo to understand the concept. Read more to learn how to do this.

Continue »

Excel to the Next Level by Mastering Multiple Occurrences

Published on Dec 9, 2014 in Learn Excel
Excel to the Next Level by Mastering Multiple Occurrences

This is a guest post by Sohail Anwar.

August 29, 1994. A day that changed my life forever. Football World Cup? Russia and China de-targeting nuclear weapons against each other? Anniversary of the Woodstock festival?

No, much bigger: Two Undertakers show up at WWE Summerslam for an epic battle. Needless to say: MIND() = BLOWN().

And thus begun one boy’s journey into understanding the phenomenon of Multiple Occurrences.

My journey continued, when just a few years later my grandfather handed me down a precious family heirloom: A few columns of meaningless data that I could take away and analyze in Excel. You may laugh but in the 90’s, every boy only wanted two things 1) Lists of pointless data and …

Continue »

A better chart to visualize “Best places to live” – Top 100 cities comparison Excel chart

Recently, I saw this chart on Economist website.

It is trying to depict how various cities rank on livability index and how they compare to previous ranking (2014 vs 2009).

Best cities to live - Chart from Economist.com

As you can see, this chart is not the best way to visualize “Best places to live”.

Few reasons why,

  • The segregated views (blue, gray & red) make it hard to look for a specific city or region
  • The zig-zag lines look good, but incredibly hard to understand % changes (or absolute changes)
  • Labels are all over the place, thus making data interpretation hard.
  • Some points have no labels (or ambiguous labels) leading to further confusion.

After examining the chart long & hard, I got thinking.

Its no fun criticizing someones work. Creating a better chart from this data, now thats awesome.

Continue »

CP021: How to quickly compare 2 lists in Excel

Published on Oct 2, 2014 in Chandoo.org Podcast Sessions, Excel Howtos
CP021: How to quickly compare 2 lists in Excel

In the 21st session of Chandoo.org podcast, lets compare lists. Quickly

What is in this session?

Comparing things is a favorite pastime for analysts all over the world. Sadly, it is also an area where we waste hours. So in this episode, I share my top secret comparison techniques to save you time.

Note: This is a short format podcast. That means you spend less time listening to it, while becoming more awesome.

In this podcast, you will learn,

  • Why I sound like I am on a secret mission at a mafia hideout.
  • 5 ways to compare 2 lists
    • Manual method
    • Conditional Formatting
    • Row Differences
    • LOOKUP formulas
    • COUNTIF formulas
  • Bonus tip: Removing duplicates
  • Conclusions
Continue »

CP011: 5 Excel magic tricks to impress your boss

Published on Jun 19, 2014 in Chandoo.org Podcast Sessions
CP011: 5 Excel magic tricks to impress your boss

If you want to create magical effect with your Excel workbook (or report, dashboard, model), then hear no further. In this episode, we explore 5 very powerful magic tricks you can apply to get jaw dropping reactions from your bosses, clients & colleagues.

In this podcast, you will learn,

  • Annoucements
  • Why magic
  • 5 Excel Magic Tricks
  • 1: Conditional formatting
  • 2: Form controls + Charts
  • 3: Pivot tables + Slicers
  • 4: Macros + Automation
  • 5: Using right feature @ right time
  • How to learn these magic tricks
  • Conclusions
Continue »

Top 10 things we struggle to do in Excel & awesome remedies for them

Top 10 things we struggle to do in Excel & awesome remedies for them

Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.

Continue »

Excel Dashboards – 49 dashboards to visualize US State to State migration trends

Hello everyone. Stop reading further and go fetch your helmet. Because what lies ahead is mind-blowingly awesome.

About a month and half ago, we held our annual dashboard contest. This time the theme is to visualize state to state migration in USA. You can find the contest data-set & details here.

We received 49 outstanding entries for this. Most of the entries are truly inspiring. They are loaded with powerful analysis, stunning visualizations, amazing display of Excel skill and design finesse. It took me almost 2 weeks to process the results and present them here.

49 Dashboards to visualize State to State Migration - Chandoo.orgExcel Dashboard Examples - Visualizing state to state migration trends - Chandoo.org

Click on the image to see the entries.

Continue »

Modeling tiles in a room using Excel Conditional Formatting

Published on Apr 22, 2014 in Charts and Graphs, Learn Excel
Modeling tiles in a room using Excel Conditional Formatting

Last week we learned how to answer questions like, “How many tiles in a room?” using Excel. We learned about CONVERT function and fraction number format settings in Excel.

But why stop at calculation? We can even model a room full of tiles, thanks to Excel’s grid nature.

So today, we will learn how to create a room layout as shown above, using Excel.

Continue »

Free Invoice Template using Excel – Download

Published on Mar 19, 2014 in excel apps, Learn Excel
Free Invoice Template using Excel – Download

Anyone running a small business knows the oozing bits of joy when you hear a customer saying, “Can you send me an invoice?”

While creating an invoice is an easy task, if you want something that is professional looking, easy to manage and works well, then you are stuck.

That is where Excel really shines. By using an invoice template, you can quickly create and send invoices.

Today I want to share one such template with you all. Why? Because we are awesome like that.

Continue »

6 Best charts to show % progress against goal

Published on Mar 10, 2014 in Charts and Graphs, Learn Excel
6 Best charts to show % progress against goal

Back when I was working as a project lead, everyday my project manager would ask me the same question.

“Chandoo, whats the progress?”

He was so punctual about it, even on days when our coffee machine wasn’t working.

As you can see, tracking progress is an obsession we all have. At this very moment, if you pay close attention, you can hear mouse clicks of thousands of analysts and managers all over the world making project progress charts.

So today, lets talk about best charts to show % progress against a goal.

Continue »

Quick and easy Gantt chart using Excel [templates]

Published on Feb 18, 2014 in Charts and Graphs, Learn Excel
Quick and easy Gantt chart using Excel [templates]

Gantt charts are a very popular way to visually depict project plans. Today, let us learn how to use Excel to make quick & easy Project Plan Gantt Chart.

This is what we will be creating.

Continue »

Top 10 things I learned using Excel for a decade

Published on Jan 6, 2014 in Charts and Graphs, Learn Excel
Top 10 things I learned using Excel for a decade

Sometime during the 2nd half of 2013, I finished 10 years of Excel usage. In the last 10 years, I completed my studies, got my first job, married, had kids, visited 15 different countries, quit my job to start a business, bought first car, first house, made dozens of new friends, read 100s of books, wrote a book and learned 1000s of new things. And all along, Excel stayed a true companion. Right from MBA entrance exam preparation in 2003 to making my summer internship project reports in 2005 to planning my wedding expenses in 2007 to getting a promotion in 2009 to planning my kids feeding schedule in 2010 to running a successful business in 2014, Excel helped me in every step.

So today, I want to tell you the top 10 things I learned using Excel in last decade. Grab a hot cup of coffee, buckle your belts and get ready for time travel.

Continue »

Free 2014 Calendar, daily planner templates [download]

Published on Jan 2, 2014 in Learn Excel
Free 2014 Calendar, daily planner templates [download]

Here is a New year gift to all our readers – free 2014 Excel Calendar & daily planner Template.

This calender has,

  • One page full calendar with notes, in 4 different color schemes
  • Daily event planner & tracker
  • 1 Mini calendar
  • Monthly calendar (prints to 12 pages)
  • Works for any year, just change year in Full tab.
Continue »

Thank you Sachin [a small tribute]

Lets talk about people who inspire us. People who show us that anything is possible. People who prove that commitment, hard work and perseverance are true ingredients of a genius.

I am talking about Sachin Tendulkar. Those of you who never heard his name, he is the most prolific cricketer in the world. He is the leading scorer in both tests (15,921 runs) and one day matches (18,426 runs). Read more about him here.

Tendulkar has been an inspiration for me (and millions of others around the world) since I was a kid. The amount of dedication & excellence he has shown constantly motivates me. It is a pity that the great man is retiring from test cricket. He is playing his last test match (200th, most by any person) as I am writing this.

So as a small tribute, I have decided do something for him. Of course, I have never been a cricketer in my life. Once in college I was reluctantly asked to be a stand-by player in a game with seniors. I did not get a chance to pad up though. That is the closest I have been to a cricketer. So I did what I do best. Create an Excel workbook celebrating Sachin’s test career.

Thank you Sachin – his test career in a dashboard

Here is a dashboard I made visualizing his test cricket statistics. It is dynamic, fun & awesome (just like Sachin).

(click on the image to enlarge)

Sachin Tendular 200 Tests - Visualized in an Excel Dashboard

Continue »

Closing gaps in this Gender Equality Gap chart…

Published on Nov 5, 2013 in Charts and Graphs
Closing gaps in this Gender Equality Gap chart…

Today lets close some gaps.

Recently I saw this interesting chart on Economist Daily Charts page. This chart is based on World Economic Forum’s survey on how women compare to men in terms of various development parameters. First take a look at the chart prepared by Economist team.

So what are the gaps in this chart?

This chart fails to communicate because,

  • All country charts look same, thus making it difficult to spot any deviations.
  • We cannot quickly compare one country with another on any particular indicator.
  • It does not provide a better context (for eg. how did these countries perform last year?)

But criticizing someone’s work is not awesome. Fixing it and making an even better chart, that has awesome written all over it. So that is what we are going to do. You can see the improved chart above. Click on it to learn how you can create it.

Continue »