Microsoft Excel is one of my favorite applications. Long ago, it was Quattro Pro that displaced Lotus 1-2-3 in my heart, but Excel long ago rose to the throne.
My wife and have I have maintained Excel workbooks for our daughters’ schoolwork since the pandemic began. We updated the workbooks to full-fledged gradebooks at the start of the 2020-21 academic year. We also use Excel for tracking their reading logs, Girl Scout activities, and more. Sometimes, a spreadsheet is the best tool for a task.
As we end the first half of the 2021-22 academic year, I have been adding yet more features to our gradebooks. Some of what I have done, in order:
- Revised the weighting of grades by assigning type;
- Created a summary sheet at the front of each girls’ workbook;
- Applied color-coded “Conditional Formatting” to grades for visual feedback;
- Added a “Day of the Week” column to study patterns;
- Converted the grade regions to “Tables” within spreadsheets; and
- Added line charts to each academic subject’s sheet, revealing grade trends.
Summary Sheets
The first sheet of each gradebook is a summary of grades by subject, with breakdowns by quarter. There are simple “brute force” ways to accomplish this, but I took a complicated — and more flexible — route.
The summary sheet includes cells to set the start and end of each quarter. Using the dates in those cells, the data in each subject’s sheet is filtered and presented in the summary. If a quarter hasn’t started, the corresponding cells are blank. This is an example of the equation I use to obtain a quarterly grade:
=SUMIFS(INDIRECT($B4&”!$J$5:$J$5000″),INDIRECT($B4&”!$C$5:$C$5000″),”>=”&Summary!J$12,INDIRECT($B4&”!$C$5:$C$5000″),”<=”&Summary!J$14)/SUMIFS(INDIRECT($B4&”!$I$5:$I$5000″),INDIRECT($B4&”!$C$5:$C$5000″),”>=”&Summary!J$12,INDIRECT($B4&”!$C$5:$C$5000″),”<=”&Summary!J$14)
No, I didn’t actually code all the cell references. Instead, I used Excel’s Table feature and named columns. However, Excel converts the easier to read original to what you see above. I have no idea why Excel does this, defeating the purpose of the Table. (Yet, named ranges are preserved in complex equations.)
The equation translates as:
Total the data for the subject named in “B4” of the summary sheet where the date of assignments is between the first (J12) and last (J14) days of the quarter.
The Excel function INDIRECT() converts a calculated text string into a range reference. I use INDIRECT() frequently when LOOKUP() functions don’t quite meet my needs.
I’ve seen many sheets with such summary formulas hard-coded. The resulting formulas are shorter, but then you need to edit the formulas to get specific results. You could also use fancier tables with data grouped by quarters using outlining features. Many ways exist to obtain the same type of data summaries.
Conditional Formatting
When grades are color-coded, it’s easy to spot problem areas. I use Conditional Formatting to color code grades below 75 and 60 in yellow and red. Anything below a 75 suggests I need to review the materials again with one of the girls. Below a 60 means I need to present the information in a new way and practice a different strategy.
Excel offers automatic conditional formatting. For a gradebook, “Color Scales” work well.
Days and Dates
Days and dates mark the school year, the semesters, and the quarters. The primary date column is a standard date, formatted in “YYYY-MM-DD” notation because that’s how I think about dates. It’s sorting thing from programming databases. I use the TEXT() function to return days of the week in another column.
Believe it or not, this approach has revealed that Wednesdays are difficult for our daughters.
Worksheets Aren’t Tables
Workbooks contain worksheets and worksheets may contain Tables and Pivot Tables, though worksheets do not need tables. Many users assume the worksheets (spreadsheets) are “Tables” since they are… well… tables.
An Excel Table is a range of data that behaves a lot like a database. Tables have great functionality and look nice.
The header row acts as names for data, allowing you to create equations that are easier to read and debug. For example, =SUM(ELAGrades[Earned]) returns the total points earned by a student in English Language Arts. Unfortunately, as mentioned earlier, Excel has a nasty habit of sometimes — but not always — converting the readable functions to column notation. This doesn’t always happen, but sometimes. Microsoft should fix this since named regions remain as coded in functions.
Table headers can feature “Filter Buttons” that quickly sort and filter data, hiding rows that don’t meet the set criteria. I use filters often to display assignments on specific topics. For example, I might want set a filter within Social Studies to show only assignments on reading maps. That filter was useful when the girls were learning about longitude and latitude.
The last row of a table offers automatic summary calculations, including lowest value (minimum), highest value (maximum), total, average, and counts. Sure, you could do this manually, or use named ranges, but tables automatically expand and adjust without a lot effort. Adding rows doesn’t require fixing calculations with a table.
People who don’t use the Table feature of Excel often resort to workarounds like putting totals at the top of sheets. That’s not a bad approach, since you can easily locate totals, but it isn’t ideal when you share reports. People expect totals at the bottom of a table.
Another reason to like tables: there are cool formatting templates. I stick to basic shades of blue for tables. I turn on alternating row colors, automatic headers, and other features as I need them.
Charts Are Great (and Painful)
Charts require some practice in Excel (and in every other application I’ve used). The old “Chart Wizards” would create disastrous charts more often than not. It seems Excel makes a lot of assumptions about the order of data columns and rows.
Within each academic subject’s worksheet, I have a Table for the data. Then, using the named columns from the Table, I generate line-chart tables showing daily performance and running averages. The x-axis is [Date], the y-axis is 0-100, and the two lines plotted are [Grade] and [RunningAverage].
Charting in Excel is slightly, and only slightly, less annoying than trying to format a complex document in Word. Ask anyone who has wrapped text around a table or illustration in Word what it is like to update the document later. Charts in Excel can be incredibly frustrating. One wrong click things go sideways.
Once I get a chart working, I hate to make changes for fear of breaking the chart. I place charts alongside data on a worksheet so I can make sure data sources remain correct. Moving a chart to its own tab in the workbook leads to extra frustration when something goes awry.
When you reformat data, sometimes Excel warns that regenerating the table might take some time. You might wonder if the application has locked when updating a chart with lots of data points.
Though I love the power of Excel, I do wonder if Microsoft has tried to include too many charting options.
We Like Data
Most users don’t need to master Excel. However, Excel is designed for complicated financial, statistical, and research work. If you live in Excel for complicated tasks, then having a few charts in a homeschooling gradebook isn’t a stretch.
As you can tell, we like data. For “small” amounts of tabular data, Excel is a great tool. Yes, there are now fancy cloud-based alternatives and “good enough” low-cost or free alternatives, but Microsoft Excel dominates for a reason.
In the not-to-distant past, programming Excel using Visual Basic was necessary for some tasks. Now, Microsoft has integrated 90% or more of what I used to code directly into Excel. Tables and PivotTables are magical. The improved Conditional Formatting replaces lots of VBA code. I cannot think of many times I need to write code anymore. It’s nice to know I could code if I wanted.
At least I’m keeping my Excel skills honed during the pandemic.