How to organize submissions from Forms in Excel

Knorr, Brittany 0 Reputation points
2026-01-21T23:37:48.03+00:00

Our team has an active form to collect requests from colleagues for our expertise via Microsoft Forms. As is the standard output, the requests then go into an Excel spreadsheet. Is there a way to modify or organize the spreadsheet so that we can separate the requests by year? Perhaps having different tabs for different years?

Thank you for your help!

Microsoft 365 and Office | Microsoft Forms | For business

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sophie N 18,175 Reputation points Microsoft External Staff Moderator
    2026-01-22T00:39:53.15+00:00

    Dear @Knorr, Brittany,

    Thank you for contacting the Microsoft 365 Q&A Forum Community Support team. The challenge with Microsoft Forms is that the primary "Responses" spreadsheet is a live connection; if you manually move rows or create new tabs, it can often break the sync or lead to data being missed. 

    To organize your requests by year while maintaining a professional and automated workflow, I recommend two specific methods. 

    Option 1: The "Power Query" method  

    This is the most robust way to separate data into year-specific tabs without manual copy-pasting. Power Query allows you to create "Views" of your data that update automatically when the main sheet is refreshed. 

    1. Open your Excel response sheet in the desktop app. 
    2. Go to the Data tab > Get & Transform Data > From Table/Range. (Ensure your data is formatted as a Table first).  User's image
    3. In the Power Query Editor: 
      • Find your "Start Time" or "Completion Time" column. 
      • Right-click the header > Transform > Year > Year. 
      • Click the Filter arrow on that column and select only the year you want (e.g., 2024). 
    4. Select Close & Load To and choose New Worksheet. 
    5. Repeat this process for each year. 

    Every time you click "Refresh All" in the Data tab, these year-specific tabs will automatically pull the latest entries from the main "Form1" sheet. 

    For more details, refer to: Power Query date format (How to + 5 tricky scenarios) 

    Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link. 
    Option 2: Using the FILTER Function

    If you are using Excel for the Web or a recent version of Microsoft 365, you can use a dynamic formula to "leak" data into separate tabs automatically. 

    1. Create a new tab and name it "2025". 
    2. In cell A1, enter the following formula: =FILTER(Table1, YEAR(Table1[Completion time])=2025) 
    3. Excel will automatically populate all rows from the year 2025 into this tab. 

    Note: Replace "Table1" with the actual name of your Forms table. 

    For more details, refer to: FILTER function - Microsoft Support 
    Option 3: Automating with Power Automate  

    If you want the data to be physically moved or copied to a specific file at the moment of submission, you can bypass the default Excel sync and use a Power Automate flow. 

    1. Trigger: When a new response is submitted. 
    2. Action: Get response details. 
    3. Condition: If Submission Date is between Jan 1 and Dec 31. 
    4. Result: Add a row into a specific Excel Table (e.g., "Requests_2025.xlsx"). 

    Microsoft recommends using Power BI or Power Query for advanced data organization from Forms to ensure the "Source of Truth" remains intact. You can find more details on managing response data on the View results of your form - Microsoft Support 

    Please note that as a forum moderator, I don’t have access to backend tools or internal systems to investigate further, and certain settings or configurations are managed exclusively by your organization’s administrators, so I’m unable to check or make changes on that side. That said, I truly hope these suggestions help you move forward.  

    Please let me know if you have any further questions or if the problem persists after trying these solutions. Thank you for your patience and cooperation.  

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

  2. AI answer

    2026-01-21T23:37:56.7833333+00:00

    To organize submissions from Microsoft Forms in Excel by year, you can create separate tabs (worksheets) for each year within your Excel workbook. Here are some steps you can follow:

    1. Create a New Worksheet for Each Year: In your Excel workbook, create a new worksheet for each year you want to organize submissions by. You can name the tabs according to the year (e.g., "2021", "2022", etc.).
    2. Use Filters or Formulas: You can use Excel's filtering capabilities or formulas to separate the data. For example, you can filter the submissions based on the date of the request and copy the relevant entries to the corresponding year's worksheet.
    3. Automate with Power Automate: If you want to automate this process, consider using Power Automate to create a flow that automatically sorts the submissions into the appropriate worksheet based on the submission date. This way, every time a new response is submitted, it can be directed to the correct tab.
    4. Exporting Data: If you need to retain historical data, consider exporting the existing responses to an Excel workbook before clearing the form to collect new responses. This will help you maintain organized records over the years.

    By following these steps, you can effectively manage and organize your form submissions in Excel by year.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?