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 that row) into 1 text block.
And then, because of that, I need to extract that back, into table
Convert cell into textbox
Often times, I found myself the need to design a sheet where I want users to input something, and I need them to focus on that particular single input (or more)
So, i learned a trick, it is formatting the entire sheet to certain color and give that cell special format to make it look like a textbox.
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 use it for more than 2 conditions? seriously?
Excel 2013 comes with IFS, the perfect alternative to nested IFs
As you can see, takes up to 127 conditions, neat, right?
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 do magic
Excel ad from early 90s
Guess what I just found in my old archive !
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 are limited to number of decimal places.
CEILING can round to any number, decimal or not.
Below is an example of rounding the number in D2 which is 22 into the next 7 up, 7 is in D3, result is 28 as you can see
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 many times cells found with more than 1 condition
So, when you have a table like this…
Read More “Count cells with condition – multiple conditions” »
Show developer tab
Starting Excel 2007, users are not getting easy access to developer stuff.
Not sure why is that, but we can go around that, so quickly, if you need to start developing inside Excel (using VBA) first thing to do is to show the “Developer” tab that came in hidden by default
Just follow on these steps
Get monthly total from table with dates
Again, thanks to one of my students, I am getting another post with example 🙂 I did promise her to post it after she sounded interesting in getting this example Here is an example on how to calculate total amounts of dollars per month (and number of days) from a table with dates The tricky…