Advanced Excel

1. Excel Fundamentals Refresher

This program builds on basic Excel skills, so we start with a quick refresher on the concepts every advanced technique depends on: cell referencing. Getting this right is the difference between a formula that works when copied across a sheet, and one that breaks.

• Relative reference (e.g. B2): changes automatically when you copy the formula to another cell.
• Absolute reference (e.g. $B$2): stays locked to the same cell no matter where you copy the formula. Use it for values like a tax rate or exchange rate that every row should reference.
• Mixed reference (e.g. $B2 or B$2): locks only the column or only the row.
• Press F4 after clicking a cell reference in a formula to quickly cycle between relative, absolute, and mixed.

Practical Skill: Build a price list where every item's total is Quantity × Unit Price × (1 + Tax Rate), with the tax rate in one locked cell referenced by every row using an absolute reference.

=== TAX CALCULATION SHEET ===

      A            B          C            D
1     Tax Rate:    0.05
2
3     Item         Qty        Unit Price   Total
4     Notebooks    10         1,500        =B4*C4*(1+$B$1)
5     Pens         50         200          =B5*C5*(1+$B$1)
6     Folders      20         800          =B6*C6*(1+$B$1)

Notice: $B$1 stays locked to the tax rate cell when the
formula in D4 is copied down to D5 and D6 — but B4, C4
shift automatically to B5/C5 and B6/C6.

2. Core Functions for Real Work

Beyond SUM and AVERAGE, a handful of functions cover most real business needs: conditional logic and conditional counting/summing.

• =IF(condition, value_if_true, value_if_false) — e.g. =IF(B2>=50,"Pass","Fail").
• =COUNTIF(range, criteria) — counts cells matching a condition, e.g. =COUNTIF(C2:C50,"Present").
• =SUMIF(range, criteria, sum_range) — adds values matching a condition, e.g. total sales for one region.
• Nesting functions: functions can be combined, e.g. =IF(COUNTIF(C2:C50,"Absent")>5,"At Risk","OK").

Practical Skill: Build a class or staff attendance sheet that automatically counts total "Present" and "Absent" days per person using COUNTIF, and flags anyone with more than 5 absences using IF.

=== ATTENDANCE TRACKER ===

      A            B     C     D     ...   H            I
1     Name         Mon   Tue   Wed         Days Present  Status
2     Amuor D.     P     P     A           =COUNTIF(B2:G2,"P")   =IF(H2<10,"At Risk","OK")
3     Deng K.      P     P     P           =COUNTIF(B2:G2,"P")   =IF(H2<10,"At Risk","OK")

=== FUNCTIONS USED ===
  COUNTIF(range, "P")     Counts "Present" marks
  SUMIF(range, "North", sales_range)   Totals sales for "North" region only
  IF(H2<10, "At Risk", "OK")           Flags low attendance

3. Lookup Functions — VLOOKUP & INDEX/MATCH

Lookup functions let you pull matching information from another table instead of retyping it — essential for anything with a "list of items" and a "list of details," like a product catalog or staff directory.

• =VLOOKUP(lookup_value, table_range, column_number, FALSE) — finds a value in the first column of a range and returns a value from another column in the same row. Always use FALSE for an exact match.
• =INDEX(range, MATCH(lookup_value, lookup_range, 0)) — a more flexible alternative to VLOOKUP that can look left as well as right.
• Data Validation (Data → Data Validation): restrict a cell to a dropdown list of allowed values — prevents typos like "Nairobi" vs "nairobi" vs "Niarobi" in the same column.

Practical Skill: Build a simple inventory sheet with a product code list on one tab and a sales log on another tab that uses VLOOKUP to automatically pull the product name and price from the code entered.

=== SHEET 1: "Products" ===
      A          B                C
1     Code       Name             Price (SSP)
2     P001       Exercise Book    1,200
3     P002       Ballpoint Pen    300
4     P003       USB Flash Drive  8,500

=== SHEET 2: "Sales" ===
      A          B                       C
1     Code       Name                    Price
2     P002       =VLOOKUP(A2,Products!A:C,2,FALSE)   =VLOOKUP(A2,Products!A:C,3,FALSE)

Result: typing "P002" in A2 automatically fills in
"Ballpoint Pen" and "300" — no retyping, no typos.

4. Summarizing Data with PivotTables

A PivotTable turns a long, raw list of data into a quick summary — total sales per region, attendance per month, expenses per category — without writing a single formula.

• Select your data range → Insert → PivotTable. Drag fields into Rows, Columns, and Values to build the summary.
• Change how values are summarized (Sum, Average, Count) by clicking the field in the Values area and choosing "Value Field Settings."
• A PivotChart can be created directly from a PivotTable to visualize the same summary.
• Refresh a PivotTable after changing the source data with Right-click → Refresh, or it will show outdated numbers.

Practical Skill: From a raw list of sales transactions (date, region, product, amount), build a PivotTable showing total sales by region and by month, then add a PivotChart.

=== RAW DATA (before) ===
Date        Region    Product      Amount
2026-01-05  North     Notebooks    45,000
2026-01-06  South     Pens         12,000
2026-01-07  North     Pens         9,000
... (hundreds of rows) ...

=== PIVOTTABLE SETUP ===
Rows:    Region
Columns: Month (grouped from Date)
Values:  Sum of Amount

=== RESULT (after) ===
Region     Jan        Feb        Total
North      54,000     61,000     115,000
South      12,000     18,500     30,500

[ ] Select the full raw data range including headers
[ ] Insert -> PivotTable -> New Worksheet
[ ] Drag "Region" to Rows, "Date" to Columns (group by Month)
[ ] Drag "Amount" to Values -> confirm it shows "Sum of Amount"
[ ] Insert -> PivotChart to visualize the same summary

5. Building a Simple Dashboard

A dashboard is one screen that shows the most important numbers at a glance — useful for reporting to a manager, a board, or a donor without making them read a raw spreadsheet.

• Combine 2-4 PivotCharts or summary tables on a single sheet.
• Use Conditional Formatting (Home → Conditional Formatting) to automatically color-code values — e.g. red for over-budget, green for under-budget.
• Keep the raw data on a separate, hidden sheet so the dashboard sheet stays clean and doesn't get accidentally edited.
• Add a title and date to the dashboard so anyone printing it knows what they're looking at and when it was last updated.

Practical Skill: Combine your attendance tracker, sales summary, and budget sheet into a one-page dashboard with conditional formatting highlighting any figures that need attention.

=== ONE-PAGE DASHBOARD LAYOUT ===

+----------------------------------------------------+
| ORGANIZATION NAME — MONTHLY DASHBOARD (March 2026)  |
+----------------------------------------------------+
| [Chart: Sales by Region]   | [Chart: Attendance %]  |
|                             |                        |
+----------------------------------------------------+
| Budget Summary Table (conditional formatting)       |
|   Item         Planned    Actual    Status           |
|   Transport    5,000      6,200     [RED — over]     |
|   Supplies     12,000     11,000    [GREEN — under]  |
+----------------------------------------------------+

=== CONDITIONAL FORMATTING RULE EXAMPLE ===
  Select the "Status" or "Actual" column
  Home -> Conditional Formatting -> New Rule
  Format cells where: Actual > Planned
  Apply: Red fill
  Format cells where: Actual <= Planned
  Apply: Green fill

Quick Quiz — Advanced Excel Basics