You are reading the article **How To Find Discrepancies In Excel** updated in March 2024 on the website Cattuongwedding.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 April 2024 How To Find Discrepancies In Excel*

Mistakes like a missing period or incorrect spelling may change the entire output. Fortunately, Microsoft Excel provides a set of built-in tools that can prove useful to detect inconsistencies. In this post, we will discuss how to find differences or discrepancies in Excel and get a clean data sheet.

What does discrepancy mean in Excel?

My brand name & Co. total sales = 587

My brand name & Co total sales = 587

As you can see the period is missing in the 2nd example (Co total sales) compared to the 1st one (Co. total sales). This may look normal to you since you already know what is correct, but the computer does not consider it similar.

Likewise, discrepancies in Excel may also arise due to input errors like space after a value, uppercase or lowercase letters, duplicate entries, or inconsistent data formatting. Therefore, we have this detailed guide to explain how to find discrepancies in Excel easily.

How to find discrepancies in ExcelIf that doesn’t help, you can proceed with the below methods to find discrepancies in Excel.

Use Excel Filter to find differences

Find discrepancies in Excel using Conditional Formatting

Detect discrepancies using Excel Add-ins

1] Use Excel Filter to find differencesUsing the Filter function is one of the easiest methods to find out discrepancies in data, especially for Excel sheets with large data and spelling errors. Here we will consider an example of Class 5 test results. Under the Results column, there are two categories Pass and Fail, and some are spelled incorrectly.

2] Find discrepancies in Excel using Conditional FormattingConditional Formatting helps you to identify and compare two or more rows or columns with each other to identify mismatched data. The quickest way to find the difference would be to highlight cells for Duplicate or Unique entries.

However, you can also use other rules like Top/Bottom rules, Data Bars, Color Scales, or Icon Sets to find discrepancies in Excel data.

You can use the IF and IS function to help you to compare cells and detect any differences. So, here, you can use the IF function to find out which cells are identical and correct (TRUE) and which cells are incorrect (FALSE).

Alternatively, you can combine IF and IS functions like ISNUMBER, ISERROR, ISBLANK, etc. to analyze the set value and return TRUE or FALSE based on the outcome.

You can also use the VLookUp function, HLookUp function, or Match function to find any discrepancies in Excel.

Read: Advanced Microsoft Excel Tips and Tricks

4] Detect discrepancies using Excel Add-insOr, you can use the Analysis ToolPak (Excel Add-ins) to analyze and detect discrepancies in Excel data.

Read: How to manage Add-ins in Microsoft Office

Alternatively, you can also use the Excel Compare tool to compare Excel sheets and find discrepancies in the data set.

How do I check data correction in Excel?When you have an Excel sheet where other users can enter their data in a set format specified by you, that’s when you use Data Validation. This tool helps you to correct any anomalies in the data that do not follow the set guidelines or when entered in the wrong format. In this case, Excel prompts an error alert which means you need to find the differences and correct the data using the Data Validation tool.

To know more about how to apply Data Validation to Cells in Microsoft Excel, you can follow our detailed post.

How do I find a mismatch in a column in Excel?While Conditional Formatting (as explained above) can help you find mismatched data easily, the VLookUp function allows you to compare individual cells.

If using the Unique Equals Operator to compare columns in Excel, create a new Result column. Now, in the Result column, add the formula to compare individual cells. For example, =I2=J2, and it will return the result as False if the data doesn’t match. For the ones that match, the result will be True.

I hope this helps.

You're reading __How To Find Discrepancies In Excel__

## How To Freeze Panes In Excel?

Introduction to Freeze Panes in Excel

Freeze Panes in Excel are used to fix any frame, row, or section of the table to access the data located below so that the user can also see the header’s name. There is 3 type of Freeze Panes option available in the View menu tab under the Window section, Freeze Panes, Freeze Top Row, and Freeze First Column. Freeze Panes are used to freeze the worksheet from where we keep our cursor. This freezes both the row and column both. Then to freeze a Row and a Column, we have a separate option to freeze each of them. Once we do that, we will see some portion of the worksheet will only move once we unfreeze it.

Start Your Free Excel Course

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

A Frozen top row to know which parameters we are looking at during a review:

Before Freezing Top Row:

After Freezing Top Row:

This shows how the same dataset looks with a frozen row. This makes it easy to know which parameter we refer to when analyzing data beyond the first few records in the workbook.

A frozen first column to know which record we are evaluating for a particular parameter.

Before freezing the first column:

After freezing the first column:

Freezing Panes allows us to split the dataset into multiple parts to ease analysis: The worksheet gets split into different parts, which can be browsed independently. The figure above compares the dataset with and without the first column frozen. The Grey Lines in the middle of the worksheet indicate where the rows and columns have been frozen.

How to Freeze Panes in Excel?The Freeze Panes feature is not very complicated if we know the database we are working with. In the next few paragraphs, we will learn how to use the features associated with freezing panes and using them for analysis.

Here are a few examples of Freeze Panes in Excel:

You can download this Freeze Panes Excel Template here – Freeze Panes Excel Template

Freeze Top Row – Example #1To do this, we have to perform the following steps:

Select

View

from the Excel toolbar. Select

Freeze Panes

from the view options; this will open a dropdown menu with options to select the rows or columns we want to freeze. Select

Freeze Top Row; this

will keep the active worksheet’s top row in place and allow us to browse the rest of the data without disturbing the top row.

A Tiny grey straight line will appear just below the 1st row. This means the first row is locked or frozen.

Freeze First Column – Example #2Next, we look at the next most commonly used function in the Freeze Pane feature, freezing the first column. This can be done using the following steps:

Select Freeze Panes from the view options. From the dropdown menu, select Freeze First Column, and this would freeze the first column in place, allowing us to browse the rest of the data without disturbing the first column.

A Tiny grey straight line will appear just below the 1st Column. This means the first column is locked or frozen.

These features can be used simultaneously, making it easier for us to analyze data. As we have seen in the examples, knowing the table’s basic structure helps us decide what we want to freeze.

Freeze First Row and First Column – Example #3Freeze the first row and first column.

Here is an example of the practice table with the frozen first row and first column.

This brings us to the most useful function in the freeze panes feature, freezing multiple columns and rows.

I like to use this function the most because it enables the user to freeze rows and unfreeze rows and columns based on any number of parameters depending on the structure of the worksheet’s data.

To freeze the first row and first column, we need to perform the following steps:

Select Cell B2 from the worksheet.

Now, from the view options, select Freeze Panes. From the dropdown that appears, select the first option, Freeze Panes.

These actions freeze the first row and first column in place.

Freeze Multiple Columns – Example #4We can use similar steps to freeze multiple rows and columns. The following steps illustrate this:

Select any cell above which the rows and columns have to stay in place:

Repeat steps 2 and 3 from the previous illustration to freeze all rows and columns above and left of the selected cell.

The solid grey lines indicate that the rows and columns on the top left of the sheet have been frozen. We can also choose a whole row above which we need data to stay in place or a column.

Freeze panes in Excel are an option that makes it very easy for us to compare data in large datasets. In fact, freezing panes in Excel are so useful that software providers provide additional features based entirely on freezing panes in Excel. One such example is the ability to freeze and unfreeze multiple worksheets and tables simultaneously, which many software vendors provide as a product.

Things to Remember

Freeze panes in Excel are a default configuration that can freeze data to the left of the boundary column or above the boundary row, depending on what we choose as a boundary. There are add on available from various software providers to enhance these.

Recommended ArticlesThis has been a guide to Freeze Panes in Excel. Here we discussed how to freeze panes in Excel and different methods to freeze panes in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles to learn more –

## How To Remove #Name? Error In Excel

In this post, we are going to show you a tutorial to fix #NAME? error in Microsoft Excel. Before talking about the tutorial to correct #NAME? error, let us understand why does this error occurs in Excel.

Why do I see #NAME? error in Excel?Here are the possible reasons due to which you see a #NAME? error message in Excel:

When you have entered a wrong formula name or string or there is some typo in the formula, it returns #NAME? error message in the cells.

It indicates that something is wrong with the syntax you have used and that needs to be corrected.

If you have used a formula that refers to a name that is not defined, you will get this error message.

In case there is a colon missing in the range reference, it will return #NAME? error.

You will see a #NAME? error message if you are using some function that needs an add-in, and the add-in is disabled.

Now, how to resolve #NAME? error in Excel? Unlike some other errors in Excel, e.g., #DIV/0! error, this error can’t be fixed using any error handling function like IFERROR. Here, we are going to list some solutions to fix and correct #NAME? error.

How to remove #NAME? Error in ExcelHere are the methods to correct or fix the #NAME? error in your Excel workbooks:

Use formula suggestions or Function Wizard to avoid syntax errors.

Manually check for any typo in the function and correct it.

Check if the name used in the formula is defined in Name Manager.

Ensure that the text values have quotation marks around them.

Enable the add-in required by the used function.

Let have a detailed discussion on these methods now!

1] Use formula suggestions or Function Wizard to avoid syntax errorsMicrosoft Excel displays a list of matching formulas as soon as you start typing the function in the function bar.

Make sure you use a formula from the suggestions rather than typing it all manually. If you type a formula manually, it increases the chances of typing errors and thus showing a #NAME? error.

You can also use the Function Wizard to avoid any syntactic errors. F

2] Manually check for the typo in the function and correct itIf there are some minor typos, you can correct them manually. Just have a look at the function you have used and inspect it to see if there is a spelling error in the formula string.

If so, simply correct it and it will eliminate #Name? error. In case there is some different cause for this error, use another method from this article to fix it up.

3] Check if the name used in the formula is defined in Name ManagerIn case you haven’t defined a name that has a reference in your formula, it returns #Name? error. So, check and define a name using the Name Manager in Excel. You can follow the below steps to do so:

Reenter the formula with the name you just defined and you won’t see the #NAME? error now.

4] Ensure that the text values have quotation marks around themIf you are using text references in the formula, you must enclose them in quotation marks. Else, you will get a #NAME? error message in the cells.

The solution is that look closely at the formula string and check if the quotation marks are added properly. If not, simply put the quotation marks around the text references. This will fix the error.

5] Enable the add-in required by the used functionSome custom Excel functions need add-ins to work. For example, the EUROCONVERT function needs Euro Currency Tools add-in to work. Also, various statistical and engineering macro functions require Analysis ToolPak add-in to be enabled. So, simply enable the required add-in and it won’t return #Name? error.

Here are the steps to enable the add-in in Excel:

In the Add-ins dialog window, enable the relevant checkboxes to turn on the required add-in.

Tap on the OK button and see if the #Name? error is fixed.

That’s it! Hope this article helps you fix #NAME? error in Microsoft Excel.

Now read: How to Fix Runtime Error 1004 in Excel

## How To Use If Function In Excel

Last Updated on September 5, 2023

The IF function is probably one of the most well-known formulas for Excel, as it allows for data points to be compared between a value and what you are expecting.

IF statements are powerful because you can have two results. The first result is going to inform you of whether your comparison is true. The second result will tell you whether your comparison is false.

Here are the basics of IF functions and how you can use them in Excel.

1

Example Of IF Function

In column 1 we have a list of Musical Artists, and in column 2 we have Total Album Sales. For reference, we would consider any album sales over 100,000 good, and any album sales over 200,000 excellent.

Step

1

If Function

If we begin to write “=if(“ into cell C5, you’ll see that it now says logical_test which would represent the Total Album Sales in the second column.

Step

2

Use Comparison Symbols

What this says is is the value of cell B5 greater than 100,000?

Step

3

Use A Reference Point

To make this process even easier, you can have cells that already have this value, and then simply use this cell as a reference point. For example, we already have the value 100,000 in cell C2, so we select this instead of writing 100,000.

Step

4

Use The F4 Key

You can also use the F4 key which will fix the column and row. So the formula will look like this:

If it is greater than 100,000, which in our example it is, then we need to follow up with our formula so that the cell will complete an action.

Step

5

Use Quotation Marks

We put a comma after the value of 1000,000 and then write “Good”. You must use quotation marks when working out of Microsoft Excel, otherwise use a predetermined cell instead.

Our example now looks like this, with the Good selection being used via cell D2:

To add to this, if you want the cell to do nothing if our Total Album Sales value is less than 100,000 we can simply put in a double quotation mark, whilst closing the bracket at the end of the formula.

This will look like this:

What will happen is that in our third column, if the value of an album sales exceeds 100,000 you will see the word Good appear in those columns, and for any albums that didn’t surpass the 100,000 mark, the cell will remain blank.

You can then use this formula to determine whatever data point you are looking to highlight.

Step

6

Add Different Labels

In our example, instead of looking for album sales over 100,000, we can look for album sales under 50,000 and make the IF function list them as Bad.

Or we could add different labels such as exceptional if an album went over 200,000 total sales.

Common IssuesThere are a couple of common errors that will occur using the IF function. One example is that if you have a “0” value in the cell, this means that there was no argument or selection.

Or perhaps you see “#NAME?” in the cell. This represents that a formula has been misspelled, and as such you must make sure you not only use punctuation marks but also you spell your values correctly.

Final ThoughtsThere are many more ways it can be used, but these are the basics. Which should hopefully guide you in the right direction.

## How To Find Exponential Value In Python?

In this article, we will show you how to find the exponential value in python. Below are the various methods to accomplish this task −

Using exponential operator(**)

Using exp() function

Using exp() values with inbuilt numbers

Using pow() function

Using exponential operator(**)The exponential operator(**) can be used to calculate the power of a number.

Since importing a module or calling a function is not necessary, this is the most convenient to use.

The exponential value of an x is xth power of e, Euler’s constant which is an irrational number called Euler’s number and is equal to 2.718281.

Algorithm (Steps)Following are the Algorithm/steps to be followed to perform the desired task −

Use the import keyword to import the math module.

Get the exponential value of a number i.e, e power 2 here (where e= Euler’s constant i.e 2.718281) using the exponential operator(**). We get the value of e using math.e in python.

ExampleThe following program returns the exponential value of a number using the exponential operator(**). −

import

math

(

“Exponential value of e power 2(e^2):”

,

math

.

e

**

2

)

OutputOn executing, the above program will generate the following output −

Exponential value of e power 2(e^2): 7.3890560989306495 Using exp() functionThe exp() function of the math module is used to calculate the power of e i.e. e^x or the exponential of x. The value of e is approximately around 2.71828….

Syntax math.exp(x) Parameters

x(required) − any number either positive or negative. If x has a value other than a number, it will throw an error.

Return Value − calculates e^x and returns a floating point number.

Algorithm (Steps)Following are the Algorithm/steps to be followed to perform the desired task −

Import Math module

Use the exp() function of the math module to get the exponential value of a positive number passed as an argument to it i.e, e power 2 here (where e= Euler’s constant i.e 2.718281).

Print the output.

ExampleThe following program returns the exponential value of a positive number using the exp() function −

import

math

(

“Exponential value of e power 2(e^2):”

,

math

.

exp

(

2

)

)

OutputOn executing, the above program will generate the following output −

Exponential value of e power 2(e^2): 7.38905609893065 For negative numbers and floating-point numbers ExampleThe following program returns the exponential value of negative and floating−point numbers using the exp() function −

import

math

(

“Exponential value of e power -2(e^-2):”

,

math

.

exp

(

–

2

)

)

(

“Exponential value of e power 1.5(e^1.5):”

,

math

.

exp

(

1.5

)

)

(

“Exponential value of e power 0(e^0):”

,

math

.

exp

(

0

)

)

OutputOn executing, the above program will generate the following output −

Exponential value of e power -2(e^-2): 0.1353352832366127 Exponential value of e power 1.5(e^1.5): 4.4816890703380645 Exponential value of e power 0(e^0): 1.0 Using exp() values with inbuilt numbers ExampleThe following program returns the exponential values of inbuilt numbers like chúng tôi , math.e using exp() function −

import

math

(

“Exponential value of inbuilt number math.pi:”

,

math

.

exp

(

math

.

pi

)

)

(

“Exponential value of inbuilt number math.e:”

,

math

.

exp

(

math

.

e

)

)

OutputOn executing, the above program will generate the following output −

Exponential value of inbuilt number chúng tôi 23.140692632779267 Exponential value of inbuilt number math.e: 15.154262241479262 Using pow() functionIn Python, the pow() function calculates the power of any positive integer. It returns the value of x to the power of y (x^y).

Syntax pow(x,y) Parameters

x − It is the numerical value (base value)

y − It is the power of numerical value (exponent value)

Algorithm (Steps)Following are the Algorithm/steps to be followed to perform the desired task −

Create a variable to store the input base value which is e here(where e= Euler’s constant i.e 2.718281). Get the value of e using math.e.

Create another variable to store the exponent value.

Use the pow() function to print the resultant power of a number i.e, base^ exponent by passing the input base, and exponent values as arguments to it and print the resultant power.

ExampleThe following program returns the exponential value of the input number using the pow() function −

import

math

inputBase

=

math

.

e

inputExponent

=

3

(

“Value of e raised to the power 3(e^3)=”

,

pow

(

inputBase

,

inputExponent

)

)

OutputOn executing, the above program will generate the following output −

Value of e raised to the power 3(e^3)= 20.085536923187664 ConclusionWe learned how to find the exponential number in Python using several ways in this tutorial. We also studied how the exp() function works with various types of numbers.

## How To Use Vba Union Function In Excel?

VBA Union

As the word itself suggests, union means joining one or more things. In VBA Union means joining two or more ranges together. This function is similar to the range function in excel. This is the most common situation in our work when we need to combine one or more ranges with each other. Union function comes in very handy in those situations.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

As explained above, VBA Union functions combine one or more ranges. We can use this function to combine ranges with some common criteria. For example, if our data has a value less than a specific value, we can use this function to combine those ranges and highlight them.

Syntax of VBA Union in ExcelThe syntax for the Union function is as follows:

So, for example, if we want to combine a range A1: A5 and B1: B5, we will use the following formula,

Union (Range (“A1:A5”), Range (“B1:B5”)

First, let us ensure we have a developer’s tab enabled from the files tab in the options section to start using VBA in Excel.

How to Use VBA Union Function in Excel?We will learn how to use a VBA Union function with a few examples in excel.

You can download this VBA Union Excel Template here – VBA Union Excel Template

Example #1 – VBA UnionIn the first example, let us try to select two ranges together. Let us select A1:A5 and B1:B5 range together in this example.

Follow the below steps to use the VBA Union function in Excel:

Step 1: We need to open the VB editor from visual basic, which is in the developer’s tab.

Step 3: Once we are in the code window, name the macro as follows,

Code:

Sub

sample()End Sub

Step 4: Since we will work with sheet 1, we must activate it first to use its properties.

Code:

Sub

sample() Worksheets("Sheet1").ActivateEnd Sub

Step 5: Now, we will use the union function to combine the two ranges we have discussed above with the following code.

Code:

Sub

sample() Worksheets("Sheet1").Activate Application.Union(Range("A1:A5"), Range("B1:B5")).SelectEnd Sub

Step 6: Once we execute the code above, we can see in sheet 1 that those two ranges are in our selection. Press F5 or do it manually from the run button to see the result.

In the above example, we have only selected the two ranges, but we can do much more which we will learn in the next examples.

Example #2 – VBA UnionNow in this example, let us select two ranges as above together and change their interior color. We can change the format or change values once we combine and select the ranges together.

Code:

Sub

Sample1()End Sub

Step 3: Now, let us activate sheet 2 first since we will use the properties of sheet 2 in this example.

Code:

Sub

Sample1() Worksheets("Sheet2").ActivateEnd Sub

Step 4: Combine two ranges, A1:B5 and C1:D5, with the range function and change the interior color to a dark red by the following code.

Code:

Sub

Sample1() Worksheets("Sheet2").Activate Application.Union(Range("A1:B5"), Range("C1:D5")).Interior.Color = 255End Sub

Step 5: Execute the above and see the result in sheet 2 as follows,

After combining them, we have changed the color of the ranges as we can see that they are still in selection.

Example #3 – VBA UnionNow let’s use the union function to display the address after combining ranges. We will combine range A1:C4 and E1:F4 and display the address in the Immediate window. An immediate window is just below our code window, or we can press CTRL + G to bring it up.

Step 2: Name the macro name for this third example.

Sub

Sample2()End Sub

Step 3: Declare two variables as a range in the next step as follows.

Code:

Sub

Sample2()Dim

rng1As

RangeDim

itemAs

RangeEnd Sub

Step 4: Now set an rng1 variable as the union of the range A1: C4 and E1: F4 as follows,

Code:

Sub

Sample2()Dim

rng1As

RangeDim

itemAs

RangeSet

rng1 = Union(Range("A1:C4"), Range("E1:F4"))End Sub

Step 5: Now use for loop to bring the address of these cells from the combined ranges by the following code,

Code:

Sub

Sample2()Dim

rng1As

RangeDim

itemAs

RangeSet

rng1 = Union(Range("A1:C4"), Range("E1:F4"))For

Each

itemIn

rng1Debug

.Next

itemEnd Sub

Step 6: Once we run the above code, we can see the result in the immediate window as follows,

Application of VBA UnionThe following syntax uses VBA union:

Expression.Union(range1, range2,…..)

Here we can use as many ranges as we require.

Things to RememberThere are a few things that we need to remember about the union in VBA:

The union is used to combine two or more ranges together.

The ranges we give to the function must exist to avoid an error.

Instead of Application. The union we can simply use the union as we are working in Excel itself.

Recommended ArticlesThis is a guide to VBA Union. Here we discuss how to use Excel VBA Union Function, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

Update the detailed information about **How To Find Discrepancies In Excel** on the Cattuongwedding.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!