Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web

Imagine you have a list of things, like a price list of toys for example.

A sample price list of toys in Microsoft Excel.

You'd like to add a column that shows how many of each item you have in stock, but you don't want to have to type all of that manually. Fortunately, you have a table on another sheet that contains the current inventory by product ID.

A sample table of product inventory in Microsoft Excel.

Now you just need to have Excel look up the stock based on the product ID. Copilot can help. 

  1. Select the Copilot  The Copilot button button on the ribbon.

  2. In the prompt box ask Copilot to create your lookup column. Try a prompt like this:Create a column that looks up the quantity in stock for each item from the inventory sheet. or Add a stock column to this table based on the value in the other table in my workbook.

  3. Copilot will look over your data and recommend a formula that looks up the stock and inserts it in your price list.Copilot in Excel creates an XLookup formula based on a prompt from the customer asking for a column that looks up inventory data in another sheet.

  4. Copilot has recommend an XLookup formula that looks up the stock quantity from the Inventory sheet and inserts it here on the price list. Select +Insert Column to add the column to your price list sheet.

Just like that you've got a quantity in stock column on your price list, and because it's an XLookup from the other sheet as the inventory numbers on that other sheet change the inventory numbers on your price sheet will change automatically as well.A price list of toys including a column that shows the current quantity of each item that is in stock.

Learn more

Generate formula columns and rows with Copilot in Excel​​​​​​​

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.