Why you can trust ZDNET : ZDNET independently tests and researches products to bring you our best recommendations and advice. When you buy through our links, we may earn a commission. Our process

'ZDNET Recommends': What exactly does it mean?

ZDNET's recommendations are based on many hours of testing, research, and comparison shopping. We gather data from the best available sources, including vendor and retailer listings as well as other relevant and independent reviews sites. And we pore over customer reviews to find out what matters to real people who already own and use the products and services we’re assessing.

When you click through from our site to a retailer and buy a product or service, we may earn affiliate commissions. This helps support our work, but does not affect what we cover or how, and it does not affect the price you pay. Neither ZDNET nor the author are compensated for these independent reviews. Indeed, we follow strict guidelines that ensure our editorial content is never influenced by advertisers.

ZDNET's editorial team writes on behalf of you, our reader. Our goal is to deliver the most accurate information and the most knowledgeable advice possible in order to help you make smarter buying decisions on tech gear and a wide array of products and services. Our editors thoroughly review and fact-check every article to ensure that our content meets the highest standards. If we have made an error or published misleading information, we will correct or clarify the article. If you see inaccuracies in our content, please report the mistake via this form.


How to use Copilot Pro AI to generate formulas and analyze data in Excel

Microsoft's Copilot Pro can provide AI-infused assistance for your Excel data and spreadsheets.
Written by Lance Whitney, Contributor
Person typing on laptop with charts and graphs
dowell/Getty Images

At $20 per month, Microsoft's Copilot Pro integrates with Microsoft 365 to offer AI-powered features for different apps in the suite. People who use Excel can use Copilot Pro to convert data into a table, create formulas, analyze data, sort and filter information, generate charts, and more. Here's how you can put Copilot Pro to work for you.

First, you'll need a subscription to Microsoft 365 Personal or Family. Priced at $70 per year, the Personal edition is geared for one person using up to five devices. At $100 per year, the Family edition is designed for up to six people using as many as five devices. The core apps in the suite include Word, Excel, PowerPoint, Outlook, and OneNote.

Also: How to use Copilot (formerly called Bing Chat)

Second, you'll need a subscription to Copilot Pro if you don't already have one. To sign up, head to the Copilot Pro website. Click the Get Copilot Pro button. Confirm the subscription and the payment. The Pro version will take effect next time you use Copilot on its website, in its mobile app, or in Windows.

Third, to use Copilot in Excel, you must meet certain requirements. Your Excel files must be saved in the .xlsx or .xslm format, and the data must be formatted as an Excel table (I'll show you how to do that). Also, AutoSave has to be enabled and you can't work with local files; your spreadsheets must be stored in OneDrive or Microsoft 365 SharePoint.

How to use Copilot Pro AI for Excel

1. Launch Excel and open a spreadsheet

Launch Excel and open an existing spreadsheet with data to analyze. To open a file stored online, click the File menu, select Open, and choose OneDrive or SharePoint. Browse through the folders and files containing your spreadsheets and select the one you want to use.

Also: How to use Copilot Pro to write, edit, and analyze your Word documents

Alternatively, create or open a local file. To save it online, click the File menu, select Save as, and then save it to a folder in OneDrive or SharePoint.

Launch Excel and open a spreadsheet
Screenshot by Lance Whitney/ZDNET

2. Enable AutoSave

Next, enable AutoSave if it's not already turned on. Click the File menu and choose Options. From the Options window, select Save and then check the box for AutoSave files stored in the Cloud by default in Excel. While there, ensure the file format points to .xlsx if it's a regular Excel spreadsheet. Click OK to close the Options window.

Enable AutoSave
Screenshot by Lance Whitney/ZDNET

3. Convert data into a table

For Copilot to work with your data, you must convert it into a table. Select all the cells you want to include and click the Copilot button on the Ribbon. A message might appear and ask if you want to convert this data range into a table. Click the Convert button and the data will be formatted into a table with a header row at the top.

Convert data into a table
Screenshot by Lance Whitney/ZDNET

4. Add formula column

After converting the cells into a formatted table, Copilot will display its sidebar on the right and suggests several requests at the top. Let's say you have a table with numbers in each cell and want to add a column to total the numbers in each row. Select the suggestion for Add formula columns. At the prompt, describe your request. For example, you might type: "Add the numbers in each row and create a column to show the totals."

Add formula column
Screenshot by Lance Whitney/ZDNET

In response, Copilot creates the formula. Review the formula to make sure it looks right. If it does, hover over the button for Insert column to preview the column and then click Insert column to add it to the sheet:

Insert column
Screenshot by Lance Whitney/ZDNET

5. Format cells

Next, you might want to apply a specific format to cells that meet certain criteria. Click the suggestion for Highlight. Let's say you want to bold all cells containing a certain value. You might type: "Bold all cells with a value between $94 and $96." Enter that text at the prompt and then submit your request.

Also: 5 ways college students can use Copilot for Microsoft 365

Copilot carries out your action with an explanation of what it did. If your request was carried out successfully, great. If not, you can click the Undo button and try again.

Format cells
Screenshot by Lance Whitney/ZDNET

6. Sort data

You can ask Copilot to sort the data a certain way. Maybe you want to sort the values in a specific column in descending order? At the prompt, type: "Sort the data in column A in descending order." Copilot will then carry out your request.

Sort data
Screenshot by Lance Whitney/ZDNET

7. Filter data

Next, you might want to see only certain data in the table, which you can do by filtering. At the prompt, type something like: "Show items in column E with values of $40 or higher." Copilot will then filter the table to show only the values that meet your criteria.

Filter data
Screenshot by Lance Whitney/ZDNET

8. Analyze data

Finally, you can analyze the data. Click the Analyze button on the Ribbon. Scroll down the sidebar to view different insights already offered by Copilot.

Analyze data
Screenshot by Lance Whitney/ZDNET

Click on one of the suggested requests or enter one of your own. For example, you can ask Copilot to provide insights into particular data. Depending on the data, you can also insert a pivot table or chart to visualize specific values and information:

Insert a pivot table or chart
Screenshot by Lance Whitney/ZDNET
Editorial standards