Have you ever received the following question during an interview?
“so, how well do you know to work in MS Excel?”
Like most people, you probably said:“I am pretty good, I know how to use Excel”.
What does good really mean? some people thing that good is knowing how to work with filters and make a sum on a column. For others good is when you know how to use macros and code in VBA. So which one is it?
In this article I made a list of 10 things you should be able to do in Excel in order to say that you are good in Excel (note that I said good, not master or ninja). The list is driven from my recruitment experience and Excel courses that I deliver from time to time.
Let’s start with some easy ones and work our way to something more complex.
1. Add a new row in a cell
So, firts line in a cell you write the company name and one row bellow (in the same cell) you want to write the address.
I know you already know this, ALT + ENTER, let’s move on to the next one.
2. Freeze panes
Remember that there once was a table that had 1754 rows and 18 columns. It was the table that broke your scroll wheel, because you had to keep going up to the table header to see the name of the column.
To save your next mouse, I propose using the freeze panes option, that can make your top row visible even when you scroll down.
3. Filter and sort, even on multiple levels
Yes, I think that you should not only know, but master everything about filters in Excel. It does not matter if you want to filter by a few items, by cell color, or by smell (you can’t do that yet 🙂 ). You should be skilled enough with filters to do that.
Sorting is also important, besides that simple A-Z sort that we all probably know, I wonder if you know how to sort on multiple levels.
Radu, what is sorting on multiple levels?
You know that company contacts list, I am talking about being able to sort people first by department and then by name.
4. Data validation
When you are the only one working in the documents you’ve created you don’t worry too much about correcting data entry errors.
If however you are working on complex reports together with others, you might want to look into data validation, so you don’t waste your time correcting other people’s mistakes.
You know what I am talking about, some people just don’t understand that they should type a date in the date column and not something like Tuesday.
5. Conditional formatting
There are times when we want some things to stand out. This is why be bold them or make them red.
As an example, if you worked on budgets, you probably wanted to “bold and red” the categories where you’ve spent more than planned. Making one cell red is no big deal, making 100 is not fun anymore.
This is where conditional formatting comes in handy, you should know about it.
6. Arithmetic conditional functions
I think that Excel without functions is like a hipster without a smartphone and selfie stick. 🙂
To talk about all functions in Excel is a book subject in itself, so I won’t get into much detail here. I just want to point out that you should be able to use a countif(s) or a sumif(s) function when needed.
7. The VLOOKUP function
You hear a lot about VLOOKUP in multinational companies. The reason is simple, as they have too many reports per capita there, they are interested to see how they can grab information from one report to another, to speed things up.
If you don’t know that this function does, then it’s about time to Google it.
I think you will agree with me when I say that you cannot say you know how to use Excel until if you don’t know at least to make a pie chart.
I am not talking about selecting some data and hoping that Excel makes the chart exactly the way I want it to be. Even though Excel is getting better and better, it has limited mind reading capabilities.
9. Pivot table
We all have pivotal moments when everything changes. You know, when you finish school, get married, etc.
When you first understand how a pivot table does and how it works it’s something similar. 🙂
For me id made a great difference while I was working in a large-scale company and had to a weekly report that took an hour. After understanding how a Pivot works, it took about 3 seconds. That gave me an hour more to focus on … more important things like Facebook. 😛
10. Data import
You made it to the bottom of the list, congrats. The last one is knowing how you can import data from one workbook to another.
Note: copy paste does not qualify as data import, it’s just copy paste.
By knowing data import you can make reports talk one to another, without your intervention. This is really cool and can save you lots of time, it sure did for me.
What did I miss
I am sure that I missed something, or added things that shouldn’t be on the list. Tell me what you think in the comments bellow.