I Started Using ChatGPT-4o To Make My Spreadsheets Better (Here’s How)
Need help with your spreadsheets? Just ask ChatGPT. Here are some of the ways ChatGPT can make your spreadsheets better.
Microsoft Excel is an incredibly powerful spreadsheet application. It has so many features that most of us barely touch the sides of what it is capable of. Part of the reason that we don’t take full advantage of all its features is that we don’t know how to use them, or don’t even know they exist. This website alone contains about a hundred articles or forum posts relating to Excel.
Can AI help? Well, it turns out it can. Using ChatGPT, it’s possible not only to get instructions on how to achieve specific results in Excel, but the AI chatbot can even create entire Excel spreadsheets for you, containing highly complex tables, charts, and formulas. It can also integrate with Google Drive and Microsoft One Drive for cloud access. Here are nine ways that ChatGPT can make your spreadsheets better.
1. ChatGPT Offers a Better Way to Get Your Data Into Spreadsheets
A spreadsheet is nothing without data, but importing large amounts of data into Excel can be a time-consuming and frustrating process. ChatGPT can make that process much simpler, helping your spreadsheets become better and more useful.
You can ask ChatGPT to create an Excel file from any file. If you have a Word file containing a list of employees, for example, you can ask ChatGPT to create a new Excel file containing the list of employees with columns for Last Name, First Name, and Position. After a bit of processing, ChatGPT should pop up with a link for your new file.
You can also scrape data from websites if you wish. If you have a webpage with data you want to use in your spreadsheet, simply save the webpage, upload the file to ChatGPT by clicking the paperclip icon, and then ask ChatGPT to create an Excel file using the data from the saved file. For example, I was able to create a spreadsheet listing all the British monarchs and the years that they reigned, pulled straight from Wikipedia.
If you have hard copies of data that you need to add to Excel, ChatGPT can do that too. Simply take a photo and upload that into ChatGPT. It will use OCR to extract the text and create your spreadsheet. You’ll need to be quite explicit about how you want the data in your spreadsheets laid out, as otherwise, ChatGPT will make up its mind, and the results aren’t always optimal.
2. ChatGPT Can Clean Your Excel Data to Make Your Spreadsheets Better and More Effective
One issue with importing data from other sources is that you may end up with junk in your data, or a mess of different formats. ChatGPT is useful for cleaning up your data.
For example, you can ask ChatGPT to find and remove any duplicate rows of data from your spreadsheet. You can also ask ChatGPT to remove any rows that are missing some of the data, remove any blank rows, or ask it to remove any HTML code from your data.
3. Sorting Your Data in Excel
Excel has tools that allow you to sort your data how you want, but if all of that seems like too much effort, you can just ask ChatGPT to do it for you. For example, I asked ChatGPT to sort my list of British monarchs into the order of longest reign. This wasn’t trivial, as the data had two columns with the Reign Start and Reign End, so doing this manually would have involved creating a new column with the total reign duration, and then sorting on that column. ChatGPT did all of this for me and popped out a new spreadsheet with the data in the order I requested, including a new column showing the total reign duration.
4. ChatGPT Helps You Make Better Spreadsheets with Conditional Formatting
Conditional formatting is a great way to spot patterns or duplications in your data. ChatGPT can do this for you, helping you make even better spreadsheets. You can ask for fairly complex conditional formatting rules that might require creating multiple conditional formatting rules, and ChatGPT will do all the hard work for you.
For example, I asked ChatGPT to highlight all monarchs that had the same first name in the same color, specifying that any monarchs who did not have another monarch of a matching name should remain unformatted. It took ChatGPT about three seconds to generate exactly what I asked for.
5. Creating Excel Formulas
We’ve all had that frustrating moment when we’ve created an incredible formula full of functions within functions, but when we press Enter, a message pops up that there’s an error somewhere among all those brackets and commas.
ChatGPT can help by creating your formulas for you, either to insert manually or by adding them directly into Excel files.
I tried this out with my list of monarchs and asked ChatGPT to create a formula that would calculate the length of reign for the current row, compare that length to any other monarchs with the same first name, and output ‘Longest reigning’ followed by the monarch’s first name if they were the longest reigning Edward, for example.
The result was a highly complex formula using functions such as FIND, LEFT, and MAX, and it did exactly what I wanted. Dragging out the formula to all other rows gave me an instant view of which Henry has ruled the longest, which Richard, and so on. Creating that formula myself would almost certainly have involved at least one function-breaking typo that had me tearing my hair out.
6. Analyzing Your Excel Data
The whole purpose of Excel is to allow you to analyze your data, and ChatGPT makes that even easier. For a start, you can upload an Excel file and then ask ChatGPT to analyze it for you. For example, give it a spreadsheet of sales figures, and ChatGPT can tell you which salesperson has the best performance based on specific metrics, average sales per rep, and much more.
One of the most powerful tools in Excel for analyzing data is pivot tables, but some beginners or even intermediate Excel users can find these tables a little daunting. ChatGPT can create them for you; all you need to do is upload the file, tell ChatGPT what data you want to include in your pivot table, and what you want the pivot table to show, and the tables will be automatically generated.
7. Creating Charts of Your Excel Data
Spotting trends in a huge sheet of data is hard, but spotting trends in charts is much, much easier. ChatGPT can create charts for you based on the instructions that you give it.
For example, with my list of monarchs, I asked ChatGPT to create a pie chart that displayed the total reign based on the first name of the monarch. Within moments I had a chart that showed that from my data, 36% of the time a Henry was in charge.
Creating this chart myself would have involved using formulas to find the total length of reign for monarchs with the same first name, and then using this new data to create my pie chart. ChatGPT did it all for me.
8. Letting ChatGPT Make Your Spreadsheets Better with Custom Excel Macros
When I first started messing around with what Excel could do and discovered that you could use Visual Basic in Excel macros, I did what any self-respecting geek would do, and made a macro that would generate a four-by-four grid of letters like you get in a game of Boggle. The macro was run by a big button next to the grid that would generate a new grid of letters each time you pressed it. It took me a while to figure out how to do it all, particularly to ensure that the letter distributions matched those of Boggle dice, but I was very satisfied with my work.
I gave ChatGPT the same challenge, and it immediately created the VBA code I needed to add to my macro to generate the letter grid. It found and used the standard Boggle letter distribution, and although it couldn’t do it directly, it gave me the instructions to enable Developer mode and create the button to generate a new grid.
Just to be cheeky, I tried getting ChatGPT to reformat the cells to make them square, give them a yellow border, and hide any other gridlines, which was the final steps of making my original boggle spreadsheet, and after a few efforts at persuading it to do so, ChatGPT finally managed to replicate what had taken me much, much longer to create back in my youth.
9. Teaching You Excel
One of the dangers of AI that can do all these powerful things is that we never learn how to do them ourselves and become reliant on technology to do them all for us. The beauty of AI, however, is that it can be a great teacher, too.
You can ask ChatGPT to give you instructions on how to sort your data, rather than asking it to sort it for you, and it will do so. The instructions are easy to follow and usually explain why you need to follow each step.
ChatGPT can even generate example data for you to play with. For example, you can ask it to generate some random data you can use to learn about pivot tables, and it will generate it for you, and take you through different ways to analyze that data with pivot tables. If you’re looking to improve your Excel skills, ChatGPT can be a really useful learning tool.
ChatGPT, Excel, and the Mac App
All of the methods above will work using ChatGPT through the web app. You may need to use your phone for some things, such as taking photos of hard copies of data that you want to import into Excel, but the rest is all possible through your browser.
The new ChatGPT Mac app does make some things a little easier, however. Firstly, it makes it easier to pull up ChatGPT in the first place. Just press Alt+Space and a ChatGPT prompt window pops up, like a smarter version of Spotlight.
Secondly, the native app allows you not only to upload files, but also to take a photo using your webcam, or your iPhone via Continuity Camera. You can also take a screenshot of any open app, even if it’s not currently displayed on your Mac screen, and then ask ChatGPT about it.
Another benefit is that the ChatGPT window hovers over whatever window you’re working on. If you want to work through an example that ChatGPT has given, you can keep it hovering over the Excel app as you’re working, meaning you’re not constantly switching back and forth between Excel and your browser. Even if you minimized your browser and dragged it in front of the Excel app, every time you interact with Excel, your browser window will disappear behind the app, but the native ChatGPT app always stays on top, while still allowing you to interact with the Excel app behind it.
ChatGPT Isn’t Perfect
There is one final caveat here. ChatGPT isn’t perfect. It can and will make mistakes. If you’re making a spreadsheet for your board meeting that decides the future strategy for your entire company, it’s probably worth triple-checking anything that ChatGPT creates, or getting ChatGPT to teach you how to achieve your goal, rather than just getting ChatGPT to do it for you.
That said, it’s a pretty incredible tool that can help you achieve the Excel results that you want significantly faster than you could otherwise. With new features coming soon, such as the ability to query what ChatGPT can see on your screen, it’s only going to get more useful.
Leave a Reply
Leave a Reply
