Microsoft Excel is one of my favorite applications.\u00a0Long ago, it was Quattro Pro that displaced Lotus 1-2-3 in my heart, but Excel long ago rose to the throne.<\/p>\n
My wife and have I have maintained Excel workbooks for our daughters\u2019 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.<\/p>\n
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:<\/p>\n
The first sheet of each gradebook is a summary of grades by subject, with breakdowns by quarter. There are simple \u201cbrute force\u201d ways to accomplish this, but I took a complicated \u2014 and more flexible \u2014 route.\u00a0<\/span><\/p>\n 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\u2019s sheet is filtered and presented in the summary. If a quarter hasn\u2019t started, the corresponding cells are blank. This is an example of the equation I use to obtain a quarterly grade:<\/span><\/p>\n =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)<\/span><\/p><\/blockquote>\n No, I didn\u2019t actually code all the cell references. Instead, I used Excel\u2019s 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.)<\/span><\/p>\n The equation translates as:<\/span><\/p>\n Total\u00a0the data for the subject named in\u00a0\u201cB4\u201d of\u00a0the summary sheet where the date of assignments is between the first (J12) and last (J14) days of the quarter.\u00a0<\/span><\/p><\/blockquote>\n The Excel function INDIRECT() converts a calculated text string into a range reference. I use INDIRECT() frequently when LOOKUP() functions don\u2019t quite meet my needs. <\/span><\/p>\n I\u2019ve 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.\u00a0<\/span><\/p>\n When grades are color-coded, it\u2019s 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.<\/p>\n Excel offers automatic conditional formatting. For a gradebook, \u201cColor Scales\u201d work well.<\/p>\n Days and dates mark the school year, the semesters, and the quarters. The primary date column is a standard date, formatted in \u201cYYYY-MM-DD\u201d notation because that\u2019s how I think about dates. It\u2019s sorting thing from programming databases.\u00a0 I use the TEXT() function to return days of the week in another column.<\/p>\n Believe it or not, this approach has revealed that Wednesdays are difficult for our daughters.<\/p>\n Workbooks contain worksheets and worksheets may contain Tables and Pivot Tables, though worksheets do not need tables. Many users assume the worksheets (spreadsheets) are \u201cTables\u201d since they are\u2026 well\u2026 tables.<\/p>\n An Excel Table is a range of data that behaves a lot like a database.\u00a0Tables have great functionality and look nice.<\/p>\n 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 \u2014 but not always \u2014 converting the readable functions to column notation. This doesn\u2019t always happen, but sometimes. Microsoft should fix this since named regions remain as coded in functions.<\/p>\n Table headers can feature \u201cFilter Buttons\u201d that quickly sort and filter data, hiding rows that don\u2019t 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.<\/p>\n 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\u2019t require fixing calculations with a table.<\/p>\n People who don\u2019t use the Table feature of Excel often resort to workarounds like putting totals at the top of sheets. That\u2019s not a bad approach, since you can easily locate totals, but it isn\u2019t ideal when you share reports. People expect totals at the bottom of a table.<\/p>\n 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.<\/p>\n Charts require some practice in Excel (and in every other application I\u2019ve used). The old \u201cChart Wizards\u201d would create disastrous charts more often than not. It seems Excel makes a lot of assumptions about the order of data columns and rows.<\/p>\n Within each academic subject\u2019s 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].<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n Though I love the power of Excel, I do wonder if Microsoft has tried to include too many charting options.<\/p>\n Most users don\u2019t 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\u2019t a stretch.<\/p>\n As you can tell, we like data. For \u201csmall\u201d amounts of tabular data, Excel is a great tool. Yes, there are now fancy cloud-based alternatives and \u201cgood enough\u201d low-cost or free alternatives, but Microsoft Excel dominates for a reason.<\/p>\n 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\u2019s nice to know I could code if I wanted.<\/p>\n At least I\u2019m keeping my Excel skills honed during the pandemic.<\/p>\n","protected":false},"excerpt":{"rendered":" Microsoft Excel is one of my favorite applications.\u00a0Long ago, it was Quattro Pro that displaced Lotus 1-2-3 in my heart, but Excel long ago rose…<\/p>\nConditional Formatting<\/h3>\n
Days and Dates<\/h3>\n
Worksheets Aren\u2019t Tables<\/h3>\n
Charts Are Great (and Painful)<\/h3>\n
We Like Data<\/h3>\n