Mastering Google Sheets for Data Enthusiasts: Notes and Learning Insights from Data Camp (Google Sheet Fundamentals)

Introduction

Hello, fellow learners and spreadsheet enthusiasts! Today, I'm excited to share some insights from my journey learning about Google Sheets through DataCamp. Whether you're a beginner or looking to refresh your skills, I hope my notes will inspire you to explore the power of Google Sheets. In today's data-driven world, being proficient in tools like Google Sheets can significantly enhance your ability to analyze and present data effectively; it offers a user-friendly interface, collaborative capabilities, and numerous features that make it valuable for both personal and professional use.

Topic Outline

Google Sheet Fundamentals

Introduction to Google Sheets
Data Analysis in Google Sheets
Intermediate Google Sheets
Pivot Tables in Google Sheets
Data Visualization in Google Sheets

Intermediate Google Sheets

Introduction to Statistics in Google Sheets
Error and Uncertainty in Google Sheets
Marketing Analytics in Google Sheets

Finance Fundamentals in Google Sheets

Financial Analytics in Google Sheets
Financial Modelling in Google Sheets
Loan Amortization in Google Sheets

Google Sheet Fundamentals

I. Introduction to Google Sheets

1. Cells and Formulas

Navigating Google Sheets

Google Sheets

Millions of users worldwide
Intuitive interface
Extract insights with a few clicks
It stores tabular
Data is stored in individual cells.

Cell Addresses

Cells can be uniquely identified by a column (letters) and row labels (numbers)
Cell addresses combine the column and row label

Cell Ranges

a cell range defines a rectangular group of cells.

Formulas

calculate results from other values
support common arithmetic operations
allow interested stakeholders to see how values are calculated

Writing Formulas

tells the spreadsheet that we are writing a formula in the cell

complex formula = (2+4)/3-2

Parenthesis
Exponent
Multiplication and Division
Addition and Subtraction

Google Sheet Data Types

Types of Data seen so far

Number
Text
Currencies

Every cell value has a data type
Cell data types are automatically detected, but can also be manually assigned
Data Type determines:

What operations can be performed on the cell value
How the values are displayed

Data Type: Number

any number inputted into a cell
allow arithmetic operations and other statistical operations
numerical data is right-aligned, by default

Data Type: Plain Text

assigned to cell values if no other data types are detected
can force a cell to plain text using the Format> Number> Plain Text
can force a cell to plain text by prepending the contents with plain text
left-aligned by default

Data Type: Date

many different date formats:

2020-07-29
20/7/2022
July 29, 2000

can auto detect many date formats
manually specify or convert dates
Right-aligned by default

Data Type: Currency

cells starting with $, ¥, ₱, other currency symbol
Number > Currency
Right-aligned by default

Data Type: Logical

Logical/Boolean values: TRUE and FALSE
Logical values are case sensitive
Different cases will be converted
Center-aligned by default

Comparison Operators

equal to =
not equal <>
greater than >
less than <
greater than or equal to >=
less than or equal to <=

2. Cell References

Cell references point to cells and retrieve their values
Absolute References

don't change when copied
will update if the reference cell is changed

II. Data Analysis in Google Sheets

1. Exploring Data

Data Analysis: a process of extracting meaningful insights from data.

1. Formulate Problem
2. Collect and Stored Data
3. Explore Data
4. Clean Data
5. Analyze Data
6. Present Findings

Built-in Functions - pre-written calculations that are available in formulas.
Round()

Syntax: =Round(value, [places])
Value >> required arguments
Places >> optional argument: places 0 by default

Summary of Statistics

Measures of Frequency

Count() = count cells containing numerical data (date and currencies)
CountA() = count cells containing any data type (empty strings and errors)
Countblank() = count blank cells (empty cells and empty strings)

Measures of Center - aim to describe a typical value

Mean = Often referred to as "average"

Average() - sum of values / count of values

Median = the middle number in a sorted list of values

Median() - used when there are outliers

Identifying data quality issues

Missing Data

Countblank()
Count()
CountA()

Erroneous Data

Max() - Maximum value in a range
Min() - Minimum value in a range

Filtering and Sorting

Finding unique values - categorical data can only be one of a finite number of values

Unique() = find the number of unique values

Filtering - extract subsets of the datasets for more detailed exploration

Filter()
Syntax: Filter ( range, condition1, [condition2,...])

range - to be filtered
conditon1 - condition to filter

Sorting - identify largest and smallest values, useful for identifying potential outliers

Sort()
Syntax: Sort(range, sort_column, is_ascending,..)
Range: the cells to sort
Sort_column: to base sorting on
Is_ascending (TRUE/FALSE): the sorting order

2. Cleaning and Preparing Data

80/20 Rule: 80% Cleaning, 20% Analyzing
Clean Dataset

can be easily processed
will return a valid conclusion
save more time during analysis

Dates and Times

collected and measurements over time
continuous data: can take any value
discrete date: can take one of a finite # of categories
Extracting its component

year(date)
month(date)

Extracting a desirable month formats

Month name component

Text(numbers, format)

Short month format >> "mmm"
Long month format >> "mmmm"

Extracting weekday component

weekday(date, [type])

type: the number system to use 1 (default): start sunday = 1; 2 start Monday = 1

short weekday

Text(number, format)

Short weekday format - "ddd"
Long weekday format - "dddd"

Extracting Time Component

Hour(time)
Minute(time)
Second(time)

Calculating Date Intervals

=Today() - spreadsheet is refreshed, it will update
=Now() - date and time
Datedif(start_date,end_date, unit)

end_date > start_date
unit: "y", "m" , "d"...
Results are chopped

Cleaning Text Data

Proper() - proper case
Upper() - upper case
Lower() - lower case

Removing whitespace

TRIM () - remove extra whitespace:

Leading - space before text
Trailing - space after text
Repeated - >1 space between characters

Combining Text Data

concatenate(string1, [string2,...])

Manipulating Text Data

Searching for characters

SEARCH(search_for, text_to_search, [starting_at])

search_for : string to search for
text_to_search: text to search through
starting_at (default = 1) index to start at

Extracting Text

Left()
Right()

Substituting Characters

SUBSTITUTE(Text_to_search, search_for, replace_with, [occurence_number])

Text_to search: the text to search through
Search_for: the string to search for
replace with: the replacement string
occurence number: which occurence should be updated

3. Analyzing Data

conditional functions: returns different results depending on criteria
IF(logical _expression, value_if_true, value_if_false)
AND(logical_expression1,[logical_expression2...])
OR(logical_expression1,[logical_expression2...])
Conditional Aggregations

countif(range, criterion)

criterion:

string to match, e.g. "United States
number to match, e.g. 150
string containing a number and comparison operator, e.g. ">9"

countifs(criteria_range1, criterion1, [critera_range2,criterion2,...])
sumif(range, criterion, [sum_range])

range: range to apply the criterion to
sum_range(optional): can specify a range to sum

sumifs(sum_range,criterion_range1, criteria1, [critera_range2,criterion2,...])

The power of VLOOKUP

vertical lookup - search for information in a table based on search keys
Vlookup(search_key, range, index, [is_sorted])

search_key - the value in main table to search for in the lookup table
range - the range containing the lookup table (usually absolute reference)
index - the column index in the lookup table to return
is_sorted: TRUE/FALS to indicate is the lookup table is sorted

III. Intermediate Google Sheets

1. What's in a cell?

Four Common Data Types

Numbers (123.456)
Text ("Hello")
Dates (1990-02-29)
Logical (True)

ISNUMBER() checks if a cell contains a number
ISTEXT() checks for dates and times
ISLOGICAL() checks for TRUE and FALSE
ISURL() checks if text is web
ISFormula() checks if the cell contains a formula
Copying formulas between cells

Drag the bottom right corner of the cell to copy. Alternatively,

To copy right >> use CTRL + R
To copy down >> use CTRL + D

To*() function format numbers. For example,

To_percent()
To_dollars()

N() changes text to numbers
IF(logical, 1, 0) changes logical numbers
Convert() changes the units of a number. It takes three arguments: a number or a cell address, the existing unit (in double quotes), and the unit to convert, e.g. convert(1, "hr", "sec").

2. Working with Numbers

Logarithmic and Exponential Transformation

Log10() and LN() perform logarithmic transformation.
10^x and Exp() perform exponential transformation
SQRT() perform square root transformation

Rounding and Formatting Numbers

Round (x , n) rounds x to the nearest n decimal places
Ceiling (x , y) rounds x up to the nearest multiple of y
Floor (x, y) rounds x down to the nearest multiple of y
Floor.math() >> e,g. Floor.math(-1.57,0.1,1) = -1.50
Celing.math() >> e.g. Ceiling.math (-1.57,0.1,1) = -1.60

Generating Random Numbers

Rand() generated random uniform numbers between 0 and 1.
RandBetween() generates random uniform integers between two limits.
NORMINV(Rand()) generates random normal numbers.

Syntax: NORMINV(RAND(), mean ,standard deviation)

*INV(Rand()) generates random numbers from other distributions.

3. Logic and Errors

Logical operations

Not() swaps TRUE and FALSE
AND() returns TRUE when all inputs are TRUE
OR() returns TRUE when any inputs are TRUE

Flow Control

IF(condition, yes, no) lets you return a value based on a logical condition.
IFS(condition1, value1, condition2, value2) extends this to multiple conditions.
SWITCH(condition, category1, value1, category2, value2)

Blanks, Missing Values, and Errors

Types of Errors

#Div/0! - Dividing by Zero
#Value! - Nonsense data in calculation
#REF! - Referencing a cell that has been deleted
#Name? - Forgetting to quote a string
#Num! - Numbers being out of range
#N/A - Missing value
#Error! - Syntax problem in a Formula

Cells with nothing in are called "Blank"
Calculating with blank cells will give you the wrong answer. Instead, use NA() to create missing values. Missing values are a type of error.
ISBLANK() - checks if blanks
ISERROR() - test for errors
ISERR() - test for errors but does not consider missing values to be errors.
COUNTBLANK() - accepts a range of cells and returns the number of blanks in that range.

4. Positional Matching

Cell Addresses

There are two ways of specifying a cell address.
Row() and Column() returns integer position. It takes address in A1 format and return the number of the row and column respectively.
Address() returns the address in A1 format text. It also has an optional argument to specify the relativity of the addresses. 1 (default) return absolute addresses. 2, 3, and 4 return row absolute, column absolute, and relatively address respectively.
Indirect() returns the value of an address.
Offset() returns values relative to another cell. It retrieves the values in cells offset from the current location by a certain number of rows down to move from the current location by a certain number of rows and columns.
Syntax: Offset(reference, rows, columns, [height],[width])
Index() finds value within a cell range.
Syntax: Index (reference, row, [column])

Lookup & Matching

VLOOKUP() perform left joins on two datasets. It takes four arguments.

First is the value that you are looking for, usually a string.
Second is the data range, usually specified using absolute coordinates. The first column must contain the lookup values.
Third is the column offset, the same as with Index().
Fourth is whether or not the data is sorted by the lookup column. Usually, you need to specify FALSE here.

SORT() programically sorts data.

First: range of the dataset.
Second: number of the column to sort on. starting with 1 as the left-most column.
Third: set the sort direction

TRUE - ascending order
FALSE - descending order

If you want to break ties by sorting on further columns, you can pass another column index and another direction for each column that you want to sort with. (e.g. SORT($D$2:$H$100, 3, FALSE, 1 , TRUE)

MATCH() finds positions in sorted data where a value would occur.
(e.g. MATCH(1000, B2:B100, -1)

First: limit value
Second: data range
Third: 1 if the column is sorted in ascending order and -1 descending order.

IV. Pivot Tables in Google Sheets

1. Introduction to Pivot Tables for Google Sheets

Introduction to Pivot Tables

Basic Pivot Table Features

Filter
Sort
Subtotal
Analyze

Two Ways to Create a Pivot Table

1st Way: Click on any of the cells within the dataset, then click on the Data tab on the ribbon, and select Pivot Table.
2nd Way: Create a pivot table is to manually select the entire dataset range first, including the headers, and then go to Data on the ribbon, followed by Pivot Table.

Pivot Table Rows and Columns

Either Drag and Drop or Select Add and click for the appropriate header

Pivot Table Values and Columns

Either Drag and Drop or Select Add and click for the appropriate header

2. Behind the Scenes of the Pivot Table

How a Pivot Table Works
Using Filters in a Pivot Table

3. Advanced Options

Changing the calculation of values

Counting Calculations

COUNT: Counts numerical values only
COUNTA: Counts all non-blank values
COUNTUNIQUE: Counts all non-blank values (excluding duplicates)

Ranking Calculations

Average, Max, Min, Median

Mathematical Calculations

Product

Statistical Calculations

Standard Deviation, Variance

Calculated Fields

3. Editing Data and Troubleshooting

Adding or Changing Data

Changing Data

Simply change the original dataset

Adding Data

Add a new row below your dataset
Adjust the Source in the Pivot Table Editor

Troubleshooting Errors

Using COUNTA to identify missing data
Fixing incorrect labels

Go back to the dataset and use the Find function, by holding Ctrl and hitting F

Numbers formatted Text

Change it into Number Format

Best Practices and Recommendations

Row & Columns

Descriptive Fields: Non-numerical fields such as Industry, Year, or Product Type
Use these fields to sort and organize data

Values

Measurements: Numerical categories such as Revenue, Profit, or Inches of Rainfall
Use these fields to perform mathematical calculations

When not to use a pivot table

Small Datasets - Consider using Sort, Subtotal, & Filter
Strict Formatting - Precise control of layout and formatting may not be available with a pivot table.

IV. Data Visualization in Google Sheets

1. Using Sheets as a Business Intelligence Platform

Business Intelligence helps us in..

Decision-making
Measuring Goals
Operational Efficiency
Optimizing processes
Pinpointing Opportunities

What message are you trying to convey?

What information?
Who is your audience?
How are they viewing it?

What else matters?

Logos
Scenarios
Colors
Do you want to use 3D objects?
How much detail is too much?
What charts do you want?

Setting up a Basic Dashboard

Extracting your Data

Keep the message in mind
Pull only the data you need
Formula of reference

Formulas of reference

Calculation that picks up a value from another cell
=Sheet2!A1

Point to Sheet 2 cell A1 and return what is in cell A1"

Setting up your Data

Setting up your data in tables or lists
Structure in your data
Future Requirements
Formatting your Data Matters

Bold or Italicize Headings
Format Only what you need to

Professionalism and Consistency

2. Efficient Column Charts

Tables and Lists

Table - related data stored in columns and rows
List - manage or analyze a smaller set or sets of data

Axis, gridlines, and changing the plot
Named Range

What is a Named Range?

One cell, a range, constant value, or a formula with a given name
User-friendly names for your cells
Make formulas easier to read and understand
Range reference can be changed globally in one place

Selecting Data for the Named Range

Highlight the range, go to the toolbar, select Data, select Named Ranges
Replace default, "NamedRange1" with a meaningful name.
Remember, Named ranges do not like spaces

Creating a Named Range with the Toolbar

Select Data, Named ranges, Add a range, name it
Select the Data for your range

Editing your Named Ranges

Go to Sheets, Data, Named range to see all Named ranges in your workbook
To change the cell range, select it by clicking the grid to the right
Less prone to error if you have a visual of your range

3. Dashboard Controls

Data Validation

Creating the Data Validation

Under the Dates Heading, select Data, Data Validation
Used your Named Range
"Show warning" to warn user of incorrect entry
"Reject Input" to reject anything other than selection from list

Using VLOOKUP with data validation

Vertical Lookup
Retrieve data from tables
Find an exact match. or closest possible match, in a list
Uses dates selected through data validation and lookup from main dataset
Creating the line plot

Use data validation and VLOOKUP to show only relevant values in your data
Create a line chart

Highlight that range you want to plot, including headings
Click Insert chart icon, select line chart

4. Other Charts for Your Dashboard

Other Charts for your Dashboard

Histograms
Candlestick Charts

Display price information
Predict future behavior
Display day to day market movement
Optimize your dataset first

Named Ranges
Validations
VLOOKUPs

Scatter Charts
Sparkline in your Dashboard

Give a quick snapshot
Work better in a single cell
Type of Sparklines

Line
Column
Bar
Winloss

Sample Syntax: = Sparkline(A1:A10, {"charttype", "column"; "color", "red"})

5. Conditional Formatting

Using Formulas and Tidying up the Dashboard

Some common uses for formulas

Highlight a row that meets a certain condition, rather than a cell
Comparing lists and values
Highlight duplicates and discrepancies
Highlight specific number or text entry
Highlight alternate rows
Enable the use of a dynamic named range
Find errors and blanks

Puzzle.io -Accounting Software