What this article is about

In this article you will learn:

• How to use AI to generate complex Excel and Google Sheets formulas
• Ways to automate repetitive tasks in spreadsheets
• Techniques for parsing and analyzing CSV files
• Creating macros and scripts for spreadsheets
• Practical examples and ready-made prompts

Problem: Working with spreadsheets takes a lot of time

Every day, millions of people spend hours working with Excel and Google Sheets. Routine tasks include:

• Creating complex formulas with multiple conditions
• Merging data from different sources
• Formatting and cleaning data
• Building reports and dashboards
• Automating repetitive operations

Artificial intelligence can significantly speed up these processes.

Generating formulas with AI

Basic formulas

Instead of remembering syntax or searching in help, just describe the task:

Prompt:

Create an Excel formula that:
- Checks value in cell A2
- If greater than 100, returns "High"
- If between 50 and 100, returns "Medium"
- If less than 50, returns "Low"

Result:

=IF(A2>100,"High",IF(A2>=50,"Medium","Low"))

Complex nested formulas

Prompt:

Need a formula to calculate salesperson commission:
- Base: 3% of sales
- Bonus 1%: if sales > 50000
- Bonus 2%: if sales > 100000
- Penalty -0.5%: if less than 5 deals per month

Data: sales in B2, number of deals in C2

Spreadsheet automation

Creating VBA macros

Prompt for Excel:

Write a VBA macro that:
1. Finds all cells with text "REQUIRES PROCESSING"
2. Changes background color to yellow
3. Adds comment with current date
4. Sends email notification

Google Apps Scripts

Prompt for Google Sheets:

Create a Google Apps Script that:
- Runs daily at 9:00 AM
- Copies data from "Incoming" sheet to "Archive"
- Adds timestamp
- Clears original sheet
- Sends report to specified email

Working with CSV files

Parsing and cleaning data

Prompt:

I have a CSV file with customer data. Need to:
1. Remove duplicates by email
2. Format phones to +7 (XXX) XXX-XX-XX
3. Split full name into separate columns
4. Fill empty cities with "Moscow"

Create formulas or script for Google Sheets

Analyzing large CSV

Prompt:

I have a CSV with 50000 transaction rows. I need to:
- Group by months
- Calculate average transaction amount
- Find top-10 customers by purchase sum
- Identify anomalous transactions (more than 3 standard deviations)

Suggest optimal approach for Google Sheets

Conclusion

Artificial intelligence turns spreadsheet work from routine into creativity. Instead of spending hours searching for the right formula or writing a macro, you can simply describe the task and get a ready-made solution.

What to do next?

1. Try prompts from this article on your data
2. Explore our collection of prompts for data work
3. Share your findings with the community
4. Create and publish your own prompts