For this post, I’m using sample the file Excel4EngMajors_HarryPotter.xlsx on “The House Cup” tab. If you want to follow along, you can download the file here or on the Resources page.
Okay, so we’ve got some of the basics – we’ve learned the basic components of Excel and how formulas work – and now it’s time to dive into a real tutorial.
There are many formulas that are incredibly useful, and I’d like to explore some of my favorite in the next series of posts. For this first post, I’d like to return to the magical realm of Harry Potter to demonstrate the power of SUM.
What is the SUM formula?
Although Excel can be used for many different things, frequently Excel spreadsheets are used to store and manipulate numbers. One of the simplest ways Excel can do this is by adding up, or summing, a group of cells together using the SUM formula.
| Description | Formula |
| To add up a group of cells together | =SUM( ) |
SUM can add together any group of cells, whether the cells are together vertically in the same column or horizontally in the same row. SUM also works both for cells that contain numbers, and cells that contain formulas. The ability for Excel to use formulas on cells that themselves contain formulas makes Excel incredibly power, turning what could be a complex process into a simple one.
But as always, I think the best way to see this is in action – let’s dive into an example, shall we?
SUMming Up The House Cup
In Harry Potter and the Sorcerer’s Stone, one of the major plot points has to do with house points that are awarded over the course of the year. Now I’m sure Albus Dumbledore had a magical way to track how many points each house had at a given time, but it could have been even easier if he had used the magic of Excel.
Picture this – you have a simple spreadsheet breaking down the amount of points each house has won in a month.
Now it might be helpful to know how many points were awarded over the course of the month. We can use the sum formula to figure that out.
To start, select the cell at the end of the row (in the “HOGWARTS” column). Type =SUM( to start off the formula. Then click and drag your cursor across the row of cells that you want to add up (be sure not to include the cell with the date in it, or this will mess up the formula!). Once you have selected all the cells, release your cursor, then close the formula with ). And like magic, you have a completed formula!
Now let’s say you want to have that same formula work for every month in the school year. You could go through and type out the formula in each individual cell. Or, there’s a faster way, using one of my favorite keyboard shortcuts from my last post.
For the next month, let’s start by typing in the formula as we did before. Then, once you’re done, highlight from that cell all the way down to the last row. Then use the keyboard shortcut CTRL + D, and all of the formulas have been duplicated down the entire spreadsheet!
Now that is all very helpful, but it still doesn’t tell you which house won the House Cup overall, because it hasn’t added up all the points for each house for the year. We can also do that using the SUM formula! For this one, select the last cell in the column (in the “TOTAL” row). Then, just as before, type =SUM( to start off the formula. Then click and drag your cursor across the column of cells that you want to add up. Once you have selected all the cells, release your cursor, then close the formula with ).
And now we know how many points Gryffindor won!
You’ll notice that in the column, there is a number with parentheses around it. That is to represent a negative number (pesky events getting caught in the corridors at night…). When a negative number is included in a sum formula, sum treats it as a negative number and subtracts it from the total of the positive numbers, just as a calculator would. (If you wanted to get the absolute value of a column, regardless of if the numbers were positive or negative, you could use the =ABS( ) formula instead of SUM.)
And now, we want to sum up all the houses that way we can definitively know who won the House Cup. Just like we can duplicate a formula, we can also copy and paste a formula. When you copy a formula and paste it in the same location, it adjusts the cells it’s summing up relative to where the formula is. In other words, when you paste it at the bottom of the Slytherin column, the formula knows to sum up the Slytherin cells. You can copy and paste the formula multiple times to quickly add up a range of numbers, including the total column, which is itself a column of formulas!
And there we have it – definitive proof that Gryffindor won the House Cup, all done with the magic word SUM.
Did you learn something cool? Is there a question you have that you think Excel could solve? Leave it in the comments below!
Excel for English Majors 