You already geek out over Excel’s amazing functionality and its features in Windows. Maybe you’ve already used it to create a calendar template. Maybe you color code your personal budget targets. Maybe you already use it to track student progress, grades, test scores, attendance, and days until your next school holiday. We’re not here to judge. We’re here to help you make the most of what this powerful spreadsheet application has to offer.
Basic Conditional Formatting
When you’re dealing with a mountain of data, conditional formatting can help you find specific information quickly and visibly by color-coding cells that match specific criteria. For instance, you can highlight any student who has missed more than three days of class or any test scores that fall below passing. Learn more here.
Formula-based Conditional Formatting
And sometimes, just a basic highlight isn’t enough. Sometimes you want your spreadsheet to help you identify the next step in your process, like setting up a conference with parents or an email to the student. Formula-based conditional formatting can help with that. Here’s how.
=SUM(): The first function you learn really adds up.
You already know how to add up cells in Excel, but did you know you can hit CTRL + [Cells you want to add] to instantly add them to an equation? This is a fast way to add across random columns or rows.
=SUMIF(): For more selective sums
Sometimes you want to add and gather data in a different way. The =SUMIF equation will look for certain data in a table and add similar items together. This could be helpful if you track attendance by day of the week and want to learn which days most kids come to class. Learn more here.
=COUNTIF(): Add up occurrences of a value.
=COUNTIFS(): Add up occurrences of multiple values.
The COUNTIF() function allows you to count how many times a certain criterion occurs in your specified range. For example, if you add a letter value grade to each assignment, you can use the COUNTIF() equation to count how many As, Bs, Cs, Ds, and Fs a particular class or a particular student has. Use =COUNTIF(Where do you want to look?, What do you want to look for?).
More information on COUNTIFs here.
=AVG(): The average formula for above-average teachers
The average function works similarly to =SUM in that it allows you to grab the data within a column or throughout a sheet. If you use average test or homework scores to finalize grades, the =AVG function adds up (and divides evenly).
=MIN(): Find the lowest number in a range
=MAX(): Find the highest number in a range
This is a great way to find the range of cells quickly and accurately, especially if you are grading on a curve, or want to quickly obtain the range of test scores for a given exam.
=LEN(): Count characters in a single cell.
Never misjudge your Twitter character count again. This function will count the number of characters in a specific cell. Great for writing assignments with text limits.
=RANDBETWEEN: Generate a Random Number
Need help picking which student goes first with a presentation? Enter the RANDBETWEEN equation. Simply type =RANDBETWEEN(1,100) and it will return a completely unbiased whole number between 1 and 100 just like that.
=ROMAN: Convert to Roman Numerals
Perhaps not the most useful of formulas, but when C, V, L, and I are Greek to you, it can be a useful tool. Use =ROMAN(Destination cell with number you want to convert).
=ROUND(): Do a one-handed round-off
The =ROUND function allows you to round off numbers to a specific digit. Use =ROUND(number, digit) as the equation, keeping in mind that “0” digit will return a whole number, “1” returns to the tenths, and so on. =ROUNDUP() and =ROUNDDOWN() are equally fun, and further explained here.
=YEARFRAC(): Compare a date range to the entire year.
=DAYS(): Count the number of days in between two dates.
=NETWORKDAYS()
Still counting down to the end of the year? Excel can take the work out of that, too. Use =YEARFRAC(start_date, end_date, [basis]). For example, =YEARFRAC(1/1/2018,5/31/2018, 1). Learn more about the calendar basis here. For the number of days or actual work days, use =DAYS(start date,end date) or =NETWORKDAYS(start,end) respectively. Place your date values in cells for increased flexibility and celebrate further with conditional formatting.
These are our favorite Excel equations so far in 2018. Which ones did we miss of yours? We'd love to know and talk more about education tech, especially when it comes to Microsoft.