Excel

I need to add values from groups of cells

This is probably not something new to you, but it does explain how Chat can be used to answer your questions in Excel. Here we asked Chat to write us a formula based on our needs for the given situation. This format of question and response will be used for all of our examples.

Here is the prompt we used:

Write an excel formula that adds up all values from B3 to B38

Chats reply:

The formula to add up all values from B3 to B38 in Excel would be:

=SUM(B3:B38)

This formula will add up all the values in the range of cells B3 to B38 and return the total sum. You can adjust the cell range in the formula based on your specific needs.

I have a multi catagory summing issue

Let's say you have a table of employee information that includes each employee's name, ID number, department, and salary. You have a separate sheet where you want to calculate the total salary for each department.

Here is the prompt we used:

I have a problem in Excel, I have a table of employee information that includes each employee's name, ID number, department, and salary. I have a separate sheet where I want to calculate the total salary for each department. Can you write me a formula for that?

Chats reply:

Yes, you can use the SUMIF formula to calculate the total salary for each department. Here's how you can do it:

  1. First, go to the sheet where you want to calculate the total salary for each department.

  2. In the first column, list all the departments.

  3. In the second column, enter the following formula:

=SUMIF('Employee Information'!C:C,A2,'Employee Information'!D:D)

In this formula, 'Employee Information' is the name of the sheet where you have the employee information table. C:C is the column that contains the departments, A2 is the first cell in your new sheet that contains a department name, and D:D is the column that contains the salaries.

  1. Drag the formula down to apply it to all departments.

The formula will calculate the total salary for each department by adding up all the salaries for each employee in that department.

I need to match two sets of data

Let’s say you have a large dataset that contains information about customers, including their names, email addresses, phone numbers, and order numbers. You have a separate sheet where you want to display the customer's name, email address, and phone number based on the order number, and you want that to work in Excel.

This is the prompt we used:

I have a large dataset that contains information about customers, including their names, email addresses, phone numbers, and order numbers. I have a separate sheet where I want to display the customer's name, email address, and phone number based on the order number. Can you write me an Excel formula for that to work?

Chats reply:

Yes, you can use the INDEX MATCH formula in Excel to retrieve the customer information based on the order number. Here is an example formula you can use:

=INDEX(customer_data_range,MATCH(order_number,order_number_range,0),column_number)

In this formula, "customer_data_range" refers to the range of cells that contain the customer data, including their names, email addresses, and phone numbers. "order_number" refers to the specific order number you are searching for, and "order_number_range" refers to the range of cells that contain the order numbers.

You'll need to replace "column_number" with the appropriate column number for the customer information you want to retrieve. For example, if you want to retrieve the customer's name, email address, and phone number (in that order), you would use 1, 2, and 3 as the column numbers, respectively.

Here is an example formula using column numbers:

=INDEX(customer_data_range,MATCH(order_number,order_number_range,0),1) & ", " & INDEX(customer_data_range,MATCH(order_number,order_number_range,0),2) & ", " & INDEX(customer_data_range,MATCH(order_number,order_number_range,0),3)

This formula will display the customer's name, email address, and phone number in the format "Name, Email, Phone".

I need to assign a score to my data

Let’s say you have a table of student grades for a class, and you want to calculate the final letter grade for each student based on their numerical score.

This is the prompt we used:

I have a table of student grades for a class, and I want to calculate the final letter grade for each student based on their numerical score. Can you write me a formula in Excel to help me?

Chats reply:

Yes, I can help you with that. Here's an Excel formula you can use to calculate the final letter grade for each student:

=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))

Assuming the numerical scores are in column A and the first row with data is row 2, you can place this formula in another column (e.g. column B) and then copy it down for each student to get their final letter grade.

This formula uses nested IF statements to assign a letter grade based on the numerical score. The first IF statement checks if the score is greater than or equal to 90, and if so, assigns an "A" grade. If not, it moves to the next IF statement, which checks if the score is greater than or equal to 80, and assigns a "B" grade if so. This process continues through the remaining IF statements until it reaches the final "F" grade if none of the previous conditions are met.

You can modify the numerical cutoffs and letter grades to fit your specific grading system, if needed.

We know that Excel is complicated, hell even the most talented Excel users encounter problems that take lengthy research sessions to solve. Here are a few of our favorite prompts that have made time-saving equations for just you.

It is important to note that we are just using chat to find a formula that could be derived on your own from various other internet searches. The advantage of using Chat is that you can describe your situation and Chat can find an equation that fits your needs. Chat is smart enough that you can simply describe your issue and Chat can offer up a formula or ask enough clarifying questions that it could then give you a formula. These prompts below are just for opening your mind to the possibilities, none of the prompt structures are all that different.

Excel

I need my data to display differently

Let's say you have a dataset with dates in a column, but the dates are formatted in the default format and you want to display them in a more readable format. You want to add a new column that displays the dates in a format like "January 1, 2022" instead of "1/1/2022".

This is the prompt we used:

I have a dataset with dates in a column, but the dates are formatted in the default format and I want to display them in a more readable format. I want to add a new column that displays the dates in a format like "January 1, 2022" instead of "1/1/2022". Can you write me an Excel formula to solve this?

Chats reply:

Yes, you can use the TEXT formula in Excel to format the dates in a more readable format. Here's an example formula that you can use:

=TEXT(A2,"MMMM D, YYYY")

In this formula, A2 refers to the cell that contains the original date that you want to format. The "MMMM D, YYYY" argument tells Excel to format the date as the full month name, followed by the day of the month, followed by the four-digit year.

You can copy this formula down to apply it to all of the dates in your dataset. Just be sure to update the cell reference to the correct column and row for each date.

Combine data from multipule colums

Suppose you have a list of first names in one column and last names in another column. But you want to create a single list by combining their first and last names.

This is the prompt we used:

I have a list of first names in one column and last names in another column. I want to create a single list by combining their first and last names. Can you write me an Excel formula to solve this?

Chats reply:

Sure, you can use the CONCATENATE function in Excel to combine the first and last names into a single column. The formula would be:

=CONCATENATE(A2," ",B2)

Assuming that the first name is in column A and the last name is in column B, this formula combines the two columns with a space in between to create a full name.

Alternatively, you can use the "&" operator, which is a shortcut for the CONCATENATE function. The formula would be:

=A2&" "&B2

This produces the same result as the CONCATENATE formula. Just make sure to adjust the cell references (A2 and B2) to match your specific data.

Think for yourself

This is not meant to be a copy-and-paste guide for you to solve all of your Excel problems. This is meant to be an inspiration for you to understand how Large Language Models (LLMs) can be used to solve situations that are time-consuming.

Excel is not like other tasks for LLMs, it is very easy to state what your problem is and have the LLM solve your issue with little to no troubleshooting. This section is intentionally short because the LLM works so well at this task and requires very little expertise to master its Excel powers.

Trouble shooting

If you are not happy with the formula that the Large Language Model (LLM) gave you, tell it! Tell the model that it didn’t solve your problem and what ended up happening, and the model will readjust and then make a new formula that then meets your needs.