One – Learn how to enter data into Excel
A big big chunk of working effectively with Excel is getting your data set up right. So basically that means, if you have repeating data e.g. training records, invoices, transaction, they should be entered as a single big mother of a list. Why? So that when you want to compare data over months or years – you have it all in one place. So when someone asks you – what happened to X in Y? – all you have to do is go and filter it.
See this file for an example of what a properly set up list looks like.
- Have your headings in one cell. Make them bold so Excel knows they are a heading
- Every entry should be entered like it is the first time you have entered it – yes, I know you are repeating data, yes I know that seems like a lot of work but trust me on this….it’s the difference between doing some hard work now – and having it easy later on. Or having it hard now, then spending a hard day every month to do something that should take 20 minutes.
- Keep blank rows and columns to a minimum- preferably not at all.
- Be consistent: Mayo should always be entered as Mayo. Not “Co. Mayo”, “Co Mayo”, “County Mayo”. Yes, I know YOU KNOW they are the same. Excel doesn’t. Use dropdown lists for data you will enter more than once.
Two – Know the basics
I never ceased to be amazed by the number of people who come on an Intermediate or Advanced Excel course who do not know the basics.
- Basic formulas : plus (+), minus (-), multiply (*), divide (/). Click where you want the answer to go. Type in = . Click on the cells you want to use. Press Enter. Copy down or across. Get the first one right and copy it.
- Basic functions: =Sum(a2:a4) adds up all the numbers in the cells A2:A4 . Minimum, maximum, average, count, counta – they all follow this pattern.
- Fixing a cell – use F4 to fix a cell (or row or column) you want to use over and over again in your formulas.
Three – learn keyboard shortcuts for all the stuff you do over and over.
For example, you should know Ctrl and C to copy, Ctrl and V to paste, Ctrl and X to cut. Ctrl and + – inserts a column or row (assuming you have highlighted the row or column you want to use) . Ctrl and minus deletes it. You can find a list of shortcuts here.
Four – really learn how to use Vlookups and If functions – and get ready to be worshipped
worship
Ah yes, recently I was teaching a class and one of the participants told me that he had been in China and they had hired someone to compare two lists – a list with 6000 plus entries. They were going to do this manually….he showed the a vlookup and they got it done in minutes. I think they are looking into setting up a shrine to him.
You can view tutorials on If functions here, IF/OR functions here and vlookups here.
Five – Learn how to use pivot tables and prepare to be canonised – “The world of Work runs on Powerpoint and pivot tables.”
That is a quote from a course participant I had. And it’s becoming more and more true. A joke in the IT world is that the most used function (ha!) in IT – after OK and Cancel (I’d dare to put Undo in there as well) is “Send to Excel”…and solid Excel skills will only enhance your work life.
If you have to summarize, compare large amounts of data you NEED TO KNOW HOW TO USE PIVOT TABLES. You can find the first tutorial in a series I did here..
Thanks for this post. Sounds all pretty basic but one would be amazed about how much of this so called “basic stuff” is unknown…
If you would want to list 7 instead of 5 clever ways, I would add named formulas (aka named ranges according to Microsoft) and increased usability through form elements (the dropdowns you mentioned go in the same direction) , especially if you share your files with other users.
hi Phil, oh yes indeed an awful lot of Excel users – particularly newbies- really really do not know this stuff. I could tell you some horror stories about printing – people who have printed out stuff, cut up the pages, glued or stapled it together and then photocopied it…..If the only thing I ever achieved in my Excel training was to stop people doing that…really that would be my life well spent! I rather like your ideas of the named ranges – particularly if used with tables to make them dynamic and dropdowns – also created using a table to make them dynamic. But I find that in a lot of my classes newbies struggle with the stuff I have mentioned in the article so that’s what I tend to focus on. Thank you so much for your comments thought..much appreciated 🙂