lOMoARcPSD|37376985

MS excel - lab work

Professional ethics (Anna

University)

Scan to open on Studocu

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Studocu is not sponsored or endorsed by any college or

university

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Microsoft Excel is a software product designed and developed to store, organize and
manipulate structured data. Excel Worksheet plays a vital role in offering multiple
functionalities to ease the tedious process of managing data.

What is an Excel Worksheet?

An Excel worksheet is a software program/document that collects rows and columns

designed to store information in an organized manner. An Excel worksheet also enables users
to apply mathematical and statistical logic to the data and manipulate it according to the
requirements of the business strategies.

So, this was a brief introduction to Excel Worksheet. In the next section, you will learn how
to create a new Excel Worksheet.

2a. Create worksheets, insert and format data

Steps to create Excel Worksheet

Downloaded by Kritik Shahi
 lOMoARcPSD|37376985

To create a new Excel worksheet, you need to follow the steps mentioned below.
• By default, when you start Microsoft Excel, there is an option of selecting a variety of
worksheets, as shown below.

• You can select the option based on the requirements. For now, create a
blank worksheet. The blank worksheet looks as shown below.

Steps to insert a New Excel Worksheet

• To insert a new worksheet, you can right-click on the sheet name in the bottom
sheet tracker tray, as shown below.

• By selecting the new worksheet option from the available options, you can create a
new worksheet as displayed below.

Steps to format data in Excel

Suppose you have the data as shown below:

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Here are some ways you can format this data to make it look better:

• Give a border
• Make the headers Font Bold
• Give a cell color to headers
• Center Align the Headers

The Format Cell dialogue box is one-stop shop for all data formatting in Excel. It has
the following options

• Number: This gives you the access to number formatting in Excel. It is a

whole new world in itself. Read more about it here
• Alignment: Text alignment in Excel can be controlled through this option.

• Font: Entire font related formatting, including additional options

of Strikethrough, Superscript and Subscript can be accessed
here

• Border: All border styles and its color can be controlled here

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

• Fill: All cell fill options

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

EX 2b Work with different types of data: text, currency, date, numeric etc

Steps to work with numeric data

There are two main ways to choose a number format:
• Go to the Home tab, click the Number Format drop-down menu in
the Number group, and select the desired format.

• You can also click one of the quick number-formatting commands below
the drop-down menu.

• We've applied the Currency number format, which adds currency symbols
($) and displays two decimal places for any numerical values.

Percentage formats
• It displays values as percentages, such as 20% or 55%.
• This is especially helpful when calculating things like the cost of sales tax or a tip
• When you type a percent sign (%) after a number, the percentage
number format will be be applied to that cell automatically.

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Date formats
• Whenever you're working with dates, you'll want to use a date format to tell
the spreadsheet that you're referring to specific calendar dates, such as July 15,
2014.
• Date formats also allow you to work with a powerful set of date functions that
use time and date information to calculate an answer.
• When you enter a date, you'll need to use a specific format your spreadsheet
understands, such as month/day/year (or day/month/year depending on
which country you're in).

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

EX 2c Split, validate, consolidate, Convert data

Steps to Split Data
Use Flash Fill
One of the best additions to Excel in recent years has been Flash Fill. This little button
takes all the hard work out of splitting and combining data.

• Type the first name into a cell.

• Press CTRL+Enter to stay in the same cell.
• From the Data tab, in the Data Tools group, click Flash Fill.
• Or, press the keyboard shortcut CTRL+E.

Use the Ctrl+E shortcut to invoke Flash Fill

Alternatively, you can invoke Flash Fill by typing in the first two names.

• Type the first name into a cell.

• Start typing the second name into a cell.

Excel will ghost down the remaining names. Check these are correct.
• Press Enter to accept.

Using Flash Fill to split data in Excel

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Data validation

Excel Data Validation is a feature that restricts (validates) user input to a worksheet.
Technically, you create a validation rule that controls what kind of data can be entered into a
certain cell.
To add data validation in Excel, perform the following steps.
1. Open the Data Validation dialog box
Select one or more cells to validate, go to the Data tab > Data Tools group, and click
the Data Validation button.
You can also open the Data Validation dialog box by pressing Alt > D > L, with each key
pressed separately.

2. Create an Excel validation rule

On the Settings tab, define the validation criteria according to your needs. In the
criteria, you can supply any of the following:

• Values - type numbers in the criteria boxes like shown in the screenshot below.
• Cell references - make a rule based on a value or formula in another cell.
• Formulas - allow to express more complex conditions like in this example.

As an example, let's make a rule that restricts users to entering a whole number
between 1000 and 9999:

With the validation rule configured, either click OK to close the Data Validation
window or switch to another tab to add an input message or/and error alert.
3. Add an input message (optional)
If you want to display a message that explains to the user what data is allowed in a
given cell, open the Input Message tab and do the following:

• Make sure the Show input message when cell is selected box is checked.
• Enter the title and text of your message into the corresponding fields.
• Click OK to close the dialog window.

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

As soon as the user selects the validated cell, the following message will show up:

4. Display an error alert (optional)

In addition to the input message, you can show one of the following error alerts when
invalid data is entered in a cell

Alert type Description

Stop (default)

The strictest alert type that prevents users from entering

invalid data.You click Retry to type a different value or
Cancel to remove the entry.

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Warning

Warns users that the data is invalid, but does not prevent
entering it.You click Yes to input the invalid entry, No to
edit it, or Cancel to remove the entry.

Information

The most permissive alert type that only informs users about
an invalid data entry.You click OK to enter the invalid value
or Cancel to remove it from the cell.

To configure a custom error message, go to the Error Alert tab and define the following
parameters:

• Check the Show error alert after invalid data is entered box (usually selected
by default).
• In the Style box, select the desired alert type.
• Enter the title and text of the error message into the corresponding boxes.
• Click OK.

And now, if the user enters invalid data, Excel will display a special alert explaining the
error (like shown in the beginning of this tutorial).

Note. If you do not type your own message, the default Stop alert with the following text
will show up: This value does not match the data validation restrictions defined for this
cell.

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

CONSOLIDATION Steps
• Select the upper-left cell of the area where you want the consolidated data
to appear.
• On the Ribbon, Choose Data > Consolidate to view the Consolidate dialog:

• In the Function box, click the summary function that you want Excel to use to
consolidate the data. As you will see from the drop-down, there are 11
functions to choose from. For our data we want to add up the values so we’ll
set the
Function to Sum.

• Click in the Reference area and select the first data range to consolidate – to do
this you will need to click the Sheet tab i.e. “Year 1” and then drag over the
data (including row and column headings) and then click the Add button to add
this
first set of data to the consolidation dialog.

• Continue in the same way by clicking on the next sheet, highlighting the data,
and clicking on the Add button until all your data and worksheets appear in
the References section of the dialog e.g. “Year 2” and “Year 3”.

Top Tip: You can name your ranges before you start the Consolidation process. If
you name each range then, when you create the consolidation, place your cursor in
the

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Reference field, press F3 and then choose the range from the list in the Paste Name
dialog.

To indicate where the labels are located in the source ranges, select the check boxes
under Use labels in: either the Top row, the Left column, or both. In this example, Top
row is the name of the quarters, i.e. Quarter 1, Quarter 2, etc. and the Left Column are
the list of items, i.e. Coffee, Tea and Milk.
Automatic vs. Manual updates: If you want Excel to update your consolidation table
automatically when the source data changes, select the Create links to source data check
box. If unchecked, you can still update the consolidation manually.

When you click OK, Excel summarises all the data into your new sheet as your master
worksheet (Consolidated Summary).

You’ll immediately notice a change to the Excel worksheet that you may never have seen
before. You will see grouping tools down the left of the screen which you can use to
display and hide the data. Next to rows 7, 10 and 14, there are plus signs. This signifies
that cells are part of a group that is currently collapsed. Clicking on the plus sign will
expand the group and there is a line connecting these rows to the left:

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

You’ll find that the second column (Column C) of data shows the name of the
workbook (Core Excel Level 2) that contains the data. You can hide this column if you
want to, by right clicking it and choosing Hide. This simply hides the column so the
data is there should you need to refer to it later on.

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

EX 2d

Sorting data in Excel

There are many options for sorting data.
You can sort data on common attributes, such as:

• text (A to Z, or Z to A)
• numbers (low to high, or high to low)
• dates and times (newest to oldest, or oldest to newest)
• format (e.g. cell colour).

To sort data in Excel:

1. Select a cell in the column you want to sort.

2. In the Data tab, go to the Sort & Filter group. Then you have two options.
1. To sort values in ascending or descending order based on Excel’s
interpretation of the column, click the Sort A to Z or Sort Z to A
icons.
2. For more sorting options, click the Sort button. You can then specify the
Column, what to Sort On, and Order. With the Add Level option, you
can perform a secondary level of sorting if needed.

Filtering data in Excel

To create a filter in Excel:

1. Select the data you want to work with.

2. Select Data > Filter from the ribbon menu.
3. At the top of your selection, select the column header arrow (grey box
with downwards arrow).
4. Select Text Filters or Number Filters, and then select a comparison, such
as Between.

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Enter the filter criteria and select OK.

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

2E. Perform calculations and use functions: (Statistical, Logical, Mathematical, date,
Time etc.,)

1. Use the SUM function to calculate totals.

THE SUM FUNCTION

The SUM function is used when you need to calculate totals for a range of cells or a
group of selected cells on a worksheet. With regard to the Budget Detail worksheet, we
will use the SUM function to calculate the totals in row 12. It is important to note that
there are several methods for adding a function to a worksheet, which will be
demonstrated throughout the remainder of this chapter. The following illustrates how a
function can be added to a worksheet by typing it into a cell location:

1. Click the Budget Detail worksheet tab to open the worksheet.

2. Click cell C12.
3. Type an equal sign =.
4. Type the function name SUM.
5. Type an open parenthesis (.
6. Click cell C3 and drag down to cell C11. This places the range C3:C11 into
the function.
7. Type a closing parenthesis ).
8. Press the ENTER key. The function calculates the total for the Monthly
Spend column, which is $1,496.

Figure 2.11 shows the appearance of the SUM function added to the Budget Detail
worksheet before pressing the ENTER key.

Figure 2.11 Adding the

SUM Function to the Budget Detail Worksheet
As shown in Figure 2.11, the SUM function was added to cell C12. However, this
function is also needed to calculate the totals in the Annual Spend and LY Spend
columns. The function can be copied and pasted into these cell locations because of
relative referencing. Relative referencing serves the same purpose for functions as it
does for formulas. The following demonstrates how the total row is completed:

1. Click cell C12 in the Budget Detail worksheet.

2. Click the Copy button in the Home tab of the Ribbon.
3. Highlight cells D12 and E12.
4. Click the Paste button in the Home tab of the Ribbon. This pastes the
SUM function into cells D12 and E12 and calculates the totals for these
columns.
5. Click cell F11.
6. Click the Copy button in the Home tab of the Ribbon.
7. Click cell F12, then click the Paste button in the Home tab of the Ribbon. Since
we now have totals in row 12, we can paste the percent change formula into
this row.

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Figure 2.12 shows the output of the SUM function that was added to cells C12, D12, and
E12. In addition, the percent change formula was copied and pasted into cell F12.
Notice that this version of the budget is planning a 1.7% decrease in spending compared
to last year.

THE COUNT FUNCTION

Data file: Continue with CH2 Personal Budget.
The next function that we will add to the Budget Detail worksheet is the COUNT
function. The COUNT function is used to determine how many cells in a range contain a
numeric entry. The COUNT function will not work for counting text or other non-
numeric entries. For the Budget Detail worksheet, we will use the COUNT function to
count the number of items that are planned in the Annual Spend column (Column D).
The following explains how the COUNT function is added to the worksheet by using the
function list:

1. Click cell D13 in the Budget Detail worksheet.

2. Type an equal sign =.
3. Type the letter C.
4. Click the down arrow on the scroll bar of the function list (see Figure 2.17)
and find the word COUNT.
5. Double click the word COUNT from the function list.
6. Highlight the range D3:D11.
7. You can type a closing parenthesis ) and then press the ENTER key, or
simply press the ENTER key and Excel will close the function for you. The
function produces an output of 9 since there are 9 items planned on the
worksheet.

Figure 2.17 shows the function list box that appears after completing steps 2 and 3
for the COUNT function. The function list provides an alternative method for adding
a function to a worksheet.
Figure 2.17 Using the Function List to Add the COUNT Function
Figure 2.18 shows the output of the COUNT function after pressing the ENTER
key. The function counts the number of cells in the range D3:D11 that contain a
numeric value. The result of 9 indicates that there are 9 categories planned for this
budget.

Figure 2.18 Completed COUNT Function in the Budget Detail Worksheet

THE AVERAGE FUNCTION
The next function we will add to the Budget Detail worksheet is the AVERAGE
function. This function is used to calculate the arithmetic mean for a group of numbers.
For the Budget Detail worksheet, we will use the function to calculate the average of the
Downloaded by Kritik Shahi
 lOMoARcPSD|37376985

values in the Annual Spend column. We will add this to the worksheet by using the
Function Library. The following steps explain how this is accomplished:

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

1. Click cell D14 in the Budget Detail worksheet.

2. Click the Formulas tab on the Ribbon.
3. Click the More Functions button in the Function Library group of commands.
4. Place the mouse pointer over the Statistical option from the drop-down list
of options.
5. Click the AVERAGE function name from the list of functions that appear in
the menu (see Figure 2.19). This opens the Function Arguments dialog box.
6. Click the Collapse Dialog button in the Function Arguments dialog box
(see Figure 2.20).
7. Highlight the range D3:D11.
8. Click the Expand Dialog button in the Function Arguments dialog box
(see Figure 2.21). You can also press the ENTER key to get the same
result.
9. Click the OK button on the Function Arguments dialog box. This adds
the AVERAGE function to the worksheet.

Figure 2.19 illustrates how a function is selected from the Function Library in the
Formulas tab of the Ribbon.
Figure 2.19 Selecting the AVERAGE Function from the Function Library
Figure 2.20 shows the Function Arguments dialog box. This appears after a function is
selected from the Function Library. The Collapse Dialog button is used to hide the
dialog box so a range of cells can be highlighted on the worksheet and then added to the
function.
Figure 2.20 Function Arguments Dialog Box
Figure 2.21 shows how a range of cells can be selected from the Function Arguments
dialog box once it has been collapsed.

Figure 2.21 Selecting a Range from the Function Arguments Dialog Box
Figure 2.22 shows the Function Arguments dialog box after the cell range is defined for
the AVERAGE function. The dialog box shows the result of the function before it is
added to the cell location. This allows you to assess the function output to determine
whether it makes sense before adding it to the worksheet.
Figure 2.22 Function Arguments Dialog Box after a Cell Range Is Defined for a Function
Figure 2.23 shows the completed AVERAGE function in the Budget Detail worksheet.
The output of the function shows that on average we expect to spend $1,994 for each of
the categories listed in Column A of the budget. This average spend calculation per
category can be used as an indicator to determine which categories are costing more or
less than the average budgeted spend dollars.
Figure 2.23 Completed AVERAGE Function

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

THE MAX AND MIN FUNCTIONS

Data file: Continue with CH2 Personal Budget.
The final two statistical functions that we will add to the Budget Detail worksheet are
the MAX and MIN functions. These functions identify the highest and lowest values in a
range of cells. The following steps explain how to add these functions to the Budget
Detail worksheet:

1. Click cell D15 in the Budget Detail worksheet.

2. Type an equal sign =.
3. Type the word MIN.
4. Type an open parenthesis (.
5. Highlight the range D3:D11.
6. Type a closing parenthesis ) and press the ENTER key, or simply press the
ENTER key and Excel will close the function for you. The MIN function
produces an output of $1,200, which is the lowest value in the Annual
Spend column (see Figure 2.24).
7. Click cell D16.
8. Type an equal sign =.
9. Type the word MAX.
10. Type an open parenthesis (.
11. Highlight the range D3:D11.
12. Type a closing parenthesis ) and press the ENTER key, or simply press the
ENTER key and Excel will close the function for you. The MAX function
produces an output of $3,500. This is the highest value in the Annual
Spend column (see Figure 2.25).

Figure 2.24 MIN Function Added to the Budget Detail Worksheet

Figure 2.25 MAX Function Added to the Budget Detail Worksheet

Using the AND function in Excel

The AND function is the most popular member of the logic functions family. It
comes in handy when you have to test several conditions and make sure that all of them
are met. Technically, the AND function tests the conditions you specify and returns
TRUE if all of the conditions evaluate to TRUE, FALSE otherwise.
The syntax for the Excel AND function is as follows:
AND(logical1, [logical2], …)
Where logical is the condition you want to test that can evaluate to either TRUE or
FALSE. The first condition (logical1) is required, subsequent conditions are optional.
And now, let's look at some formula examples that demonstrate how to use the AND
functions in Excel formulas.

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Formula Description

=AND(A2="Bananas", Returns TRUE if A2 contains "Bananas" and B2

B2>C2) is greater than C2, FALSE otherwise.

=AND(B2>20, B2=C2) Returns TRUE if B2 is greater than 20 and B2

is equal to C2, FALSE otherwise.

=AND(A2="Bananas", Returns TRUE if A2 contains "Bananas", B2 is

B2>=30, B2>C2) greater than or equal to 30 and B2 is greater
than C2, FALSE otherwise.

Excel AND function - common uses

By itself, the Excel AND function is not very exciting and has narrow usefulness. But in
combination with other Excel functions, AND can significantly extend the capabilities of
your worksheets.
One of the most common uses of the Excel AND function is found in the logical_test
argument of the IF function to test several conditions instead of just one. For example,
you can nest any of the AND functions above inside the IF function and get a result
similar to this:
=IF(AND(A2="Bananas", B2>C2), "Good", "Bad")

For more IF / AND formula examples, please check out his tutorial: Excel IF function with
multiple AND conditions.
An Excel formula for the BETWEEN condition
If you need to create a between formula in Excel that picks all values between the given two
values, a common approach is to use the IF function with AND in the logical test.
For example, you have 3 values in columns A, B and C and you want to know if a value in
column A falls between B and C values. To make such a formula, all it takes is the IF function
with nested AND and a couple of comparison operators:
Formula to check if X is between Y and Z, inclusive:
=IF(AND(A2>=B2,A2<=C2),"Yes", "No")
Formula to check if X is between Y and Z, not inclusive:
=IF(AND(A2>B2, A2<C2),"Yes", "No")

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

As demonstrated in the screenshot above, the formula works perfectly for all data types -
numbers, dates and text values. When comparing text values, the formula checks them
character-by-character in the alphabetic order. For example, it states that Apples in not
between Apricot and Bananas because the second "p" in Apples comes before "r" in
Apricot. Please see Using Excel comparison operators with text values for more details.
As you see, the IF /AND formula is simple, fast and almost universal. I say "almost" because
it does not cover one scenario. The above formula implies that a value in column B is smaller
than in column C, i.e. column B always contains the lower bound value and C - the upper
bound value. This is the reason why the formula returns "No" for row 6, where A6 has 12,
B6
- 15 and C6 - 3 as well as for row 8 where A8 is 24-Nov, B8 is 26-Dec and C8 is 21-Oct.
But what if you want your between formula to work correctly regardless of where the
lower- bound and upper-bound values reside? In this case, use the Excel MEDIAN function
that returns the median of the given numbers (i.e. the number in the middle of a set of
numbers). So, if you replace AND in the logical test of the IF function with MEDIAN, the
formula will go like:
=IF(A2=MEDIAN(A2:C2),"Yes","No")
And you will get the following results:

As you see, the MEDIAN function works perfectly for numbers and dates, but returns the
#NUM! error for text values. Alas, no one is perfect : )
If you want a perfect Between formula that works for text values as well as for numbers and
dates, then you will have to construct a more complex logical text using the AND / OR
functions, like this:
=IF(OR(AND(A2>B2, A2<C2), AND(A2<B2, A2>C2)), "Yes", "No")

Using the OR function in Excel

As well as AND, the Excel OR function is a basic logical function that is used to compare
two values or statements. The difference is that the OR function returns TRUE if at least one

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

if the arguments evaluates to TRUE, and returns FALSE if all arguments are FALSE. The OR
function is available in all versions of Excel 2016 - 2000.
The syntax of the Excel OR function is very similar to AND:
OR(logical1, [logical2], …)
Where logical is something you want to test that can be either TRUE or FALSE. The first
logical is required, additional conditions (up to 255 in modern Excel versions) are optional.
And now, let's write down a few formulas for you to get a feel how the OR function in Excel
works.

Formula Description

=OR(A2="Bananas", Returns TRUE if A2 contains "Bananas"

A2="Oranges") or "Oranges", FALSE otherwise.

=OR(B2>=40, C2>=20) Returns TRUE if B2 is greater than or equal to 40

or C2 is greater than or equal to 20, FALSE
otherwise.

=OR(B2=" ", C2="") Returns TRUE if either B2 or C2 is blank or both,

FALSE otherwise.

As well as Excel AND function, OR is widely used to expand the usefulness of other Excel
functions that perform logical tests, e.g. the IF function. Here are just a couple of examples:
IF function with nested OR
=IF(OR(B2>30, C2>20), "Good", "Bad")
The formula returns "Good" if a number in cell B3 is greater than 30 or the number in C2 is
greater than 20, "Bad" otherwise.
Excel AND / OR functions in one formula
Naturally, nothing prevents you from using both functions, AND & OR, in a single formula
if your business logic requires this. There can be infinite variations of such formulas that boil
down to the following basic patterns:
=AND(OR(Cond1, Cond2), Cond3)
=AND(OR(Cond1, Cond2), OR(Cond3, Cond4)
=OR(AND(Cond1, Cond2), Cond3)
=OR(AND(Cond1,Cond2), AND(Cond3,Cond4))
For example, if you wanted to know what consignments of bananas and oranges are sold out,
i.e. "In stock" number (column B) is equal to the "Sold" number (column C), the following
OR/AND formula could quickly show this to you:
=OR(AND(A2="bananas", B2=C2), AND(A2="oranges", B2=C2))

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

OR function in Excel conditional formatting

=OR($B2="", $C2="")
The rule with the above OR formula highlights rows that contain an empty cell either in
column B or C, or in both.

For more information about conditional formatting formulas, please see the following
articles:

• Excel conditional formatting formulas

• Changing the row color based on a cell's value
• Changing a cell's color based on another cell value
• How to highlight every other row in Excel

Using the XOR function in Excel

In Excel 2013, Microsoft introduced the XOR function, which is a logical Exclusive OR
function. This term is definitely familiar to those of you who have some knowledge of any
programming language or computer science in general. For those who don't, the concept of
'Exclusive Or' may be a bit difficult to grasp at first, but hopefully the below explanation
illustrated with formula examples will help.
The syntax of the XOR function is identical to OR's :
XOR(logical1, [logical2],…)
The first logical statement (Logical 1) is required, additional logical values are optional. You
can test up to 254 conditions in one formula, and these can be logical values, arrays, or
references that evaluate to either TRUE or FALSE.
In the simplest version, an XOR formula contains just 2 logical statements and returns:

• TRUE if either argument evaluates to TRUE.

• FALSE if both arguments are TRUE or neither is TRUE.

This might be easier to understand from the formula examples:

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

Formula Result Description

=XOR(1>0, TRUE Returns TRUE because the 1st argument is TRUE and
2<1) the 2nd argument is FALSE.

=XOR(1<0, FALSE Returns FALSE because both arguments are FALSE.

2<1)

=XOR(1>0, FALSE Returns FALSE because both arguments are TRUE.

2>1)

When more logical statements are added, the XOR function in Excel results in:

• TRUE if an odd number of the arguments evaluate to TRUE;

• FALSE if is the total number of TRUE statements is even, or if all statements
are FALSE.

The screenshot below illustrates the point:

If you are not sure how the Excel XOR function can be applied to a real-life scenario,
consider the following example. Suppose you have a table of contestants and their results for
the first 2 games. You want to know which of the payers shall play the 3rd game based on the
following conditions:

• Contestants who won Game 1 and Game 2 advance to the next round automatically
and don't have to play Game 3.
• Contestants who lost both first games are knocked out and don't play Game 3 either.
• Contestants who won either Game 1 or Game 2 shall play Game 3 to determine who
goes into the next round and who doesn't.

A simple XOR formula works exactly as we want:

=XOR(B2="Won", C2="Won")

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

And if you nest this XOR function into the logical test of the IF formula, you will get even
more sensible results:
=IF(XOR(B2="Won", C2="Won"), "Yes", "No")

Using the NOT function in Excel

The NOT function is one of the simplest Excel functions in terms of syntax:
NOT(logical)
You use the NOT function in Excel to reverse a value of its argument. In other words, if
logical evaluates to FALSE, the NOT function returns TRUE and vice versa. For example,
both of the below formulas return FALSE:
=NOT(TRUE)
=NOT(2*2=4)
Why would one want to get such ridiculous results? In some cases, you might be more
interested to know when a certain condition isn't met than when it is. For example, when
reviewing a list of attire, you may want to exclude some color that does not suit you. I'm not
particularly fond of black, so I go ahead with this formula:
=NOT(C2="black")

As usual, in Microsoft Excel there is more than one way to do something, and you can
achieve the same result by using the Not equal to operator: =C2<>"black".
If you want to test several conditions in a single formula, you can use NOT in conjunctions
with the AND or OR function. For example, if you wanted to exclude black and white colors,
the formula would go like:
=NOT(OR(C2="black", C2="white"))

Downloaded by Kritik Shahi

 lOMoARcPSD|37376985

And if you'd rather not have a black coat, while a black jacket or a back fur coat may be
considered, you should use NOT in combination with the Excel AND function:
=NOT(AND(C2="black", B2="coat"))
Another common use of the NOT function in Excel is to reverse the behavior of some other
function. For instance, you can combine NOT and ISBLANK functions to create the
ISNOTBLANK formula that Microsoft Excel lacks.
As you know, the formula =ISBLANK(A2) returns TRUE of if the cell A2 is blank. The NOT
function can reverse this result to FALSE: =NOT(ISBLANK(A2))
And then, you can take a step further and create a nested IF statement with the NOT /
ISBLANK functions for a real-life task:
=IF(NOT(ISBLANK(C2)), C2*0.15, "No bonus :(")

Translated into plain English, the formula tells Excel to do the following. If the cell C2 is not
empty, multiply the number in C2 by 0.15, which gives the 15% bonus to each salesman
who has made any extra sales. If C2 is blank, the text "No bonus :(" appears.
In essence, this is how you use the logical functions in Excel. Of course, these examples have
only scratched the surface of AND, OR, XOR and NOT capabilities. Knowing the basics, you
can now extend your knowledge by tackling your real tasks and writing smart elaborate
formulas for your worksheets.

Downloaded by Kritik Shahi