Here you will discover useful excel tips & tricks to learn basic excel, formauls & functions, excel charts, macros & VBA with downloadable sample files.
Here are some reasons why learning INDEX and MATCH is important:
Business Insider has ranked it in top 11 Excel skills to master. Hub Spot includes it on their 14 Excel tips to learn in 2017. And Chandoo listed it in one of the top 10 formulas for analysts.
What Experts Says
To get some real facts, I had a word with some of the experts in the community and tried to learn what are their views about INDEX and MATCH.
❝If you want to lookup values in any direction within your workbook/worksheet, then Index/Match is your tool.❞
Most of the people choose VLOOKUP because it’s easy to apply and even it’s easy to understand for a novice.
But, when you come to an elite category of users, and you are concerned about performance, flexibility, and power, this combination of index and match should be your first choice.
Ahead I have listed some points which will help you to make a better comparison.
#1. Lookup to Any Direction
The first and biggest reason for using INDEX and MATCH is you can look up to left or any other direction in your worksheet.
Unlike VLOOKUP, you can choose lookup column and result column independently. And, this ability makes it more powerful and flexible than any other lookup formula.
In the above example, we need to lookup for the amount of an invoice but with VLOOKUP we can't do that because amount column is on left side.
#2. Easy to Refer
In VLOOKUP, we need to specify the column number from which we want to return the value.
But on the other hand in index and match, we can directly refer to the value column and this makes it easy to use it.
In above table, we are using VLOOKUP to get the score according to roll number and if you want to use the name instead of roll number you need to change reference as well as col_index_number.
#3. Easy to Drag and Drop
In VLOOKUP, you need to refer to the entire table and you have to specify column index number and when you have to change the reference, you need to change the both.
But in INDEX and MATCH, wherever you need to change the reference, you just have to drag and drop it to another columns or rows.
In the above table, if we want to lookup with the name instead of roll number you just need to drag and drop the lookup column reference to name column using your mouse.
#4. Speed
In term of speed, INDEX/MATCH is far better than VLOOKUP. Charley Kyd from Excel User did a great speed confirmation test.
Insert Image
And, in his test results, he has clearly mentioned that INDEX and MATCH is the top most priority for lookup when it comes to speed.
#5. Adding or Removing Columns
As I said, in VLOOKUP you need to specify a column index number which is static values.
So, when you insert or delete a column in between the reference table VLOOKUP would give you an error or may be the wrong value.
On the other hand, if you are using INDEX MATCH there is no such kind of problem you have to face.
Quick Intro: INDEX and MATCH
Before you put together these functions you need to understand them individually. And, here you have a quick intro and syntax details about both.
What is INDEX Function
INDEX function returns a value from a column by specifying the position of the cell. You just need to specify a range and the cell number from which you need to get the value.
INDEX(array, row_num, [column_num])
Let’s say, we have a list of values in a column and you want to get particular value from that list.
The position of the value in the list is 6th row. So, now you need to refer to that column and specify the row which is 6.
Insert Image
And, index function will return that value. It’s is really simple to use but it can solve some complex for you.
You just need to specify the column or row from which you want to match the value and the value to match.
MATCH (lookup_value, lookup_array, [match_type])
Let’s say you have a list of employees names and you want to check for a particular employee that where its name is in the list.
In match function, just need to enter the name in the employee and refer to the column or the row. And, it will return a number which will be the position of cell from column or row.
How INDEX and MATCH Work Together?
To understand the working of INDEX and MATCH, a simple thing you need to pick:
MATCH tells the INDEX the position (cell number) of the value in a column or in a row.
And, then the INDEX returns the value using that position (cell number).
The whole power of this formula is in these two points.
Just think like this, MATCH function is an undercover agent who finds the criminal and INDEX function is a cop who arrests that criminal afterward.
But, let’s learn this in detail how we can combine these two functions
Below is the syntax of INDEX, as you know.
INDEX(array, row_num, [column_num])
In INDEX function, row_num argument tells it, that from which row it has to return the value. Let’s say if you enter 4 it will return value from the 4th row.
To create INDEX MATCH formula we need to replace MATCH with row_argument of INDEX.
When we use MATCH, it looks up for the value from the lookup column and return the cell number of that value.
And then, INDEX use that number to figure out the position of the cell from the value column.
In the end, it returns the value from that cell and you get the value you are looking for.
But now, let’s work with a real example.
Below we have a list of cities and name of the employees who are working there.
Here we need to lookup for the employee name who’s working in Mumbai.
Now, if look at data, in the column where you have cities, that is our lookup column and the column where you have employees name, that is your value column.
And, the formula will be:
=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)
Let’s break down this formula into two parts to understand it.
Part-1: In the part one, we have used the match function to lookup for the value “Mumbai” and it has returned the “5” which is the position of the cell in which you have value “Mumbai” in cities column.
Sometimes while working with reports and charts we need to use some sort of symbols to present a specific type of values.
Take an example of Delta (Δ) symbol which we can use to present the difference between two values over a time of period.
So while working in Excel, if you calculate differences between values it’s better to use Delta symbol instead of using anything else, like a specific word or something.
Now the thing is if you look at the Excel ribbon there is no direct way for you to insert it into a cell.
5 Methods to Add Delta Symbol in Excel
But, I have figured out that there are total 5 possible ways which we can use.
So today in this post, I’d like to share with you these simple ways to insert a delta symbol in a cell in Excel.
This is a simple way to insert any symbol you want to add in a cell.
All you need to do is, open symbol dialog box and search for the symbol and insert it.
Here’s how to do it in steps.
First of all, select the cell where you want to add the symbol.
Then go to Insert Tab ➜ Symbols ➜ Symbol.
Now from insert symbol window, select “Greek and Coptic” and it will navigate you to the Greek symbols.
From here, click on the Delta symbol and click on insert.
Once you click on the insert it will add one symbol to the selected cell which you can copy and add further to other cells.
Pros: The benefit of this method is it’s simple and easy to insert.
Cons: It’s not dynamic and you need to do it again and again or copy paste the symbol into other cells.
2. Create a Symbol using CHAR Function
CHAR function can be used to insert a specific character by providing a code into the function.
And, this can be used for Delta symbol as well and here’s how to do this.
In a cell, where you need to insert the symbol, enter below formula.
=CHAR(112)/=CHAR(114)
After that, you need to change the font style of that cell to “Wingdings 3”.
Above two formulas add two different delta symbols, the first one inserts a white (outlined) symbol, on the other hand, the second one adds a black (filled).
Pros: As CHAR is a function and this formula gives you an option to choose between two different types of symbols.
Cons: If you want to add the symbol by concatenating it with some other formula it doesn’t work as you have changed the font style.
3. Add Delta Symbol using AutoCorrect Option
This is another useful way to add a delta symbol.
In Excel, there is an option called Auto Correct which you can use to insert a symbol by typing a simple text.
All you need to do is specify a text which you use to enter in cell and then whenever you use that text Excel will convert that text into a symbol.
Here are the steps to do it.
First of all, copy this delta symbol from here or you can also do it by inserting the symbol from the symbol options.
After that, go to File Tab ➜ Options ➜ Proofing ➜ AutoCorrect ➜ Options.
Now in the AutoCorrect dialog box, in the “Replace with” input box enter “(dlt)” and in the “With” input box paste the delta symbol which you have copied.
In the end, click OK.
Now, whenever you type “(dlt)” in a cell Excel will convert that text into a delta symbol.
You can even use a different text from the text which we have used here.
Pros: It makes super easy to insert a symbol by entering a text. Just enter the text and Excel converts it into a symbol for you.
Cons: Again, this is not a dynamic method and if you need to add the symbol again you need to enter text again.
4. Add a Delta Symbol with Custom Formatting
This one is the most awesome method and my favorite one.
Let’s say if you want to add a delta symbol with numbers in a cell then this is the best method.
When we use custom formatting, in that case, we just need to specify the format and Excel applies it to the cell without changing the values in the cell.
Follow these simple steps for this:
First of all, select the cell where you want to apply custom formatting with a delta symbol.
Now, right-click and open format option and go to Custom category.
From here, in the format input bar enter below format and click OK.
General Δ
Once you apply this custom format it will show a delta sign with all the number in the cell.
In this method idea is simple, no matter which format you have just combined that format with a delta sign in the custom formatting category and you’ll get the symbol.
Look at below percentage format with a delta sign.
0% Δ
Pros: This is the best method in all the methods we have here because it doesn’t affect the values of the cell.
Cons: If you want to add a delta symbol to a cell for real then this method is not for you.
In above syntax, first of all, you need to refer to the range from where you want to sum values, then the range to check for criteria and after that criteria to check and so on.
When you hit enter, this function will return $15,682 in the cell which is the sum of the amount between 15-Jan-2017 to 25-Jan-2017.
And here in this calculation, you have included both start and end date as well.
To verify this calculation, just select amount cells from 15-Jan-2017 to 25-Jan-2017 check out the status bar for the sum of these values or you can also filter values to check it.
How it Works
In this formula, we have used logical operators with SUMIFS which helps to add a condition while calculating the sum.
Let’s break down the formula to understand it.
First of all, we have used range B2:B31 as a sum_range where we have the amount.
After that, we have referred to A2:A31 as first criteria range and “>=15-Jan-2017” as the first criterion.
Next, we have again referred to A2:A31, but this time as second criteria range and “<=25-Jan-2017” as a second criterion.
So these two criteria tell function to sum values which are equals to or greater than 15-Jan-2017 and which are equals to or lower than the 25-Jan-2017.
So this point is, we have two different criteria, one for the lower limit and one the for the upper limit, which creates a range of dates.
And the function will only sum those values which are within this range.
Sum Values Between a Dynamic Range of Dates
There is also a situation when you need to sum values between two dates on an ongoing basis.
Let’s say, you need to sum values for last 7 days and this should be dynamic so that every day you get the sum of last 7 days.
SUMPRODUCT IF: It make it possible to product values from two or more arrays using a specific condition [...]
Get Total Days in Month: You can calculate total number of days in month using a formula [...]
Get Month from a Date: A month is one of the useful components of a date which you can use to summarize data and when it comes to Excel we have different methods [...]
If we use wildcard characters with SUMIF, we can sum values using partial criteria. The good news is we can use all three characters (Asterisk [*], Question Mark [?], Tilde [~]) with SUMIF/SUMIFS.
Today in this post, I’d like to share with you a simple way to use SUMIF with wildcard characters explaining the usage of all the three characters with examples.
Asterisk is the most popular wildcard character. And, it’s the one which can be used with SUMIF perfectly.
Asterisk: A Quick Intro
You can use an asterisk to present one or more missing characters at the end or start of criteria.
As I said, if we have John as the criteria and we need to sum values for the name John Martin, we can combine text “John” with an asterisk to present rest of the text.
Example: SUMIF + Asterisk
Here I have a better example for you to understand the working of asterisk with SUMIF.
Below is the table where we have a column where product name and invoice number is in joint or you can say it’s a product wise invoice number.
Now from this table, we need to create a product wise total.
But if you see, every single product name is unique, because we have invoice number with it. From here even if we create a pivot table we can’t get a product wise total.
So it’s time to use an asterisk and the formula would be:
In the above formula, we have combined product name with an asterisk wildcard character sum values for each product.
So, when SUMIF matches criteria from the invoice column it takes the characters before the asterisk and replaces rest of the characters.
In simple words, it ignores all the characters after the product name from a cell, returns the sum from the amount column.
This way we are able to get the product wise total even when we don’t have a proper product name in the column.
2. Question Mark [?] with SUMIF
A question mark next important character after the asterisk. With SUMIF/SUMIFS it can help you to create a partial text criteria.
Question Mark: A Quick Intro
You can use a question mark to present an unknown single character in a text string.
My name is “PUNEET”. But what if someone writes it as PUNNET and PUNIIT? I’m not able to SUM any value related these two or any other.
If I use PUN??T (Two Question Marks), it helps me to SUM values from both of the criteria.
Example: SUMIF + Question Mark
Normally, the use of a question mark is not that frequent comparing to an asterisk but even then it’s important to learn and can be used in some specific situations.
Have a look at below data (again I’m using my name). If you look carefully you’ll see my name is there serval times on the list.
But the point to be noted is instead of having space in between first and the last name you have different characters.
And if, you want to sum all the bonuses which are there you can’t simply refer to my name.
Even the name is there in several cells but each one is unique and to solve this problem and sum all the bonuses you can use below formula.
Now here you have used a question mark between first and the last name.
The thing is when you use a question mark it replaces only that character with any of the character which is available.
When SUMIF use criteria which you have specified it only matches the first and last name and ignores the characters in the matching values for the same position where you have used a question mark.
So the crux is this: A question mark represents a single character in a text string.
But if you work includes to managing most dirty data then a tilde could be helpful for you someday.
Tilde: A Quick Intro
In simple words, tilde nullifies the effect of wildcard characters (Asterisk and Question Mark).
For example, if you are trying to sum values using a text in which you have an asterisk or a question mark as a real character, SUMIF will treat them as a wildcard instead of a real character.
…read the example next.
Example: SUMIF + Tilde
Here we are using same data like we have in above example and in this data again you have my name. But this time name already has a wildcard character with it.
From this data, you need to sum the bonus values with the name where you have a question mark.
Have a look at below example where we have simply used the criteria to sum bonus values.
But.
If you look at the result of the formula it shows the sum all the bonus values (for first name and full name).
SUMIF considers that question mark in the criteria as a wildcard and returns the sum of the bonus values where text in the criteria is “Puneet”.
As I said, we need to use a tilde with the asterisk to get the sum of values. So the formula would be:
So when you use tilde next to the asterisk, SUMIF take it as a real character instead of a wildcard character.
…problem solved.
You got the right sum of the values.
Sample File
Download this sample file from here to learn more.
Conclusion
The thing which I like about using wildcard characters with SUMIF/SUMIFS is it saves time and you can sum values without making any initial changes in original values.
Just remember:
If you have a data where you are not sure about the full-text string you can use an asterisk.
And, if you want to replace a specific count of characters then a question mark can be used.
But, as I said, your work includes to managing most dirty data then a tilde could be helpful for you.
I hope you this tip will help you get better at formulas. You can learn some of the most amazing Excel formulas from here.
Now tell me one thing.
Have you ever used SUMIF with Wildcard Characters ever before?
Share your views with me in the comment section, I'd love to hear from you. And, please don’t forget to share this tip with your friends.
VLOOKUP with Wildcard Characters: Using wildcards with VLOOKUP can be super useful for you. It can help you to look up for a value using a partial match [...]
SUM Values Between Two Dates: Let’s say, you are working in a trading company and your boss tells you to get the total sales amount for last 15 days [...]
SUMIF OR: SUMIF OR is an advanced formula which helps you to increase the flexibility of SUMIF [...]
Remove First Character from a Cell: Yesterday, one of my colleagues, who is really passionate to learn Excel these days asked me this [...]
R1C1 Reference Style: Most of the Excel users don’t even know about the existence of R1C1 reference style. But, some users love to use it and found it more convenient [...]
Where the number is the number or cell’s reference which contains the number for which you want to find the square root and power is the Exponent to raise power to.
Let’s say we need to find the square root of a number from cell A1 (that’s 625) then we can use 1/2 as power argument in this function.
As you see it returns 25 in the result which is the square root of 625.
#3. Apply an Exponent Formula to Get SQUARE root of a Number
If we need to insert a simple formula (other than above two methods) to calculate a square root then we can use a simple formula using exponent operator.
This formula is just like POWER. The only difference is instead of the function we need to use an exponent operator.
=A1^(1/2)
In the above formula, A1 in the cell where we have a number for which we need to find the square root and then we have used an exponent operator and then (1/2) for raise to power.
The best benefit of this formula is its application. Apart from the square root, we can use it to calculate cube root or any nth root.
#4. A VBA Code to Show the SQUARE Root of a Number
Let’s me tell you about a different scenario where we need to check the square root of a number instead of a calculating it in a cell.
Code-1
Below is a VBA code which returns the square root when we select a cell and run this code.
Sub getSquareRoot()
Dim rng As Range
Dim sqr As Long
If Application.Selection.Cells.Count > 1 Then
MsgBox "Please select only one cell", vbOKOnly, "Error"
Exit Sub
End If
Set rng = ActiveCell
If IsNumeric(rng) = True Then
sqr = rng ^ (1 / 2)
MsgBox "The Square Root of " & rng & " is " & sqr, vbOKOnly, "Square Root Value"
Else
MsgBox "Please select a numeric value", vbOKOnly, "Error"
End If
End Sub
How this code works
When we run this code, it verifies the value in the selected cell and if that value is a number then it calculates the square root of that number and shows it using a message box.
One thing you need to take care, that if you select more than one cell this code will not work.
Code-2
By using below code we can check enter a square root of a number without having the actual number in a cell.
Sub getSquareRoot()
Dim sq As Long
Dim sqr As Long
sq = InputBox("Enter the value to calculate square root", "Calculate Square Root")
If IsNumeric(sq) = True Then
sqr = sq ^ (1 / 2)
MsgBox "The Square Root of " & sq & " is " & sqr, vbOKOnly, "Square Root Value"
Else
MsgBox "Please enter a number.", vbOKOnly, "Error"
End If
End Sub
How this code works
When we run this code it shows us an input box asking for the number for which we need to get the square root.
And when we enter that number, it calculates the square root for that numbers and shows a message box with the square root.
You can use any of the above code which is perfect for you.
#5. Using Power Query to Convert Numbers into SQUARE Roots
Here’s another way which we can use if you need to convert multiple numbers into their square roots.
That’s Power Query.
Below we have a table where we have few numbers and here we want to get the square root of these numbers in a one go.
Note: Using power query for square root is a dynamic method, every time when you enter a new value in your table it will return the square root of that number.
Now, follow these simple steps.
First of all, select any cell from the table and go to Data Tab ➜ Get & Transform Data and click on From Table/Range.
Once you click on it, Excel opens the power query editor and add that table into it.
From here we need to create a new column with square root values and for this go to Add Column tab and click on Custom Column.
At this point, we have Custom Column window opened and in this window, we need to add following:
First of all, enter the column name “Square Root” in column name input box.
After that, enter below formula in the custom column formula input box and click OK.
=Number.Sqrt([Numbers])
Now we have a new column with square roots of the number we have in the first column.
From here, you need to delete the first column of original values. So, right click on it and click remove.
After that, go to home tab and click on close and load to.
Here you get a window to load data. First of all, select the existing worksheet and add range B1. Second, tick mark "Add to the Data Model".
While working with huge data, duplicate values are always a concern. Most of the time I use remove duplicate option to remove all those values.
And, I’m sure you do the same.
But, removing these duplicate values or just counting them never sort out the problem. The important thing is to review all the duplicate values before deletion.
Yes, that’s right.
Once you highlight all those values, you can check and then you can delete them.
Today, in this post, I’d like to share with you 4 different VBA codes to highlight duplicate values.
And the part is, these codes can highlight cell using different ways. You can simply copy-paste them in your VBA editor and use them.
This VBA code checks all the cells from a row and highlights all the cells which are duplicate within a row.
In simple words, if a row has the value “522” twice then it will be considered as duplicate. But if the another 522 is in another row then it will be considered as unique.
Sub DuplicateValuesFromRow()
'Declare All Variables.
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
'Count Number of Rows and Columns
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
'Loop Each Row To Check Duplicate Values and Highlight cells.
For i = 2 To myRow
Set myRange = Range(Cells(i, 2), Cells(i, myCol))
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3
End If
Next
Next
End Sub
If you go through this macro, you will find that we have used a loop to check each row for duplicate values and highlight them with a color.
Important Points
Your data should not have a blank row or column in it, otherwise, it ignores that cell.
Starting cell of your data should be “A1” cell. And if you want to adjust the starting point you have to adjust the code.
First row and column of your data sheet should be heading.
2. Within Each Column
This VBA code checks all the cells from a column and highlights all the cells which are duplicate within each column.
In simple words, if a column has the value “231” twice then it will be considered as duplicate. But if the another “231” is in another column then it will be considered as unique.
Sub DuplicateValuesFromColumns()
'Declare All Variables
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
'Count number of rows & column
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
'Loop each column to check duplicate values & highlight them.
For i = 2 To myRow
Set myRange = Range(Cells(2, i), Cells(myRow, i))
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3
End If
Next
Next
End Sub
3. Within Selection
Let’s say you just want to highlight cells with duplicate values from the selection, this code can help you in this.
To use this code you just need to select a range of cells and run this code. It checks each cell with the selection and highlights it with red color if a cell has a duplicate value.
Sub DuplicateValuesFromSelection()
Dim myRange As Range
Dim i As Integer
Dim j As Integer
Dim myCell As Range
Set myRange = Selection
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3
End If
Next
End Sub
4. Entire Data
If you have a large data-set, you can use this macro code to check the entire data and highlight duplicate values.
This code loops through each cell one by one and applies red color all those cells which are the duplicate.
Sub DuplicateValuesFromTable()
Dim myRange As Range
Dim i As Integer
Dim j As Integer
Dim myCell As Range
Set myRange = Range("Table1")
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3
End If
Next
End Sub
Important Note: In the above code, we have used the table name "Table1", you can change this name from the code. Or, if you have a range then you can simply use range name.
[Bonus Tip] Count Duplicate Values
This code helps you to count the numbers of duplicate values from the selection. When you run this code, it returns a message box which shows the count.
Sub CountDuplicates()
Dim i As Integer
Dim j As Integer
Dim myCell As Range
Dim myRange As Integer
myRange = Range("Table1").Count
j = 0
For Each myCell In Range("Table1")
If WorksheetFunction.CountIf(Range("Table1"), myCell.Value) > 1 Then
j = j + 1
End If
Next
MsgBox j
End Sub
Again in the above code, we have used table name and you change it or replace it with a range.
Yesterday, one of my colleagues, who is really passionate to learn Excel these days asked me this:
“Do you know how to remove the first character from a cell in Excel?”
I told him a small formula (which was easy) for that, but he was not satisfied and want another method.
And after that, I told him about five different methods for this but then he got confused about which method is easy.
Even I have created a small UDF to make it super easy to for him to remove any number of characters from the start of a string.
Today, in this post, I’d like to share with you all these methods which I’ve found easy and convenient to remove a character from starting off a string.
Here LEN function first counts the total characters from a cell and then RIGHT extracts the characters from right side leaving the first character.
This way you get the string without the first characters.
Note: If you want to remove more one character then simply replace 1 in the right function with 2, 3, 4 for whatever the characters you want to remove.
2. Using REPLACE Function
And, if you want to use a single formula for that you can use REPLACE Function. In below example, we have used REPLACE function to remove the first character from the cell.
We can simply replace the first character with an empty character using the below formula.
In this formula, LEN returns the total number of characters in the string and then we have deducted 1 from that number. So, if we have 4 characters in a cell it returns 3.
After that, MID extract the characters from the second position and the which we have got from LEN tells MID how many characters need to extract.
4. With Text to Column
Let’s say instead of removing the first character you need to seprate it from the string, then you also can use text to column.
Here are the steps:
First of all, select the column or range of cells.
After that, go to ➜ Data ➜ Data Tools ➜ Text To Column. Where you’ll get a dialog box.
From here, select “Fixed Width” and click next.
In the data preview, click between first and second character to insert a vertical line between both of the characters.
And then click next.
Now from the data column format, select format type you want to apply and click finish.
Finally, here you have two columns, one with the first character and other with the rest of the characters.
You can delete if you don’t want to use the column with first character.
5. UDF
To give you more ease we have created a simple UDF. It will exactly do the same which we can do with all above-mentioned methods.
Copy the following code and use the steps to insert in your workbook.
Public Function removeFirstC(rng As String, cnt As Long)
removeFirstC = Right(rng, Len(rng) - cnt)
End Function
First of all, open the visual basic editor from your workbook using Alt + F11.
After that, insert a module and paste above code into it.
Now, come back to your worksheet and insert the following formula.
Note: If you want to remove more than one character from the starting of the string then you can enter that number in the function.
In conclusion, I’d like to say that all the above methods are quite easy and convenient to use.
First three methods are based on formulas which are dynamic and on the other hand text-to-column is quick but you need to use again and again so sometimes it comes less handy.
But the important thing is if you want to remove more than one character (two, three, four etc) from the string/cell then using a UDF is a perfect way.
If you ask me method 1 and 5 is my favorite. But here you need to tell me one thing.
Which method do you like the most?
Please share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share this tip with your friends.
You have dates in column and you want to highlight all the dates which are between the range of two dates.
Huh?
OK, alright!
Let’s say you have a start date 17-Jan-2017 and an end date 19-Mar-2017, and from the dates column, you want to highlight all the cells in which date is between these two dates.
For this, the easiest way is to use conditional formatting. You can use a formula based on AND and DATE functions.
Just need to create a new rule in conditional formatting with this formula. Let’s follow these steps.
Steps to use CF to Highlight Dates Between Two Dates
First of all, select the range or column (A2:A20) where you have dates.
Now, go to Home Tab ⇢ Styles ⇢ Conditional Formatting ⇢ New Rule.
Fron here, in New Rule window, select “Use a formula to determine which cell to format”.
After that, in formula input bar, enter below formula.
Finally, select a color from formatting option to apply to cells.
In the end, click OK.
Once you hit OK, all the cells where dates are within the range which you mentioned will get formatted with the color the color you have specified.
How this CF formula works
AND function can take more than one condition and returns TRUE if all the conditions are TRUE, else FALSE.
So when you use this formula in conditional formatting it checks the date in the cell and returns TRUE if that date is within the date range.
With that TRUE, conditional formatting apply color to all the cells with dates.
Let say you need to create a data validation for dates in which you want to use a specific date range.
In simple words, you want when a user enters a date in a cell that should be within a range of dates which you specify.
Well, this can be done by using a custom formula based on the AND function and a simple way by defining dates in the date range option in data validation.
These both methods will restrict a user to enter a date out of the range.
So, let learn this thing.
Steps to Create Date Validation with Date Range
First of all, select the cell where you want to apply this data validation rule.
Now, go to Data Tab ⇢ Data Validation ⇢ Data Validation.
From here in data validation dialog box, select “Date” from “Allow” drop down.
After that, select between from the data drop down.
Next, you need to enter two dates in “Start Date” and “End Date” input boxes.
In the end, click OK.
Now, in all those cells which you have selected user can only enter a date which is within the range of those dates which you have specified.
Even, instead of entering dates directly into the data validation you can refer to the cells where you have dates.
This way you can change dates any time without opening the option.
Quick Tip: You can use TODAY function to create a dynamic range of dates.
Using AND Function to Create a Date Range in Data Validation
The same thing can also be done by using a custom formula based on AND function.
First of all, select cell A1.
From data validation dialog box, select “Custom” from “Allow” drop down.
Now, in the formula input bar enter below formula and click OK.
=AND(A1>=DATE(2016,6,1),A1<=DATE(2016,6,30))
This method is bit limited and complex. But, if you want to specify dates for one-time then you can use this method.