Transcript FinancialLink & Excel/Pivot Tables Adam DiProfio Director of Budget and Planning Jacobs School of Engineering x44950, [email protected] Staff Education and Development FinLink & Excel Tools Data Management • FinancialLink.

FinancialLink &
Excel/Pivot Tables
Adam DiProfio
Director of Budget and Planning
Jacobs School of Engineering
x44950, [email protected]
Staff Education and Development
FinLink & Excel Tools
Data Management
• FinancialLink is great for getting report
summaries
• Sometimes we need something more
customized
• Use Operating Ledger Detail
Transaction or QueryLink to get more
data
Page 2
FinLink & Excel Tools
Operating Ledger Detail
• Great for getting data without using
QueryLink
• Can run by account code or rule class
– 638070 = office supplies
– FE54 = High risk ENPETS
– X = All Express Card Transactions
• Good for downloading and using Excel
AutoFilter or Pivot Tables
Page 3
FinLink & Excel Tools
Example: Look at all High Risk ENPETs
Page 4
FinLink & Excel Tools
If Data sets are larger, you can manipulate data in Excel
Page 5
FinLink & Excel Tools
Example: Download a large amount of data to Excel
Page 6
FinLink & Excel Tools
Download to Excel
Page 7
FinLink & Excel Tools
Transfer file to Excel
Page 8
FinLink & Excel Tools
Use Auto Filter to quickly sort data
Page 9
FinLink & Excel Tools
Example: Look at Rule Class XCRD & XXCD for express card
Page 10
FinLink & Excel Tools
Works for flat data. But what if we need something more?
Page 11
FinLink & Excel Tools
What is a pivot table?
• A tool to quickly summarize data
• A way to graphically change a
table’s structure
• Can allow cross tabs, summaries,
totals, etc.
Page 12
FinLink & Excel Tools
Use Pivot Tables to get more advanced reports
Page 13
FinLink & Excel Tools
Drag and drop data into PivotTable fields
Page 14
FinLink & Excel Tools
Example: Look at Travel Account Codes by Index
Page 15
FinLink & Excel Tools
Double click on a cell to bring up detail
Page 16
FinLink & Excel Tools
Page 17
FinLink & Excel Tools
Other Examples of Pivot Tables
• Group expenses by Fund, then index
• Look at rule class XCRD & XXCD
(express card transactions)
Page 18
FinLink & Excel Tools
Drag and drop data into PivotTable fields
Page 19
FinLink & Excel Tools
Page 20
FinLink & Excel Tools
Page 21
FinLink & Excel Tools
Other Examples of Pivot Tables
• Check how many express cards
transaction are being used on each
index
• Right click on data, change “Field
Settings” to “Count”
Page 22
FinLink & Excel Tools
Right click to change
Field Settings
Page 23
FinLink & Excel Tools
Page 24
FinLink & Excel Tools
Pivot Tables in Excel 2007
• Excel 2007 has a different interface
for Pivot Tables
• Easy to implement styles and themes
Page 25
FinLink & Excel Tools
Page 26
FinLink & Excel Tools
Page 27
FinLink & Excel Tools
Page 28
FinLink & Excel Tools
Conclusion
• Questions?
• Come practice at the Fiscal Café.
• Thank you!
Page 29