MAKING EXCEL WORK FOR
YOUR TRIBAL COMMUNITY
Building Your Intake Spreadsheet
Your intake spreadsheet can hold as little or as much data as you are
interested in collecting. When thinking about what your agency
does, you can start to build columns in the spreadsheet to collect that
data, like child information, important dates, and investigation
results. Data collection should always reflect your practice, never the
other way around!
Developing Columns
Let’s start with child abuse/neglect reports. Typically, a child
welfare case begins when a report comes in on a family. So a
column on the spreadsheet could be “Report Received Date.” If you
are interested in collecting information on how the report came in,
you could create a column for “Source.” The alleged victim’s first
and last name should be two columns, and you should also have a
column for the child’s gender and the type(s) of alleged
abuse/neglect reported (e.g., Physical Abuse).
Notice that we have a column for both “Date of Birth” and “Age” so
the worker can see at a glance how old the child is. Other columns
you may want to include are “Report Decision” (was the report
accepted or screened out?) and for reports that are screened out, a
column for “Screened Out Reasons.” Another column such as
assigned caseworker and date assigned could be helpful for tracking
staffing issues. Often cases aren’t followed up on as quickly as they
should be and if you track the date assigned, it’s a helpful piece of
info.
Video & Guide: Building Your
Intake Spreadsheet
Using Your Data
Videos:
Displaying Data with Excel
Charts
Next Steps for Using Your Data
Next Steps for Building Data
Capacity
Guide: Displaying Data with
Excel Charts
Building Your Intake
Spreadsheet
Video: Basic Excel Skills: Beginner
and Intermediate Refresher
Guides:
Navigating Excel
Excel Basic Skills
Excel Intermediate Skills
Basic Excel Skills
Drop-down Lists
Video: Using Drop-down Lists for
Easy and Consistent Data Entry
Guide: Drop-down List
Reference Guide
Excel Formulas
Videos:
Intro to Excel Formulas
Basic Excel Formulas for
Reporting
Using Formulas: Beyond
Reporting
Guides:
Basic Guide to Excel Formulas
Formulas for Counting Kids
MORE IN THIS SERIES
Once the report is accepted, it is assigned to a worker to investigate. The worker will need to know what
“Priority” the report is given to direct what the response time should be to visit the alleged victim. The
result of the investigation should be documented in a column. The terms “Unsubstantiated” and
“Substantiated” are often used to describe the result of an investigation – this is a great opportunity to
make a drop-down list.
What happens after the investigation is found substantiated or confirmed? Was the child removed from
the home and placed with a foster parent or relative? Or was a safety plan put in place and the child is
able to remain in the home? The answers to these questions should be documented in your spreadsheet.
To continue your spreadsheet, keep thinking about the sequence of activities and processes that you do in
your job to identify what data you need to collect. For example, when a child is removed from the home,
you most likely would want to know what date the child is removed, where the child is placed, and what
kind of placement it is, like foster home (relative or non-relative), shelter or group home.
When setting up your spreadsheet, include data that you want to collect for:
Federal or State Reporting
Grant Reporting and Evaluation
Future Funding Opportunities
Program Management
Additional information you may want to collect:
Family’s home address (for GIS mapping analysis, if your Tribe has this capability)
Worker assigned to case
Date and time of initial contact with child
Family names of father and mother (maiden name)
Additional substance abuse or mental health information
Cultural/Spiritual information specific to your community
Organizing by Month or Quarter
Depending on how many families and children your agency works with in any given month, you will want
to organize your data into months or quarters, while still keeping at least a full year of data in the same
file. This will make your routine reporting easier to manage.
Add and name a new tab for each month of the year. You may want to start with January or October,
depending on whether you are organizing by the calendar year or the federal fiscal year. Once you have all
your columns in the spreadsheet labeled, you can copy the column headers to the other worksheets and
start collecting data!
For help with adding and renaming worksheets, you can visit the Basic and Intermediate Excel Skills
Refreshers in this learning series. You can also find helpful design tips like freezing header rows,
widening columns, wrapping text, and changing the font to make your spreadsheet more attractive and
easy to read.
Reporting Sheet
One of the benefits of having your data in a spreadsheet is that you can have all the counts for reporting
and other uses readily available in the same file. You can update the numbers by hand – or better, you can
add formulas to automatically update your counts as data is entered into another tab. However you
compile these numbers, you’ll want to set up a separate worksheet for them.
In this learning series, we’ll be using the BIA Quarterly and Annual Report Part B as our example for
reporting counts. To set up these reporting numbers, you’ll add several columns and rows to the blank
worksheet:
The time periods (months, quarters, and year-to-date) will go in the column headings
The BIA reporting categories will go in the rows of the left-hand column.
You can format the font and use color to make the spreadsheet easier to read. You can also add sections
for other reporting you do or other kinds of information you’re interested in looking at.
Next Steps
Now that you have your spreadsheet set up, you can enhance it with drop-down lists and formulas to make
it more efficient and useful. Check out the rest of the Making Excel Work for Your Tribal Community
learning series to keep learning about enhancing your data collection and what you can do with your data
beyond reporting.
VIEW THE COMPANION VIDEO
