Working with Excel functions can pose several challenges without the assistance of ChatGPT. Firstly, the vast array of functions available in Excel can be overwhelming, making it difficult to determine which function is best suited for a particular task. Additionally, understanding the syntax and arguments of each function can be complex, especially for users with limited experience of Excel knowledge. The troubleshooting and debugging functions can be time-consuming, as errors in formulas can produce unexpected results, and identifying the source of the issue can be challenging. Lastly, as the complexity of data and tasks increases, combining multiple functions and creating nested formulas can become intricate, requiring careful planning and attention to detail.
In this article, we would like to draw attention to how these challenges can be easily handled using ChatGPT and how it could become your personal Excel advisor.
Table of Contents
Excel Formula Wiki
There is an Excel feature that helps you choose the most suitable one from more than 500 available Formulas. Unfortunately, this option rarely gives adequate results. As you can see in the picture below, I was looking for the function for a simple daily problem, but even after several rephrases, I was not successful with it.
Let’s see what would be a ChatGPT response to the same question. (I had to add the word “Excel” to the request to refer to the software where I’m looking for a function)
It’s pretty amazing how detailed an answer we got to a simple problem, and since I wasn’t specific enough, the answer came from several aspects. It is beneficial that we also received examples that can help us apply the function to our own tasks.
Note, before you start to copy and paste the code advice by ChatGPT, please make sure the separator character the code contains fits your regional settings.
Suggested message Nr1: “Please advise which Excel function I should use if …”
… I have to convert text to date format.
… I need the last 3 characters of a string.
… I should count the number of characters in a cell.
Remember that the chat can be continued, so if you want more details about any of the functions, you can ask for them by referring to their number from the previous response.
Excel Formula Writer
If you want to perform a specific task in Excel, ChatGPT will select the appropriate function and create the syntax according to your description. To have the best answer and get the desired result, be as detailed as possible in the task description. Refer to specific cell or column names, and specify the format you want to receive the result.
Example: We have a list of email addresses in the column “A” from which we want to generate names in a standard first-name and last-name format. Let’s ask ChatGPT for advice.
In this case, it is a multi-complex function, so we get a detailed explanation to understand how it will work. I think this is great because this way, we can learn how the function works and how to build them on top of each other. If a function or code is included in the answer, the system makes it copyable separately with the “Copy code” button in the upper right corner so that you don’t have to bother with the selection.
Suggested message Nr2: “Please write an Excel function for the following task: …”
… I need to summarize revenues which are in column “A” based on …
… In Cell “C2”, there is a ticket number; if this number contains the string “ABC”, then …
… There is a Date in cell “A2” in date and time format, but I need only the date …
Excel Formula Reader
It can easily happen in an office environment when you open an Excel spreadsheet and you come across a function that you don’t really understand. In a standard way, you can click into the code and follow its operation using the coloring, but this is almost impossible if 2 or more functions are combined. When you ask ChatGPT to explain how a certain function works, it explains each part of it step by step and then sums it up in simple terms at the end. Let’s see an example of this:
With this solution, it is much easier to understand even the more complicated Excel functions, and as a result, modifications can be made more safely if there is a need to change.
Suggested message Nr3: “Please describe the operation of the following Excel function: …”
… =IF(ISNUMBER(SEARCH(“ABC”, A1)), “Yes”, “No”)
Excel Formula Troubleshooter
The more complicated an Excel function is, the more likely it is that an error will slip into the code, or that the formula will not give a result due to changes in the input data. If the function indicates an error or an unexpected result is obtained, it is worth analyzing the code with ChatGPT and asking for suggestions. In the following example, I used the previously generated, email address split code as a basis and changed the input data to get an error. When I cut out the “.” character from the beginning of the email address, the function did not find this character and therefore gave a #Value as a result. Let’s see what the system thinks about it:
What caused the problem is detailed in the second point, so there is a good chance I could fix the code or know how to clean the source data. This is much more efficient than the previous troubleshooting methods (google it) because it refers to your own Excel function code and specifically asks whether one or another character can be found in the target cell or not.
Suggested message Nr4: “Please check why the following function returns …
… #Value
… #N/A
… Error Message
Excel Data Dictionary
This term typically refers to a document or spreadsheet that provides a detailed description and explanation of the data contained within a workbook or dataset. It serves as a reference document that helps users, analysts, or developers understand the structure and meaning of the data in an Excel workbook. It promotes consistency, and data integrity, and aids in data analysis, reporting, and system integration efforts. To create a comprehensive data dictionary, it is advisable to include enough example rows to cover various scenarios and edge cases. A good rule of thumb is to include at least 10 example rows for each data element or column. Right after the message sentence, just copy and paste the table’s first rows, including the header line.
It is quite smart that ChatGPT tries to figure out not only what type of data is in which column, but also what it actually is content-wise. I have to say that the system can see the table quite well and recognize the correlations of the data accurately.
Suggested message Nr5: “Please create a data dictionary about the following data …
Data Analyzer
One of the most surprising and shocking capabilities of ChatGPT is yet to come. It is extremely time-consuming and requires a lot of expertise and experience to discover the patterns of the data and draw conclusions from a big dataset. I created a 20 rows Excel table for a non-existent helpdesk service, which I imported into the chat, and let’s see what result we get after the analysis.
Since the system prepared a rather long analysis for this request, I will not copy it into this article, but I will highlight the most interesting parts. If you are interested in the whole answer, you can get it here.
“Software: The most common category of reported issues is related to Software problems. This indicates that there may be potential areas for improvement in the software development process. Recommendations: Consider implementing better testing and quality assurance practices to reduce the occurrence of such problems.”
“Priority U1: Tickets with priority level U1 also appear multiple times. It’s important to address these high-priority issues promptly to minimize their impact on the system and users. Recommendations: Evaluate the criteria for assigning priority levels to tickets to ensure that the most critical issues are addressed promptly.“
“Assigned Agents: Agent 55 and Agent 89 are assigned to the majority of the ticket. It’s worth assessing their workload and performance to ensure they are handling their tickets efficiently. Recommendations: Provide additional training or support if needed, and consider workload redistribution if there are imbalances.”
I think ChatGPT had pretty good insights, and come to fairly right conclusions in about one second. What is especially interesting is that, from the fact that the ticket priority was marked as U1, it already deduced that these are the most urgent tickets, so they must handle priority. All of this is from the column header, the letter the priority code starts with, and what number behind it. Simply amazing.
Suggested message Nr6: “You are a Data Analyst. Can you please give me some insights and recommendations for this dataset: …”
Data Visualizer
As ChatGPT is a text-based model, it’s unable to draw charts or process external files like Excel sheets directly. However, it can assist you in creating a chart using your dataset with the necessary instructions and guiding you through the process. If you paste the dataset into the chatbox, it can analyze and suggest visualization options. Using the same dataset as I did before, the system suggested the below charts:
I think that the insights about which chart to use to visualize which element of the dataset are good, but their ASCII representations do not predict the appearance of the graphs. Of course, the design solutions and color sets pre-built into Excel can help you find the most suitable design, but you need some experience to create a beautiful Excel dashboard with the help of ChatGPT.
If you continue the conversation by asking for detailed instructions on how to create a graph, you will receive help for creating a graph broken down to the level of mouse clicks in response.
Suggested message Nr7: “You are a Data Analyst. Can you please suggest visualization options for this dataset: …”
In this part, it must be mentioned that everyone should be careful when importing sensitive data because the system stores the data and can use it in the later learning rounds. If you want the chat history not to be saved and your conversations to be removed from the learning database, read this article and, if necessary, change the default settings.
Comments 1