The Ultimate Excel & Office Cheatsheet

-- Extract first name from full name
=LEFT(A2, FIND(" ", A2) - 1)

-- Extract last name from full name
=TRIM(MID(A2, FIND(" ", A2), LEN(A2)))

-- Age from date of birth
=DATEDIF(A2, TODAY(), "Y")

-- Running total (cumulative sum)
=SUM($B$2:B2)

-- Grade from score (nested IF)
=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))

-- Concatenate with line break
=A2 & CHAR(10) & B2
-- (enable Wrap Text on the cell)

-- Get domain from email address
=MID(A2, FIND("@", A2) + 1, LEN(A2) - FIND("@", A2))

-- Days remaining until deadline
=A2 - TODAY()

-- Percentage change vs previous month
=(B2 - A2) / ABS(A2)

=LET(
  tax_rate, 0.18,
  amount, A2 * (1 + tax_rate),
  discount, IF(amount > 1000, amount * 0.1, 0),
  amount - discount
)
-- LET: assign names to intermediate values

=LAMBDA(price, qty, price * qty)
-- Reusable formula; assign via Name Manager

=LAMBDA(radius, PI() * radius ^ 2)
-- Calculate circle area from radius

' Auto-format a selected range as a table
Sub FormatAsTable()
    Dim rng As Range
    Set rng = Selection
    rng.Borders.LineStyle = xlContinuous
    rng.Font.Name = "Calibri"
    rng.Font.Size = 11
    rng.EntireColumn.AutoFit
End Sub

' Loop through rows and highlight duplicates
Sub HighlightDuplicates()
    Dim cell As Range
    For Each cell In Selection
        If WorksheetFunction.CountIf(Selection, cell.Value) > 1 Then
            cell.Interior.Color = RGB(255, 200, 200)
        End If
    Next cell
End Sub

' Create a summary sheet from all sheets
Sub CreateSummary()
    Dim ws As Worksheet
    Sheets.Add Before:=Sheets(1)
    ActiveSheet.Name = "Summary"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = ws.Name
        End If
    Next ws
End Sub

-- Total Sales
Total Sales = SUM(Sales[Amount])

-- Sales Last Year (time intelligence)
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))

-- Year-over-Year Growth
YoY Growth = DIVIDE([Total Sales] - [Sales LY], [Sales LY])

-- Running Total
Running Total = CALCULATE(
    [Total Sales],
    FILTER(ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date]))
)

-- Distinct Count of Customers
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])