To use  AI-powered tools like ChatGPT effectively and safely with Excel, users  should have , at a minimum , a  strong fundamental  knowledge of  Excel’s  syntax, structures and logic.

When using AI to generate or modify formulas, it can sometimes produce syntax errors or incorrect logic due to the complexity of the task or ambiguous instructions. So a sound knowledge of Excel  is essential to spot these errors and mistakes.
Here is a checklist to help you gauge whether that minimum requirements is met. If unsure about any of these skills , take action to strengthen  understanding of Excel before integrating AI into workflows.

You can download a PDF version of the full Evaluation Skills  Checklist – ‘ is it safe for my team to use Excel with AI”  from Here.

You can download a PDF version of the full Evaluation Skills  Checklist – ‘ is it safe for my team to use AI with Excel ”  from Here

1. Relative and Absolute References

• Question: Do you know the difference between $A$1, A$1, $A1, and A1? If you don’t, it’s essential to understand how references change when copying formulas across cells.
• Explanation: Absolute references (like $A$1) stay fixed regardless of where the formula is copied, while relative references (like A1) adjust based on the position. Mixed references (e.g., $A1 or A$1) fix either the row or column.
• Importance: AI often gets them confused or even leaves them out. It’s vital users can check for this. Using the wrong type of reference can lead to incorrect calculations and flawed data analysis.

2. Linking Between Worksheets and Workbooks

• Question: Can you identify the mistake in this formula: Sheet3!G2 + Sheet3H3 + Sheet4!$B$2?
• Explanation: When linking between worksheets, the correct syntax for referencing a cell should include an exclamation mark and a valid range name, such as Sheet3!G2 + Sheet3!H3 + Sheet4!$B$2. Omitting the exclamation mark (Sheet3H3) results in an invalid reference.
• Importance: Again, AI can often makes mistakes here. Understanding and knowing  the correct syntax is vital. Being familiar with linking ensures data consistency across multiple sheets and workbooks.

3. Building Formulas: especially using Mathematical and Logical Operators

• Question: How would Excel evaluate the formula = (B2 + F5) * G4? Are you aware of how order of precedence affects the outcome?
• Explanation: In Excel, parentheses control the order of operations. Without parentheses, operations follow the standard mathematical precedence rules: multiplication and division occur before addition and subtraction.
• Importance. AI might provide formulas that reference incorrect or non-existent cell ranges. For example, if your data starts in row 2 but the suggested formula references row 1, it could lead to inaccurate calculations.

4. Data Formatting: Currency, Percentages, Text, and Dates

• Question: Do you know how to correctly format a cell to display data as a currency, percentage, or date?
• Explanation: Applying the appropriate format helps with both the readability and accuracy of data. For example, treating a date as text will prevent it from being used in date-based calculations.
• Importance: ChatGPT may not account for data type mismatches. For instance, suggesting a mathematical operation on text values or attempting to calculate with empty cells without considering their implications can result in errors.

5. Understanding Table Structures and Their Syntax

• Question: Can you explain what this formula does: =SUM(Table1[Revenues])?
• Explanation: Excel tables allow you to reference columns by name, making formulas more readable and less prone to errors when the structure changes. In this example, the formula sums the “Revenues” column in “Table1.”
• Importance: ChatGPT may not fully understand the specific context or structure of your data Understanding table syntax enables you to spot this and remind AI to fix its response.

6. Key Functions: IF, AND, Lookups, SUMIFS, COUNTIFS, SUBTOTAL, etc.

• Question: Do you know why this formula might produce an error: =IF(B10 >= 1000, Give Bonus, “”)?
• Explanation: In this formula, “Give Bonus” should be enclosed in double quotes to indicate text, like this: “Give Bonus”. Without quotes, Excel will look for a named range or function called “Give Bonus.”
• Importance: ChatGPT might generate formulas with incorrect syntax, such as missing parentheses, incorrect argument separators (commas vs. semicolons), or invalid function names. This can lead to errors when you try to enter the formula in Excel..

7. Conditional Formatting: Highlighting Cells Based on Their Values

• Question: Are you comfortable creating rules that format cells based on criteria, such as highlighting values above a certain threshold?
• Explanation: Conditional formatting lets you visually emphasize important data, such as outliers or trends, by applying color schemes, icons, or data bars.
• Importance: AI often gets column references mixed up when using CF and thus the formulas themselves. Also AI  can  suggest complex logical expressions without fully understanding the intended conditions. This can result in unexpected outcomes, especially with AND functions.

8. Error Handling and Troubleshooting

• Question: Can you diagnose common formula errors such as #DIV/0!, #VALUE!, or #REF!?
• Explanation: Understanding error messages helps you troubleshoot and correct problems in your formulas. For instance, #DIV/0! occurs when dividing by zero, while #REF! indicates an invalid cell reference.
• Importance: Knowing how to diagnose errors like #DIV/0! (division by zero), #VALUE! (invalid data type), or #REF! (invalid cell reference) allows you to spot and correct mistakes the AI might make.

9. Named Ranges

• Question: Are you familiar with creating named ranges to make formulas easier to understand?
• Explanation: Named ranges simplify formulas by replacing cell references with descriptive names, which can make your spreadsheets easier to maintain and understand.
• Importance: Understanding named ranges is important when using Excel with AI because they can improve the accuracy, readability, and usability of AI-generated formulas.

Conclusion:

Update Excel Knowledge  & Company Policy on using AI safely.

If they are comfortable with these concepts, they are  in a good position to start using AI with Excel. However, if some of these areas are unfamiliar, taking time to update their knowledge to avoid potential errors and ensure safe is well worth it in order for the  effective use of AI-enhanced tools. Being well-versed in Excel fundamentals will not only help them  avoid mistakes but also allow them and your team to harness the full power of AI-driven solutions.

It might also be prudent to add these minimum requirements when drafting company policy on using AI safely.

Next Steps

• Brush Up:

  If needed, take  online Excel  courses  like ours which also includes a level ‘ Using Excel with AI” or practice with sample data to improve your skills.

• Learn AI Integration

  Once you’re confident with Excel, explore how AI tools can automate and enhance your workflows.

• View our 5 cases studies with videos on building complete practical spreadsheets using AI Prompts

   

By mastering these fundamental Excel skills, you will be better prepared to use AI tools like ChatGPT effectively and securely.

Prompt  Template  For Spreadsheets.

Our Prompt Template For Excel will help you write better, powerful  and more accurate  prompt for use with Spreadsheets.. You can download the full version at  https://onlineexceltraining.com/prompt_template/    Feel free to pass it on to your  team members, and friends. Also to learn more about the Prompt Template for Spreadsheets, watch the video here.

Five Real life Cases Studies of Building Practical Spreadsheets ‘the old way’ and the new way with AI prompts. View the sample below or  Click here to View  all 5 Case studies 

 Building Practical Spreadsheets using  our  ChatGPT Prompt Template.

Full Data Tracking System for Partial Payments.