10X Your Excel Skills with ChatGPT

Posted bykerrystratvertPosted inChatGPT, Excel

CHATGPT IN EXCEL

In this step-by-step tutorial, learn how you can exponentially improve your Excel skills using OpenAI’s ChatGPT artificial intelligence. Write nested functions with ease, calculate the number of unique text values in a list, write a basic invoicing macro, and more, all with just a few clicks and a few basic refinements.

  1. CHATGPT IN EXCEL
  2. GET CHATGPT
  3. USE EXCEL FOR CHATGPT FOR SIMPLE SUM FUNCTION
  4. USE EXCEL FOR CHATGPT FOR PROFIT FUNCTION
  5. USE EXCEL FOR CHATGPT FOR LOOKUP FUNCTIONS
  6. USE EXCEL FOR CHATGPT FOR LEFT AND FIND NESTED FUNCTIONS
  7. USE EXCEL FOR CHATGPT FOR UNIQUE COUNT NESTED FUNCTIONS
  8. USE EXCEL FOR CHATGPT TO WRITE MACROS
  9. VIDEO STEPS
  10. RESOURCES

GET CHATGPT

  1. Visit https://chat.openai.com/ and sign up for an OpenAI account.
    • Setup an OpenAI account by clicking the “Sign Up” button.
    • Select your email or Google or Microsoft account you want to log in with.
    • Allow OpenAI to access your account
    • Verify you are a human
    • Verify your name, email address and phone number
  2. Then you will be able to use ChatGPT with the following landing page.

USE EXCEL FOR CHATGPT FOR SIMPLE SUM FUNCTION

You can use ChatGPT to help find the function you’re looking for. In this case, you will be trying to find a function to add together the revenue across all markets- the Excel SUM function.

  1. Open an Excel workbook to follow along and open tab 1: Sum
  2. Open ChatGPT.
  3. Type an Excel question “Write an Excel formula to add up values in cells B2 to B10” and click Go.
  4. See the response from ChatGPT, in this case “=SUM(B2:B10)”
  5. Copy the function. Open Excel and paste in the ChatGPT response “=SUM(B2:B10)”
  6. Open ChatGPT again and ask “Can you explain how this function works?”
  7. ChatGPT will then respond with how the Excel Sum function works.

USE EXCEL FOR CHATGPT FOR PROFIT FUNCTION

You can use ChatGPT to help find the function you’re looking for. In this case, you will be trying to find a function to compute the profit- the revenue minus the cost.

  1. Open an Excel workbook to follow along and open tab 2: Profit
  2. Open ChatGPT.
  3. Type an Excel question “Write an Excel formula to calculate the profit. The revenue is in cell A2 and the cost is in cell B2” and click Go.
  4. See the response from ChatGPT, in this case “=A2-B2”
  5. Copy the function. Open Excel and paste in the ChatGPT response “=A2-B2”
  6. You now have the profit.

USE EXCEL FOR CHATGPT FOR LOOKUP FUNCTIONS

You can use ChatGPT to help find the function you’re looking for. In this case, you will be trying to find a function to do a vlookup .

  1. Open an Excel workbook to follow along and open tab 3: “Vlookup & Index Match”
  2. Open ChatGPT.
  3. Type an Excel question “Write an Excel formula to find Sugar Cookie in a table and return the price” and click Go.
  4. See the response from ChatGPT, in this case reference information for the vlookup function. It also provides a function “=VLOOKUP(“Sugar Cookie”, A1:Bn, 2, FALSE)” Copy the function.
  5. Copy the function. Open Excel and paste in the ChatGPT response “=VLOOKUP(“Sugar Cookie”, A1:Bn, 2, FALSE)”. Replace the n with the number of rows in the table, in this case 4 to select the entire table- “=VLOOKUP(“Sugar Cookie”, A1:Bn, 2, FALSE)”
  6. Hit Enter and see the correct response “3” that is the price of the Sugar Cookie.
  7. You can also ask ChatGPT if there is another way to answer this question by opening up ChatGPT and asking “Can I use any other functions”
  8. See the response that you can also use the Index and Match functions to do the same Sugar Cookie price lookup.

USE EXCEL FOR CHATGPT FOR LEFT AND FIND NESTED FUNCTIONS

You can use ChatGPT to help find the function you’re looking for. In this case, you will be trying to find a function to get the name from an email address.

  1. Open an Excel workbook to follow along and open tab 4: “Left & Find”
  2. Open ChatGPT.
  3. Type an Excel question “Write an Excel formula to extract all the text before the @ character in cell A2” and click Go.
  4. See the response from ChatGPT, in this case “=LEFT(A2,FIND(“@”, A2)-1)”
  5. Copy the function.
  6. Open Excel and highlight the cells where you want the function to be used and press “CTRL-Enter” to paste in the ChatGPT function.
  7. You see the characters to the left of the @ symbol in the email address, in this case the name, in the column.

USE EXCEL FOR CHATGPT FOR UNIQUE COUNT NESTED FUNCTIONS

You can use ChatGPT to help find the function you’re looking for. In this case, you will be trying to find a function to find the number of unique values in a list.

  1. Open an Excel workbook to follow along and open tab 5: “Unique Count”
  2. Open ChatGPT.
  3. Type an Excel question “Write an Excel formula to count the number of unique values in a list” and click Go.
  4. See the response from ChatGPT, in this case reference information for the vlookup function. It also provides a function “=SUM(1/COUNTIF(A1:A10, A1:A10))” Copy the function.
  5. Copy the function. Open Excel and paste in the ChatGPT response “=SUM(1/COUNTIF(A1:A10, A1:A10))”. Replace A1 with A2 since the value in A1 is the header name and doesn’t need to be included in the list of unique values.
  6. Hit Enter and see the correct response “7” that is the number of unique cookie types.

USE EXCEL FOR CHATGPT TO WRITE MACROS

You can use ChatGPT to help write an Excel Macro.

  1. Open an Excel workbook to follow along and open tab 6: Macro
  2. Open ChatGPT.
  3. Type an Excel question “Write an Excel macro to send emails. Use the following subject “Kevin Cookie Company Invoice”. Use the following text: “You owe the Kevin Cookie Company X.”. Take the value X from column A. Send to the email address listed in column B.” and click Go.
  4. See the response from ChatGPT, in this case an Excel macro that will send the email based on the subject and body information.
  5. Click the copy code option.
  6. Open Excel
  7. Enable macros in excel, by:
    • Right clicking the ribbon to and click the “Customize Ribbon” option on the dialog
    • On the right hand side, select the Developer checkbox.
    • Click OK.
    • YOu will see the new Developer tab.
  8. Click the Developer tab
  9. Open Excel Visual Basic Editor by clicking the “Visual Basic” button on the far left hand side.
  10. Paste in the code from ChatGPT.
    • The code all works perfectly, except you will need to update the “For each cell in Columns(“A”)” line to “For each cell in Columns(“B”)” as the lookup is in the Column B, not A as the code suggests.
  11. Save the macro and close the window
  12. On the developer tab, click the Macros button.
  13. you will now see the new macro “SendEmails” that you can run.
  14. Click Run and now it will send out the emails.

VIDEO STEPS

RESOURCES

6 thoughts on “10X Your Excel Skills with ChatGPT”

  1. Great email, keep up all your hard work. Fan from the beginning, when still at Mike Row Soft, thought that was funny. Going to grow up just like you one day. Never to late at 60.

    Like

    Reply
  2. Pingback: 10X Your Excel Skills with ChatGPT 🚀 - TJX

Leave a comment

Discover more from Kevin Stratvert

Subscribe now to keep reading and get access to the full archive.

Continue reading