You might think that this point will be about hard work and determination. It’s not. You might think it’s about acquiring the most knowledge. You’d be wrong. I don’t care if you’re the hardest working person in your cube farm and you have Bill Jelen in a hole in your basement. There are two things you need if you want to go from average to great.
Keyboard Centrism
Many of you are groaning even though you saw this coming a mile away. But hear me out. Even the Excel masters who aren’t as keyboard-crazy as me still use their keyboard more than the average user. To be great at Excel, or anything else, you have to use it. That means practice. That means making mistakes. That means fixing those mistakes and trying again.
Terrible analogy alert: There are two people who wish to be great at breaking up a rack of billiards balls, so they practice. The first person can re-rack the balls in ten seconds. The second person takes one minute. I can’t predict which person will end up better, but the first person will have a greater opportunity because he will have broken more racks per hour of practice.
Keeping your hands on the keys rather than in the air between the keyboard and mouse won’t make you great at Excel. But it will give you more iterations using any piece of software you want to learn (except Minesweeper).
Move your mouse to the left side of your keyboard for two hours, one day per week. You’ll learn more keyboard shortcuts in those two hours our of sheer frustration than you would in a whole day of trying to remember them.
Data Organization
Many things in Excel go from impossible to simple by changing how the data is organized. If you can’t quite get that pivot table to do what you want, it’s probably because you’re data isn’t laid out well. If that chart isn’t doing what you want, you probably need to restructure the underlying data. Go read peltiertech.com and see how my charting solutions involve setting up the data first. The answer is almost never “click the waterfall button”; the answer is usually organizing your data so it works with how charting features work.
You don’t need any formal training to learn how to organize your data. In fact, to get Excel to do what you want, sometimes you have to break the rules of data organization. However, I encourage anyone who wishes to excel at Excel to learn about databases. Learn what a record is. And a field. Learn the first three normal forms. Or you could buy Microsoft Access 2013 Bible
I imagine you have an opinion on how to be great at Excel. That’s the what the comments are for, so have at it.
I can speak to being pretty darn good at Excel.
One key is realizing you can make Excel do just about anything you want, probably in several ways. The trick is figuring out the simplest. Without this skill you’ll likely create disaster-prone spreadsheets. An example of this is people who’ve recently discovered VBA using it to rearrange data for presentation, when a pivot table is faster and more reliable.
Or as Mickey Rourke (kind of) said to William Hurt:
“Any time you open a spreadsheet there’s fifty ways you can screw it up, counselor. If you can think of twenty-five of them, you’re a genius. And you, my friend, ain’t no genius.”
To be good at Excel, learn from those that are better than you. Until you can hold your own. Reading and answering questions on Forums is probably the best way to do this, because you get to see real live genius at work. For instance, check out Sajan’s formula at http://chandoo.org/forums/topic/counting-unique-items-in-same-cell?replies=13#post-111705
I never would have thought of that, and now it’s in my toolkit.
And subscribing to the comments feed on blogs such as this or Contextures is another, because the comments sections is where the party is.
Chandoo has a great post on this at http://chandoo.org/wp/2010/05/10/becoming-excel-expert/
That quote is classic, Doug. But sooo true. And amen to the comment re using the inbuilt functionality, rather than programming Excel ineffecintly. Here’s a spiel I’m writing for a training course I’m putting together regarding this:
Excel is very fast at calculating, but is often programmed without realising by non-experts to do things in a very inefficient way. The larger that a spreadsheet becomes, the more all these inefficient methods multiply, to the point that very large spreadsheets can have real issues in terms of bloated file sizes and very sluggish responsiveness.
Many people mistakenly think that having to run a spreadsheet in manual calculation mode is simply a byproduct of having a big spreadsheet with lots of data and formulas in it. But in actual fact in most cases I’ve seen, performance issues are more often than not because of inefficient spreadsheet design – compounded by so-called volatile functions such as OFFSET, INDIRECT, TODAY, NOW, and RAND – rather than simply being a by-product of big files.
Make it a point to dedicate one hour a week to putter around on Excel sites.
Over the years, sites like Daily Dose, Mr. Excel, Chip Pearson, and J-Walk have fueled my Excel proficiency fifteen minutes at a time.
I still take my one hour per week stroll through Excel sites. More often than not, I see things I already know. But at least twice a month, I learn something new.
Most analysts leave work and don’t think about Excel. Want to get improve your Excel skill set? You’ve got to jump into the nerd pond and actually visit some sites.
Oh and buy an Access book too. Understanding how databases work is critical to effectively use the new BI tools like PowerPivot, Data Mining, Data Explorer.
I hear there’s a good book out there called Access 2013 Bible (in stores now).
Ferran Adria ( http://en.wikipedia.org/wiki/Ferran_Adri%C3%A0 ) said that one of the reasons behind a creative and successful food industry in Spain, combining the old with new, comes from their culture. And I think this is very true for the Excel-community as well, and something to follow if you want to become great (at anything).
He said that in Spain, the elderly cooks stopped and listened to the young chefs questions and ideas. Whereas in france, the young ones are quiet and the elder tells them what to do. So in Spain the young ones are allowed to challenge the old school, and together they try out new things with elder and young working together either to prove the young ones wrong, or to learn something new. Very similar to the scientific method. This also means that the young chefs have great respect for their teachers which in turn means they listen and learn more.
I think this is the same for the Excel-community. The old legends always lurk around forums to answer questions from us newbies with great patience. And once in a while us newbies can teach them something new since we aren’t stuck in the old ways.
Try to make things in Excel you don’t know how to make.
Subscribe to the Daily Dose Of Excel RSS feeds and make a point of reading them.
I’m also a huge proponent of learning and using the keyboard. I really like the idea of moving the mouse to the left side for a few hours. Thanks!
To become great at Excel: Never accept a cumbersome solution to any problem without investigating it yourself.
And watch/search for tutorials at youtube :)
To become great at VBA to Excel: A different story…
If you want to become great at Excel – do what all these wise people tell you. Secondly, start the basics. Understand that gaps (blank rows/spaces) are not the optimal way to work in Excel. Learn the basics of formula creation i.e. click where you want the answer to go and then write the formula (basic ones) as you learned to do maths in primary school. Get the first one right and copy. Learn how to use fixed cells. (An awful lot of Excel users really do not understand what the dollar signs do……). Learn keyboard shortcuts. Learn how to set up your lists correctly and do pivot tables. Subscribe to MVP lists. At the end of my Excel training I give the participants a list of Excel MVPs websites and explain that these are the Excel equivalent of Oscar nominees and winners. :-)
Per reading Excel blogs to become great here is a tool to make it easy to read old blog posts:
http://www.streamspigot.com/feed-playback/
I usually use it for technical blogs I want to learn. Of course, now that Google Reader is dead, I’m not sure if that tool still works or not. Hopefully it does, otherwise I’ll be missing many good old blog posts!
Great post! I’m a keyboard junky. Years ago I switched my mouse to the left side (I’m right-handed), and change the settings to left-hand mouse. This throws people off all the time, the IT people hate it. However, I you 10-key with your right and navigate with your left hand you save time and effort going from keyboard to mouse. If you data-entry a lot, I would suggest trying this. It will take about a week or two to get it down, but you will be the rock star of productivity.
Hmmm, that’s a good idea. I’ll be it goes away with Google Reader. I should make a WordPress plugin (or ask JP to do it) that creates a separate rss feed that pulls a random post from the archives. You could subscribe to the regular feed and the “rerun” feed.
Data organization, learned through struggling to become proficient at Access, is key.. When teaching others, making them realize how the data is stored vs. presented do not have to be anything similar and generally should not be. That is why a Show/Hide worksheet which automatically hides your data worksheets when activated is a simple way to separate the 2 processes. Something as simple as having a period/month column and the data in many rows vs. 12 columns simplifies summations, pivot tables, weighted averages just to name a few. Love the post and having started programming pre-Windows, the keyboard is a no brainer. I generally don’t know how to do many tasks with a mouse.
@David: That’s what I loved about the online Excel community when I joined it as a newbie on compuserve 20+ years ago, helped through my time at the helm and it’s still the predominant culture of the community even as the interaction mechanisms have changed.
@Dick: I’m a mouser. I use Ctrl+S and Ctrl+C. I click the Paste Special > Value button more than Ctrl+V.
Getting the data organization correct is a critical step to working with many of the tools in Excel, especially pivot tables. You inspired me to write a post on the proper way to structure source data for a pivot table.
http://www.excelcampus.com/modeling/structure-pivot-table-source-data/
This seems to be a commonly overlooked issue that prevents users from using pivot tables to their full potential.
Excellent article and comments. I’ve used my left hand for mouse activity for many years now and can attest to its productivity enhancement.
Dick, really enjoy your keyboard tips, especially this recent one:
Shift+Ctrl+U, it has really come in handy. Thanks.Nice article Jon. I’ve had that article in my ‘to write’ pile for a couple of months, but I guess I don’t need to write it now.
DK, I came across this WP plugin today: Old Post Promoter – unfortunately they don’t keep it up.
But here are a couple other ones:
Tweet Old Post
Buffer My Post
Unfortunately my favorite StreamSpigot old post generator died (although their twitter tool works still) with Google Reader. I wonder if there is another one out there? That would be nice! I still have some old tech blogs that I would like to read but it is a pain to read each day when I can’t just put them in my reader.