For $20 a month, Microsoft’s Copilot Pro is designed to integrate seamlessly with Microsoft 365, providing AI-powered features across various applications in the suite. If you are an Excel user, Copilot Pro allows you to perform tasks such as converting data into a table, creating formulas, analyzing data, sorting and filtering information, generating charts, and more. Here’s how you can get the most out of Copilot Pro.

To get started, you’ll need a Microsoft 365 Personal or Family subscription. The Personal Edition, priced at $70 per year, is tailored for one person using up to five devices. On the other hand, the family edition, which costs $100 per year, is designed for up to six people on as many as five devices. The core applications in the suite include Word, Excel, PowerPoint, Outlook and OneNote.

Next, if you don’t already have one, you’ll need to subscribe to Copilot Pro. To do this, visit the Copilot Pro website, click the “Get Copilot Pro” button, confirm your subscription and complete the payment process. The Pro version will become active the next time you use Copilot on its website, mobile app or Windows.

Third, in order to use Copilot in Excel, you must meet certain requirements. Make sure your Excel files are saved in .xlsx or .xlsm format and the data should be formatted as an Excel table (I’ll walk you through this process). Also, AutoSave must be enabled and you cannot work with local files; your spreadsheets must be stored in OneDrive or Microsoft 365 SharePoint.

Guide to using Microsoft Copilot Pro in Excel

Start Excel and open a spreadsheet:

To get started, launch Excel and open an existing spreadsheet that contains the data you want to analyze. If the file is stored online, click the File menu, select Open, and select OneDrive or SharePoint. Navigate through the folders and files that contain your spreadsheets, then select the specific one you want to use.

You can also create or open a local file. If you want to save it online, click the File menu, choose Save As, and then save it to a folder in OneDrive or SharePoint. This ensures that your file is stored in the cloud for use with Copilot.

Enable AutoSave in Excel:

Now make sure that AutoSave is enabled. Click the File menu, select Options, and in the Options window, select Save. Check the box for “Auto-save files stored in the cloud by default in Excel”. While you’re there, confirm that the file format is set to .xlsx if it’s a standard Excel spreadsheet. Click OK to close the options window. This ensures that your changes are automatically saved to the cloud.

Convert data from Excel to a table:

Image credit: ZDNET

To allow Copilot to work with your data, you need to convert it to a table. Select all the cells you want to include and click the Copilot button on the ribbon. A message may appear asking if you want to convert this range of data to a table. Click the Convert button and the data will be formatted into a table with a header row at the top. This ensures that Copilot can effectively analyze and manipulate your data.

Gizchina News of the week

Add formula to Excel Sheet:

Excel Copilot
image credit: ZDNET

After you convert the cells into a formatted table, Copilot will display its sidebar on the right and offer various queries at the top. Suppose you have a table with numbers in each cell and you want to add a column to sum the numbers in each row. Select the “Add formula columns” suggestion. In the prompt, describe your request. For example, you can enter: “Add the numbers in each row and create a column to show the totals.” This helps Copilot understand your specific task and generate the appropriate formula.

Copilot will then generate the formula in response to your request. Review the formula to make sure it looks right. If it does, hover over the Insert Column button to preview the column, and then click Insert Column to add it to the sheet. This allows you to check the formula before including it in your spreadsheet.

Format cells:

Then, if you want to apply a certain format to cells based on specific criteria, click the Highlight suggestion. For example, if you want to bold all cells containing a certain value, you can type: “Bold all cells with a value between $94 and $96.” Type this text at the prompt, and then submit your query. Copilot will then perform the formatting based on the criteria you provided.

Copilot will perform your action and give an explanation of what it did. If your request was completed successfully, that’s great. However, if it doesn’t meet your expectations, you can click the Cancel button and try again. This allows you to make adjustments and refine your requests until you achieve the desired results.

Sort data:

If you want to sort the data in a certain way, you can ask Copilot for assistance. For example, if you want to sort the values ​​in a particular column in descending order, type: “Sort the data in column A in descending order” at the prompt. Copilot will then execute your query and sort the data accordingly.

Filter your data:

Excel Copilot
image credit: ZDNET

To narrow down the data displayed in the table, you can use filtering. For example, if you want to show items in column E with values ​​of $40 or higher, type something like: “Show items in column E with values ​​of $40 or higher” at the prompt. Copilot will then apply the filter to show only the values ​​that match the criteria you specify.

Analyze Excel data with Copilot

Excel Copilot
Image credit: ZDNET

To further analyze the data, click the Analyze button located on the ribbon. Scroll down the sidebar to explore various insights already provided by Copilot. This allows you to gain valuable information and insights from the analysis of your data.

You can either click on one of the suggested queries or enter your own. For example, you can ask Copilot to provide information about specific data. Depending on the nature of the data, you can also insert a pivot table or chart to visualize specific values ​​and information. Copilot will then help you generate the desired analysis or visualization based on your query.

Rebuttal: We may be compensated by some of the companies whose products we talk about, but our articles and reviews are always our honest opinions. For more details, you can view our editorial guidelines and learn how we use affiliate links.

Master Excel Formulas and Data Analysis with Copilot Pro AI