MICROSOFT EXCEL

POWER TIPS AND TRICKS

FILTERS
WHAT IS A FILTER?
Filters allow the user to only see data that meets certain
criteria.
FILTERS
 Click any single cell inside a data set.
 On the Data tab, click Filter.

 Arrows will appear above columns.

 Click and Arrow then use the check
boxes to filter.
UNIQUE VALUES
There are several ways to filter for unique values or
remove duplicate values:
 To filter for unique values, use the Advanced command
in the Sort & Filter group on the Data tab.

 To remove duplicate values, use the Remove

Duplicates command in the Data Tools group on the
Data tab.
VLOOKUP
What is VLOOKUP?
You can use VLOOKUP when you need to find things in a
table or a range by row. For example, look up an
employee's last name by her employee number, or find her
phone number by looking up her last name.
VLOOKUP
Syntax:
 The syntax for the VLOOKUP function is:
VLOOKUP (value, table, index_number, [not_exact_match])
 Parameters or Arguments
 Value: The value to search for in the first column of the table.
 Table: Two or more columns of data that is sorted in ascending
order.
 Index number: The column number in table from which the
matching value must be returned. The first column is 1.
 Not exact match: Optional. It determines if you are looking for an
exact match based on value. Enter FALSE (or 0) to find an exact
match. Enter TRUE (or 1) to find an approximate match, which
means that if an exact match if not found, then the VLOOKUP
function will look for the next largest value that is less than value. If
this parameter is omitted, the VLOOKUP function returns an
approximate match.
NAMED CELLS AND RANGES
 By using names, you can make your formulas much easier
to understand and maintain.
 A name can be shorthand that makes it easy to
understand the purpose of a cell reference, constant,
formula, or table.
 All names have a scope, either to a specific worksheet
(local worksheet level) or to the entire workbook (global
workbook level).
NAMED CELLS AND RANGES
 Name box on the formula bar: This is best used for
creating a workbook level name for a selected range.

 Create a name from selection: Right click your

selection and select Define Name…
 Define Name box: This is best used for when you
want more flexibility in creating names.
SUMIF
What is SUMIF?
You can use the SUMIF function to sum the values in a
range that meet criteria you specify. For example, suppose
that in a column of numbers you want to sum only the
values that are larger than 30.
SUMIF
Syntax:
 SUMIF (range, criteria, [sum_range])
 Parameters or Arguments:
 Range: The range of cells that you want evaluated by criteria. Cells in
each range must be numbers or names, arrays, or references that contain
numbers. Blank and text values are ignored.
 Criteria: The criteria in the form of a number, expression, a cell
reference, text, or a function that defines which cells will be added. For
example, criteria can be expressed as 32, ">32", B5, "32", "apples", or
TODAY().
 Important: Any text criteria or any criteria that includes logical or
mathematical symbols must be enclosed in double quotation marks ("). If
the criteria is numeric, double quotation marks are not required.
 Sum_Range: The actual cells to add, if you want to add cells other
than those specified in the range argument. If the sum_range argument
is omitted, Excel adds the cells that are specified in the range argument
(the same cells to which the criteria is applied).
SUMIFS
What is SUMIFS?
You can use the SUMIFS function in Excel to sum the
values in a range of cells that meet multiple criteria. For
example, you might use the SUMIFS function in a sales
spreadsheet to add up the value of sales of a specified
product by a given sales person
SUMIFS
Syntax:
 SUMIF (sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2, ... criteria_range_n, criteria_n] )
 Parameters or Arguments
 Sum_Range: The cells to sum.
 Criteria_Range1: The range of cells that you want to apply criteria
1 against.
 Criteria1: Used to determine which cells to add. criteria1 is applied
against criteria_range1.
 Criteria_Range_n: The range of cells that you want to apply
criteria_n against. There can be up to 127 ranges.
 Criteria_n: Used to determine which cells to add. Criteria_n is
applied against criteria_range_n. There can be up to 127 criteria.
COUNTIF
WHAT IS COUNTIF?
You can use COUNTIF to count the number of cells that
meet a criterion; for example, to count the number of times
a particular city appears in a customer list.
COUNTIF
Syntax:
 COUNTIF( range, criteria )
 Parameters or Arguments
 Range: The range of cells that you want to count based on
the criteria.
 Criteria: The criteria used to determine which cells to count.
COUNTIFS
WHAT IS COUNTIFS?
You can use COUNTIFS to count the number of cells
that meet multiple criteria; for example, to count the
customers in a particular city who spend greater than
$1000 per year
COUNTIFS
Syntax:
 COUNTIFS(criteria_range1, criteria1, [criteria_range2,
criteria2, ... criteria_range_n, criteria_n] )
 Parameters or Arguments
 Criteria_Range1: The range of cells that you want to apply
criteria1 against.
 Criteria1: The criteria used to determine which cells to
count. criteria1 is applied against criteria_range1.
 Criteria_Range_n: The range of cells that you want to apply
criteria2, ... criteria_n against. There can be up to 127 ranges.
 Criteria_n: Used to determine which cells to count. criteria2
is applied against criteria_range2, criteria3 is applied against
criteria_range3, and so on. There can be up to 127 criteria.
IF / THEN
WHAT IS IF AN IF STATMENT?
You can use an IF statement to check whether a condition
is met, and then return one value if that condition is True
and another value if that condition is False.
IF STATEMENT
Syntax:
 IF(condition1, value_if_true1, value_if_false 1)
This is the equivalent of an IF THEN ELSE statement.
 Parameters or Arguments
 Condition: The value that you want to test.
 Value_if_true: The value that is returned if condition
evaluates to TRUE.
 Value_if_false: The value that is return if condition evaluates
to FALSE.
Advanced Tip: You can nest IF statements
within each other if you really want to get
serious!
PIVOT TABLES
What is an Excel Pivot Table?
A pivot table is basically a user-created summary table of
your original spreadsheet. You create the table by defining
which fields to view and how the information should be
displayed. Based on your field selections, Excel organizes
the data so you see a different view.
PIVOT CHARTS
What is an Excel Pivot Chart?
A pivot chart is a visual representation of a pivot table in
Excel. Pivot charts are a good way to provide quick visual
representation of pivot table data. To insert a pivot chart:
 Click any cell inside the pivot table.
 On the Insert tab, click Column and select one of the
subtypes. For example, Clustered Column.
PIVOT TABLES
PIVOT CHARTS
800 0.3

700 0.29

600 0.28

500 0.27

Batting Average
400 0.26
Sum of HR
Sum of RBI
300 0.25

200 0.24

100 0.23

0 0.22

STL
ARI

BAL
ATL

BOS
CHC

COL

KC
CIN
CLE

CWS

MIL
MIA

SEA

TOR
MIN
DET

PHI

SD

SF

TEX
HOU

LAA

NYY
OAK
LAD

NYM

PIT

TB

WAS
WILDCARD SEARCHES
 * (Asterisk) – It represents any number of characters.
For example, ex* could mean excel, excels, example,
expert, etc.
 ? (Question mark) – It represents one single
character. For example, T?p could mean Tip , Top, or Tap.
 ~ (Tilde) – It is used to identify a wildcard character (~,
*, ?) in the text. For example, if you want to find the
exact phrase excel* in a list.
NESTING
 Nesting can be used when there are multiple conditions
to be met.
 A nested function uses another function as one of the
arguments. You can nest up to 64 levels of functions.

 IF within IF (30/30 Club)

=IF(J2>=30,IF(L2>=30,"YES","NO"),"NO")

 COUNTIF within IF (Begins with)

=IF(COUNTIF(A2,"G*"),"YES","NO")
QUESTIONS