This tutorial will walk you through how to use Microsoft Excel to build a simple dashboard using CommCareHQ data exports. By the end of the tutorial you will be able to create a dashboard that:
- Refreshes automatically when you download a new data export from CommCareHQ.
- Allows users to filter the dashboard for information relevant to them.
The final result will contain the following:
A graphical dashboard
Individual pages with data:
User definable filters:
Step 1: Create the data export and download data
- See this page for details on creating the data export: Raw Data
- Be sure to rename your Display columns to how you want them to display in your reports.
- Download your export and save it with the name: "Raw Data.xlsx".
- It is important to choose a name for this file that you will remember. Every time you save a new update it will need to have the same name.
- Your exported data will look like this.
Step 2: Create the Report File
The next step is to create the file that will hold the reports. This is a separate Excel file than your raw data file. This way you can update the raw data without having to change the reports file.
- In Excel, Create a New File.
- Save the file as "Reports.xlsx"
- You will have a blank Excel file now.
Step 3: Create the first Pivot Chart
The first chart we will create is the Line Graph of Expected Delivery Date. This will plot the number of women expected to give birth in each month, broken down by Clinic.
- In the blank Reports.xlsx file rename the first Tab "Expected Delivery Date"
- Be sure that your Raw Data file is open
- Select the top-left cell (A1) in the Reports.xlsx
- In the Insert menu choose Pivot Chart from the PivotTable icon
- In the window that pops up, click the icon next to "Table/Range"
- Switch windows to the Raw Data.xlsx file that is already open and select the entire columns that contain the data
- It is important that the Raw Data.xlsx file is already open before doing step 5.
- The small window should populate with something similar to "'[Raw Data.xlsx]Raw Data'!$A:$E". Click the icon next to that.
- Click Ok.