Elegant Formulae: Taking Spreadsheets to the next level

 

Elegant Formulae: Taking Spreadsheets to the next level

 Slide doc - Toolkit by David Clarke

Spreadsheets to valuable tool for Data! 

If data exceeds 10,000 records (probably best to use database software). Hard to see all data and software becomes a slower. Database structure becomes more suitable. Freeze tool is always worthwhile. Always makes it easier for others to view your sheet. At least freeze the heading rows/columns. First thing you do when open spreadsheets. Merge cell button is cursed. Think about why you are using this tool. It can break things. Use wrap text. 

Magic Functions: 

1- Importrange 

Pull data from multiple sheets into one sheet page. 

I felt stressed watching this section of the toolkit because that large amount of data on a sheet gives me OCD vibes where I felt like i needed to significantly reduce this to make it easier to find information and see it all on one page. Thankfully, David quickly reduced my OCD by introducing VLOOKUP which I had not heard of before.  

2 - VLOOKUP

Pulls adjacent data together to find specific information. It works by matching two cells from different sheets in a sheet formulae code follow "=vlookup(search key, range, index, is_sorted)". is_sorted should be false because it makes formulae information unmatched.  Very sequential. 

3 - ARRAYFORMULA

Allows you to use range formula that don't usually accept them. Useful for dynamic datasets like form response sheets. 

4 - QUERY 

run a sort of SQL-like query string against a range, treating it like a pseudo-databases. Pulls values from a list based on multiple conditions and formats them in a way you want. It is advanced but once you apply it, very useful.

I found this formulae really useful to learn. It will be helpful to use when working with school data information and I need only certain information for contacts/emergency lists  or sports results etc.. Also helpful for cleaning up and simplifying sheets that may have unnecessary information. Important to remember if data is importrange or not. Importrange data will mean queries will take data from previous sheet. Change A, B,C to Col1, Col 2, Col3 to resolve.