Transcript EXCEL ADVANCED - Algonquin College

EXCEL ADVANCED
1
Mathematical Operators for
Excel
•
•
•
•
•
•
•
<
>
=
>=
<=
<>
^
•
•
•
•
•
•
•
Less than
greater than
Equal
Greater than or equal
Less than or equal
Not equal
Power of
2
Functions
• SUMIFS
• Adds the cells in a
range that meet
multiple criteria
• COUNTIFS
• Applies criteria to
cells across multiple
ranges and counts
the number of times
all criteria are met
The key difference between these and Countif/Sumif is that these
allow the use of multiple criteria. Countif/Sumif do not
3
DATA TABLES
• A data table is a range of cells that shows how
changing one or two variables in your formulas
will affect the results of those formulas
• To create a Data Table select
data and click Insert tab, Table
(in table group)
• Convert a table to a range of data Click
anywhere in the table, click on Design tab then
click Convert to
Range in Tools group.
4
DATA TABLES
• Can be used to Calculate Options
• In example sheet in cell J2 type =G3 then
select I2:J15
• Click Data tab, What-if-analysis,
then Data Table
• In Data Table, Column input
cell, click D4, and click OK
5
Protecting Worksheets
• Two step process, first unlock cells you
want user to change
– Select cells you want unlocked
– Home tab, Font group, click on Dialogue Box
expander, click on Protection tab, and remove
check mark from “Locked” choice
6
PROTECT SHEETS
• REVIEW tab > CHANGES group >
PROTECT
• SHEET button
• select the options you
want to be protected
> OK
7
APPLY CONDITIONAL FORMATTING WITH A RULE
• Select cell range
• HOME tab > STYLES group >
CONDITIONAL FORMATTING > NEW
RULE
8
CONDITIONAL FORMATTING WITH A RULE cont.
• Select a RULE TYPE:
• Set your parameters:
• Select the formatting you want by clicking on
the
button at the bottom
9
SORT BY MULTIPLE FIELDS
• HOME tab > EDITING group > SORT
& FILTER Button > CUSTOM SORT
• For each category you want
to sort by, click on the
ADD LEVEL button
10
AUTOFILTER
• Select a range of cells containing data.
• HOME tab > EDITING group >
SORT & FILTER button > FILTER
• Drop-down arrows will now
Appear beside each
Column heading
• Select the drop-down arrow and:
• De-select: SELECT ALL
• Then select the checkbox beside
the option you wish to sort by
11
SUBTOTALS
• DATA tab >
•
Note that data should be sorted to get best results
•
You can automatically calculate subtotals and grand totals
in a list for a column by using the Subtotal command in the
Outline group on the Data tab.
12
PIVOT TABLE
• Are used to summarize, analyze, explore, and
present summary data
• Select the range
• INSERT > click on
PivotTable
•  My table has headers is selected > OK
13
Modify A PivotTable So That A Column Displays The
MAXIMUM Value, Instead Of The SUM
• Select the cell which has the desired
COLUMN HEADING
• OPTIONS tab > ACTIVE FIELD group >
FIELD SETTINGS button
• In the list, select the
Desired function > OK
14
PIVOT TABLE
• Drag the fields you want
into the areas you want
15
PIVOT TABLE cont.
• Format a PivotTable using a Pivot style
• Click the DESIGN tab:
• Light styles
• Medium styles
16
PIVOT CHART BASED ON A PIVOT TABLE
• PIVOT TABLE TOOLS > OPTIONS > TOOLS group >
PivotChart button
•
in the PivotChart Filter Pane
when you create the PivotChart
• Click on the drop-down arrow beside
the 1st category name
• De-select: SELECT ALL
• Then select the categories you want to be
Able to view in your PivotChart > OK
which pops up
17
GOAL SEEK
• Automatically vary the contents of one cell
– so that the value of the contents of another cell
equals a certain amount
• Click DATA tab > DATA TOOLS group >
"WHAT-IF ANALYSIS" icon >
GOAL SEEK
• In the SET CELL textbox, key in the cell
you want the ANSWER to appear in
• In the BY CHANGING CELL textbox,
key in the cell reference you want
changed in order to get the desired answer > OK
18
FREE “TIP OF THE WEEK”
19