Spreadsheet Awesome: Check Mark Totals

Here’s the problem, you want to use LibreOffice to do a simple registration for a small class, so you open it up and write a small table for the dates you want to record:

You use a simple X to record when someone was present and a simple dash – to record an absence. But the mood strikes you and you want to make it look a little more professional. So you load up the Character Map program and grab a tick character from the symbols available and paste it into all the marked in cells:

OK so your spreadsheet looks nice, but your reviewer wants to know how many students were in each class total. this should be easy enough and you make a new row and add in the cell SUM formulas. But woe! it doesn’t work. Using characters in a spreadsheet doesn’t count because they’re not numbers:

So to fix the issue you use the search and replace to replace all your nice tick symbols with the number ‘1’ and to be consistent, all of the dashes to the number ‘0’. And it works, you have your totals; but this doesn’t look nice! So you decide to use the format cell option to figure it out:

This brings up the number formatting window. Here you can decide what the cell should look like given a certain value. Our values are ‘1’ and ‘0’, anything else is a problem, so we use the cell formatting code: [=1]"✔";[=0]-;[RED]"Error" which shows a tick when the cell is ‘1’, a dash when the cell is ‘0’ and a red coloured Error when the cell is anything else:

Now everything is formatted wonderfully and LibreOffice Calc has saved us from having to decide between an ugly or a useless spreadsheet, we can have both beauty and functionality!

As a bit of extra curricular, I also created one for deciding if someone loves you: