Excel glossary

A reference with a $ sign before both the column and the row, such as $E$1, that does not change when you copy or drag the formula. Use it for fixed cells like a tax rate: =B2*$E$1 keeps multiplying by E1 on every row. While editing the formula, press F4 with the cursor on the reference to add the $ signs without typing them.

Each piece of information you give a function, inside the parentheses and in a set order. =IF(C2>=50,"Pass","Fail") has three: the condition, the value if it is true and the value if it is false. As you type the function, Excel shows a tooltip with its arguments; the ones in square brackets, such as [if_not_found], are optional.

The character that separates a function's arguments. In the US and UK it is the comma (=IF(A2>0,"Yes","No")); in Spain and much of Europe and Latin America it is the semicolon, because the comma is the decimal separator there. It comes from the Windows regional settings, not from Excel's language. If a formula from a colleague abroad is rejected, swap ; for ,.

The AutoSum (Σ) button in Home › Editing and on the Formulas tab, which types a =SUM() for you with the range it thinks you want (shortcut Alt+=). Its arrow also offers average, count, max and min. Before pressing Enter, check the range it suggests: it stops at the first blank cell.

Each box on the sheet, where a column and a row meet. It is named by its column letter and row number: B3 is column B, row 3. The address of the active cell shows in the Name Box, to the left of the formula bar, and its real content (the value or the formula) shows in the formula bar.

A visual representation of a range or table, inserted from the Charts group on the Insert tab and adjusted on Chart Design. Pick the type to fit the question: columns to compare categories, lines to show change over time, pie only for a few parts of a whole. If it is based on an Excel Table, it picks up new rows automatically.

Several people working in the same workbook at once, seeing in real time which cells each one is editing in their own color. The file must be stored in OneDrive or SharePoint and shared with the Share button; with AutoSave on, changes save by themselves. If someone breaks something, Version History lets you go back to an earlier version.

Rules that change a cell's color depending on its value and update when the value changes: highlight negative amounts, overdue invoices or duplicates. You find them in Home › Conditional Formatting, with quick rules, Data Bars, Color Scales, Icon Sets and New Rule to use a formula. Use it sparingly: if everything is highlighted, nothing stands out.

Microsoft's AI assistant built into Excel, available when your Microsoft 365 subscription includes it and the file is in OneDrive or SharePoint with AutoSave on. From the Copilot button on the Home tab it can suggest formulas, highlight data, create charts or PivotTables and summarize trends. It works best with data in an Excel Table, and you should check what it suggests just as you would a formula from ChatGPT.

A plain text file with the data separated by a delimiter, widely used to export from banks, online stores and business software. It stores no formulas, formatting or multiple sheets. Files from countries that use a decimal comma often use semicolons instead of commas, and opening one with a double-click can garble dates and drop leading zeros; importing it with Data › Get Data (Power Query) lets you choose the delimiter and each column's type.

The Data › Data Validation tool that limits what can be typed in a cell: only numbers between 1 and 100, only dates or, most usefully, a drop-down list (Allow: List). It can show a message when the cell is selected and an error alert when someone types something invalid. Watch out: pasting over the cell bypasses the validation.

A name you give a cell, range or constant so you can use it in formulas: name cell E1 TaxRate and you can write =B2*TaxRate, which reads better and behaves like an absolute reference. Create one by typing it in the Name Box or with Formulas › Define Name, and review them all in Formulas › Name Manager.

The behavior in Excel for Microsoft 365 and Excel 2021 or later where a single formula returns several results that fill the neighboring cells. =UNIQUE(Sales[Region]) typed in one cell lists every region once, one per row. Functions such as FILTER, SORT, UNIQUE and SEQUENCE are built for it.

A range turned into a table object with Insert › Table (Ctrl+T) or Home › Format as Table. It is more than a look: the table grows when you add rows, comes with filters, copies formulas down the whole column and has its own name (Table Design › Table Name). PivotTables, charts and formulas based on it pick up new data without you extending ranges by hand.

The small square in the bottom-right corner of the selected cell or range. Drag it to copy a formula while adjusting its references, or to continue a series (January, February…; 1, 2, 3…). Double-click it to fill down as far as the data in the next column goes, without dragging a thousand rows.

The arrows in the header row that hide the rows that don't meet a criterion, without deleting them. Turn them on with Data › Filter or Ctrl+Shift+L; Excel Tables have them built in. Careful: SUM still adds the rows hidden by the filter; to total only what is visible, use SUBTOTAL or AGGREGATE, or the table's Total Row.

The Data › Flash Fill feature (Ctrl+E) that works out a pattern from one or two examples you type by hand: pull the first name out of “Ana Lopez”, join first and last names or extract the domain from an email address. It saves a lot of time, but the result is fixed values, not formulas: if the source data changes, it does not update.

Anything you type in a cell starting with = so that Excel calculates it: =B2*C2, =SUM(D2:D20). The cell shows the result and the formula bar shows the formula. Change any value it depends on and the result recalculates by itself, which is why you never type in a total Excel can work out.

The codes Excel shows when it cannot calculate: #N/A (a lookup can't find the value), #VALUE! (the wrong type of data, such as text where a number belongs), #REF! (the formula points to cells that were deleted), #DIV/0! (division by zero or by an empty cell) and #NAME? (Excel doesn't recognize a function or range name). Each one tells you where to look; they're not there to be covered up.

The View › Freeze Panes option that keeps the top rows and left columns in place while you scroll, so you never lose the headers. Freeze Top Row locks only row 1; Freeze Panes locks everything above and to the left of the active cell (select B3 to lock rows 1 and 2 and column A).

A ready-made, named calculation you use inside a formula: SUM, AVERAGE, IF, XLOOKUP. Function names are translated in other language versions of Excel (in Spanish, SUM is SUMA), so typing or pasting a name from another language shows #NAME?. You can browse them in Formulas › Insert Function.

The Data › What-If Analysis › Goal Seek tool that works backwards: you tell it the result you want in a formula cell and which cell it may change, and Excel tries values until it hits the target. For example, how many units you need to sell for profit to reach $10,000. The cell being changed must feed into the formula, directly or indirectly.

The function that chooses between two results depending on a condition: =IF(C2>=1000,"Target met","Pending"). Text goes in quotation marks and numbers don't. For three or more cases you can nest several IF functions or use IFS (Excel 2019 or later), which is far easier to read.

Returns whatever you specify if the formula produces an error: =IFERROR(B2/C2,0) shows 0 instead of #DIV/0!. Only use it when you know which error to expect, because it hides all of them, including the ones warning you about a real mistake. For lookups, IFNA catches only #N/A and lets the others show.

The pair of functions that performs flexible lookups in any version of Excel: MATCH finds the position of the value and INDEX returns whatever sits in that position in another column. =INDEX(C2:C100,MATCH(A2,B2:B100,0)) does the same job as XLOOKUP; the final 0 asks for an exact match.

A recorded series of actions that Excel replays with one click or shortcut, such as applying the same formatting to a report every week. You record one with View › Macros › Record Macro, and the workbook must be saved as .xlsm. What a macro does cannot be undone with Ctrl+Z, so test it on a copy first, and never enable macros in files you weren't expecting.

A reference that locks only the column ($A1) or only the row (A$1). It is essential in two-way tables and in formula-based conditional formatting: =$D2<TODAY() always looks at column D but moves down the rows. Each press of F4 cycles through A1, $A$1, A$1 and $A1.

How a value is displayed, without changing the value stored: 0.25 can show as 25%, a date is really a serial number underneath, and 1250 can show as $1,250.00. You change it in Home › Number or with Ctrl+1 (Format Cells › Number). To keep leading zeros, as in a ZIP code, use the Text format or a custom format such as 00000.

The Home › Paste › Paste Special dialog that lets you choose which part of what you copied comes in: Values only (the result, without the formula), Formats only, Transpose to turn rows into columns, or an Operation that adds, subtracts, multiplies or divides what was already there. Pasting values is the safe way to send results without formulas breaking in another file.

An interactive summary that groups and totals thousands of rows without writing formulas: sales by region and month, expenses by category. You create one with Insert › PivotTable and build it by dragging fields into Rows, Columns, Values and Filters in the PivotTable Fields pane. It does not update by itself: after the data changes, use PivotTable Analyze › Refresh or Data › Refresh All.

Excel's engine for importing and cleaning data (CSV files, other workbooks, folders, databases, the web) from Data › Get Data. Every cleaning step you take in the Power Query Editor (remove columns, change types, filter, merge) is recorded under Applied Steps. When new data arrives, Data › Refresh All replays every step by itself, with no macros or code.

The range Excel prints when you don't want the whole sheet. Select it and use Page Layout › Print Area › Set Print Area. Combine it with Print Titles, to repeat the header row on every page, and with fitting all columns to one page wide so nothing gets cut off.

In Power Query, the saved combination of a data source plus transformation steps that produces a result table. A workbook's queries are listed in Data › Queries & Connections; from that pane you can edit them, refresh them or combine them with others (for example, appending each month's sales into one table).

A group of adjacent cells written as the first and last cell separated by a colon: B2:B10 is nine cells in one column and A1:D5 is a block four columns wide and five rows tall. Almost every function works on ranges, as in =SUM(B2:B10).

The normal way to point to a cell in a formula, with no dollar signs: B2. When you copy or drag the formula, the reference shifts by the same amount: if C2 holds =A2*B2 and you drag it down, C3 becomes =A3*B3. It is what you want almost every time, except when every row must point to the same cell.

A panel of buttons that filters a PivotTable or an Excel Table in one click and shows at a glance which filter is on. Insert one from Insert › Slicer or from PivotTable Analyze; a single slicer can drive several PivotTables at once. For dates there is the timeline, which filters by months, quarters or years as you drag a bar.

A tiny chart that lives inside a single cell and shows the trend of one row of data, such as each product's sales month by month. You create one in Insert › Sparklines, with three types: Line, Column and Win/Loss. It works well in dashboards and reports with no room for a full chart per row.

When a dynamic array formula returns several results, they “spill” into the cells below or to the right, which Excel outlines in blue (the spill range). You can refer to that whole range by adding # after its first cell: =SUM(F2#). If any of those cells already contains something, the formula shows #SPILL! until you clear it.

The way to point to an Excel Table by its names instead of its cells: =SUM(Sales[Amount]) adds up the whole Amount column of the Sales table, whether it has 10 rows or 10,000. Inside the table, [@Amount] means “the Amount on this same row”. They read better than D2:D500 and never fall short when the table grows.

Adds up only the rows that meet one or more conditions: =SUMIFS(Sales[Amount],Sales[Region],"North",Sales[Month],"January"). The range to add goes first, followed by pairs of range and criteria, the opposite of SUMIF, where it goes last. Its sibling COUNTIFS counts the rows instead of adding them.

The programming language Excel macros are stored in. When you record a macro, Excel writes the VBA code for you, and you can view and edit it with Alt+F11 or Developer › Visual Basic (turn on the Developer tab in File › Options › Customize Ribbon). AI tools write VBA quite well, but always test the code on a copy of the workbook.

The classic lookup function, available in every version: =VLOOKUP(A2,Customers,3,FALSE) looks up A2 in the first column of the table and returns the item from the third column. Its traps: without FALSE as the fourth argument it can return the wrong item without warning, it only searches the first column, and the column number doesn't adjust when you insert or delete columns.

The whole Excel file, usually with the .xlsx extension, which can hold many sheets. If the workbook contains macros it must be saved as an Excel Macro-Enabled Workbook (.xlsm); save it as .xlsx and the macros are lost.

Each grid inside a workbook, with its tab along the bottom (Sheet1, January, Customers). Add one with the + button next to the tabs and move between them with Ctrl+Page Down and Ctrl+Page Up. To use a cell from another sheet in a formula, write the sheet name and an exclamation mark: =January!B5.

The modern lookup function: it finds a value in one column and returns the matching item from another. =XLOOKUP(A2,Customers[ID],Customers[Name],"Not found") looks up the ID in A2 and returns the name. It finds exact matches by default, can look to the left and doesn't break when you insert columns. Available in Microsoft 365 and Excel 2021 or later; in older versions use INDEX and MATCH.