Others

How to Track Tuck Shop Purchases with Microsoft Forms and Excel

Nimra Malik Sep 28, 2026 869 views

Question details

The user wants to create a workflow to reliably record workplace tuck shop purchases, identify purchasers, and calculate their monthly balances.

How to Track Tuck Shop Purchases with Microsoft Forms and Excel

Product: Microsoft Forms and Excel
Device & OS: not provided
Scenario: Managing and tracking internal workplace tuck shop or snack bar purchases effectively.
Observed behavior: Setting up an authenticated data collection method to capture purchaser details and calculating totals automatically.

Before you start

Ensure you have an active Microsoft 365 organizational account to restrict form access and automatically capture the names of the purchasers within your tenant.

Solution 1Recommended

Create a Restricted Form and Link to Excel

Build a custom Microsoft Form restricted to your organization and connect it to an Excel workbook to capture authentic names and calculate monthly balances.

By restricting the form to your organization, you eliminate anonymous entries and guarantee that each purchase is tied to a specific employee's work account. Linking this to Excel allows for real-time automated accounting.

Create the purchase form

Go to Microsoft Forms and create a new form. Add specific fields for the purchased Item, Quantity, and Purchase Date.

Restrict access to capture names

Open the form Settings. Under 'Who can fill out this form', select 'Only people in my organization can respond' and check the 'Record name' box. This ensures responses are tied to organizational accounts.

Link the form to Excel

Navigate to the 'Responses' tab in your form and click 'Open in Excel'. This will create a connected spreadsheet that automatically syncs all incoming purchase records.

Calculate monthly balances

In the connected Excel file, add a separate reference sheet for item prices. Use a PivotTable or the SUMIFS function to calculate the total monthly balance owed by each person based on their recorded names and quantities.

Share via QR code

Click 'Collect responses' in Microsoft Forms, generate a QR code, and print it out for the tuck shop. Employees can easily scan it with their phones to log purchases.

Create a Restricted Form and Link to Excel

Best Practice: Always maintain a separate 'Price List' table in your Excel workbook. If item prices change, you can update the table without breaking previous calculation formulas.

Track Purchases Efficiently

Manage Tuck Shop Data with WPS Spreadsheet

You can easily track purchases, calculate balances, and manage tuck shop inventory using WPS Office. It provides powerful spreadsheet features that are perfectly compatible with Excel files exported from any form tool.

1. Import your data: Open WPS Spreadsheet and load the .xlsx file downloaded from your form responses.
2. Set up a pricing table: Create a new worksheet within the file to list all tuck shop items and their corresponding prices.
3. Calculate totals with functions: Use the SUMIFS function or VLOOKUP combined with basic arithmetic to calculate the total amount owed by each purchaser based on the recorded names.
4. Summarize with a PivotTable: Go to the Insert tab, select PivotTable, and drag the purchaser names to the Rows area and total costs to the Values area to view individual monthly balances.

Fully compatible with Microsoft Excel formats (.xlsx)Advanced PivotTables and SUMIFS functions for easy balance calculationBuilt-in templates for inventory and expense trackingLightweight and free to use across multiple devices

microsoft office alternative - wps office

Frequently Asked Questions

How do I prevent anonymous responses in my tuck shop form?

In Microsoft Forms, go to Settings and select 'Only people in my organization can respond', then check the 'Record name' option. This forces users to log in with their work accounts and captures their identities automatically.

Can I automate the monthly balance calculation?

Yes. By linking your form directly to an Excel workbook, you can set up formulas like SUMIFS or use a PivotTable to automatically calculate and update totals whenever a new purchase is submitted.

How can colleagues easily access the purchase form?

You can generate a QR code from the 'Collect responses' menu in your form settings. Print the QR code and display it at the tuck shop so colleagues can scan it with their mobile devices.

What if a purchaser enters the wrong item?

Form responses are static once they are submitted. You will need to manually correct the entry in the connected Excel workbook to ensure your monthly billing remains accurate.

Office Quick Solutions