EXCEL FORMULARS

AND FUNCTIONS
FOR BEGINNERS 2021
A Practical Guide with illustrations on How to use Microsoft Excel
Formulas and Functions with Ease

John Shipman
 Copyright
All rights reserved. No part of this book may be reproduced or used in any manner without the prior
written permission of the copyright owner, except for the use of brief quotations in a book review.
While the advice and information in this book are believed to be true and accurate at the date of
publication, neither the authors nor the editors nor the publisher can accept any legal responsibility
for any errors or omissions that may be made. The publisher makes no warranty, express or implied,
with respect to the material contained herein.
Printed on acid-free paper.

Printed in the United States of America

© 2021 by John Shipman
 Table of Contents
CHAPTER ONE
Carry out basic and advanced calculations with formulas in Excel.
Checking a formula
How does Excel formula look
Calculation operators in Excel formula
Using functions and Nested functions in Excel formula
Define and use names in Excel formula
How to create or change a cell reference
How to create a cell reference on the same worksheet
Using the Link cell command to create a cell reference
How to remove or delete a formula
How to delete a formula but keep the result of the formula (for Windows
users)
Delete an array formula
Delete a formula but keep the results (for web users)
Avoiding broken formula in Excel
CHAPTER TWO
The “IF” function
The “IF” syntax
Simple IF cases
Using the “IF” function to confirm if a cell is blank
The IFS Function
Simple syntax
The Counta Function
The COUNTIFS function
EXCEL KEYBOARD SHORTCUTS (for Windows users)
Ribbon keyboard shortcuts
Use the Access keys for ribbon tabs
 Navigate through the main areas
Navigate the ribbon tabs
Navigate the ribbon
Navigate the worksheet
Use Search
VLOOKUP
How to get started
How to combine your data from many tables onto a single worksheet
using the VLOOKUP function
ABOUT AUTHOR
 CHAPTER ONE
Carry out basic and advanced calculations with
formulas in Excel.
Make a formula that can be used to refer to values in some other cells
- Click on a cell
- Enter the equal to (=) sign
- Choose a cell or enter the cell address in the selected cell

- Fill in the appropriate operator in the cell. For instance, + for

addition.
- Hit the Enter key and you will see the result of your calculation
appearing inside the cell with the formula.

Checking a formula
- When you enter a formula inside a cell, you will also see the
formula appearing in the formula bar. The space highlighted in red
below is the formula bar.

- To view a particular formula, choose a cell containing the identity of

the formula and you will see the formula appearing in the formula
bar.
 How does Excel formula look

In the screenshot above, the number 1 to number 4 represent the following;

1. Functions: The pi function will return the pi value which is

3.142…
2. References: The A2 will return the actual value in the cell A2.
3. Constants: Numbers or texts that are just directly present in a
formula
4. Operators: The caret ^ operator in the above formula will raise a
particular number to a certain power.

Calculation operators in Excel formula

Operators are the ones that usually tell the kind of calculation that you plan
to carry out on the elements of a particular formula. The general
mathematical prompts for mathematical calculation are also the one that
Excel emulates in carrying out its mathematical operation. These
mathematical prompts include; Parentheses , Exponents , Multiplication
and Division , and Addition and Subtraction , or for easy remembrance
the acronym PEMDAS (which can means Please Excuse My Dear Aunt
Sally). When you use parentheses in an operation, you will be able to
change the order of calculation.
Types of operators: There exists four important categories of calculation
opera-tors which include; arithmetic , reference, comparison , and text
concatenation.
- The Arithmetic operators
To properly carry out basic mathematical calculations, like subtraction,
multip-lication, addition or division; combine various numbers; and bring
out numeric result, the following arithmetic operators are best;
- Plus sign: Use the + sign for addition purpose. Example 3+8
- Minus sign: Use the minus sign for subtraction purpose. Example 5
–3
- Asterisk: Use this sign for multiplication purpose. Example 5*3
- / Forward slash: Use this sign for division purpose. Example 12/4
- Percent (%) sign: Use the percent sign to carry out percentage
operation. Example 60%
- Caret (^): Use this symbol to raise a certain number to the power
of another.
- The Comparison operator: Two values can be compared by using
the operators below. When you compare two entities with these
operators, you will get a logical value, which can either be True or
False. See screenshot below;

- Concatenation operator: The ampersand symbol (&) is a

concatenation operator that can be used to combine one or more text
strings to get a single piece
 - Reference Operator: Can be used to combine some range of cells
for the purpose of calculation.

Using functions and Nested functions in Excel

formula
When we talk about predefined formulas that are used to carry out
calculations by using some specific values referred to as arguments using
some particular structure or order, we are most times referred to as
Functions. You can use Functions to carry out simple or hard calculations.
Tap on the Formula tab at the top to explore all of Excel functions.

▪ Excel function syntax

To understand function syntax, consider the Round function below
designed to round off a particular number in cell A10;

1. Structure . An equal to symbol (=) is used to initiate the structure of any

function in Excel, then followed by the name of the function, opening
parenthesis, the function’s argument which is usually separated by a
comma, and a closing parenthesis.
2. Function name : For a list of available functions, select a cell and
click SHIFT+F3 , and you will see the Insert function dialog.
3. Arguments : The arguments can be texts, numbers, logical values like
TRUE or FALSE , error values like #N/A, arrays or cell references. The
designated argument must be one that can give a value that is valid for the
argument. The Arguments can as well be formulas, constant or some other
functions.
4. Argument tooltip . As you are entering the function, you will see a
tooltip with the arguments and syntax. For instance, enter =ROUND ( and
you can see the tooltip coming up. Note that the Tooltip is only available for
built-in functions.
▪ Entering Excel functions
When you build a formula that has a function, you will be able to enter
worksheet function with the Insert Function dialog box. Once a function
is selected from the Insert Function dialog, a function wizard will be
launched by Excel, which will show the function name, function’s
description, argument description, the result’s function, and the entire
formulas result.

To make it especially easier to build and edit formulas while at the same
time minimizing syntax and typing error, you can enter an = (equal sign)
and starting letters of an errors, use Formula AutoComplete . After
function, Excel will bring a dynamic drop-down list of arguments, valid
functions, and names that match those letters. You can then choose one
from the drop-down list and it will be entered for you by Excel.

▪ Nesting Excel functions

In few cases, there might be a need for you to use a function as one of the
argume-nts of another function. For instance, the formula below uses a
nested AVERAGE function and then compares the output with the value 50.

1. The SUM and the AVERAGE functions are nested within the IF function.
Valid returns : When you use a nested function as an argument, the nested
function must be able to return the exact type of values that the argument
uses. For instance, the nested function must send back a TRUE or FALSE
value. If the function is unable to do this, then Excel will bring a #VALUE!
error value.
Nesting level limits : One formula can have up to 7 levels of nested
functions. When a particular function, say function B, is deployed, as an
argument in another function say Function A, we will say that the function
B is acting as a second level function. For instance, the SUM function and
the AVERAGE Function are considered to be second-level functions if
they are deployed as arguments of the IF function. A function that is nested
within the nested AVERAGE function will now be considered as a third-
level function, and so on.

Define and use names in Excel formula

When you use names in a formula, the formula can be made very easy to
maintain and comprehend. You will also be able to define names for a cell
range, constant, function, or table. Once you follow the practice of
deploying names in your workbook, the names become easy to audit,
update and manage.
Naming a cell (For Windows users)
Choose a cell.
Enter a name inside the Name Box.

Click Enter.
Define names from a selected range
Choose the range that you wish to name, including the column or
row labels.
Choose Formulas, and tap Create from Selection.
From the dialog box of the Create Names from Selection , assign
the location containing the labels by clicking on the Left column,
Top row , Bottom row , or the Right column check box.
Click OK .

Excel will assign names to the cells based on the labels in the designated
range.
Use names in formulas
Choose a cell and input a formula.
 Position your cursor exactly where you plan to deploy the name in
the formula.
Enter the first letter of the name, and choose the name from the
displayed list.

Or, choose Formulas , select Use in Formula and choose the name that
you wish to use.
Click Enter.
Managing names in your workbook with Name Manager
On the Ribbon, navigate to Formulas, click on Defined Names and select
Name Manager . From there, you can create, delete, edit and locate all the
names deployed in the workbook.
Naming a cell (For macOS users)
Choose a cell.
Enter a name inside the Name Box.

Click Enter.

Define names from a selected range

Choose the range that you wish to name, including the column or
row labels.
Choose Formulas, and tap Create from Selection.
From the dialog box of the Create Names from Selection , assign
the location containing the labels by clicking on the Left column,
Top row , Bottom row , or the Right column check box.
Click OK .

Excel will assign names to the cells based on the labels in the designated
range.

Use names in formulas

 Choose a cell and input a formula.
Position your cursor exactly where you plan to deploy the name in
the formula.
Enter the first letter of the name, and choose the name from the
displayed list.

Or, choose Formulas , select Use in Formula and choose the name that
you wish to use.
Click Enter.

Managing names in your workbook with Name Manager

On the Ribbon, navigate to Formulas, click on Defined Names and select
Name Manager . From there, you can create, delete, edit and locate all the
names deployed in the workbook.
Creating a name range in Excel
Check below to design a name range for your Excel spreadsheet;

1. Select Define Name from the Define Names selection from

the Formulas tab.
 2. You will be prompted with a “New Name” dialog box where you
can enter the name that you wish to use. Be aware that this name
here cannot be more than 255 characters in length.
3. The scope of the named range can be specified by selecting
Workbook or the worksheet name from the Scope dialog box as
shown in the above screenshot.
You will have a named range that has been set to a Workbook scope
available for use throughout the workbook, whereas you will only be able to
use a range set to a particular sheet's scope right within the sheet only.

4. Although optional, you can fill in a descriptive comment inside

the Comm-ent box, up to about 255 characters.
5. Inside the Refers to box, carry out any of the actions below;
▪ Select Collapse Dialog (and the dialog box will shrink
temporarily), choose the cells on the worksheet, and tap Expand Dialog
.
▪ To input a constant, simply type = (equal sign) and then enter the value
of the constant.
▪ To input a formula, simply type = and then enter the formula.

6. Select OK .

How to create or change a cell reference

A cell reference can be used to refer to a particular cell or a range of cells
on a particular worksheet and can often be deployed inside a formula to
allow Excel calculate the value or data that you want the formula to find.
In a single or many formulas, cell reference can be used to refer to;
▪ Data from single or more adjacent cells on a worksheet.
▪ Data available in different parts of a worksheet.
▪ Data available on other worksheets inside the same workbook.
See the screenshot below for example;
 How to create a cell reference on the same
worksheet
1. Click on the cell where you wish to enter your formula.
2. Inside the formula bar , enter the = (equal sign).
3. Carry out any of the actions below;

▪ Reference a single or more cells : Click on a cell or range of cells

available on the same worksheet to create a reference.
The border of the cell selection can be dragged in order to move the
selection, or expand selection by dragging the edge of the border.
▪ Reference a defined name : You can create reference to a specific
name by carrying out any of the action below;
▪ Enter the name.
▪ Click F3, choose the name inside the Paste name box, and
then tap OK.
4. Perform any of the actions below;

- Click Enter if a reference is being created in a single cell.

- Use the Ctrl + Shift + Enter when you are creating a reference in an
array formula (such A1:G4.
The reference can actually be a range of cells or a single cell, and the array
formula can also be a formula that can calculate multiple or single results.

Using the Link cell command to create a cell

reference
As an alternative, cell reference can be copied and pasted, and you can then
deploy the Link Cells command to create your cell reference. The Link
cells command can be used to;
- Easily show important details in a way that will make them look
very prominent. Let us assume that you have a particular workbook
that has many worksheets, and on each of the worksheets there exists
 a cell that shows summary information about the other cells on that
same worksheet. If you want to make the summary cells to appear
more prominent, a cell reference can be created to them on the very
first worksheet of the workbook, which allows users to access
summary information about the entire workbook on the very first
worksheet.
- Make it especially very easy to design cell references between
workbooks and worksheets. The Link Cells command will paste the
actual syntax for users automatically.
To create a cell reference by using the Link Cell command;
- Select the cell where the data that you want to link is available.
- Select Ctrl+C, or navigate to the Home tab, and tap Copy from
the Clipboard group.

- Click Ctrl+V, or navigate to the Home tab, and click Paste from
the Clipboard group category.
By default, you will see the Paste Options button once you paste
the data you copied.

- Select the Paste Options button, and then select Paste Link .
To create a cell reference if you are using the Excel for Web, follow the
prompts below;
- Click on the cell where you wish to enter your formula.
- Inside the formula bar , enter the = (equal sign).
- Carry out any of the actions below;
▪ Reference a single or more cells : Click on a cell or range of cells
available on the same worksheet to create a reference.
The border of the cell selection can be dragged in order to move the
selection, or expand selection by dragging the edge of the border.
▪ Reference a defined name : You can create reference to a specific
name by carrying out any of the action below;
▪ Enter the name.
▪ Click F3, choose the name inside the Paste name box, and
then tap OK.
- Perform any of the actions below;
- Click Enter if a reference is being created in a single cell.
- Use the Ctrl + Shift + Enter when you are creating a reference in an
array formula (such A1:G4.
The reference can actually be a range of cells or a single cell, and the array
formula can also be a formula that can calculate multiple or single results.

How to remove or delete a formula

Deleting a particular formula will also delete the result of the said formula.
If you are not planning to delete the result of the formula, you can instead
remove the formula. To delete a formula;
- Click on the cell that or the range of cells where the formula is
contained.
- Select Delete.

How to delete a formula but keep the result of the

formula (for Windows users)
To do this, the formula should be copied and then pasted inside the same
cell by using the Paste Values option.

1. Click on the cell or the range of cells where the formula is

contained.

If the said formula is an array formula,all the cells in the range of cells that
contain the array formula should be selected first.
 Select a cell from the array formula.
From the Home tab, under the Editing group, select Find &
Select , and then tap Go To .
Select Special .
Choose Current array .

2. From the Home tab, in the Clipboard group, select Copy .

3. From the Home tab, in the Clipboard group, tap the arrow below
Paste , and then choose Paste Values .

Delete an array formula

To delete a particular array formula, ensure that all the cells in the cell range
that contains the array formula are selected. To do this;
Select a cell from the array formula.
From the Home tab, under the Editing group, select Find &
Select , and then tap Go To .
Select Special .
Choose Current array .
Select DELETE .

Delete a formula but keep the results (for web users)

1. Choose the cell or the range of cells where the formula is

contained.
2. Choose Home , and select Copy (or simply use the Ctrl + C
shortcut).

3. Select Home , click on the arrow below Paste and choose Paste
Values .
 Avoiding broken formula in Excel
When Excel is unable to resolve any formula you are trying to create, you
will most often get an error prompts below;

Unfortunately, what this indicates is that the Excel program is unable to

compr-ehend what you are about to do, so you might consider starting over.
Begin by choosing OK or simply click on ESC button to dismiss the error
prompt.
You can then go back to the cell that has the broken formula (you will meet
the cell in Edit mode ready to be edited), and the Excel program will
highlight the actual spot where the problem originates from.If you are
confused about what you want to do from here, you can consider starting
over by pressing the ESC again, or choose the Cancel button inside the
formula bar, which will bring you out of the edit mode.
If you decide to move forward, you can consider the following
troubleshooting steps to guide you in figuring out what actually went
wrong;
- The formula is not calculating: This is usually a common problem
exp-erienced by users. When you observe that your formula is not
calculating as expected, you should check if you have enabled
Automatic calculation. Your formula will not compute if it is the
manual calculation that is enabled. Proceed with the prompts below to
confirm automatic calculation;
- Click on the File tab, choose Options , and then click on the Form-
ulas category.
- From the Calculation options section, under Workbook
Calculation , ensure that you select the Automatic option.
[[

- Does your function actually start with an equal to sign?

If the formula you entered doesn’t start with an equal to (=) sign, then it is
obviously not a formula, and will not be computed. This is also a common
mistake, and you should try and avoid this as much as possible.
When you enter calculation such as SUM(B2:B10) , Excel will instead
display the text string SUM(B2:B10) instead of bringing forth a formula.
Also, if you enter 12/3, Excel will bring a date, such as 3-Dec or
12/03/2021, instead of actually dividing 12 by 3. To avoid
misrepresentation like this, endeavor to always start your function with an
equal to symbol. For instance, type: =SUM(B2:B10) and =12/3 .
- Is the opening parentheses matching with the closing
parentheses?
When a particular function is being used in a formula, each of the
opening parentheses will require a closing parenthesis for that particular
function to work effectively. Ensure that all the parentheses you are
using are a matching pair of parentheses. For instance, the
formula =IF(B5<0),"Not valid",B5*1.05) will not work simply
because the closing parentheses there are two, but there is only one
opening parentheses. The formula will be correct if it actually looks like:
=IF(B5<0,"Not valid",B5*1.05) .
- Does your formula contain multiplication without the asterisk
sym-bol?
It is a common mistake to think that Excel will accept x as a
multiplication operator instead of an asterisk (*). By now, you should
understand that Excel only recognizes asterisk as its multiplication
operator. If a constant is present in your formula, Excel will display an
error message, and the program will help fix the formula by substituting
the asterisk symbol in place of an x.

However,when you deploy cell references,the program will output a

#NAME? error.
[

- Ensure that you are evaluating a nested formula one after the
other
Evaluating a formula allows you to see how a nested or complex formula
com-putes the final result.
1. Choose the formula that you wish to evaluate.
2. Click Formulas , and select Evaluate Formula .

3. Choose Evaluate to confirm the actual value of the underlined

expression. You will get the result of the evaluation in italics.
 4. If the underlined area of the formula is actually a reference to
another formula, click Step In to display the other formula in
the Evaluation box. Click on Step Out to navigate back to the
previous formula and cell.

The Step In button will not be available the second time the reference
shows in the formula—or if the formula refers to a cell in another
workbook.
5. Continue until every part of the formula has been completely
evaluated.

The Evaluate Formula tool will not necessarily indicate why your formula
is actually broken, but it can actually help to point out where the formula is
broken. This can be a very useful tool in larger formulas where it can seem
very difficult to troubleshoot the problem.
 CHAPTER TWO
The “IF” function
The “IF” is among the most common functions in Excel, and it enables users to
carry out logical comparisons between expectations and a specific value.
In essence, an IF statement can contain two results. The first result being when the
comparison is true, and the second result being when the comparison is false.
For instance, =IF(B3=”Yes”,1,2) says IF(B3 = Yes, then return a 1, else output a
2).

The “IF” syntax

The “IF” function can be considered a logical function capable of returning one a
value if a condition is true, and return a different value if the condition is deemed
false.
IF(logical_test, value_if_true, [value_if_false])
For instance:
▪ =IF(A2>B2,"Over Budget","OK")
▪ =IF(A2=B2,B4-A4,"")
 Simple IF cases

▪ =IF(C2=”Yes”,1,2)
In the case above, the cell D2 implies: IF(C2 = Yes, then return a 1, else output a
2)

▪ =IF(C2=1,”Yes”,”No”)
In this instance, the formula in the cell D2 implies: IF(C2 = 1, then output Yes,
otherwise output No). It then becomes obvious that the “IF” function can be
deployed to evaluate both values and texts. The “IF” function can as well be used
to evaluate errors. With the “IF” function, you are not only limited to confirming if
one term is equal to another term and outputting a single result, in fact you can
deploy mathematical operators and carry out extra calculations depending on what
you want. Multiple “IF” functions can also be nested together if you plan to do
multiple comparisons.

▪ =IF(C2>B2,”Over Budget”,”Within Budget”)

In the above example, the IF function in D2 is saying IF(C2 Is Greater Than B2,
then return “Over Budget”, otherwise return “Within Budget”)

▪ =IF(C2>B2,C2-B2,0)
In the example above, rather than returning a text result, mathematical
computations will be returned instead. The formula in E2 above implies IF(Actual
is Greater than Budgeted, then Subtract the Budgeted amount from the Actual
amount, else return nothing).

▪ =IF(E7=”Yes”,F5*0.0825,0)
In the case above, the formula in F7 implies IF(E7 = “Yes”, then compute the
Total Amount in F5 * 8.25%, otherwise no Sales Tax is due so return 0)
Note: If you want to have texts inside a formula, the text should be wrapped in
quotes. One exception to this is using the TRUE or FALSE, which Excel actually
understands.

Using the “IF” function to confirm if a cell is blank

Sometimes you will have to confirm if a particular cell is blank, maybe because
you don't know your formula to bring an output without a tangible input.

In a case like this, you can deploy the IF with the ISBLANK function:
▪ =IF(ISBLANK(D2),"Blank","Not Blank")
Which means that IF(D2 is blank, then return "Blank", else return "Not Blank") .
You can as well decide on your own formula to be used for the "Not Blank"
condition. In the following case, we will be using "" instead of ISBLANK. The ""
actually means "nothing".

=IF(D3="","Blank","Not Blank")
The above formula implies IF(D3 is nothing, then return "Blank", else "Not
Blank") . See below for a common means of using "" to prevent your formula from
calculating if a dependent cell is actually blank:
▪ =IF(D3="","",YourFormula())
IF(D3 is nothing, then return nothing, else calculate your formula).

The IFS Function

The IFS function can be used to confirm whether one or more conditions have
been met, and then output a value that truthfully corresponds to the first TRUE
condition. IFS can be used in place of multiple nested IF statements, and is
actually much easier to read with multiple conditions.

Simple syntax
Generally, the IFS function has a syntax that looks like the one below;
=IFS([Something is True1, Value if True1,Something is True2,Value if True2,
Something is True3,Value if True3)
Please note that the IFS function can actually allow users the chance to test up to
127 different conditions. However, it is not advisable to nest too many conditions
with the IFS or IF statement. This is actually because we don't recommend nesting
too many conditions with IF or IFS statements. This is because multiple conditions
need to be entered in the correct order, and can be very difficult to build, test and
update.
Example 1

The formula for cells A2:A6 is:

▪ =IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F")
Which implies IF(A2 is bigger Than 89, then “A” should be returned, IF A2 is
bigger Than 79, a “B” should be returned, and so on and “F” should be returned
for any other values that are particularly less than 59).
Case 2
The formula in cell G7 is: =IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5
,D6,F2=6,D7,F2=7,D8)
Which implies IF(the value in cell F2 equals 1, then the value in cell D2 should
be returned, IF the value in cell F2 equals 2, then the value in cell D3 should be
returned, and so on, finally ending with the value in cell D8 if none of the other
conditions are met).
Remarks
▪ To indicate a default result, input TRUE for the final logical_test argument. If
none of the other conditions are met, the corresponding value will be returned.
In the Example 1 above, rows 6 and 7 (that contains grade 58) actually
demonstrate this.
▪ If a logical_test argument is provided without a corresponding value_if_true ,
the function will bring a "You've entered too few arguments for this function"
error prompt.
▪ If a logical_test argument is being evaluated and a value different from the
TRUE or FALSE value is returned, this function will output a #VALUE! error.
▪ If no TRUE conditions available, the function will returns #N/A error.

The Counta Function

The COUNTA function will counts the number of active cells in a range that are
not empty.
Syntax
COUNTA(value1, [value2], ...)
Then following arguments are contained in the syntax of the COUNTA function;
▪ value1 Required. This is the first argument that represents the values that
you plan to count.
▪ value2, ... Optional. This is like an additional argument that represents the
values that you plan to count, up to a maximum of 255 arguments.
Remarks
▪ The COUNTA function actually counts cells that contain any kind of
information, including empty texts ("" ) and error values. For instance, if the
range has a formula that can output an empty string, then the COUNTA
function will counts that value. The COUNTA function will not compute cells
that are empty.
▪ If you do not need to count texts, logical values, or error values (in other
words, if you only plan to count only the cells that have numbers), deploy the
COUNT function.
▪ If you plan to count only the cells that meet some specific criteria, deploy the
COUNTIF function or the COUNTIFS function.
Example
The Example data in the example below can be copied by users and then paste
inside the cell A1 of a new Excel worksheet. You will have to select formulas for
them to display results, click F2 and then hit the Enter button. If you wish, the
column width can be adjusted to access all the data.

The COUNTIFS function

The COUNTIFS function uses criteria to cells across many ranges and also
counts the actual number of times that all criteria are met.
Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
The following arguments are contained in the syntax of the COUNTIFS function:
▪ criteria_range1 Required. The first range in which to evaluate the
associated criteria.
▪ criteria1 Required. The criteria in the form of an expression, number, cell
reference, or text that indicate which cells will be counted. For instance, criteria
can be expressed as 67, ">88", A4, "orange", or "32".
▪ criteria_range2, criteria2, ... This is optional. Additional ranges and their
associated criteria. Up to 127 range/criteria pairs are actually allowed.
Important: Each extra range must contain the same number of columns and rows
as the argument for criteria_range1 . The ranges do not actually need to be
adjacent to each other.
Remarks
▪ At a time, each range's criteria will be applied to one cell. The count will
normally increase by one (1) if all of the first cells actually meet their
associated criteria. The count will usually increase by one (1) again when all of
the second cells also meet their associated criteria, and this will continue until
all cells have been evaluated completely.
▪ Any cell that is empty is treated as zero value by the COUNTIFS function if
the criteria argument is a reference to an empty cell.
▪ You can deploy the wildcard characters— consisting of the question mark (?)
and an asterisk (*) — in criteria. The question mark will match any single
character, while the asterisk will match any character sequence. To find an
actual asterisk or an actual question mark, simply insert a tilde (~ ) before the
character.

EXCEL KEYBOARD SHORTCUTS (for Windows

users)
For some users, working with an external keyboard with keyboard shortcut for
Excel allows them to work more perfectly. For some other users with vision
impairment or some other disabilities, keyboard shortcuts can be better than using
their computer’s touchscreen, and can even provide a viable alternative to using a
mouse.
Notes:
The shortcuts highlighted here only work for US Keyboard layout. If you have a
computer that doesn’t comply with the US Keyboard layout, these shortcuts might
not work.
A positive symbol (+) in a shortcut implies that you will have to click multiple
keys at the same time.
A comma symbol (,) in a shortcut implies that you will have to click multiple keys
in order.
Frequently used shortcuts
The table below highlights many of the most commonly used shortcuts in Excel.
Actions to be done What to do
Terminate a workbook Press Ctrl+W
Launch a workbook Press Ctrl+O
Navigate to the Excel Home tab Press Alt+H
Save your workbook Use Ctrl+S
Copy anything Highlight the item, then hit
Ctrl+C
Paste anything Press Ctrl+V
Undo an action Use Ctrl+Z

Remove a cell contents Delete

Select a fill color Press Alt+H, H
Cut an item/word/anything at all Press Ctrl+X
Navigate to the Insert tab Press Alt+N
Bold Highlight the item, then press
Ctrl+B
Position cell contents with center Press Alt+H, A, C
alignment
Navigate to the Page Layout tab Press Alt+P
Navigate to the Data tab Press Alt+A
Navigate to the View tab Press Alt+W
Open the context menu Use Shift+F10, or
Context key
Add borders in Excel Press Alt+H, B
Delete a column Press Alt+H, D, C
Navigate to the Formula tab Use Alt+M
 Hide the selected rows Ctrl+9
Hide the selected columns Ctrl+0

Ribbon keyboard shortcuts

Related options are usually grouped on tabs. For instance, the Number group
contains the Number Format option on the Home tab. You can bring the ribbon
shortcut (key tips) by pressing the Alt key as letters identified in small images next
to the tabs and options as shown in the image below.

You will be able to combine the Alt key with the Key Tips letters to produce the
Access Keys shortcuts for the ribbon options. For instance, click Alt+H to access
the Home tab, and click on Alt+Q to scroll to the Search field or Tell me field.
Click on the Alt key once again again to access the KeyTips for the options for the
selected tab.
In the Office 2013 and the Office 2010, some of the old Alt key menu shortcuts
will still work, too. Nonetheless, you will still have to know the complete shortcut.
For instance, click Alt, and then click on one of the old menu keys E for (Edit), V
for (View), I for (Insert), and so on. You will receive a notification pops up telling
you that you are using an access key from an earlier version of Microsoft Office. If
you know the entire key sequence, go ahead and use it. If you don't know the
sequence, press Esc and use Key Tips instead.

Use the Access keys for ribbon tabs

To go straight to a tab on the ribbon, click one of the access keys below. You might
see additional tabs depending on what you select in the worksheet.
Use a screen reader to explore and navigate Excel
Experiment with Excel by using your screen reader and the keyboard to navigate
and explore the app elements and main view, and to equally between functions and
views.

Navigate through the main areas

To move between the Excel main areas, click F6 (forward) and click Shift+F6
(backward). The main areas include;
▪ Sheets tabs
▪ Worksheet table grid
▪ Ribbon tabs
▪ Status bar

Navigate the ribbon tabs

The key menu bars in Excel are the ribbon tabs. To get to the ribbon tabs, click on
F6 till you hear "Ribbon tabs," and then followed by the name of the current tab.
To scroll between tabs, deploy the Right and the Left arrow keys. When you get to
a tab, you will see a tab-specific ribbon appearing below it.
The listed below are examples of some of the most common tabs and what you can
achieve with each tabs;
▪ Home
The Home tab is where you can format and align numbers and text, and also add
new columns and rows.
▪ Insert
The Insert tab is where you can add shapes, pictures tables, and charts inside your
worksheet.
▪ Page Layout
The Page Layout tab is where you can set the orientations, margins, and size of
your worksheet page.
▪ Formulas
The Formula tab is where you will be able to add various formulas and functions
inside your worksheet.

▪ Data
This tab will be used to import data from different sources, sort the data and filter
it, and also enjoy data tools like the “remove duplicate rows” tools.
▪ Review
Check the spelling and accessibility of your worksheet, and collaborate with others
using comments and notes.
▪ View
Select a view such as Normal View or Page Layout view, and set the page zoom
level.
▪ Help
This tab is used to access the Microsoft Excel Help, contact support, and drop
feedback.
In addition to the ribbon tabs, you will also need to access the File menu for some
other important commands. To open the File menu, use the Alt+F shortcut.
The File menu will be launched in a new pane. To explore the main commands,
deploy the Down and the Up arrow keys, then utilize the Tab key and the Up and
Down arrow keys to explore the various options for that particular command.
From the File menu, a new workbook can be initiated, save a file, launch an
existing workbook, share or print the document you are working on currently, and
also access Excel options. To dismiss the File menu and get back to your
worksheet, click on the Esc button.
Navigate the ribbon
After successfully navigating to the right ribbon tab as said previously, click the
Tab key to scroll to the ribbon and to explore its options and commands. You can
click Shift+Tab to go backwards. Click Enter to choose or click on the Esc button
to leave the ribbon and go back to the worksheet.
Navigate the worksheet
On opening an Excel sheet workbook, the focus is often on the worksheet table
grid. If the focus has been moved out of the worksheet, click F6 until the screen
announces the location of the table grid cell. See below for how to navigate inside
worksheet and between the other workbooks and sheets;
▪ The arrow keys can be used to navigate between the cells in the grid table.
The screen reader will announce the row and column of each cell including the
content of the cell.
▪ Use the Shift+F10 shortcut to access the context menu for the active cell.
Deploy the Down and Up arrow keys to explore the menu, and click Enter to
choose or click Esc to go back to the worksheet.
▪ To navigate to the previous or next worksheet in your workbook, use F6 until
the name of your active sheet tab is heard, deploy the right and the Left arrow
keys to locate the right sheet, and click Enter to choose it.
▪ To move to the next available workbook when there are more than one opened
workbook, simply press Ctrl+F6. The name of the workbook will be announced
by the screen reader.

Use Search
To navigate through an option or to quickly carry out a particular action, utilize the
Search field. To do this, follow the prompts below;

1. Select the place or the item in your Excel spreadsheet where you plan to
carry out an action. For instance, click on a range of cells in your
spreadsheet.
2. Use the Alt+Q shortcut to navigate to the Search field.
3. Fill in the search words for the exact action that you wish to carry out. For
instance, if you wish to insert a bulleted list, simply type bullets.
 4. Deploy the Down arrow key to navigate through the results.
5. Once you have seen the result that you need, click Enter to select the result
and to carry out the action.

VLOOKUP
Use the Vlook up when you need to locate items in a table or range by rows.
1. Choose a cell.
2. Input =VLOOKUP( and then choose the value that you want Excel to
lookup.
3. Enter a comma (,) and then choose the table or range to look for that value.
4. Enter a comma (,) and the exact number of the column where that lookup
value is actually located.
5. Enter ,FALSE) to locate an exact match.
6. Click Enter.

The VLOOKUP formula can look like this;

=VLOOKUP(A7, A2:B5, 2, FALSE).
In the simplest form, the VLOOKUP function implies:
=VLOOKUP(lookup value, range containing the lookup value, the column number
in the range containing the return value, optionally specify TRUE for approximate
match or FALSE for an exact match).

How to get started

To build syntax for VLOOKUP, the following pieces of information are required;
1. The exact value that you wish to look up, this value is also referred to as
the lookup value.
2. The range where that lookup value is particularly located. For VLOOKUP
to work properly, the LOOKUP value must always be inside the first
column in the range. For instance, if the lookup value is in the cell C2
then, obviously, your range should begin with C.
3. The column number from the range that features the return value. For
instance, if B2:D11 is specified as the range, B should be counted as your
first column, C should be counted as the second column, and so on.
4. Optionally, TRUE can as well be specified if an approximate match is
required or you can specify FALSE if an exact match of the return value is
 needed. If nothing is specified, approximate match or TRUE will always
be taken as the default value.

Now, you can combine all of the above together as follows;

=VLOOKUP(lookup value, range containing the lookup value, the column number
in the range containing the return value, Approximate match (TRUE) or Exact
match (FALSE)).
See the examples of VLOOKUP function below;
 How to combine your data from many tables onto a
single worksheet using the VLOOKUP function
The VLOOKUP can be used to combine as many tables as possible into a single
table, so far one of those tables contains fields in common with all of the other
tables. This can be useful especially when you are required to share a workbook
with those who have older models of Excel that do not actively support data
features with multiple tables as data sources - by simply combining the data
sources into a single table and changing the data source of the data features to the
new table. When you do this, you will be able to use the data features in older
copies of Excel (provided the data feature itself is supported by the older version).

1. The table that contains common fields should be copied onto a new
worksheet. The table should be assigned a name.
2. Click on Data , select Data Tools and choose Relationships to launch
the Manage Relationships dialog box.
 3. For each relationship listed, you should note the following:

▪ The field that links the tables (this will be listed for you in parentheses in the
dialog box): This is actually the lookup_value for the VLOOKUP formula.
▪ The Related Lookup Table name: This will represent the table_array in the
VLOOKUP formula.
▪ The field (column) from the Related Lookup Table that features the data you
need in your new column: You will not be able to see this information inside
the “Manage Relationships” dialog – as you will have to look at the Related
Lookup Table to know which field you actually want to retrieve. You may
decide to note the column number of that column (A=1) - this will be
the col_index_num in your formula.
4. If you wish to add an extra field to the new table, fill in your VLOOKUP
formula inside the first empty column by using the information that you
have gathered in step 3.

In the example above, it was obvious that column G deploys Attorney

(the lookup_value ) to obtain the Bill Rate data from the fourth column
(col_index_num =4) from the worksheet table that contains Attorneys,
tblAttorneys (the table_array ), with the formula =VLOOKUP([@Attorne-
y],tbl_Attorneys,4,FALSE) .
The formula can as well deploy a range reference and a cell reference. In the insta-
nce above, the formula would be =VLOOKUP(A2,'Attorneys'!A:D,4,FALSE ).
5. Continue to add fields until you have all the required fields that you
actually need. If you want to prepare a workbook that has data features
which uses multiple tables, you can simply change the source of data of
the data feature to the new table.
 ABOUT AUTHOR
John Shipman is a certified Microsoft Certified Solutions Expert (MCSE
) with many years of experience working with all of the Microsoft Office
packages. He has written many instructional guides for Microsoft office
packages, especially the Microsoft Word and Excel.