Advanced Data Manipulation with Excel Functions
Advanced Data manipulation in excel is a important topic for people who are looking to become more efficient in excel. Imagine having to highlight every cell that meets a condition manually or even looking up a piece of data among 1000’s and 1000’s of rows. Dreadful right?
Thankfully with tools like conditional formatting or VLOOKUP we can speed up the process. These tools can help reduce errors and automate tasks. What are you waiting for then? Let’s see how we can do just that!
Conditional Formatting
Applying Conditional Formatting
Conditional formatting if you couldn't guess by the name is a way to apply a specific format based on the condition you have set. This format will only apply to the cells which the condition has returned “TRUE”.
Why would you even want to use it right? Well Lets say you had a table of data that includes the names and salary each person made. We want to label the ones that made above a certain threshold with a certain color and so on.
With conditional formatting we will be able to highlight important information based on the specific condition we set. Thus saving us time going back and forth to see which row meets our condition.
Steps to Apply Conditional Formatting
- Identify the Range: First lets highlight the range that we want to format of course.
- Create a New Rule: Go to the home tab and click on conditional formatting and select “New Rule”. In our case we are going to choose “Use a formula to determine which cells to format”.
- Enter the Formula: When entering a formula remember that we start from the perspective of the first cell that is being highlighted so if the first cell that shows the salary is in b6 we put b6. So if we wanted to see which cells are ≥$80,000 and its in cell a1 then we do “$b$6≥$a$1”.
- Apply Formatting: Now we click on format. Then we choose our desired format like a color. Lets choose green. Wait their is one more thing. We need to make one small change to our formula. “b6≥$a$1”. Okay much better. We remove the dollar signs because we don’t just want to compare that one cell we want to compare the entire range we selected against the exact cell where our condition was.
- Manage Rules: If we want to change a rule. We can go to conditional formatting > manage rule. Then change as necessary.
- Highlighting Rows: Lets say we had names and salary and we didn’t just want to highlight one column but both. We cant use this formula “b6≥$a$1” we need to change it to this “$b6≥$a$1”. This locks the column to the column with the value so we can always return the correct format instead of it shifting to another column. Thus we can apply the condition of the value column onto the name column.
- Copy and Pasting with Conditional Formatting: Lets say we copy and paste a table and we want to clear the formatting that was applied. We can copy and paste the table and then select it. Then go to conditional formatting and clear rules. Another way is to copy your original table. Then right click and go to paste special and select formulas and number formats and press ok.
VLOOKUP Function
Understanding VLOOKUP
The VLOOKUP function purpose is to help you search for data in a much faster way. Especially when it comes to larger datasets. Its formula is written as so “=VLOOKUP(Lookup_value,table_range,coloum_num,[false/true]”
If your data set had 1000 rows and you wanted to find the department a certain employee worked at. You would normally have to go through each row one by one. This is where VLOOKUP comes in! With this function something that would take hours can take just a minute.
Steps to Use VLOOKUP
- Identify the Lookup Value: Lets say we want to find what department Reining works at. For the first Parameter we will select the cell that contains “Reining”.
- Specify the Lookup Range: Here we will highlight the range of the table that we will be looking for our value in. Something important is that our desired result which is department column has to always be on the right of the lookup value column which is employee names.
- Result Column Number: Next we will define with a number which column contains the department results and we start counting from 1.
- Range Lookup: Finally this one is optional but I highly suggest you don’t ignore it especially in this case when we are looking for a EXACT match. Type FALSE or 0 to get an exact match or don’t type anything for an approximate match which is preferably used with sorted data only.
Tips for Using VLOOKUP
If you are going to drag the formula so that you can also see the department name for the other employees make sure that the table range is fixed with the $ signs.
You can use VLOOKUP across different sheets following the same exact steps. Neat isn't it?
As mentioned before you can use approximate match to find the closest value as long as the data is sorted. Like if you wanted to assign students a letter grade given by a list of respective scores sorted in ascending order.
Common Errors and Solutions
I can’t stress this enough when you are looking for “EXACT” matches please put “FALSE” in the last parameter of the formula.
Also make sure to handle extra spaces in your lookup value with functions like “TRIM” that I will discuss after or else it will return N/A.
Ensure that the result column is at the right of the lookup value.
If you don’t want to see those N/A errors and would like to see something a bit less cryptic and more friendly. You can wrap the VLOOKUP function inside a IFERROR function.
TRIM Function
Purpose
The TRIM function helps remove extra spaces and leaves only single spaces between words. It takes in one parameter called text.
This text parameter is a string of text of course. It removes spaces to right of a sentence for example and the left and if their is more than one space in between words it will be reduced to a single space.
Steps to Use TRIM
- Syntax: “TRIM(text)”
- For example if we had a text like this “ hi how are you”. The space to the right would be removed to this “hi how are you”.
CONCATENATE Function
Purpose
The concatenate function combines text from different sources.
Instead of having to write something over and over again and it already exists somewhere else we can bring it together in one place.
Steps to Use CONCATENATE
- Syntax: “CONCATENATE(text1,text2,text3…)”
- Lets say we want the full names of employees in a single cell and we conveniently have their first and last names separated in other cells. We can use the function to combined them as so. “=CONCATENATE(first_name, “ “, last_name)” you might have noticed you also need to add space if you want their to be space between your text.
PROPER Function
Purpose
If we have text that is mixed with uppercase and lowercase and all messy and we just want the first letter of each word to be capitalized we can use the PROPER function.
It will change all the words to lowercase except the first letter of each word turning to uppercase if it wasn't already.
Steps to Use PROPER
- Syntax: “=PROPER(text)”
- For example “ mAnhATTAN, nEW yORk” will turn into “Manhattan, New York”
Overall, from conditional formatting help us format data based on specific conditions we set to VLOOKUPS helping us search for data in 1000’s of rows in a matter of seconds we can safely say that these tools help us get the job done efficiently and quickly.
As always I encourage to practice these tools and functions yourself that's where true learning takes place. Once you have mastered these tools you have taken your data manipulation skills to the next step. From speeding up your workflow to impressing your boss the rewards of learning are endless. Next time we will continue to look at more functions to improve our data cleaning. Until then Good Morning , Good Evening and Good Night.
