Earth Venus cosmic dance – Animated chart in Excel
Recently I saw an interesting Earth Venus cosmic dance video on Facebook. See the original video below or here.
Although this is not entirely accurate from physics & astronomy perspectives, the dance is a stunning example of patterns that are generated by simple things.
I wanted to recreate this cosmic dance in Excel. How else am I to get my spreadsheet fix on a Saturday?
Here is a quick demo of the final outcome. Read on to learn more about the Earth Venus cosmic dance.
How to create cascading drop downs in Excel – video
Cascading drop downs enhance usability of your dashboards & interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.
There are many ways to setup cascading drop downs. You can use formulas coupled with either data validation or form controls. You can also use Slicers. In this video we will review these techniques.
Narrating the story of change using Excel charts – case study
Here are three questions you often hear from your boss:
- What changes are happening in our business and how do they look?
- Do you know how to operate this new coffee machine?
- Why does every list has 3 items?
Jokes aside, our urge to find change in environment predates cave drawing, slice bread and Tommy Lee Jones. So, today let’s examine a very effective chart that tells the story of change and re-create it in Excel.
Export iPhone contacts to Excel using this free template
Recently my iPhone 4 crashed. It is 3.5 years old. And just like any other 3 year old, it started acting weird & crazy one night. The next morning it went silent. It won’t go beyond the Apple logo whenever I start it. Since I couldn’t wait for the phone to start, I took out the SIM card (the phone is unlocked, if you are wondering) and placed it in my old Nokia phone. But alas, none of my contacts are on the SIM. They are in “cloud”.
After a day of answering phone calls from everyone including my mom as “Chandoo here”, I’ve decided to get my contacts back. So I logged in to iCloud to download a backup. And the backup was a .VCF file.
Since I wanted to have all my contact numbers in a spreadsheet, I did what any Excel nerd would do. I built a template that can convert VCF data to Excel worksheet.
Looking up when data won’t play nice – few more alternatives
Recently, we discussed about the case of unwieldy data and how we lookup what we want using formulas like SUMIFS. Today, let us learn few more ways to solve the same problem.
Suitable structure spawns simple solutions
Poorly structured is the 2nd biggest problem of analysts. The first one is not enough coffee. That is why there is a dictum in the data analytics world.
Structure is everything
So, we can easily solve our lookup problem, if our data were to magically re-arranged in 2 column fashion – Data & Value.
CP015: Handling big data, Controlling model railroad sets, Overcoming Excel obsession & more – ASK CHANDOO
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
In the 15th session of Chandoo.org podcast, lets answer some of your burning Excel questions.
What is in this session?
Around last week, I invited you to ask me anything. More than 150 people responded to this call and sent in their questions. Since answering all the questions is not possible, I handpicked roughly 10 questions to answer in this episode of Chandoo.org podcast.
In this podcast, you will learn,
- How to fill blank cells with data from above
- How to work with Big data in Excel
- How to combine data from multiple sources & analyze it in Excel
- How I am managing my life after starting Chandoo.org
- How to create and distribute stand-alone Excel products
- How to control a model railroad set using Excel VBA (not fully answered)
- & more…
Handle Volatile Functions like they are dynamite
If you’re building large models, then you may want to use volatile functions – including OFFSET(), INDIRECT(), and TODAY() – with caution, because unless you know what you are doing, they *might* slow Excel down to the point that data entry is sluggish, if not downright tedious.
In fact, you *might* want to consider getting out of the habit of using these functions at all if there are alternatives, and you might want to replace volatile functions in your existing models with non-volatile alternatives…I have reduced recalculation time in large models from minutes to milliseconds by doing just that!
So what the heck does volatile actually mean? And why should you care? Let’s find out, shall we?