Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 5, Live 2
Excel Track Review, and What Comes Next
The last live class of the Excel track. One question on every topic, the gaps fixed, and a first look at the tool that does what SUMIFS does, on millions of rows.
By the end of this class you can
- Answer one question on each of the 22 topics without looking anything up
- Find your own weakest topics and know where to revise them
- Read a simple SQL query and say what it does
- Explain where Excel ends and SQL begins
Try each question before opening the answer. Keep a list of the ones you got wrong: that list is your revision plan, and each topic says which lesson to go back to.
Part 1: All 22 Topics
Week 1
1. What is data analysis?
Examining data to answer a question so someone can make a better decision.
2. Is Transaction_ID numerical or an identifier?
An identifier. Adding or averaging it means nothing.
3. Name the five workflow stages in order.
Raw data, clean, analyse, visualise, decide.
4. Which tool for a dashboard 40 managers check every morning?
Power BI.
5. What does the Name Box show, and what else can it do?
The address of the selected cell. Type an address in it to jump there.
6. A number sits on the left of its cell. What is probably wrong?
It is stored as text, so SUM will skip it.
7. Why import with From Text/CSV, then Convert to Range, then save as .xlsx?
To check the types in a preview, to keep formulas in plain cell addresses, and because a CSV loses formulas and extra sheets.
8. Name the five dimensions of data quality.
Completeness, consistency, validity, uniqueness, accuracy.
Week 2
9. Remove Duplicates says it removed far more rows than you expected. Why?
Too few columns were ticked, so look-alike rows were treated as duplicates. Ctrl + Z at once.
10. What does =MID("PRD-015",5,3) return, and what type is it?
015, as text.
11. Why is PROPER dangerous on product names?
It changes brands and abbreviations: HP becomes Hp, TV becomes Tv, iPhone becomes Iphone.
Week 3
12. What does a $ do in Settings!$B$2?
Locks the reference so it does not move when filled.
13. COUNT on Quantity and COUNTA on Transaction_ID disagree by 25. Why?
25 Quantity cells are blank; COUNT only counts numbers, COUNTA counts every filled cell.
14. Why did IF label sales with no Revenue as High?
Their Revenue is empty text (""), and in a comparison Excel ranks any text above every number.
15. What does IFS return when no test is true and there is no TRUE at the end?
#N/A.
16. Does conditional formatting change a cell's value?
No, only how it looks.
17. Where does the sum_range go in SUMIF, and in SUMIFS?
Last in SUMIF, first in SUMIFS.
18. How do you count wholesale sales from Abuja?
=COUNTIFS(J2:J1051,"Abuja",K2:K1051,"Wholesale")
19. AVERAGEIF returns #DIV/0!. Most likely cause?
No rows matched: a misspelling or a value that does not exist.
Week 4
20. Why does VLOOKUP break when a column is inserted, and XLOOKUP does not?
VLOOKUP counts to a column number; XLOOKUP points at the column, and Excel moves the reference.
21. A PivotTable says Count of Revenue. What do you do?
Value Field Settings, Sum.
22. When is Power Query better than cleaning with formulas?
When the same kind of file arrives again and again: its steps rerun on Refresh.
| If you missed | Go back to |
|---|---|
| 1 to 4 | Week 1, Live 1 |
| 5 to 7 | Week 1, Live 2 |
| 8 | Week 1, Virtual 2 |
| 9 | Week 2, Live 1 |
| 10 | Week 2, Virtual 1 |
| 11 | Week 2, Live 2 |
| 12 to 15 | Week 3, Live 1 |
| 16 | Week 3, Virtual 1 |
| 17 to 19 | Week 3, Live 2 |
| 20 | Week 4, Live 1 |
| 21 and 22 | Week 4, Live 2 |
Picture the whole track as the kitchen from Start Here: you have washed (Week 2), cooked (Week 3), plated (Week 4) and tasted (Week 5). The 22 questions are the recipe card you could now write yourself.
Part 2: A First Look at SQL
Definition
SQL (Structured Query Language, often said "sequel") is the language for asking questions of a database: the system where a company's real records live, often millions of rows across many tables. You do not see the rows. You write a question, called a query, and get back only the answer.
Real-Life Scenario
Excel is like having every receipt spread out on your desk: you can see and touch each one. SQL is like phoning the records office: you cannot see the filing cabinets, but you can ask "total of every Port Harcourt corporate receipt, please", and they read you the answer in seconds, even if there are ten million receipts.
SUMIFS, Written as SQL
In Week 3 you learnt SUMIFS. Written for this question it is =SUMIFS(M2:M1051,J2:J1051,"Port Harcourt",K2:K1051,"Corporate"), and it gives N41,877,000. Here is the same question in SQL, run on a database table called sales that holds the same 1,050 sales:
The same N41,877,000. Read it almost as English: SELECT the sum of quantity times price, FROM the sales table, WHERE the branch is Port Harcourt AND the customer is corporate. WHERE does the job of SUMIFS' criteria pairs.
A Whole PivotTable in Five Lines
GROUP BY does what dragging Branch into Rows does; ORDER BY ... DESC is Sort Largest to Smallest. 10 SUMIFS, or one PivotTable, or one query: the same answer. The difference is scale. A query like this runs just as happily on fifty million sales, which no sheet can hold.
Everything You Learnt, in SQL Words
| In Excel you used | In SQL it is |
|---|---|
| A filter | WHERE |
| SUMIFS, COUNTIFS, AVERAGEIFS | SUM, COUNT, AVG with WHERE |
| A PivotTable's Rows | GROUP BY |
| Sort Largest to Smallest | ORDER BY ... DESC |
| XLOOKUP into a second table | JOIN |
| Remove Duplicates | DISTINCT |
| A calculated column | An expression in SELECT, such as Quantity * Unit_Price |
| TRIM, UPPER, SUBSTITUTE | TRIM, UPPER, REPLACE |
Where Excel Ends and SQL Begins
| Use Excel when | Use SQL when |
|---|---|
| The data fits on a sheet (comfortably under a few hundred thousand rows) | The data is millions of rows, or lives in a company system |
| You need to see and touch the data | You need the same answer again next week, exactly |
| The work is a one-off | Several tables must be joined |
| You are sharing a file with a person | An app or dashboard needs to read the data directly |
Nothing you learnt here is wasted. Cleaning, conditions, grouping, lookups and clear presentation are the same ideas in SQL and in Power BI. You have learnt to think like an analyst; the next tracks give you bigger tools for the same thinking.
Checkpoint
Q1. Which SQL word does the job of a filter?
WHERE.
Q2. Which SQL words make a PivotTable by branch, sorted?
GROUP BY Branch, then ORDER BY ... DESC.
Q3. Give one reason to move from Excel to SQL.
Data too large for a sheet, data that lives in a database, joining several tables, or needing the same answer repeatedly.
Tick Before You Move On
- ☐ I answered all 22 questions and listed the ones I missed
- ☐ I know which lesson to revise for each topic I missed
- ☐ I can read a SELECT ... FROM ... WHERE query and say what it returns
- ☐ I can match WHERE, GROUP BY, ORDER BY and JOIN to the Excel tools I used
Quick Recap
Twenty-two topics, one workflow: raw data, clean, analyse, visualise, decide. SQL asks questions of databases: WHERE filters, GROUP BY groups, ORDER BY sorts, JOIN looks up. Excel is for data you can see; SQL is for data at scale and answers you must repeat.
Next, Week 5, Virtual 2: the final check of your pod's Verdant Foods workbook, tab by tab, and submission.