NEXT STEPS and FURTHER DETAILS on MAXIMIZING YOUR USE of SPREADSHEETS
STEP 1 - CREATE FOUR Spreadsheets:
Students
What to Include?
Priorities:
- ID and Contact Data
- Instrument / Part / Class
- Columns for Financial records / Inventory
How to Use?
Sort & Filter / Create Queries
- for Field Trips / Attendance
- for Payments & T-shirts
- for the amount of copies needed for each part . . .
Financials
What to Include?
Priorities:
- Deposit Slip / Payment Records
- PO / Invoice creator
- Budget Planner / Tabulation Page
How to Use?
Connect to Student Database
- makes deposit entry easier
- allows searching for past payments
Inventory
What to Include?
Priorities:
- Category / Description
- Brand/Model/Serial#
- Check-in / Check-out / Repair Info
How to Use?
Keep track of:
- Repair / Usage Stats
- Age of equipment / Future Purchase Planning
Library
What to Include?
Priorities:
- Title, Composer, Arranger
- Past Performance List
- Links to online recordings
How to Use?
Database Magic:
- Keep track of past song choices / patterns
- Know whether you already own something before you buy another copy...
STEP 2 - Explore the Settings and Menus
(File, Edit, View, Format, Data, etc...)
Figure out all the features to see what the database can do!
STEP 3 - Use FORMATTING
It makes working with all this data easier!
Conditional Formatting
(automatically highlights certain cells based on whatever parameters you set up)
Freeze Rows and Columns
(makes Header rows / columns so you can scroll through the data and always know where you are)
Alternating Colors
(makes each row automatically a different color)
Data Validation
(create drop down lists, calendar date inputs, and other stuff within cells)
Named Ranges
(save grouped cells for use in later formulas)
Filter Views
(Great for isolating groups of data easily)
STEP 4 - Formulas / functions
NOW THE REAL FUN BEGINS!!! :)
This is the real power of spreadsheets. Make the computer/robot do all the work for you!
ALL FORMULAS ARE BUILT IN THE SAME WAY:
[ start with {= sign} ];
[ then {Formula Name} ];
[ then Specific Parameters inside the (), following the guidelines for that specific formula using references to other cells in the spreadsheet. ]
Google Sheets / Excel have lots of built-in help for finding the formulas you need and teaching you how to use them.
FIRST FORMULAS/FUNCTIONS:
=SUM(), SUMIF(), SUMIFS()
Add numbers together. SUMIF lets you give a parameter for adding, SUMIFS lets you give multiple parameters for adding.
=COUNTIF(), COUNTIFS()
Count how many of something there is, based on one or more parameters
= (A1) vs ($A1) vs (A$1) vs ($A$1)
Use the Dollar Sign in Cell references to ‘FREEZE’ cell references.
=CONCATENATE(D2,", ",E2) or =CONCATENATE(E2, " ", D2)
Combine First & Last Names from separate cells into one cell with a comma in between (Last, First) or a Space (First Last)
=DATEDIF(J2,TODAY(),"Y")
Subtract birth date from Today’s date to get current age in years. (Automatically stays updated)
=REPLACE(N2,1,3,"")
Automatically Turn the Instrument Code (01 Flute, 09 Trumpet, etc.) INTO the Instrument Name (Flute, Trumpet, etc.)
ADVANCED FORMULAS/FUNCTIONS:
=IMPORTRANGE()
Import any range of data from other spreadsheets you have access to.
Will require authentication the first time you use it.
=INDEX() & =MATCH(), OR =VLOOKUP()
Grab Specific Data from a Specific Place somewhere else in your spreadsheet - good for seeing payment records, health forms, permission slips, t-shirt orders, etc...
=FILTER() or =SEARCH()
Get a Report of specific data you need without having to hide columns and rows of your main data.
=RAND() or =RANDBETWEEN()
Great for coming up with random numbers for use in a presentation . . .
=HYPERLINK()
Turn information into a dynamic hyperlink (to a website) - good for creating automatic links to search the internet for audio files of songs that are in your music library.