Data analysis moves fast. New functions in Excel and evolving SQL features mean smarter, faster workflows; if you know where to look. We’ve put together 20 practical, well-explained hacks (10 Excel, 10 SQL) that every data analyst should have in their toolbox in 2025.
Want to master these and more? Join the 1Goal Analytics Data Analytics & Data Science course at 1goalanalytic.com.
Excel hacks
- Leverage Dynamic Arrays (spill behavior)
Use functions like FILTER, SORT, UNIQUE, and SEQUENCE to produce auto-spilling arrays that update downstream cells automatically — ideal for dashboards and lists without manual ranges. - Replace VLOOKUP with XLOOKUP
XLOOKUP is more flexible (left-lookup, exact/approximate, default return if not found) and works cleanly with dynamic arrays. Use it to simplify nested INDEX/MATCH combos. - Make formulas readable with LET
LET stores intermediate calculations as names inside a formula. This reduces repeated work, improves performance, and makes complex formulas easier to debug. - Create reusable functions with LAMBDA
Turn a complex formula into a named custom function with LAMBDA, then call it like any built-in function across your workbook — excellent for standardizing business logic. - Power Query = ETL inside Excel
Use Power Query to import, clean, merge and reshape data (CSV, databases, APIs) before analysis. Save queries and refresh them — a must for repeatable data wrangling. - Power Pivot & Data Model for big data
Load tables into the Data Model and use DAX measures to calculate on millions of rows far faster than worksheet formulas. - Use structured tables and names
Convert ranges to Tables (Ctrl+T) for stable references, automatic expansion, and easier Power Query/Power Pivot flows. - TEXTBEFORE / TEXTAFTER for parsing
Newer text functions let you extract values from strings without fragile FIND/MID nesting — very handy for parsing IDs, URLs, or combined fields. - Audit with Formula Evaluation & Trace Precedents
Use “Evaluate Formula”, “Trace Precedents/Dependents” and ERROR.TYPE to debug and make spreadsheets auditable — crucial when handing off work. - Profile data with QUICK ANALYSIS & PivotTables
Quick Analysis offers instant charts and pivot suggestions. PivotTables remain the fastest way to summarize and explore large tables interactively.
SQL hacks
- Use Common Table Expressions (CTEs) for clarity
Break complex queries into named steps with WITH. CTEs improve readability and make iterative logic (recursive CTEs) easy for hierarchical data. - Master window (analytic) functions
ROW_NUMBER(), RANK(), LEAD()/LAG(), and SUM() OVER (PARTITION BY …) let you compute running totals, gaps, percentiles, and row-level analytics without self-joins. Essential for modern analytics. - Prefer EXPLAIN + indexes for performance
Before tuning, run EXPLAIN/EXPLAIN ANALYZE to see the query plan. Add selective indexes (and avoid over-indexing) based on real plans. - Upsert patterns: MERGE or ON CONFLICT
Use MERGE (SQL standard) or INSERT … ON CONFLICT (Postgres) to do atomic insert/update operations — useful for incremental loads. - JSON support: store or query semi-structured data
Modern DBs feature JSON functions and JSON_TABLE-like capabilities. Use JSON when source schemas vary, but extract and normalize into columns for heavy analytics. - Use LATERAL joins for per-row expansions
LATERAL lets you run a subquery for each row of the outer query — ideal when expanding arrays or performing correlated calculations (e.g., exploding JSON arrays). - Avoid SELECT * ; project only needed columns
Returning only required columns reduces I/O, network transfer, and memory pressure—especially important on wide tables. - Window + CTE combo for advanced ranking & deduping
Use a CTE with ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) to pick the latest record per key and delete duplicates in a clean, transactional way. - Parameterize queries, use prepared statements
Protect against SQL injection and improve repeated query performance by preparing and parameterizing queries in your client or DB. - Profile with sample data and explain different plans
Test at scale: run queries on realistic sample datasets and compare plans. Small datasets can hide costly full-table scans and join blowups.
Hands-on practice makes these hacks stick. If you want guided, project-based training that teaches these Excel and SQL techniques (and how to apply them to real business problems), check out 1Goal Analytics’ Data Analytics & Data Science course. In a couple of weeks, you’d be able to turn these hacks into measurable impact.
Learn more & register at 1goalanalytic.com/courses
