Trending November 2023 # Find And Remove Duplicates In Excel # Suggested December 2023 # Top 19 Popular

You are reading the article Find And Remove Duplicates In Excel updated in November 2023 on the website Hatcungthantuong.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested December 2023 Find And Remove Duplicates In Excel

Watch Video – How to Find and Remove Duplicates in Excel

With a lot of data…comes a lot of duplicate data.

Duplicates in Excel can cause a lot of troubles. Whether you import data from a database, get it from a colleague, or collate it yourself, duplicates data can always creep in. And if the data you are working with is huge, then it becomes really difficult to find and remove these duplicates in Excel.

In this tutorial, I’ll show you how to find and remove duplicates in Excel.

Duplicates in Excel can come in many forms. You can have it in a single column or multiple columns. There may also be a duplication of an entire row.

Conditional Formatting makes it simple to highlight duplicates in Excel.

Here is how to do it:

Select the data in which you want to highlight the duplicates.

In the Duplicate Values dialog box, select Duplicate in the drop down on the left, and specify the format in which you want to highlight the duplicate values. You can choose from the ready-made format options (in the drop down on the right), or specify your own format.

This will highlight all the values that have duplicates.

Quick Tip: Remember to check for leading or trailing spaces. For example, “John” and “John ” are considered different as the latter has an extra space character in it. A good idea would be to use the TRIM function to clean your data.

If you have data that spans multiple columns and you need to look for duplicates in it, the process is exactly the same as above.

Here is how to do it:

Select the data.

In the Duplicate Values dialog box, select Duplicate in the drop down on the left, and specify the format in which you want to highlight the duplicate values.

This will highlight all the cells that have duplicates value in the selected data set.

Finding duplicate data and finding duplicate rows of data are 2 different things. Have a look:

Finding duplicate rows is a bit more complex than finding duplicate cells.

Here are the steps:

Drag this down for all the rows. This formula combines all the cell values as a single string. (You can also use the CONCATENATE function to combine text strings)

By doing this, we have created a single string for each row. If there are duplicate rows in this dataset, then these strings would be exactly the same for it.

Now that we have the combined strings for each row, we can use conditional formatting to highlight duplicate strings. A highlighted string implies that the row has a duplicate.

Here are the steps to highlight duplicate strings:

Select the range that has the combined strings (E2:E16 in this example).

In the Duplicate Values dialog box, make sure Duplicate is selected and then specify the color in which you want to highlight the duplicate values.

This would highlight the duplicate values in column E.

In the above approach, we have highlighted only the strings that we created.

But what if you want to highlight all the duplicate rows (instead of highlighting cells in one single column)?

Here are the steps to highlight duplicate rows:

Drag this down for all the rows. This formula combines all the cell values as a single string.

Select the data A2:D16.

This formula would highlight all the rows that have a duplicate.

In the above section, we learned how to find and highlight duplicates in excel. In this section, I will show you how to get rid of these duplicates.

If you have the data in a single column and you want to remove all the duplicates, here are the steps:

Select the data.

In the Remove Duplicates dialog box:

If your data has headers, make sure the ‘My data has headers’ option is checked.

Make sure the column is selected (in this case there is only one column).

This would remove all the duplicate values from the column, and you would have only the unique values.

CAUTION: This alters your data set by removing duplicates. Make sure you have a back-up of the original data set. If you want to extract the unique values at some other location, copy this dataset to that location and then use the above-mentioned steps. Alternatively, you can also use Advanced Filter to extract unique values to some other location.

Suppose you have the data as shown below:

To delete the duplicate row in this case:

Select the data.

In the Remove Duplicates dialog box:

If your data has headers, make sure the ‘My data has headers’ option is checked.

Select all the columns

except the Date column.

This would remove the 2 duplicate entries.

NOTE: This keeps the first occurrence and removes all the remaining duplicate occurrences.

To delete duplicate rows, here are the steps:

Select the entire data.

In the Remove Duplicates dialog box:

If your data has headers, make sure the ‘My data has headers’ option is checked.

Select all the columns.

Use the above-mentioned techniques to clean your data and get rid of duplicates.

You're reading Find And Remove Duplicates In Excel

Excel Find Column Containing A Value

Last week I had an email from Mike asking how he can lookup a suburb in a range of columns and return the post code from the header row.

I imagine his data was a bit like this:

And in cell B9 he wants to find the post code for Herston.

One way is with this array formula:

INDEX(B1:F1,,

MIN(

IF(B2:F5=A9,COLUMN(A:E))

)

Entered with CTRL+SHIFT+ENTER.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook . Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.

Before we dive in, here are the syntaxes for the INDEX and IF functions as a reminder (I’ve crossed out the arguments we’re not using):

INDEX(reference,

row_num

,[column_num],[

area_num

])

IF(logical_test, [value_if_true],[

value_if_false

])

The INDEX formula is returning a reference to the cell in the first row for the column containing ‘Herston’. For the column_num argument it uses a combination of IF, COLUMN and MIN.

Here it is again for reference:

INDEX(B1:F1,,

MIN(

IF(B2:F5=A9,COLUMN(A:E))

)

Check the cells in the range B2:F5 for ‘Herston’ and tell INDEX what column number it’s in. i.e. column 4. INDEX (look in) the range B1:F1 and return a reference to the 4th cell i.e. E1, which contains 4006.

So what’s MIN got to do with it….hold your horses, more on that in a moment.

Let’s step through the formula in the order it evaluates:

Step 1 – IF function’s logical_test: B2:F5=A9 i.e. B2:F5=Herston and it looks like this:

=INDEX(B1:F1,,MIN(IF({FALSE,FALSE,FALSE,FALSE,FALSE; FALSE,FALSE,FALSE,FALSE,FALSE; FALSE,FALSE,FALSE,

TRUE

,FALSE; FALSE,FALSE,FALSE,FALSE,FALSE},,COLUMN(A:E))))

Tip: did you notice in the formula above there is a semicolon after every 5th ‘FALSE’ instead of a comma. This semicolon represents a new row in the array.

Or if you imagine our formula is putting together a list of values representing each row in the table like this:

Step 2 – COLUMN function: This evaluates to return a horizontal array of numbers {1,2,3,4,5} for our IF function’s value_if_true argument. These numbers represent the 5 columns B:F in our table.

Our formula now looks like this:

=INDEX(B1:F1,,MIN(IF({FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,

TRUE

,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE},{1,2,3,

,5})))

Tip: Instead of using the COLUMN function to generate the array of numbers we could simply type {1,2,3,4,5} into the formula. However, with large horizontal arrays it’s quicker (and dynamic) if we use the COLUMN function to generate the array, or for vertical arrays you can use the ROW function.

Note: if you don’t want your COLUMN function to be dynamic you can use the INDIRECT function to fix it, like this:

=INDEX(B1:F1,,MIN(IF(B2:F5=A9,COLUMN(INDIRECT("A:E")))))

Step 3 – IF function value_if_true: The IF function finishes evaluating by assigning the value_if_true numbers (generated by the COLUMN function) to the TRUE results in the logical_test.

To visualise this we can look at the 3rd horizontal array (i.e. the series of FALSE/TRUE after the second semicolon below). Remember this is the 3rd row of our table above.

Excel gives the TRUE results the corresponding number from the array generated from the COLUMN function {1,2,3,4,5} like so:

Note: In this step the FALSE values evaluate to nothing i.e. they are ignored. Remember we don’t have a value_if_false argument in our IF formula. Our formula now looks like this:

Step 4 – MIN: This simply evaluates to find the one and only number; 4.

=INDEX(B1:F1,,

)

Tip: Since there is only one number remaining (the rest are all FALSE) we could have used MAX or SUM to get the same result as MIN.

Step 5 – INDEX: Finally INDEX can return a reference to the 4th column in the range B1:F1 which is cell E1 containing post code 4006.

Tip: Notice how our INDEX formula doesn’t have a row_num argument:

Since our reference is only one row high we don’t have to type a 1 in for the row_num argument, we simply enter a comma as a placeholder and continue on to the column_num argument.

What The?

Did you find that tricky?

When working with long or complex formulas I like to use the Evaluate Formula tool to understand what’s going on behind the scenes.

You can also evaluate parts of your formula by highlighting the section of the function in the formula bar and pressing the F9 key. Below I’ve evaluated the COLUMN(A:E) part of my formula:

To revert to the original formula either press the escape key or CTRL+Z.

Thanks

Thanks to Mike for inspiring this post.

If you liked this please share it with your friends and colleagues.

How To Find Circular References In Excel To Avoid Faulty Data

Did you see the circular reference error on an Excel sheet and wondering if the worksheet is broken? Trust me! You’re not the first one to face this Excel error. Hundreds and thousands of Excel users experience circular reference errors every day. Not to worry! Here’s how to find circular references in Excel and resolve those.

When someone changes the cell values of the cell references, the worksheet updates itself automatically. This is called data analysis automation and data analysts love to play with cell references.

However, the problem arrives when you intentionally or accidentally type the cell address where you’re currently performing a calculation. Or, when you’re creating many formulas throughout the worksheet, a cell containing a formula appears in another formula indirectly.

In such and many other similar situations you get the circular reference error in Excel. Read on to learn what circular reference is, its reasons, why to avoid it, and most importantly how to find circular references in Excel to get rid of these anomalies for good.

What Is a Circular Reference in Excel?

Excel encounters a circular reference error when it sees that one or many functions of the worksheet reference back to the cell address where their functions were created.

It might sound confusing to you, so think about how severe it could be for the Excel app. In short, you never want to put the cell address in a formula where you’re creating it. Look at the following example for a visual clarification:

You’re trying to multiply 10 from cell B1 and put the result in cell B1 itself.

Now, in the above scenario, if you just press OK, Excel will close the error and show zero as the calculated result. This is just a straightforward example.

Now, imagine there are hundreds of formulas in a bookkeeping worksheet, finance planning worksheet, employee payroll, vendor payment worksheet, and so on.

You’ll trust the faulty data calculated by Excel due to the circular reference error and that’ll in turn impact your business or professional decision-making.

Furthermore, circular referencing can severely reduce the speed of calculations within the Excel app. In severe cases, the very error can cause extremely slow performance in the PC too if not dealt with immediately.

For example, in energy production units, like windmills, you need to calculate recycled output energy from a fraction of input energy. Alternatively, in a chemical plant, engineers use the output stem as input for the same or another boiler or process vessel. Such high-level engineering calculations indeed require circular references for iterative calculations.

When Do You See the Circular Reference Error in Excel?

Clearly speaking, the circular reference pop-up isn’t an error message. It’s a way for Excel to inform you that it encountered circular reference in one or many formulas and that may influence its calculation algorithm and performance.

To avoid any damages caused by circular references in Excel formulas, you must know when they appear and when they don’t. Find below the instances:

In any open worksheet or workbook, you create a formula and enter the circular reference for the first time.

You or someone removed all the circular references from the Excel file and saved it. Now, again you or another user enter a circular reference in the same file.

You closed all open workbooks. Now, opened a different workbook and then type a formula containing a circular referencing.

You’ve just opened a workbook that previously contained one or many circular references. You’ll see the warning.

Thus, you must carefully look for the above instances to catch circular reference warnings and resolve those if you’re not looking for iterative calculations.

Types of Circular References in Excel

If you want to learn how to find circular references in Excel, you must understand their types. Because finding and eliminating circular references in Excel isn’t an automated task. Find below the common types of this error:

Intended circular references occur when you use them intentionally for iterative calculation purposes. For example, you need to input a time stamp on another cell when someone enters a value in a referenced cell using volatile and logical functions like IF, OR, SUMIF, etc.

Unintended circular referencing when you try to work on an Excel formula faster and refer to the formula-containing cell mistakenly. For instance, you need to apply the SUM function from cell A1 to A9 and fetch the result in cell A10. But mistakenly, you type the formula like this on cell A10:

=SUM(A2:A10)

Hidden circular references are those that don’t show any warning messages when you create a formula or open the worksheet for the first time. It happens because someone allowed iterative calculation on the Excel file.

Indirect circular references are also popular and they occur when you create a new formula in any cell, for instance, cell A1, and this new formula would use another function in another cell, like cell B1, which already is using cell A1 as a reference.

Now that you understood the basics of circular references in Excel, let’s find out how to find circular references in Excel below:

How to Find Circular References in Excel: From Status Bar

Provided that the iterative calculation feature is deactivated, you can see the following notifications about circular references on the Excel Status Bar:

Circular References: C3 (or any other cell reference) when you’re on a worksheet containing circular formula in the workbook.

You’ll only see Circular References if you’re on a worksheet that doesn’t contain any but the workbook has a few of these circular formulas.

How to Find Circular References in Excel: Using Error Checking

It’s the best way to check for circular references in a workbook or worksheet. Here are the steps you must know:

Open a worksheet that you suspect of containing circular formulas.

Hover the cursor over the Circular References option to see all the available instances in all worksheets.

Select each instance and Excel will pinpoint those cells automatically.

How to Find Circular References in Excel: From Formula Tracing

If you don’t find the Error Checking method efficient enough to detect large numbers of formulas with circular references, you can try these steps to use the formula tracing method:

Select any referenced cell or the formula-containing cell.

Excel will highlight the cells that affect the formula result on the current cell with arrows.

Repeat the steps on all worksheets to check the entire workbook.

This method is particularly important when the iterative calculation features are active on the Excel worksheet.

How to Find Circular References in Excel: Using Excel Options

On the Excel Options dialog box, select the Formulas menu from the sidebar and then uncheck the checkbox for Enable iterative calculation.

Now, the workbook or worksheet will automatically show you the circular reference warning.

If it doesn’t show instantly, save the workbook, close it, and reopen it to see the warning.

Once you see the warning check the Excel Status Bar to know which cell is in the circular reference.

How to Resolve Circular References in Excel

The sureshot way to correct one or many circular reference errors on Excel is by manually editing each formula that contains it. In this way, you can resolve both accidental and indirect circular references in Excel.

Another easy way is to remove the formula from the current cell and place it somewhere else within the same worksheet.

How to Find Circular References in Google Sheets (Bonus Method)

Like Excel, Google Sheets doesn’t allow any circular references in one or more formulas if you haven’t activated the iterative calculation feature. Hence, Google Sheets will immediately show the #REF! error on the cell that contains a circular formula.

Therefore, you can simply select all the cells that contain the #REF! warning to find and resolve circular references on Google Sheets.

Excel Circular References: Final Words

So, now you know all the secrets for how to find circular references in Excel. Use the method you like and ditch all the circular references to ensure you don’t get confused by unreliable data from an Excel report or mathematical dashboard.

How To Remove Junk Photos In Whatsapp

How to Remove Junk Photos in WhatsApp

Also Read:- How to Find & Delete Hidden Photos with Photos Cleaner

Benefits of using Systweak Photos Cleaner:-

On a Android smartphone, unwanted images may be eliminated by removing the one you don’t want & arranging the remainder into folder. Manual completion of the work is possible, but will take efforts & long time. Instead, we may use third-party programs to remove photographs from a Android phone. One of the best photo cleanup apps is Systweak Software’s Photos Cleanup. Some essential aspects are listed below:

It’s simple to use.

User interface and steps are some factors to think about while assessing a app. The Photos Cleaner program has a basic structure & is straightforward to use without training or specific instructions.

All Images are sorted

Users may categorize images using a photo cleaning program like Photos Cleaner by name, date & size.

Remove the picture cache.

Before deleting, have a look at the preview.

After photos have been scanned, users may check them before deleting them with the Photos Cleaner app. Users can decide which photographs to delete and whether or not to keep duplicates.

Scanners are used to look through both external and internal storage.

The Image Cleaner examine the phone’s external SD card as well as internal storage, allowing users to remove all of the photographs on their Android device.

Improves Android handset performance.

When the transitory and cache photograph are eliminated, the Android smartphone runs quicker and performs better.

Way To Eliminate Junk Photos From WhatsApp?

The most tempting feature of Photos Cleaner program is how little efforts & time it takes & how convenient to use. Here’s a detailed explanation of how to use the Photos Cleaner app to remove photographs from a Android device:

The File Explorer on your Android phone will now open.

Select the images and tap the Trash Bin Icon on your Android smartphone to permanently erase the hidden photographs or photos cache that you do not want.

Conclusion:-

Photo Cleaner by Systweak is a terrific software that makes recovering images from your SD card on Android cellphones a breeze. Although the bulk of hidden pictures are undesirable images such as junk files and photo cache, there is a handful that may just brighten your day. Another benefit is that you’ll be able to erase old photos and free up valuable storage space on your phone.

Quick Reaction:

About the author

Aayush Yadav

How To Use The If And Function In Excel? Step

What is the IF AND Function in Excel?

IF AND function in Excel is a powerful combination of logical functions that allows users to evaluate multiple conditions in a single formula. It simplifies calculations by returning “TRUE” or “FALSE” results based on whether all specified conditions are met, making data analysis more efficient and accurate.

For example, =IF(AND(A6= “Banana”, B6<=25), “Reorder”, “Don’t Order”) can help decide whether to reorder bananas.

If both conditions are true, the function will return “Reorder” to indicate the need for more bananas; otherwise, it will return “Don’t Order” to signify no need for reordering.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

We can automate processes, reduce errors, and make informed decisions using IF AND statements based on data. They are particularly useful in determining whether an employee qualifies for a promotion or incentive, verifying a customer’s eligibility for a discount, or checking the delivery status of customer orders.

Key Highlights

The IF function’s output depends on whether the AND function returns TRUE or FALSE.

We can nest the IF AND function within other Excel functions for complex tests.

Other logical functions like OR or NOT can replace the AND function based on specific conditions.

Syntax of IF AND Function in Excel

IF(AND(Logical1, Logical2,…), val_if_true, val_if_false)

Explanation:

Logical1, Logical2: Test/ compare values of specified cells

Value_if_true: Value Excel returns if the condition is satisfied

Value_if_false: Value Excel returns if the condition is not satisfied

How to Use the IF AND Function in Excel?

You can download this IF AND Function Excel Template here – IF AND Function Excel Template

#1 Testing Two Conditions

A] Consider the table below consisting of Employee Names, Departments, and the days they were present in six months. Employees are eligible for the promotion if they belong to the Sales department and are present for at least 160 days. We will use the IF AND function in Excel to check the two conditions.

To use the function, follow these steps:

Step 1: Create a new column heading called “Eligible for Promotion.”

Here’s how the formula works:

1. The AND function checks two conditions:

Condition 1 (B6= “Sales” ): If an employee belongs to the Sales department.

2. If the values meet both conditions, the AND function returns TRUE and passes this information to the IF function.

3. The IF function returns the value “Yes” if the condition is TRUE and “No” if it’s FALSE.

Step 2: Press Enter key to see the result

Result- Employee Mike belongs to the Sales department but was present for less than 160 days. As he satisfies only one condition, he is not eligible for the promotion.

Step 3: To check the eligibility of remaining employees, drag the formula into the other cells

B] Below is a table that shows a list of orders along with their respective products, Progress, and Delivery status. We aim to determine whether the final order status has been closed or is still open.

Follow these steps to understand how to check the final order status:

Step 1: Create a new column called “Status.”

Step 2: In an empty cell, such as E6, enter the formula =IF(AND(C6= “Delivered”, D6= “On-time”), “Closed”, “Open”).

Explanation of the formula:

1. The AND function verifies two conditions:

Condition 1 (C6= “Delivered” ): It checks if an order’s Progress is marked as “Delivered.”

Condition 2 (D6= “On-time” ): It verifies if the order was delivered “On-time.”

2. When both conditions satisfy, the AND function returns a TRUE value, passed to the IF function.

3. The IF function returns “Closed” if the condition is TRUE and “Open” if it’s FALSE.

Step 3: Press Enter key to get the result

Result- The Progress status for Order No. 51 is Delivered, and it was delivered on time, i.e., it satisfies both conditions. Therefore, the IF AND formula returns “Closed” for that order.

Step 4: Drag the formula into the remaining cells to check the remaining orders’ current status.

#2 Nested IF AND Statement

A Nested IF AND Statement in Excel is like a set of nested (embedded) boxes where each box has an IF AND condition to test. Here’s an example of a nested IF AND statement.

We have a list of students with their marks in Chemistry and Maths, and we want to evaluate their performance based on their marks in the two subjects.

Here’s how we can use the IF AND Function in Excel to check the student’s performance-

Step 2: In an empty cell (e.g., D6), enter the formula:

Note: It is known as a Nested IF AND statement because one IF AND formula appears inside the other. In other words, two IF AND statements are in a single formula.

Explanation of the Formula:

1. The first AND formula check two conditions:

Condition 1 (B6<30): If marks scored in Chemistry are less than 30

Condition 2 (C6<25): If marks scored in Maths are less than 25.

If both conditions satisfy, the AND function returns TRUE and passes the information to the first IF function, which displays the result as Poor.

2. The second AND formula check two conditions:

If both conditions are satisfied, the AND function returns TRUE and passes the information to the second IF function, which displays the result as Excellent. If not, the formula shows the result as Average.

Step 3: Press Enter key to get the output

Result- Johana has scored more than 60 marks in Chemistry and more than 30 marks in Maths, satisfying both conditions. As a result, the Nested IF AND formula display the output as Excellent.

Step 4: Drag the Nested IF AND formula into the remaining cells to get the performance of the remaining students.

#3 Alternatives to the IF AND Function

There are two logical functions to use instead of AND, depending on the specific situation: OR and NOT.

It is to test whether at least one of several conditions is true.

For instance, the formula =IF(OR(A2= “Apple”, A2= “Orange”), “Fruit”, “Not Fruit”) will display “Fruit” if cell A2 contains either “Apple” or “Orange,” and “Not Fruit” if it has any other value.

It returns the opposite of the result of another logical function.

For example, the formula =IF(NOT(A2= “Banana”), “Not Banana”, “Banana”) will return “Not Banana” if cell A2 does not contain “Banana” and “Banana” otherwise.

Things To Remember

If any argument is an empty cell, the IF AND function ignore it. However, if all arguments are empty cells, Excel returns a #VALUE! Error.

In newer versions of Excel, including Excel 2007 and later versions, you can include up to 255 arguments in the formula of the IF AND function. For previous versions of Excel, Microsoft had limited the AND function to test a maximum of 30 conditions at a time.

You can use a combination of text and numeric values, cell references, boolean values, and comparison operators as arguments in the IF AND function. If the arguments are characters, then AND returns a #VALUE! Error. But it can’t handle strings.

Best Practices

In this formula, “MathGrade” and “ScienceGrade” are named ranges that refer to the appropriate columns in the table.

Avoid common mistakes when using the IF AND function, such as forgetting to close all parentheses or improperly nesting the functions.

Frequently Asked Questions (FAQs) Q1. How do you use IF and AND in Excel together?

Answer: To use the IF and AND functions together in Excel, you can create a formula like this:

=IF(AND(B6=31, C6=” Achieved”),” Eligible”,” Not Eligible”)

In this example, we check whether an employee is eligible for an incentive based on two criteria: attendance and achieving a monthly target.

The AND function checks whether the employee was present 31 days a month (using cell B6) and whether they achieved their monthly target (using cell C6).

If the cell values meet both criteria, the AND function returns TRUE, and the IF function displays “Eligible” as a result.

If the cell values meet either criterion, the AND function returns FALSE, and the IF function displays “Not Eligible” as a result.

Q2. Can you have 2 IF AND statements in Excel?

Answer: Yes, you can use two IF AND statements in a single formula in Excel to check multiple criteria. For instance, consider the formula

In this formula:

The first IF AND statement checks whether a customer has a purchase total greater than or equal to $25,000 and whether the customer chooses to pay through a digital wallet.

If both conditions are met, the formula returns a 20% discount on the total bill.

The second IF AND statement check whether a customer has a purchase total greater than or equal to $20,000 and whether the customer chooses to pay through a credit card.

If both conditions are met, the formula returns a 10% discount on the total bill. If neither of these conditions is met, the formula returns “No Discount“.

Q3. Can you use IF AND and OR together in Excel?

Answer: Yes, you can use IF AND OR functions in Excel to evaluate multiple criteria in a single formula. For example, consider the formula:

=IF(AND(A6=”Chair”,OR(B6=”Green”,B6=”Red”)),0.5*C6,0.9*C6)

In this formula, the AND function checks if the product is a chair and its color is either green or red. If the AND function is TRUE, the IF formula reduces the product price by 50%, and if it is FALSE, it reduces the price by 10%.

Countif Not Blank In Excel

COUNTIF Not Blank in Excel

COUNTIF Not Blank in Excel is used for counting any defined number/text range of any column without considering any blank cell. This becomes possible only by using the COUNTIF function, which follows the defined criteria to get the desired output.

The syntax for COUNTIF is Not Blank in Excel

COUNTIF(Range, Criteria)

The syntax for COUNTIF Function includes 2 parameters which are as follows:

Range = The range we must select from where we will get the count.

Criteria = Criteria should be any exact word or number we need to count.

The return value of COUNTIF in Excel is a positive number. The value can be zero or non-zero.

How to Use?

Using Excel Countif Not Blank is very easy. Here we will see How to use COUNTIF Function to find how many cells are not blank in the sheet. Let’s understand the working of the COUNTIF Function in Excel through some examples below.

You can download this Excel COUNTIF Not Blank Template here – Excel COUNTIF Not Blank Template

COUNTIF Not Blank in Excel – Example #1

We have small data of some random text and numbers in a column. And this column has a blank cell as well. Counting the cell without blank cells for a large amount of data becomes very difficult. So, we will apply the COUNTIF function with the combination of criteria that allow the formula to neglect the blank and give a total number of cells with some value.

If the criteria we have defined are correct, the Function Arguments box will show the box’s output on the bottom left side. It shows the result of our defined range and criteria as 6.

Also, in the below screenshot, we got a count of cells that are not blank as 6. Cell A6 is blank, so COUNTIF has neglected that cell and given the output of the remaining cell count, which has some value (number or text).

COUNTIF Not Blank in Excel – Example #2

There is another method of using COUNTIF, not blank, which counts all selected cells but not blank by directly editing the cell. For this, go to the edit mode of any cell and press the equal “=” sign, enabling all the inbuilt functions of Excel. Type COUNTIF and select it there, as shown in the screenshot below.

Pressing “=” (Equal sign) in any cell enables all the functions available in Excel. And even if we type selective words (Let’s say “Count”), as shown in the screenshot below, it will give all the possible functions available. From there also, we can select the function as per our requirement.

And press Enter key. We will get the count of cells with the value, “6”, but we selected 7 cells, including cell A6, which is blank. Here also, COUNTIF functions count the total cells that are not blank.

But if we put incorrect criteria, we may get an error message, which will explain the problem, as shown in the screenshot below. Here, we have removed “” (Inverted Commas) from the Criteria and got the error for testing.

Note:

Using “&” in a formula, we can add more criteria per our requirement.

Pros of Excel COUNTIF Not Blank in Excel

Excel’s Countif Not Blank feature efficiently counts non-blank cells, saving time when working with large data sets.

It gives an instant and exact result.

The COUNTIF formula is fully automatic and easy and instant to use.

It is very helpful in accounting work.

Things to Remember

Always check the data if it is migrated output of a different source. There are some chances that the data may contain blank cells with hidden values. In that case, filter the blank cell and delete the cell values to avoid incorrect output.

Always unhide the sheet or column to get the exact result.