Watching the Olympic athletes run & jump all I could think of is,
- What should I eat to jump & sprint like that?
- How come I never heard about steeple chase?
- Should we really have 3 bullet points in all lists?
But I digress. Coming back, when watching one of those hurdles events, I got an idea as sharp as Chinese table tennis team.
Why not create a hurdles game in Excel to measure how good you are with keyboard?
So ladies & gentleman, let me present you our very own Olympics hurdle run.
Excel Hurdles Challenge
What is it?
This is a simple spreadsheet based game. Your objective is to reach from Start cell to End cell in shortest possible time, ONLY USING KEYBOARD.
Hurdles challenge eh?!? What rules I must obey?
- The first rule of hurdles challenge is you use ONLY KEYBOARD.
- You must touch cells on the red trail only.
- You are not allowed to land on blank cells unless.
- For each violation of above 3 rules, you get 1 second penalty.
- That is all. Happy jumping & sprinting.
Download Excel Hurdles Challenge
Click here to download the challenge workbook.
Just enable macros and get jumping.
Note: this workbook is tested in Excel 2007, 2010 and 2013 and works ok.
How fast did you go?
When I tried it first time, I got 23 seconds. But after a bit of practice, I got to 17 seconds and no penalties. While I am no Usain Bolt, I feel pretty happy with result.
What about you? How fast can you go? Go ahead and take the hurdles challenge and post your time in comments.
Once you reach your personal best, go enjoy the weekend. When we meet next week, I will have something awesome, something new and something smart waiting for you.
Struggling with the hurdles? You should learn a few keyboard shortcuts.
Check out below pages to pick up some keyboard shortcuts. Once done, go back and take the challenge.
- Comprehensive list of keyboard shortcuts
- 10 Keyboard shortcuts I can’t live without
- 100 Excel tips, tricks & shortcuts
PS: If you have more time to kill, check out these Excel games.
8 Responses to “Pivot Tables from large data-sets – 5 examples”
Do you have links to any sites that can provide free, large, test data sets. Both large in diversity and large in total number of rows.
Good question Ron. I suggest checking out kaggle.com, data.world or create your own with randbetween(). You can also get a complex business data-set from Microsoft Power BI website. It is contoso retail data.
Hi Chandoo,
I work with large data sets all the time (80-200MB files with 100Ks of rows and 20-40 columns) and I've taken a few steps to reduce the size (20-60MB) so they can better shared and work more quickly. These steps include: creating custom calculations in the pivot instead of having additional data columns, deleting the data tab and saving as an xlsb. I've even tried indexmatch instead of vlookup--although I'm not sure that saved much. Are there any other tricks to further reduce the file size? thanks, Steve
Hi Steve,
Good tips on how to reduce the file size and / or process time. Another thing I would definitely try is to use Data Model to load the data rather than keep it in the file. You would be,
1. connect to source data file thru Power Query
2. filter away any columns / rows that are not needed
3. load the data to model
4. make pivots from it
This would reduce the file size while providing all the answers you need.
Give it a try. See this video for some help - https://www.youtube.com/watch?v=5u7bpysO3FQ
Normally when Excel processes data it utilizes all four cores on a processor. Is it true that Excel reduces to only using two cores When calculating tables? Same issue if there were two cores present, it would reduce to one in a table?
I ask because, I have personally noticed when i use tables the data is much slower than if I would have filtered it. I like tables for obvious reasons when working with datasets. Is this true.
John:
I don't know if it is true that Excel Table processing only uses 2 threads/cores, but it is entirely possible. The program has to be enabled to handle multiple parallel threads. Excel Lists/Tables were added long ago, at a time when 2 processes was a reasonable upper limit. And, it could be that there simply is no way to program table processing to use more than 2 threads at a time...
When I've got a large data set, I will set my Excel priority to High thru Task Manager to allow it to use more available processing. Never use RealTime priority or you're completely locked up until Excel finishes.
That is a good tip Jen...