Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for pricing analysts

For pricing analysts who maintain rate lists, check discounts and analyse margin in Excel. The Complete Excel Course with AI Integration has no pricing data of its own, though much of it runs on sales transactions; this page picks out the lessons pricing work uses: percentages and rules for rate changes, lookups for competitor rates and approved discounts, PivotTables and DAX for margin, the waterfall chart, and variance for before and after.

See the plans Watch free lessons

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

At work, you will be able to:

  • Apply rate increases by rule: an across-the-board percentage held in one cell, and IF or IFS for a different increase by category.
  • Analyse margin by product, customer and channel with PivotTables, and with DAX measures for margin percent and percent of total.
  • Compare your rates with competitors' by joining their lists by SKU with XLOOKUP, and show each gap with its sign and colour.
  • Check quotes against approved discounts with XLOOKUP, IF and AND, and list the breaches with FILTER.
  • Show how much of the list rate is realised, from list rate to net, with SUMIFS and a waterfall chart.
  • Model a rate change by setting the margin before and after side by side with variance and percent variance.

Projects

What you will build.

The course has 14 projects. These five matter most here.

  • Excel PivotTables and a PivotChart of sales data (SalesPlusPerformance): net sales by product category as a column chart, with State, Region and City filters.

    Project 05

    SalesPlusPerformance

    A sales question answered in seconds, straight from the order data.

    Margin and discount by product, customer and channel from order-level data, with slicers to compare one channel with another.

  • Power Query sales report in Excel (SalesFusion): sales by category, sales by region and a monthly trend, with slicers for channel, year, category and region.

    Project 12

    SalesFusion

    A sales report that refreshes itself from a folder of files.

    Transaction exports turned into a model where DAX gives margin percent by product, customer or channel.

  • XLOOKUP formula in Excel (PeopleInsight): each employee row looks up its location from another sheet.

    Project 06

    PeopleInsight HR workbook

    Employee data joined across sheets with one formula.

    Competitor rates and approved discounts looked up by SKU or customer tier instead of by employee ID.

  • Budget vs actual dashboard in Excel (FinanceGuard): total budget, actuals and variance cards above a monthly line chart and a column chart by department.

    Project 09

    FinanceGuard

    The budget vs actual report finance teams send every month.

    Its variance and custom format lessons, used to set your rate against a competitor's and show the gap by sign.

  • Project 03

    Sales Goal Tracker

    IF, IFS and SUMIFS on quotes and invoices: increases by category, discounts over their limit, revenue by customer.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 58 of them, 4 h 51 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 10 Relative vs absolute references in Excel

Begin at Lesson 10 if you can enter data and total a column: rate changes start with percentages and fixed references. New to Excel? Begin at Lesson 1. If SUMIFS and XLOOKUP are familiar, go to Lesson 55 for the waterfall chart, or to Lesson 153, where Power Query and the DAX margin measures begin.

58 of 186 lessons · 4 h 51 min

01 Microsoft Excel fundamentals: building a foundation from scratch 3 of 13 lessons · 10 min A rate increase held in one cell with an absolute reference, percentages for discounts and margins, and IFERROR for a product with no sales. The lessons build a personal budget.
  1. 10 Start here Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  2. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
  3. 12 Common Excel errors and how to fix them DIV0, NAME, VALUE errors and IFERROR 4 min
04 Essential formulas and functions (IF, SUMIF and more) 10 of 13 lessons · 59 min IF and IFS for increases by category, AND and OR to flag a discount beyond its approved level, then SUMIF, SUMIFS and COUNTIFS for revenue, discounts and quotes by product, customer and salesperson. The lessons use sales data.
  1. 28 Logical functions in Excel TRUE and FALSE comparisons for beginners 5 min
  2. 29 The IF function in Excel Pass or fail results with a single condition 3 min
  3. 30 IF function practice in Excel Categorise sales data with IF formulas 6 min
  4. 31 Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  5. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  6. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  7. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  8. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  9. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  10. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
06 Data visualisation: charts and graphs 4 of 9 lessons · 21 min A combo chart for volume against margin percent, chart formatting, sparklines for each product's trend, and the waterfall chart that walks from list rate to net.
  1. 52 Combo charts in Excel Column and line chart with a secondary axis 4 min
  2. 53 Formatting charts in Excel Titles, data labels, colours and chart styles 7 min
  3. 54 Sparklines in Excel Line, column and win-loss mini charts in cells 5 min
  4. 55 Funnel, Pareto and waterfall charts in Excel Modern charts for business data 5 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables over transaction data: revenue, discount and margin by product, customer and channel, grouping by date, slicers and PivotCharts. The lessons use order-level sales data.
  1. 57 PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
  2. 58 Preparing data for PivotTables Clean data with TRIM, PROPER, find and replace 5 min
  3. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  4. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  5. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
  6. 62 Slicers in Excel Filter PivotTables with clickable buttons 3 min
  7. 63 PivotCharts in Excel Create interactive charts from a PivotTable 5 min
  8. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  9. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
  10. 66 Excel Ninja Challenge PivotTable sales by region, category and monthly trend 1 min
08 Advanced Excel functions: lookups, text and arrays 5 of 12 lessons · 21 min XLOOKUP, VLOOKUP and INDEX and MATCH join competitor rates or approved discounts by SKU or tier, with IFERROR for a product a competitor does not list. The lessons look up employee data.
  1. 67 Lookup functions in Excel The PeopleInsight HR dataset and why lookups matter 2 min
  2. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  3. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  4. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  5. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
08 Advanced Excel functions: lookups, text and arrays 3 of 12 lessons · 15 min UNIQUE for lists of products or customers, FILTER for quotes beyond their approved discount, and SORT and SORTBY to rank products by margin.
  1. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  2. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  3. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 2 of 10 lessons · 6 min Variance and percent variance, and custom number formats that show a gap in colour and with its sign: before and after a rate change, or your rate against a competitor's. The lessons compare budget with actual.
  1. 118 Budget variance in Excel Calculate variance and percent variance 4 min
  2. 119 Custom number formats in Excel Show budget variances with colours and signs 2 min
15 SalesFusion: advanced data tools and a Power Query modelling system 21 lessons · 2 h 7 min SalesFusion: Power Query cleans and combines transaction exports and adds profitability columns; Power Pivot relates the tables and DAX measures give total sales, margin percent and percent of total. The Power Pivot lessons need Excel for Windows.
  1. 153 SalesFusion Power Query project Excel ETL pipeline and data model overview 5 min
  2. 154 Introduction to Power Query in Excel The ETL workflow explained 5 min
  3. 155 Import a CSV with Power Query Get data from text and transform data 2 min
  4. 156 Clean columns in Power Query Remove and rename columns for modelling 5 min
  5. 157 Clean text in Power Query Trim, clean, replace values and fix text case 6 min
  6. 158 Data types in Power Query Fix date, whole number, decimal and text types 7 min
  7. 159 Date columns in Power Query Add year, month and year-month fields for reports 7 min
  8. 160 Business logic in Power Query Custom and conditional columns for profitability 6 min
  9. 161 Group By in Power Query Summarise sales by month and region to validate data 5 min
  10. 162 Close and Load in Power Query Connection only, table or Data Model 5 min
  11. 163 Merge queries in Power Query Look up product and region names with a left join 5 min
  12. 164 Reuse a Power Query pipeline Duplicate queries for a new year of sales data 5 min
  13. 165 Append queries in Power Query Combine 2024 and 2025 sales tables 3 min
  14. 166 From Folder in Power Query Combine every sales file in a folder automatically 9 min
  15. 167 From Folder automation in Power Query Profit metrics and fiscal year columns 7 min
  16. 168 Build a sales fact table in Power Query Merge lookups and append history 6 min
  17. 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
  18. 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
  19. 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min
  20. 172 Interactive Excel dashboard Data model PivotTables, slicers and styling 10 min
  21. 173 Unpivot columns in Power Query Turn wide month columns into a long table 8 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Is there an Excel course for pricing analysts?

Yes. The Complete Excel Course with AI Integration from Coding Liquids teaches the Excel a pricing analyst works in: rate increases applied by rule with IFS and fixed percentages, competitor rates joined by SKU with XLOOKUP, discounts checked against approved levels with IF and FILTER, margin by product, customer and channel in PivotTables and DAX, and a pricing waterfall from list rate to net. The 58 lessons on this page take 4 hours 51 minutes.

Can I build a pricing waterfall from list rate to net in Excel?

Yes. Lesson 55 in The Complete Excel Course with AI Integration builds waterfall charts, and the steps of a pricing waterfall are figures you total first: the list rate, each discount and rebate, freight and the net rate, added up with SUMIFS for a product or a customer. Custom number formats from the FinanceGuard lessons show each step with its sign and colour.

Does the course teach elasticity or pricing optimisation?

No. The Complete Excel Course with AI Integration teaches Excel. Elasticity estimation, pricing optimisation and pricing software are not taught. A rate change can still be modelled with the formulas it does teach: a new rate, your own assumption for the change in volume, and the margin before and after set side by side with variance and percent variance.

How do I check quotes against approved discounts?

With lookups and logic from The Complete Excel Course with AI Integration: XLOOKUP brings the approved discount for each customer's tier onto the quote list, IF and AND flag a quote whose discount goes beyond it, and FILTER lists the breaches on a sheet of their own for review. COUNTIFS counts breaches by salesperson, and SORTBY puts the largest first.

Can I measure margin by product, customer and channel?

Yes, two ways in The Complete Excel Course with AI Integration. PivotTables total revenue and cost by product, customer and channel and group them by month, with slicers to compare. The SalesFusion lessons go further: Power Query cleans the transaction export, Power Pivot relates the tables, and DAX measures give margin percent and percent of total. The Power Pivot lessons need Excel for Windows.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

See the plans Watch free lessons

The same course

For another job, level or topic.

By job

Finance and accounting
HR and office administration
Operations and supply chain
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
Technology

By level and topic