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.

Click here to learn more about our VBA Classes & join us today.

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.

Then master critical functions like VLOOKUP, INDEX, MATCH and SUMIFS. Refer to Podcast session 2 & session 7

Take up formula challenges. See our Excel Homework page for inspiration.

Join a course – Both Excel School & Excel formula crash course are excellent for learning formulas.

Get a book for quick reference. I recommend The VLOOKUP Book, Excel 2013 Formulas

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.

Go visit popular visualization websites like Flowingdata or NY Times Upshot. Try to imitate the charts in Excel.

Learn the rules of beautiful visualizations from books like Visual Display of Quantitative Information & Information Dashboard Design.

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

Explore 5 powerful ways in which it can help you

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.

7. Dashboards

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:

• Sales dashboards
• Salary analysis dashboards
• State-to-state migration analysis dashboards
• Expense analysis & tracker dashboards
• More than 200 dashboard examples.

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.

See if any of the data management steps can be automated. Examples: 1, 2, 3, 4, 5

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

Learn basics of trend analysis, regression & forecasting

Understand how to use data tables for business problem modeling

Get a good book on statistical analysis (college level should do)

Also the 3 part “Are you Trendy” series is worth reviewing

Are you Trendy – Part I

Are you Trendy – Part II

Are you Trendy – Part III

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

Learn the basics of Power Pivot.

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.

Still struggling?

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.