In the realm of data, Excel reigns supreme - a double-edged sword of power and perplexity. Yearning for efficiency, we ponder: Can Excel AI unlock its true potential, simplifying formulas and freeing us from data's entangled grasp? The answer whispers resolutely: Embrace Excel AI, witness simplicity and speed entwine, as tables materialize effortlessly, unburdened by complexity or graphs.
How to Use WPS AI to Write Formulas
WPS AI is a ChatGPT-like system that uses artificial intelligence to deliver intelligent assistant services for workplace settings. It is compatible with a wide range of office programs, including document editing, form processing, presentation building, and others. WPS AI offers strong text generating skills that can assist users with a variety of text creation activities like content creation, summary, translation, and more.
Advantages of WPS AI:
Time-Saving Wizardry: Speed up document creation and spreadsheet tasks with AI-powered automation.
Simplified Workflows: WPS AI's intuitive interface and natural language processing simplify complex tasks.
Error-Free Outputs: Enhance accuracy and reliability by leveraging AI's expertise in data handling and analysis.
Steps
Step 1 Open an Excel spreadsheet and select the cell where you want to write the formula.
Step 2 Click the Formulas tab and then click the WPS AI button.
Step 3 In the WPS AI dialog box, type a brief description of the formula that you want to write. For example, you could type "sum of cells A1 and A2".
Step 4 WPS AI will generate a list of possible formulas that match your description.
Step 5 Select the formula that you want to use and click OK.
How to download WPS AI
Step 1 To download WPS AI, you can visit the WPS AI website: https://www.wps.ai/
Step 2 Click on the Free Download button
Step 3 Select the operating system that you are using.
Step 4 The file will be downloaded automatically
Step 5 After approval, customers will get an email from WPS AI with instructions on how to download and install WPS AI on their systems. Once the installation is complete, customers may activate WPS AI on their systems and take use of the numerous functions it provides.
How to Use ChatGPT to Write Formulas
Method 1: Excel Plugin for ChatGPT
Step 1 Install the ChatGPT Excel Plugin from the website.
Step 2 Open Excel and access ChatGPT through the newly added menu or icon.
Authenticate or connect to the ChatGPT service if required.
Step 4 Type your formula-related request, and ChatGPT will respond with suggestions.
Step 5 Insert the desired formula into Excel by clicking on the provided option.
Method 2
Step 1 Enable the Developer tab in Excel options.
Step 2 Access the Visual Basic for Applications (VBA) Editor from the Developer tab.
Step 3 Create a new module in the VBA Editor.
Step 4 Write VBA code to integrate ChatGPT's API or SDK for formula suggestions
(Sub chatGPT()
Dim request As Object
Dim response As String
Dim text, API, api_key, DisplayText, error_result As String
Dim status_code As Long
Dim cellr As Range
Dim prompt, rng As Range
'API Info
API = "https://api.openai.com/v1/chat/completions"
api_key = "sk-xxxxxxxxxxxxxxxxxxxxxxxxxx"
If api_key = "" Then
MsgBox "Error: API key is blank!"
Exit Sub
End If
Set prompt = Range("B3")
Set cellr = prompt.Offset(1, 0)
Set rng = Range(prompt.Offset(1, 0), prompt.Offset(2000, 0))
rng.Clear
'Input Text
If prompt.Value = "" Then
MsgBox "Error: Cell " & prompt.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " is blank!"
Exit Sub
End If
text = Replace(prompt.Value, Chr(34), Chr(39))
text = Replace(text, vbLf, " ")
'Create an HTTP request object
Set request = CreateObject("MSXML2.XMLHTTP")
With request
.Open "POST", API, False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Authorization", "Bearer " & api_key
.send "{""model"": ""gpt-3.5-turbo"", ""messages"": [{""content"":""" & text & """,""role"":""user""}]," _
& """temperature"": 1, ""top_p"": 0.7, ""max_tokens"": 2048}"
status_code = .Status
response = .responseText
End With
'Extract content
If status_code = 200 Then
DisplayText = ExtractContent(response)
Else
DisplayText = ExtractError(response)
End If
'Put response
cellr.Value = DisplayText
'Split to multiple rows
Call SplitTextToMultipleRows(cellr)
rng.WrapText = True
'Clean up the object
Set request = Nothing
End Sub
Sub SplitTextToMultipleRows(cell As Range)
Dim splitArr() As String
Dim delimiter As String
delimiter = "\n"
splitArr = Split(cell.Value, delimiter)
For i = LBound(splitArr) To UBound(splitArr)
x = splitArr(i)
If Left(Trim(x), 1) = "=" Then
x = "'" & x
End If
cell.Offset(i, 0).Value = Replace(x, "\""", Chr(34))
Next i
End Sub
Function ExtractContent(jsonString As String) As String
Dim startPos As Long
Dim endPos As Long
Dim Content As String
startPos = InStr(jsonString, """content"": """) + Len("""content"": """)
endPos = InStr(startPos, jsonString, "},") - 2
Content = Mid(jsonString, startPos, endPos - startPos)
Content = Trim(Replace(Content, "\""", Chr(34)))
'Fix for excel forumulas as response
If Left(Trim(Content), 1) = "=" Then
Content = "'" & Content
End If
Content = Replace(Content, vbCrLf, "")
Content = Replace(Content, vbLf, "")
Content = Replace(Content, vbCr, "")
If Right(Content, 1) = """" Then
Content = Left(Content, Len(Content) - 1)
End If
ExtractContent = Content
End Function
Function ExtractError(jsonString As String) As String
Dim startPos As Long
Dim endPos As Long
startPos = InStr(jsonString, """message"": """) + Len("""message"": """)
endPos = InStr(startPos, jsonString, """")
If startPos > Len("""message"": """) And endPos > startPos Then
ExtractError = Mid(jsonString, startPos, endPos - startPos)
Else
startPos = InStr(jsonString, """code"": """) + Len("""code"": """)
endPos = InStr(startPos, jsonString, """")
If startPos > Len("""code"": """) And endPos > startPos Then
ExtractError = Mid(jsonString, startPos, endPos - startPos)
Else
ExtractError = "Unknown error"
End If
End If
End Function
Step 5 Assign the macro to a button or shortcut for easy access.
Step 6 Use the macro to get formula suggestions in Excel.
Method 3
You can also run ChatGPT as an excel function. The benefit of running it as function is that you can paste it down on multiple cells. See the animated image below how to use the function.
Step 1 Access ChatGPT: You can use ChatGPT as an Excel function to generate responses based on your input.
Function Syntax: The function syntax is: AIAssistant(text, [word_count]).
text: Input your prompt or question here.
word_count (optional): Specify the maximum number of words in the response.
Step 2 Set up the Function: a. Enter your prompt/question in one cell. b. In another cell, use the formula =AIAssistant(A1, [word_count]), where A1 is the cell with your prompt, and [word_count] is the optional word limit.
Step 3 Apply to Multiple Cells: Copy the formula with the AIAssistant function to other cells to get responses for multiple prompts.
Example - Extracting Capital Cities: a. List countries in Column A. b. In Column B, use the formula =AIAssistant(A1, [word_count]) to get the capital cities for each country. c. Copy the formula to other cells in Column B for other countries.
Limit Response Length: Control response length by adjusting the [word_count] to get more concise answers.
ChatGpt Prompts
ChatGPT can be a valuable tool for writing formulas in Excel, simplifying the process and enhancing your productivity. Explore these prompts to leverage ChatGPT's capabilities and effortlessly generate formulas for various data analysis tasks.
"Sum Formula": Generate a formula to calculate the sum of a range of cells, streamlining total calculations.
"Average Formula": Get a formula for finding the average of a set of values, aiding in data analysis.
"Percentage Formula": Create a formula to calculate percentages, useful for various business scenarios.
"IF Function": Generate an IF function to perform conditional calculations based on specific criteria.
"VLOOKUP Formula": Get a formula to perform a vertical lookup, extracting data from another table.
FAQs
1. Does Excel have Artificial Intelligence?
Excel doesn't have true AI, but it uses machine learning in features like Flash Fill, Smart Lookup, and dynamic arrays. Third-party add-ins also enhance Excel with AI capabilities for data analysis and productivity. As technology evolves, more AI-powered features are likely to be introduced.
2. What Ethical Considerations Should be Taken into Account When Using AI for Formula Writing?
Ethical considerations for AI in formula writing encompass preventing discrimination and ensuring equitable treatment, transparency in AI workings and user data protection, safeguarding privacy, and accounting for safety and environmental impacts.
Providing understandable AI decisions and involving human oversight aligns AI with human values and regulations, building trust through transparency and accountability. Prioritizing user needs in AI system design, taking responsibility for AI actions and impacts, and considering long-term societal and environmental effects ensure responsible and ethical AI usage.
3. What are the Different Types of AI?
AI can be categorized into different types based on their capabilities and functionalities. The main types of AI are as follows:
Narrow AI (Weak AI): Performs specific tasks better than humans within a limited domain (e.g., virtual assistants, image recognition).
General AI (Strong AI): Outperforms humans in any intellectual task, possesses human-like intelligence, and can apply judgment in various situations.
Artificial Superintelligence (ASI): Surpasses human capabilities in almost every field, including creativity, wisdom, and social skills.
4. What is Excel AI?
Excel AI is the integration of artificial intelligence (AI) technology into Microsoft Excel, one of the most widely used spreadsheet applications. By leveraging AI capabilities, Excel becomes more powerful, efficient, and user-friendly, enhancing data processing, analysis, and automation.
Benefit of AI Tool for Excel
Formula Writing: Intelligently suggests formulas based on natural language, saving time and reducing errors.
Data Analysis: Uncovers hidden patterns, trends, and insights for more informed decision-making.
Automation: Automates repetitive tasks, boosting productivity and efficiency.
Data Visualization: Creates interactive and visually appealing charts and graphs effortlessly.
Decision-Making: Presents meaningful insights from large datasets, aiding smarter decisions.
Some AI Tools for Excel
WPS AI: An AI-powered assistant that can help users write formulas and analyze data in Excel.
Microsoft Power Query: A data transformation tool that can help users clean and transform data in Excel.
Formula Bot: A web app that uses AI to help users write formulas in Excel.
Summary
In this comprehensive guide, we embarked on a journey to master the art of formula writing with the aid of WPS AI/ChatGPT. Excel, a powerful yet intricate tool, has been made more accessible and efficient through AI integration, empowering beginners to navigate its complexities effortlessly.
WPS AI proved to be a game-changer, simplifying formula writing and empowering us in Excel's realm. Its intuitive interface, time-saving automation, and AI-powered insights streamline data analysis. Embrace WPS AI's power and unlock Excel's true potential, simplifying complex calculations and revolutionizing productivity in your data-driven endeavors.
