ChatGPT Unprotected a Worksheet

I recently discovered that you can upload an Excel file to ChatGPT, ask it to perform tasks on the file, and then download the updated file. In this post, I wanted to share what I discovered and demonstrate it with a few examples including unprotecting a worksheet, writing formulas, and creating a chart. Microsoft’s Copilot in Excel is designed to interact with, and operate on, Excel files as well. But, for those without a Copilot license this provides a possible alternative (depending on what you are trying to accomplish). So, let’s get to it.

Video

Step-by-step Guide

In addition to the video above, I’ll provide a written tutorial of the same using a few exercises.

Exercise 1: Removing Worksheet Protection

Our first exercise involves removing worksheet protection using ChatGPT. This was a surreal experience. I’ve known ChatGPT is great for text-based conversations and even generating images. But, I had no idea ChatGPT could actually operate on an Excel file you upload. It was a bit surprising! In any event, I applied worksheet protection, saved the file, and uploaded it to ChatGPT. I asked ChatGPT to remove the worksheet protection. It did and provided a download link. Sure enough, worksheet protection was removed.

Now, when no password was assigned when applying worksheet protection, ChatGPT removed worksheet protection every time. When I did assign a password, it wasn’t as successful. It was able to remove worksheet protection when a simple 4-letter password was used, but unable to remove it when a stronger password was used. So, depending on the environment, password, and version, your mileage may vary.

If you’d like to try this at home, here are the steps I used.

Step-by-Step:

Unlock edit cells:

• Select the range of cells you want to allow others to edit.
• Open the Format Cells dialog and uncheck the Locked checkbox on the Protection tab.
• Click OK to confirm.

Protect the Worksheet:

• Go to the Review tab and click on Protect Sheet.
• You can optionally set a password, but for this example, I didn’t use one. I clicked OK to enable protection.

Attempt to Edit Locked Cells:

• If you try to change anything in locked cells, Excel will display an error message preventing the edit.
• This confirms that worksheet protection has successfully been enabled.

Save and Close the Workbook:
Now that the worksheet is protected, we can upload it to ChatGPT and see what it can do!

Using ChatGPT to Remove Protection:

• Prompt ChatGPT with something similar to: Can you unprotect worksheets if I upload a workbook?
• ChatGPT will respond with something like: Yes, upload the workbook and I’ll unprotect the worksheets.
• Then, upload the workbook.
• ChatGPT will respond with something like: I have unprotected the sheets, you can download the unprotected version here [URL]
• Click the link and download the updated file.
• Confirm if worksheet protection was removed either by trying to edit locked cells or by viewing the Review tab. If the Protect Sheet caption is displayed on the toggle button, worksheet protection was removed. If the Unprotect Sheet caption is displayed instead, worksheet protection was not removed.

Here’s what my interaction looked like:

I uploaded the file, and was presented with the following:

And sure enough … worksheet protection was removed by ChatGPT. Wow.

Curious about what other types of changes ChatGPT was capable of making to Excel files, I decided to try asking it to write some formulas directly inside the file.

Exercise 2: Writing Formulas in Excel

The next example is where I asked ChatGPT to write formulas inside a worksheet.

Here is a look at the worksheet before I sent it to ChatGPT.

Then, I said this:

I uploaded the file, and then:

I downloaded the file, and:

So, it didn’t get it perfect. It improperly inserted the formula in E8 instead of entering the value “Extended.” But, it did accurately write the extended value formula for rows 9:13. Then for some reason, it didn’t continue the formula down for rows 14:15. So, it was close, but not perfect.

Now, in practice, I could write the formula much faster and more accurately than ChatGPT did. But, the interesting part is that ChatGPT was able to read the Excel file for context, insert some formulas directly into the file, and then let me download the updated file. The actual formulas didn’t impress me … it was that it has the ability to insert formulas into an Excel file in the first place. It’s a possible option if you don’t have access to the more integrated tool of Copilot.

Exercise 3: Creating Charts and Analyzing Data

For our final exercise, I tested ChatGPT’s ability to analyze data and create charts within an Excel file.

Here’s a preview of the data in the worksheet:

I asked ChatGPT if it could help analyze the data:

I uploaded the file and then:

It initially created the graph inside the chat window. Which is nice, but I wanted it inside the Excel file. So I clarified:

I downloaded the file, and got this:

I confirmed that the chart was an actual Excel chart object that I could move around and edit as desired. I had full access to all of the normal chart format options.

Conclusion

ChatGPT is a fascinating tool for Excel users looking to streamline tasks like unprotecting worksheets, writing formulas, and analyzing data. While it’s not a direct replacement for more integrated tools like Microsoft’s Copilot, it offers an alternative way to enhance your Excel experience, especially for those without access to Copilot.

If you try any of these techniques or have other tasks you’d like me to explore with ChatGPT, please let me know in the comments below!

FAQ

Here are some frequently asked questions based on what we’ve explored today:

1. Can ChatGPT remove worksheet protection if a password is set?

ChatGPT can remove protection when no password is set. If a password is used, the success rate varies.

2. Can ChatGPT write formulas in Excel?

Yes, ChatGPT can write various formulas, but it may not always get the structure or application perfect. Always double-check the results.

3. Is it safe to upload Excel files to ChatGPT?

Always be cautious with sensitive data. While ChatGPT can process files, avoid uploading anything with confidential or private information.

4. Can ChatGPT create charts and graphs in Excel?

Yes! You can ask ChatGPT to create charts based on your data, and it will generate the chart for you to download and customize further.

5. What are the limits of using ChatGPT with Excel files?

While ChatGPT can handle a variety of tasks, it’s not a fully integrated Excel tool like Copilot. Some tasks may require manual adjustments or further refinement.

6. Can ChatGPT help analyze large datasets?

Yes, ChatGPT can summarize and analyze data, although it may not handle extremely large datasets as efficiently as Excel.

7. Does ChatGPT work with all versions of Excel?

ChatGPT works independently of Excel versions. You can upload files from any version, and it will process them for you.