When referral and appointment reports come in messy or combined, it’s easy to miss trends and lose time cleaning them up. This guide shows you how to:
Split combined fields (like date/time) into separate columns.
Use IF formulas to automatically classify messy codes or URLs.
Apply conditional formatting to quickly highlight duplicates and important outcomes.
Build pivot tables for easy summaries by provider, date, or outcome.
With these techniques, you can turn raw data into clear, usable insights.
✂️ Splitting combined fields into separate columns
If your report includes multiple pieces of information in one cell (e.g., a date and time together, or several referral outcomes in one column), you can use the Split Text to Columns feature to separate them.
Navigate to our tutorial that digs into this further and includes several visual depictions of this process.
Why this matters: Once split, you can easily build pivot tables to see appointments per provider by day, or quickly scan referral outcomes without extra cleanup.
🔧 Using IF formulas to classify data automatically
Sometimes the source url column contains data that isn't very user-friendly. You can use an IF formula to automatically translate them into clearer labels.
Example:
=IF(ISNUMBER(SEARCH("searchurl", A2)), "eRefer Search interface",
IF(ISNUMBER(SEARCH("sid", A2)), "Service details page",
IF(ISNUMBER(SEARCH("scheduler", A2)), "Internal calendar page",
A2)))
If a URL in A2 contains searchurl, it will show eRefer Search interface.
If it contains sid, it will show Service details page.
If it contains scheduler, it will show Internal calendar page.
Otherwise, it keeps the original value.
Reach out to us via the chat if you want some support in classifying some of these pieces.
Why this matters: This saves you from having to manually label or clean messy source data every time.
🎨 Conditional formatting for quick insights
Conditional formatting helps you highlight important values automatically — no manual scanning required.
Highlight duplicates
Formula (Sheets): =COUNTIF(A$2:A$100, A2) > 1
Formula (Excel): Similar approach using Home → Conditional Formatting → New Rule → Use a formula.
This will colour any cell where the value appears more than once in your range.
Highlight specific outcomes
Want to quickly spot discharges, no-shows, or other important outcomes?
Select your range.
Go to Format → Conditional formatting.
Use a rule such as “Text contains ‘No-Show’” and apply a distinct colour.
Why this matters: You’ll immediately see opportunity areas (like repeat parent referrals or missed appointments) without needing to dig through rows manually.
📊 Pivot tables for summarizing referrals and appointments
After cleaning your columns with splitting and formulas, pivot tables can give you instant summaries:
Count referrals per provider, per day.
Compare booked vs. open appointments.
Look at monthly vs. daily appointment trends.
Both Excel and Google Sheets have a Insert → Pivot Table option to help you create these summaries.
💡 Reach your analysis goals
By combining these techniques, you can take raw referral data and transform it into structured insights that drive action. Splitting text into columns gives you cleaner fields to work with, IF formulas save you time on labeling, conditional formatting highlights what matters most, and pivot tables summarize the big picture at a glance.
Start with one or two of these methods in your next report, and you’ll quickly see how much easier it is to track outcomes, identify trends, and share clear information with your team.