Embedded Files
Check out my TPT Store for tons of resources and templates!
🤔 Want to LEVEL UP your SPREADSHEET SKILLS? 🤔
🤔 Want to LEVEL UP your SPREADSHEET SKILLS? 🤔
NEXT STEPS to MAXIMIZE YOUR SPREADSHEETS
NEXT STEPS to MAXIMIZE YOUR SPREADSHEETS
STEP 1 - CREATE FOUR Spreadsheets:
STEP 1 - CREATE FOUR Spreadsheets:
Students
Students
What to Include?
What to Include?
Priorities:
- ID and Contact Data
- Instrument / Part / Class
- Columns for Financial records / Inventory
How to Use?
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
Financials
What to Include?
What to Include?
Priorities:
- Deposit Slip / Payment Records
- PO / Invoice creator
- Budget Planner / Tabulation Page
How to Use?
How to Use?
Connect to Student Database
- makes deposit entry easier
- allows searching for past payments
Inventory
Inventory
What to Include?
What to Include?
Priorities:
- Category / Description
- Brand/Model/Serial#
- Check-in / Check-out / Repair Info
How to Use?
How to Use?
Keep track of:
- Repair / Usage Stats
- Age of equipment / Future Purchase Planning
Library
Library
What to Include?
What to Include?
Priorities:
- Title, Composer, Arranger
- Past Performance List
- Links to online recordings
How to Use?
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
STEP 2 - Explore the Settings and Menus
(File, Edit, View, Format, Data, etc...)
(File, Edit, View, Format, Data, etc...)
Figure out all the features to see what the database can do!
Figure out all the features to see what the database can do!
STEP 3 - Use FORMATTING
STEP 3 - Use FORMATTING
It makes working with all this data easier!
It makes working with all this data easier!
Conditional Formatting
Conditional Formatting
(automatically highlights certain cells based on whatever parameters you set up)
Freeze Rows and Columns
Freeze Rows and Columns
(makes Header rows / columns so you can scroll through the data and always know where you are)
Alternating Colors
Alternating Colors
(makes each row automatically a different color)
Data Validation
Data Validation
(create drop down lists, calendar date inputs, and other stuff within cells)
Named Ranges
Named Ranges
(save grouped cells for use in later formulas)
Filter Views
Filter Views
(Great for isolating groups of data easily)
STEP 4 - Formulas / functions
STEP 4 - Formulas / functions
NOW THE REAL FUN BEGINS!!! :)
NOW THE REAL FUN BEGINS!!! :)
This is the real power of spreadsheets. Make the computer/robot do all the work for you!
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:
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.
Google Sheets / Excel have lots of built-in help for finding the formulas you need and teaching you how to use them.
FIRST FORMULAS:
FIRST FORMULAS:
Just starting out? Try these formulas to get a taste for the magic.
=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
= Locking Range References with "$" (A1 vs $A1 vs A$1 vs $A$1)
Use the Dollar Sign as part of Cell references in other formulas to ‘FREEZE’ the reference, making copy and paste of the formulas easier across rows and columns.
=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)
Can also be done with a simpler formula using the "&" symbol: =D2&", "&E2 or =E2&" "&D2
=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:
ADVANCED FORMULAS:
Feeling brave? Try these formulas to take your spreadsheets to the next level!
=XLOOKUP() - Similiar to VLOOKUP, but better!
Grab Specific Data from a Specific Place somewhere else in your spreadsheet - good for connecting payment records, health forms, permission slips, t-shirt orders, and Google Form results to all the other data in your spreadsheets!
=FILTER() or =SEARCH()
Get a Report of specific data you need without having to hide columns and rows of your main data.
=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.
=IMPORTRANGE()
Import any range of data from other spreadsheets you have access to.
Will require authentication the first time you use it.
=RAND() or =RANDBETWEEN()
Great for coming up with random numbers for use in a presentation . . .
There are SO MANY formulas out there, all waiting to help you move, twist, filter, sort, cut, copy, or paste any amount of data in any way you need.
There are SO MANY formulas out there, all waiting to help you move, twist, filter, sort, cut, copy, or paste any amount of data in any way you need.
The Power of Spreadsheets is in their ultimate customizability to fit any specific situation or use-case. All it takes is a little curiosity, some dedicated practice, and a healthy dose of creativity to discover the solution you need! And once you find that solution, the spreadsheet can automate the task forever, freeing you to focus on teaching your students!
Page updated
Google Sites
Report abuse