Excel has a countless number of formulas and functions that you can use and learn, but when working with data, consider focusing on these 5!
1) VLOOKUP() or better yet, XLOOKUP()!
You will never find all the data in one place. That is the sad truth of working with data. Information is usually spread across different sources, sheets, files, etc. If there is one thing that can help rescue you and save hours on manual efforts, then it is the vlookup formula. Now there are more variations of this formula available that you can read about navigating the links below, but getting started with a vlookup since it has been around for ages or xlookup (a newer version) would be the easiest!
VLOOKUP function (microsoft.com), XLOOKUP function (microsoft.com)
Here is the link for all Lookup and reference functions (reference) (microsoft.com)
2) LEN()
From time to time, you will come across situations where you may need to validate different aspects of your data. For example, if looking at the student ID, verifying that all students’ IDs have the same character length. What if some students entered only the last 5 digits and dropped the first 4. Perhaps you need to check the credit card info of customers and ensure that all credit card numbers are valid. What if some customers did not enter 16 digits? The Len() formula comes in super handy in these situations. With a simple step, you will be able to identify the number of characters for all your data points and instantly identify the outliers.
LEN, LENB functions (microsoft.com)
3) LEFT(), RIGHT(), MID()
You have the phone numbers for all your clients, but you only need their area code? You have a product ID of which the 4 four digits represent the product category and you wished you knew how to automatically parse out the first 4 digits from the full product ID. Well, look no further than the Left(), Right() and Mid() formulas. These will help you do all that and more!
LEFT, LEFTB functions (microsoft.com), RIGHT, RIGHTB functions (microsoft.com), MID, MIDB functions (microsoft.com)
4) CONCATENATE()
Often times we come across data where first names and last names are in two different columns. What if you wanted them to be in the same column? The solution is easy – use concatenate() function. It allows to put together strings in seconds.
CONCATENATE function (microsoft.com)
5) IF() Statements
Let’s say that you have the final scores of 100 players ranging from 1 to 10 for a certain game. Based on their scores, you want to assign them the labels, “Winner” if the score was higher than 5 and “Loser” if the score was equal to or lower than 5. Instead of manually doing this, you could just use an IF() statement! What if you had 3 labels, (“Winner”, “Loser”, “Not Qualified”)? In this case, a nested IF() statement would be used.
IF function (microsoft.com), IF function – nested formulas and avoiding pitfalls (microsoft.com)
These are just some of the most commonly used for data analysis (or data analytics) in MS Excel / Google Sheets function. Interested in learning more, especially with an experienced industry-leading instructor in a live session? Check out the beginner-friendly Data Analytics Courses at LDA (Learn Data Analytics) – Beginner-Friendly Data Analytics Courses, Hands-on Learning, Live & Online Classrooms, Led by Industry Experts!
1800-400-5321 | info@learndataanalytics.ca | www.learndataanalytics.ca