20. Glossary

What: An absolute reference has $ signs before the column and the row, so it does not move when the formula is copied.

Why: You use it when one value, such as a tax rate, must stay fixed while you copy a formula down.

How: Type the reference and press F4 once to add both $ signs.

What: The active cell is the one selected cell with a thick green border. Its name shows in the Name Box.

Why: Excel types, pastes and runs shortcuts on the active cell, so you must know where it is.

How: Click a cell or move to it with the arrow keys. Check the Name Box on the left of the formula bar.

What: Advanced Filter reads its rules from a small criteria range that you type on the sheet.

Why: It handles AND and OR rules that a normal filter cannot do in one step, and it can copy the result to another place.

How: Type the column headings and rules above the data, then use Data, Advanced, and choose the list range and the criteria range.

What: To aggregate means to turn many values into one value, for example a sum, a count or an average.

Why: Managers rarely read every row. They want one number such as a total or an average.

How: Use SUM, COUNT, AVERAGE, a Total Row or a PivotTable.

What: AGGREGATE is one function that can do 19 different summaries and can skip errors or hidden rows.

Why: It can ignore error values and hidden rows, which SUM cannot do.

How: Give the function number first (9 means SUM), then the option (6 means ignore errors), then the range.

What: AND returns TRUE only when every test inside it is TRUE.

Why: It lets you check two or more rules at the same time before you decide something.

How: Write each test inside AND, separated by commas.

What: AOV is net sales divided by the number of orders.

Why: It tells a shop how much a customer spends in one order on average.

How: Divide the sum of Amount by the count of orders.

What: Append stacks the rows of two or more tables with the same columns, one below the other.

Why: Monthly or branch files often have the same columns, and you need them in one long list.

How: In Power Query, choose Home, Append Queries, and pick the tables.

What: Applied Steps is the list of changes Power Query has recorded for a query.

Why: They are your cleaning recipe. Power Query repeats them every time you refresh.

How: Look at the right side of the Power Query Editor. Click a step to see the data at that point.

What: An argument is one input you give to a function, written inside the brackets.

Why: Every function needs the right inputs in the right order, or the answer is wrong.

How: Separate arguments with commas. Excel shows a small tooltip with the order.

What: An array is a set of values arranged in rows, columns or both.

Why: Many modern functions work on a whole list at once instead of one cell.

How: Select a range, or write values in braces like {1,2,3}.

What: The fill handle is the small square at the bottom-right of a selected cell. Dragging it copies or continues a pattern.

Why: It saves typing when you need dates, numbers or a formula in many rows.

How: Select the cell, then drag the square down, or double-click it to fill to the end of the data.

What: AVERAGEIF finds the average of the values that match one rule. AVERAGEIFS allows many rules.

Why: It gives the average for one group only, for example one city.

How: Give the column to test, the rule, and then the column to average.

What: Break-even is the point where total income equals total cost, so profit is zero.

Why: It shows the sales you need before you stop losing money.

How: Divide fixed cost by the profit made on each unit.

What: A calculated field is a PivotTable field made from a formula on other fields.

Why: It adds a new measure to a PivotTable without changing the source data.

How: PivotTable Analyze, Fields, Items and Sets, Calculated Field. Write a formula such as =Amount/Qty.

What: A cell is the box where one row and one column meet. Its address is the column letter plus the row number.

Why: Every value and every formula lives in a cell.

How: Click any box in the grid. The Name Box shows its address.

What: A chart is a picture of data, such as columns, lines or a pie.

Why: People understand a picture of the numbers faster than a table.

How: Select the data and press Alt+F1, or use Insert, Charts.

What: A circular reference is a formula that refers to its own cell, directly or through other cells.

Why: It is a mistake that makes Excel show a warning or a zero.

How: Use Formulas, Error Checking, Circular References to find the cell, then change the formula.

What: Co-authoring means several people edit the same online workbook at the same time.

Why: A team can update one file together instead of mailing copies.

How: Save the file to OneDrive or SharePoint and click Share.

What: A column is a vertical line of cells, named with letters A, B, C and so on.

Why: Each column should hold one kind of information, such as City or Amount.

How: Click the letter at the top to select the whole column.

What: A comment is a threaded chat with replies. A note is a simple yellow sticky label.

Why: They let you explain a value without changing the data.

How: Right-click the cell and choose New Comment or New Note.

What: To concatenate means to join pieces of text into one text.

Why: You often need one label from two columns, such as Customer and City.

How: Use the & sign, CONCAT or TEXTJOIN.

What: Conditional formatting changes the colour of a cell automatically when a rule is true.

Why: Colours make important rows stand out, such as late or cancelled orders.

How: Home, Conditional Formatting, then choose a rule such as Text that contains Cancelled.

What: Copilot is Microsoft's AI assistant inside Excel.

Why: It can explain data and suggest formulas, but you must check its answers.

How: Open Copilot on the Home tab and ask a clear question about a Table.

What: COUNTIF counts cells that match one rule. COUNTIFS counts rows that match several rules.

Why: It answers questions like how many orders came from Pune.

How: Give the column to check, then the rule in quote marks.

What: Criteria are the conditions used to filter, count or add, such as "Pune" or ">1000".

Why: Most summary functions need a rule that tells them which rows to use.

How: Write text and signs in quote marks, for example ">1000".

What: A currency format shows a number with a symbol such as ₹, $ or €, without changing the value.

Why: Money must show its currency so that nobody mixes rupees, dollars and euros.

How: Press Ctrl+1, choose Currency, and pick the symbol.

What: A dashboard is a one-page summary of KPIs, charts and slicers.

Why: A manager can see the health of the business on one screen.

How: Build PivotTables on a calculation sheet, then place cards and charts on a clean sheet.

What: The Data Model is Excel's memory store of tables joined by relationships.

Why: It lets you report on several related tables without VLOOKUP.

How: When you create a PivotTable, tick Add this data to the Data Model, or use Power Pivot.

What: A Data Table is a grid that recalculates a formula for a list of input values.

Why: It shows many answers at once when one input changes.

How: Data, What-If Analysis, Data Table. Point it at the input cell.

What: Data validation is a rule that limits what can be typed in a cell, such as a list or a number range.

Why: It stops wrong entries before they enter the sheet.

How: Data, Data Validation. Choose List and type Delivered,Returned,Cancelled.

What: A date serial number is the number of days since 1 January 1900. Formatting makes it look like a date.

Why: Excel can subtract dates only because it stores them as numbers.

How: Change a date cell to General format to see its number.

What: A delimiter is a character that separates parts of text, such as a comma, a dash or a space.

Why: You need it to split one text column into several columns.

How: Data, Text to Columns, Delimited, then pick the character. Or use TEXTSPLIT.

What: A dimension table has one row per item, with details about that item.

Why: It holds the descriptions you slice by, such as product names and categories.

How: Keep one row per product, customer or date, with a unique key column.

What: A duplicate is a value or a whole row that appears more than once.

Why: Duplicate rows double-count sales and make reports wrong.

How: Use Data, Remove Duplicates, or count with COUNTIF.

What: A dynamic array formula returns several values that spill into the cells next to it.

Why: One formula can return a whole list, and it updates when the data changes.

How: Type a function such as UNIQUE or FILTER in one cell and press Enter.

What: Encryption locks the whole file with a password so it cannot be opened without it.

Why: It keeps salary or customer files safe if they are mailed to the wrong person.

How: File, Info, Protect Workbook, Encrypt with Password.

What: #N/A means not found, #DIV/0! means divide by zero, #VALUE! means the wrong type, and #REF! means a deleted cell.

Why: Each error tells you a different thing is wrong, so you can fix it fast.

How: Read the error name, then use IFERROR only after you understand the cause.

What: A fact table has one row per event, such as one order, with keys and amounts.

Why: It holds the numbers you add up, such as sales.

How: Keep only IDs, dates and numbers. Put descriptions in dimension tables.

What: A filter shows only the rows that meet a condition. The others are hidden, not deleted.

Why: It hides the rows you do not need right now.

How: Press Ctrl+Shift+L and use the arrow on a heading.

What: FILTER returns only the rows that match a rule.

Why: It gives a live list that updates when the data changes.

How: Give the data, then the test.

What: Flash Fill sees the pattern in your example and fills the rest of the column.

Why: It splits or joins text from one example without a formula.

How: Type one example and press Ctrl+E.

What: A formula is a calculation that starts with an = sign.

Why: Formulas do the maths for you and update when the numbers change.

How: Type =, then numbers, cell addresses or functions, and press Enter.

What: Freeze Panes locks rows at the top or columns at the left on screen.

Why: The headings stay visible while you scroll through many rows.

How: View, Freeze Panes, Freeze Top Row.

What: A function is a ready-made calculation with a name, such as SUM or XLOOKUP.

Why: Functions save you from writing long maths by hand.

How: Type = and the name, then the arguments in brackets.

What: Goal Seek changes one input cell until a formula shows a target value.

Why: It works backwards from the answer you want to the input you need.

How: Data, What-If Analysis, Goal Seek. Set the formula cell, the target and the input cell.

What: Granularity is the level of detail of one row, for example one order or one month.

Why: Mixing daily and monthly rows in one table gives wrong totals.

How: Decide what one row means before you build a report.

What: A helper column is an extra column used only for a middle step of a calculation.

Why: It breaks a hard formula into easy steps that you can check.

How: Add a column next to the data, write the step, and hide it later if needed.

What: HLOOKUP looks across the first row and returns a value from a row below it.

Why: Some old reports keep headings down the side and values across.

How: Give the value, the table, the row number and FALSE.

What: IF checks a test and returns one answer when it is TRUE and another when it is FALSE.

Why: It makes a decision for each row, such as Big or Small order.

How: Write the test, then the TRUE answer, then the FALSE answer.

What: IFERROR returns your own value when a formula gives any error. IFNA does it only for #N/A.

Why: It shows a clean message instead of an ugly error.

How: Wrap the formula: IFERROR(formula, "message").

What: The Immediate window is a pane in the VBA Editor where you type a command and see the result.

Why: You can test one line of VBA without running the whole macro.

How: In the VBA Editor, press Ctrl+G, type ? Range("G2").Value and press Enter.

What: MATCH finds the position of a value. INDEX returns the value at that position from another column.

Why: It works in every Excel version and can look to the left.

How: Put MATCH inside INDEX.

What: Iteration means repeating a calculation or a block of code many times.

Why: Some tasks repeat the same step for every row.

How: In VBA, use a For or For Each loop.

What: A KPI (Key Performance Indicator) is one important number that shows performance, such as sales or return rate.

Why: A KPI tells you quickly whether the business is on track.

How: Pick a few numbers that match the goal and show them as cards.

What: LAMBDA creates a custom function from parameters and a formula.

Why: You can make your own function without VBA.

How: Write LAMBDA with names and a formula, then save it in Name Manager.

What: LET gives a name to a middle result inside one formula.

Why: It makes long formulas shorter and easier to read.

How: Write a name, its value, and then the final calculation.

What: A lookup finds a value in one column and returns a matching value from another column.

Why: You often need a value from another table, such as a customer's city.

How: Use XLOOKUP, or INDEX and MATCH.

What: A loop is VBA code that repeats, using For, For Each or Do.

Why: A loop lets one macro handle 10 rows or 10,000 rows.

How: Write For i = 2 To lastRow, the steps, and Next i.

What: A macro is a recorded or written set of steps that Excel can run again.

Why: It repeats a boring job with one click.

How: Developer, Record Macro. Do the steps, then Stop Recording. Save as .xlsm.

What: MAXIFS returns the largest value that matches rules. MINIFS returns the smallest.

Why: It finds the biggest or smallest value for one group.

How: Give the column to measure first, then pairs of column and rule.

What: A measure is a DAX formula in the Data Model, such as Total Sales = SUM(Orders[Amount]).

Why: It calculates totals that react to every filter and slicer.

How: In Power Pivot, type the measure in the calculation area under the table.

What: Merge joins two tables side by side on a matching column.

Why: It brings columns from another table, like a lookup, for every row.

How: Home, Merge Queries. Pick the matching column in both tables and the join kind.

What: A mixed reference fixes only the column ($A1) or only the row (A$1).

Why: It is useful in grids where only the row or only the column must stay fixed.

How: Press F4 two or three times to switch.

What: A module is a container for VBA code inside a workbook.

Why: You need a place to write macros that are not tied to one sheet.

How: In the VBA Editor, choose Insert, Module.

What: A named range is a name you give to a cell or a group of cells.

Why: Names make formulas easy to read, for example =SUM(Sales).

How: Select the cells and type a name in the Name Box, then press Enter.

What: A nested formula has one function inside another.

Why: Real questions often need two or three steps in one cell.

How: Build it from the inside out and check each part.

What: A number stored as text looks like a number but Excel treats it as words. It often has a green triangle.

Why: SUM ignores numbers that are stored as text, so totals come out too small.

How: Use the warning icon, Convert to Number, or VALUE.

What: An operator is a sign such as + - * / ^ & = > <.

Why: Operators tell Excel what maths to do.

How: Use brackets to control the order: Excel does * and / before + and -.

What: OR returns TRUE when any one of its tests is TRUE.

Why: It checks if at least one rule is true, such as Returned or Cancelled.

How: Write each test inside OR, separated by commas.

What: An outlier is a value far away from most other values.

Why: One very big or very small value can spoil an average.

How: Sort the column, or compare each value with the average.

What: Paste Special lets you choose what to paste: values, formats, formulas or transpose.

Why: You can paste only values or only formats when a normal paste would bring the wrong thing.

How: Copy, then press Ctrl+Alt+V and choose an option.

What: A percentage is a part out of 100. In Excel, 18% is stored as 0.18.

Why: Percent makes numbers of different sizes easy to compare.

How: Divide the part by the total and format the cell as %.

What: A PivotChart is a chart connected to a PivotTable.

Why: The chart changes when you filter the PivotTable.

How: Click inside the PivotTable, then PivotTable Analyze, PivotChart.

What: A PivotTable is an interactive summary that groups and totals data.

Why: It summarises thousands of rows in a few clicks.

How: Insert, PivotTable. Drag fields into Rows, Columns, Values and Filters.

What: Power Pivot is the Excel add-in for the Data Model and DAX measures.

Why: It handles big data and relationships beyond a normal sheet.

How: File, Options, Add-ins, COM Add-ins, tick Power Pivot.

What: Power Query is Excel's tool to import, clean and reshape data with recorded steps.

Why: It cleans the same messy export every month with one Refresh.

How: Data, Get Data, choose the source, clean it in the editor, then Close and Load.

What: Print titles are rows or columns repeated at the top or left of each printed page.

Why: Every printed page should show the column headings.

How: Page Layout, Print Titles, Rows to repeat at top: $1:$1.

What: A procedure is a named block of VBA code that starts with Sub and ends with End Sub.

Why: Each job in VBA is written as one named procedure.

How: Write Sub Name(), the steps, and End Sub.

What: Python in Excel lets you write Python code in a cell with =PY.

Why: Python adds pandas and charts inside the workbook.

How: Type =PY in a cell, write the code, and press Ctrl+Enter.

What: A range is a group of cells, written with a colon, such as the Amount column from row 2 to row 9.

Why: Most functions work on a group of cells, not just one.

How: Drag across the cells, or type the first and last address with a colon.

What: Recalculation means Excel works out all formulas again. F9 forces it.

Why: If a file is set to manual calculation, numbers may look out of date.

How: Formulas, Calculation Options, Automatic. Press F9 to recalc now.

What: Reconciliation means checking that counts and totals match between two sources.

Why: It proves that two lists agree before you send a report.

How: Compare totals, then find the rows that differ with a lookup.

What: A relationship links a column in one table to a matching column in another table.

Why: It connects tables so that one report can use them together.

How: In Diagram View, drag the key column from one table to the other.

What: A relative reference has no $ signs, so it moves when the formula is copied.

Why: It lets one formula work for every row when you copy it down.

How: Write the formula for the first row and drag it down.

What: A report is a sheet or document that presents results, often with tables and charts.

Why: A report answers a business question for a reader.

How: Decide the question, build the numbers, then add short notes.

What: ROUND rounds normally. ROUNDUP always goes up. ROUNDDOWN always goes down.

Why: Money and counts must have the right number of decimals.

How: Give the number and the number of decimals.

What: A row is a horizontal line of cells, numbered 1, 2, 3 and so on.

Why: Each row should be one record, such as one order.

How: Click the row number on the left to select the whole row.

What: Scenario Manager stores sets of input values and shows their results side by side.

Why: It saves best, normal and worst cases and compares them.

How: Data, What-If Analysis, Scenario Manager, Add.

What: A sheet is one grid tab inside a workbook.

Why: Separate sheets keep raw data, calculations and the dashboard apart.

How: Click the + at the bottom to add a sheet. Double-click the tab to rename it.

What: A slicer is a set of buttons that filters a PivotTable or a Table.

Why: Anyone can filter a report with buttons, without knowing filters.

How: Click the PivotTable, then PivotTable Analyze, Insert Slicer.

What: Solver is an add-in that changes several inputs to reach the best value within rules.

Why: It finds the best mix under limits, such as the cheapest delivery plan.

How: Turn it on in Add-ins, then Data, Solver. Set the objective, the variable cells and the limits.

What: Sorting arranges rows in ascending or descending order by a column.

Why: Sorted data shows the top and bottom items at once.

How: Data, Sort, choose the column and the order.

What: SORT returns a sorted copy of a range. SORTBY sorts by another column.

Why: They give a sorted copy that updates by itself.

How: Give the range, the column number and 1 or -1.

What: A sparkline is a tiny chart inside a single cell.

Why: It shows a trend inside one cell next to the numbers.

How: Insert, Sparklines, Line, and choose the data range.

What: Spill means a formula's results flow into the cells next to it. If something blocks them, you see #SPILL!.

Why: You must keep the cells below a dynamic formula empty.

How: Clear the cells in the way, or move the formula.

What: A star schema has one fact table in the middle and dimension tables around it.

Why: It is the cleanest way to arrange tables for reports.

How: Link each dimension table to the fact table by a key.

What: A structured reference refers to a Table column by name, such as Orders[Amount].

Why: Formulas read like words and grow with the table.

How: Click inside an Excel Table column while writing a formula.

What: SUMIF adds values that match one rule. SUMIFS adds values that match many rules.

Why: It adds only the rows that you care about.

How: SUMIF: test column, rule, sum column. SUMIFS: sum column first, then pairs.

What: SUMPRODUCT multiplies matching items in ranges and returns the sum.

Why: It multiplies two columns and adds the results in one step.

How: Give two ranges of the same size.

What: An Excel Table is a range with a name, filters, banded rows and structured references.

Why: Formulas, charts and pivots grow automatically when you add rows.

How: Click inside the data and press Ctrl+T.

What: TEXT turns a value into text with a format you choose.

Why: It formats a number or date inside a sentence.

How: Give the value and a format code in quote marks.

What: A timeline is a slicer made for dates: days, months, quarters or years.

Why: It filters a report by dates with a slider.

How: PivotTable Analyze, Insert Timeline, pick the date field.

What: Transpose switches rows to columns and columns to rows.

Why: It turns a layout around when rows should be columns.

How: Paste Special, Transpose, or the TRANSPOSE function.

What: TRIM removes extra spaces and leaves single spaces between words.

Why: Extra spaces make lookups fail even when names look the same.

How: Give it the text.

What: A UDF is a function written in VBA that you use in cells like a normal function.

Why: You can add your own business rule as a function.

How: Write a Function in a module, then type =FunctionName( in a cell.

What: UNIQUE returns each different value once.

Why: It gives a clean list of different values without duplicates.

How: Give the range.

What: Unpivot turns column headings (like Jan, Feb, Mar) into rows with one value column.

Why: Month-wise columns are hard to analyse. One long list is easier.

How: In Power Query, select the ID column, then Unpivot Other Columns.

What: A variable is a named storage place for a value in VBA.

Why: Variables store a value while a macro runs.

How: Declare it with Dim, for example Dim total As Double.

What: VBA (Visual Basic for Applications) is the programming language of Excel macros.

Why: It automates Excel beyond what recording can do.

How: Press Alt+F11 to open the VBA Editor.

What: VLOOKUP looks down the first column of a table and returns a value from a column to the right.

Why: Many old files still use it, so you must be able to read it.

How: Give the value, the table, the column number and FALSE for an exact match.

What: A volatile function recalculates every time anything changes, for example TODAY, NOW and OFFSET.

Why: Too many volatile functions slow down a big file.

How: Use them only where needed.

What: A wildcard is * for any number of characters or ? for one character.

Why: It matches text when you know only part of it.

How: Use it inside a text rule, such as "*tea*".

What: A workbook is one Excel file with one or more sheets.

Why: One file can hold all sheets of a project together.

How: Save it with Ctrl+S. Use .xlsx, or .xlsm for macros.

What: XLOOKUP finds a value in one column and returns the matching value from another column.

Why: It is the easiest modern lookup. It can look left and can show a message when nothing is found.

How: Give the value, the column to search, the column to return, and an optional message.

What: XMATCH returns the position of a value in a list.

Why: It tells you the position of an item, which other functions can use.

How: Give the value and the list.