Sheet() and Sheets()
Getting total number of sheets (Sheets), or ID of this sheet (Sheet). The two new formulas Sheet() and Sheets() can do well, yes not perfect, we still need to have …
Category: Standard functions
Standard functions that comes with Excel, plain single functions
Worksheet name, dynamically using formula
Get the name of the active worksheet in active workbook, using formulas only This function needs to have workbook saved Then, you just paste below formula into any cell …
Text-to-Columns, dynamically using formulas
I often use the technique of concatenating columns into 1 cell with separators. Something like the CSV, 1 line that has all values for a single row (all columns for …
FORMULATEXT to show formulas
Back in the day, we used to need to write a full macro (also called User-Defined-Function) to allow users to show the formula from a certain cell Not any more
IFS (and IFNA) to avoid nested IFs
I tend to see more and more the usage of nested IF functions recently Now, do not get me wrong, IF is great, but come on, are you going to …
Highlight dates dynamically
Conditional formatting is another powerful feature in Excel, especially when you combine it with functions A simple function as in below if you set it up inside Conditional formatting, can …
Limited Ceiling
The function CEILING (CEILING.Math as of Excel 2016) is a very good powerful function. It allows you to round number to any value up Unlike ROUND, ROUNDUP and ROUNDDOWN, that …
Count cells with condition – multiple conditions
We did see who we can get total of a column for a certain month in certain year using SUMIFS. SUMIFS got a sister function, COUNTIFS Which will count how …