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 →Atul Vij
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 →Windows 11 Clipboard History and Snipping Tool: 9 Tricks That Save Time
Windows 11 Clipboard History and Snipping Tool: 9 Tricks That Save Time
Win+V clipboard history, pinned snippets, text extraction from screenshots, delayed snips and screen recording — nine built-in Windows 11 tricks most people never use.
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 →RANK.EQ Function in Excel: Rank a Number in a List
RANK.EQ Function in Excel: Rank a Number in a List
StatisticalLevel: IntermediateAvailable in: Excel 2010+RANK.EQ returns the position of a number in a list. Ties share the same rank.Syntax=RANK.EQ(number.
Read article →Charts Lesson 5: Dynamic Charts With Drop-Downs
Charts Lesson 5: Dynamic Charts With Drop-Downs
Make one chart that changes with a drop-down: data validation list, INDEX/XLOOKUP helper range, chart on the helper, and dynamic titles linked to a…
Read article →CUBEKPIMEMBER Function in Excel: KPI Value From a Data Model
CUBEKPIMEMBER Function in Excel: KPI Value From a Data Model
CUBEKPIMEMBER returns a KPI property (value, goal, status, trend) from an OLAP cube or Power Pivot model. Cube functions read from a Power Pivot…
Read article →VBA Collections and Dictionaries: Unique Lists, Counts and Fast Lookups
VBA Collections and Dictionaries: Unique Lists, Counts and Fast Lookups
Excel VBA course lesson 14: when to use a Collection or a Scripting.Dictionary, build unique lists, count and total by key, look up values…
Read article →VBA UserForms for Beginners: Build a Data-Entry Form Step by Step
VBA UserForms for Beginners: Build a Data-Entry Form Step by Step
Excel VBA course lesson 13: design a UserForm, fill combo boxes from a sheet, validate inputs, write a new row to a table, and…
Read article →