Who This Hub Is For
This hub targets mid-level to senior analysts who already use Excel for basic work but have not been shown what it can actually do for analyst-specific deliverables. The articles focus on three groups:
🏥 Healthcare IT Analysts
ICD-10 mapping, CDM lookups, Epic export analysis, UAT test management, charge lag tracking, and reconciliation workbooks.
📋 BA and QA Analysts
Test case tracking, defect dashboards, RTM sign-off packs, sprint reports, data migration reconciliation, and stakeholder reporting packs.
💰 Finance and Revenue Cycle
AR aging buckets, denial rate analysis by payer, charge capture summaries, budget vs actual, and payer comparison reports.
Four Skill Levels — Start Where You Are
Articles are tagged by level so you know what to read first and what to save for later.
Articles
Foundations
Foundations · Beginner
Absolute vs Relative Cell References: Why Your Formulas Break When You Copy Them
$A$1, A$1, $A1, A1 — what each does when you drag a formula and how to choose correctly the first time.
Foundations · Beginner
IF, AND, OR in Excel: Building Conditional Logic Without Losing Your Mind
Nested IF statements that actually work, AND/OR shortcuts, and IFERROR for graceful failure instead of #N/A errors.
Foundations · Beginner
Excel Tables: Why You Should Convert Every Dataset Before Touching It
Structured references, auto-expanding ranges, and why Tables make every formula, pivot table, and chart easier to build.
Analyst Core
Analyst Core · Intermediate
VLOOKUP vs XLOOKUP: When Each Works, When Both Fail, and Why INDEX/MATCH Still Matters
VLOOKUP's three failure modes, XLOOKUP's advantages, and the scenarios where INDEX/MATCH is still the right call.
Analyst Core · Intermediate
Pivot Tables for Analysts: Calculated Fields, Date Grouping, and Slicers
Beyond the default pivot — calculated fields for KPIs, date grouping for time series, and slicers that let stakeholders filter without touching the data.
Analyst Core · Intermediate
SUMIF, COUNTIF, SUMIFS: Conditional Aggregation Without a Pivot Table
When you need a single-cell answer instead of a pivot — SUMIFS for multiple conditions, wildcard matching, and cross-sheet references.
Healthcare IT · Intermediate
Excel for Healthcare IT: ICD-10 Mapping, CDM Lookups, and Charge Code Reference Tables
How to build and use ICD-10 lookup tables, CDM mapping files, and APR-DRG weight references using XLOOKUP and structured Excel tables.
BA Tools · Intermediate
Excel for UAT Management: Test Case Tracking, Defect Logs, and Sign-Off Dashboards
A full UAT tracking workbook — test status by module, defect log with severity, and a pivot-driven sign-off dashboard the clinical lead can read without IT help.
Conditional Formatting · Intermediate
Conditional Formatting for Test Status and Defect Dashboards
Formatting rules that make pass/fail/blocked status visible instantly, flag overdue items in red, and surface patterns that manual review misses.
Power User
Power Query · Advanced
Power Query for Analysts: Cleaning Messy Source Data Without Formulas
Remove blank rows, split columns, unpivot wide tables, standardize date formats — without touching a single TRIM or SUBSTITUTE formula.
Power Query · Advanced
Merging and Appending in Power Query: The Excel Version of SQL JOIN and UNION
Combining monthly exports, matching datasets on a shared key, and building reconciliation tables — without VLOOKUP or manual copy-paste.
Dynamic Arrays · Advanced
FILTER, SORT, UNIQUE: The Functions That Replace Half Your Pivot Tables
Return filtered lists that update automatically — no Refresh All, no helper columns, no pivot table redesign every time the source data changes.
Expert
Power Pivot · Expert
Power Pivot Data Models: Relationships, DAX Measures, and Why This Beats VLOOKUP Forever
Multi-table data model in Excel — relationships between tables, DAX measures for KPIs, and the bridge to Power BI.
VBA · Expert
VBA for Analysts: Automating the Tasks You Do Manually Every Month
Formatting a pasted report, running a sequence of updates, saving a copy with today's date — the specific VBA patterns analysts actually use.
Power BI Bridge · Expert
From Excel Power Pivot to Power BI: When to Graduate Your Data Model
When an Excel model outgrows Excel — and how DAX, relationships, and data model design transfer directly to Power BI.
How Business Analysts Actually Use Excel
Not tutorial exercises. These are the specific tasks that show up in BA job descriptions, sprint backlogs, and UAT cycles at healthcare IT companies.
| Task | What BAs do | Key formula or feature |
|---|---|---|
| UAT test tracking | Test case log with status per module. Pivot table drives the clinical sign-off dashboard. | =COUNTIFS(Module,"Willow",Status,"Fail") |
| Code mapping | Map ICD-10 codes, encounter type codes, or charge codes to display names from a reference table. | =XLOOKUP(A2,CodeMap[Code],CodeMap[Name],"Not found") |
| Migration reconciliation | Compare source vs target record counts. Find records in source missing from target. | =COUNTIF(Target[MRN],[@MRN])=0 |
| Defect aging | Calculate business days a defect has been open. Conditional formatting flags overdue items automatically. | =NETWORKDAYS(OpenDate,TODAY()) |
| Denial rate analysis | Sum denied claim amounts by payer, service type, and denial code from a raw billing export. | =SUMIFS(PaidAmt,Payer,"BCBS",DenialCode,"197") |
| Monthly report refresh | Drop a new export file in a folder. Power Query picks it up and refreshes all pivot tables automatically. | Data → Refresh All |
Excel vs SQL — When to Use Which
Both are legitimate tools. The skill is knowing when to reach for each one.
| Task | Excel | SQL | The reason |
|---|---|---|---|
| Format and present data for leadership | ✓ Best | Export needed | SQL output is raw data. Excel is the presentation layer. |
| Query a database with millions of rows | ✗ Crashes | ✓ Built for it | Epic Clarity has tens of millions of rows. Excel's limit is ~1 million. |
| Clean messy exported data | ✓ Power Query | ✓ Transform layer | Data already in a file: Power Query. Data in a database: fix it in SQL before exporting. |
| Join two data sources on a key | XLOOKUP (fragile on duplicates) | ✓ JOIN | Under 100K rows, one-time: Excel. Repeatable or large: SQL JOIN. |
| Share interactive filters with stakeholders | ✓ Slicers | Not applicable | Pivot table slicers let stakeholders filter without touching data or formulas. |
| Validate a data migration | Partial (exports only) | ✓ Direct DB access | SQL queries the database directly. Excel works only with what was exported. |
| Work with PHI (HIPAA) | ✗ Risky | ✓ Query in-place | PHI must not be copied to a local Excel file. Query Clarity in-place; export aggregated summary data only. |
Excel, Power Query, SQL, Power BI — What to Learn in What Order
For BA and analyst roles in healthcare IT. Based on what appears in job postings and what actually gets used day-to-day.
📊
Excel Core
Master this first. Every other tool assumes you can use Excel.
Learn First
⚡
Power Query
Free, built into Excel 2016+. Eliminates most manual data cleaning permanently.
Learn Second
🗄
SQL
Gets data from databases directly. Complements Excel — does not replace it.
Learn in Parallel
📈
Power BI
After Power Query and SQL. Power Query and DAX skills transfer directly.
Learn Third
🔗
Power Pivot + DAX
When your Excel models need table relationships or calculated measures.
When Needed
🐍
Python
For processing files at scale or calling APIs. Not required for most BA roles.
Optional
🗄 Ready to Add SQL?
Once you can use Excel effectively, SQL is the next highest-ROI skill for any BA or QA analyst who works with data. The SQL hub covers Epic Clarity, Medicaid data, revenue cycle queries, and interactive challenges.