Visualize state to state migration data and you could win an iPad or Galaxy Tab [Datavis Contest 2014]

Posted on April 9th, 2014 in Excel Challenges - 43 comments

Time for another dashboard contest. Sit straight, set aside your appointments and get number-crunching.

Analyze state to state migration data between 2012 & 2010 and present your results in a dashboard (or charts) and you could walk away with glory, fame & and an iPad.

State to State migration contest - Prizes

If all of this sounds tempting, read on.

The contest

  1. Download dataset for the contest [mirror]
  2. Analyze the data and create a dashboard or set of charts (see rules below)
  3. Submit your workbook by 20th of April (Sunday), 2014.
  4. If readers at chandoo.org and our panel of judges love your work, you get one of the cool prizes.

About this data

Every year, US Census organization surveys a sample of residents (age 1 or more) and asks them whether they lived in same residence 1 year ago. If someone moved, they collect details of previous residence (state). This data is published on State to State migration page on census website.

I downloaded the migration data for last 3 published years (2012, 2011 and 2010) and cleaned it up for this contest.

Goals for Analysis

  • Help us understand state to state migration flows & trends.
  • Highlight interesting facts & trends

How to send your entries

Once you are ready to send your entry,

Email your entries:

  1. Check the rules (below) and make sure you have followed all of them.
  2. Email your workbooks to chandoo.d @ gmail.com with subject “Datavis contest 2014
  3. Please include your name & best email address(es) so that we can reach you if there is any clarification needed.
  4. Please mention the version of Excel used.
  5. Please send in your entries before end of 20th April (Sunday), 2014.

Send them to our Facebook fan page:

  1. Join Chandoo.org fan page
  2. Post your entry as a message
  3. Attach your workbook. Mention your name, email and Excel version used.

The Rules

  • You can make a dashboard or 1 page report
  • You can make several individual charts (or tables) too as long as they all fit in to one window.
  • You can make interactive or static charts
  • You can also use VBA to add interaction to the charts.
  • You cannot use add-ins for creation of the output.
  • You can add data to this dataset (for example, macroeconomic data, weather conditions, demographic data etc. from public sources like govt. databases or websites)
  • You can use Excel 2013, 2010, 2007, 2003 or Office 365. Please note that this contest requires use of Excel.
  • You can submit more than one entry. But only one entry per individual is considered when awarding prizes.
  • Your files must be unlocked & any macros you use must have enough comments so that others can learn.

The Prizes

The winner of this contest will receive an iPad Air 16 GB (wi-fi model, valued at $499).

For the 2nd best entry, you get an iPad Mini 16 GB (retina, wi-fi model, valued at $399)

and for 3rd best entry, you get a Samsung Galaxy Tab 3 (wi-fi model, $199).

So there you go. 3 gorgeous gadgets up for grabs.

Note: Since prizes are delivered thru Amazon.com, in case they cannot ship the item to your location, you will be offered $ amount.

Disclosure: I am using affiliate links to mention the gadgets. That means if you click on them and buy, Amazon pays me a few $s.

How do we pick the winners?

Once all the entries are received, then we will open voting.

  1. All entries will be published on Chandoo.org and you can vote for winner.
  2. A panel of judges (names to be revealed later) will also vote. Judges votes carry higher weight.
  3. The entries with highest votes will be winners.

PS: If we get a lot of entries, we may eliminate a few during voting stage.

So what are you waiting for?

Go ahead and download the data [mirror]. Analyze and share your results.

Need some inspiration?

Check out past contest result pages to get some inspiration.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

43 Responses to “Visualize state to state migration data and you could win an iPad or Galaxy Tab [Datavis Contest 2014]”

  1. Lubos Pribula says:

    Hi Chandoo,

    what about the standard Excel 2013 add-ins like Power Pivot, Power View and Power Map? Are we allowed to use them?
    I refer to the rule of not using add-ins.

    Thanks to make this clear.

    • Chandoo says:

      @Lubos… for this contest, Power Pivot or any other Power BI add-ins are not allowed. (reason: a majority of our visitors still use earlier versions of Excel. We want them to learn as much as possible and that can be done only when we have dashboards made without Power BI features).

  2. Jon says:

    What exactly do you mean by “fit in to one window”? Does that mean a single worksheet or one view on a screen? And if the latter, what screen size should we assume (though now I as I write this, I wonder if that’s really an issue)?

    Thanks.

    • Chandoo says:

      It just means your dashboard / report / or set of charts should be 100% visible when Excel maximized on a typical computer screen. This is mentioned just to avoid situations where one creates a dashboard with 1000 rows and 100 columns (standard widths). Dont worry if yours is not like that.

  3. Mustafa says:

    Hi Chandoo,

    Could you give a little more explanation on your data arrangement in the excel file, how to read and understand the columns from column J onwards, and the data below in row I68 below.

    Thanks,

    Mustafa

    • Chandoo says:

      Hi Mustafa…

      To know more about the data, see source links mentioned in the workbook.

      In my data workbook, all I have done is re-arranged the data provided by census website (after removing a few un-necessary columns and rows).

  4. Nation says:

    Hi Chandoo,

    Will backward compatibility have any bearing on the judgment of an entry (including in a tie-break situation)? i.e., Could an entry potentially be judged less favorably for utilizing functionality not supported in 2003, 2007, etc. (be it VBA or built-in) – versus one that is fully functional/supported in 2003-2013? Just curious.

    Thanks!

  5. Niyaz says:

    Can PowerPivot be used?

    • Chandoo says:

      Hi Niyaz… No. Please use only standard Excel features (formulas, tables, VBA etc.). Power Pivot is an add-in, so it does not qualify. (Same for Power View…)

  6. Viral says:

    I am not able to understand the data. could you please help me..

  7. Viral says:

    The total figures are not matching ,, eg. column C – United States 305628607, Total of all the below names is 309305100, only if Puerto Rico is ignored than the total matches. I have already said I am not able to understand the data, it will be of great help if you could please explain the same at the earliest.

    • Chandoo says:

      Please visit census website link in the data spreadsheets to understand this data better. The totals should exclude Puerto Rico as it is not a state in USA.

  8. Viral says:

    The same is happening in all the columns

    • Balli says:

      Hi Viral,

      I try to explain the data as I understood and request Chandoo or forum memeber to correct me if I am wrong.
      First I explain the Data for one state i.e. “Alabama”.
      * Data- 4764428 (Cell Address: C11) is total population of Alabama. Out of which Data- 4054260 (Cell Address: E11) are residence of this state in last year also. And Data- 590326 (Cell Address: G11) are currently residence of Alabama but they were in different states last year.
      Second- State of Residence Last Year
      Data- 104600 (Cell Address: I11) is people came to Alabama from diff states. This is sum of range (K11:BH11).
      Data- 109210 (Cell Address: J10) is people left to diff states from Alabama. This is sum of range (J12:J61).

  9. Jon says:

    Do we need to include our calculation worksheets or just the charts?

    Thanks.

  10. Niyaz says:

    Hi,

    Can we create a single page dashboard with options to show and hide different charts in different views using macro based on user selection. Will that qualify? Or will you consider only the main view?

    • Chandoo says:

      Yes, you can. However, we would appreciate if you can show everything on the same sheet (instead of jumping to another). Again, it is up to you and you can submit multiple entries.

  11. Jal says:

    Is this contest only open to US citizens?

  12. Nila Khresna says:

    I want to join this contest
    but I do not understand to a file that you provide.
    of the file we are asked for what?
    make a chart or worksheet or summarize per census analysis for a country or for the projection of the census year.
    Can give a view to the worksheet data?
    thanks

  13. Niyaz says:

    Should Puerto Rico be skipped??

  14. Niyaz says:

    What is the cut off time and date for submission? Am from IST timezone.

  15. sreekhosh says:

    OMG Missed this Opportunity!!!!!!!!!!!! unfortunately I saw this contest today…… :( :(
    @chandoo.
    Do you have any plan to extent the date of submission.?

    Hopefully
    Sreekhosh

    • Hui... says:

      Sreekhosh
      I’m sure if you submit it asap it will be reviewed
      All the more reason to be a subscriber here !

    • Chandoo says:

      @Sreekhosh… Please note that we have more than 50 people participating in this contest with some world class dashboards. It would unfair for them to give extension. However, you can send your submission tonight / tomorrow morning. We will consider it only for publication but will not able to include it in final set of contest entries for prizes.

  16. sreekhosh says:

    Hui/Chandoo Thanks for your Reply.

    I think one day is not enough to compete with masters of excel. But I will not miss next time. Bcoz I learned the basics and techniques of creating dashboard form Chandoo.org only.

    Thanks
    Sreekhosh

  17. permana says:

    I hope my entry wasn’t too late (april 20th, 8pm in delhi timezone), and looking forward to see more than 50 fabulous entries.

    rgrd,
    permana

  18. krishna says:

    Hi,

    When will the results be out?

  19. Muhammad Ikhlaq says:

    no file is opening, what is the reason?

  20. Ram says:

    Dear Chandoo,
    We all are waiting for the result!!!!!!!

  21. Tyler Barr says:

    Can’t wait for the results — It seems like a long time ago that I heard about this competition :)
    have a nice weekend all

  22. Gangadhar says:

    I am waiting for results… Do we know when can we get it?

Leave a Reply