Scribd
Upload
50 views

Excel Formulas and Functions_ A Detailed Guide

This document is a detailed guide on using Excel formulas and functions, covering the basics of formulas, cell references, and common functions like SUM, AVERAGE, and VLOOKUP. It provides step-by-step instructions for entering formulas, creating custom formulas, and tips for error checking and using AutoFill. The guide emphasizes the importance of mastering these tools for effective data management and analysis in Excel.

Uploaded by

Штефырца Елена
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
50 views

Excel Formulas and Functions_ A Detailed Guide

This document is a detailed guide on using Excel formulas and functions, covering the basics of formulas, cell references, and common functions like SUM, AVERAGE, and VLOOKUP. It provides step-by-step instructions for entering formulas, creating custom formulas, and tips for error checking and using AutoFill. The guide emphasizes the importance of mastering these tools for effective data management and analysis in Excel.

Uploaded by

Штефырца Елена
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

Excel Formulas and Functions: A

Detailed Guide
Introduction
Microsoft Excel is a powerful tool for data analysis and organization, but its true strength lies
in its formulas and functions. This lesson provides a step-by-step guide for understanding
and using Excel formulas effectively.

1. Basics of Excel Formulas


What is a Formula?

A formula is an expression used to perform calculations in Excel. Every formula starts with
an equals sign (=). This tells Excel to calculate something rather than treat it as text.

How to Enter a Formula

1.​ Select a cell (e.g., A1).


2.​ Type = followed by your expression (e.g., =2+2).
3.​ Press Enter, and Excel will calculate the result.

Basic Arithmetic Operators

●​ +: Addition (e.g., =5+3 results in 8)


●​ -: Subtraction (e.g., =5-3 results in 2)
●​ *: Multiplication (e.g., =5*3 results in 15)
●​ /: Division (e.g., =5/2 results in 2.5)
●​ ^: Exponentiation (e.g., =5^2 results in 25)

2. Understanding Cell References


What are Cell References?

A cell reference points to a specific cell or range of cells in Excel. Using cell references
makes formulas dynamic, meaning they update automatically when the referenced data
changes.
Types of Cell References

1.​ Relative Reference: Changes when copied to another cell.


○​ Example: =A1+B1 will adjust to =A2+B2 if copied down.
2.​ Absolute Reference: Does not change when copied.
○​ Syntax: Use $ to lock rows/columns (e.g., =$A$1).
○​ Example: =A1*$B$1 keeps $B$1 constant.
3.​ Mixed Reference: Either the row or column is locked.
○​ Example: =A$1 or =$A1.

3. Common Excel Functions


SUM

Adds a range of numbers.

●​ Syntax: =SUM(number1, [number2], ...)


●​ Example: =SUM(A1:A10) adds all numbers in cells A1 through A10.

AVERAGE

Calculates the mean of numbers.

●​ Syntax: =AVERAGE(number1, [number2], ...)


●​ Example: =AVERAGE(B1:B5) finds the average of values in B1 through B5.

IF

Performs a logical test and returns different values for TRUE and FALSE.

●​ Syntax: =IF(logical_test, value_if_true, value_if_false)


●​ Example: =IF(A1>10, "High", "Low") returns "High" if A1 > 10, otherwise
"Low".

VLOOKUP

Searches for a value in the first column of a range and returns a value in the same row from
another column.

●​ Syntax: =VLOOKUP(lookup_value, table_array, col_index_num,


[range_lookup])
●​ Example: =VLOOKUP(101, A1:C10, 2, FALSE).

Example Scenario
Suppose you have the following table:

A B C

100 Apple $1.00

101 Banana $0.50

102 Cherry $2.00

The formula =VLOOKUP(101, A1:C10, 2, FALSE) works as follows:

1.​ Search for 101 in column A (first column):​


Excel finds the value 101 in the second row.
2.​ Go to the second column of the same row:​
The second column (B) in that row contains Banana.
3.​ Return the result:​
The formula outputs Banana.

In our case we can use this formula for finding a risk score for a specific PM from the first
column.

VLOOKUP always searches for a value in the first column and retrieves data from a column to
the right.

TRIM

Removes extra spaces from text.

●​ Syntax: =TRIM(text)
●​ Example: =TRIM(" Hello ") returns "Hello".
NOW

Returns the current date and time.

●​ Syntax: =NOW()

4. Creating Your Own Formula


Steps to Create a Formula

1.​ Select the cell where you want the result to appear.
2.​ Start with an equals sign (=).
3.​ Enter your formula using cell references and operators.
○​ Example: =A1+B1*C1 calculates A1 + (B1 * C1).
4.​ Press Enter.

When to Use Parentheses (())

Parentheses control the order of operations. Excel follows the BODMAS rule (Brackets,
Orders, Division/Multiplication, Addition/Subtraction).

●​ Example: =A1+B1*C1 calculates multiplication first.


●​ To prioritize addition: =(A1+B1)*C1.

Using Commas and Semicolons

●​ Comma (,): Separates arguments in most functions (e.g., =SUM(A1, B1, C1)).
●​ Semicolon (;): Used in certain regional settings.
○​ Example: =SUM(A1; B1; C1).

Double Quotes

Empty String:

●​ An empty string ("") is used to represent a blank cell or an empty value in a formula.
It can be used to return nothing as a result, or as a condition in functions.
●​ Example: =IF(A1 > 10, "Yes", "") – This formula returns "Yes" if the value in
cell A1 is greater than 10, and an empty string (blank) if it is not.

Text Values:

●​ When you want to input text into a formula, it must be enclosed in double quotes.
●​ Example: =CONCATENATE("Hello, ", A1) – This formula combines the word
"Hello, " with the value in cell A1.
Representing Quotation Marks:

●​ To include a double quote within a string of text in Excel, you need to use two double
quotes ("").
●​ Example: ="She said, ""Hello!""" – This formula will display the text She
said, "Hello!".

Conditional Formulas:

●​ In formulas like IF or COUNTIF, "" is used to check or return empty values.


●​ Example: =IF(A1 = "", "No value", "Has value") – This checks if A1 is
empty and returns "No value" if true.

5. Tips for Working with Excel Formulas


Error Checking

●​ #DIV/0!: Division by zero error.


●​ #N/A: Value not available.
●​ #VALUE!: Incorrect argument type.

AutoFill for Formulas

1.​ Enter a formula in one cell.


2.​ Drag the fill handle (small square at the bottom-right corner of the cell) to apply the
formula to adjacent cells.

6. Practice Exercises
Exercise 1: Basic Calculations

1.​ Open a blank Excel sheet.


2.​ Enter numbers in cells A1 to A5.
3.​ Use =SUM(A1:A5) in cell A6 to calculate the total.

Exercise 2: Logical Test

1.​ Enter the following values:


○​ A1: 85
○​ B1: 50
2.​ In C1, enter =IF(A1>B1, "Pass", "Fail").
Conclusion
Excel formulas are essential for efficient data management and analysis. By mastering basic
and advanced functions, you can automate calculations, analyze data, and improve
productivity. Keep practicing and experimenting to unlock Excel’s full potential!

You might also like