Analytics is a scientific process used to examine raw data to draw
meaningful and logical conclusions.
Types of Analytics
Descriptive Predictive
The sort and filter functionalities are
Diagnostic Prescriptive available to order or filter the data for
further analysis.
Group by and ungroup allow data to group data by collapsing and expanding
rows with similar content to create more compact and understandable views.
Formatting helps format the data using different techniques, making the
data easy to read and analyze
Functions in Excel
Vlookup Hlookup And If Not Rank Quartile
Logical Functions
If False And Or Not True
Lookup and Reference Functions Statistical Function Types
If SUMIFS COUNTIFS
False
PERCENTILE QUARTILE
And
STANDARD
MEDIAN
Or DEVIATION
Not RANK MODE
Pivot tables are used to summarize, analyze, explore, and present the
data in the form of a table.
Slicer is a filtering component that A dashboard is a real-time tool
allows narrowing down the data and with an easy-to-use user
extracting necessary information interface that displays data in a
in the pivot table. graphical format.
Charts represent data graphically, making it easy to analyze comparisons and trends.
Line chart Pivot chart
Column chart Speedometer chart
Bar chart Stacked column chart
Pie chart Funnel chart
Thermometer chart Types of Charts Chart with combo box
Pareto chart Chart with scrollbar
Form controls are objects that allow one to The What-if analysis feature allows one to
interact with their data in Excel. manipulate data with ease.
What-If Analysis Tools
Goal Data Scenario
Solver
seek table manager
Descriptive statistics is a data analysis tool that produces a summary of the
key statistics for a dataset.
Statistical analysis involves the collection, examination, summarization, manipulation, and
interpretation of quantitative data to discover underlying causes, patterns, relationships, and trends.
Statistical Analysis Tools
Analysis TookPak Correlation Regression
Hypothesis testing ANOVA t-Test
Covariance
Macros is an important feature in Excel that permits to do VBA programming
within the Excel workbook.
Visual Basic for Applications (VBA) allows a programmable
interface to Excel.
Types of Macros
Subroutine
Event-
/Sub Functions
based
procedure
Mean is defined as the sum of values in a data set
divided by the number of values in the data set.
Values of Five Point Summary Simple Linear Regression (SLR)
y = function(x)
Minimum value
25th percentile value (Q1) Multiple Linear Regression (MLR)
y = β0 + β1x1+ β2x2 +… + βixi + e
50th percentile value (Median or Q2)
Where:
y: dependent or resultant variable
75th percentile value (Q4)
x1, x2, x3,…,xi: independent variables
β0: constant term in the equation
Maximum value βi: slope coefficients to each independent variable
e: error term
Logistic Regression Sigmoid Equation
P(Y=1) = 1 / (1 + e^-(β0 + β1x1 + β2x2 + … + P(E) = 1 / (1 + e^-x)
βnxn))
Odds of event (E) is defined as the probability Correlation Coefficient
of E happening divided by the probability of E
not happening.
odds(E) = P(E)/1-P(E)
Formulas and Functions Used in Excel
Arithmetic Operations
Addition (`+`): `=A1+B1` adds the values in cells A1 and B1.
Subtraction (`-`): `=A1-B1` subtracts the value in B1 from the value in A1.
Multiplication (`*`): `=A1*B1` multiplies the values in cells A1 and B1.
Division (`/`): `=A1/B1` divides the value in A1 by the value in B1.
SUM: The SUM function adds up a range of AVERAGE: The AVERAGE function calculates
cells. For example, `=SUM(A1:A10)` adds up all the average of a range of cells. For example,
the numbers in cells A1 through A10. `=AVERAGE(A1:A10)` calculates the average of
the numbers in cells A1 through A10.
COUNT: The COUNT function counts the MAX and MIN: The MAX and MIN functions find
number of cells in a range that contain the highest and lowest numbers in a range,
numbers. For example, `=COUNT(A1:A10)` respectively. For example, `=MAX(A1:A10)` finds
counts the number of cells with numbers in the highest number and `=MIN(A1:A10)` finds the
cells A1 through A10. lowest number in cells A1 through A10.
VLOOKUP: The VLOOKUP function looks for a
IF: The IF function performs a logical test and certain value in the leftmost column of a table,
returns one value for a TRUE result, and and then returns a value in the same row from
another for a FALSE result. For example, a column you specify. For example,
`=IF(A1>10, "Over 10", "10 or less")` checks if `=VLOOKUP("Apple", A1:C10, 3, FALSE)` looks
the number in cell A1 is greater than 10, and for "Apple" in cells A1 through A10 and returns
returns "Over 10" if true, or "10 or less" if false. the corresponding value from the third column
(C1 through C10).
COUNTIFS: This function counts the number
SUMIFS: This function adds up cells that meet
of cells in a range that meet multiple
multiple conditions. For example,
conditions. For example, `=COUNTIFS(A1:A10,
`=SUMIFS(A1:A10, B1:B10, "Apple", C1:C10,
"Apple", B1:B10, ">20")` will count the
">20")` will add the values in A1:A10 where the
instances where "Apple" appears in cells
corresponding value in B1:B10 is "Apple" and
A1:A10 and the corresponding value in
in C1:C10 is greater than 20.
B1:B10 is greater than 20.
AVERAGEIFS: This function calculates the
average of cells that meet multiple conditions. IFERROR: This function is used to handle
For example, `=AVERAGEIFS(A1:A10, B1:B10, errors in your formulas. For example,
"Apple", C1:C10, ">20")` will calculate the `=IFERROR(A1/B1, "Error")` will return "Error" if
average of values in A1:A10 where "Apple" is the division operation (A1 divided by B1)
in B1:B10 and the corresponding value in results in an error.
C1:C10 is greater than 20.
INDEX MATCH: This is a powerful
Concatenate: This function combines text
combination used to perform lookups. It is
from two or more cells into one cell. For
more flexible than VLOOKUP.
example, `=CONCATENATE(A1, " ", B1)` will
`=INDEX(B1:B10, MATCH("Apple", A1:A10, 0))`
combine the text in cells A1 and B1 with a
will return the value in B1:B10 where "Apple"
space between them.
appears in A1:A10.
Logical Functions: AND, OR, NOT are logical
LEFT, RIGHT, MID: These functions extract a functions that can be combined with other
specific number of characters from a text functions to increase their power.
string from the left, right, or middle. For For example, the formula `=IF(AND(A1>10,
example, `=LEFT(A1, 5)` will give you the first 5 B1>20), "Yes", "No")` will return "Yes" only if
characters from the left in cell A1. A1 is greater than 10 and B1 is greater than
20; otherwise, it will return "No".