I'm trying to build a data collection and reporting solution for a colleague, and I decided to try using Excel 2016 with Microsoft Forms for the collection part. I started by creating an Excel worksheet and using the Insert > Tables > Forms > New Form command to attach a Microsoft Form. After designing the form, I returned to Excel and found the corresponding worksheet it had created to store the collected data. I proceeded to build some a Pivot Table report in the same workbook using Power Query with the table as the connection's data source.
All seemed to be working fine; however, my colleague has some existing data that he wants to load into the worksheet. I found that I could add rows to the table created by the form and they flow into my Pivot Report. However, when I look at the Microsoft Forms page, it doesn't reflect any of the entries from Excel. I'd assume this would also be true for any edits that might be made to the collected data within Excel.
I would have thought whatever automation occurred between Forms and Excel, that it would keep the two synchronized consistently. Apparently not. Is there any way to get to the actual source data being collected by Microsoft Forms? Where is it stored and how does the connection to Excel work? Thanks!
