Long time ago, I wrote a blog post How much do I need to pay? which was not really related to Excel but calculation of a random daily event. Similar to that old story, a friend asked me for an Excel solution for a different scenario, which I think is quite interesting and really need Excel to do the calculation.
Long story short – when we have more than one birthday stars in a party, maybe a birthday star should treat other birthday star(s) as well. Thus, s/he will need to pay a share. WOW… it sounds like an Excel challenge that I really like to take.
In the previous blog post, we see how Flash Fill extracts First Name and Last Name from an email address in a format shown above. I’ve also recorded a video for that post.
Flash Fill is so smart to detect the pattern of CamalCase and return the desired result in a flash. However, if you are still using Excel 2010 or before, it is NO EASY TASK and required advanced skills in formula writing. The key challenge is to identify the position of the second CAPITAL letter in the text string. Once we have identified the position of it, getting the First Name and Last Name is totally manageable.
Here’s the formula to identify the position of the second CAPITAL LETTER:
{=MATCH(TRUE,CODE(MID(A2,ROW(INDIRECT("2:"& FIND("@",A2)-1)),1))<=90,0)+1}
Note: This is an array formula, requiring Ctrl+Shift+Enter
Without a single input in formula bar. UI only. No kidding!
What’s even more unbelievable? The above can be done in just a couple of minutes.
The technique discussed in this blog post is applicable to Excel 2016 or later. And the techniques used are mainly Flash Fill and Quick Analysis, which are available since Excel 2013. But why I said it’s applicable to Excel 2016 or later? Because the chart types Histogram and Pareto were introduced in Excel 2016.
I am not saying that we cannot perform the above transformation using Excel 2013/2010. It’s just a matter of time + higher skill level is required.
Glad to share with you my second video here. This video is about how to plot a compelling to compare Actual vs. Budget, Target and Last Year data. The written instruction is available in previous post – A compelling chart in three minutes…
I spent more or less the same amount of time (~12 hours in total, on and off) to produce it comparing to the first video. However this video is 8-minute long, which is eight times longer than the first one. In a way, I am doing it more efficiently. 🙂
Honestly it takes much more time and effort than writing a blogpost. After making just two videos, I can imagine the GREAT efforts all other Excel geniuses like ExcelIsFun, MrExcel.com, Leila Gharani, Excel On Fire (just to name a few) they have put on their channels, where you will find a lot of great Excel videos. Really appreciate their efforts! 👍 Indeed they have inspired me a lot through my Excel journey.
I am not dreaming about my channel will become as popular as theirs. Though I do hope, my channel will get more popularity from time to time, especially in the Cantonese-speaking communities. My goal is to produce one to two videos per month. Should you have any comments/suggestions, please leave your comments.
Last but not least, if you like my video, please SUBSCRIBE my channel.
you need to plot chart to show actual sales vs budget, target, and LY sales;
agree that the chart on the right is a better visualization such purpose;
want to know how to create the chart on the right;
Then
please continue to read this post.
In business world, we often compare actual sales to various benchmarks such as budget, target, and last year. In this post, I am going to show you step-by-step how to make a compelling chart for this purpose. This is basically a simplified version of bullet chart, and is super-easy to create. I hope you find it useful and relevant.
Kung Hey Fat Choi! This week is Chinese New Year. I wish you a fruitful, healthy, and rewarding Year of Pig!
I have almost one week off for CNY holiday. I have cleaned my PC and spared 50G+ space in the hard drive; got a new fantastic video production and editing tool; and more importantly produced my first Excel video and posted it on YouTube.
Indeed, I have been thinking about the topics for making my first video. There are a few on my list but I could not decide. Then all a sudden when I was having shower one day (yes… many ideas come from 🚿), an idea came to my mind: Why don’t make my first video for my top blogpost – When unhide row doesn’t work…? That’s why we have this video: Continue reading →
Chinese New Year is coming really soon. Let’s talk about something about “New” in Excel in this post… but don’t make me wrong, I am not talking about any new features in Excel 2016/2019/365. Indeed, what I am going to discuss about is a feature that exists for a long long time, but not many people are using it (at least not to the people I work with).
Interestingly, this “New” stuff is very common and I believe all Excel users have used it at the most fundamental way.
Still have no idea what I am talking about? Watch the following:
Note: All screenshots are prepared using Excel for Office 365
We don’t know what we don’t know. The best way is to learn something new is to explore the web (or world), and/or learn from experts.
Sometimes we just don’t know, or even don’t expect what Excel could do for us.
If you’ve ever felt like:
“My Excel reports take way too long to update each month/week”
“I spend hours collating and cleaning data, updating formulas and charts and then no one reads my reports anyway, what’s the point?”
“If I could just impress the boss I’d get that pay rise/promotion I deserve”
“I need to get my Excel skills up to date so I can stand out from the crowd of other job applicants”
“I’m not sure how to approach setting up my Excel workbook the right way, so it’s easy to build and maintain”
Then you should spend a few hours in the coming days to watch the three webinars (Yes, I recommend you to watch all three webinars) by Mynda. You will find the time worthy spent as you will learn many time-saver techniques in using Excel; and learn something about Power BI. Continue reading →
The INDIRECT trick of using Structured Reference in Conditional Formatting
Is it a feature or a bug?
This is an extension of the previous post, in which we discussed the use of INDIRECT trick to deal with Excel Table in Conditional Formatting, to fix the problem we face demonstrated below:
David N gave a magical tip to handle that DIRECTLY. Here’s his comment:
I believe you’ll also find that simply writing your conditional formula to refer to an entire column from a Table somehow enables that range reference to auto-magically grow as the Table grows without needing to use INDIRECT or other trickery…
When setting up conditions for Conditional Formatting in Excel, have you ever tried to set a formula that refers to a Excel Table, but failed?
First of all, why we want to refer to Excel Table in Conditional Formatting? There is one simple answer: To accept new data automatically.
Let’s look back at the sample we used in the previous two blog posts. In the following screen shot, you see that the formula used refers to a “fixed” range of $I$2:$I$9. It works totally fine given the current static situation.
But what if we are moving into May and are ready to input more “PublicHolidays” to the list?
Oh no… it didn’t work as the formula was hard-coded.
To fix that, we need to go into the formula of Conditional Formatting and revised the hard-coded range. (provided that you remember to do it; and/or your user knows how to do it) 🙂
That’s why we want to make the formula used in Conditional Formatting be dynamic!
We want to highlight both rows (the blank row and the row below when that’s a weekend), i.e. Row 4, 5; 6, 7; 18,19; 20,21) in the above example.
Well, you may not consider the above layout bad. Indeed it is quite common in workplace. A blank row is inserted for whatever reasons (and some people quite insist on it)…
Let’s see how this “bad” layout complicates the whole process of setting up of conditional formatting to highlight weekends.
Note: This post is a continuation of previous post. You may want to read the previous post first if you do not know how to use conditional formatting to highlight weekends.
It is not uncommon to work with dates in Excel. Be it a Gantt chart for project management, a roster, or simply a calendar, we would like Excel to highlight non-working dates. One of the most common tasks should be highlighting weekends and public holidays. It could be a tedious manual task when you need to maintain it on monthly basis. The good news is this tedious, boring task can be done by conditional formatting with ease.
Leave the boring tasks to Excel, enjoy your life more. 🙂 Continue reading →
Happy New Year! Wish you all an Excel-lent year of 2019!
As an Excel nerd, I’d like to spend my holidays on learning Excel… haha… I know I am abnormal. ;p My recent love of Excel is Power Query (M) and Power Pivot (DAX). As their names say, they add POWER to Excel.
I really like Power Query as the user-friendly User Interface offers most of the Power! We can do lots of amazing stuffs by applying steps in Power Query editor, simply via the UI. Having said that, learning the M language behind the scenes would definitely take you to the next level. However, it is not an easy journey…
If you are already familiar with the UI of Power Query Editor and are ready to go further with M, I’d highly recommend you spend an hour to watch the “extensive” introduction of M code of Power Query by Mike Girvin of ExcelIsFun:
Like it?
Bear in mind that, this is only the beginning. You will still need to study and practice, again and again, to become a true master of Power Query which is my new year resolution. 🙂 Let’s work hard toward this!
So I will go straight to the point. For background information, please read the previous post. 🙂
To insert Icon Set
Select the data range –> Go to Home Tab –> Conditional Formatting –> Icon Sets
Choose the icon set you like. For this demonstration, the set of flags is used.
This is the result you will see. However, we don’t want all three flags. We just want to have a green flag for the Top X value(s). So let’s manage it. Continue reading →
Select the range of data (where you the conditional formatting applies to)
Go to Home Tab –> ConditionalFormatting
Top 10 items…
The select the Top X (tip: you can customize the format)
Super easy! Indeed it is a very handy way to highlight Top X values in a dataset. The only drawback is the Top X is statics. If you want to change the number of values to be highlighted later, you need to go deep into the conditional formatting to revise the Top X value:
Conditional Formatting –> Manage Rules…
Select the rules –> Edit Rules…
Change the value, then OK, OK
This slideshow requires JavaScript.
Not quite ideal…
So why don’t reference the (Top) X to a cell, say G2, where a user can input directly?
When talking about keyboard shortcuts, there is always discussion on what’s the most time-saving shortcuts, what are the top X shortcuts you should learn, what is the most frequently used shortcuts, etc…
Just to name a few, Ctrl+S to save, Ctrl+Z to undo, Ctrl+C to Copy, together with Ctrl+V to paste. I won’t disagree these are popular shortcuts and probably the most frequently used shortcuts for most users. Nevertheless, we always want to learn more and then start searching on the Internet for various shortcuts. But did you know how many shortcuts are there for Excel? I don’t know the exact number, but I know there is a lot! More than I can remember a quarter of them. So it is not practical to try to learn them all.
Then the question is:
Which shortcuts key should I learn?
My answer to this question is: You are asking the wrong question. Your question should be:
When I work with Excel, the most frequently performed actions are A, B, C, D… What are the shortcuts for these actions?
Have you ever had this question? From my experience, 99.9% users (I work with) have no idea what it is or even are not aware of that option in Paste Special.
Are you one of them? Let’s try to understand what it does through the following imaginary conversation between Excel and you… Continue reading →
The recent HOT topics about Excel should be the new Dynamic Arrays! After watching all the amazing demonstrations about the new Dynamic Arrays, I’ve decided to sign up to the Office Insider (Fast) programme (for Office 365 subscriber only). And I finally got it last Saturday. I was thrilled to try it out. Simply awesome!
Then I try to solve a problem with the new dynamic arrays – Creating a shrinking dropdown list, as shown below.
It is a common task to assign people to different groups. Once a person is assigned to a group, s/he should not be shown up again from the dropdown list in order to avoid duplication.
In “old” Excel, I needed three helper columns, with a “scary” array-formula, together with the old-school trick to create a dynamic list by using OFFSET function as Named Formula. It took me quite a while (in terms of hour) to figure it out and make it work correctly.
With the new Dynamic Arrays in Excel 365, I need only one helper column with simple MATCH function + Dynamic Array. And believe it or not, it took me only a couple of minutes. Let’s see how:
Dear Excel 2016, you have made so many enhancements that help me a lot in work… but why you took away my favorite Pinned Folder by giving me a half page of white space?
My workplace just got upgrade to Office 2016 from 2010 (it’s a big jump although it is not yet reaching to the latest version). I guess I am the only one in the office who is feeling excited and happy about the upgrade. Nevertheless, I could not find my farovite Pinned Folder… which I used to have in Excel 2010… as well as Excel 365 (which I use at home). Googled a bit and realized that the Pinned Folder is not available in Office 2016 for whatever reason.
Probably Microsoft knows that we are trained to accept limitations of Excel and most of the time we can think out-of-the-box work-around solutions indirectly…
Whenever there is obstacle, there will be work-around. (hopefully)
Although imperfect, I would like to share two alternatives for your consideration (if you are using Excel 2016, or 2013): Continue reading →
Disclosure: The author earns a small amount of commission (at no additional cost to you) for the product/service successfully sold through the links of [Affiliate].
Wanna buy me a drink!?
Click below photo to my PayPal.Me
I appreciate it. :)
Transform your technical information for non-technical audiences through better data visualizations, reports, slideshows, and dashboards. I provide workshops, webinars, conference keynotes, coaching sessions, and custom design projects.
Fresh Excel Tips, Power BI, Power Pivot, Power Query, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity
