The Complete Excel Course with AI Integration

How to build a sales goal tracker in Excel

A sales tracker earns its keep when it answers a manager's questions before they are asked: who hit target, who is close, who earns a bonus, which deals need a call. In The Complete Excel Course with AI Integration you build exactly that: the Sales Goal Tracker, an industry-standard project designed after talking with people from many different industries, made from a starter workbook of sales records with the finished tracker to compare. Every rule in it is a formula you write, read and change, in Excel alone.

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

You will be able to:

  • Mark every deal achieved or missed against its quota with IF, inside an Excel table that fills each new formula down the column.
  • Grade results as achieved, near target or underperformed with nested IF, and sort deals into performance and priority bands with IFS.
  • Decide bonus eligibility with IF and AND, and raise risk alerts and follow-up flags with IF and OR.
  • Total sales for one product, region or customer rating with SUMIF, and stack conditions with SUMIFS and COUNTIFS.
  • Prove the sales data is complete with COUNT and COUNTA, and count the distinct products with UNIQUE.
  • Count the days since each sale, set a follow-up date and put a live date and time stamp on the report.

Projects

What you will build.

The course has 14 industry-standard projects. These three matter most here.

  • Project 05

    SalesPlusPerformance

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

    Built on order-level sales data, it is the next step for a team's numbers: PivotTables that total sales by category, region and month in a few clicks, with slicers and PivotCharts.

  • Project 04

    Marketing Engagement Dashboard

    Nine charts, each picked for the question it answers.

    Built on marketing figures, it teaches the chart for sales against target: a combo chart with one series as columns and another as a line, plus sparklines that fit a trend inside a cell.

Tap or pinch to zoom in, drag to move around. Click or scroll to zoom in, drag to move around.

  • Project 03

    Sales Goal Tracker

    The tracker this page builds: a table of sales with a quota on every row, where formulas judge each deal, grade it, flag bonus, risk and follow-up, and answer the totals a manager asks for.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 28 Logical functions in Excel

Start at Lesson 28: TRUE, FALSE and IF come first on a class's marks, the sales table arrives at Lesson 30, and the tracker grows one rule at a time up to the Ninja Challenge at Lesson 40. New to formulas? Take Lesson 10 and Lesson 11 first, and Lesson 19 for tables. With the combo chart of Lesson 52, you can then put sales and target on one chart.

17 of 186 lessons · 1 h 35 min

01 Microsoft Excel fundamentals: building a foundation from scratch 2 of 13 lessons · 6 min Dollar signs that pin a formula to one cell, and one figure as a percentage of another, practised on a personal budget: what a commission rate kept in its own cell and sales as a share of target are made of.
  1. 10 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
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 1 of 9 lessons · 8 min Excel Tables, practised on a staff list: the structure the Sales Goal Tracker runs on, where new rows join the table and the total row keeps counting them.
  1. 19 Excel tables Create and format a table, structured references and total row 8 min
04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min The Sales Goal Tracker itself, one rule at a time: TRUE and FALSE tests and IF on a class's marks, then a table of sales with quotas where IF marks each deal achieved or missed, nested IF and IFS set the bands, AND decides bonuses, OR raises risk and follow-ups, COUNT, COUNTA and UNIQUE check the data and SUMIF, COUNTIF, SUMIFS and COUNTIFS total it, with the function library, dates and a Ninja Challenge to close.
  1. 28 Start here 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
  11. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  12. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
  13. 40 Excel Ninja Challenge COUNTIF, SUMIF, IF, COUNTIFS, SUMIFS and IFS practice 8 min
06 Data visualisation: charts and graphs 1 of 9 lessons · 4 min Combo charts, practised on monthly revenue and sign-ups: one series as columns and another as a line in a single chart, the layout for sales against target.
  1. 52 Combo charts in Excel Column and line chart with a secondary axis 4 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.

How do I build a sales goal tracker in Excel?

Put a quota on every row of your sales table, then let formulas judge each deal: that is how The Complete Excel Course with AI Integration from Coding Liquids builds the Sales Goal Tracker, an industry-standard project. The table holds date, salesperson, region, client, product, sales amount, customer rating and quota; formulas mark every deal achieved or missed, grade it, decide bonus eligibility, flag risk and follow-ups, and answer the totals a manager asks for. You work from the starter workbook, check against the finished one, and this page's path runs 17 lessons (1 hour 35 minutes).

What is the formula for sales target achievement in Excel?

Start with IF: =IF(F2>=H2,"Achieved","Missed") compares a deal's sales amount in F2 with its quota in H2, the first rule The Complete Excel Course with AI Integration writes into the Sales Goal Tracker (Lesson 30). Nest a second IF and a deal at 90 per cent of quota or more reads Near target, the rest Underperformed (Lesson 31). For the achievement percentage itself, divide sales by quota and format the result as a percentage, the method Lesson 11 uses on spending against income.

Can the tracker calculate sales commission and bonuses?

Bonuses it decides already, and commission is one column away. The Complete Excel Course with AI Integration has the Sales Goal Tracker mark a deal Eligible only when it beats quota and its customer rating is Excellent, IF and AND working together (Lesson 33). For commission, add a rate column whose IFS picks a rate by the size of each deal, as Lesson 32 picks a performance band, then multiply the sales amount by it; keep the rates in cells of their own, fixed with dollar signs (Lesson 10), so a new commission plan is a single edit.

How do I track sales team performance in Excel?

Label every deal by performance, then count and total the labels. In The Complete Excel Course with AI Integration, IFS sorts each sale into Top performance, Strong, Average or Low by its amount (Lesson 32), a second rule picks out strong deals outside the West region, which runs its own incentive plan (Lesson 33), and COUNTIF counts how many deals sit in any band (Lesson 40). Put a salesperson's name in SUMIF's criteria for each person's total; for the whole team by region and month, a PivotTable does it in a few clicks (Lesson 61).

How do I find total sales in Excel?

SUM for the whole column, SUMIF or SUMIFS for a slice of it. The Complete Excel Course with AI Integration totals the sales amount with a SUM written on the whole table column, which stays connected as new sales are added (Lesson 34); SUMIF gives the total for one product, one region or one customer rating (Lesson 36); and SUMIFS stacks conditions, such as one product in one region or North deals above a threshold (Lesson 37). Wrap SUM round FILTER and it adds up the quota of a single region too.

How do I track monthly or daily sales in Excel?

Keep every sale as one dated row and let the dates do the sorting. The Complete Excel Course with AI Integration gives each sale its month number with MONTH, counts the days since it with TODAY, sets a follow-up date ten days later by simple addition and stamps the report with NOW (Lesson 39); COUNTIFS with a start date and an end date counts the deals inside one month, and SUMIFS takes the same conditions to total their value (Lesson 37). A PivotTable on the order dates then rolls daily sales up into months (Lesson 61).

Can the tracker tell me which clients to follow up?

Yes, by rule instead of by memory. The Complete Excel Course with AI Integration has the Sales Goal Tracker write Follow-up needed against any deal whose customer rating is Average or whose region is South, an OR test you can rewrite with your own reasons (Lesson 33), and a Risk alert on any deal rated Poor or below a minimum size. Add the days since each sale and a follow-up date ten days after it (Lesson 39), filter the table to the rows that need a call, and your follow-up list builds itself.

Should I use a free sales tracker template or build my own?

A template has to guess what achieved, near target and a bonus mean in your team; build the tracker yourself and you decide. In The Complete Excel Course with AI Integration you write each rule as a formula, so when targets, bands or the bonus policy change, one formula changes and the whole table follows. You begin from the starter workbook and its sales data, finish with the complete tracker to compare, and test yourself on a Ninja Challenge with its own starter and solution files. Lifetime access keeps every lesson open for the day your plan changes.

Excel with AI

See the whole course.

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

The same course

For another job, goal, level, topic or comparison.