TechFitFlow.com · Excel for IT Analysts
Excel for Healthcare IT and Business Analysts
From basic formulas to Power Query and pivot dashboards. Written for BAs, QA analysts, and healthcare IT professionals who use Excel every day but have never been shown what it can actually do.
Your Excel Path
✓
Foundations
Formulas and formatting
→
Analyst Core
Pivot tables and VLOOKUP
🔒
Power User
Power Query and dynamic arrays
🔒
Expert
Power Pivot, DAX, VBA
Progress Tracking
Your Excel Skill Progress
Check off skills as you learn them. Your progress saves automatically in the browser.
Progression Map
Foundations → Analyst Core → Power User → Expert
Each level unlocks the next. Your position updates as you check off skills above.
📋
Foundations
Formulas and formatting
0/6 skills
📊
Analyst Core
Pivot tables and lookups
0/7 skills
⚡
Power User
Power Query and arrays
0/6 skills
🏆
Expert
Power Pivot, DAX, VBA
0/5 skills
Learning Paths
Four Paths — Matched to Real Analyst Roles
Each path has a specific output skill that maps to what BAs, QA analysts, and healthcare IT professionals actually need on the job.
Foundations Path
Excel Foundations
Goal: use Excel confidently, not accidentally
- Cell references: absolute ($A$1) vs relative (A1) and when each breaks
- SUM, COUNT, COUNTA, AVERAGE, MIN, MAX
- IF and nested IF — the building block of everything
- Text functions: TRIM, LEFT, RIGHT, MID, LEN, CONCATENATE
- Formatting: number formats, date formats, conditional formatting basics
- Tables: structured references and why they matter
Output Skill
You can build a clean, functional spreadsheet from scratch, use formulas reliably, and format data for sharing with stakeholders.
Start Foundations →Analyst Core Path
Analyst Core Skills
Goal: answer business questions with data
- VLOOKUP and XLOOKUP — when each works, when both fail
- SUMIF, COUNTIF, SUMIFS, COUNTIFS — conditional aggregation
- Pivot tables: design, grouping, calculated fields, slicers
- Conditional formatting for status dashboards and heat maps
- Data validation: dropdowns, input restrictions, error alerts
- IFERROR, ISBLANK, ISNA — building resilient formulas
- Named ranges and structured table references
Output Skill
You can build operational reports, look up data across sheets, summarize datasets with pivot tables, and create dashboards that update when data changes.
Start Analyst Core →Power User Path
Power Query and Dynamic Arrays
Goal: stop cleaning data by hand
- Power Query: connecting to files, databases, and APIs
- Power Query transformations: merge, append, unpivot, split column
- M language basics: custom column logic in Power Query
- Dynamic arrays: FILTER, SORT, UNIQUE, SEQUENCE
- XLOOKUP, INDEX/MATCH — why INDEX/MATCH still wins in some cases
- Array formulas and SUMPRODUCT for multi-condition logic
Output Skill
You can automate data cleaning, connect Excel to source systems, and write formulas that dynamically filter and sort without manual intervention.
Start Power User →Expert Path
Power Pivot, DAX, and Automation
Goal: build systems, not spreadsheets
- Power Pivot: data model, relationships between tables
- DAX basics: CALCULATE, SUMX, RELATED, time intelligence
- VBA for automation: macros, loops, conditional logic
- Connecting Excel to SQL Server and SharePoint
- Distributing Excel as a template others can use safely
Output Skill
You can build multi-table data models in Excel, write DAX measures for KPIs, automate repetitive tasks with VBA, and deliver Excel tools that non-analysts can use without breaking.
Start Expert Path →Articles
Excel Articles for Healthcare IT and Business Analysts
Every article is written from a practitioner perspective — real scenarios from healthcare IT implementations, QA test management, and BA reporting workflows.
Excel Foundations
Excel Absolute vs Relative References: The One Concept That Fixes Half Your Formula Errors
Why $A$1, A$1, $A1, and A1 all behave differently when you copy a formula — and how to choose correctly the first time instead of chasing broken formulas across 500 rows.
Coming soonIF, AND, OR in Excel: Building Conditional Logic Without Losing Your Mind
Nested IF statements that actually work, the AND/OR shortcut that simplifies them, and IFERROR for when you need the formula to fail gracefully instead of showing #N/A.
Coming soonExcel Tables: Why You Should Convert Every Dataset to a Table Before Touching It
Structured references, auto-expanding ranges, and the three reasons Excel Tables make every formula, pivot table, and chart easier to build and maintain.
Coming soon
Analyst Core Skills
VLOOKUP vs XLOOKUP: When Each Works, When Both Fail, and Why INDEX/MATCH Matters
VLOOKUP breaks on duplicate keys, can't look left, and slows down on large datasets. XLOOKUP fixes most of that. This covers when to use which — and when none of them are the right answer.
Coming soonPivot Tables for Analysts: Beyond the Default — Calculated Fields, Grouping, and Slicers
Pivot tables that actually answer business questions. Calculated fields for KPIs, date grouping for time series, and slicers that let stakeholders filter without touching the data.
Coming soonSUMIF, COUNTIF, and SUMIFS: Conditional Aggregation Without a Pivot Table
When you need a single-cell answer instead of a pivot table — SUMIF for one condition, SUMIFS for multiple. Includes wildcard matching and cross-sheet references that most analysts miss.
Coming soonConditional Formatting for Test Status and Defect Dashboards
Using conditional formatting rules to build QA dashboards that show pass/fail/blocked status, highlight overdue items in red, and surface patterns that manual review misses.
Coming soonExcel for Healthcare IT Analysts: ICD-10 Code Mapping, CDM Lookups, and APR-DRG Reference Tables
How to build and use ICD-10 lookup tables, charge description master (CDM) mapping files, and APR-DRG weight references using XLOOKUP, FILTER, and structured Excel tables.
Coming soonExcel for UAT Management: Test Case Tracking, Defect Logs, and Sign-Off Dashboards
A full UAT management workbook built in Excel — test case status by module, defect log with severity and owner, and a pivot-driven sign-off dashboard that the clinical lead can read without IT help.
Coming soon
Power Query and Dynamic Arrays
Power Query for Analysts: Cleaning Messy Source Data Without Formulas
How to use Power Query to remove blank rows, split concatenated columns, unpivot wide tables, and standardize inconsistent date formats — without touching a single TRIM or SUBSTITUTE formula.
Coming soonMerging and Appending Queries in Power Query: The Excel Version of SQL JOIN and UNION
Merge (JOIN equivalent) and Append (UNION equivalent) in Power Query — with real examples: combining monthly report exports, matching two datasets on a shared key, and building a reconciliation table.
Coming soonFILTER, SORT, UNIQUE: The Dynamic Array Functions That Replace Half Your Pivot Tables
How FILTER returns a dynamic subset, UNIQUE deduplicates a column without a pivot table, and SORT produces an automatically-updated sorted list — all updating when the source data changes.
Coming soon
Power Pivot, DAX, and Automation
Power Pivot Data Models for Analysts: Relationships, Measures, and Why This Beats VLOOKUP Forever
Building a multi-table data model in Excel — defining relationships between tables, writing DAX measures for calculated KPIs, and why this approach scales where VLOOKUP breaks.
Coming soonFrom Excel Power Pivot to Power BI: When to Graduate Your Model
When an Excel Power Pivot model outgrows Excel — and how the skills transfer directly to Power BI. DAX, relationships, and data model design are the same in both. The tool is just bigger.
Coming soonVBA for Analysts: Automating the 15 Tasks You Do Manually Every Month
No programming background required. This covers the specific VBA patterns that automate the repetitive work analysts actually do: formatting a pasted report, running a sequence of updates, saving a copy with today's date.
Coming soonBA Real Context
How Business Analysts Actually Use Excel on the Job
Not tutorial exercises. These are the specific Excel tasks that appear in BA job descriptions, sprint backlogs, UAT cycles, and stakeholder deliverables at healthcare IT companies.
UAT Test Case Tracking
BAs maintain the UAT test case log in Excel — test case ID, module, tester, status (Pass/Fail/Blocked), defect ID if failed, and retested status. Pivot tables on this log produce the sign-off dashboard for clinical leadership.
=COUNTIFS(C:C,"FAIL",B:B,"Willow")
→ Count Willow failures for triage
=SUMIF(E:E,"High",F:F)
→ Sum high-severity defect count
Code Mapping and Lookups
BAs build mapping tables — ICD-10 to service line, CPT to charge code, encounter type code to display name. XLOOKUP or Power Query Merge pulls these mappings into operational reports without manual copying.
=XLOOKUP(A2,CodeMap[Code],
CodeMap[Description],"Not found")
→ Maps any code to its label
without VLOOKUP's left-only limit
Stakeholder Reporting Packs
Weekly or monthly reports for clinical leadership, project steering committees, or revenue cycle directors. Excel is the delivery format. The BA owns the formatting, the formula logic, and the commentary — not just the data.
=TEXT(TODAY(),"MMMM YYYY")
→ Dynamic report period header
=IFERROR(B2/C2,"N/A")
→ Ratio that doesn't break
when denominator is zero
Data Migration Reconciliation
After a data migration, BAs use Excel to reconcile source vs target record counts by entity type. Power Query pulls the export files, Merge compares them on MRN or account number, and the mismatch count tells you what to investigate in the database.
=COUNTIF(Source[MRN],[@MRN])=0
→ TRUE = record exists in source
but not in target migration
Flag these for investigation
Sprint and Project Tracking
BAs maintain sprint task logs, defect aging trackers, and release readiness dashboards in Excel when Jira reporting is insufficient or when non-IT stakeholders need a simpler view. Conditional formatting does the visual status work automatically.
=NETWORKDAYS(B2,TODAY())
→ Business days since defect opened
→ Apply conditional format:
>5 = Red, 3-5 = Amber, <3 = Green
Financial and Revenue Cycle Analysis
Revenue cycle BAs use Excel for charge lag analysis, denial rate tracking, and payer-specific claim summaries before the data makes it into a formal report. SUMIFS across payer, service type, and date range is the core pattern.
=SUMIFS(PaidAmt,Payer,"BCBS",
ServiceType,"Inpatient",
DenialCode,"197")
→ BCBS inpatient prior auth
denial exposure in one cell
Real Use Cases
Three Excel Workflows Analysts Actually Use
End-to-end walkthroughs showing how Excel fits into real analyst deliverables — not spreadsheet demos.
1
The BA receives 340 UAT test cases from the Epic build team across 8 modules
Each test case has: module, scenario ID, tester name, test steps, expected result. No status yet — that comes during testing. The BA's job is to set up the tracking workbook before testing begins.
2
Convert the test case list to an Excel Table and add status columns
Convert to a Table (Ctrl+T). Add columns: Status (Pass/Fail/Blocked/Not Run), Defect ID, Defect Severity, Retest Status, Retest Result. Add data validation dropdowns for Status and Severity so testers can only select valid values — no "Passed" vs "Pass" inconsistency.
=Data Validation → List → "Pass,Fail,Blocked,Not Run"
Applied to Status column — prevents free-text entry errors
💡 Use a separate Lookup sheet for all dropdown lists. When you need to add a new status option, you update one cell — not 340 validation rules.
3
Build summary formulas for the daily status report
On a separate Summary sheet, COUNTIFS pulls counts by module and status — no pivot table refresh required, updates automatically as testers enter results.
=COUNTIFS(TestLog[Module],$A2,TestLog[Status],"Pass")
=COUNTIFS(TestLog[Module],$A2,TestLog[Status],"Fail")
=COUNTIFS(TestLog[Module],$A2,TestLog[Status],"Blocked")
=COUNTIFS(TestLog[Module],$A2,TestLog[Status],"Not Run")Pass Rate: =B2/SUM(B2:E2) — format as percentage
4
Add conditional formatting to the summary for the clinical leadership view
Format the Pass Rate column: green if ≥ 95%, amber if 80–94%, red if below 80%. This is the one view the CMO will look at. The BA controls the threshold logic — not IT, not the project manager.
💡 Lock the Summary sheet so clinical stakeholders can read it but cannot accidentally edit the formulas. Sheet → Protect Sheet → allow Select Cells only.
5
Add a Defect Log sheet that links back to test cases
The Defect Log is a second table in the same workbook — Defect ID, linked Test Case ID, description, severity, assigned analyst, status, resolution date. XLOOKUP pulls the test case details from the first table so you don't enter them twice.
=XLOOKUP([@[Defect ID]],TestLog[Defect ID],
TestLog[Module],"No match")
→ Pulls Module for each defect ID
automatically — no manual entry
1
The BA receives 12 monthly claim export files, each formatted slightly differently
January has headers in row 1. February has headers in row 3 with two blank rows at the top. March uses "Claim Status" while others use "Status". Each file has 8,000–15,000 rows. Manual cleanup would take 2 hours. Power Query does it in 5 minutes — once set up, it runs in 10 seconds every month.
2
Load all files into Power Query using "Get Data → From Folder"
Point Power Query at the folder. It loads all 12 files simultaneously and combines them into one dataset. This is the Append operation — the Power Query equivalent of SQL UNION ALL.
Data → Get Data → From File → From Folder
→ Select the monthly exports folder
→ Power Query loads all .xlsx files
→ "Combine" button → Combine and Load
3
Apply transformations to standardize the combined dataset
Every step recorded in the Applied Steps pane — remove top rows where needed, rename columns to a consistent header set, change data types, filter out blank rows, standardize "Claim Status" vs "Status" with a Rename Column step.
💡 Every transformation in Power Query is recorded as a step. If a future file has a different format, you add a conditional step — not a formula in 15,000 cells.
4
Load the clean combined dataset to an Excel Table — then build pivot tables on top
Close and Load to → Table. Every month, when you receive a new export: drop it in the folder, click Data → Refresh All. The table updates, the pivot tables update, the charts update. Zero manual formatting.
1
The revenue cycle director asks for a denial rate summary by payer and service type — today
You have a claim export from the billing system: 22,000 rows, payer name, service type, claim status, paid amount, denial code. No SQL access. Just Excel. You can answer this in 10 minutes.
2
Convert to Table, insert a Pivot Table on a new sheet
Rows: Payer Name. Columns: Service Type. Values: Count of Claim ID (filtered to Denied status). Add a second pivot for total claims per payer/service. Then calculate denial rate using a formula referencing both pivot tables.
Pivot 1: COUNT of claims where Status = "Denied"
Pivot 2: COUNT of all claims
Denial Rate sheet:
=Denied_Pivot/Total_Pivot → format as %
3
Add SUMIFS for the dollar exposure by denial code
The director also wants to know which denial code is costing the most. SUMIFS is faster than a pivot table for a single-number answer per code.
=SUMIFS(ClaimData[PaidAmt],
ClaimData[Status],"Denied",
ClaimData[DenialCode],"197")
→ Total prior auth denial exposure
4
Add conditional formatting to the denial rate table and send
Format: red if denial rate > 15%, amber if 10–15%, green if below 10%. Lock the pivot table sheets. Save as a new file with today's date. Send. Total time from question to answer: under 15 minutes. This is what Excel expertise actually looks like in a BA role.
Power User Corner
Power Query, Dynamic Arrays, and the Formulas Most Analysts Skip
The capabilities that separate analysts who can do the basics from analysts who build tools that other people use.
Power Query — M Language
Adding Conditional Column Logic Without a Formula
In Power Query's Add Column → Custom Column, you write M language conditions. No cell formulas, no IFERROR wrappers — the logic applies once at query level and runs on every row automatically.
// Custom Column in Power Query
if [Status] = "Denied"
and [DenialCode] = "197"
then "Prior Auth"
else if [Status] = "Denied"
then "Other Denial"
else "Not Denied"
Power Query Deep Dive →Dynamic Arrays — FILTER Function
FILTER: A Pivot Table That Doesn't Need Refreshing
FILTER returns a dynamic subset of a table based on one or multiple conditions. Unlike a pivot table, it spills the results automatically and updates the moment the source data changes. No Refresh All needed.
// Show only Willow defects with High severity
=FILTER(DefectLog,
(DefectLog[Module]="Willow")*
(DefectLog[Severity]="High"),
"No results")// * between conditions = AND logic
// + between conditions = OR logic
Dynamic Arrays Article →DAX — Power Pivot Measure
Your First DAX Measure: Denial Rate Across Any Filter
A DAX measure calculates dynamically based on whatever filter is active in the pivot table or slicer — no hardcoded cell references, no formula rework when a new payer is added.
// DAX measure in Power Pivot
Denial Rate :=
DIVIDE(
CALCULATE(
COUNTROWS(Claims),
Claims[Status] = "Denied"
),
COUNTROWS(Claims),
0 // return 0 if denominator is 0
)
Power Pivot and DAX Article →Tool Clarity
Excel vs SQL — When to Use Which
This table is calibrated for BA, QA, and healthcare IT roles. Both tools are legitimate — the skill is knowing when each is the right choice.
| Task | Excel | SQL | Winner | Why It Matters for Analysts |
|---|---|---|---|---|
| Format and present data for leadership | ✓ Built for this | Needs export | Excel | SQL output is raw data. Excel is the presentation layer. Always export SQL results to Excel for stakeholder delivery. |
| Combine data from two sheets on a shared key | XLOOKUP / Power Query | ✓ JOIN is cleaner | Both | Under 100K rows and one-time: Excel. Repeatable or large: write the SQL JOIN, export result to Excel. |
| Query a database with 1M+ rows | ✗ Crashes | ✓ Built for this | SQL | Excel has a row limit (~1M). Epic Clarity tables have tens of millions. SQL is the only option. |
| Clean and reshape a messy data export | ✓ Power Query | ✓ Transform layer | Both | If the data is already in a file: Power Query. If it's in a database: fix it in the SQL query before exporting. |
| Build a report that runs every month | ✓ Power Query refresh | ✓ Save query, rerun | Both | Power Query + Excel is a fully automated refresh cycle if the source is a file. SQL is better if the source is a live database. |
| Build a multi-table data model for KPIs | Power Pivot (works) | ✓ SQL views are cleaner | SQL | Power Pivot works up to a point. For production KPIs shared across the organization, define the logic in a SQL view — then Power BI or Excel connects to the view. |
| HIPAA-compliant PHI handling | ✗ Risky — local file | ✓ Query in-place | SQL | PHI must not be copied to a local Excel file. Query Clarity in-place, aggregate the results, export only de-identified summary data to Excel. |
| Share interactive filters with a non-technical user | ✓ Slicers and dropdowns | Not applicable | Excel | Slicers on pivot tables let stakeholders filter by department, date, payer — without touching formulas or raw data. |
| UAT test case and defect tracking | ✓ Right tool | No | Excel | Jira is for defects that need workflow. Excel is for test case lists, sign-off dashboards, and status packs that stakeholders need to read and understand quickly. |
Topic Library
Browse Excel Topics by Domain
Each topic links to articles covering beginner explanation, real dataset examples, and advanced extension. Start anywhere.
Healthcare IT Analytics
ICD-10 mapping, CDM lookups, APR-DRG tables, Epic export analysis
BA and QA Deliverables
UAT tracking, defect logs, RTM, sign-off dashboards, sprint reports
Finance and Revenue Cycle
AR aging, denial analysis, charge lag, budget vs actual, payer summaries
Project and Sprint Tracking
Gantt charts, sprint velocity, milestone tracking, resource allocation
Data Cleaning and ETL
Power Query transformations, deduplication, format standardization, file combining
KPI Dashboards
Pivot table dashboards, slicers, charts, conditional formatting heat maps, executive summaries
Tool Stack
Excel, Power Query, Power BI, SQL, Python: What to Learn in What Order
For BA and analyst roles in healthcare IT and enterprise. Based on what appears in job postings and actual day-to-day work — not generic "learn data science" advice.
Excel Core
Master this first. Every other tool assumes you can use Excel. No exceptions for analyst roles.
Power Query
Learn immediately after core Excel. Eliminates most manual data cleaning. Free, built into Excel. Highest ROI upgrade to your Excel skills.
SQL
Learn alongside or after Power Query. SQL gets data from databases directly — Power Query then shapes it. The two tools complement each other perfectly.
Power BI
After Power Query and SQL. Power BI is Power Query + Power Pivot with a better visual layer. Your Power Query skills transfer directly. SQL feeds its data model.
Power Pivot + DAX
The bridge between Excel and Power BI. Learn when your Excel data models need relationships between tables or calculated measures that formulas can't handle cleanly.
Python
For BAs who need to automate complex data prep, call APIs, or process files at scale. Not required for most BA roles — but powerful when Excel and SQL have reached their limits.
Interview Ready
Excel Interview Questions by Role
Actual questions from analyst interviews at health systems, payers, and enterprise IT companies. Not "what is a pivot table" — questions that separate candidates who can actually use Excel from those who listed it on a resume.
🏥 Healthcare IT Analyst
You receive a 40,000-row claim export with inconsistent date formats across columns. How do you standardize it before analysis?
How would you use Excel to map encounter type codes to display names from a reference table?
You have 12 monthly export files in different formats. How do you combine them without copying and pasting?
A VLOOKUP on a 50,000-row table is making the workbook slow. What are your options?
How would you use conditional formatting to flag test cases that have been open for more than 5 business days?
You need to calculate average LOS from admit and discharge timestamps in an Excel export. What formula do you use and what edge cases do you handle?
📋 Business Analyst
Walk me through how you would build a UAT sign-off dashboard in Excel for a clinical audience.
What is the difference between VLOOKUP and INDEX/MATCH and when do you use each?
You have a requirements traceability matrix in Excel with 200 requirements. How do you count how many are linked to a test case?
How do you prevent stakeholders from accidentally editing formulas in a shared workbook?
A manager pastes new data into your report and your formulas break. How do you prevent this?
How would you use Excel to reconcile two lists — a source system export and a target system export — to find records that are in one but not the other?
🔍 QA / Test Analyst
How would you set up a test case log in Excel so that a pivot table automatically shows pass/fail counts by module?
You need to compare an expected results file to an actual results file for 500 records. How do you find the mismatches in Excel?
How do you use conditional formatting to make failing test cases visually obvious in a shared tracker?
What is the risk of using VLOOKUP for test data matching and how do you mitigate it?
How would you use Excel to calculate how many business days a defect has been open?
Your test data has duplicate test case IDs. How do you identify and flag them without removing them?
💰 Finance / Revenue Cycle Analyst
Build an AR aging report in Excel that buckets outstanding claims by 0-30, 31-60, 61-90, and 90+ days from submission date.
How would you calculate denial rate by payer and service type from a raw claims export?
You need a report that automatically updates when the monthly file is dropped in a folder. How do you build this?
What is SUMPRODUCT used for and when is it better than SUMIFS?
How do you create a chart that shows month-over-month revenue trend with a target line?
A VLOOKUP to a charge code table returns #N/A for 15% of rows. What are the three most likely causes?
🗄 Ready for SQL?
Excel and SQL are complementary tools. 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 Learning Hub covers Epic Clarity, Medicaid data, revenue cycle SQL, and interactive challenges.
FAQ
Frequently Asked Questions
Should I learn Excel or SQL first?
Excel first if you have no data skills at all. It is faster to become functional in Excel, it is immediately useful for formatting and presenting work, and it gives you mental models (tables, filters, aggregation, lookups) that transfer directly to SQL. Once you can use Excel confidently for reporting, learn SQL. The two tools together make you genuinely competitive at the mid-to-senior analyst level.
What is Power Query and do I need it?
Power Query is Excel's built-in data transformation tool. You use it to connect to files and databases, clean messy data, combine multiple files, and reshape data — all without formulas. If you ever manually copy and paste data from one place to another, clean columns by hand, or combine monthly exports: yes, you need Power Query. It eliminates most of that work permanently.
What version of Excel do I need for Power Query, XLOOKUP, and dynamic arrays?
Power Query: Excel 2016 or later (built in), Excel 2010/2013 via free add-in download. XLOOKUP: Microsoft 365 (Office 365) or Excel 2021 — not available in Excel 2019 or earlier. Dynamic arrays (FILTER, SORT, UNIQUE): Microsoft 365 only. If your organization runs an older standalone Excel version, check before depending on these functions in shared workbooks.
Is Excel still relevant when Power BI exists?
Yes — for different reasons than 10 years ago. Excel is still the universal format for sharing data with stakeholders, the fastest tool for one-off analysis, and the delivery format for most BA deliverables (requirements, test plans, sign-off packs). Power BI is for dashboards that need to scale, update live, or be shared with large audiences. Most analyst roles in healthcare IT use both. Power Query and Power Pivot skills in Excel transfer directly to Power BI, so mastering Excel's advanced features makes learning Power BI significantly faster.
What Excel skills do healthcare IT analyst job postings actually require?
Based on what appears in mid-to-senior BA and QA job postings at health systems: pivot tables (listed in ~85% of postings), VLOOKUP or XLOOKUP (~70%), conditional formatting (~50%), data validation (~40%), Power Query or "advanced Excel" (~35%), VBA or macros (~20% — usually for senior roles). The Analyst Core path covers everything through the 50% threshold. Power User covers what separates candidates at the senior level.
How is Excel used specifically for UAT in Epic implementations?
Epic UAT test cases are typically tracked in Excel workbooks maintained by BA/QA analysts. The workbook contains: the test case list (often exported from Zephyr or a project tracker, then managed in Excel during the test cycle), a defect log linked to test cases by ID, and a summary dashboard using COUNTIFS and pivot tables for the sign-off report. BAs who set this up correctly give clinical leadership a readable status view without IT involvement. The "UAT Dashboard" use case above walks through the exact setup.