Charts and Graphs, Cool Infographics & Data Visualizations

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

Last updated on October 8, 2014

Chandoo

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).

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 they are incredibly hard to understand
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.

So I went looking for the raw data behind this graphical mess. Turns out, Economist sells this data for a meager price of US $5,625.

Alas, I was saving my left kidney for something more prominent than a bunch of raw data in a workbook. May be if they had sparklines in the file…

So armed with the certainty that my kidney will stay with me, I now turned my attention to a similar data set.

I downloaded my website visitor city data for top 100 cities in September 2014 & September 2013 from Google Analytics.

And I could get it for exactly $0.00. Much better.

This data is similar to Economist data.

Chart visualizing top 100 cities

Here is a chart I prepared from this data.

This chart (well, a glorified table) not only allows for understanding all the data, but also lets you focus specific groups of cities (top % changes, new cities in the top 100, cities that dropped out etc.) with ease.

Download top 100 cities visualization – Excel workbook

Click here to download this workbook. Examine the formulas & formatting settings to understand how this is made.

How is this visualization made?

Here is a video explaining how the workbook is constructed. [see it on our YouTube channel]

The key techniques used in this workbook are,

SUMIFS, INDEX + MATCH formulas for figuring out data
Sorting data by a particular column
Conditional formatting to show % change arrows
Form controls for user interactivity

Since the process of creating this visualization is similar to some of the earlier discussed examples, I recommend you go thru below if you have difficulty understanding this workbook:

How would you visualize similar data?

Here is a fun thought experiment. How would you visualize such data? Please share your thoughts (or example workbooks) in the comments. I & rest of our readers are eager to learn from you.

Share this tip with your colleagues

13 Comments
Ask a question or say something...
Tagged under Advanced Charting, charting, charting principles, downloads, dynamic charts, form controls, INDEX(), interactive charts, MATCH(), Microsoft Excel Conditional Formatting, Microsoft Excel Formulas, screencasts, sorting, sumifs, videos, visualizations
Category: Charts and Graphs, Cool Infographics & Data Visualizations

Want an AWESOME
Excel Class?

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.

Rebekah S

Reporting Analyst

FREE Goodies for you...

Related Tips

Excel Challenges

26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”

Stef@n says:

July 29, 2011 at 11:48 am

first solution for AND
The two numbers are in A1 and B1
= SUBSTITUTE (SUBSTITUTE (A1+B1*9*9, 9, 1), 8, 0)
regards
Stef@n

Reply
Stef@n says:

July 29, 2011 at 11:50 am

next solution for OR
=1*SUBSTITUTE (A1+A2;2;1)

regards
Stef@n

Reply
Stef@n says:

July 29, 2011 at 11:52 am

last solution for XOR
=1*SUBSTITUTE (A1+A2;2;0)

regards
Stef@n

Reply
Irvine says:

July 29, 2011 at 12:26 pm

Or you could make use of the VBA logical operators!

Define the following as custom functions
Public Function BITXOR(x As Long, y As Long)
BITXOR = x Xor y
End Function

Public Function BITAND(x As Long, y As Long)
BITAND = x And y
End Function

Public Function BITOR(x As Long, y As Long)
BITOR = x Or y
End Function

and then use them such:
A B =BITOR(A,B) =BITAND(A,B) =BITXOR(A,B)
0101 0100 0101 0100 0001

Reply
Stef@n says:

July 29, 2011 at 12:46 pm

an another solution for AND
=1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

note:
the binary numbers are in A1 and A2 !
regards
Stef@n

Reply
Shair says:

July 29, 2011 at 2:22 pm

I was obviously playing hooky at the beach during the bit-wise math lesson – you lost me at “Understanding bit-wise operations” 🙂

Reply
Anup Agarwal says:

July 30, 2011 at 8:46 am

After looking at the above solutions, I find my solution silly, but still:

For the following formulae,
Row 1: headers,
Row 2: OR
Row 3: AND
Row 4: XOR

Column 1: Input 1
Column 2: Input 2
Column 3: Result

OR
{=SUM(IF(MID(A2,ROW(OFFSET($A$1,0,0,LEN(A2),1)),1)+MID(B2,ROW(OFFSET($A$1,0,0,LEN(B2),1)),1)>0,1,0)*10^(LEN(A2)-ROW(OFFSET($A$1,0,0,LEN(B2),1))))}

AND
{=SUM(IF(MID(A3,ROW(OFFSET($A$1,0,0,LEN(A3),1)),1)+MID(B3,ROW(OFFSET($A$1,0,0,LEN(B3),1)),1)=2,1,0)*10^(LEN(A3)-ROW(OFFSET($A$1,0,0,LEN(B3),1))))}

XOR
{=SUM(IF(MID(A4,ROW(OFFSET($A$1,0,0,LEN(A4),1)),1)+MID(B4,ROW(OFFSET($A$1,0,0,LEN(B4),1)),1)=1,1,0)*10^(LEN(A4)-ROW(OFFSET($A$1,0,0,LEN(B4),1))))}

Reply
Hui... says:

July 31, 2011 at 11:57 pm

@Anup
Please don't consider your solution silly
Firstly, You are the 3rd person to submit an answer
Secondly, The best formula/function is the one that you know and understand.

Reply
Michael Azer says:

August 1, 2011 at 1:40 pm

I think I have a very tedious solution, which people won't have the patience to do except in small numbers.

I used the same problem setup as "Anup Agarwal"

AND =IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)

OR =IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)

=IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

Reply
Michael Azer says:

August 1, 2011 at 1:43 pm

Sorry my last post was totally messed up

AND
=IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)
OR
=IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)
XOR
=IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

Reply
Stephen says:

August 1, 2011 at 1:45 pm

@stefan,
I just couldn't get your solutions to work.
01010101010 + 01010101110 = 02020210120
what am i doing wrong?

Reply
Stephen says:

August 1, 2011 at 1:56 pm

@anup
...I got yours to work!

Reply
Rich says:

August 1, 2011 at 8:17 pm

@Stephen - I get the same, but Stef@an's second solution for AND does work (at least for the test cases I used)

Reply
Stef@n says:

August 2, 2011 at 9:09 am

@ Stephen / Rich
yes , you are right ! - only this works:

OR
=1*SUBSTITUTE (A1+A2;2;1)
XOR
=1*SUBSTITUTE (A1+A2;2;0)
AND
=1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

Reply
Michael Azer says:

August 3, 2011 at 1:36 pm

@Stef@n - You're answer is really smart, I never knew about the substitute function before. Great Work!

Reply
Stef@n says:

August 3, 2011 at 2:45 pm

Thx Michael 🙂
yes - it is simply easy 😉
if you add 1 and 1 - excel calculate 2
and then you have to substitute the 2 - new = 0 respectively 1

Reply
Rob says:

September 11, 2011 at 4:16 pm

Here is a good resource for people wanting to learn binary and hexadecimal.
http://justwebware.com/bitwise/bitwise.html

Reply
Kyle McGhee says:

January 29, 2012 at 3:57 pm

Three that weren't asked for:
NOT
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),1,0),3,1)

EQV
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),2,3),1,0),3,1)

IMP
=SUBSTITUTE(SUBSTITUTE(A1+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,3),1,0),3,1),0,1),2,0)

(was using Daniel Ferry's bitwise file to verify against)

Reply
Q says:

February 27, 2012 at 9:05 am

@ Kyle

Not only takes one parameter and inverts 0 -1 and 1-0

Took out the +A2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,3),1,0),3,1)

Reply
Richard Tallent says:

November 1, 2012 at 3:57 pm

Great solutions!

I'll add two:

NAND =1*SUBSTITUTE (A1+A2,2,0)

NOR=1*SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A1+A2,0,2),1,0),2,1)

Reply
John Fuller says:

August 29, 2014 at 4:15 pm

This will work for binary numbers of any size (although the text format mask will have to have as many zeroes as there are digits in the longest addend)

Assume binary #s are in C35 & C36, then add and format as text in C37:
=TEXT(C36+C35,"000000000000")
-sum- = 101112211112

AND - SUBSTITUTE 0s for 1s in -sum-, then sub 1s for 2s
=SUBSTITUTE(SUBSTITUTE(C37,"1","0"),"2","1")

OR - sub 1s for 2s in -sum-
=SUBSTITUTE(C37,"2","1")

XOR - sub 0s for 2s in -sum-
=SUBSTITUTE(C37,"2","0")

Reply
Shane says:

September 21, 2014 at 3:47 am

Just wandered by:

AND:

=SUBSTITUTE(A1+A2,1,0)/2

Reply
- xlpadawan says:
  
  September 22, 2014 at 2:22 pm
  
  Clever, Shane. I like that.
  
  Reply
How do I perform a XOR calucation of two binary numbers in excel 2007 - QuestionFocus says:

December 9, 2017 at 2:27 pm

[…] post http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html for examples using Sumproduct, and http://chandoo.org/wp/2011/07/29/bitwise-operations-in-excel/ for examples using Text […]

Reply
Ronald Vonk says:

December 6, 2018 at 12:00 pm

Hi Chandoo,

I am not (yet) really into bitwise calculation, but I am looking for a way to speed up my vba calculation with very big numbers. Would is ben convenient to use bitwise notation for this?

Best regards,

Ronald (the Netherlands)
p.s. love your country!

Reply
- Hui... says:
  
  December 7, 2018 at 4:25 am
  
  @Ronald
  
  I'd suggest asking this in the Chandoo.org Forums
  https://chandoo.org/forum/
  
  Attach a sample file with an example of some data and describe what you want to achieve
  
  Reply

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

Chandoo

Chart visualizing top 100 cities

Download top 100 cities visualization – Excel workbook

How is this visualization made?

How would you visualize similar data?

Get FREE Excel + Power BI Tips

Welcome to Chandoo.org

Mandatory Trainings: Are we doing them? [Data Analysis Challenge – 002]

FREE Calendar & Planner Excel Template for 2026

Who is my boss’s boss? [Data Analytics Challenge – 001]

New Zealand GST Calculation with Excel [Free Template]

Make a Pivot from Another Pivot Table in Excel

Related Tips

Mandatory Trainings: Are we doing them? [Data Analysis Challenge – 002]

Who is my boss’s boss? [Data Analytics Challenge – 001]

New Zealand GST Calculation with Excel [Free Template]

Make a Pivot from Another Pivot Table in Excel

How to use XLOOKUP with two sheets?

26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”

Leave a Reply

Get FREE Excel & Power-BI Newsletter

Get Started

Online Classes

About

Downloads