How to Use Microsoft Copilot in Excel to Work Smarter

Microsoft Excel is one of the most powerful tools in the Microsoft 365 suite — but it’s also one of the most complex. Even experienced users can find themselves hunting for formulas, wrestling with conditional formatting, or searching online for how to perform lookups. With Microsoft Copilot, those time‑consuming tasks become dramatically easier.

In a recent Pro Drive video, Bruce Penson demonstrates how Copilot can simplify everyday Excel work using a sample dataset of food product sales. The video (embedded below) walks through several practical examples.

Categorising Data Automatically

Bruce begins by segmenting product quantities into performance categories. Instead of manually building a nested IF formula, he simply asks Copilot to create one. Copilot confirms the request, generates the correct formula, and inserts a new column directly into the table — instantly categorising each item.

Smarter Conditional Formatting

Next, Bruce uses Copilot to apply colour‑coded conditional formatting to highlight high‑ and low‑value sales. While Excel’s built‑in tools can be fiddly, Copilot applies precise formatting based on his instructions and even updates the colour scheme when asked. It’s a fast, accurate way to add visual insight to your data.

Lookups Made Easy

Finally, Bruce demonstrates how Copilot can perform an XLOOKUP between two tables — even if you’ve never used the function before. Copilot writes the formula, previews the results, and inserts the column automatically.

These examples show how Copilot can save time, reduce errors, and make Excel more accessible for everyone — whether you’re a seasoned analyst or an occasional spreadsheet user.

If you’d like help getting the most from Copilot in Excel or across Microsoft 365, contact Pro Drive via the contact page and our team will be happy to assist.