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.
If all of this sounds tempting, read on.
The contest
- Download dataset for the contest [mirror]
- Analyze the data and create a dashboard or set of charts (see rules below)
- Submit your workbook by 20th of April (Sunday), 2014.
- 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:
- Check the rules (below) and make sure you have followed all of them.
- Email your workbooks to chandoo.d @ gmail.com with subject “Datavis contest 2014“
- Please include your name & best email address(es) so that we can reach you if there is any clarification needed.
- Please mention the version of Excel used.
- Please send in your entries before end of 20th April (Sunday), 2014.
Send them to our Facebook fan page:
- Join Chandoo.org fan page
- Post your entry as a message
- 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.
- All entries will be published on Chandoo.org and you can vote for winner.
- A panel of judges (names to be revealed later) will also vote. Judges votes carry higher weight.
- 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.
43 Responses
Hi ,
Something on the lines of this chart ?
https://sites.google.com/site/e90e50fx/home/talent-traffic-chart-with-chord-diagram-in-excel
Narayan
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.
@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).
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.
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.
Just to increase the benchmark.. and to make our judges comfortable.. 🙂
http://www.census.gov/dataviz/visualizations/028/
http://vizynary.com/2013/11/18/restless-america-state-to-state-migration-in-2012/
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
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).
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!
No. You can make the workbook in any version of Excel and use native functions and features of that version.
Can PowerPivot be used?
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…)
I am not able to understand the data. could you please help me..
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.
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.
The same is happening in all the columns
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).
Do we need to include our calculation worksheets or just the charts?
Thanks.
@Jon
The whole box and dice including calculations
Otherwise how do we know you haven’t used an external package ?
Hui…
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?
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.
Is this contest only open to US citizens?
@Jal.. no, it is open for everyone.
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
@Nila
The file contains three years of state by state movement data
It is up to you how and what you present
Should Puerto Rico be skipped??
What is the cut off time and date for submission? Am from IST timezone.
You can submit by 12 noon on 21st of April, Indian time.
OMG Missed this Opportunity!!!!!!!!!!!! unfortunately I saw this contest today…… 🙁 🙁
@chandoo.
Do you have any plan to extent the date of submission.?
Hopefully
Sreekhosh
Sreekhosh
I’m sure if you submit it asap it will be reviewed
All the more reason to be a subscriber here !
@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.
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
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
Hi,
When will the results be out?
@Krishna & All… we are processing the entries. Initial set of results & poll will be published in 2 weeks. Final results may be out in about 4 weeks from today.
We all wait with baited breath… 😀
no file is opening, what is the reason?
Dear Chandoo,
We all are waiting for the result!!!!!!!
I’m looking forward so much to the results – want to be inspired :)))
Stef@n
Can’t wait for the results — It seems like a long time ago that I heard about this competition 🙂
have a nice weekend all
I am waiting for results… Do we know when can we get it?
@All,
The result is expected next week (2nd week of June).
Source: http://chandoo.org/wp/2014/05/20/excel-links-delay-in-state-migration-visualization-results-edition/