Now I wouldn’t say I’m a professional Excel master, but I have been called an excel wizard before, so I would say that’s pretty good too. Excel is an amazing tool, especially when it comes to analyzing data, and it is without a doubt one of the reasons I was hired. I even had to take an Excel exam with my boss in the room to test my skills in the middle of my interview to see how well I could use the program (A+ on that one!).
Excel is one of the main programs that I use day in and day out, so I’ve become pretty familiar with it. One of my main tips in regards to using it is if you don’t know how to do something – google it! That’s how I have learned half of what I know, and the other half is from asking questions. If you think there is an easier way or a specific way of doing something, someone else has probably thought similar and shared their experience online, just like this! These are a couple of my favorite tips that I’ve learned along the way that constantly have people impressed and also save me lots of time.
Control+Shift+L This is my favorite one that I think my fingers have muscle memory of. Holding down Control + Shift + L will turn on or off the filters on any table or set of data. This is really helpful because you don’t have to go up to the menu and click the filter button.
Control + Arrow Keys This is a helpful little trick that can make you move faster around a large excel file. Simply hold the control key and press whatever arrow in the direction you want to go. Want to jump to the bottom of 2,000 rows? Control + Down Arrow. Go over the column AC? Control + Right Arrow. I feel like I sound like a nerd, but this was is wonderful, especially when you’re in a meeting and need to move around quickly.
Conditional Formatting This can be extremely helpful, especially because now you can filter by fill or text color. In most formats of excel is right next to the formatting section of the home tab. There are lots of different options, finding negatives, top values, but my favorite style of conditional formatting is by far finding duplicates. This is helpful when you’re copying in a lot of data or about to enter a lot of values into a form and need to make sure every value is unique. Look in the highlight rules section of the dropdown menu to find it.
Vlookups These can be kind of difficult, but they can save lots of time in spot check and matching up different sets of value. The point of a vlookup is to do a range look up to match a unique value in two sets of data. I would suggest looking at some youtube clips and looking into this more and to keep experimenting with them when you have the time! I was always extremely nervous to try and use them because I didn’t know how they worked, but once you get the formula down it’s super easy. The function in excel is basically :
vlookup(the value you want to find,where you are looking the value up in, what column you want to pick data from, true/false)
To pick the column you want, you just count the columns over from the first one in the range you are looking up and write that column’s number
For true/false you usually just picked false. This way any value that can’t be found will show up as an error and you’ll be able to find it easily.
It seems intimidating if you don’t use these regularly, but vlookups will save you hours of trying to match up data! If you need help also feel free to use the excel formula wizard (clicked the italicized f underneath the menu bar) and it will give you instructions and a nice form to fill out instead of writing the equation yourself.
If Statements I love if statements. They help out with decision making by basically creating a condition, and if that condition is true then it will spit out one value and if it’s false it will spit out another. This can be helpful, for example, if you are looking for only negative values. You could write an if statement that says if one cell is negative, then write true, and if not then write false. The basic format is:
If(conditional statement, solution if true, solution if false).
So for the example above it would look like:
If(A2>0, “True”, “False)
Helpful hint: any time that you want it to write out a word or not a number value, you need to put that expression in quotations!
I know these can seem a little confusing, so I made this little card with these tips to help you remember! Feel free to print it out and put in next to your computer or save the photo to look back on later. Let me know in the comments your favorite excel tips and if you have any questions!