Formulas & functions
How a function works, with worked examples you can paste.
- Remove Spaces at the End of Text in Excel with TRIMTo remove ordinary spaces at the end of text in Excel, put =TRIM(A2) in a helper column if the text is in…
- How to Show a Unique Count in Excel with a FormulaTo count distinct nonblank values in Excel for Microsoft 365, put your data in A2:A100 and enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) in an empty cell.…
- How to Count Unique Values in a Row in Excel EasilyTo count distinct, nonblank values across a row in Microsoft 365, use =IFERROR(COLUMNS(UNIQUE(FILTER(B2:K2,B2:K2<>""))),0). This assumes the values are in B2:K2; it filters…
- How to Count Unique Values in an Excel PivotTableTo count distinct values in an Excel PivotTable, create the PivotTable with Add this data to the Data Model selected, then place…
- Count Unique Records in Excel: Formulas and PivotTablesTo count distinct values in a column in Microsoft Excel for Microsoft 365, put this formula in an empty cell: =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). It…
- Excel SUBTOTAL: Count Unique Values in Filtered DataExcel’s SUBTOTAL function cannot count unique values by itself. In Microsoft 365, combine UNIQUE and FILTER with SUBTOTAL to count distinct, nonblank…
- Count Each Unique Value in Excel with a FormulaTo count how often each value appears, put your source values in A2:A11, then enter =SORT(UNIQUE(FILTER(A2:A11,A2:A11<>""))) in D2 and =COUNTIF(A2:A11,D2#) in E2.…
- How to Calculate a Unique Count in Excel with FormulasTo count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range. The formula filters out…
- Count Unique Values Across Excel Sheets with One FormulaTo count distinct nonblank values across worksheets in Excel for Microsoft 365, stack the ranges and count the unique results. With entries…
- Count Unique Strings in Excel: Formulas and ExamplesTo count distinct, nonblank text strings in Excel for Microsoft 365, put your data in A2:A100 and use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(ISTEXT(A2:A100))*(A2:A100<>"")))),0). It returns the…
- How to Compare Two Columns in Excel with VLOOKUP FormulaTo compare two columns in Excel, check whether each value in the first list appears in the second with an exact-match VLOOKUP.…
- How to Compare Two Columns in Excel: Formulas and TipsTo compare values on the same row, put the lists in columns A and B, then enter a formula in C2 and…
- How to Compare Two Columns in Excel With a FormulaTo compare two Excel columns row by row, enter =IF(A2=B2,"Match","Different") in C2, then fill the formula down. This labels each pair of…
- Excel Average Ignoring Zeros: Formula and ExamplesTo calculate an average in Excel while ignoring zeros, use AVERAGEIF with the criterion “0″. If your values are in A2:A10, enter…
- Percentage Calculator in Excel: Formulas and ExamplesTo calculate a percentage in Excel, divide the part by the total, then format the result as a percentage. For example, with…
- Excel Formula for General Average and When to Use ItIf your values are in A2:A10, the Excel formula for their general arithmetic average is =AVERAGE(A2:A10). It adds the numeric values in…
- How to Calculate Quality Percentage in Excel With FormulasTo calculate quality percentage in Excel, divide the number of checks that passed by the total number of checks, then format the…
- Calculate Percentage in Excel: Formulas and Table TipsTo calculate a percentage in Excel, divide the part by the total, then format the result as a percentage. For example, if…
- Calculate Percentage in Excel: Formulas for a ColumnTo calculate a percentage in an Excel column, divide each part by its whole, then format the results as percentages. For example,…
- Insert a Row in Excel With a Formula: Real OptionsA formula cannot insert a physical worksheet row or move existing cells. To show a blank row inside a formula-generated copy of…
- Hide Columns in Excel Based on a Formula AutomaticallyExcel formulas cannot hide columns by themselves. To hide or show columns when a formula’s result changes, put a formula in a…
- Excel F4: How to Lock Cell References in FormulasTo lock a cell reference in an Excel formula, edit the formula, place the cursor in or select the reference, and press…
- Split Cells in Excel: Text to Columns and FormulasTo split text stored in one Excel cell into separate columns, use Data > Text to Columns on a normal range, or…
- Combine Cells in Excel That Have Words: Formulas and StepsTo combine words from two Excel cells, enter a formula such as =A2&" "&B2 in an empty cell; it joins the contents…
- Remove Duplicates in Excel: Sort Data the Right WayTo remove duplicates in Excel and keep the right record, sort your data first if the order determines which copy should stay.…
- Clear Excel Sort: Remove Sorting or Restore Original OrderTo clear a sort in Excel for Microsoft 365 on Windows, select a cell in the sorted data and choose Data >…
- Lower Quartile Formula in Excel: Find Q1 CorrectlyIn Excel, calculate the lower quartile (Q1) with =QUARTILE.INC(A2:A9,1), replacing A2:A9 with your data range. This returns the 25th percentile using Excel’s…
- Excel Formula for the Lower of Two Values in Two CellsTo return the lower of two values in Excel, use =MIN(A2,B2). If the values are in cells A2 and B2, the formula…
- Upper Quartile Formula in Excel: How to Find Q3 CorrectlyIn Excel, use =QUARTILE.INC(A2:A20,3) to find the upper quartile, or third quartile (Q3), of the numbers in A2:A20. The function returns the…
- Excel LOWER Formula: Convert Text to LowercaseUse Excel’s LOWER function to convert text to lowercase: if the original text is in A2, enter =LOWER(A2) in another cell. The…