Guest speaker - Tiffany Little of PINES to talk about tips and tricks for manipulating report output in Excel!
Formulas at the top of the spreadsheet for ease
To freeze header row - View tab - Freeze pane (select row underneath header row)
Filters and subtotal function
=subtotal(9,B2, B10000000)
If filter applied, it will only show that sum
=sum()is everything, doesn't care about filter
9 is sum in the subtotal function, 3 is count
subtotal by itself does nothing
Color coding - apply filter, apply color, filter by colored or not colored
Add comma to value
Absolute references
Combine values from two cells into one (with space in between)
Transit report example
IF(B2="Branch Name","From","To")
If sending library is this branch it's "From," if it's not then it's "To"
From system
=LEFT(B2,FIND("-",B$2)-1)
Absolute reference allows you to copy over to To System
In system transit
(B2=C2) will show true of false
Pivot table to show authors that patrons are requesting that you may not own
Rename column for reference in formulas
To refer back to formulas later, keep formulas in a separate sheet to work in. Then paste report data into another sheet.