Embedded Files
July 17, 2019
BASIC TIPS AND TRICKS IN CREATING GOOGLE SHEETS TO TRACK DATA
BASIC TIPS AND TRICKS IN CREATING GOOGLE SHEETS TO TRACK DATA
A Row is a group of cells that run horizontally. Rows are numerically labeled.
A Column is a group of cells that run vertically. Columns are alphabetically labeled.
Cells are the intersection of a Row and Column.
Cells are identified by first stating the intersecting column letter followed by the intersecting Row number.
For example, D5 is an example of a cell address in the picture to the left.
HOW TO DRAG FORMULAS/TEXT DOWN THE COLUMN
HOW TO DRAG FORMULAS/TEXT DOWN THE COLUMN
Why? If you want your spreadsheet to auto-populate, you need to enter functions.
You can drag the formulas that you entered in one cell to the other cells.
HOW TO FREEZE THE TOP ROW
HOW TO FREEZE THE TOP ROW
Why? If your spreadsheet will collect multiple rows of data and you want the heading to repeat, you freeze the top row.
You can sort your data once you freeze a row!
HOW TO DRAG FORMULAS DOWN THE COLUMN
HOW TO DRAG FORMULAS DOWN THE COLUMN
Why? If you want your spreadsheet to auto-populate, you need to enter functions.
You can drag the formulas that you entered in one cell to the other cells.
For example, I want the second column to show the day of the week. I will use the formula
=TEXT(A2,“DDDD”)
Where A2 = the CELL that you are pulling the date from
HOW TO GRAPH DATA BY THE DAY OF THE WEEK
HOW TO GRAPH DATA BY THE DAY OF THE WEEK
STEP ONE.
Create a total points column using the formula
=SUM(CELL BEGINNING:CELL ENDING)
Example
=SUM(C2:E2)
STEP TWO.
Highlight the columns you want in a graph.
For multiple columns, hold CTRL key.
I added the chart to the own sheet so that I can see it better.
I changed the setting to “aggregate” so all of the similar items in Column B are grouped together.
I used AVERAGE because there was not an even amount of data per weekday.
This also helps me interpret it since there were 3 points possible per day.
HOW TO GRAPH DATA BY WEEK
HOW TO GRAPH DATA BY WEEK
STEP ONE.
Create a “Week Of” column using the formula:
=IF(ISBLANK(A2),””,A2-WEEKDAY(A2,2)+1)
Why? The “Week Of” is helpful to track data per week or progress over time.
=IF(ISBLANK(A2),””,A2-WEEKDAY(A2,2)+1)
A2 = the CELL that you are pulling the date from
The first formula is the same as:
=A2-WEEKDAY(A2,2)+1
The ISBLANK added before helps when you set up a spreadsheet with formulas so you don’t get error messages when there no data has been entered.
STEP TWO.
Highlight the columns you want in a graph.
For multiple columns, hold CTRL key.
QUICK FORMULAS
QUICK FORMULAS
ADD FORMULA OR FUNCTION
ADD FORMULA OR FUNCTION
Type an equal sign (=) in a cell and type in the function you want to use.
COUNT RANGE
COUNT RANGE
=COUNT(RANGE)
range = the cells that you want to count how many numbers are entered
ADD SUM
ADD SUM
=SUM(RANGE)
range = the range of cells you want to sum
example =SUM(A1:A10)
ADD AVERAGE
ADD AVERAGE
=AVERAGE(RANGE)
range = the range of cells you want to average
example =AVERAGE(A1:A10)
DATA FROM OTHER SHEETS
DATA FROM OTHER SHEETS
Type = followed by
The sheet name
An exclamation point
The cell being copied
For example, =Sheet1!A1 or =’Sheet number two’!B4
GENERATE WEEKDAY
GENERATE WEEKDAY
=TEXT(A2,“DDDD”)
A2 = the cell that you are pulling the date from
WEEK OF
WEEK OF
=IF(ISBLANK(A2),“”,A2-WEEKDAY(A2,2)+1)
A2 = the CELL that you are pulling the date from
GENERATE AGE
GENERATE AGE
=DATEDIF(A2,TODAY(),“Y”) & ” YEARS, ” & DATEDIF(A2,TODAY(),“YM”) & ” MONTHS “
A2 = the cell that you want to calculate
=TEXT(A2,“DDDD”)
A2 = the cell that you are pulling the date from
Page updated
Report abuse