Introduction to Excel

Powerful Spreadsheet Wide Range of Data Visualization

Software Features Excel provides powerful
Excel allows you to Excel offers a variety of tools to create visually
organize, analyze, and features and functions to compelling data
visualize data with ease. tackle complex data-driven visualizations.
tasks.
Statistical Functions in Excel

Average Count Functions Sum Functions Min and Max

Functions The COUNT(), SUM(), SUMIF(),
Functions
Excel's AVERAGE(), COUNTIF(), and and SUMIFS() The MIN() and
AVERAGEIF(), and COUNTIFS() functions make it MAX() functions
AVERAGEIFS() functions enable easy to add up allow you to
functions allow you you to tally up the values in your data, quickly identify the
to calculate the number of values with options to smallest and
average of a in a range, with the filter and condition largest values in
dataset, with ability to apply the summation. your dataset.
advanced options criteria to the
to filter and counting process.
condition the data.
How to use the =AVERAGE function, step by step: AVERAGEIF Function: Calculates the average of a range based on
1.Select a cell (F2) a true or false condition.
2.Type =AVERAGE It is typed =AVERAGEIF and has three parts:
3.Double click the AVERAGE command =AVERAGEIF(range, criteria, [average_range])
4.Select a range (B2:E2) The condition is referred to as criteria, which can check things like:
5.Hit enter •If a number is greater than another number >
AVERAGEIFS Function: Calculates the average of a •If a number is smaller than another number <
range based on one or more true or false condition. •If a number or text is equal to something =
It is typed =AVERAGEIFS: The [average_range] is the range where the function calculates the
=AVERAGEIFS(average_range, criteria_range1, crit average.
eria1, ...) Note: The [average_range] is optional.
The conditions are referred to as critera1, If not specified, the function calculates the average of the same
criteria2, .. and so on, which can check things like: range as condition.
•If a number is greater than another number > Note: The different parts of the function are separated by a symbol,
•If a number is smaller than another number < like comma , or semicolon ;
•If a number or text is equal to something =
The criteria_range1, criteria_range2, and so on, are
the ranges where the function check for the COUNTIF Function: Counts cells as specified.
conditions. It is typed =COUNTIF
The average_range is the range where the function NOTE: The COUNTIF function can have basic or more advanced
calculates the average. uses. This covers the basic use for how to count specific
numbers and words.
COUNT Function: Counts cells with numbers in a
Numbers (e.g. 90) and words (e.g. "Water") can be specified.
range.
How to use the =COUNTIF function:
It is typed =COUNT
Select a cell, Type =COUNTIF, Double click
Note: The COUNT function only counts cells with
the COUNTIF command, Select a range, Type, Select a cell (the
numbers, not cells with letters.
criteria, the value that you want to count), Hit enter
How to use the =COUNT function:
Select a cell, Type =COUNT, Double click
the COUNT command, Select a range and enter
COUNTIFS Function: Counts cells in a range based on one or
SUMIFS Function: Calculates the sum of a range based
more true or false condition.
on one or more true or false condition.
It is typed =COUNTIFS:
It is typed =SUMIFS:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2,
=SUMIFS(sum_range, criteria_range1, criteria1, [cr
criteria2], ...)
iteria_range2, criteria2] ...)
The conditions are referred to as critera1, criteria2, .. and so on,
The conditions are referred to as criteria1, criteria2,
which can check things like:
•If a number is greater than another number > and so on, which can check things like:
•If a number is greater than another number >
•If a number is smaller than another number <
•If a number is smaller than another number <
•If a number or text is equal to something =
•If a number or text is equal to something =
The criteria_range1, criteria_range2, and so on, are the ranges
The criteria_range1, criteria_range2, and so on, are the
where the function check for the conditions.
ranges where the function check for the conditions.
SUM Function: Adds numbers in a range. The [sum_range] is the range where the function
It is typed =SUM calculates the sum.
Note: The =SUM function adds cells in a range, both negative
and positive. MIN Function: Finds the lowest number in a range.
How to use the =SUM function: Select a cell, Type =SUM, Double It is typed =MIN, The function ignores cells with text. It
click the SUM command, Select a range, Enter will only work for cells with numbers.
The SUMIF function calculates the sum of values in a range How to use the =MIN function: Select a cell ,
based on a true or false condition. Type =MIN, Double click the MIN command, Select a
It is typed =SUMIF: range, Enter
=SUMIF(range, criteria, [sum_range])
MIN Function: Finds the highest number in a range.
The condition is referred to as criteria, which can check things
It is typed =MAX, The function ignores cells with text. It
like:
•If a number is greater than another number > will only work for cells with numbers.
•If a number is smaller than another number < How to use the =MAX function: Select a cell ,
•If a number or text is equal to something = Type =MAX, Double click the MAX command, Select a
range, Enter
The [sum_range] is the range where the function calculates the
sum.
Work Sheets
Advanced Statistical Functions

Trend Analysis Correlation Forecasting Standard

Use the TREND()
Calculations The FORECAST()
Deviation
function to identify The CORREL() function allows you STDEVA()
and visualize function helps you to predict future calculates the
trends in your data understand the values based on standard deviation,
over time. relationship historical data. providing insights
between different into the spread of
data points. your data.
TREND Function: The TREND function returns values along a linear trend. Fits a straight line (using
the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that
line for the array of new_x's that you specify.
Syntax: TREND (known_y's, [known_x's], [new_x's], [const])

GROWTH Function: The GROWTH function calculates predicted exponential growth by using existing
data. GROWTH returns the y-values for a series of new x-values that you specify by using existing x-values
and y-values. You can also use the GROWTH function to fit an exponential curve to existing x-values and y-
values.
Syntax: GROWTH (known_y's, [known_x's], [new_x's], [const])
CORREL Function: The CORREL function returns the correlation coefficient of the Array1 and
Array2 cell ranges. Use the correlation coefficient to determine the relationship between two
properties.
Syntax: CORREL (array1, array2)
FORECAST Function: The FORECAST function calculates, or predicts, a future value by using existing
values. The predicted value is a y-value for a given x-value. The known values are existing xvalues and
y-values, and the new value is predicted by using linear regression.
Syntax: FORECAST(x, known_y's, known_x's)

MEDIAN Function: The MEDIAN function returns the median of the given numbers. The median is
the number in the middle of a set of numbers.
Syntax: MEDIAN (number1, [number2] ...)

STDEV.P Function: The STDEV.P function calculates standard deviation based on the entire population
Syntax: STDEV.P (value1, [value2] ...)

STDEVA Function: The STDEVA function estimates standard deviation based on a sample. The
standard deviation is a measure of how widely values are dispersed from the average value (the mean).
Syntax: STDEVA (value1, [value2] ...)
Logical Functions: IF ( )

Using IF() Logical Evaluation Diverse Applications

Excel's IF() function allows The IF() function assesses The versatility of IF() allows
you to evaluate a condition the truthfulness of a given you to build complex logic
and return one value if condition, and then into your spreadsheets,
true, and another if false. executes the appropriate automating decisions and
This enables powerful action based on the result. streamlining your workflow.
decision-making in your
spreadsheets.
Logical Functions: Nested IF
Examples

Nested IF Complex Decisions Efficient Logic

Combining multiple IF Handling a wide range of Optimizing decision-making
statements to build scenarios and making in your spreadsheets to
complex decision-making granular distinctions based streamline workflows and
logic. on your data. ensure clarity.

Excel's IF function becomes even more powerful when nested, allowing you to build complex
decision-making logic. By layering multiple IF statements, you can handle a wide range of
scenarios and make granular distinctions based on your data. This approach helps
streamline your spreadsheets and ensures your logic is clear and well-structured.
Leveraging AND() for
Sophisticated Spreadsheet Logic

Combine Multiple Nest with IF() for Sophisticated Data

Conditions Powerful Formulas Processing
Excel's AND() function By nesting AND() with Leveraging the power of
allows you to evaluate if other functions like IF(), AND() allows you to build
multiple logical conditions you can create highly complex decision-making
are all true. This enables sophisticated formulas that logic into your
you to build complex intelligently process your spreadsheets, ensuring
decision-making logic in data based on a set of specific criteria are met
your spreadsheets. specific requirements. before taking an action.
Logical Functions: OR()
Flexibility
1
Evaluate multiple conditions

Comprehensive Logic
2
Handle complex decision-making

Optimized Outcomes
3
Ensure desired results

Excel's OR() function allows you to combine multiple logical conditions, evaluating whether
at least one of them is true. This is invaluable for building flexible and comprehensive
decision-making logic in your spreadsheets. By nesting OR() with other functions, you can
create sophisticated formulas that ensure your desired outcomes are achieved, even when
faced with complex scenarios.
Logical Functions: NOT()
1 Negating Conditions
Excel's NOT() function allows you to reverse the logic of a
statement, returning the opposite of the evaluated condition.

2 Streamlining Formulas
By nesting NOT() with other logical functions, you can create more
concise and efficient formulas that handle complex decision-
making.

3 Enhancing Flexibility
The NOT() function gives you the ability to quickly flip the logic of
your spreadsheet, adapting to changing requirements or
unexpected scenarios.
The DAYS() Function

Calculating DifferencesTracking Deadlines Analyzing Trends

The DAYS() function allows By using DAYS() to Tracking the number of
you to calculate the compare a due date with days between key events
number of days between the current date, you can can unveil important
two dates, providing easily monitor progress and patterns and trends in your
valuable insights into time- ensure timely delivery of data, informing strategic
based metrics. projects. decision-making.
The NOW() Function

Dynamic Date and Generating Tracking Elapsed Time

Time Timestamps The dynamic nature of the
The NOW() function Use the NOW() function to NOW() function allows you
provides a real-time automatically generate to easily calculate the time
reference point for your timestamps, ensuring difference between events,
spreadsheet calculations accurate records of your providing valuable insights
and data analysis, keeping data's creation and into your data.
your data up-to-date. modification.
The TODAY() Function
1 Timestamps
The TODAY() function in Excel returns the current date, providing a
dynamic reference point for date-based calculations and data
analysis.

2 Tracking Progress
By comparing the TODAY() function to other dates, you can monitor
deadlines, project timelines, and the passage of time within your
spreadsheets.

3 Automating Processes
The TODAY() function can be used to automate date-related tasks,
ensuring your spreadsheets stay up-to-date and relevant as time
goes on.
The YEAR() Function
Extracting the Year
1
The YEAR() function retrieves the year from a given date value.

Analyzing Trends
2 Isolating the year component allows you to identify
patterns and trends over time.

Conditional Formatting
3 YEAR() can be used in conditional
formatting rules to highlight data based
on the year.

The YEAR() function in Excel is a powerful tool for working with dates and analyzing time-
based data. By extracting the year component from a date, you can easily perform year-
over-year comparisons, identify seasonal patterns, and even set up conditional formatting
rules to highlight important data points.
Other Examples of Logical Functions

COUNTIF() SUMIF() AVERAGEIF()

Count the number of cells Sum the values in a Calculate the average of
that meet a specified range that meet a cells that meet a
criterion, like counting specified criterion, like specified criterion, like
how many sales totaling sales for a finding the average sales
exceeded a target particular product per region.
amount. category.
Logical Operators in Excel

Combining Conditions Powerful Conditional Informed Decision-

Excel's logical operators,
Logic Making
including AND(), OR(), and By combining logical Logical operators and
NOT(), allow you to operators with IF conditional logic empower
evaluate multiple statements, you can build you to extract meaningful
conditions simultaneously sophisticated, context- insights from your data and
within IF statements. This aware formulas that make informed, data-driven
enables you to create automate complex decisions within your Excel
flexible, data-driven decisions based on your workbooks.
decision-making in your data.
spreadsheets.
Logical Function Automate Decision- Gain Deeper Insights
Examples Making Leverage logical functions
Explore practical examples Logical functions can save to uncover meaningful
of how to use logical time, improve accuracy, patterns and trends in your
functions in Excel to build and provide deeper data, empowering you to
sophisticated data models insights into your data by make informed, data-driven
and automate complex automating complex decisions.
decision-making processes. decisions.