Loading...

Follow Excel Champs Blog on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


INDEX MATCH, when combined, can change the approach you use to lookup values in Excel.

Yes, you get it right.

The combination of these two functions can match the gap of all other functions which we use for lookup.

Let me tell you how.

If you love to use VLOOKUP then I’m sure you have this secret pain inside: You can’t use it for advanced lookup (e.g. lookup to left).

Right?

But INDEX and MATCH can do this for you.

This combo formula is so flexible and powerful that you can lookup for a value in any direction.

Well, if you are still not convinced to use it, I have some facts and experts opinion to make it for you.

And after that, this monster guide will show you each and everything about combining and using INDEX with MATCH.

...so without any further ado let's get started.

Why Should You Use INDEX MATCH

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.❞

John Michaloudis

❝I find using a combination of the INDEX and MATCH formula to be a really flexible and robust way of building formulas in Excel.❞

❝VLOOKUP had energy drink and turns into INDEX+MATCH to become more powerful.❞

Rahim Zulfiqar Ali

I am sure these are enough reasons for you to use​ INDEX and MATCH instead of using other functions and formulas.

And today, when you finish this guide, you will an advanced INDEX MATCH user as I have listed all the stuff you need to master it.

This guide is the biggest thing ever written on these two functions.

Benefits of Using INDEX MATCH in Excel

There's always a debate about INDEX and MATCH Vs. VLOOKUP.

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.

What is MATCH Function

MATCH function lookup for a value in a column or in a row and return the position of the cell where that value is.

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.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.

…let’s learn this thing.

Want to get better at Excel? Then you must download these free e-books from here.
1. Insert Delta Symbol from Symbols Option

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.

  1. Pros: The benefit of this method is it’s simple and easy to insert.
  2. 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).

  1. Pros: As CHAR is a function and this formula gives you an option to choose between two different types of symbols.
  2. 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.

  1. 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.
  2. 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% Δ

  1. Pros: This is the best method in all the methods we have here because it doesn’t affect the values of the cell.
  2. Cons: If you want to add a delta symbol to a cell for real then this method is not for you.
5. VBA Code to Insert Delta Symbol in a Cell

Macro codes are big time savers and if a code can insert a delta symbol in a cell for you then there is nothing like this.

And, below we have this code:

Sub Macro1()
Dim rng As Range
For Each rng In Selection
rng.Value = rng.Value & ChrW(916)
Next rng
End Sub

To use this code, first of all, you need to enter it in VB editor (open it from developer tab).

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

SUMIFS is the easiest method to sum values between two dates (range of dates).

Let’s say, you are working in a trading company and your boss asks you to get the total sales amount for last 15 days.

In this case, you need to sum sales amount from the current date to 15 days prior i.e. between two dates.

Let me tell you how.

Here, you have a range of two dates which includes total 15 days and you need to sum values for all of them.

And with SUMIFS you can add those two dates as criteria and it works like a range and sum all the values between that range.

Confused? Don’t worry.

Today in this post, I’d like to share with this simple formula and explain it in plain language to make you understand it.

…let's get started.

Quick Intro: SUMIFS

It can help you to sum values by specifying more than one criteria. In short, it will only sum those cells where all the conditions are met.

=SUMIFS(sum_range,range1,criteria1,[range2],[criteria2],…)

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.

...click here to learn more about this function

Formula to Sum Values Between Two Dates in Excel

Here you have sales data for the month of Jan and you need to sum values between 15-Jan-2017 to 25-Jan-2017.

Download this sample file from here to follow along.

In the D2 cell, insert below formula and hit enter:

=SUMIFS(B2:B31,A2:A31,”>=15-Jan-2017″,A2:A31,”<=25-Jan-2017″)

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.

So, the formulas will be:

=SUMIFS(B2:B31,A2:A31,”<=”&TODAY(),A2:A31,”>=”&(TODAY()-6))

How it Works

In the above formula, we have used TODAY Function to get the current dynamic date which will change automatically.

The first criterion tells the function to sum only those values which are equals to or lower than today’s date.

And, the second criterion tells the function to sum only those values which are equals to or lower than the date which is 6 days before than today.

Now, the range of two dates will be something like this: from today to six days back which means you have total seven days.

And the function will only sum values which are in this range.

Sample File
Conclusion

As I said, SUMIFS is the best way to get the sum of values between a range of two dates.

But one thing which you need to take care is to use lower than sign with upper date and greater than with the lower date.

And, I hope you have found this formula tip useful but you need to tell me one thing.

Do you know any other method to sum values between two dates?

Please share your views with me in the comment section, I'd love to hear from you. And, don't forget to share this tip with your friends.

Related Excel Tutorials

  1. SUMIF OR: By using OR logic you can sum values based on two different criteria  which is not normally possible with SUMIF [...]
  2. SUMIF with Wildcard Characters: By using wildcard characters with SUMIF you can use a partial match criteria [...] 
  3. SUMPRODUCT IF: It make it possible to product values from two or more arrays using a specific condition [...]
  4. Get Total Days in Month: You can calculate total number of days in month using a formula [...]
  5. 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 [...]

The post How to Sum Values Between Two Dates using SUMIFS appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

SUMIF is one of my favorite Excel functions and I’m sure it’s yours also.

But one thing which I always worry about it, we can only sum values when criteria fully match with values.

Let’s say, your name is John Martin.

In this case, if we want to sum values using your name we should have your full name. And, if we just have your first name we can’t use it.

Yes, it’s a problem. But, I have a solution for this as well.

Do you remember Excel’s wildcard characters? Yes, you get it right.

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.

If you want to learn about wildcard characters you learn from this guide and make sure to download this data file from here to follow along.

And now, let’s get started…

Want to get better at Excel? Then you must download these free e-books from here.
1. Asterisk [*] with SUMIF

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:

=SUMIF(invoice_column,product_name&"*",amount_column)

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.

=SUMIF(name_column,“Puneet?Gogia”,amount_column)

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.

3. Tilde [~] with SUMIFS

I’m not sure if you will ever require using a tilde as a wildcard character.

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:

=SUMIF(name_column,“Puneet*~”,amount_column)

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:

  1. If you have a data where you are not sure about the full-text string you can use an asterisk.
  2. And, if you want to replace a specific count of characters then a question mark can be used.
  3. 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.

Related Excel Tutorials

  1. 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 [...]
  2. 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 [...]
  3. SUMIF OR: SUMIF OR is an advanced formula which helps you to increase the flexibility of SUMIF [...]
  4. Remove First Character from a Cell: Yesterday, one of my colleagues, who is really passionate to learn Excel these days asked me this [...]
  5. 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 [...]

The post How to use SUMIF with Wildcard Characters in Excel appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Excel is like a Swiss army knife for calculations.

From mathematical to financial, it’s for every single thing you need to calculate. You can calculate simple to most complex things in Excel. 

Take an example of SQUARE ROOT.

A square root is a quite common calculation which we do in our day to day work, and in Excel, you can calculate it with an ease.

You know what?

I have found that there are five different ways to calculate a square root in Excel.

Yes, five different methods, which you can use in different situations.

And today in this post, I’d like to share with you these five simple methods to find a square root of a number in Excel.

So let’s learn this thing…

Want to get better at Excel? Then you must download these free e-books from here.
What's a SQUARE Root?

Before we learn to calculate it, it’s important to understand what it really means, how we calculate it normally.

Quick Intro: Calculating a square root is an inverse calculation for coming back to the root of a square.

So, when you calculate the square of 10 by multiplying it with its self, that’s (10 * 10 = 100).

Then, square root means coming back from 100 to 10.

You can learn more about it from Wikipedia. But now, let’s learn how we can do this in Excel.

Top 5 Ways to Calculate SQUARE Root in Excel

As I said, we can use 5 different ways for the calculation of square root according to the need or personal preference.

Not all the methods are same but useful.

#1. Use SQRT Function to find the SQUARE Root of a Number

If you need to use only one method for calculating square root, SQRT function is that one. It’s a specific function to do this.

You just simply need to refer to a cell or specify a number.

SQRT’s Syntax:

SQRT(number)

Where the number is the number or cell reference which contains the number for which you want to find the square root.

Examples:

In the below example, we have the number 625 in the cell A1. So, to calculate square root for this we can insert below formula into B1.

=SQRT(A1)

The value it has returned is 25 which is the square root of 625.

As you see, SQRT is simple to use and easy to apply.

But there’s a kicker.

If you have a negative number like below then it will return a #NUM!.

So the solution to this problem is to use ABS function along with SQRT. Here’s an example:

=SQRT(ABS(A1))

When we use ABS it converts a number into an absolute number. That means a converts a negative number into a positive.

#2. Calculate SQUARE Root of a Number Using POWER Function

Another way to calculate a square root is by using POWER function.

POWER function has more power than SQRT as it returns the result of a number raised Nth to a given power.

POWER’s Syntax:

POWER(number, power)

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.

And, the formula would be:

=POWER(A1,1/2)

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:
  1. First of all, enter the column name “Square Root” in column name input box.
  2. 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". 
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.

So let’s get started…

1. Within Each Row

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
  1. Your data should not have a blank row or column in it, otherwise, it ignores that cell.
  2. Starting cell of your data should be “A1” cell. And if you want to adjust the starting point you have to adjust the code.
  3. 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.

Sample File
Conclusion

In different situations, we need check duplicate values in the different ways. And, all the above codes can be helpful for you in this.

If you want you can change the highlight color from the codes using color index number.

Now help me on one thing.

Where can we improve these codes?

Please share your views with me in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends.

Must Read Excel Tutorials

  1. Highlight Blank Cells: These are 3 popular ways to count all the highlight cell which are blank. If a cell has a space then these methods [...] 
  2. Count Unique Values: Just like duplicate values, you can count unique values using a simple formulas + VBA methods [...]
  3. 100 Macro Codes: These useful macro codes can help you save your a ton of time everyday by automating your tasks [...]
  4. Count Cells with Text: These are 6 simple ways to count all those cells where the value is a text. You can use any of them according to [...]
  5. Count Words: Using these 5 ways we can count words from a cell or from a range of cells or ven from the entire worksheet in Excel [...]

The post VBA Codes to Highlight Duplicate Values in Excel appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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 are they.

1. ​Combine RIGHT and LEN

Using a combination of RIGHT and LEN is the most suitable way to remove the first character from a cell or a string.

In below example, we have a list of roll numbers where we have alphabet “H” as a prefix.

Here we need to remove “H” and want rest of the string and for this, we can combine RIGHT with LEN to remove this first character.

And the formula which we have used for this is:

=RIGHT(A2,LEN(A2)-1)

How it works

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.

=REPLACE(A2,1,1,””)

How it works

Here REPLACE function simple allows you to replace a character from a cell which is in a specific position with a different character.

To remove the character “H” we have replaced it with a blank character. Read more REPLACE from here.

3. Combine MID and LEN

You can also remove the first character from a cell by combining MID and LEN. This combination works like the method-1 which we have used above.

In below example, we have used this combination to get characters from a cell except for the first most character.

And the formula is:

=MID(A2,2,LEN(A2)-1)

How it works

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

  1. First of all, open the visual basic editor from your workbook using Alt + F11.
  2. After that, insert a module and paste above code into it.
  3. 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.

Sample File
Conclusion

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.

The post

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Let's say you got an Excel workbook from your colleague where you have a list of hyperlinks in a column.

Now from this list, you want to extract all the hyperlink URL addresses.

The manual method is a real pain, you need to copy-paste each URL one by one.

So the better way is to create a UDF in VBA. And, today I'd like to share with you a simple code for this UDF.

VBA code to Extract Hyperlink URLs

Function GiveMeURL(rng As Range) As String
On Error Resume Next
GiveMeURL = rng.Hyperlinks(1).Address
End Function

How to use this Code

To use this UDF, you need to follow these steps:

  1. Open visual basic editor from developer tab or use keyboard shortcut ALT + F11.
  2. Insert a new module, paste above code into it and close it.
  3. Now in your worksheet, enter function [=GiveMeURL(A1)] and hit enter.

It will return the URL from the cell where you have a hyperlink.

The post How to Extract Hyperlink Address (URL) in Excel Using VBA UDF appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.

Related Functions

  1. AND Functions

Must Read Next

  1. 8 Examples for Using Formulas in Conditional Formatting
  2. The 3 Authentic Ways To Highlight Blank Cells in Excel
  3. SUMIFS is the Best Method to SUM Values Between Two Dates in Excel
  4. 5 Custom Date Formats You Have To Learn Today Right Now
  5. How to Convert Text to Date in Excel

The post How to Highlight Dates Between Two Dates appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.

The post How to Create a Data Validation with Date Range appeared first on Excel Champs Blog.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview