Hands on: Copilot Pro for Excel review

Unlike the other Copilot Pro tools, Copilot for Excel is labelled prominently as “beta”. But even in this qualified state, it has the promise of being a game-changer for anyone who needs to work with data but doesn’t want to become an expert in writing formulas, working out the best way to pivot data or spotting trends in large data sets.

Copilot for Excel exists in the toolbar, but sometimes it’s greyed out. That’s because it only works on . xlsx or .xlsm files saved in OneDrive or SharePoint. When the button is green, hitting it allows you to write natural language instructions to create formulas, analyze data or highlight cells according to whatever criteria you want. If you’ve ever struggled with creating a complex formula (or even a simple one), you’re going to love it.

The first thing to note is that Copilot only works within tables; if your data is unstructured, it won’t let you do anything with it. This probably isn’t a big deal for most people (every serious Excel user I have known has lived or died by tables), but it’s a limitation that may affect some users of large and disparate data sets.

If your data is in a table, though, Copilot is miraculous. You can ask simple queries, such as how much you spent between two dates, or what categories you spend the most on. It’s also great at creating graphs: just type, “make a pie chart showing expenditure based on month”. And you can manipulate existing data, say by asking it to use conditional formatting to highlight specific ranges of numbers, something that’s easy to get wrong for people who aren’t Excel experts.

You can also use Copilot to look for outliers in the data and highlight them, which helps if you’re trying to clean up a dataset or are finding results just aren’t what you think they should be. If you’re using a big dataset, that’s a wonderful time saver. It’s nothing that you can’t do with Excel’s existing tools – but for users who don’t live in Excel, it would involve quite a bit of searching online to know where to start.

Where the real power kicks in is when you ask it to make formulas for you. I created a simple table of expenditure for a small business, tracking spending across a range of categories. But I also wanted to have a column indicating the running total of my expenditure. This isn’t an easy formula to make if you don’t know much about Excel because it involves a SUMIF function that’s based on the row’s date, comparing it to others in the table.

I asked Copilot to “add formula columns to summarize total expenditure so far in this date sequence”, and within seconds it had created a formula and added it to the end of the table.

This kind of formula creation is going to save users of Excel hours, and if you regularly work with data but wouldn’t call yourself an expert, it’s probably worth the money on its own. Even on occasions where I wanted to do something that I knew how to do, I found myself using Copilot instead because it created better results than the various hacks, shortcuts and cheats that I have learned over the years.

We also ranked the best customer relationship management software for small businesses.