using microsoft excel as - usfws · tails phase 5 t a i l s racking ntegrated ogging ystem nd 1...
TRANSCRIPT
![Page 1: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/1.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
1
UsingMicrosoft Excel
asa TAILS extension
Pivot Tables & Pivot Charts
![Page 2: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/2.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
TAILS Reports: Getting your data out♦TAILS has a large set of Reports, including;
– Office Reports, Ad-Hoc Reports, Geographic Reports, etc♦Query these focused reports and review result,♦Reports provide query result summary / export.
Office ReportsAd-Hoc Reports
Setting a Query DefinitionReviewing Report Result
Summary & Exports
Excel as anExport Option
Using Excel as a TAILS extension: Pivot Tables/Charts
![Page 3: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/3.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Trends of Utah CPAs with ‘Power’ Action/Work TypesAll Utah Conservation Planning Assistance Activities
TAILS Reports: Getting your data out♦Excel is seen as an extension to TAILS
– No need to recreate Excel’s capabilities in TAILS– Hierarchical Sorting, Filtering, Pivot Tables/Charts
Using Excel as a TAILS extension: Pivot Tables/Charts
Raw data export from TAILS Storytelling Data/ChartsTurn this In to this
Here’s how.
![Page 4: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/4.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
ndFY3FY1 FY2
Primary Action / Work Type A
Primary Action / Work Type B
Primary Action / Work Type C
Primary Action / Work Type D
Primary Action / Work Type E
# # #
# # #
# # #
# # #
# # #
Pivot Table Example ♦Let’s look at Utah’s ‘Power’ CPAs over time
– A number of reports could get us the raw data– Let’s get a count of ‘Power’ Activities per year– We’ll use Pivot Tables/Charts to tell the story
Using Excel as a TAILS extension: Pivot Tables/Charts
All Utah Conservation Planning Assistance Activities
Raw data export from TAILS # = The number of CPA Activities in this Primary Action/Work Type & in this Fiscal Year with ‘power’in the Primary Action/Work Type Description.
Only Primary A/W T w/ ‘power’
Trends of Utah CPAs with ‘Power’ Action/Work Types
Storytelling Data/Charts
![Page 5: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/5.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
5
Selecting the data to ‘pivot’♦Select the entire dataset with Control + ‘A’ or
Using Excel as a TAILS extension: Pivot Tables/Charts
Click here.
Data tablewill
‘highlight’in
light blue.
![Page 6: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/6.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
6
Build a Pivot Table♦From the ‘Insert’ tab, select ‘Pivot Table’.
Using Excel as a TAILS extension: Pivot Tables/Charts
Selectthe ‘Insert’
tab.
Select toinsert a
‘Pivot Table’
![Page 7: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/7.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
7
Build a Pivot Table♦Accept the defaults and click ‘OK’.
Using Excel as a TAILS extension: Pivot Tables/Charts
Acceptdefault.
Put intoa new
worksheet.
Click OK.
![Page 8: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/8.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
8
Build a Pivot Table♦Understanding the ‘Pivot Table Wizard’.
Using Excel as a TAILS extension: Pivot Tables/Charts
Pivot Table Field List
Pivot Table Filter Area
Pivot Table Columns Area
Pivot Table Row Area
Pivot Table Values Area
Pivot Table Worksheet Area
Pivot Table Wizard
![Page 9: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/9.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Build a Pivot Table♦Place a field (Primary Action/Work Type) in Row Labels.
Using Excel as a TAILS extension: Pivot Tables/Charts
9
Single clickon
Field Namein
Field List
HoldMousebutton
down and drag to
Row Area
♦Place a field (Primary Action/Work Type) in Row Labels.
ReleaseMouseButton
![Page 10: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/10.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Build a Pivot Table♦Place a field (Fiscal Year) in Column Labels.
Using Excel as a TAILS extension: Pivot Tables/Charts
10
Single clickon
Field Namein
Field List
HoldMousebutton
down and drag to
ColumnsArea
ReleaseMouseButton
♦Place a field (Fiscal Year) in Column Labels.
![Page 11: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/11.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Build a Pivot Table♦Place a field (Activity_Code) in Values Area.
Using Excel as a TAILS extension: Pivot Tables/Charts
11
Single clickon
Field Namein
Field List
HoldMousebutton
down and drag toValuesArea
ReleaseMouseButton
♦Place a field (Activity_Code) in Values Area.
![Page 12: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/12.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Build a Pivot Table♦Filter the Content: Select only desired columns
Using Excel as a TAILS extension: Pivot Tables/Charts
12
Activate‘ColumnLabels’Picklist
Uncheck‘blank’
EveryActivityhas FYso there
will be no‘blanks’
Click‘OK’
![Page 13: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/13.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Build a Pivot Table♦Filter the Content: Select only desired rows.
Using Excel as a TAILS extension: Pivot Tables/Charts
13
Activate‘Row
Labels’Picklist
Select‘LabelFilters’
Click‘OK’
Let’s getonly those
with ‘Power’
Select‘Contains’
Type‘Power’
![Page 14: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/14.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
14
Pivot Table is complete♦Number of ‘power’ CPAs per FY
Using Excel as a TAILS extension: Pivot Tables/Charts
Presented in this way – you, your staff or your manager may better be able to:• Visualize patterns• See relationships• Portray ratios
and more.What data is associated with these
values?
Double-click a value in a Pivot Table to see the original data that made up that count
Data may also be easily portrayed as:• Percent of Row,• Percent of Column• Percent Difference• Count, Sum, Max. Min and Std Devand more.
![Page 15: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/15.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
15
Pivot Table is complete♦Number of ‘power’ CPAs per FY
Using Excel as a TAILS extension: Pivot Tables/Charts
Other than Utah’s Power CPAs slowing,there is not much of a ‘story’ here.Do you need to tell a story?Try making a pivot table to help.orOtherwise use Pivot Tables to make yourdata more presentable or more useful.
♦ Determine the data you need to tell your story, or♦ Otherwise better understand and portray the information,♦ Run a TAILS Report that will provide that needed data,♦ Export that report result to Excel, and♦ Make a Pivot Table
This was only an introduction to Pivot Table Reports
Your turn.
Make aPivot Table
![Page 16: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/16.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Build a Pivot Chart from a Pivot Table♦Let’s make a bar chart from our Pivot Table
Using Excel as a TAILS extension: Pivot Tables/Charts
1616
Assure’Insert’ tab
is active
Selectthe area tochart with
Click, Drag,Release
Select ‘100%
StackedColumnin 3D’
Select‘Column’
from Charts sectionSelecting this chart type allows us to see
the relative amount that each Fiscal Year (column)contributed to each Action/Work Type (row) total.
![Page 17: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/17.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Pivot Chart is complete♦Focused results to address issue or tell story.
Using Excel as a TAILS extension: Pivot Tables/Charts
1717
Fiscal Year(columns)
Legend
Filter accessfor
continuousreselection
FilteredAction/Work
Types(rows)
![Page 18: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/18.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Pivot Tables / Chart are dynamic♦Pivot Tables have dynamic connections to data
Using Excel as a TAILS extension: Pivot Tables/Charts
1818
So you canreselectand seeinstantresults
Furtherfilter
Fiscal Year
Also: youmay edit
the raw dataand the
Pivot Tableand Chartwill update
![Page 19: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/19.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
Pivot Tables / Chart are dynamic♦Pivot Tables are dynamic connections to data.
Using Excel as a TAILS extension: Pivot Tables/Charts
1919
Your turn
Make aPivot Chart
From yourPivot Table
![Page 20: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/20.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
20
How to use Pivot Tables / Pivot Charts♦Try this one to show the ‘Top 10’ A/W Types
Using Excel as a TAILS extension: Pivot Tables/Charts
Clear all thefilters bysetting
Columnsand Rows to‘Select All’
SelectValue Filters
Select‘Top 10’
SelectRow Labels
picklist
![Page 21: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/21.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
21
How to use Pivot Tables / Pivot Charts♦Try this one to show the ‘Top 10’ A/W Types
Using Excel as a TAILS extension: Pivot Tables/Charts
Accept‘Top 10’
Click‘OK’
![Page 22: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/22.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
22
How to use Pivot Tables / Pivot Charts♦Try this one to show the ‘Top 10’ A/W Types
Using Excel as a TAILS extension: Pivot Tables/Charts
![Page 23: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/23.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
23
How to use Pivot Tables / Pivot Charts♦Try this one to show the ‘Top 10’ A/W Types
Using Excel as a TAILS extension: Pivot Tables/Charts
Here is adonut chart
of theseTop 10Inner ring = 2006
Outer ring = 2009 Pivot Tablesand
Pivot Chartscan be very
powerfuland
somewhattricky.
Use themwith any
Excel data
![Page 24: Using Microsoft Excel as - USFWS · TAILS Phase 5 T A I L S racking ntegrated ogging ystem nd 1 Using Microsoft Excel as a TAILS extension Pivot Tables & Pivot Charts](https://reader031.vdocuments.us/reader031/viewer/2022030213/5b37ade77f8b9aad388e9a46/html5/thumbnails/24.jpg)
TAILSPhase 5
TAILS
racking
ntegrated
ogging
ystem
nd
24
UsingMicrosoft Excel
asa TAILS extension
Pivot Tables & Pivot Charts