When I saw the spreadsheet for the editors of Expressions, I knew what I had to do. I saw the potential and I took over. For some reason, I really love spreadsheets. The frustration of searching forums just to be hit with another ERROR in your cell only makes the triumph of finally getting it right that much sweeter.
So pay attention, future Expressions editors and anyone who has ever come across numbers before. Here is how I made our jobs a lot easier over the past semester.
Borders. They make the sheet easier to read, and there are multiple widths and styles. But don’t overdo it. Borders surrounding each row is too much of a good thing.
Colors. The defaults are an insult to colors. They are either way too bright or muddy. Make your own. Try finding a nice hue on the square color canvas and then move the linear slider below to find more colors. This will make the colors similar, giving you a nice palette overall. Use different colors for better visibility of your more important data, however, many cells can still be left with a blank background color.
Conditional Formatting. The format menu has this and it’s a great tool. It will change the highlighted color of a cell automatically based on criteria you choose. Text, numbers, anything. This helps cells stick out for better visibility of more important data.
Gray lines. I don’t know the technical term, but there are these grey lines that border the left of the A column and the top of the 1st row. If you hover over them, a hand cursor will pop up and you can drag them over to a different column or row. This will keep them on the screen no matter how far you scroll in either direction. This is convenient for submission titles and editor names, any data that you want to keep an eye on.
Easy data entry. The small blue square on the bottom right corner of the selected cell can be dragged to duplicate the content to other cells. It is intuitive, so if your formula uses a range from the same row, dragging the square down one cell will change the range by one cell as well. This works with any pattern including dates and numbers.
Formulas. First of all, I can’t guarantee that these are the most efficient formulas, but they work, and that’s the important part.
I’d have to say the most useful automation was counting editor votes. That way, we could easily see the total number of yes, no, and maybes. I made the text of the “yes” and “no” columns colored green and red so they’d stand out, while leaving “maybe” black as it was less important.
If you’ve never used a formula, that’s okay. GoogleSheets typically gives you quite a bit of help. Well, it’ll help you until you have no idea what you’re doing, you have eight parentheses and three functions and it will just tell you the dreaded “Formula Parse Error.” Yeah, get used to seeing errors; seemingly impossible errors until you finally get it and you can move on to the next puzzle.
For formula beginners, you always start with an equal sign [=]. Then you choose your function. To count the yes, no, and maybes, I used =COUNTIF(C2:M2, “yes”). The =countif function counts things as long as they fit the criteria. C2:M2 is the range and that will depend on your spreadsheet. Column C in my sheet is the voting spot for the first editor in the list and M is the spot for the last editor. Another way of saying column C is C:C which simply means it’s the entire C column, all the way to the bottommost cell. The comma gets you to the next part of the formula and quotation marks are used if you want to count text rather than a number. This formula will count each occurrence of exactly “yes” in the range, C2:M2 (It is not case-sensitive). Adding asterisks (“*yes*”) will count the yes if it is included in the cell, so it doesn’t have to be exact.
We needed to figure out which submissions qualified for the Editor’s Choice awards. Assuming those without votes of Maybe or No would qualify for editor’s choice, I used the formula =ARRAYFORMULA( IF( AND( (P2:Q2=0) ), "✅", "--")). Yes, you can use emojis in your formulas, just put them in quotes. So that seems pretty complicated. I’ll break it down. =ARRAYFORMULA contributes to using 2 criteria, as does AND. IF designates the criteria. (P2:Q2=0) is the range and I wanted to make sure the votes No and Maybe would both equal 0. If yes, then I want a checkmark emoji placed in the cell. If no, I want two dashes. Then you just make sure the parentheses are closed.
I ended up moving the contenders for Editor’s Choice to their own sheet. We had a column that specified the type of submission (poetry, prose, photography, etc.). Using the conditional formatting, I highlighted a single type so that it was easy to see and copy all of those types of submissions that had a checkmark next to them. I held control and selected each one before copying and pasting them into the new sheet
Conclusion. We often don’t think about what a great tool spreadsheets are. There are so many things you can do, including statistics with colorful charts, but this is just an introduction. If you find yourself calculating the same things over again, just pop it all into a spreadsheet. With automations, it’ll make your work much easier and faster. Plus, if you have to change something, you won’t have to redo everything. When set up correctly, everything will update and you’ll be all set. With a grasp on the basics, you can ask the internet for help and soon you’ll find the answer you’re looking for. So try it out and you’ll realize what you’ve been missing out on for way too long.