Have you ever set up a Google Sheet with formulas that you drag all the way down to the bottom? See all those messy error values littering your otherwise immaculate spreadsheet when those formulas don’t reference a value? It sucks, right?
What if I told you that you could hide all those messy errors? What if I told you that you could truly have that pristine nirvana that you have been imagining that your Google Sheet project would truly be?
Drink the Kool-Aid and strap in, my friends, as your very own guru in a goat takes you on a journey of enlightenment into hiding formula error when:
It’s time to get evangelical, peeps! Release yourself from the yolk of the menu bar and its insidious demands on your coordination and time!
Break free from the right-click menu! For is it not anything more than a proxy menu bar?! A veritable wolf in sheep’s clothing with no other design, but to bring you back to the fold of the menu-using, mouth-drooling reprobates who stand in the way of efficient progress!
Can I get an Amen!
I said, can I get an Amen!
No? Fair enough…
Let’s just get on with it then, shall we? After all, this is about efficiency, right?
Here are the 5 main shortcuts that I use in Google Sheets each and every day to save me a tonne of time. I’m going to give you a bit more than just the Keyboard Shortcut, I’m going to demonstrate how I use it with some clear examples.
Every academic quarter I receive a list of students by their full name in one cell that I need to split into a cell for the first name and then a cell for the middle and last names combined.
This fairly simple process can be achieved with the Google Sheets formulas left, right and find.
If you are in a hurry, here are the formulas below:
To get the first name we do the following:
Where ‘A2’ is the cell that we have our full name in – in our case Vasco Nunez de Balboa.
When you have multiple formulas in a cell it’s often best to go from the inside out. So let’s first look at what FIND does.
FIND looks inside the cell for the first value that we want to search for. For us, it’s an empty space, ” “. Find then returns the numerical position of that found item. To do this FIND takes two arguments:
=FIND(the item we are searching for, the cell or string the item is in)
For example, if we are searching for the location of the space in Vasco Nunez de Balboa which is in cell A2, we would do the following:
Which would give the result: 6
There are five letters in the first name Vasco. The space would be in position 6.
Now that we have the position of the space, lest just grab everything in the cell to the left of that space. We do this with LEFT.
LEFT also take two values. The first is cell location and the second is the number of characters we want to take from the left-hand side.
LEFT(cell location, number of characters from the left)
Now that we know the first space is character 6, the formula would look like this:
We then replace the 6 with our FIND formula and we are good to go.
Google Sheets – MIN, FILTER, INDEX, MATCH, SUM, COUNTIF
In the region of the world that I work in, it is a pretty common occurrence for university courses to run weekly assessment. At the end of the course, all the weekly assessment is then added together minus the lowest piece of assessment.
For lecturers with small course sizes, this is a pretty simple task that you could simply eyeball if you have a small enough group, but what if your course runs into the thousands with half a dozen tests to choose from. Eyeballing is just not going to do it.
Recently I was asked to do the same thing for the program that I manage. Over an 8 week term, we run 7 assessment at the end of each week for our students. My job was to find the lowest grade out of the 7 assessment and drop it, taking note of the assessment unit that I dropped for each student.
I use Google Sheets for this purpose for its ease of use and sharability.
This is an example dataset of the 7 assessment (in this case, weekly tests) in Google Sheets. We need to remove the lowest grade from each student. As you can see not all students have their lowest grade in the same Unit test.