While a lot of articles may focus on the failures and often unreliable responses of ChatGPT it’s important to remember that this tool is much like your Google Search, and is used to perform tasks to make you more efficient, not be a crutch to rely on.
With that said, I’ve ChatGPT to be a super helpful companion when working in Excel. In my current and previous roles as an IT Ops Manager, Program Manager, Business Analyst, etc I’ve always found myself needing to generate reports. Often these can be for leadership teams to summarize work done over the past quarter, other times it can be to provide detailed data reports for our development teams as an example or proof of concept for a much larger project. For those in similar roles, you all know too well how often you have to stitch together different data sets.
With that said, below are some of the Excel formulas along with their descriptions that I often use, but can never quite remember exactly how they should be written out. ChatGPT makes it easy by simply asking specifically how I want to look at and compare my data sets and then providing the appropriate formula.
7 of the most commonly used Excel Functions for those in Tech
IFERROR and VLOOKUP
=IFERROR(VLOOKUP(M:M, Data!$A$1:$E$21573,2,FALSE),”NA”)This formula looks up a value in the M column, compares it to the first column of a table on a worksheet named “Data”, and returns the value from the second column of that table, if found. If the value is not found or some error occurs, it will return “NA”.
IF and ISNUMBER
=IF(ISNUMBER(SEARCH("/events/",B14)),"events",IF(ISNUMBER(SEARCH("/s/",B14)),"s",IF(ISNUMBER(SEARCH("/webinars/",B14)),"webinars",IF(ISNUMBER(SEARCH("/customersuccess/",B14)),"customersuccess",IF(ISNUMBER(SEARCH("/resources/",B14)),"resources",IF(ISNUMBER(SEARCH("/product/",B14)),"product",IF(ISNUMBER(SEARCH("/contact-us/",B14)),"contact-us",IF(ISNUMBER(SEARCH("/request-free-demo/",B14)),"request-free-demo",IF(ISNUMBER(SEARCH("/blog/",B14)),"blog",""))))))))))This formula is checking for the presence of specific text patterns in cell B14, and if a match is found, it returns a corresponding string. If no match is found, it returns an empty string “”.
LEFT and FIND
=LEFT(A1,FIND("?",A1)-1)This formula is extracting a substring from the left side of cell A1, starting from the first character and ending at the character before the “?” character.
RIGHT and FIND
=RIGHT(D2,LEN(D2)-FIND("?",D2))This formula is extracting a substring from the right side of cell D2, starting from the character after the “?” character to the end of the string.
Concatenation Operator (Combine Cells Contents Together)
=A2&””&B2&””&C2
This formula is combining the contents of cells A2, B2, and C2 into a single string with empty strings as separators between the contents of each cell.
IF and COUNT
=IF(COUNTIF(J2,"*stage*"),"prod","Other")This formula is checking for the presence of the text pattern “stage” in cell J2, if the text pattern is found, it returns the string “prod” otherwise it returns the string “Other”.
MID
=mid(P:P,12,5)This formula is taking the contents of the range of cells P:P and extracting 5 characters, starting at the 12th character of the contents.
If you have some other ones you find handy feel free to mention them in the comments. Take a look at other things I’ve made with ChatGPT’s help.