PY Function in Excel: Run Python Inside Excel
PY (Python in Excel, 2023) runs Python code (pandas, matplotlib and more) in Microsoft's cloud and returns the result to a cell. You don't…
Read article →Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
CUBESETCOUNT Function in Excel: Number of Items in a Set
CUBESETCOUNT Function in Excel: Number of Items in a Set
CUBESETCOUNT counts the items in a set made with CUBESET. Cube functions read from a Power Pivot Data Model (connection "ThisWorkbookDataModel") or an Analysis…
Read article →PERCENTRANK Function in Excel: Percentile Rank (Older Name)
PERCENTRANK Function in Excel: Percentile Rank (Older Name)
PERCENTRANK returns the rank of a value as a percentage of the data. It is a compatibility function: Excel keeps it so workbooks from…
Read article →Why Does Excel Have Exactly 1,048,576 Rows? The Logic Behind Software Limits
Why Does Excel Have Exactly 1,048,576 Rows? The Logic Behind Software Limits
Why Excel stops at 1,048,576 rows and column XFD, why old Excel had 65,536 rows, why numbers lose digits after 15, why FAT32 files…
Read article →PERCENTRANK.INC Function in Excel: Where a Value Stands, as a %
PERCENTRANK.INC Function in Excel: Where a Value Stands, as a %
PERCENTRANK.INC tells you what share of the data is below a value — “this store beats 82% of stores”.
Read article →AVERAGEIF Function in Excel: Average With One Condition
AVERAGEIF Function in Excel: Average With One Condition
StatisticalLevel: IntermediateAvailable in: Excel 2007+AVERAGEIF averages values that meet one condition — often used to ignore.
Read article →VBA: Loop Through Every File in a Folder (and Combine Them)
VBA: Loop Through Every File in a Folder (and Combine Them)
Process every file in a folder with VBA: Dir loops, picking a folder with FileDialog, opening workbooks quietly, copying data into a master sheet…
Read article →QUARTILE.EXC Function in Excel: Quartiles Excluding the Ends
QUARTILE.EXC Function in Excel: Quartiles Excluding the Ends
QUARTILE.EXC matches the method many statistics textbooks and tools use; results differ slightly from QUARTILE.INC on small data.
Read article →MAKEARRAY Function in Excel: Generate a Grid From Row and Column Numbers
MAKEARRAY Function in Excel: Generate a Grid From Row and Column Numbers
MAKEARRAY builds an array of a given size, computing each cell from its row and column number.
Read article →BYCOL Function in Excel: Apply a Calculation to Each Column
BYCOL Function in Excel: Apply a Calculation to Each Column
LogicalLevel: ExpertAvailable in: Excel 365 / 2021+BYCOL is BYROW turned sideways: one result per column.Syntax=BYCOL(array, LAMBDA(col.
Read article →PERCENTRANK.EXC Function in Excel: Percentile Position, Excluding Ends
PERCENTRANK.EXC Function in Excel: Percentile Position, Excluding Ends
PERCENTRANK.EXC works like PERCENTRANK.INC, but never returns exactly 0 or 1 — matching the EXC family of percentile functions.
Read article →FREQUENCY Function in Excel: Count Values Into Bins
FREQUENCY Function in Excel: Count Values Into Bins
FREQUENCY counts how many values fall into each bin — the data behind a histogram.
Read article →