A small Excel file to show how we can create a chart-like Excel sheet
Used in my Resume to show different skill sets and the level of expertise in each
Category: Standard functions
Standard functions that comes with Excel, plain single functions
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 function to list sheet names, but it is a start
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 that row) into 1 text block.
And then, because of that, I need to extract that back, into table
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
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” »
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…
IF with AND and IF with OR
The powerful function If can already do a lot of tricks, but we can for sure do more when we use it with AND or OR functions.
Also, once we understand that, we can use the power of combining AND and OR inside the Logical test of IF, to make it even smarter
Once good example is when we need to see if a certain date is within the same month as this month like below…