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:

Spreadsheet showing simple registration form with several names and some checked off boxes

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:

Character Map showing a pannel of symbols

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:

Spreadsheet showing the new tick symbol and a new totals column incorrectly showing 0 in each cell.

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:

Spreadsheet showing all tick symbols replaced with 1

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:

LibreOffice Format Cell Window showing new custom format typed in.

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!

Spreadsheet showing correct registration totals and nice tick symbols.

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