Master Excel Formulas and Data Analysis with Copilot Pro AI

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

To get started, you’ll need a subscription to Microsoft 365 Personal or Family. 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, costing $100 per year, is designed for up to six people across 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 on 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, in its mobile app, or in Windows.

Thirdly, to use Copilot in Excel, you need to meet certain requirements. Ensure your Excel files are saved in the .xlsx or .xlsm format, and the data must be formatted as an Excel table (I’ll guide you through this process). Additionally, AutoSave must be enabled, and you can’t work with local files; your spreadsheets need to be stored in OneDrive or Microsoft 365 SharePoint.

Guide to Use Microsoft Copilot Pro in Excel

Launch 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 on the File menu, choose Open, and select OneDrive or SharePoint. Navigate through the folders and files that house your spreadsheets, then choose the specific one you wish to use.

Also, you can create or open a local file. If you want to save it online, click on 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 Auto save in Excel:

Now, make sure AutoSave is enabled. Click on the File menu, choose Options, and in the Options window, select Save. Check the box for “AutoSave 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 Excel Data in Table:

To enable Copilot to work with your data, you need to convert it into 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 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. This ensures that Copilot can effectively analyze and manipulate your data.

Add Formula to Excel Sheet:

Once you’ve converted the cells into a formatted table, Copilot will show its sidebar on the right and suggest various requests at the top. Suppose you have a table with numbers in each cell and want to add a column to total the numbers in each row. Choose the suggestion for “Add formula columns.” In the prompt, describe your request. For instance, you might type: “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 ensure it looks correct. 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 verify the formula before incorporating it into your spreadsheet.

Cell Formatting:

Following that, if you wish to apply a particular format to cells based on specific criteria, click on the suggestion for “Highlight.” For instance, if you want to bold all cells containing a particular value, you could type: “Bold all cells with a value between $94 and $96.” Enter this text at the prompt and then submit your request. Copilot will then execute the formatting based on the criteria you provided.

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

Data Sorting:

If you want to sort the data in a specific 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 request and arrange the data accordingly.

Filtering your Data:

To narrow down the data displayed in the table, you can utilize filtering. For instance, 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 display only the values that meet your specified criteria.

Analyzing Excel Data with Copilot

To analyze the data further, click on 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 your data analysis.

You can either click on one of the suggested requests or enter your own. For instance, you can ask Copilot to provide insights into specific data. Depending on the nature of the data, you can also insert a pivot table or chart to visualize particular values and information. Copilot will then assist you in generating the desired analysis or visualization based on your request.