Subscribe :: Sponsor :: Read Online

Hi Friends 👋

Today I will walk you through a framework for using ChatGPT to give you superpowers within your Excel or Google spreadsheets.

I started integrating ChatGPT with spreadsheets in March 2023.

Since then, I've boosted my spreadsheet output by 4x and saved countless hours fixing formula errors.

Want to know a secret?

I (pretty much) use the same 9 ChatGPT prompts every time. I’m covering them in this week’s deep dive.

Also in today’s email:

• An easy way to search your history in ChatGPT

• Using Advanced Data Analytics (previously called Code Interpreter) to create spreadsheet templates

• and more…

Let’s get started…

💬 ChatGPT History Search

If you find searching ChatGPT search history hard you need to read this. I use a simple Chrome extension to search my chat history. With this, it is easy to find my favourite conversations. It takes 30 seconds to install, is free for 20 history items and only $4.99 one-off to buy [Link]*

Deep Dive

Master Excel & Google Sheets with ChatGPT: A Guide for Business People to Save Hours Each Week

Why you should care: If you use spreadsheets, you can boost your output 4x with the prompts in this deep dive. That frees you up to focus on revenue-generating tasks instead of fixing formula errors.

What you need to know: There are several ways to use the prompts here to make things easier, such as getting help with formulas or having ChatGPT do everything for you. As you learn these techniques, you’ll find yourself using spreadsheets in combination with ChatGPT Advanced Data Analytics too.

On with the (short) story…

My first office job was managing logistics for a medical manufacturer.

It was about organising moving supplies by the truckload across Europe.

The logistics tool of choice? Complex Excel sheets.

Yet, one mistake and trucks would go off course.

Sometimes, that mistake was mine. I was still learning.

A formula error could mean 20 tonnes of supplies 2000 miles from its intended destination. And that happened every week.

But Wayne, the spreadsheet wizard? He sat across from me and knew Excel inside out.

He made no errors, ensuring his trucks reached Spain, not Germany!

Now, with ChatGPT, we can all match Wayne’s spreadsheet skills.

Here’s how…

9 ChatGPT To Prompts To Become a Spreadsheet Wizard Overnight

As always, copy and paste the prompts. If you’re confused try out the first basic ones and find your feet. Remember, getting better at ChatGPT is a journey, not a destination.

Good news? This guide is designed for popular spreadsheet tools - Google Sheets, Excel, and Mac Numbers.

1: Find the Perfect Formula

Straightforward and powerful - bookmark it.

Here’s an example: I want to count the number of cells with a value of more than 21.

2: Find a Solution To Your Problem

If you’re stuck for solutions, use this prompt.

Here’s the output for an efficiency question. See the full chat here.

3: Find & Delete Duplicates

Use this prompt to identify and remove duplicates quickly.

Then answer the questions ChatGPT ask to get the help you need, eg:

Then ChatGPT will give you easy instructions to delete your duplicates.

4: Merge and Match Data - VLOOKUP Made Simple

VLOOKUP is the best. It lets you find matches and pull related data from other sheets. It’s also really hard to get it to work correctly. Not any more with this prompt:

5: Get Help With Advanced Google Sheets Functionality

This prompt will unlock new functions and level up your spreadsheet game

Here are the results of the example prompt: “How do I add a tick box in a Google Sheet?”

Another example: How do I automatically add a date on a new row entry in Google Sheets?

So now it is easy to get help on the advanced features.

6: Using The ChatGPT for Scalable Prompting in Google Sheets

This allows you to run multiple ChatGPT prompts simultaneously.

This tool is an absolute game-changer when generating SEO titles, creating ads for Google Ads, or processing any data set with a repetitive prompt.

What it does

It runs a ChatGPT prompt within Google Sheets, instead of the normal ChatGPT interface. This means you can run 1000s of prompts simultaneously, like copying formulas in Sheet.

How it works

The GPT for work Google Sheets plugin lets you create a formula like this:

=GPT(your prompt)

eg =GPT(B3)

This formula runs whatever is in the brackets as a ChatGPT prompt and gives you the output:

To set it up, install the Google Sheets GPT for Work Plugin and add your Open AI API key to the settings. You can get an OpenAI API key by getting an OpenAI developer account.

7: Data Modeling Using ChatGPT Instead of Excel

If you hate formulas, then you’ll love this.

You can bypass spreadsheets totally and use ChatGPT’s Advanced Data Analytics (previously called Code Interpreter) for data analysis.

Just select the Advanced Data Analysis chat option under GPT4, then hit the (+) to upload your data as a CSV file. Finally, tell ChatGPT what to model using your data.

Here’s a budget modelling example:

I gave it a CSV with data, and it gave me this output:

The next example is a statistical analysis prompt for use with another data file you upload:

So you can forget trying to find what formulas you need and get ChatGPT to work it all out for you…

It’s way quicker than working with formulas in spreadsheets.

Where I use Chat Advanced Data Analysis instead of Excel:

• Complex Algorithms: Intricate tasks like searching or sorting.

• Text Manipulation: For deeper tasks like pattern matching or removing duplicates.

• Looping and Iteration: Some spreadsheets loop. But Advanced Data Analysis offers more control.

• Logic and Conditions: Advanced Data Analysis makes complex decisions clear and direct.

• Simulation: Want to run simulations? Use Advanced Data Analysis.

• Generating templates: Need a template made? Just ask ChatGPT.

Here’s the template example…

8: Get Ideas on How to Solve Your Problem and Build a Sheet

Here’s my example running this prompt:

”ChatGPT, I am trying to create a simple inventory tracking system for my coffee beans and other supplies within Google Sheets. Using your knowledge of spreadsheets, can you please provide a solution to my problem? If there is multiple solutions, ensure you provide the simplest solution. Ask me some questions to clarify exactly what I need, and once you have all the information create an XLS file for me to download.”

9: Bonus: Using Bard To Find and Extract Datasets

Just had to add this in, and it is super helpful if you are collating data.

Use Google’s Bard to find datasets and export them into Google Sheets.

Here’s an example asking for USA state population data:

Steps

1. I prompted Bard with: “I want you to find USA population data by state, including population size, annual population growth, average age and give me the source URL”

2. Click on Export to Sheets to get the data in a Google Sheet

Boom!

And just like that, you’ve gone from not knowing Google Sheets well to writing formulas, analysing data, creating sheet templates and finding and exporting data in a matter of minutes.

Because you have learned to leverage the power of ChatGPT to accelerate your spreadsheet skills AND your data analytics!

If you enjoyed today's issue please do reply (it helps with deliverability). If you didn't you can unsubscribe at the bottom.

If someone forwarded you this email, you can subscribe here.

P.S. Want to sponsor? Details here (Sponsored content is denoted by *)

Thanks for reading,

Tim

Tim O'Shea
​Let's connect on Twitter, Linkedin or Instagram