How to use ChatGPT for Excel: A guide for FP&A

Tired of memorizing and writing Excel formulas?

Enter ChatGPT for Excel: a cutting-edge language model developed by OpenAI. Think of it as a digital consultant that also happens to be a pro at Excel. It can help you crunch numbers, analyze financial data, or even draft reports with ease.

In this blog post, you'll learn about some of the best ways to use ChatGPT and Excel to help enhance your productivity and optimize your daily processes. We also discuss:

• How ChatGPT can be used in finance
• ChatGPT vs Copilot: What’s the difference?
• ChatGPT pros and cons
• How to upload data from Excel to ChatGPT
• Using ChatGPT to create Excel formulas
• How ChatGPT can help explain complex formulas
• How to create macros
• Generating financial reports
• Using ChatGPT for data analysis
• ChatGPT FP&A prompts

How ChatGPT can be used in finance

According to Forbes, since hitting 100 million monthly engaged users, ChatGPT set a new record as the most rapidly expanding consumer internet application in history. On top of that, Yahoo Finance reported that UBS analysts estimate the overall potential market value for ChatGPT at a staggering $1 trillion.

So, it should come as no surprise to learn that modern finance teams have jumped at the chance to leverage ChatGPT within their roles, helping to perform activities such as:

• ChatGPT data analysis: Uncovering insights from complex data sets.
• Drafting reports: Creating clear and concise financial documents.
• Forecasting trends: Predicting market movements and identifying opportunities.
• Managing risk: Evaluating potential risks and making informed decisions.
• Automating routine tasks: Streamlining processes and improving efficiency.
• Research assistance: Staying up to date with the latest financial news, market trends, and regulations.
• Decision support: Providing data-driven insights for strategic planning and investment decisions.

ChatGPT vs Copilot: What’s the difference?

Both ChatGPT and Microsoft’s Copilot are AI-powered tools developed by OpenAI, but they serve different purposes.

While ChatGPT is a language model designed to understand and generate human-like text, Copilot is an AI tool specifically tailored for code generation and assistance in software development.

Similarities between Copilot vs ChatGPT

• Both are powered by OpenAI's cutting-edge technology.
• They can understand context and provide relevant suggestions or solutions.
• Both have been trained on massive data sets to deliver accurate and useful outputs.

Differences between ChatGPT vs Copilot

• ChatGPT focuses on natural language processing and understanding, while Copilot is centered on code generation and software development.
• Copilot is integrated with code editors, like Visual Studio Code, whereas ChatGPT is designed for more general-purpose text-based tasks.

ChatGPT pros and cons

This cutting-edge tool has made a significant impact on the financial landscape, streamlining tasks and revolutionizing the way professionals work. However, like any technological advancement, ChatGPT has its pros and cons when applied to finance tasks.

ChatGPT Pros:

• Natural language understanding: ChatGPT excels at understanding and generating human-like text, making it a versatile tool for a wide range of tasks.
• Diverse applications: ChatGPT can be used for tasks such as data analysis, report drafting, forecasting, and research assistance.
• Large knowledge base: Trained on a massive dataset, one of the best benefits of ChatGPT is its extensive knowledge on a variety of topics. This allows it to provide relevant and informed responses.
• Time-saving: It can help automate routine tasks and improve efficiency, freeing up time for finance professionals to focus on more complex or strategic work.
• Decision support: ChatGPT can provide data-driven insights and information to support decision-making and problem-solving in various domains.

ChatGPT Cons:

• Knowledge cutoff: ChatGPT's training data only extends up to September 2021, which means it may not be up-to-date on the most recent information or events.
• Context limitations: While ChatGPT is good at understanding context, it may sometimes misunderstand or misinterpret user inputs, leading to less accurate or relevant responses.
• Output quality: ChatGPT's generated text may occasionally contain errors, inaccuracies, or inconsistencies, which means users must verify and cross-check information.
• Bias and sensitivity: The AI may unintentionally exhibit biases or generate outputs that are politically, culturally, or socially sensitive, as it is trained on data that may contain such biases.
• Dependence on user guidance: ChatGPT's performance largely depends on the clarity and specificity of user input; unclear or ambiguous queries may result in less helpful or accurate responses.

Does ChatGPT save data?

As an AI language model, ChatGPT itself doesn't save data. However, the platform you're using to access ChatGPT might store your conversation history for a certain period. OpenAI, the company behind ChatGPT, has a data retention policy outlining how long user data is stored.

As of September 2021, OpenAI retains API data for 30 days and no longer uses data sent via the API to improve its models. It's essential to review the data storage and privacy policies of the platform you're using to access ChatGPT to understand how your data is handled.

How to upload financial data from Excel to ChatGPT

While ChatGPT doesn't have a built-in feature to directly upload data from Excel or Google Sheets, you can work around this limitation. Here's how:

1. Copy and paste data as plain text

The simplest way to share data from Excel with ChatGPT is by copying the relevant data and pasting it as plain text in the chat window.

Be sure to maintain a clear and organized structure so that the AI can understand the data easily.

For example:

With this ChatGPT dataset, you can now ask the AI tool to analyze or perform specific tasks based on your financial data.

2. Summarize the data and share key details

Another approach is to summarize the data in a brief, concise format before sharing it with ChatGPT. This method can be especially useful when dealing with large datasets but may require more effort on your part in the 'prepping' stage.

For example, if you have a dataset containing monthly revenues, you could share a summary like this:

You can then ask ChatGPT to analyze the data, find trends, or provide insights based on the summary.

3. Use third-party integrations

Developers can also use the OpenAI API to integrate ChatGPT into custom applications, which could potentially facilitate a more seamless connection between Excel, Google Sheets, and ChatGPT.

By doing so, you could programmatically transfer data between these platforms and access the AI's analysis capabilities more efficiently.

Remember that while these methods allow you to share data with ChatGPT, the AI might not always interpret or analyze the information with perfect accuracy. Be prepared to review the responses and make any necessary adjustments to ensure the desired results.

How to use ChatGPT to write Microsoft Excel formulas

Instead of manually entering complex formulas, you can now ask ChatGPT to generate them for you. Simply type your query in plain English, and ChatGPT will provide the appropriate formula, saving you time and reducing the risk of errors.

Here are a few examples of ChatGPT use cases with Excel:

Prompt 1:

ChatGPT told us to use the SUM function and gave us the Excel formula =SUM(B2:B11). So, we pasted that exact formula into cell B12 and the total sum was immediately calculated.

Prompt 2:

Next, we tested using a prompt for a formula to help calculate the 12-month rolling average of monthly expenses in one column only. Here's how ChatGPT responded:

Remember these three tips to help you get the most out of ChatGPT and Excel:

1. Be specific with your request

When asking ChatGPT to generate a formula, provide clear and detailed instructions. For example, instead of asking for "a formula to calculate the total revenue," specify "a formula to sum up the revenue from column B, rows 2 to 100."

2. Double-check the output

ChatGPT is an impressive tool, but it's not infallible. Always verify the generated formula before incorporating it into your spreadsheet. This will help you avoid any inaccuracies that may arise from miscommunication or AI limitations.

3. Iterate your request

If ChatGPT doesn't provide the desired formula on the first try, don't be afraid to rephrase your request or provide additional context. This can often lead to a more accurate result.

How to use ChatGPT to explain Excel formulas

FP&A professionals rely on Excel formulas to help search for specific values, calculate findings, and so on. But what are the most useful Excel formulas used by FP&A teams? And how does each one work?

We asked ChatGPT that question and here’s what it had to say:

As you can see, ChatGPT was able to not only provide examples of the top three Excel formulas for FP&A but also explained each one accurately.

How to use ChatGPT to create macros

Macros are like your personal Excel assistants, taking care of those repetitive tasks for you with just a click of a button. They're programmed using Visual Basic for Applications (VBA) and can handle everything from simple formatting to complex calculations.

Now, imagine combining the power of macros with the intelligence of ChatGPT. Sounds like a dream team, doesn't it? Here's how you can make that dream a reality:

1. Start by getting crystal clear on what you want your macro to do. Jot down the steps, calculations, or any specific details involved in the task.
2. When you're ready to ask ChatGPT for help, channel your inner storyteller and paint a vivid picture of the task at hand. The more information you provide, the better ChatGPT can understand what you need.
3. If your macro feels like a jigsaw puzzle, don't worry! Break it down into smaller, bite-sized pieces, and ask ChatGPT for help with each section. This way, you can build your macro step by step, making it much easier to grasp and manage.
4. Once you've got your shiny new macro from ChatGPT, give it a thorough once-over. Keep in mind that ChatGPT is incredibly smart, but it's not foolproof. Double-checking the code ensures that it's accurate and does exactly what you need it to do.

How to use ChatGPT to generate financial reports

ChatGPT can also help you create comprehensive and visually appealing reports. By asking the AI model to generate charts, tables, or graphs based on your data, you can quickly transform raw data into actionable insights.

You can use ChatGPT with both Excel and Google Sheets to create financial reports such as:

• Income statements
• Balance sheets
• Cash flow statements
• Budget vs. actual reports
• Sales performance reports
• Financial ratios analysis

We asked ChatGPT to provide a mock example of how FP&A can use it to create a quarterly sales performance report. Here’s what it gave us:

This mock example demonstrates how ChatGPT can help FP&A professionals create a financial report by providing a structured summary of sales performance, including key insights and recommendations.

Remember to always review and verify the output to ensure accuracy and relevance.

Tips to effectively use ChatGPT for Excel reports

1. Data preparation: Start by ensuring your data is clean, organized, and formatted correctly. Remove any inconsistencies or errors to enable ChatGPT to work with the most accurate information possible.
2. Be specific with your request: When asking ChatGPT to create charts, tables, or graphs, provide clear instructions and context about your data. For example, instead of requesting a "chart for revenue," ask for a "bar chart representing monthly revenue from January to December 2022."
3. Use templates: Create a set of report templates with predefined sections, charts, and tables. This will help ChatGPT understand the structure and layout you want to maintain, resulting in more accurate and visually appealing reports.
4. Textual explanations and summaries: In addition to visual elements, ask ChatGPT to provide written explanations, summaries, or insights based on your data. This can help you identify trends, patterns, or anomalies that may not be immediately apparent from the charts and tables.
5. Review and refine: Always review the generated reports to ensure accuracy and relevance. While ChatGPT is a powerful tool, it is not infallible. By reviewing and refining the output, you can catch any inaccuracies and make necessary adjustments.
6. Iterate and optimize: As you continue to use ChatGPT for financial reporting, learn from its output and optimize your instructions and templates to yield even better results. Over time, this iterative process will enable ChatGPT to create increasingly accurate and insightful reports.

How to use ChatGPT for Excel data analysis

ChatGPT can be a game-changer for your data analysis process. Here are some ways to harness its potential:

• Data cleaning and preprocessing: ChatGPT can assist you in cleaning and preprocessing your data by identifying inconsistencies, missing values, and outliers, ensuring your analysis is based on accurate and reliable data.
• Descriptive analysis: Use ChatGPT to generate summary statistics, frequency distributions, or visual representations of your data, providing you with an overview of the key trends and patterns.
• Predictive analysis: ChatGPT can help you create predictive models by suggesting relevant algorithms, estimating parameters, and validating your model's performance.
• Scenario analysis: By utilizing ChatGPT's natural language understanding, you can conduct scenario analyses by asking the AI model to forecast outcomes based on different input variables.

ChatGPT prompts for FP&A

To get the most out of ChatGPT, it's crucial to communicate your requests effectively. Use clear and concise language, and don't hesitate to provide context or ask follow-up questions if needed.

Imagine you're a finance professional trying to assess the impact of a proposed merger on your company's financials. Instead of asking ChatGPT, "What happens after the merger?", provide more specific information, like the financials of both companies, the merger terms, and the expected synergies. This will enable ChatGPT to give you a more accurate and insightful analysis.

Here's a list of prompts that you can use to get help from ChatGPT for various FP&A tasks, along with some tips on how to achieve the best and most accurate results:

1. "What are the key drivers affecting our revenue forecast? Please consider the provided historical data and external factors."

Provide historical data and mention specific external factors that could impact your business.

2. "Help me create a financial model to project our company's cash flow over the next five years based on the provided assumptions and historical data."

Share relevant data and assumptions, such as revenue growth rates, expenses, and capital expenditures.

3. "Analyze the provided budget data and suggest areas where we could potentially reduce costs without significantly impacting operations."

Offer a clear overview of your current budget, including detailed expense breakdowns.

4. "Based on our historical sales data, identify any seasonal trends that we should consider in our sales forecast."

Provide sales data with clear date markers to help ChatGPT spot patterns.

5. "What is the optimal capital structure for our company, given our current financials and industry benchmarks?"

Share your company's financials, along with relevant industry benchmarks or ratios.

6. "Evaluate the potential financial impact of implementing a new marketing campaign, considering our current marketing spend and ROI data."

Provide information on your current marketing budget and ROI, as well as details about the proposed campaign.

7. "Help me create a flexible budget model that can accommodate changes in sales volume, production levels, and other variables."

Specify the variables that should be considered and share any relevant data or assumptions.

8. "Analyze the provided sales data by product category and region to identify areas of growth and underperformance."

Offer detailed sales data, including product categories and regional information.

9. "How should we allocate our R&D budget to maximize long-term growth and innovation, considering the provided data on past projects and their outcomes?"

Share information on previous R&D projects, their budgets, and results.

10. "Using the provided financial data, help me determine the breakeven point for our new product line."

Provide information on product costs, pricing, and any relevant fixed or variable expenses.

To get the best and most accurate results from ChatGPT, remember to:

• Be specific and clear in your prompts.
• Provide relevant and well-organized data.
• Offer context and additional information when needed.
• Break down complex tasks into smaller, manageable parts.
• Always review and verify the AI's output for accuracy.

By using these prompts and following the tips above, you can leverage ChatGPT to enhance your daily activities, streamline processes, and make data-driven decisions.

This article was originally posted on the Finance Alliance website.