Introduction to Excel
Microsoft Excel is a powerful spreadsheet application used for data entry, analysis, visualization, and automation using formulas and functions.
Excel combines a grid-based interface with advanced calculation engines, enabling data modeling, reporting, and automation at scale.
Excel Basics
Workbook & Worksheet
A workbook is an Excel file (.xlsx) and worksheets are individual tabs inside it.
Cell & Data Entry
Cells store data like text, numbers, dates. You can type directly or use formulas.
Use structured references and named ranges for clarity in larger models.
Basic Operations
Addition: =A1 + B1
Subtraction: =A2 - B2
Multiplication: =A3 * B3
Division: =A4 / B4
Excel Functions
Mathematical Functions
=SUM(A1:A5) : Sum values
=AVERAGE(A1:A5) : Average
=MIN(A1:A5) : Minimum
=MAX(A1:A5) : Maximum
=ROUND(A1,2) : Round to 2 decimal
Text Functions
=CONCAT(A1,B1) : Combine text
=LEFT(A1,5) : Left 5 characters
=RIGHT(A1,3) : Right 3 characters
=UPPER(A1) : Uppercase
=LOWER(A1) : Lowercase
Logical Functions
=IF(A1>50,"Pass","Fail")
=AND(A1>50,B1<100)
=OR(A1>50,B1<100)
=NOT(A1>50)
Lookup Functions
=VLOOKUP(1001,A2:C10,3,FALSE)
=HLOOKUP("Product",A1:Z1,5,FALSE)
=INDEX(A2:C10,2,3)
=MATCH(50,A2:A10,0)
Use XLOOKUP (or INDEX/MATCH) for modern, flexible lookups.
Charts & Data Visualization
Visualize your data using different charts.
- Column & Bar Charts
- Line Charts
- Pie Charts
- Scatter Plots
- Combo Charts
Choose chart types based on data: line for trends, bar for comparisons, scatter for relationships.
Example: Creating a Bar Chart
Select your data > Insert > Chart > Bar Chart
Data Analysis Tools
Sorting & Filtering
Sort data ascending/descending, filter using criteria.
Pivot Tables
Summarize data dynamically using rows, columns, and values.
Conditional Formatting
Highlight cells based on conditions.
Highlight > Greater than 50
Color scales
Icon sets
Combine PivotTables with slicers and timelines for interactive dashboards.
Advanced Excel Techniques
Advanced Excel focuses on modeling, automation, and scalable reporting. This includes modern formulas, structured data modeling, and repeatable transformations.
Advanced Formulas
=SUMIF(A1:A10,">50",B1:B10)
=COUNTIF(A1:A10,"Pass")
=TEXT(A1,"dd-mmm-yyyy")
=PMT(5%/12,60,10000) : Loan calculation
Modern formulas like FILTER, UNIQUE, and SORT enable dynamic, spill‑based models.
=FILTER(A2:C100, C2:C100="Active")
=UNIQUE(B2:B100)
=SORT(A2:D100, 4, -1)
LET & LAMBDA (Modern Excel)
LET reduces repeated calculations and makes formulas readable. LAMBDA lets you create custom reusable functions.
=LET(x, A1:A10, avg, AVERAGE(x), SUM(x) - avg)
=LAMBDA(n, n * n)(5) : returns 25
PivotCharts
PivotCharts visualize PivotTable summaries and update automatically when filters change.
Insert > PivotChart > Choose chart type
Use slicers to filter and refresh the chart interactively
Macros & VBA
Automate repetitive tasks using VBA scripts.
Sub HighlightCells()
For Each cell In Selection
If cell.Value > 50 Then
cell.Interior.Color = vbYellow
End If
Next cell
End Sub
For reliability, modularize code and avoid selecting ranges unnecessarily.
Sub FormatReport()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Report")
ws.Range("A1:D1").Font.Bold = True
ws.Columns("A:D").AutoFit
End Sub
Data Validation
Restrict cell input with dropdowns, numbers, or dates.
Use named ranges for dropdowns and add error messages for better data quality.
Power Query (Get & Transform)
Power Query cleans and combines data with a repeatable set of steps (no formulas required).
Data > Get Data > From File/Database
Transform: remove columns, split text, change types
Close & Load to refresh in one click
Power Query is based on the M language. Use it to merge, append, and clean data at scale.
let
Source = Csv.Document(File.Contents("sales.csv"), [Delimiter=",", Encoding=65001]),
Promoted = Table.PromoteHeaders(Source),
Filtered = Table.SelectRows(Promoted, each [Status] = "Active")
in
Filtered
Power Pivot & DAX
Power Pivot builds data models with relationships. DAX adds measures for dynamic calculations.
// Example DAX measures
Total Sales := SUM(Sales[Amount])
Average Order := AVERAGE(Sales[Amount])
Sales YTD := TOTALYTD([Total Sales], 'Date'[Date])
DAX Best Practices: prefer measures over calculated columns, avoid complex row context, and use clear naming for readability.
// Use measures for performance
Profit := [Total Sales] - SUM(Sales[Cost])
// Keep measures readable
Avg Sales Per Order :=
DIVIDE([Total Sales], [Order Count])
Use time intelligence functions like SAMEPERIODLASTYEAR and DATESMTD for period comparisons.
Sales LY := CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Sales MTD := CALCULATE([Total Sales], DATESMTD('Date'[Date]))
Simple Data Model Diagram
DimDate DimCustomer DimProduct
| | |
+----------+-------------+
|
FactSales
Star Schema Example (Keys)
DimDate(DateKey) DimCustomer(CustomerKey) DimProduct(ProductKey)
| | |
+-----------+-------------+-------------+-------------+
FactSales
(DateKey, CustomerKey, ProductKey, SalesAmount, Cost)
Bridge Table Example (Many-to-Many)
DimStudent(StudentKey) BridgeStudentCourse(StudentKey, CourseKey) DimCourse(CourseKey)
| | |
+-------------------------+-------------------------+
FactEnrollments
Relationships are typically one‑to‑many from each dimension to the fact table. Filters flow from dimensions to facts to keep aggregation accurate.
Many‑to‑many relationships require a bridge table, and bidirectional filters should be used sparingly to avoid ambiguous results.
Advanced users often combine Power Query, Power Pivot, and DAX for robust data models.