Excel to KHIS Dashboard: Step-by-Step Guide for Health Facility Reporting (2025)

How to Create Excel-to-KHIS Dashboards

This step-by-step guide walks you through creating automated dashboards that pull live KHIS data into Excel for real-time facility reporting.

What You'll Learn:
• Setting up KHIS API connections in Excel
• Data mapping and transformation
• Creating interactive dashboard visualizations
• Automating monthly reporting cycles

Tools Required:
- Excel 2016 or later
- KHIS account with API access
- Power Query (built into Excel)

Step 1: Connect to KHIS API
[Content continues...]

This approach saves facilities 4-6 hours monthly on manual data compilation.

Why Excel Dashboards for KHIS Reporting

Facility managers know their programs better than anyone. But they often don't have direct access to KHIS dashboards. Excel is the tool they already use daily. So why not bring KHIS data into Excel where they're comfortable working?

Excel-based dashboards offer several advantages. They work offline, so you don't need internet connectivity every time you need to check data. Your facility can prepare reports even during connectivity issues. Plus, Excel files can be shared via email or USB drives, no special software required.

Many facilities already have monthly Excel reporting templates. Adding KHIS data directly into these templates eliminates manual copying and ensures consistency across all reports.

Understanding Your Data Flow

KHIS provides APIs that allow Excel to pull data directly. When you set up these connections properly, your monthly reporting becomes semi-automated. Data refreshes when you open the file, or you can refresh it manually whenever needed.

The data flow is simple: KHIS API provides data, Excel transforms it using Power Query, then displays it in your reports and dashboards. Each step is straightforward once you understand how it works.

Setting Up KHIS API Connections

Before you start, your KHIS administrator needs to enable API access for your account. Ask them to grant you appropriate permissions. Different users might have access to different data. A facility in-charge sees their facility data. A district manager sees all facilities in their district.

Once API access is enabled, you'll get credentials (username and password or API token). Keep these secure. Never share them or paste them in unsecured emails.

Data Mapping: Connecting KHIS Fields to Your Report

KHIS and your local reporting template probably use different field names. You need to map them. For example, KHIS might call something "ANC_1st_visit" while your template calls it "Antenatal visits first visit."

Create a mapping document that lists each field in your template and its corresponding KHIS field. This mapping becomes the bridge between the two systems.

Building Your First Dashboard

Start simple. Create a dashboard that shows:
- Monthly target vs actual for key indicators
- Trend line showing performance over the last 3 months
- Color coding (red/yellow/green) based on target achievement
- Facility name and reporting period

Keep dashboards to one page. Users don't want to scroll through multiple pages of dashboards. What's the single most important thing facility managers need to know right now?

Using Power Query for Data Transformation

Power Query (in Excel) is where the magic happens. It connects to KHIS, pulls data, and transforms it exactly as you need. You don't need to be a programmer. Power Query is visual - you click buttons, and it builds the transformation steps.

Common transformations include:
- Filtering data to your facility or district
- Calculating percentages
- Creating derived indicators (like % target achieved)
- Grouping data by month or quarter

Once your Power Query is set up, refreshing data takes one click.

Automating Monthly Reporting

Your first month will take longer while you set everything up. But month two? Much faster. Month three? You're just clicking refresh.

Set up a monthly reminder to refresh the data the first week of each month. Create backups before refreshing in case something goes wrong. Keep a file history so you can always go back to previous months.

Visualizing Your Data

Charts and graphs make dashboards useful. People understand visual trends faster than numbers. Use:
- Line charts for trend data
- Bar charts for comparing months
- Gauge charts to show progress toward targets
- Tables for detailed line-item data

Color code everything. Green for target achieved, yellow for borderline, red for missed targets. Facility managers can understand colored dashboards at a glance.

Sharing Your Dashboards

Your dashboard is useful only if people actually use it. Share it with:
- Your facility leadership
- Department heads
- District supervisors
- Your donor partners

Include a simple one-page guide explaining what each chart means and what actions should be taken if targets are missed.

Troubleshooting Common Issues

If your dashboard stops updating, check these:
- Are your API credentials still valid?
- Has your facility been renamed in KHIS?
- Are there new data fields you need to map?
- Is your Power Query formula still correct?

Each issue has a solution. Error messages usually tell you exactly what went wrong.

Scaling to District Level

Once your facility dashboard works, you can build a district version combining data from multiple facilities. Use the same Power Query approach but aggregate data across all your facilities.

Next Steps

Start by listing the 5-10 most important indicators your facility tracks. Create a simple Excel template with those fields and labels. Then connect the KHIS API. Build your first dashboard. Share it with your team. Get feedback and improve.

The time you invest now will pay dividends monthly when your reporting process is half as long as it used to be.