Yusuf , One of the blog readers, sent me an interesting email the other day. Here it is, reproduced in full.
I have been reading your blog for the last six months and have found it to be very enriching. You blog has inspired me a lot. I have decided to undertake the quest of becoming from an intermediate excel user to an excel ninja and ultimately CEO (Chief excel officer).
This is my plan of action:
1) Refer a excel book to revise and refresh excel fundas. Trying to create a strong foundation. Would be great if you can recommend One books to cover this part.
2) Refer 2 or 3 excel blogs. Got thru all the posts of last couple of years to bring me to the advance level. Thinking of using PHD and Mr Excel
3) Read a formula related ebooks
4) Final step would be learning VB macro to qualify as a Ninja
5) Am think of using Excel 2007Any suggestions or feedback is welcome. Will update you periodically on my progress.
Wish me Luck !!
I am obviously flattered to be included in his list of “websites to deep dive”. But I think his plan for Excel Awesomeness can be improved. Here is my advice to him,
- Learning excel alone is probably useless. You must learn how to apply excel to your industry, area of work to improve existing processes or way of working. To that extent, any plan to improve your excel skills should begin with a plan to improve your understanding of the industry and area of work.
- For eg. if you are a business analyst, you could learn about requirement gathering, structuring your thoughts, estimation, project planning etc.
- You should invest in story telling, communication skills as well. Learning how to present, how to write and how to talk can be of great help. Conveying ideas in short but powerful ways is more important in todays world.
- Resources: Presentation Zen, Copy Blogger, Seth Godin’s blog, Made to Stick
(book)
- Resources: Presentation Zen, Copy Blogger, Seth Godin’s blog, Made to Stick
- You should develop strong design (UI) sense.
- Resources: Smashing Magazine blog, Non-Designer’s Design Book, Don’t Make Me Think (book)
- Resources: Smashing Magazine blog, Non-Designer’s Design Book
- You should develop your programming skills. understand basic programming structures and learn how to modularize your code (and structure your thoughts).
- Resources: C Programming Language by K&R, Data Structures Using C, Excel VBA Programming For Dummies
- Resources: C Programming Language
- Stay open to new tools (this has been one of my weak areas). Explore upcoming technologies like R, Tableau, Google Visualization API, Processing,js to expand your horizon and ideas.
- Resources: Learn R, R Project, Tableau Public, Google Docs
(book)
, 
(book)
by K&R, 
, 
But I think My advice can be improved too. So I turn to you.
What do you think a person should do to become really awesome in Excel?
Share your thoughts, ideas and suggestions using comments. Suggest blogs, websites or books or anything that can make Yusuf (and countless others) really good in using Excel so that they can be awesome in their work.
Go!
34 Responses to “How to become really awesome in Excel? [Reader Questions]”
I don't believe you can be considered an advanced Excel user unless you are at at least intermediate stage in Access too. There are a few reasons for this:
1. People will persist in developing apps in Access that would be quicker, simpler and easier to use and support in Excel. You will almost certainly have to migrate a solution from Access to Excel some day.
2. Conversely, some things are very tricky in Excel that would be a breeze in Access. (Although you can build a relational database in Excel, it'll take you a lot of time and trouble and you'll only be reinventing the wheel.) You can't expect muggles to know this, so you need to be able to help them.
3. Learning to use logical data structures will save you untold grief in Excel. Access is where you can learn this most effectively.
4. You can't do much in Access without good VBA skills. You really need these to become an Excel champ.
Ray
I agree Chandoo!
I have used spreadsheets since 1986, and Excel since 1991 along with BASIC/Assembler/Pascal programming. The one thing holding me back in Excel Guru attainment is the lack of application!
I wrote hundreds of sheets for my former employers, lacking Access I put together management reports, Scheduling, and even works order systems all in Excel! This inspired me to improve my skills, which I have over the last 2 years.
At the moment between jobs, I spend a lot of time on excel help forums answering questions, I find this is a good way of improving your skills solving problems for others!
If I cant answer the question, I read others replies and learn how to, which I think is a better way than reading every post, attempt the answer before reading though!
hmmm its a worthy aim to be awesome in Excel.. I would like this a too...
At the moment I am learning by trying to do things like Pivot tables, Pivot tables with external data, learning by reading blogs like this one, excel books, vba books, by joining Chandoos Excel School...
However it is frustrating when things do not work and it takes so long to understand why! Also time to fit in learning advanced techniques while keeping up with all the other things in life.
I encourage you to apply what you learn and you learn by doing...
Good luck
Catherine
Would just like to add teaching/mentoring. Nothing solidifies your understanding of something technical like trying to explain it to somebody else. If you can bring one or two budding Excel power users under your wing then this may help you to become great at Excel.
Just a thought.
I would also add:
Interesting post! I'd add the PTS blog (Jon Peltier's blog) as your third blog. I agree with Chandoo's suggestion to stay open to other tools. Some things are much easier to do outside of Excel.
I would also add:
+ Business Systems Analysis (understand data flow in your business), look for opportunities to streamline/simplify it where ever possible
+ Keep an eye out for people who do repetitive tasks (I've automated a few people out of roles)
+ Ability to Listen (from above and below)
+ Understand corporate data storage and naming conventions or aim to develop them
None of the above are Excel related, but Excel is just a tool and understanding how that tool fits into the environment is more important
+ Read and Try
+ Try small parts of big problems and don't move on until you understand what it is and it is doing what you want.
+ Don't try and invent Rome in a Day, give yourself a week or at least a few days.
+ Learn how to use search engines and use them well, Rarely will you come up upon a problem that hasn't been tackled similarly somewhere else
+ Join a few good blogs and read and ask questions, contribute to forums to assist other people (even if you don't know the answer - Challenge yourself to learn something new)
and maybe have a read of a similar post here:
http://www.dailydoseofexcel.com/archives/2009/01/15/how-to-learn-excel/
Ahhhh gee.....
IMO, it's not always knowing about a different formula or having some vba skills. More often than not, it has to do with your ability to get meaningful source data.
Just saying.
But I agree:
-Learn access or firebird. Either gives you a tremendous amount of power to accomplish some of your jerk work very quickly
-Learn to communicate well with your consumers. This one is tricky. Report consumers say they want either:
A. A watered down explanation of what they really want. They don't know the power of teh excelz, so they give you something that they think you can accomplish.
B. A pipe dream that really can't be accomplished. Or shouldn't be accomplished. Learn to apply the "just because I can, doesn't mean I should" rule.
You can deliver C. C is a happy medium.
-Quickly make use of new tricks you learn here and elsewhere.
I'd add to these lists being a little bit of "Excel Yoda". In a lot of cases, you are developing excel worksheets for others to use. Your boss asks you do something or colleague bugs you for help. First, try to understand if this is one-off or going to stick to you for ever. Then, see through the problem that requestor is trying to solve. Don't chase your tail just because somebody asked you to do something in excel. Once you clearly see the problem that excel is trying to solve, then apply your excel ninja ninja.
As far becoming excel ninja, I believe that best way to learn is to try teach somebody else.
Cheers!
Shorusan
Great question Yusuf!
Read..
Read everything you can find. There are a lot of great bogs out there, don't limit yourself, I think I look at 10 or more very frequently. Try out all the tips. Download everything you find and really get under the hood and try it out. Go to Amazon. See what books are available on the subject. Maybe you can find them inexpensive /used on Amazon or check out at your Library)
Research..
Check out many of the formus or Usenet groups (Google Groups) There is a lot of great information out there. You just have to look for it (Google search).
Forum post..
Ask question at one of the Excel forums (Mr Excel or OzGrid are examples) Search first, chances are, someone may have already asked the question.
Business need..
Folks above make an excellent point. Sometimes it is difficult to envision what to do and how to do it until you have a business need or business case to do so. Read what folks are asking for help with on the forums and see if you can come up with an answer. Look at all of the workflows at your place of work and see if you can come up with improved processes leveraging the power of Excel.
Have fun..
IMHO, you have to have fun. When I'm stuck or feeling a little down, I look at Chadoo's blog or Mike Alexander's blog (bacon bits). Then I'm back in the game. I see how much fun these guys have and it re-inspires me to dive back in.
Best wishes, Yusuf. I hope your journey is as excellent as mine has been.
I'd say before you start building anything in Excel you need to know a thing or two about designing tables and graphs. A recommend read is "Show Me the Numbers" by Stephen Few.
Then I'd say good overall Excel knowledge is important. For that I can recommend the "Excel 2007 Bible" by John Walkenbach.
And like others have said a good understanding of the business you're in is very important. Knowledge of how to build a spreadsheet is great but if you don't know what to put in it it's pretty useless 🙂 I would advise reading an Excel book that's related to your job. If you're in finance for example there are quite a few books which discuss financial analysis with Excel.
When you're done with that you can find other books and resources for the areas you want to expand your knowledge in. Good luck!
I have increased my level of expertise with Excel over the last few months. I did this just by using google search and visiting excel MVP websites (like Chandoo). I have saved every macro code, add-ins etc. It has made my work far more efficient, easy and productive. I also help my coworkers with their excel problems with ease using macros. I also create dynamic dashboards and other reporting tools . My work is still using Excel 2003. I am looking forward to 2010 version for more features and more learning opportunties. I would like to see more web interface features with excel which will make it even more powerful. I am not sure if they provide that in 2010.
I agree with other comments above that Access is also an integral part of data-analytics and visualization. It would be nice if Chandoo can start a blog on Access also. Thanks for everything my man
... from an honest desi.
VJ
VJ makes a great point,
I wish from the beginning I had a tool like Code Warehouse or similar to arrange all code snippets, links, thoughts, formulas - hery helpful.
Disclosure,
I am not affiliated with Code Warehouse in any shape, form or fashion
The advice to learn about data structures, and in general to look into other languages, is sound, however I find it a bit weird that you would recommend C language books. While C is a great language, IMO, it is more productive to look into a modern OO language like C#, VB.NET or Java, because they are much closer to VBA, and because if you want to become an Absolute Ninja, you will probably look into .Net and VSTO at some later point 🙂
I am more like a "quazi" ninja. I search for macros online and tweak it to meet my needs. I am not an expert in VB. I can understand when i see the code and I make adjustments to suit my needs. A great way to learn using copy and paste and it saves me a lot of time. I am thankful to those who post their codes online for others to use.
VJ
DIVERSIFY.....
Learn Web Analytics - read Avanish Kaushiks blog Occams Razor, make yourself valuable by being able to apply in different arenas.
Absolutely - great post - could not agree more on the point about "Learning Excel alone is useless" I specialise in Financial Modelling and whilst Excel skills are absolutely critical, there is a lot more to it than that. I wrote an article on this recently: http://www.fimodo.com/2010/05/what-to-look-for-in-a-financial-modeller/
Danielle Stein Fairhurst
Plum Solutions
@Chandoo Learning excel alone is probably useless. I disagree (slightly).
Learn it, and jobs will follow. Opportunity favors the prepared mind. etc.
I had a boring job with hardly any use for Excel. I picked up a copy of Walkenbach's excel bible, and worked my way through it on work time. (There's some great references in the back of these). Then I found the MrExcel podcasts and worked my way through them. Then I purchased Walkenbach's Mr Spreadsheet's 2007 library (a whole bunch of his books at a great price) and basically read through them one by one, usually at night just before bed. (rather than putting me to sleep, I'd have to force the books closed after midnight).
Then I spent a heck of a long time playing around with pivot tables and Charts (discovering Jon Peltier's second-to-none resources at the same time), worked my way through them, and then after 18 months added 'advanced excel user' to the bullet points of my CV. While I'm not advanced compared to the bloggers I follow, I'm certainly advanced compared to the bosses that have hired me since then.
Incremental, continued learning is what you need. Recently I learned SQL (which is much easier than VBA and probably almost as handy if you're working with data) , which allows me to get data from Access, Sql Server, or even other excel workbooks. I'm only now starting to tackle VBA seriously.
You should invest in story telling, communication skills as well. Couldn't agree more. Grab yourself some books from Edward Tufte, or Stephen Few, to learn how to do this with pictures. Grab yourself a copy of Mike Alexander's Excel dashboards for dummies while your at it. Don't worry about how much money you spend on good books, because they are a fantastic investment.
I have been using spreadsheets since 85 (Lotus 123, Quatro, Quatro Pro, Excel) and believe that you can do almost anything in Excel, with a bit of imagination...and knowing many of its functions. Good understanding of data matrices also helps.
But the ONE THING which you absolutely need to progress in Excel (and I believe in any other SW application) is the REAL NEED to DO SOMETHING. That SMETHING should be ambicious enough to almost make you to give up.
It is no good to just learn functions and tricks, if you don't use them.
Regards
Jose Lourenco
Use ASAP Utilities. A must have utility for excel. It has saved me hundreds of hours.
Yusuf,
It's great that You want to became awesome in Excel. I think that the best way is to solove real-life problems. By real-life problems I mean not only problems which you can stumble upon in Your work but also helping others on newsgroups and forums.
I think that knowing excel is not sufficient. You should consider learning at least basics of programming, relational databases and SQL.
When you will be able to write vba code, you will be able to automate Your spreadsheet. Knowing databases and SQL will help you to retrieve data from external sources, which in the end You will present in excel.
Excel is great tool for certain purposes and if you are not aware of other technologies you will try to solve every problem with excel which is not good idea because you should try to use proper tools to certain problems.
@All.. excellent inputs. Very useful & practical advice.
I have forgotten to mention about the power of sharing. Any path to knowledge becomes much more enriching and entertaining when we share. we can use the power of internet to help others need. I would recommend starting a blog or becoming active in a forum / newsgroup as a sureshot way to excel awesomeness.
On the Access front, I would add that instead of focusing on "access" one should focus more on "database design, RDBMS fundamentals and SQL". These are generic concepts that will help you whether you are designing a dashboard or building an app. Especially knowing how to structure data for a certain report to be produced can speed up the whole development process. Of course, focusing on "access" is good if your work involves building access based solutions.
Now to specific replies... (gosh, I have to replace this commenting system with threaded comments)
@Mathias: "The advice to learn about data structures, and in general to look into other languages, is sound, however I find it a bit weird that you would recommend C language books. "
Very good point. Of course one can use C#, .NET or even Java to learn programming. But often these things come with a lot of extra baggage (like dev environment with a ton of windows and toolbars, lots of libraries, pre-built code for almost anything from search to sort) and most literature on these languages focuses on the features of language than programming fundamentals (my opinion based on what I read). That is why I think C is best for a beginner programmer. It is stripped down and very much like an empty canvas. (plus both K&R and Data Structures books are exceptionally well written).
But if the obvious goal is to write VBA code, then one should get some basics and then spend more time learning VBA than programming fundamentals.
@Jeff... "I disagree (slightly).
Learn it, and jobs will follow. Opportunity favors the prepared mind. etc. "
Very interesting perspective. You are right. But you are also fortunate. In my experience I have seen very few "excel only" roles in recruitment ads.
Many thanks to everyone for their input.
I have realized that learning excel thoroughly is the first step. The real challenge lies in applying the right tool to a problem and getting the task done.
@Chandoo: Thanks 🙂
Gostaria de obter mais conteúdos sobre o excel. Do mais básico ao mais avançado, juntamente com os exercícios. Como faço pra conseguir?
[...] Chandoo adds plenty wise tips, but I wanted to add one of my own: [...]
Some Problems in my Excel working. I'm attached the VBA module in excel Add-in for always running when I'm open my excel then automatically open my module in all excel file. but then I'm attached the module and save in add-in. But then add in "Microsoft excel option\addin\go"
then give the massage
"Microsoft office Excel Security Notice
Warning : It is not possible to determine that this content came from a trustworthy source. You should leave this content disabled unless the content provides critical functionality and you trust its source.
File Path: c:\.........\addins\File name.ext
This application add-in has been disabled. Add-ins might contain viruses or other security hazards.
More Information. [Enable Macros] [Disable Macros]"
If I'm click the "Enable Macros" then work correctly but when I close the excel and then again open the excel so if I'm use the function of my module then excel give me error is "#Name!"
So please if you solve me matter then give me the solution of my problem,
I'm waiting for any body response.
Thanks
Sohail Khan
Hi Chandoo,
I need to buy a copy of (Chandoo.org) MS Excel 2010 book, can you please help me how can i get it.
Thanks & Regards,
Swamy
@Chaluva... I have not authored any books yet. But you can get some from the list I recommend: http://chandoo.org/wp/shop/excel-vba-books/
I NEED RESULT SHOULD BE
DESCRIPTION PAC STOCK PYS STK IN BOOKS DIFFRENCE -1.094
ITEM (A) 100X1 100.002 98.008 -1.994
ITEM (B) 6X1 50.004 56.001 5.997 5.003
I NEED SOME EXCEL FROUMULA
@Harish
What do you need a formula to do ?
If
100.002 is in C2
98.008 in D2
E2 should have =D2-E2
Copy that down to E3 etc
[...] How to become awesome in Excel? [...]
How to use filter in microsoft excel and what extent help us in job/programme and even data report
This is an excellent thread, I'm so glad someone asked the question.
I agree 100% that there are few Excel-only jobs advertised. I've been looking for one for years so I could just play with my best friend all day.
It is paramount that you learn what Excel is capable of. Finding out how is the easy part. (Google, etc.) My experience with earlier data bases gave me a clue as to what to look for in Excel. I always found it.
Don't be afraid to ask a question in a blog. The only dumb question is the one you didn't ask. None of us were born Excel Experts. For every one of us there was a time when we knew absolutely nothing. It is a good idea to demonstrate with a bit of code that you have tried. Even if it doesn't work that code makes it easier for others to understand what it is that you are trying to achieve.
I have to wonder if, in reality, there is such a thing as an Excel expert. As soon as we think we know it all Microsoft comes out with a new version for us to learn.
As someone said earlier "Rome wasn't built in a day". You have to allocate free time to your own R&D. I remember when I first realized that I had no choice but to become proficient with INDEX/MATCH and SUMPRODUCT. I had to give myself the time to learn, experiment, seek further advice, practice and apply these functions to real world situations.
Now I'm off to clear down a shelf in my library for some new books then I'll set time aside to click on all the links above.