MS Excel Functions and Pivot Tables
In the syllabus of AR-IITD
Saved in this browser only. See all revisions due
Key points
- COUNT counts numbers, COUNTA non-empty cells, COUNTIF with a condition
- COUNTIF is not case-sensitive
- SUMIF has the sum range last; SUMIFS has it first
- VLOOKUP searches only the first column and cannot look left; FALSE gives exact match
- XLOOKUP is exact by default and can look left
- Pivot table default is Sum for numeric fields, Count if text or blanks are present
- A pivot table must be refreshed after the source data changes
Common functions
| Function | Syntax | Purpose |
|---|---|---|
| SUM | =SUM(A1:A10) | Adds numbers |
| AVERAGE | =AVERAGE(A1:A10) | Arithmetic mean |
| COUNT | =COUNT(A1:A10) | Counts cells containing numbers only |
| COUNTA | =COUNTA(A1:A10) | Counts all non-empty cells |
| COUNTIF | =COUNTIF(range, criteria) | Counts cells meeting one condition |
| SUMIF | =SUMIF(range, criteria, sum_range) | Adds values meeting one condition |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1) | Several conditions |
| IF | =IF(test, value_if_true, value_if_false) | Conditional result |
| AND / OR | =AND(cond1, cond2) | True if all conditions / any condition holds |
| IFERROR | =IFERROR(value, value_if_error) | Replaces an error with your own text |
Key point
In SUMIF the sum range comes last, but in SUMIFS it comes first. COUNTIF is not case-sensitive, so Approved and approved are counted as the same value.
Lookup functions
VLOOKUP has the form =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). It searches only the first column of the table and returns a value from a column to its right. Passing FALSE or 0 as the fourth argument gives an exact match. Leaving the argument out gives an approximate match.
XLOOKUP, available from Microsoft 365 and Excel 2021, performs an exact match by default, can look to the left, and has a built-in argument to return your own message when nothing is found.
Example
Departments sit in A2:A6 as Civil, Mech, Civil, CSE, Civil and amounts in B2:B6 as 5000, 3000, 7000, 4000, 2000. Then =SUMIF(A2:A6, "Civil", B2:B6) gives 5000 + 7000 + 2000 = 14000. Shortcut: total 21000 minus the non-Civil 7000.
Pivot tables
A pivot table summarises a large table, such as department-wise expenditure. It has four areas: Filters, Columns, Rows and Values.
- A purely numeric field added to Values is summarised by Sum by default.
- If the field contains text or blank cells, the default becomes Count.
- A pivot table does not update by itself. After the source data changes it must be refreshed, because it reads from a stored copy of the data.
Data tools used in office work
| Feature | Tab |
|---|---|
| Sort and filter, remove duplicates, text to columns, data validation, goal seek | Data |
| Freeze panes | View |
| Conditional formatting | Home |
| Protect sheet | Review |
Exam tip
Output-prediction questions are fully solvable. Write each condition as TRUE or FALSE on rough paper before you look at the options, and attempt them rather than skipping.
Practice questions
Answer all, then check. Explanations appear after checking.
Finished this topic? Tick it off.
Saved in this browser only. See all revisions due