I have previously shown you how to add multiple rows simultaneously.
Copilot in Excel Series:
2024.08.23 - [Copilot] - Copilot in Excel: Useful Prompt (3). If (Calculate condition) (English)
As I continue to research prompts, it seems that a request like "Create a column" is more appropriate than simply asking for calculations, so I plan to focus on that aspect.
Prompt 1. Create a "X" column
I started with the following prompt.
Calculate "Month", based on "Date".
I believe this has transformed into the following form.
Add a column Month. Calculate Month, based on Date.
= Add a Column Month based on Date
= Calculate "Month", based on "Date".
= Create a 'Month' column
Calculate "Month", based on "Date".
The first is presented as follows.
Give me another suggestion based on my prompt
Give me another suggestion based on my prompt
I think it might be a form where the beginning is omitted, as shown below.
Add a column Month. Calculate Month, based on Date.
The previous prompt execution history might have an influence, but the prompt below produces almost the same result.
Add a column Month. Calculate Month, based on Date.
= Add a Column Month based on Date
= Calculate "Month", based on "Date".
= Create a 'Month' column
Prompt 2. Create columns "Title: A, B, C, D". Calculate Condition, Matching Month based on Table1.
As I input many prompts, there were many cases where the following prompt from a previous entry did not function as intended.
In particular, the following prompt did not produce the desired results in many cases.
Calculate Method W, X, Y, Z. Matching Name, Quarter based on Table1
This time, I will proceed with a slight change. It has been confirmed that using the "Create Columns" prompt results in higher accuracy.
Create columns "Department: Global Sales 1, Global Sales 2, Internal Sales 1, Internal Sales 2". Calculate Sales Performance, Matching Month based on Table1.
Requesting to add the desired columns (up to a maximum of 4), along with additional explanations or examples, increases the accuracy.
Clicking "Hide explanation" allows you to check the explanation of the formula.
Check the results.
Let's proceed in the same pattern as follows.
Create columns "Method: W, X, Y, Z". Calculate Sales Performance, Matching Month based on Table1.
Check the formula explanation to ensure it matches the intended content.
It is confirmed that the 4 columns are added as intended.
Proceed with the addition and verify the results.
Prompt 3. Create 4 columns "A, B, C, D". Provide the basis, location, and definition regarding the requested matter.
There is no correct answer in prompts. The only solution is how well you present it for Copilot to understand. This time, I will try entering the prompt in a different way.
Create 4 columns "Global Sales 1, Global Sales 2, Internal Sales 1, Internal Sales 2".
Each column calculate monthly sales performance by Department.
The performance must be matched based on the month of Table1.
I will generate 4 columns.
Check if it aligns with my intended purpose.
Click on the formula to review it once more for confirmation.
Let's calculate the monthly sales performance by method using the same approach.
Create 4 columns "W, X, Y, X".
Each column calculate monthly sales performance by Method.
The performance must be matched based on the month of Table1.
It can be confirmed that the columns were generated following the same pattern.
Click on the formula to verify it.
It seems that calculating the quarter isn't working well. Pre-inserting the quarter into Table1 lowers the difficulty. Therefore, the structure of the Source Table is crucial when using Copilot.
Let's see what happens when we simply change "Monthly" to "Quarterly."
Create 4 columns "Global Sales 1, Global Sales 2, Internal Sales 1, Internal Sales 2".
Each column calculates quarterly sales performance by Department.
The performance must be matched based on the month of Table1.
Looking at the results, there is no part where the month is calculated as a quarter.
This time, I added more detailed explanations.
Create 4 columns "Global Sales 1, Global Sales 2, Internal Sales 1, Internal Sales 2".
Each column calculates quarterly sales performance by Department.
The performance must be matched based on the quarter of Table1.
There is no quarter column in Table1. The quarter must be calculated based on the month.
It can be observed that the quarter definition was added and calculated correctly. However, only one column was added.
I want to create 4 columns
It only generates the formula.
Create 4 columns using the provided formula.
Still, the result was just one.
When proceeding as below, it suggests other columns. Calculating multiple rows felt quite challenging. Perhaps it's because this is still in the preview stage
Give me another suggestion based on my prompt
Give me another suggestion based on my prompt
By adding one at a time, I was able to complete it as shown below.
It would be great if things were clearer, but for now, offering various directions seems to be the best approach.