Hi Everyone! 

You all know how much I love spreadsheets and love building them for other people. I also like to help other people learn how to use them. So I decided to make a little forum series on some spreadsheet-building tips & tricks. Here is the first part: How To Organize Your Data & Some Handy Shortcuts that will save your life! 

Please drop some requests in the comments for things you'd like to know how to do and I will make tutorials for them. Otherwise, I'm just gonna make stuff up! 

Spreadsheets 101

How to Organize your Data

Overall the best data practice is to have a header row, with a column for each attribute you want to tracking. It is OK if your data is very wide, but splitting it up into multiple rows causes problems later down the road when you try to look things up using formulas.

Example: Notice the header row at the top (I usually make this a different color for the sake of visibility), and one row per horse, with all of the attributes (cells) filled in.

Shortcuts I Use All the Time! (Google Sheets)

Ctrl + C, Ctrl + V (standard copy and paste) = this will take the exact data in the cell and copy it to another place. If you have formulas in the cell, it will update the formula based on the new location, unless your cell references are locked (more on this later).

Ctrl+C, Ctrl+Shift+V = copy/paste values. This pastes the result of a formula. Lets say cell A2 has a formula that is 2+2=4 (=2+2=4), and I just want to paste the result of that formula in another cell. I can use this shortcut just to paste the result (4) into another cell, without copying the formula

Ctrl+C,Ctrl+Alt+V = copy/paste formatting. Want to make your cells look pretty without a lot of manual coloring? Use this short cut to automatically paste formatting from one cell to other cell, or a group of cells!  

Ctrl+DownArrow = go to the bottom most cell in a column without highlighting (works for any directional arrow)

Ctrl+Shift+DownArrow = go to the bottom most cell in a column and highlight all the cells in between. (works for any directional arrow)

Ctrl+H (Find and Replace) = use this to locate values in a specifc range, and replace them with other values. Similir to Ctrl+F (Find), but more powerful. 

Cell References

Any cell in a spreadsheet can be referenced by it’s column letter and row number. (Cell A1 is the first cell in any spreadsheet). You can use cell references to create dynamic formulas that update automatically based on the data in those cells. There are two types of cell references – movable and fixed. Movable cell references will update if you copy the formula to a different cell.

Example: I have a formula in A1 that calculates off A2 and A3, if I copy the formula to B1, it will now calculate off B2 and B3.

Fixed cell references reference the same cell no matter if you move the formula or not. Using the above example, if I used fixed references, the formula would still calculate off of A2 and A3 even if I copied the formula to cell B2 (Or Z91 or H14 or M20).

How to Write Cell References:

Movable: A1

Fixed: $A$1

Fixed Column, Movable Row: $A1

Movable Column, Fixed Row: A$1

Intro to Formulas

How to calculate your horses’ show score

How to calculate your horses’ rank score

How to calculate your horses’ BV

How to pair your horses – using math!