Caseload Data Tools an Excel file with Macros

Ok, so I have decided I will probably never be done, tinkering with my Excel file named Caseload Data Tools, but I have decided to release what I have so far to the public.  I first started using Excel to record the data of my students’ sessions over a year ago and this file has seen a lot of improvements since. This file includes macros (programs written inside Excel to automate tasks). With macros, I have hopefully simplified charting of student data and making individual worksheets for each student.  For more information about macros with Excel, check out my post here. There are lots of cells with red spots in the upper right corners. When you scroll over these, they display comments to help explain what to do and where to do it. Here is some (ok a lot) of the information. All the names and information in the pictures are fictitious, and were fabricated to illustrate how the file functions.  Any resemblance to real persons, living or dead, is purely coincidental. You will need to enable macros in Excel to use most the the file’s functions. 


The file starts with four sheets. The most important sheet in the file is ‘Caseload’.  Data entered into this sheet is copied into other worksheets. Goals, minutes etc. can be changed at any time here, and the information will automatically be changed in the other worksheets.  At the bare minimum student first names need to be entered. After students’ names are entered click “Click to here to make new client sheets”.


 Data can be entered into the client sheets (in Date, Target Area, and Percent and charted by clicking “Click here to make a Chart”.

A macro creates a pivot chart. Data can be filtered by selected by selecting “Target Area” or “Date”.

If one “Target Area” is selected, the chart can be changed to line graph by right clicking on the chart, selecting “Change Chart Type”, then selecting “Line with Markers”.
Line charts usually do not work, when there is more than one target area, because days when only one target area is tracked the other area is tracked as zero. “Cluster Column” works best with multiple target areas.
After you enter dates into the ‘Attendance’ sheet, minutes from the student sheets are automatically transferred into this sheet.
Goals from the ‘Caseload’ sheet are also in the ‘Goals’ sheet for easy printing.
The workbook also has a ‘Schedule’ sheet.  Without using macros only the ‘Caseload’, ‘Schedule’, ‘Goals’, and ‘Attendance’ will work. I should probably include a disclaimer, so I don’t get sued or anything.  Macros contained in this Excel file are not intended to cause harm, but use at your own risk.  Hopefully you will find this file as useful as I do! 

Using Macros in Excel 101

If you have downloaded my Evaluation Performance Graph, you have seen what I can do without macros (little programs within Excel that automate tasks).  I am almost done with my Student Data file, so soon you can see what I can do with macros.  I have used macros to simplify complex and tedious tasks in my personal files for years.  Macros can contain virus, so you need to be careful and make sure you trust your sources of .xlsm files. I promise my files do not have viruses. I would rather create useful programs instead of destructive ones.  I have password protected the programming to prevent others from adding a virus.

With that said, if you are still interested in using my .xlsm files, you will need to enable the macros.  In Excel 2010 this is usually easy; click on ‘Enable Content’ the first time you open the file and you are good to go. If this doesn’t work your Trust Center Settings need to be changed. Comment below this post if you run into this problem and need help. I will explain further. 

In Excel 2007 things are more complicated.

You will need to click on this ‘Options’ button, select ‘Enable this Content’ and then click ‘ok’ every time you open the file or . . .

you can select ‘Open the Trust Center’, find  and select the ‘Add new location button’ 
then add the location of your file.  Note:  It is best not to have my file in main folder such as “My Documents” for this option. It is better to have it in another folder within “My Documents” like “rwslp”. Hopefully at least some of this makes sense. If anyone needs help with an older version of Excel let me know.  If your company/school is like where I work you can’t access blogs while at work so you may need to print this post off at home. I should probably put a disclaimer so I don’t get sued or anything.  These Excel files are not intended to cause harm, but use at your own risk.