Popular Help Content
- In Excel, Create a New File.
- You will have a blank Excel file now.
- Go to a blank tab in your workbook. Rename that sheet "Raw Data".
- Select the top-left cell (A1).
- Now we will pull in the data from the Excel file
- Choose the Data menu
- Choose From Web in the "Get External Data" section
- Be sure you have Copy link address from step 2 above so it is on your clipboard.
- The "New Web Query" window appears.
- In the "Address" bar at the top, Paste the link you address you copied in Step 2 above. It should be something like "https://www.commcarehq.org/a/excelreportingdemo/reports/export/saved/download/96c41fd6fb9be6119ad32467254cbc9a/"
- Click Go
- The "Windows Security" window appears. Type your CommCareHQ username and password.
- The export will be displayed to you in the Web Query Window
- Click the second Yellow Arrow above the table of data (not the very top one). This will select all the CommCare data in the export.
- Click Import
- The "Import Data" window appears.
- Click Properties
- The "External Data Range" window appears
- In the top of the window, name your export "Raw Data"
- Choose Save query definition
- Choose Untick Enable background refresh
- Choose Preserve Cell Formatting
- Choose Adjust column width
- Choose Overwrite existing cells with new data, clear unused cells
- Choose Fill down formulas in columns adjacent to data
- Click OK
- Click OK again
- The data is pasted into the empty worksheet
- Choose Data menu
- Click Refresh All
- This will pull the latest data from CommCareHQ. You will be prompted for your password.
If Excel hangs during the refresh, disable Enable Background Refresh on all data connections. This is a known problem for spreadsheets with multiple web-based data connections. Find it under Data -> Connections -> Select Connection, click Properties -> unselect Enable Background Refresh.
Step 11: Send to Others
If you just send the Excel file to others they will be prompted to enter login credentials in order to refresh the data. If you don't want this to happen, you can do the following: