17.1 Basics and References

Q1. What is the difference between a workbook and a worksheet?

A workbook is the Excel file (.xlsx, .xlsm, .xlsb). A worksheet is a single grid (tab) inside the workbook. One workbook can contain many worksheets.

Q2. Explain relative, absolute and mixed references.

A relative reference (A2) changes when the formula is copied. An absolute reference ($A$2) always points to the same cell. A mixed reference fixes only the column ($A2) or only the row (A$2). F4 cycles through the four forms. For example, to multiply each amount by a GST rate stored in F1, I use =B2*$F$1 so F1 stays fixed when I copy the formula down.

Q3. When would you use a mixed reference?

When a formula is copied both across and down and must lock one direction only – for example a multiplication grid or a two-way SUMIFS matrix, =SUMIFS($G:$G,$C:$C,$A2,$E:$E,B$1), where city names are in column A and months in row 1.

Q4. What are named ranges and why use them?

A name such as GST_Rate refers to a cell or range. Formulas become readable (=B2*GST_Rate), names work across sheets, and they are useful in data validation lists and charts. They are managed in Formulas › Defined Names › Name Manager.

Q5. What is the difference between COUNT, COUNTA, COUNTBLANK and COUNTIF?

COUNT counts numeric cells; COUNTA counts non-empty cells of any type; COUNTBLANK counts empty cells; COUNTIF counts cells meeting one condition, for example =COUNTIF(I:I,"Delivered"). COUNTIFS handles multiple conditions.

Q6. How do you store Indian-style numbers like ₹12,50,000?

Either set Windows regional settings to English (India) and use ₹#,##,##0, or use a conditional custom number format such as [>=10000000]₹##\,##\,##\,##0;[>=100000]₹##\,##\,##0;₹##,##0. The cell value stays a number, so calculations are unaffected.

Q7. Why does a number sometimes behave like text, and how do you fix it?

It was imported or typed as text (left-aligned, green triangle, SUM ignores it). Fix it with the error menu › Convert to Number, Text to Columns › Finish, =VALUE(), multiplying by 1, or Paste Special › Multiply by 1. In Power Query, change the data type.

Q8. What is the difference between a formula and a function?

A formula is any expression that starts with = (for example =B2*C2). A function is a predefined operation used inside formulas (for example SUM, XLOOKUP). Every function is used in a formula; not every formula uses a function.