Geeking on Spreadsheets – Bag of Tricks

I admit it, I am a spreadsheet junky. It’s true. And the things I like most are the formulas. I like the ability to somehow count, group, and analyze stuff in a spreadsheet. The things I don’t like much are formulas. Yes, both like and dislike. I like the results, but I haven’t spent the time to learn a whole lot about formulas and functions in spreadsheets. I can usually figure out what I want the spreadsheet to do, then I spend time breaking it down into steps. For example, in order to get the percentage of change between two things, I probably have to get the average for each thing first, then compare them, and then figure out what % of change is between the two of them. Once I know what I want to do, I spend time googling how to do it and then figuring out the best way to represent those numbers to other people.

Recently I was playing with some data, trying to figure out the best way to represent what I had… a chart, a graph, a percentage, change over time… was there a way I could compare this data with data from past years? Could I make one spreadsheet out of five different spreadsheets – One Spreadsheet to Rule Them All!!! Yeah, did I mention I kind of geek out on this stuff? Anyway, I found a few tutorials I think are worth sharing in case you or a patron end up playing with a spreadsheet.

The first tutorial is Excel Easy (https://www.excel-easy.com/). The tutorial itself is okay, but the feature I like the most is the 300 Examples (https://www.excel-easy.com/examples.html). As the title suggests, there are 300 examples of formulas and functions in Excel. For me, it is a good resource to see what is possible. The way it is laid out, I can grab the one thing I want to know about without having to click through other tutorial bits. This may not be great for someone wanting to step through the whole spreadsheet process from beginning to end, like in a class, but ideal for someone who has the basics down and needs a bit of specific help.

A good basic step-by-step tutorial is from GCF Learn Free (https://edu.gcfglobal.org/en/excel2016/). The Goodwill Community Foundation Learn Free Tutorials start with the basics and keep going. They also have a tutorial on Google Sheets! (https://edu.gcfglobal.org/en/googlespreadsheets/) To me this is important. While using a spreadsheet in Google Sheets is very similar to Microsoft’s Excel, there are a few differences, especially when it comes to formulas. These GCF tutorials are set up more like a class with quizzes, a YouTube video playlist, step by step instructions, and a whole section of other resources for you to check out.

Another favorite of mine is Chandoo (https://chandoo.org). This is a good source to find intermediate information about conditional formatting and creating reports quickly. The raw basics are outlined here (https://chandoo.org/wp/welcome/), with links to some text, images, and video. However, it also has some additional resources for you to click through and read more. Chandoo even has a section called, “51 Everyday Excel formulas explained in plain English” (http://chandoo.org/excel-formulas/) that is very handy.

There are a lot of good Excel and Sheets tutorials out there, and you probably already have a few of your favorites in your Bag of Tricks. It is a good idea to have a few tutorials and cheatsheets for different levels of learning at your fingertips for patrons. As we talked about in other parts of this Bag of Tricks series, having resources at your fingertips and a basic familiarity with up-and-coming technology can come in very handy for better serving patrons and can also give you a bit more confidence. While I suggest that you create your own Bag of Tricks, I have an example Bag of Tricks to get you started at https://padlet.com/kieran/CSLSHAREANDLEARN.

Kieran Hixon

Technology & Digital Initiatives Consultant at Colorado State Library
Contact Kieran at hixon_k@cde.state.co.us.
Kieran Hixon

Latest posts by Kieran Hixon (see all)