Excel Copilot Use Case: Streamlining Complex Summaries and Formula Creation
Inefficiencies in formula creation
Aggregation tasks (formula creation) in administrative and accounting work become inefficient due to multiple factors.
First, there is the cognitive load involved in selecting the appropriate function. Choosing between functions like SUMIFS, COUNTIFS, or VLOOKUP based on the specific purpose requires a certain level of knowledge and experience. This selection process itself increases the time required for the task.
Second, there is complexity in setting function arguments. When specifying multiple conditions, failure to accurately understand the order and syntax of each argument can lead to syntax errors or unintended calculation results.
Third, identifying the cause of errors takes time. Even with formula auditing tools, tracing and verifying the logical structure of complex functions remains a burden on the user.
Copilot reduces knowledge barriers and the risk of operational errors by converting natural language instructions into logical formulas.
Prerequisites and Preparation
Using Copilot requires a paid license and saving files to a cloud environment (OneDrive/SharePoint).
If available, “Copilot” will appear on the Home tab; click it.
Practical Prompt Design and Execution Results
When requesting Copilot to create a formula, clearly specifying the “purpose” and “conditions” is essential for obtaining efficient results. Specific examples are shown below.
Aggregation by Multiple Conditions
Example Prompt:
“Generate a formula to sum the ‘Sales Amount’ in the ‘Performance Data’ sheet, where the condition is that the ‘Department’ is ‘Accounting’ and the ‘Status’ is ‘Completed’.”
The formula is generated based on the sheet contents.
Example of Generated Formula:
=SUMIFS(F2:F11, C2:C11, "Accounting", D2:D11, "Completed")
You may use the proposed formula as is, or you may request modifications.
Count by Date Range
Example Prompt:
“Create a formula to count the number of records in the ‘Contract Date’ column of the ‘Contract List’ sheet where the value is ‘March 1, 2025 or later’.”
Example of Generated Formula:
Formulas using serial numbers and date strings are listed.
=COUNTIF( 'Contract List'!B2:B11, ">=45717")
=COUNTIF('Contract List'!B2:B11, ">=2025/3/1")
Data Search and Extraction
Prompt Example:
“Generate a formula that searches the ‘Unit Price’ column in the separate ‘Product Master’ sheet using the ‘Product ID’ column in the ‘Order’ sheet as the key. Suppress error messages if no match is found.”
Generate composite functions all at once, embedding error handling logic alongside the implementation of search processing. This eliminates the need to build multiple functions incrementally.
Example of Generated Formula:
=IFERROR(VLOOKUP(A2,'Product Master'!A:C,3,FALSE),"")
=IFERROR(XLOOKUP(A2,'Product Master'!A:A,'Product Master'!C:C,""),"")
Condition-Based Evaluation
Prompt Example:
“Create a formula that displays ‘Achieved’ in the ‘Sales’ column of the ‘Monthly Summary’ sheet when the value is greater than or equal to the value in the ‘Target’ column, and ‘Not Achieved’ otherwise.”
Example of Generated Formula:
=IF(B2>=C2,"Achieved"," Not Achieved"
Evaluation of Both Opportunities and Risks
Opportunities for Efficiency Improvement
Copilot demonstrates extremely high efficiency in designing function arguments and avoiding syntax errors—tasks that non-engineers often struggle with. This enables advanced aggregation regardless of one's knowledge level of functions.
Specifically, the following efficiency gains can be expected:
- Reduced time spent searching function references
- Reduced time learning argument order and notation
- Reduced trial-and-error time
Verification Requirements and Risks
Formulas generated by Copilot are not always logically correct. Particularly when prompts are ambiguous or data structures are complex, unintended results may occur.
It is essential to always verify generated formulas by manually calculating results using a small sample of data to ensure the logic meets business requirements.
Verification checkpoints include:
- Whether the specified conditions align with business rules
- Whether the calculation results match known samples
- Whether error handling functions appropriately
- Whether the reference range accurately includes the intended scope
Copilot is a tool; the user is the decision-maker. The responsibility to evaluate the validity of generated formulas and apply them to business operations ultimately rests with the user. Understanding this principle is key to appropriately utilizing Copilot as a means of efficiency.