Top 10 things we struggle to do in Excel & awesome remedies for them
Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.
1. VBA, Macros & Automation
VBA is the most struggling area of Excel. 38 people (more than 20%) of survey respondents said they struggle writing macros, automating parts of their work, understanding VBA and developing applications using Excel.
No wonder VBA is the trickiest of Excel features. It took me more than 3 years to become proficient in VBA (and I had a head start as I learned VB programming in college). VBA is vast, technical and complicated (for those without programming or technical background).
My recommendation – learn it in a structured way
Based on my years of experience learning VBA, using it & teaching it, the fastest way to learn VBA is to learn it from ground-up in a structured manner. If you use macro recorder to learn VBA, you will not understand the eco-system of VB programming language, when to use what and what else is available. Not to mention the inefficient, bloated code generated by recorder that you will be stuck with.
I hate to sound like a salesman, but I must tell you about our VBA classes online training program. It is a step-by-step, example based, comprehensive VBA course that we launched in September 2011. Since then, we have trained more than 3,000 professionals in VBA, macros & automation. Many of them went to build impressive Excel apps, saved time & effort for their organizations and got envious promotions & hikes.
You too can overcome the fear of VBA and learn to to love it. The course offers more than 24 hours of video training material, 50+ VBA examples, 24×7 accessibility, full video downloads and more.
Other resources to learn VBA:
If you are absolutely new to VBA, you will certainly enjoy our 5 part crash course. It starts with simple introduction of VB programming language and ends with a useful, practical application built using VBA. Check it out.
Also check out our Excel VBA page for resources, tips, examples, videos, book recommendations and more.
2. Writing Formulas
Excel has hundreds of functions. But when it comes to calculating what you want, you are faced with questions like,
- Which function(s) should I use?
- How should I combine (or nest) them?
- Is there a way to write better formula?
- How to make it fast, scalable, easy to understand and auditable?
Writing formulas to analyze data & get answers is 50% art and 50% craft. While most people can memorize all the important functions, we struggle figuring out how to combine functions, how to optimize them, how to build reusable (fill-down) functions.
My recommendations – learn, read & take up challenges
Start with important functions for data analysis.
Take up formula challenges. See our Excel Homework page for inspiration.
You can get lots of practice questions at the Chandoo.org Forums
3. Making Charts
While Excel offers dozens of chart types and hundreds of formatting options, making the right chart for an occasion is the trickiest part. The plethora of bad chart formatting choices that Excel comes loaded with also make our life difficult.
My recommendations – learn, practice, imitate and simplify
Start with how to select right chart for your data
Then explore any 10 you like from our 100s charting of examples.
4. Pivot Tables
For many, pivot tables is like an invisible border. We see pivot tables, slicers and powerful calculations done with simple mouse click, and yet we go lengths to avoid them. Its almost like self-inflicted punishment when we try to mimic pivot table behavior with a mash-up of lengthy formulas and tricky macros.
But once you cross that invisible border and enter the territory of pivot tables and breathe the fresh air of instant reporting and powerful calculations, you start wondering how you ever lived without all this comfort.
So my friend, go embrace pivot tables. They simplify your life.
My recommendations – use, take what you want, leave the rest
Start with introduction to pivot tables.
Explore pivot table examples, tips, ideas and resources on our site.
Understand the limitations of pivot tables and use them to your advantage.
5. Conditional formatting
Conditional formatting is a very powerful feature of Excel. And yet, not many people play with it. But once you start to work with it, you realize how easy it is and the vital role it can play in creating beautiful reports & dashboards.
My recommendations – Experiment, learn and use often
Start with introduction to conditional formatting
See some of the examples & case studies
Use it often to loose the fear.
6. Array Formulas
The dreaded CTRL+Shift+Enter formulas are one of the most struggling areas of Excel. I am using them for more than 6 years, and even today, I see array formulas that can spin my head and kick-off a strong headache. Array formulas are Excel’s equivalent of Rubik’s cube. While normal people see mayhem & confusion, trained people see order and rules.
My recommendations – Learn, implement, challenge and learn some more
Start with any of the fine examples in our formula forensics page which is full of worked Array Formula examples.
Implement them right away. Use F9 to debug portions of the formula until you understand it completely.
Take up a challenge. See our Excel Homework page for inspiration.
Learn some more. Get the CTRL + Shift + Enter book by Mike Girvin, highly recommended.
Now a days, many businesses are relying on Excel to create dashboard reports, MIS reports, scorecards and other management reports. This means one more skill to acquire for analysts and managers. Unfortunately, there is no “Create Dashboard” button in Excel. So this leaves many confused and puzzled, when their bosses ask them to “make a dashboard from our data“.
But worry not, a dashboard is a one page collection of charts, information and action points to support decision making.
My recommendations – Learn, Create and Conquer
Start with this incredibly useful and simple KPI dashboard tutorial (6 parts)
Enroll in Excel School Dashboards program. There is no better way to learn Dashboards. Period.
Explore some of the world-class dashboards from our contest entries:
Go make your first dashboard. Then repeat.
8. Working with data
Another challenging aspect of Excel is, bringing data from a variety of sources, structuring it inside Excel, connecting one data set with another, managing it and analyzing it effectively. Getting data is the first step of anything we do in Excel. And often we struggle a lot doing this first step correctly.
My recommendations – Tables, Power Query, connections and automation
Learn unbelievably powerful and versatile table feature of Excel.
Install Power Query and play with it if you can.
NOTE: Very soon I will be writing tutorials on Power Query. Stay tuned.
Learn how to connect to various data sources from Excel. These are simple steps and you can access them Data ribbon.
9. Forecasting, Regression & Trend Analysis
These are 3 of the most frequently used statistical analysis in business situations. And not surprisingly, quite a few of us struggle doing them in Excel.
My recommendations – Get basics right, implement and expand your knowledge
Get a good book on statistical analysis (college level should do)
Also the 3 part “Are you Trendy” series is worth reviewing
10. Power Pivot
Power Pivot, the newest & most powerful feature of Excel continues to puzzle many of us. Features of Power Pivot like DAX formula language are tricky to understand for seasoned users of Excel due to mindset shift required. But once you understand them, they seem infinitely useful and intuitive.
My recommendations – Learn, Experiment and Explore
Enroll in Power Pivot Classes. It is my comprehensive online training on Power Pivot aimed to make you awesome.
Get a book. I recommend Rob’s DAX formulas for Power Pivot.
If you are still struggling in Excel, at least you are at the right place. Just visit our archives and start reading any one of the 500+ Excel articles that you fancy.
Wishing you fewer struggles and more awesomeness.
Introducing our Online Power BI Class:
Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.Click here to know more and join us.
Leave a Reply
|Excel Dashboards – 49 dashboards to visualize US State to State migration trends||CP011: 5 Excel magic tricks to impress your boss|