Here is a list of formulas I wish MS Excel had. Alas, we need to build some work-arounds to solve them though.
- CONCATENATE (range): that can take a range of cells and churn out a big text combining all of them. Of course, here a VBA UDF alternative to concatenate range of cells.
- SPLIT (text, delimiter, part): that can take a text and split it based on the specified delimiter. We will have to use a formula based alternative to split text
- UNIQUE(list, number): that takes a list and returns the unique items based on the number specified. Well, for the time being we can get unique items with formulas.
- ISBETWEEN(number, first, second): to check whether “number” is between “first” and “second”. You can do this using AND() formula like AND(number>=first, number<=second).
- FINDLAST(findthis, text): that can find the last occurrence of a particular text in another text. I don’t know any good formula for this.
What are the formulas you wish Excel had ?
3 Responses to “How-to create an elegant, fun & useful Excel Tracker – Step by Step Tutorial”
Hi Chandoo,
I am responsible for tracking when church reports are submitted on time or not and the variations from the due date for submission.
Here is the Scenario;
The due date for the submission of monthly reports is on the 5th of each month. and I would like to know how many reports have been submitted on time (i.e, those that have been submitted on or before the due date) I would also want to track those reports that have been submitted after the due date has passed.
How can I create such a tracker?
Hi Chandoo,
I am a member of your excel school.
I was trying to create SOP Tracker I follow all your steps but I keep this error below.
The list source must be a delimited list, or a reference to a single row or cell.
I try looking on YouTube for answer but no luck.
can you help on this?
thanks
Carl.
Dear Mr. Chando,
Rakesh, I'm working in a private company in the UAE. Recently, I'm struggling to get more details about the staff sick, annual, unpaid, and leaves. I would like to get a tracker in excel. Could you please help me in this situation?
I also watching your videos in YouTube. i hope you can help me on this situation.