SQL + Excel Dashboard Automation: Power Query, Copilot & AI

Still preparing daily sales reports manually? Learn how to connect database queries with Microsoft Excel, automate repetitive reporting tasks, and build an interactive business dashboard using a practical, beginner-friendly project.

In this hands-on course, you will follow a sales reporting scenario to understand how data moves from a database to a business-ready Excel dashboard. Instead of repeatedly exporting CSV files, fixing formulas, and updating charts manually, you will explore a more efficient reporting workflow using SQLite, SQL commands, Windows Command Prompt, Excel, Power Query, and Microsoft Copilot.

What You Will Learn

• Understand how databases, tables, records, and columns work using a realistic sales dataset.

• Set up and work with SQLite, a free database engine, without purchasing Microsoft SQL Server.

• Create a sales database and table using SQL commands.

• Insert sample sales transactions and retrieve records using SQL queries.

• Analyze sales performance by region and product category using aggregation and grouping.

• Connect data to Microsoft Excel and explore Power Query for data import and transformation.

• Prepare clean, structured data for reporting and dashboard development.

• Build an Excel sales dashboard with KPI cards, charts, and business performance summaries.

• Explore Microsoft Copilot to assist with data analysis, formulas, and dashboard development, where supported by your Microsoft 365 plan.

• Understand how refreshing data can reduce repetitive manual reporting work.

Who Should Take This Course?

This course is designed for MIS executives, reporting specialists, business analysts, data analysts, team leads, managers, Excel users, and professionals who want to improve their business reporting skills.

It is also suitable for beginners who want a practical introduction to SQL and database concepts before applying them to Excel reporting.

What Makes This Course Practical?

You will work through a realistic sales reporting example, starting with a new database and sample transactions, then progressing toward SQL analysis, Excel integration, data transformation, and dashboard creation.

The goal is to help you understand the complete workflow—not just individual commands or Excel features—so you can apply the same approach to suitable reporting tasks in your own workplace.

Tools Covered

• SQLite and SQL

• Windows Command Prompt

• Microsoft Excel

• Power Query

• Microsoft Copilot and AI-assisted workflows

You do not need to purchase SQL Server for the SQLite exercises. You will need access to a compatible version of Microsoft Excel for the Excel-based activities. Copilot availability and features depend on your Microsoft 365 subscription, license, and Excel version.

Start learning how to transform raw sales data into useful business insights with Discover Talent.

Build practical skills. Reduce repetitive work. Make your reporting workflow smarter.

In this lecture, you'll learn how to populate your SQLite database with 20 sample sales transactions and verify that your data is ready for business analysis.

Using a fictional sales company as an example, we'll work with order IDs, order dates, regions, product categories, and sales amounts. You'll understand how SQL INSERT statements add records to a database and how SELECT queries help you retrieve and validate the stored information.

What you'll learn:

• Insert multiple sales transactions into a SQLite database using SQL.

• Understand how each record maps to the columns in your sales table.

• Use SELECT COUNT(*) to verify the total number of transactions.

• Retrieve sales records using SELECT * FROM Sales.

• Display column headers and sort records by OrderID.

• Understand why validating source data is important before analyzing business performance.

We'll also connect the exercise to a practical business question: which region generates the highest revenue? You'll see why structured data is the foundation for SQL analysis, Power Query transformations, and Excel dashboard development.

By the end of this lecture, you'll have a populated sales database containing 20 sample transactions and the knowledge to verify your records before moving to the next stage of the reporting workflow.

Continue to the next lecture to calculate total sales by region using SQL aggregation and sorting.

Tools used: SQLite and Windows Command Prompt.

In this practical lecture, you'll learn how to transform raw sales transactions into meaningful business insights using SQL and begin connecting your database to Microsoft Excel for reporting automation.

We start by analyzing sales performance across different regions using SQL aggregate functions, GROUP BY, and ORDER BY. You'll see how a simple SQL query can summarize multiple transactions and help identify high-performing and low-performing regions.

Next, we'll move from the SQLite command-line environment to Microsoft Excel and explore how an ODBC (Open Database Connectivity) driver enables Excel to communicate with a SQLite database.

What you'll learn:

• Analyze sales data by region using SQL GROUP BY.

• Calculate and rank total sales using SUM() and ORDER BY.

• Interpret query results to identify regional sales performance.

• Understand the role of ODBC in connecting databases with Excel.

• Locate Excel's Data and Get Data options.

• Understand why a compatible SQLite ODBC driver is required for this connection method.

• Begin configuring a data source name (DSN) for the SQLite sales database.

This lecture establishes the foundation for the next stage of the project: bringing database data into Excel, transforming it with Power Query, and developing a business dashboard.

Practical tools covered: SQLite, SQL, Windows Command Prompt, Microsoft Excel, and SQLite ODBC.

Note: ODBC setup depends on installing a compatible driver and configuring the correct database file path. Follow the connection steps carefully to ensure Excel can access your SQLite database.

Learn how to connect SQLite to Microsoft Excel and prepare database data for business reporting using Power Query and Microsoft Copilot.

In this practical lesson, we continue building our automated sales reporting workflow. After configuring the SQLite ODBC connection, you'll use Excel's Get Data options to access the sales database and bring its records into Power Query.

What you'll learn:

• Configure and use a SQLite ODBC data source to connect Excel with your database.

• Navigate to Excel's Data tab and select the appropriate data connection.

• Import sales records from SQLite into Power Query.

• Understand how Power Query helps import, transform, and refresh supported data sources.

• Assign appropriate data types to Order ID, Order Date, Region, Product Category, and Sales Amount.

• Load the transformed dataset into an Excel worksheet.

• Explore Microsoft Copilot to analyze sales data and plan dashboard KPIs.

• Identify potential dashboard metrics such as total revenue, total orders, and regional sales performance.

We'll also discuss an alternative approach for learners who do not have Copilot available directly in Excel: using the Microsoft Copilot website with an uploaded workbook, where supported.

By the end of this lecture, you'll understand the core steps involved in bringing SQLite sales data into Excel and preparing it for dashboard development.

Prerequisites: Complete the previous SQLite database and ODBC setup lessons. You will need Microsoft Excel, a compatible SQLite ODBC driver, and access to your sample sales database. Copilot features depend on your subscription and product availability.

Next, we'll build on this foundation to develop a more useful, interactive sales dashboard.

Turn your SQLite sales data into a professional Excel dashboard and learn how to update business reports without manually rebuilding them every time new transactions are added.

In this hands-on lesson, you'll use Microsoft Copilot to help plan dashboard KPIs and visualizations, refine the dashboard layout in Excel, and demonstrate how a database update can flow into your report through a data refresh.

What you'll learn:

• Use AI-assisted prompts to suggest relevant sales KPIs and dashboard layouts.

• Identify key metrics such as total revenue, total orders, average order value, and highest-performing region.

• Review KPI calculations and verify AI-generated suggestions before using them.

• Format dashboard elements, including charts, KPI cards, number formats, colors, and borders.

• Add new sales transactions to your SQLite database using SQL INSERT statements.

• Understand how new database records affect sales reporting.

• Use Excel's Refresh All command to retrieve updated data through the configured connection.

• Observe how dashboard metrics and visuals respond to new transactions.

• Understand the value of combining SQL, Power Query, Excel, and AI-assisted analysis in a repeatable reporting workflow.

Practical outcome

You'll see how a sales reporting process can move from database creation and SQL analysis to an Excel dashboard that can be refreshed when new records are added.

This approach can help reporting specialists, MIS executives, business analysts, data analysts, team leads, and managers reduce repetitive spreadsheet work and make business performance easier to understand.

Tools covered

SQLite, SQL, Windows Command Prompt, Microsoft Excel, Power Query, and Microsoft Copilot.

Requirements: Complete the earlier database and connection lessons. You need a compatible Excel version and a working connection to your SQLite database. Copilot features depend on your Microsoft 365 license and availability.

Important: Refreshing updates the dashboard only when the underlying Excel query or connection is configured correctly. AI-generated calculations and recommendations should always be checked against the source data.