Section 4: Excel/Google Sheets, data analysis and reporting

Scenario 31: Adding a formula and lookup column to a disorganized sales file

Level: Intermediate · Tools: Excel for Microsoft 365 and Copilot in Excel

Who and in what situation

Elif, a sales operations specialist at a manufacturing SME in Bursa, adds profit margin and stock columns to a sales export every week. Because the product list and stock list are in different orders, she writes the formulas manually.

How it is done today

Elif compares the headers on the two sheets, copies the formulas down, and examines cells one by one in files she has inherited.

How to do it with AI

  1. She opens the workbook and selects the Copilot icon at the bottom right of Excel. Copilot can suggest new calculated columns, single-cell formulas, and lookup columns. [69]
  2. She requests a profit margin column, specifying the product, sales price, and cost columns by name. She asks it to retrieve the quantity matching the product code from the stock sheet; the official example uses an XLOOKUP-type lookup. [69]
  3. While typing = in another cell, she reviews the formula completion suggestion and its explanation. Suggestions can be managed under File > Options > Copilot. [73]
  4. She manually recalculates the result for one product, compares the stock quantity with the source sheet, then selects the formula cell and says, “Explain the formula in the selected cell.” [69]

Example prompt

Add a profit margin column using the product, sales price, and cost columns {in the sales table}. Create a lookup column that retrieves the stock quantity matching the product code {on the stock sheet}. Explain the logic of the formulas you added briefly and simply.

Evidence and real-world example

No published independent case study was found for this persona and file workflow. The basis is Microsoft’s official pages documenting formula columns, XLOOKUP-type lookups, and formula explanations. [69] [73]

Points to consider

Microsoft requires Copilot outputs to be reviewed and verified. [69] Formula completion may depend on the Excel version, account, and organizational access; if the menu is not visible, check Copilot availability. [73]

Sources for this scenario

  1. Get data insights with Copilot in Excel (support.microsoft.com)
  2. Turn Copilot formula suggestions on or off in Excel (support.microsoft.com)