Hello Excel Fans! I’m excited to kick off the first in a series of posts reviewing the tests of the #ExcelWorldChamp competition!
For those of you that have not seen any information on the competition, you can learn more here. In short, from October 2016-November 2016 we ran a series of Excel challenges in many countries to find local Excel Champions. In 2017, these country champs will compete for the world champ title!
Before I go any further I want to stress a few points for this post and all posts to follow...
Challenge 01-01 (Data Relays part 1)
The intent of the question was to introduce tables.
The question COULD be solved using Get & Transform (Power Query) tools to produce a query that easily updates results if your data changes. This is one reason why I love Get & Transform (and we will explore this in a later round).
For now, we’ll approach this question as having a table of data that we need to quickly manipulate once to get to an answer and not worry about needing to repeat the steps in the future if the data changes. I’ll also avoid the use of any formulas in this solution, so that we stick to just manipulating the data. This approach will be a little slow and a bit tedious, but it’s starting point for playing with data in Excel. This is not the best practice for tackling a similar problem if you encounter it in the real world…better to follow methods that will adjust dynamically if your data changes. Again, we’ll get a chance to see how much easier modern Excel can make our lives in later rounds.
Challenge 01-02 (Data Relays part 2)
The previous question set us up well to tackle this challenge. We’ll leverage some of the strategies we used but layer on some more of Excel’s built-in data tools. Again, I COULD use formulas or Get & Transform to help make this process easily repeatable, but I am going to take a more simplistic approach. I am confident my data will not change nor will I need to repeat the process later, so I am going let some of Excel’s data tools, make this challenge just a few button clicks….
Challenge 02 (Chart Gymnastics)
As mentioned before data visualization is highly subjective. That said, I was very impressed with the creativity and the thorough data analysis people incorporated into their final visualization. On the latter point especially, the community really blew me away.From my view, the challenging pieces of this data set were that it included:
I’ll point out 3 of the most liked charts by the community from Round 1 courtesy of
|@Hicham Bouhabib||@Serena Blarasin||@Anna Di Vora|
|link to post||link to post||link to post|
You’ll notice that all three of them employed the tactics mentioned above. And when they really wanted to land a message, they spelled it out in plain text with arrows or other indicators telling us where to look. It doesn’t hurt that they also did some slick formatting to provide very clean but strikingly different outputs. Another one of my favorite things about Excel – it is really an open canvas where you can explore your creative side as well as any data you might have.
For those looking for general pointers on charting in Excel, check out these 2 blog posts on Office.com. There are plenty more articles on charting tips and tricks if you search “Excel Charts” on the blog too.https://blogs.office.com/2013/10/02/telling-a-stor
Challenge 03 (Formula Wrestling)
This challenge was an introduction to formulas. The questions were intended to be straight forwarded and most can be solved with a single function, assuming you know the functions exist. If you don’t know about the functions, Tell Me can be a lifesaver. Things also get a little more complicated as we get down to questions 4 and 5. For the questions below I will share a solution that I think has a clear enough logic flow, especially for someone less familiar with Excel functions. Where I think it could be useful, I’ll also share the most streamlined (least number of functions/operations) solution submitted – often times these 2 approaches are the same. I recommended checking the video and the solution file to get the best understanding of these answers…
To those of you that made it to the end of this post – thank you! We really appreciate your interest in the #ExcelWorldChamp, this community, and Excel in general. I’ll be following up with posts on the test solutions to the other rounds of the competition shortly. Thanks again!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.