Sign In
Get Started

The Ultimate Guide to Excel Formulas for Beginners

by | Excel

Excel Content Article

Table of Contents

Excel Basics: Practical Implementation

1. Navigating Excel

  • Open Excel
  • Open a new workbook

2. Basic Operations

2.1 Insert Data

  • Enter data into the cells.

Example:

   A        B
1 Name    Score
2 John     85
3 Alice    90
4 Bob      78

3. Basic Formulas and Functions

3.1 Sum

  • Insert in cell B5:
=SUM(B2:B4)

3.2 Average

  • Insert in cell B6:
=AVERAGE(B2:B4)

3.3 Max

  • Insert in cell B7:
=MAX(B2:B4)

3.4 Min

  • Insert in cell B8:
=MIN(B2:B4)

4. Cell Referencing

4.1 Relative Referencing

  • Copy formula =SUM(B2:B4) from B5 to C5 (will adjust range to C2:C4).

4.2 Absolute Referencing

  • Insert in cell C2 to fix cell B2 in formula:
=$B$2 + 10

5. Conditional Formatting

  • Highlight cells in column B where values are greater than 80:
    • Select column B
    • Conditional Formatting > Highlight Cell Rules > Greater Than…
    • Set condition 80 and select formatting style

6. Data Sorting and Filtering

6.1 Sorting

  • Sort scores in column B in descending order:
    • Select range A1:B4
    • Data > Sort > Sort by: Score > Order: Largest to Smallest

6.2 Filtering

  • Filter Names and Scores:
    • Select range A1:B4
    • Data > Filter

7. Creating a Basic Chart

  • Create a Bar Chart for Scores:
    • Select range A1:B4
    • Insert > Chart > Bar Chart

8. Basic Data Validation

  • Set a restriction on cell B2 to only allow values between 0 and 100:
    • Select cell B2
    • Data > Data Validation
    • Allow: Whole number
    • Data: between
    • Minimum: 0
    • Maximum: 100

This setup will help you get started with basic Excel operations and formulas to enhance data analysis and automate calculations.

Sales Now On Advertisement

Understanding Cell References

Types of Cell References

  1. Relative References
  2. Absolute References
  3. Mixed References

Practical Implementation

1. Relative References

In a worksheet, use relative references when filling down or across.

Example:

  • Enter =A1+1 in B1
  • Drag the fill handle down.

2. Absolute References

Use $ to lock a specific cell, column, or row reference.

Example: Lock cell B1 totally:

  • =$B$1

Lock cell B1's row:

Power BI Tools Advertisement
  • =B$1

Lock cell B1's column:

  • =$B1

3. Mixed References

Combination of absolute and relative references.

Example:

  • Enter =$A1+B$1 in C1
  • Drag the fill handle down and/or across.

Practical Usage

Scenario: Calculate total sales per item with additional tax.

A B C D
Item Price Quantity Tax
Pen 1.50 10 0.10
Pencil 0.80 20

Steps:

  1. In D2, input =$B$2 * $C2
  2. Drag D2 downwards to apply to other items.

Scenario: Multiplying a column of numbers by a fixed percentage in another cell.

A B C
Number % Result
100 0.15
200
300

Steps:

  1. In C1, input =A1*$B$1
  2. Drag C1 downwards to apply to other numbers.

Shortcut Keys

Toggle Reference States Effectively:

  1. Select or Enter the cell reference.

    • Toggle between states by pressing F4:
      • A1 (Relative)
      • $A$1 (Absolute)
      • A$1 or $A1 (Mixed)

Conclusion

Mastering cell references enhances your efficiency in Excel and ensures accurate and dynamic data manipulation. Practice these references in different scenarios to improve your Excel skills.

Basic Arithmetic Formulas in Excel

Addition

Formula

=A1 + B1

Example

=5 + 3

Result: 8

Subtraction

Formula

=A1 - B1

Example

=5 - 3

Result: 2

Multiplication

Formula

=A1 * B1

Example

=5 * 3

Result: 15

Division

Formula

=A1 / B1

Example

=15 / 3

Result: 5

Exponentiation

Formula

=A1 ^ B1

Example

=5 ^ 2

Result: 25

SUM Function

Formula

=SUM(A1:A10)

Example

=SUM(1, 2, 3, 4, 5)

Result: 15

AVERAGE Function

Formula

=AVERAGE(A1:A10)

Example

=AVERAGE(1, 2, 3, 4, 5)

Result: 3

COUNT Function

Formula

=COUNT(A1:A10)

Example

=COUNT(1, 2, 3, "", 4)

Result: 4

MAX Function

Formula

=MAX(A1:A10)

Example

=MAX(1, 2, 3, 4, 5)

Result: 5

MIN Function

Formula

=MIN(A1:A10)

Example

=MIN(1, 2, 3, 4, 5)

Result: 1

SUM and AVERAGE Functions Practical Implementation

Part 4: Using the SUM and AVERAGE Functions

SUM Function

To calculate the sum of values in a range of cells (e.g., A1 to A10):

=SUM(A1:A10)

AVERAGE Function

To calculate the average of values in a range of cells (e.g., B1 to B10):

=AVERAGE(B1:B10)

Practical Example

Assuming you have sales data in column C from row 1 to row 12:

Total Sales

To get the total sales:

=SUM(C1:C12)

Average Sales

To get the average sales:

Data Mentor Advertisement 
=AVERAGE(C1:C12)

Combined Example

Assuming you want to calculate the total and average of different columns. For instance, calculate the total and average of columns C, D, and E in row 13:

C13: =SUM(C1:C12)
D13: =SUM(D1:D12)
E13: =SUM(E1:E12)

C14: =AVERAGE(C1:C12)
D14: =AVERAGE(D1:D12)
E14: =AVERAGE(E1:E12)

Applying Conditional Formatting

To highlight cells in the Average row if they are below a certain threshold, use:

  1. Select the average row (e.g., C14:E14).
  2. Go to Conditional Formatting.
  3. Choose Highlight Cells Rules, then Less Than.
  4. Enter the threshold value and select the desired formatting.

Using the Functions in a Table

If your data is organized in a table:

  1. Insert a new row below the last data row.
  2. Use the SUM and AVERAGE functions referencing the table columns:
=SUM(Table1[ColumnName])

=AVERAGE(Table1[ColumnName])

Summary

  • Use =SUM(range) to calculate the total of a range.
  • Use =AVERAGE(range) to calculate the average of a range.
  • Combine these functions with conditional formatting and structured references for enhanced data analysis.

Proceed to implement these formulas in your Excel sheets for practical data analysis tasks.

Conditional Formulas with IF Statements in Excel

1. Basic IF Statement

=IF(A1 > 10, "High", "Low")
  • Checks if the value in cell A1 is greater than 10.
  • Returns "High" if true, "Low" if false.

2. Nested IF Statement

=IF(A1 > 10, "High", IF(A1 > 5, "Medium", "Low"))
  • Checks if the value in cell A1 is greater than 10.
  • If true, returns "High".
  • If false, it checks if A1 is greater than 5.
  • Returns "Medium" if true, "Low" if false.

3. IF with AND

=IF(AND(A1 > 10, B1 < 5), "Pass", "Fail")
  • Checks if the value in A1 is greater than 10 and the value in B1 is less than 5.
  • Returns "Pass" if both conditions are true, "Fail" otherwise.

4. IF with OR

=IF(OR(A1 > 10, B1 < 5), "Accept", "Reject")
  • Checks if the value in A1 is greater than 10 or the value in B1 is less than 5.
  • Returns "Accept" if at least one condition is true, "Reject" otherwise.

5. IF with ISBLANK

=IF(ISBLANK(A1), "Empty", "Filled")
  • Checks if A1 is blank.
  • Returns "Empty" if true, "Filled" if false.

6. IF with Text Comparison

=IF(A1 = "Yes", 1, 0)
  • Checks if A1 contains the text "Yes".
  • Returns 1 if true, 0 if false.

7. IF with Numerical Range

=IF(AND(A1 >= 5, A1 <= 15), "In Range", "Out of Range")
  • Checks if A1 is between 5 and 15 inclusive.
  • Returns "In Range" if true, "Out of Range" if false.

8. Multi-Condition Nested IF

=IF(A1 > 90, "A", IF(A1 > 80, "B", IF(A1 > 70, "C", IF(A1 > 60, "D", "F"))))
  • Checks multiple conditions to assign a grade based on the value in A1.
  • Returns "A" for >90, "B" for >80, "C" for >70, "D" for >60, and "F" otherwise.

9. IF and COUNTIF

=IF(COUNTIF(A1:A10, ">10") > 5, "Majority High", "Majority Low")
  • Counts how many cells in the range A1:A10 have values greater than 10.
  • Returns "Majority High" if more than 5 cells meet the condition, "Majority Low" otherwise.

10. IFERROR for Error Handling

=IFERROR(A1/B1, "Error")
  • Attempts to divide A1 by B1.
  • Returns the result if no error.
  • If an error occurs (e.g., division by zero), returns "Error".

Working with Text Functions

Text Function Examples

CONCATENATE (or CONCAT)

Concatenates multiple strings.

=CONCATENATE(A1, " ", B1)

LEFT

Extracts a given number of characters from the left side of a text string.

=LEFT(A1, 5)

RIGHT

Extracts a given number of characters from the right side of a text string.

=RIGHT(A1, 3)

MID

Extracts text from the middle of a text string, given a starting position and length.

=MID(A1, 2, 3)

LEN

Returns the number of characters in a text string.

=LEN(A1)

FIND

Finds a substring within a text string and returns the position of the first character.

=FIND("text", A1)

SEARCH

Similar to FIND but not case-sensitive.

=SEARCH("text", A1)

UPPER

Converts all characters in a text string to uppercase.

=UPPER(A1)

LOWER

Converts all characters in a text string to lowercase.

=LOWER(A1)

PROPER

Capitalizes the first letter of each word in a text string.

=PROPER(A1)

TRIM

Removes all spaces from text except for single spaces between words.

=TRIM(A1)

SUBSTITUTE

Replaces occurrences of a specified text string with another text string.

=SUBSTITUTE(A1, "old_text", "new_text")

REPT

Repeats text a given number of times.

=REPT(A1, 3)

VALUE

Converts text that appears in a recognized format (like a number) into a numeric format.

=VALUE(A1)

TEXT

Formats a number and converts it to text.

=TEXT(A1, "0.00")

LEFT

Extracts a substring from the leftmost characters of a text value.

EDNA AI Advertisement 
=LEFT(A1, [num_chars])

This content can enhance your Excel capabilities, making your data analysis more efficient and effective.

Exploring Date and Time Functions in Excel

Current Date and Time

=NOW()           # Returns the current date and time
=TODAY()         # Returns the current date

Extracting Parts of a Date

=YEAR(A1)        # Extracts the year from date in cell A1
=MONTH(A1)       # Extracts the month from date in cell A1
=DAY(A1)         # Extracts the day from date in cell A1
=HOUR(A1)        # Extracts the hour from datetime in cell A1
=MINUTE(A1)      # Extracts the minute from datetime in cell A1
=SECOND(A1)      # Extracts the second from datetime in cell A1

Date and Time Arithmetic

=A1 + 7          # Adds 7 days to the date in cell A1
=A1 - A2         # Subtracts date in A2 from date in A1 to get difference in days
=A3 + TIME(2, 30, 0) # Adds 2 hours and 30 minutes to the datetime in cell A3

Formatting Dates and Times

=TEXT(A1, "dd-mm-yyyy")       # Formats date in A1 as day-month-year
=TEXT(A1, "hh:mm AM/PM")      # Formats time in A1 to 12-hour clock
=TEXT(A1, "yyyy-mm-dd hh:mm") # Formats datetime in A1 to ISO 8601 style

Weekday and Week Number

=WEEKDAY(A1, 1)   # Returns the day of the week for date in A1 (1=Sunday, 7=Saturday)
=WEEKNUM(A1)      # Returns the week number for date in A1

Adding/Subtracting Specific Time Units

=EDATE(A1, 3)        # Adds three months to the date in cell A1
=EOMONTH(A1, 0)      # End of the month for date in cell A1
=WORKDAY(A1, 10)     # Returns date 10 workdays after the date in cell A1
=NETWORKDAYS(A1, A2) # Number of workdays between dates in A1 and A2

Creating a Date or Time

=DATE(2023, 10, 5)              # Creates date 5th October 2023
=TIME(14, 30, 0)                # Creates a time value 14:30:00
=DATEVALUE("2023-10-05")        # Converts a date string to a date value
=TIMEVALUE("14:30:00")          # Converts a time string to a time value

Combining Date and Time

=A1 + B1                       # Combines date in A1 and time in B1
=DATE(YEAR(A1), MONTH(A1), DAY(A1)) + TIME(HOUR(B1), MINUTE(B1), SECOND(B1)) # Custom combination

Conditional Date and Time

=IF(A1 > TODAY(), "Future", "Past or Present") # Checks if date in A1 is in the future
=IF(MOD(HOUR(A1), 2) = 0, "Even Hour", "Odd Hour") # Checks if the hour in datetime A1 is even or odd

Lookup Functions in Excel: Practical Implementation

VLOOKUP

Example

Suppose you have a table of data representing employee IDs and their respective salaries. You want to find the salary of a specific employee using their ID.

Data Table:

Employee ID Salary
101 $50,000
102 $55,000
103 $60,000

Implementation

  1. Select the cell where you want the result to appear: Let's say it's D2.
  2. Enter the following formula:
=VLOOKUP(B2, $A$2:$B$4, 2, FALSE)
  • B2: The cell containing the Employee ID you want to search for.
  • $A$2:$B$4: The range of the data table.
  • 2: The column number in the table from which to retrieve the value (Salary).
  • FALSE: Ensures that an exact match is found.

HLOOKUP

Example

Consider you have a table of data representing sales made by different representatives in different months. You want to find the sales made in a specific month.

Data Table:

Month Jan Feb Mar
Sales Rep A B C
John 200 300 400
Jane 250 350 450

Implementation

  1. Select the cell where you want the result to appear: Let's say it's D6.
  2. Enter the following formula:
=HLOOKUP("Feb", $B$1:$D$4, 3, FALSE)
  • "Feb": The month you want to search for.
  • $B$1:$D$4: The range of the data table.
  • 3: The row number in the table from which to retrieve the value.
  • FALSE: Ensures that an exact match is found.

Summary

  • Use VLOOKUP to find a value in a vertical table.
  • Use HLOOKUP to find a value in a horizontal table.
  • Always use absolute references ($) for the table range to avoid errors when copying formulas.
  • Ensure the match type (TRUE for approximate, FALSE for exact) is specified correctly.

Exploring Logical Functions in Excel

Part #9 of the Project: Learn how to master Excel formulas to enhance your data analysis skills, automate calculations, and make informed decisions.

This section provides practical implementations for utilizing logical functions in Excel, which can help in enhancing data analysis and automating various calculations.

1. IF Function

Purpose: Perform a conditional test and returns one value for TRUE result and another for FALSE result.

Syntax: IF(logical_test, value_if_true, value_if_false)

Example:

=IF(A1 > 10, "Greater than 10", "10 or less")

2. AND Function

Purpose: Checks whether all arguments are TRUE, and returns TRUE if so, otherwise FALSE.

Syntax: AND(logical1, logical2, ...)

Example:

=AND(A1 > 10, A2 < 20)

3. OR Function

Purpose: Checks if any of the arguments are TRUE, and returns TRUE if so, otherwise FALSE.

Syntax: OR(logical1, logical2, ...)

Example:

=OR(A1 > 10, A2 < 5)

4. NOT Function

Purpose: Reverses the logic of its argument, returning FALSE for a TRUE input, and TRUE for a FALSE input.

Syntax: NOT(logical)

Example:

=NOT(A1 > 10)

5. IFERROR Function

Purpose: Returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.

Syntax: IFERROR(value, value_if_error)

Example:

=IFERROR(A1/B1, "Error in calculation")

6. Combining Logical Functions

Performing Nested Logical Tests:

=IF(AND(A1 > 10, OR(B1 < 5, C1 = "Yes")), "Condition met", "Condition not met")

7. IFS Function

Purpose: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

Syntax: IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Example:

=IFS(A1 = "Apple", 1, A1 = "Banana", 2, A1 = "Cherry", 3)

Conclusion

Use these logical functions to enhance your Excel formulas, automating decisions and making your spreadsheets more sophisticated. These practical examples can be directly applied to real-life scenarios to provide immediate benefits.

#10: Data Validation and Conditional Formatting in Excel

Data Validation

Implementing Data Validation:

  1. Select the cells where you want to apply data validation.
  2. Navigate to the Data tab on the Ribbon.
  3. Click on the Data Validation button in the Data Tools group.
  4. In the Data Validation dialog box:
    • Select "List" from the Allow dropdown.
    • Enter the valid entries separated by commas or refer to a range of cells using a formula like =A1:A10.

Example:

=INDIRECT("ValidOptions")

Where ValidOptions is a named range containing your valid entries.

Conditional Formatting

Implementing Conditional Formatting:

  1. Select the cells you want to format conditionally.
  2. Go to the Home tab on the Ribbon.
  3. Click on Conditional Formatting in the Styles group.
  4. Choose New Rule from the dropdown menu.
  5. In the New Formatting Rule dialog box:
    • Select Use a formula to determine which cells to format.
    • Enter the formula that defines the condition.

Example:

=AND(A1>100, A1<200)
  1. Click on Format, choose your desired formatting, and press OK.

Example Conditional Formatting Rules:

  • Highlight cells greater than a value: 
    =$A1>100
  • Highlight cells where text contains specific word: 
    =SEARCH("Urgent", $A1)

Final Integration:

  1. Apply Data Validation to restrict entries to a list of predetermined values.
  2. Set Conditional Formatting to dynamically highlight cells based on the specified conditions.

This allows you to automate and enhance data integrity and presentation in your Excel sheet effectively.

Unit 11: Introduction to Pivot Tables

Step-by-Step Implementation

Creating a Pivot Table

  1. Select Your Data Range:

    • Make sure your data is structured in a tabular format with headers.

  2. Insert Pivot Table:

    • Go to the Insert tab.
    • Click on PivotTable.
    • Ensure the correct data range is selected.
    • Choose where to place the PivotTable (New Worksheet or Existing Worksheet).

Configuring the Pivot Table

  1. Drag Fields to Areas:
    • Rows Area: Drag the fields you want to appear as rows.
    • Columns Area: Drag the fields you want to appear as columns.
    • Values Area: Drag the fields you want to aggregate.
    • Filters Area: (Optional) Drag fields you want to use as filters.

Example

Suppose you have the following data in your Excel sheet:

Date Product Sales
01/01/2023 A 100
01/01/2023 B 150
02/01/2023 A 200
02/01/2023 B 250

Creating a Pivot Table

  1. Select Data Range:

    • Highlight the data range A1:C5.

  2. Insert Pivot Table:

    • Click Insert > PivotTable.

Configuring the Pivot Table

  1. Drag Fields:
    • Rows Area: Drag Date.
    • Columns Area: Drag Product.
    • Values Area: Drag Sales.

Final Pivot Table Layout:

Date A B
01/01/2023 100 150
02/01/2023 200 250

Formatting

  1. Value Field Settings:
    • Click on any value in the PivotTable.
    • Go to Value Field Settings.
    • Choose Sum, Average, Count, etc., as needed.
    • Click OK.

End of Unit 11

Apply these steps to practice creating and manipulating Pivot Tables to enhance your data analysis skills in Excel.

Creating Charts and Visualizing Data in Excel

Here's a practical guide for creating charts and visualizing data in Excel. Follow these steps:

1. Prepare Your Data

Ensure your data is organized in a table format with headers.

2. Select Your Data Range

Highlight the data range you want to visualize.

3. Insert a Chart

  1. Go to the Insert tab on the Ribbon.

  2. Choose from the chart options available (e.g., Column, Line, Pie).

    Insert -> (Chart Type) -> (Specific Chart)

4. Customize Chart Elements

Modifying Chart Title

  • Click on the Chart Title and edit it to reflect the data being shown.

Adding Labels

  • Click on the chart and then on Chart Elements (plus icon on the side) to add axes, data labels, or a legend.

Formatting Axes

  • Right-click on the axis and select Format Axis to adjust the scale, interval, or other properties.

5. Apply Data Labels

  1. Click on the chart.
  2. Navigate to the Chart Elements.
  3. Check Data Labels for additional data points visibility.

6. Customize Chart Styles and Colors

  1. Click on the chart.
  2. Use the Chart Styles and Chart Colors options in the Design tab to format the chart as needed.

Example

Suppose your data range is in cells A1:B6:

   A           B
1  Category   Values
2  A           10
3  B           20
4  C           30
5  D           40
6  E           50

Steps to Create a Column Chart

  1. Highlight cells A1:B6.
  2. Go to Insert -> Column or Bar Chart -> Clustered Column.

This will create a basic column chart. Customize as needed.

Remember, these steps should help you practically create and customize charts in your Excel worksheets for better data visualization. Adjust element names and selections based on your specific version of Excel.

Related Posts