-
Can’t assign R-like subs to button
If your sub name begins with either R or C followed by a number, Excel fails to assign the macro to Form Control: The dropdown in Assign Macro window is a RefEdit control that works with formulas, and it probably can’t assign subs that look like cell address1 (R1C1 reference style). Sources:
-
Enter Selection
I already knew that Tab moves active cell inside selection from left to right, but I didn’t know Enter does the same but top to bottom:
-
Translate Formula
I know Google Sheets supports formula =GOOGLETRANSLATE() that translates a phrase into another language. However Excel does not. VBA can make the job, but this can also be achieved with Excel formulas! The formula goes like: Sources:
-
Alt+Enter in Ready mode
Alt+enter inserts new line in a cell when in Edit mode: In Ready mode it.. repeats recent action! I haven’t worked it out yet, but it looks like it repeats only some actions, not all. For example, it doesn’t repeat when you insert worksheet with plus button: ..but it does when you do it through…
-
Conditional Custom Format
I knew that Custom Format syntax consists of 4 blocks: But I didn’t know it can also recognize conditions1: See example2 from the GIF: Source:
-
AsteRISK in formula
Asterisk in Excel formula match any string, but blanks. Imagine you need SUMIF to sum up values by any* string incl. blanks. You need to expand criteria argument to {“*”,””} and wrap into another SUM: *you may ask – why not to use SUM instead of SUMIF since the criteria doesn’t filter out anything? The…
-
Assign to Mid
I always thought Mid function can only return a value e.g.: But we can also assign a value to Mid and it will update the original String!
-
Fragile Named Range
What happens when you drag a cell from Named Range? *if your drag inwards (to make Named Range smaller) by more than 1 cell at a time then it will shrink down by 1 cell anyway.