#18 Data Visualization

Data Viz in Spreadsheets

Named ranges

• Named ranges
• can refer to a cell, range, const value, or a formula with a given name
• user friendly names that make it easy to read / understand
• range references can be changed globally

• How to add named ranges
• Don’t use spaces in named ranges “name”
• Select range. Data > Named ranges

• Named ranges can be used with formulas
• Instead of passing a direct range as argument, pass a named range

Data Validation

• Dropdown is the simplest way to limit the options and allow for data validation

• Data Preparation
• Formatting column headings
• Remove blank rows / columns
• Format dates and decimal places
• Add named ranges

• Creating Data Validation
• Data > Data Validation.
• Select Named range
• “Show Warning” & “Reject Input” can be modified based on need
• Can also check “show validation help text” option

• Absolution is denoted by $
• $A3 always referring to column A
• Row reference will change as it is copied further
• locking on column A

More Chart types

• Histograms
• used to chart quantitative data, numerical distribution that is grouped into bins (intervals)
• There are no gaps between series. A missing bin would mean, zero values in that bin.
• Item dividers segments columns so we can see how many lots are in each bin

• Candlestick
• Each candle stick shows one day of behavior
• Dates need to be formatted as text to be used as for candle sticks. Use text() formula

• Scatter
• correlation between two sets of data, or relation between two variables
• Trend lines can be super imposed too. they are linear by default.
• Linear : Stead state increase or decrease
• Logarithmic : inc/dec quickly and then balance out
• Polynomial : fluctuating data
• Power : inc at a specific rate
• Moving Average and Exponential

• Sparkline charts
• Created using a formula sparkline. Uses json like arguments to add customizations
• Useful for showing trends and do not have axes
• Work better in a single cell
• Types of sparkline
• Line
• Bar
• Column
• Winloss

Conditional Formatting

• It works by using rules, these can be a custom formula too.

• Pallet can be set by specifying the max, min and mid points

• If the rules conflict, higher rule takes precedence.

• To highlight duplicates
• =countif($A:$A,A3)>1 will count the value in A3 and highlight the duplicated one

• Make dashboards look better
• Add borders to the charts to make it look better
• Hide the formula and tool bar