Generative AI has taken over almost all industries by storm. ChatGPT, in particular, has become the fastest platform to cross over 100 million users. Excel is not left behind as people rush to apply it in their workplaces and schools to make work easier. In this article, you shall learn various ways you can use ChatGPT with Excel.
What is ChatGPT?
ChatGPT is a Generative pre-trained model that processes natural language and produces results. You can use it to write emails, blogs, essays, social media posts, and code snippets. Although it’s usually accurate, sometimes it can spit inaccurate information.
1. Write VBA code
One of the most useful features of using ChatGPT with Excel is that you can use it to write VBA code. Before this AI was introduced, you needed to hire a developer or research for hours if you wanted to write VBA. Now ChatGPT can do it within a few seconds. Here are a few examples
Prompt: Write VBA code to lowercase all the text in column A
Chat GPT output:
Sub LowercaseColumnA()
Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range
Dim cell As Range
' Set the worksheet object
Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the actual name of your worksheet
' Find the last row in Column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Set the range to be the entire Column A
Set rng = ws.Range("A1:A" & lastRow)
' Loop through each cell in the range
For Each cell In rng
' Convert the text to lowercase
cell.Value = LCase(cell.Value)
Next cell
' Cleanup
Set rng = Nothing
Set ws = Nothing
End Sub
Apart from providing me with the VBA code, the AI also added some extra tips on how I could use the VBA code it had just generated.
To get accurate VBA codes, you need to create a prompt that explains what you need precisely.
Excel and ChatGPT are no-brainers as you can automate many tasks. Let’s look at another example.
Prompt: Create a VBA code to highlight the duplicates in range B1:F7 with the colour red
Output:
Sub HighlightDuplicates()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
' Set the worksheet object
Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the actual name of your worksheet
' Set the range to be B1:F7
Set rng = ws.Range("B1:F7")
' Loop through each cell in the range
For Each cell In rng
' Check if the cell value is a duplicate
If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then
' Highlight the duplicate cell with red color
cell.Interior.Color = RGB(255, 0, 0)
End If
Next cell
' Cleanup
Set rng = Nothing
Set ws = Nothing
End Sub
As you can see from the image above, the AI has generated a VBA code that highlights red all the duplicates in sheet1 given range.
Apart From Excel VBA codes, if you are a Google Sheets user, you can use AI to generate an app script.
Apps script works in Google Sheets the same Way Macros work in Excel
Example
Prompt: Generate Google Sheets Apps Script that prevents entering a duplicate value in the same column:
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var editedRange = e.range;
// Check if edited cell is in the desired column
if (editedRange.getColumn() === 1) {
var editedValue = e.value;
var columnValues = sheet.getRange(1, editedRange.getColumn(), sheet.getLastRow()).getValues();
for (var i = 0; i < columnValues.length; i++) {
if (columnValues[i][0] === editedValue && i !== editedRange.getRow() - 1) {
// Duplicate value found, clear the cell and show an error message
sheet.getRange(editedRange.getRow(), editedRange.getColumn()).clearContent();
Browser.msgBox('Duplicate value is not allowed in this column.');
return;
}
}
}
}
As you can see from the image above, Chat GPT has provided an app script that works on Google Sheets.
Apart from the script, you also get instructions on how to use it underneath.
2. Generate Tables
ChatGPT can generate tables that you can easily copy and paste into your Excel sheet. All you need is to enter a prompt describing the type of table you want. Here are a few Examples.
Prompt: generate a 3-column and seven rows table with random data
Output:
All I need is to copy the above table and paste it into my Excel file
Paste as match destination formatting to avoid pasting the grey background color from ChatGPT on your spreadsheet
Prompt: Generate a table that contains products in the first column, serial numbers in the second and prices in the third column
As you can see from the image above, the AI has generated a table following my prompt. All you need to do next is copy and paste it into your actual spreadsheet.
3. Generate a formula
Formulas make work easy in Excel. But it may be hard to memorize all of them, especially the complex ones. Luckily, you can use ChatGPT to generate any kind of formula you want fast.
For example, You can ask it to generate a formula to split names into new cells.
Prompt: Generate an Excel formula that splits names into new cells
Output:
The AI will provide you with the formula and how to use it
The image below shows the result when the formula is applied in Excel.
4. Explain the formula
Apart from generating formulas, you can ask the AI to explain a formula and how to use it step by step guide.
For example, I can ask the AI to explain the following formula. =RIGHT(A1, LEN(A1) – FIND(” “, A1))
As you can see from the image above, it has explained everything you need to know about the mentioned formula.
5. Create random numbers or text
If you are looking to generate random numbers or text in Excel, then you can make use of this platform.
For example, you can ask it to generate a list of random names where the first name is in the first column while the second name is in 2nd column.
Once it gives you the output, you can go ahead and copy-paste it into Excel
6. Excel Tips
If you are learning Excel, you can ask ChatGPT for tips to help improve your skills. For example, you can ask it to give you tips on how to work pivot tables in Excel.
7. Excel Shortcuts
There are more than 500 Excel shortcuts to make work easier. It’s hard to memorize all of them. If you are looking for a shortcut to simplify your work, you can ask ChatGPT to help.
For example, you can ask: Generate 20 Excel Windows shortcuts to work with Macros.
Final Thoughts
You can automate your most repetitive tasks using Excel and Chat GPT AI. There is no official plugin yet from Microsoft. However, there are many ways you can use the website. Whether it’s to create VBA code, Formulas, or create tables, you can achieve it all with ChatGPT.