How to use AI as a spreadsheet assistant

Spreadsheets are one of the most ubiquitous business tools with users across domains relying on them for tasks like data entry, analysis, reporting and more. However, spreadsheets also come with their own headaches – erroneous formulas, data inconsistencies, version control issues, lack of collaboration and security risks.

This is where an AI-powered spreadsheet assistant comes in handy. AI-powered tools such as ChatGPT and Claude can amplify human capabilities and ease many spreadsheet burdens through automation. Let’s look at how such an assistant can make your life easier and help you work more efficiently.

Creating dummy data

Creating representative dummy datasets is crucial for testing spreadsheet models thoroughly before deployment. But manually fabricating rows of fictional data can be tedious and time-consuming.

AI assistants solve this problem by automatically generating dummy data tailored to your spreadsheet’s parameters. If you simply describe the structure, the assistant will instantly produce hundreds of rows of realistic test data. 

You can specify value sets, formats, ranges, correlations, formulas, aggregations and statistical distributions for each column. When you specify values and numeric ranges for each column, the assistant incorporates all the requirements to output a comprehensive dummy dataset with one click.

This enables rigorously testing spreadsheets under different conditions with sizable data. As a result, the automation frees analysts from manual fabrication so they can focus on modeling.

 

Creating dummy data in ChatGPT

In order to create dummy data, I’ll feed this prompt into ChatGPT.

Write dummy data for a spreadsheet containing 5 columns: Product, Q1, Q2, Q3, Q4. The first column should contain the name of the product. Create dummy data for 6 rows.

For the first column, create names for 6 cell phones. Fill the other 4 columns with sales in each quarter. Sales for each model should range between 10,000,000 and 25,000,000. To make it more realistic, don’t use round numbers.

Here’s the answer.

Of course, let’s create dummy data for your spreadsheet with 5 columns: Product, Q1, Q2, Q3, Q4. The first column will contain the names of cell phone models, and the other four columns will contain sales data in millions for each quarter. We’ll use non-rounded numbers and keep the sales figures between 10,000,000 and 25,000,000:

Product

Q1

Q2

Q3

Q4

Galaxy Z Fold 3

23,480,000

21,865,000

24,375,000

22,790,000

iPhone 13 Pro

20,125,000

22,375,000

21,690,000

19,875,000

Google Pixel 6

11,340,000

12,780,000

11,975,000

13,420,000

OnePlus 9 Pro

16,790,000

15,230,000

14,875,000

13,290,000

Xiaomi Mi 11

12,650,000

11,950,000

13,145,000

12,880,000

Huawei P50 Pro

10,350,000

11,650,000

10,255,000

11,790,000

These numbers provide a mix of sales figures for different cell phone models across the four quarters. They fall within the specified range and are not rounded for added realism. Feel free to use or adapt this data as needed. If you have any more questions or need further assistance, please let me know!

Generating spreadsheet formulas

Crafting complex formulas and macros for calculations is one of the trickier spreadsheet skills. If you use an AI assistant, it can help you write formulas in plain English.

For example, simply describe what you want to calculate – “Add the values in Column A and B, find the average and display the result in Column C”. The assistant will automatically generate the formula for you with all the cell references.

You can ask for formulas for aggregation, lookup, conditions, string manipulation and math operations. The assistant figures out dependencies and builds the formula based on your description. Therefore, it simplifies formula creation without worrying about syntax.

By handling the translation and tedious syntax, AI helps professionals, as well as new users, create formulas independently without advanced spreadsheet skills. 

Generating spreadsheet formulas in ChatGPT

Let’s say we want to calculate the percentage each value

In order to generate the formula, I’ll feed this prompt into ChatGPT.

I have a Google Sheets spreadsheet with a column of values in cells F2 through F7. Cell F8 contains the total sum of the values in F2:F7.

Please provide a Google Sheets formula that can be entered in cell G2 and copied down through G7, that calculates the percentage each value in column F represents out of the total in cell F8.

The formula should reference the value in column F for that row and divide it by the fixed total cell F8. When copied down, it should maintain the fixed reference to F8 while changing the row value reference in column F.

This will output the percentage breakdown of each row value compared to the overall total. Please provide the full formula with details on which cells need to be referenced and any required syntax for fixed vs relative references when copying the formula.

Here’s the answer:

To calculate the percentage each value in column F represents out of the total in cell F8, you can use a Google Sheets formula in column G. Here’s the formula you should enter in cell G2:

=F2/$F$8

This formula divides the value in cell F2 by the fixed reference to F8. The $ sign before both the row and column reference ($F$8) ensures that when you copy the formula down through G2 to G7, the denominator (F8) remains fixed, while the numerator (F2, F3, F4, etc.) changes relative to the corresponding row.

After entering this formula in cell G2, you can simply copy it down through G7, and it will automatically adjust the cell references as needed. Each cell in column G will show the percentage of the value in the corresponding row of column F relative to the total in cell F8.

When I add this formula, Google Sheets will calculate all the percentages.

How to use AI as a spreadsheet assistant

Fixing spreadsheet errors

Spreadsheet formulas are prone to errors – it’s easy to make mistakes like incorrect cell references, dividing by zero, mismatched data types, and faulty logic when creating long or interconnected formulas. These errors get propagated through the spreadsheet calculations, leading to inaccurate and misleading results

Finding and diagnosing the root causes of formula errors manually can be like searching for a needle in a haystack. This is where AI-powered tools come in handy.

AI assistants can audit spreadsheet formulas systematically to identify potential issues and risks. Specifically, they check for problems like cells referenced that don’t exist, circular references causing infinite loops, syntax issues like missing parentheses, clashes with expected data types, and more.

In addition, the assistants use pattern recognition and natural language processing to parse formula intent and logic. Moreover, they can trace precedents and dependents to understand formula linkages and data flow. Any anomalies get flagged for the user to debug. Some assistants can even generate fixes and rewrites for identified formula errors. 

Furthermore, advanced assistants may also secure formulas by scanning for vulnerabilities like dynamic code generation. By automating the error checking process, AI assistants save spreadsheet users hours of manual auditing and debugging. In summary, they pinpoint likely error hotspots faster. This helps correct formula errors before they negatively impact calculations and decision making. 

Analyzing data and gaining insights

AI assistants can simplify and accelerate spreadsheet analytics via conversation.For example, you can ask “What are the sales trends by region?” and the assistant will instantly process the data, output trend charts, statistical insights, and explanations in plain language.

By using an AI tool such as Claude, I can not only analyze and interpret data more efficiently, but I can also generate reports and write summaries. 

Analyzing data and gaining insights in Claude

I will upload spreadsheet data in a CSV file and feed a prompt to ask Claude to analyze the data and write a full report.  

Please interpret and analyze data from this spreadsheet. After reading the data, write a full report summarizing the data, identifying trends and patterns, and finding insights.

Here’s the answer:

Here is a 285 word report analyzing and interpreting the sales data from the provided spreadsheet:

Overview

The spreadsheet contains smartphone sales data for 6 models across 4 quarters in 2022. Total revenue across all models and quarters was $380,945,000.

Trends

  • The Galaxy Z Fold 3 was the highest selling model with total sales of $92,510,000. The iPhone 13 Pro came second at $84,065,000.
  • Q3 had the highest total sales at $96,315,000 followed by Q2 at $95,850,000. Q4 had the lowest sales at $94,045,000.
  • Most models except the Pixel 6 saw a dip in sales in Q4 compared to other quarters. This indicates slowing demand towards the end of the year.

Insights

  • Foldable phones like the Galaxy Z Fold 3 seem to be gaining strong traction with sales rivaling established brands like iPhone. This points to growing consumer interest in new form factors.
  • Google Pixel performed the poorest with total sales less than half of the Galaxy Z Fold 3. Google likely needs to refresh its marketing and distribution strategy.
  • Sales across quarters remained relatively stable without much seasonal fluctuation. This implies steady demand across the year without major quarterly variations.
  • Total annual sales were concentrated among the top 3 models which contributed over 70% of total revenue. The market is still dominated by leading brands like Samsung and Apple.

In summary, the smartphone sales data indicates growing interest in foldable phones, steady sales across quarters, and market concentration among the top brands. Targeting high-growth segments like foldables and improving Pixel sales could be areas of focus going forward.

As you can see, Claude has written a full report that can help you interpret the data and gain useful insights.

How to use AI as a spreadsheet assistant

If you want to learn more AI tools and how you can use them as spreadsheet assistants, read this article on how to generate Google Sheets formulas with AI.

You may also like…