Scribd
Upload
53 views2 pages

Excel Learning Guide

The document is a comprehensive learning guide for Microsoft Excel, covering formulas from beginner to advanced levels. It includes essential functions for calculations, text manipulation, data analysis, and dynamic array formulas. Additionally, it touches on expert-level techniques such as referencing dynamic ranges and using Power Query and Power Pivot for advanced analysis.

Uploaded by

muhammedahmed2238
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
53 views2 pages

Excel Learning Guide

The document is a comprehensive learning guide for Microsoft Excel, covering formulas from beginner to advanced levels. It includes essential functions for calculations, text manipulation, data analysis, and dynamic array formulas. Additionally, it touches on expert-level techniques such as referencing dynamic ranges and using Power Query and Power Pivot for advanced analysis.

Uploaded by

muhammedahmed2238
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Microsoft Excel Learning Guide

From Basic to Advanced Formulas

📘 Beginner Formulas
 **=SUM(A1:A10)** → Adds up numbers
 **=AVERAGE(A1:A10)** → Finds the mean value
 **=MIN(A1:A10) / =MAX(A1:A10)** → Smallest & largest values
 **=COUNT(A1:A10)** → Counts numbers
 **=COUNTA(A1:A10)** → Counts all non-empty cells
 **=IF(A1>50,"Pass","Fail")** → Basic logical test
 **=TODAY() / =NOW()** → Insert current date & time

📗 Intermediate Formulas
 **=TRIM(A1)** → Removes extra spaces
 **=PROPER(A1) / =UPPER(A1) / =LOWER(A1)** → Text formatting
 **=CONCATENATE(A1," ",B1) or =A1&" "&B1** → Combine text
 **=LEFT(A1,4) / =RIGHT(A1,3) / =MID(A1,2,5)** → Extract text
 **=LEN(A1)** → Count characters in a cell
 **=SEARCH("apple",A1) / =FIND("apple",A1)** → Find text inside a cell
 **=ROUND(A1,2) / =ROUNDUP() / =ROUNDDOWN()** → Rounding numbers
 **=IFERROR(A1/B1,"Error")** → Handle errors safely
 **=AND(A1>0,B1<10) / =OR(A1>0,B1<10)** → Complex logic

📙 Advanced Formulas
 Lookup & Reference

 **=VLOOKUP(lookup_value, table_array, col_index, FALSE)** → Search by column


 **=HLOOKUP(lookup_value, table_array, row_index, FALSE)** → Search by row
 **=INDEX(range, row, column)** → Pull value from a position
 **=MATCH(lookup_value, range, 0)** → Find position of a value
 **=XLOOKUP(lookup_value, lookup_array, return_array)** → Modern replacement
for VLOOKUP

 Data Analysis

 **=SUMIF(range, criteria, sum_range)** → Conditional sum


 **=COUNTIF(range, criteria)** → Conditional count
 **=SUMPRODUCT(array1,array2)** → Multiply & sum arrays
 **=UNIQUE(range)** → List unique values (Excel 365+)
 **=FILTER(range, condition)** → Filter data dynamically
 **=SORT(range, column, order)** → Sort data dynamically

 Array & Dynamic Formulas (Excel 365/2021)

 **=SEQUENCE(rows, columns, start, step)** → Generate number series


 **=TEXTJOIN(", ", TRUE, A1:A5)** → Join text with a delimiter
 **=LET(name,value,calculation)** → Define variables in formulas
 **=LAMBDA(parameters, formula)** → Create custom functions

📕 Expert / Specialized
 **=INDIRECT("Sheet2!A1")** → Refer to dynamic ranges
 **=OFFSET(reference, rows, cols, height, width)** → Return a shifted range
 **=CELL("address",A1)** → Info about cell
 Array formulas (e.g., {=SUM(A1:A10*B1:B10)} in older Excel)
 Power Query & Power Pivot (not formulas, but essential for advanced analysis)

You might also like