The VLOOKUP is one of, if not, the most important formula in Excel. During the course of any internship or entry level job you’ll have to compare multiple lists of people, places, or things and match them up. A lot of people still sadly do this by manually comparing two or more lists together. As long as you have one unique identifier (something in both lists and there is only one of them ex.: invoice number, employee ID number, etc.) you can use the VLOOKUP formula to compare two lists and bring things together in the order you are looking for. You can do this in seconds rather than hours by using the formula. Check out my post on how to construct a VLOOKUP formula: Learning Excel VLOOKUP
2. Pivot Tables
Pivot Tables are another essential part of Excel that everyone should know. Often you’ll be dealing with extremely large databases with hundreds of rows and thousands of columns. It’s impossible to look at all this data as it is and hope to ever get any valuable information from it. Pivot tables allow you to bring large amounts of data into simple tables that you can manipulate using filters and then view that data on a pivot chart. Check my post on how to create an Excel Pivot Table: Learning Excel Pivot Tables
3. IF Statement (Excel Conditionals)
When you are working with data you’ll often times have to figure something out based on certain criteria. If you break down what you are doing in your mind you can usually boil it down to an IF statement. Think in your head IF this equals that then do this, if it doesn’t equal that then do this. There ya have it! If you find yourself in this situation you could use an IF statement (otherwise known as a conditional formula) to handle the thinking for you. Not only can you use this for one row of data, but rather than going through thousands of lines of data you can use this on all of them (just drag the formula down) and all your work is done for you.
4. Excel Keyboard Shortcuts
When you’re working in Excel your moving around in small spaces and it can actually take more time and cause more errors using your mouse. That is why I recommend using the arrow keys if you just have to move a couple of cells in either direction. Tiny things like this can make your life so much easier and faster when you’re working in Excel. Another useful short cut that I use all the time is Alt+D+F+S. If you have like 20 rows filtered and you don’t need them to be anymore you can just press Alt, then the letter D, then F, then S and it will un-filter all of the columns that you had filtered. There are a lot of Excel shortcuts and they can make your life a lot easier.
Macros are how you automate Excel. You can literally have your work done for you! I’m not kidding with this. I had a job that was a Business Analyst job and part of my responsibility was reporting on a daily and weekly basis. I automated every single report. All I had to do was do a data dump from a web database into Excel, Save the file, and watch. The macros I made were timed to go off at certain times, opened up files, manipulated and formatted the data, and sent emails in outlook for me. I literally had to do nothing. Before I was there a co-worker was doing the reports manually and it took her 4 hours to do every day.
The best part about this was I DIDN’T TELL ANYONE. No one knew that I had the reports automated so I literally would go into work and surf the web (this is where the shortcut keys of ALT+TAB came in handy).
You should at least know how to record macros and how to get to the developer tab in order to do so. The macro recorder will watch what you’re doing in Excel and write the VBA code for you to automate your tedious tasks in Excel.
Filters are extremely important because you will be looking at a lot of data in large databases. If you just need one time information or just need to know something real quick it doesn’t make sense to create a whole pivot table, but it does make sense to use filters. Applying filters will put a filter on every column in your worksheet that has data in it and you can filter anyone for any of the criteria in that column, giving you the exact data you are looking for.
7. Text Manipulation
The Excel formulas LEN, LEFT, RIGHT, MID, CONCATENATE, & allow you to manipulate text which will be necessary in your internship or job. LEN gives you the character count, LEFT, RIGHT, and MID give you a given number of characters from the left, right, or middle. Concatenate and & allow you to combine multiple strings of text, whether it’s a word or a sentence you can use these to join them together.
8. Excel Charts
Excel Charts are extremely important in a business environment. Whether you need to create a whole business intelligence Excel dashboard or just a single chart knowing how to create charts, which charts to choose for a given situation, and how to make these charts look damn good (most people don’t know how to do the last step at all) is a very important thing to learn in Excel and it can set you apart from the rest
9. Combining Formulas
Formulas and Functions in Excel are AWESOME by themselves, but when you combine them they are ten times as powerful. You can do so much more combining formulas together then you can just using formulas by themselves. Take a look at the example below:
Let’s say you are using an IF statement, but you have multiple conditions that must be true. You could use a combination of the IF and the AND formula together to solve this: =IF(AND(logic1, logic2) = TRUE, Do this if it’s true, do this if it’s false)
10. Protecting Excel Worksheets
So you have done all this working, filtering data, putting in formulas and pivot tables and charts and you don’t want anyone messing with it! This is where locking your sheet comes in. You can protect your sheet so that no one else can change anything on your worksheet. This way none of your formulas get messed with and therefore none of the data that you are showing is wrong.