Getting Started with Copilot in Excel for Finance (Part 1: Basics)

As a finance professional, Excel is your essential toolkit. Let's start with the basics of how Copilot can transform your daily Excel tasks.

Getting Started with Basic Prompts

1. Formatting Financial Data

"Format column B as currency with two decimal places and thousands separator"
"Add accounting format to range A2:D50 with negative numbers in red"
"Format dates in column C to show as MM/DD/YYYY"

2. Simple Calculations

"Sum the values in column B if the corresponding date in column A is this quarter"
"Calculate the percentage change between each row in column C"
"Find the average of the last 12 months of revenue"

3. Basic Data Cleaning

"Remove duplicate transactions from range A2:E100"
"Standardize company names in column B to match the master list in sheet 'References'"
"Convert all text in column D to proper case"

Pro Tips for Basic Operations

1. Be Specific with Ranges

Instead of:

"Format the numbers"

Try:

"Format cells B2:B100 as currency with $ symbol and two decimal places"

2. Use Clear Data Context

Instead of:

"Calculate the total"

Try:

"Calculate the total revenue for each quarter, excluding cancelled orders in column E"

3. Chain Related Commands

"First sort the data by date in column A, then calculate monthly totals, and finally create a pivot table showing monthly trends"

Common Basic Tasks with Examples

Creating Simple Reports

"Create a summary table with total sales by region"
"Generate a monthly comparison showing this year vs last year"
"Build a basic dashboard showing top 5 customers by revenue"

Data Validation

"Add data validation to column C to only allow dates between 1/1/2024 and today"
"Create a dropdown list in column B using the values in range 'References!A2:A20'"
"Flag any numbers in column D that are more than 2 standard deviations from the mean"

Quick Reference: Helpful Prompts

Formatting

• "Make headers bold and center-aligned with light gray background"
• "Apply comma format to numbers over 1000"
• "Add borders around the range A1:E50"

Basic Analysis

• "Show me rows where revenue exceeds expenses"
• "Highlight cells with negative values in red"
• "Create a running total in column E"

Simple Charts

• "Create a line chart showing trend of monthly sales"
• "Make a bar chart comparing regions"
• "Generate a pie chart of expenses by category"

Best Practice Tips

1. Start Simple

   • Begin with basic formatting requests
   • Add complexity gradually
   • Test commands on small ranges first

2. Be Clear and Specific

   • Specify exact ranges when possible
   • Mention column headers by name
   • Include any special conditions

3. Review and Refine

   • Check Copilot's output
   • Save successful prompts for reuse
   • Build on working examples

Next Steps

• Try each example prompt with your own data
• Practice combining multiple commands
• Move on to Part 2 for more advanced financial operations
• Keep track of which prompts work best for your needs

Remember: Start with these basics and get comfortable with the command structure before moving on to more complex financial operations. The key is to be specific and clear in your prompts.