Formulas and functions form the core of Excel, and if you believed AI would eliminate the need for you to ever craft them personally – you’re only partially correct. Let’s delve into how Copilot can greatly simplify the process of creating and debugging formulas and functions.
To follow along, download the exercise file below:
Ensure this file is properly configured to work with Copilot before proceeding.
The first step I’ll take in Copilot is to select the option to add formula columns. This will prepare Copilot to assist us specifically with this task.
Let’s get started. I’m not fond of the Status column being entirely in uppercase. Therefore, I’ll request a column formula to convert it to proper case by saying, “Add a column to convert Status to proper case.”
Copilot will return a formula that we can either directly copy or insert into the table. Since we are working within a table, these formulas use the structured references unique to tables, so it’s essential to become familiar with them.
Additionally, there is an “Explain formula” option available, which offers a chance to learn more about how the formula functions. Although the formula provided is about as simple as it gets, this feature is quite beneficial for understanding how various functions operate.
It’s important to note that the original Status column remains in the table, so to fully remove it further action is required. This may involve utilizing Excel skills beyond those related to Copilot.
Let’s delve deeper: The Size column lists measurements in ounces. My goal is to convert these measurements to cups. I am uncertain about the exact conversion rate and am curious to see if Copilot can assist with this. Therefore, I will make the following request:
“The Size column is in ounces. Could you convert it to cups and label the new column as Size (cups)?”
Indeed, Copilot can comprehend such conversions and offers calculations to facilitate them.
Imagine, instead of creating a formula, we want Copilot’s help in deciphering one. Presently, our dataset includes a Proposed Price column filled with various IF() statements. Understanding these can be quite puzzling, and we’re aiming to make sense of the formula provided below.
At the time of writing, Copilot may become confused if asked to explain the formulas of a given column directly. However, if you input the formula and request an explanation, you are likely to receive a comprehensive answer.
Finally, I aim to aggregate data from one of the columns. For example, I intend to calculate the average price across all items. By instructing Copilot with the query “What is the average of the price?”, a suggested PivotTable is generated. Once added to a new sheet, the answer is presented. It is noteworthy that Copilot frequently recommends PivotTables over conventional formulas and functions to produce results.
In conclusion, while the days of manually crafting formulas may not be completely over, the process will undergo a transformation. With Copilot, much of the heavy lifting is handled, allowing you to oversee operations similar to a supervisor monitoring an assistant skilled in both formulas and PivotTables.
Do you have any questions about using conditional formatting with Copilot in Excel? Or is there something else you’d like to explore with Copilot in Excel? Please let me know in the comments.
Leave a Reply