Data validation,
whole numbers is
to restrict the cell
to accept only
whole numbers.
Select the range that you will
limit date entries with specify
character length.
Click data validation then
settings.
Make the rule you want.
The Excel LOOKUP function performs
an approximate match lookup in a
one-column or one-row range, and
returns the corresponding value from
another one-column or one-row
range. LOOKUP's default behavior
makes it useful for solving certain
problems in Excel.
A VLOOKUP function exists of 4 components:
1) The value you want to look up.
2) The range in which you want to find the value and the return value.
3) The number of the column within your defined range, that contains
the return value.
4) 0 or FALSE for an exact match with the value you are looking for; 1
or TRUE for an approximate match.
Similar to VLOOKUP, we have
another function called
HLOOKUP() or horizontal
lookup. The function HLOOKUP
looks for a value in the top row
of a table or array of benefits. It
gives the value in the same
column from a row you specify.
The Excel XLOOKUP function is a
modern and flexible replacement
for older functions like VLOOKUP,
HLOOKUP, and LOOKUP.
XLOOKUP supports approximate
and exact matching, wildcards (*
?) for partial matches, and
lookups in vertical or horizontal
ranges.
The Excel DGET function
gets a single value in a
given field from a record
that matches criteria.
DGET will throw the
#NUM error if more than
one record matches
criteria.
Combine data with the Ampersand symbol (&)
Type = and select the first cell you want to
combine. Type & and use quotation marks with a
space enclosed. Select the next cell you want to
combine and press enter. An example formula
might be =A2&" "&B2.
The Excel AND
function is a logical
function used to
require more than one
condition at the same
time. AND returns
either TRUE or FALSE.
The Excel OR function
returns TRUE if any
given argument
evaluates to TRUE,
and returns FALSE if
all supplied
arguments evaluate to
FALSE.
IF function is one of the
most popular functions, and
it allows you to make
logical comparisons
between a value and what
you expect.
IFS function checks whether
one or more conditions are
met and returns a value that
corresponds to the first
TRUE condition. IFS can take
the place of multiple nested
IF statements and is much
easier to read with multiple
conditions.
Multiple IF statements
require a great deal of
thought to build correctly
and make sure that their
logic can calculate
correctly through each
condition all the way to
the end.
You can test for specific
combinations of
conditions in Excel
using the IF function in
conjunction with the
AND and OR functions.
1) Select a cell within your data.
2) Select Home, Format as Table.
3) Choose a style for your table.
4) In the Format as Table dialog
box, set your cell range.
5) Mark if your table has headers.
6) Select OK.
Select the table, first change to color to white then go to table design
and click convert to range.
If you have a table, do this
simple technique for easier life.
At the bottom of the table, just
press ALT + = then it will give
you the result same in the
picture, just click on the drop-
down list then you can select
which formula do you need. It
will also count only the item
you only selected.
Select data then convert it to a table, click table design and select slicer.
The INDEX function
returns a value or
the reference to a
value from within a
table or range.
The MATCH function
searches for a
specified item in a
range of cells and
then returns the
relative position of
that item in the
range.
=INDEX() returns the value of a
cell in a table based on the
column and row number.
=MATCH() returns the position
of a cell in a row or column.
Combined, the two formulas can
look up and return the value of a
cell in a table based on vertical
and horizontal criteria.
The RANK function returns
the rank of a number within
a set of numbers. The RANK
function is a built-in function
in Excel that is categorized
as a Statistical Function.
Is a method of writing a formula in
a document so copying that
formula to another cell does not
change the cell its formula
references. To apply this press F4,
if its not working, just hold down
the FN key before you press F4.
