June 2023 – AI Dare

The dreaded or loved? Excel spreadsheet, oh how I struggle with thee, at least sometimes, and if you are here I assume you do too. So can you use ChatGPT to help you with Excel formulas? ChatGPT is well-versed in Excel functions and can assist with creating formulas. Clear formatting, personas, and adding context will improve the accuracy and implementation of the formulas. So there you have it, ChatGPT rocks when it comes to creating Excel formulas, but you do need to be careful to ask it in a way that it understands. I also suggest testing the formula that it gives you to make sure that it is what you need. (I'll cover exactly how to do that in the article.) How To Format Your Excel Questions I prefer to teach by example so let's dive right into an example. Example: Let's suppose that you want to know how many numbers there are in a particular column, but you don't remember the formula. First, we need to determine what we want for an OUTPUT. This is an old programmer's trick to make sure we think about where we are going before we start. In this case, I want to my OUTPUT to be a single number counting the number of numbers in column B from row 1 to 30 I want to store my OUTPUT in cell B31 Ok That seems pretty straightforward lets ask ChatGPT and see what it says. ChatGPT please act as an Excel teacher. I would like you to help me to create the following formula. I want to my OUTPUT to be a single number counting the number of numbers in column B from row 1 to 30 I want to store my OUTPUT in cell B31 To create the desired formula, follow these steps: Select cell B31, where you want to store the output. Enter the following formula into cell B31: =COUNT(B1:B30) Press Enter to finalize the formula. After following these steps, cell B31 will display the count of numbers in column B, specifically from row 1 to row 30. Now we put it back into our Excel spreadsheet and test it. This example has a small number of rows and it is pretty easy to go and count how many numbers there are. Sure enough, there are 26. That brings up our principles for out prompt. Principle #1: Use "Personas" with ChatGPT to give it additional focus. I specifically asked ChatGPT to act as an Excel teacher, why? I did a small study on asking ChatGPT to take on different personas when answering. I found that the answers that it produced with a persona were, more systematic, clearer, and had more depth (you can see an article about that here). In this case I asked ChatGPT to take on the persona of an Excel teacher, why? Teachers explain things simply Teachers are more likely to "show the steps" Teachers want to teach the material to make it clearer. Using a teaching persona with ChatGPT will make it easier to implement a formula. Principle #2: Start with the End in Mind and be very clear. If you cannot describe what you want for the output then you won't be able to get the answer from ChatGPT. There will be times when this is difficult, I'll go over how we'll generate some additional context to give ChatGPT in these cases. Principle #3: Use a small subset of your data to test a new formula If I really wanted to know how many numbers there were out of 1,000 rows testing to make sure the formula works would be a lot of work! Test your formulas first on just a few rows if you can to make sure that they are working before you try a larger dataset. Harder Excel Problems Great! I am glad that you are still following along. Let's suppose that I am asking ChatGPT about something that I am not even sure Excel can do? How am I going to explain that to ChatGPT? Well, first we are going to ask ChatGPT if Excel can do that. Let's take an example. I have the spreadsheet that you saw before. I want Excel to transfer all the rows that have a number over to a new tab on my spreadsheet, Lets ask ChatGPT if that is possible. ChatGPT please act as an Excel teacher. I have a spreadsheet with data on one sheet and I want to transfer it to a new sheet with a formula. Can I do that? (I only want to transfer the rows that meet a certain condition). Yes, you can transfer specific rows from one sheet to another based on certain conditions using formulas in Excel. Here's a step-by-step guide to achieve this: Create a new sheet where you want to transfer the data. Identify the condition that determines which rows should be transferred. Let's say you want to transfer rows where the value in column A is greater than 10. In the first cell of the new sheet (let's assume it's cell A1), enter the following formula: =IF(Sheet1!A1>10, Sheet1!A1, "")Replace "Sheet1" with the name of your original sheet. This formula checks the condition for the first row of data in column A and transfers the value if the condition is met. Otherwise, it leaves the cell empty. Drag the formula across the row to cover all the columns that you want to transfer. Now, select the cell with the formula (A1) and drag it down to cover all the rows of data you want to transfer. The formula will adjust automatically for each row. The new sheet will now display the rows from the original sheet that meet the specified condition. Any rows that don't meet the condition will have empty cells in the new sheet. Heck, that answer was so good I should have just asked it the entire question to begin with! Let's keep on plodding along though and take a look at that answer to make sure we have our Excel sheet prepped. Ok, first thing I see is that I need to know the Sheet names. I took a look and it looks like we'll be using the super original Sheet1 and Sheet2. Now I need to determine what will determine if I want the formula to transfer the data or not for a given row. I am going to choose to only transfer rows where there is a number in column B. So let's create a prompt and use the answer we got before to properly name our sheets so we get the formula we need. ChatGPT please act as an Excel teacher. I have an Excel spreadsheet with data on one sheet "Sheet1" and I want to transfer it to a new sheet "Sheet2" with a formula. I want to transfer a row only if there is a number in the B column. for a given row What formula should I use? To transfer rows from "Sheet1" to "Sheet2" only if there is a number in the B column, you can use the following formula: In "Sheet2," ensure that the data starts from row 2 (assuming row 1 is for headers). In cell A2 of "Sheet2," enter the following formula: =IF(ISNUMBER(Sheet1!B2), Sheet1!A2, "")This formula checks if there is a number in cell B2 of "Sheet1." If true, it transfers the value from cell A2 of "Sheet1" to cell A2 of "Sheet2." Otherwise, it leaves the cell blank. Drag the formula across the row to cover all the columns you want to transfer. Select cell A2 with the formula, and drag it down to cover all the rows of data you want to transfer. The formula will adjust automatically for each row. The "Sheet2" will now display the rows from "Sheet1" where there is a number in the B column. Any rows that don't have a number in the B column will have empty cells in the corresponding row of "Sheet2." Ok, great Now I am seeing a problem with the answer, are you? ChatGPT took it upon itself to assume that I had column headers (which I don't). There is a second problem as well which if you know Excel you probably already noticed. Let's test the formula though and see what happens. I pasted it in where I was supposed to It worked fine for cell A2 on the new spreadsheet, but It did not copy the value for B2 over to the new spreadsheet. We can see the error from the formula, =IF(ISNUMBER(Sheet1!C2), Sheet1!B2, "") That C2 should still be a B2. ChatGPT made a mistake. Let's assume for a moment that we have no idea why our formula failed. Let's describe the problem to ChatGPT and see if it can fix it. Thanks, it looks like there is a problem though, cell B2 on Sheet 2 is blank and it should be a number. Apologies for the confusion. To ensure that cell B2 in "Sheet2" contains a number, you can modify the formula as follows: In cell A2 of "Sheet2," enter the following formula: =IF(ISNUMBER(Sheet1!B2), Sheet1!A2, "") In cell B2 of "Sheet2," enter the following formula: =IF(ISNUMBER(Sheet1!B2), Sheet1!B2, "")This formula checks if there is a number in cell B2 of "Sheet1." If true, it transfers the value from cell B2 of "Sheet1" to cell B2 of "Sheet2." Otherwise, it leaves the cell blank. Drag both formulas across the row to cover all the columns you want to transfer. Select cells A2:B2 with the formulas, and drag them down to cover all the rows of data you want to transfer. The formulas will adjust automatically for each row. Now, both cells A2 and B2 in "Sheet2" will contain the values from cells A2 and B2 of "Sheet1" if there are numbers present. Otherwise, they will remain blank. This works if you copy those 2 cells down and up the rows. It's not my favorite solution because you have to change the formula for each column. There is a better solution let's see if ChatGPT can figure it out. I'll be honest, I thought it would figure it out, but it didn't (after a lot of fussing around). =IF(ISNUMBER(Sheet1!$B1), Sheet1!A1, "") The $ in front of the B tells it to not change the column when I am copying it into other cells. ChatGPT still knew how to do this better than I did and it would have saved me time. However, there is one thing I still have not tried. ChatGPT 4.0! So let's give it a shot ChatGPT please act as an Excel teacher. I have an Excel spreadsheet with data on one sheet "Sheet1" and I want to transfer it to a new sheet "Sheet2" with a formula. I want to transfer a row only if there is a number in the B column. for a given row What formula should I use? In Excel, you cannot directly use formulas to copy data to a different sheet based on criteria such as a condition of a number being in column B. Instead, you can use a combination of Excel functions and tools, such as a helper column and filter, or use VBA (Visual Basic for Applications), Excel's built-in programming language. However, Excel formulas such as INDEX and MATCH, or the newer FILTER and UNIQUE functions can help to create a view of the data on a second sheet that includes only rows where column B contains a number. Let's go step by step for the formula approach. Here's how to do it with the FILTER function (assuming Excel 365 or Excel 2019): Go to "Sheet2" where you want to transfer the data. In the first cell, enter the following formula: =FILTER(Sheet1!A1:D1000, ISNUMBER(Sheet1!B1:B1000), "No match") In this formula: Sheet1!A1:D1000 is the range of cells you want to transfer from "Sheet1" to "Sheet2". Adjust the range according to your data. ISNUMBER(Sheet1!B1:B1000) is the condition that checks if there is a number in column B. If there is a number, the condition returns TRUE; if not, it returns FALSE. "No match" is the value the function returns if there are no rows that meet the condition. You can change this to whatever you want. After you press Enter, Excel will automatically fill the rows in "Sheet2" with the data from "Sheet1" where there is a number in column B. Keep in mind that this is a dynamic array formula. The resulting array is "spilled" to the necessary number of cells automatically. If your version of Excel doesn't support this, you may need to use an older method like INDEX/MATCH or use a helper column. Let me know if you have any other questions about this process. Wow! That was really different, different enough that I am going to have to use a different version of Excel to test it. Let's take a look. This method got rid of all of the rows that did not have a number in column B. It would be better in most circumstances. So kudos to ChatGPT4! So what did we learn? ChatGPT is not a miracle worker ChatGPT makes mistakes You need to test the formulas that it gives you You may need to go back and forth a few times with ChatGPT to get a good result ChatGPT4 may be a good option for some harder problems. Wrapping It Up The journey of untangling the intricacies of Excel formulas is never an easy one, but as we've seen, OpenAI's ChatGPT can act as a formidable guide. Although not perfect, and like any tool requiring our active engagement, ChatGPT brings a strong understanding of Excel functions, making it a valuable assistant for solving tricky spreadsheet problems. When consulting ChatGPT for Excel help, remember three guiding principles: Use Personas: Adding context by asking ChatGPT to act in a specific role, such as an Excel teacher, can often lead to more systematic and clearer answers. Start with the End in Mind: Clearly articulate the desired output. This clarity helps ChatGPT understand and cater to your specific needs more effectively. Test with a Small Data Subset: Before applying a new formula to an entire dataset, test it on a smaller portion of your data. It’s easier to verify the correctness of the formula and saves potential troubleshooting time. We dived into several examples, demonstrating how to ask ChatGPT for help and the importance of iteratively refining and testing the provided solutions. Be it a simple COUNT function or transferring rows based on specific conditions, ChatGPT assisted effectively, albeit with a few errors. Remember, despite ChatGPT's extensive knowledge, it is not infallible. It can make mistakes, and thus, it is crucial to test the formulas it suggests before using them on significant datasets. Interactions with ChatGPT might require a few rounds of clarification and tweaking to ensure that the answers suit your needs. And remember, newer versions of ChatGPT, such as ChatGPT-4, might offer innovative solutions for more complex problems. In conclusion, the journey through the land of Excel need not be a solitary endeavor. With the right approach and a touch of patience, ChatGPT can be a supportive companion, helping you navigate Excel's twists and turns and unveiling the secrets hidden in your data. Happy spreadsheeting!