Highlight weekends and holidays using Conditional Formatting in #Excel Part 2

Posted on January 13, 2019 by

…When the layout is bad…

Here’s the situation:

excel tip - highlight weekends and ph_part2.1

  1. There are blank rows between each rows with data;
  2. 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.

Continue reading →

Advertisements
Posted in Formula | Tagged , , , | Leave a comment

Highlight weekends and holidays using Conditional Formatting in #Excel

Posted on January 6, 2019 by

excel tip - highlight weekends and ph

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 →

Posted in Format | Tagged , , , | 2 Comments

[Share] Introduction of #Power Query M code by ExcelIsfun

Posted on January 1, 2019 by

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!

Side notes:

Power Query is available as Adds-In for Excel 2010 / 2013.  You may download it from https://www.microsoft.com/en-hk/download/details.aspx?id=39379

If you are using Excel 2016 or Office 365, Power Query is already loaded.  It is under “Data” tab and renamed as Get and Transform.

Posted in Power Query | Tagged | Leave a comment

Highlighting Top X values with Icon Set in #Excel

Posted on December 22, 2018 by

Excel Tips - Highlight Top X with drop down Part 2.1

This post is a continuation of the previous one – Highlighting Top X values with Conditional Formatting in #Excel

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

Excel Tips - Highlight Top X with drop down Part 2.3

Choose the icon set you like.  For this demonstration, the set of flags is used.

Excel Tips - Highlight Top X with drop down Part 2.4

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 →

Posted in Excel Tips | Tagged , | Leave a comment

Highlighting Top X values with Conditional Formatting in #Excel

Posted on December 15, 2018 by

Well… think this topic is too simple?  How about doing this with

  1. a dropdown menu to make the Top X a dynamic one?
  2. with Icon Set?

Excel Tips - Highlight Top X with drop down

You may download a Sample File to follow along.

Let’s start with the basics first.  Here is

The simple way of highlight Top X

  1. Select the range of data (where you the conditional formatting applies to)
  2. Go to Home Tab –> Conditional Formatting
  3. Top 10 items…
  4. The select the Top X (tip: you can customize the format)

Excel Tips - Highlight Top X with drop down 1

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:

  1. Conditional Formatting –> Manage Rules…
  2. Select the rules –> Edit Rules…
  3. 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?

Continue reading →

Posted in Excel Tips | Tagged , , , , | Leave a comment

Customize Alt 1 to 4 to become the favorite #Excel shortcuts of your own

Posted on December 8, 2018 by

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?

Continue reading →

Posted in Excel Tips, Shortcut | Tagged , | Leave a comment

Using Skip blanks in Paste Special

Posted on December 2, 2018 by

What the Skip blanks does?

Excel Tips - Paste spceial with Skip Blank

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 →

Posted in Excel Tips | Tagged | Leave a comment

The making of the bee…

Posted on November 22, 2018 by

The making of the bee2

Did you know… the bee you see on top of this page is drawn with Excel using shapes?The making of the bee1.PNGThe making of the bee

Yes… these are the shapes I used to draw the bee.  What I did was to put them together… Watch this:

The making of the bee
Continue reading →

Posted in General | Tagged | Leave a comment

Dynamic Shrinking Dropdown with Dynamic Arrays in #Excel 365

Posted on November 14, 2018 by

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.

Excel Tips - Dynamic Shrinking List with Dynamic Arrays 1

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:

You may download a Sample File to follow along IF you are on Excel 365 Insider Program.
Continue reading →

Posted in Excel Tips | Tagged , , , , | Leave a comment

Pinned Folder for #Excel 2016 – A workaround

Posted on November 8, 2018 by

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?

Excel Tip - Alternative to Pin Folder for Excel 2016_0

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 →

Posted in Excel Tips | 7 Comments

Prepare yourself to be an #Excel Dashboard Pro…

Posted on November 3, 2018 by

Have you ever had an Excel moment like this?

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 webinars (Yes, I recommend you to watch all the webinars) by Mynda.  You will find the time worthy spent as you will learn many time-saver techniques in using Excel; and also Power BI.

Free Excel Dashboard Webinars

If you’re not sure what a dashboard is, or how these skills might be relevant to you, and help you overcome the challenges above, then you can attend one or both of Mynda’s FREE Excel Dashboard Webinars.

Webinar 1 – Excel Dashboard for Excel 2007/2010/2013/2016/2019/Office 365

The first webinar will teach you how to use Excel to build this interactive dashboard below, no additional software or add-ins required, just plain old Excel and some data.

Click here to register for webinar 1.

Webinar 2 – Excel Dashboard for Excel 2010/2013/2016/2019/Office 365 using Power Query and Power Pivot

Excel is evolving; in the last couple of years Microsoft have added new tools like Power Query and Power Pivot, to name a couple.

Embracing these new tools will not only enable you to get your work done more quickly, but also since less than 1% of Excel users know these tools you’re going to have a huge competitive edge in the workplace.

It’s easy to ignore these developments and continue doing things the way you always have, but that won’t get you ahead in your career, but you know that right?

If you have Excel 2010, 2013, 2016, 2019 or Office 365* then this webinar will showcase how you can use Excel’s Power Query tool to get data from multiple sources, mash it up with Power Pivot, analyse it in PivotTables to create this interactive Excel Dashboard.

 

*Power Query is available with all versions of Excel 2010, 2013, 2016, 2019 and Office 365. Power Pivot is available with the desktop version of Excel 2010 and Excel 2013/2016 Office Professional, Office 2019, Office 365, or in the standalone versions of Excel 2013/2016. Power Query and Power Pivot are not available for Mac.

Click here to register for webinar 2.

What people are saying about the webinars

Over 35,000 people have attended Mynda’s Excel Dashboard webinars and this is what some of them had to say:

“Thank you! This webinar has already saved tremendous time and effort. I tried some of the techniques you illustrated for a cumbersome report update that I can see will cut our preparation time by 2/3’s or more. ….awesome! Many thanks!!!!”

Cynthia Tashjian

“WOW, just WOW
Can’t type more now – am trying to create a dashboard. Thank you Mynda!”

Susan

“The webinar was fantastic! I really enjoyed every moment. As you said, it was fast paced, but I am so excited to go back and review the presentation. We have been trying to get a dashboard started for my department and this really motivated me to take the lead. Thank you so much!”

Kathryn Puskar

“Oh. My. God. I know nothing! My jaw dropped a couple of times as you casually mentioned a way to manipulate PivotTable data. That’s the problem with being largely self-taught – there are huge gaps in my knowledge.”

Ann

“I presented my first dashboard to the CEO yesterday and he was ecstatic. Since we are downsizing I was very worried about my job, but after seeing his reaction…I understand that my job is secure (for now). Thanks!”

Iris Stein

Invite a Friend

Feel free to share this to your friends and colleagues and invite them to attend Mynda’s free Dashboard webinar too. They’ll thank you for it.

Dashboard Course Format

If you’ve seen Mynda’s free Dashboard Webinars and you’re ready to take your dashboard skills to the next level then check out Mynda’s Online Excel Dashboard course.

It will teach you how to build amazing interactive dashboards that impress your boss, get you noticed and help you stand out from the crowd, like this incredible le Tour de France dashboard she made, which is also included in the course:

 

The best part, aside from the praise from your boss and colleagues ;-), is you’ll also learn loads of tips you can use in your everyday Excel work to get things done more efficiently.

What to Expect in the course:

  • The course is video based and available online 24/7
  • It comes with comprehensive Excel workbooks complete with notes
  • 5 example Excel dashboards to keep
  • There’s also an option to download the videos,
  • Plus Mynda personally provides support for the first 6 weeks of the 12 month membership.

By the way, the 6 weeks of personal support is the reason the course is only open for a limited time (registration closes Nov 15). Mynda limits the number of classes to allow time for other projects and a break every now and again. That’s not to say she won’t support you after the first 6 weeks, it’s just that it might take her a few days or a week to reply to you if she’s out of town or busy working on another course etc.

 

What members are saying about the course:

The previous classes have been a huge hit with many people saying how they love the cool techniques and how they’ve been able to impress their colleagues and clients by using them in all sorts of reports, not just dashboards.

“An excellent course, Mynda. You have the gift of making the complex simple to understand.
I thoroughly enjoyed your teaching. I have applied nearly everything taught.

Thank you”

Charlie.

“This was my first online training course and I was a bit dubious about how much I was going to learn. I have to say, minute for minute I have learnt more on this course than any other course I have been on. The way the course is delivered is exceptional and the learning material and exercises really cement the learning.

This course has without a doubt furthered my career and has allowed me to secure a permanent job based on the skills I have acquired from this course. I have already signed up to another course!

Thanks”

Ryan Cunningham

You can read further comments from past students and find out more here.

Bonus 20% Off

If you register by Thursday, Nov 8 it’s 20% off .

So, do yourself a favour and check out the course. The course is awesome and it’ll transform your Excel reports and possibly even your Excel career.

 

Disclosure: I make a small commission for students who join Mynda’s course via my site (at no additional cost to you), but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, as if you won’t become a Master Chef by just reading cookbooks. You have to actually put it into practice, but then you know that.  

 

Posted in General | Tagged , | Leave a comment

Trick or Treat 2018

Posted on October 31, 2018 by

♪♫♬ This is Halloween, this is Halloween ♪♫♬
♪♫♬ Halloween! Halloween! Halloween! Halloween! ♪♫♬

Please join me to welcome our special guest – Jack Skellington, appears on wmfexcel’s Excel sheet.

Excel Trick or Treat 2018_1

♪♫♬Halloween! Halloween! Halloween! Halloween!
In this town we call home
Everyone hail to the pumpkin song ♪♫♬

Posted in General | Tagged | Leave a comment

Exciting Dynamic Array functions in #Excel 365

Posted on October 27, 2018 by

Have you heard of the new Dynamic Array functions recently available in Excel 365 (currently for Insider Fast users only)?

If you are an advanced Excel user, you will be impressed by these new functions.  They are simply awesome, super powerful and have lots of potential to solve “used-to-be” complicated problems in Excel with ease.

Leila Gharani has great example to show you how the Dynamic Array functions saves 30+ minutes of time in solving a complex problem, comparing to using traditional array formulas.   Check this out from her YouTube video:  Continue reading →

Posted in General | Tagged | Leave a comment

Learn something new from something that I think I knew well…

Posted on October 17, 2018 by

Today is a public holiday in Hong Kong.  A perfect break in the middle of a busy week.  What did I do on a public holiday?  Excel, Excel and Excel. 🙂

I watched a few videos from my favourite YouTube channels, and learned something new (to me).  Probably you think I learned some cool new features of Excel 365.  Yes I did.  Although the new features are really impressive, they are not as much as surprising than a simple trick I learned from a video in Excelisfun.

It is s a simple trick to return blank instead of “0” when a formula references to an empty cell. Continue reading →

Posted in Formula | Tagged , | Leave a comment

Calculating CAGR with Goal Seek in #Excel

Posted on October 10, 2018 by

when the starting point is a negative number…

Calculating CAGR is not difficult, all we need is the starting value, ending value and the number of periods.  Then we use the formula:

CAGR = (Ending Value / Beginning Value ) ^ (1 / N) -1 
where N is the number of periods to reach the ending period

CAGR stands for Compound Annual Growth Rate.  The formula does not require any values in between because it does not matter.  It is a “backward” calculation for the “average” annual growth with known figures.  In other words, if I have $100 on the first year and it magically becomes $300 by the end of the fourth year , and CAGR will be: 31.61%.

Excel Tips - Calculating CAGR with Goal Seek

To validate if the result is correct, I work “forward” by calculating the value of each year by applying the growth rate of 31.61%.  We can do it in Excel easily:

Excel Tips - Calculating CAGR with Goal Seek1

Note: the second formula (in C3) is referencing to C2, not B1.  From C3, you may copy the formula down.

I always do this kind of validation in order to double click the result.  I am not in doubt with the formula (which has been proven correct); I am in doubt with myself… As human, it’s so easy to make “human” error.

Well, the validation gives me peace of mind by returning consistent results, even with different values input…

Excel Tips - Calculating CAGR with Goal Seek3

Everything works fine until…. a negative value is input:

Excel Tips - Calculating CAGR with Goal Seek2

Well, if you are a finance (or math) people, you may pinpoint that CAGR cannot be computed from a negative starting value… we should start from first positive value and adjust the number of period accordingly.  Totally agree.

However, in real world situation, the requester (probably your boss) is not a finance nor a math people.  S/he just gives you three values and asks for the answer.  So the question is: Can we still do the calculation is an efficient way with Excel, when the starting value is a negative number?  Of course.

Goal Seek comes to rescue.

Continue reading →

Posted in Excel Tips | Tagged , | Leave a comment

Excel Humor #7 just happened to me…

Posted on October 7, 2018 by

Two years ago… I posted this:

excel-humor-interactive-report

which turned out a real case to me last week… I printed 99 pages of report out of one worksheet by selecting 99 items on a slicer one by one. @_@

Posted in General | Tagged | Leave a comment

Adding worksheet background in #Excel

Posted on October 3, 2018 by

How to add worksheet background in #Excel?

Excel Tips - Insert worksheet background

If there is a secret recipe for an interesting Excel training, it would probably be starting with something interesting… 🙂

So in a recent in-house training, I’ve prepared a special Sheet1 in the working file.  Like the one above.  Guess what, many participants asked instantly how to make that.

To add background to a worksheet is easy.  Simply go to  Continue reading →

Posted in Excel Tips | 4 Comments

Three days in paradise…

Posted on September 29, 2018 by

Note: This is not an Excel post.

I am back from holiday, physically 🙂

I went to a very remote village called YuBeng in Yunnan, China.  It’s a village 3200M above sea level.  To get to there wasn’t easy.  Not to mention the traveling time on flight, coach, minibus, and mini mini bus… the real challenge was the final part of the journey –  a six-hour hike on high altitude.  You know what, two-third of the trail was uphill.  And I was carrying my backpack weighted about 10kg.

Like learning Excel, no pain no gain.  Notwithstanding all the sweats and efforts, the result (scenery) pays off.  Some photos to share.

 

Btw, I’ve just got my new laptop.  That means I can resume writing Excel posts.  Stay tuned. 🙂

Posted in General | Leave a comment

[Share] – Excel Hash

Posted on September 12, 2018 by

Have you heard about the Excel Hash competition?

It is a contest initiated by Oz du Soleil at Excel On Fire.  Six well-known Excel MVPs, including Oz himself, show you what they can do with four designated Excel features/functions, and more importantly – how, on their YouTube channels.

The four Excel Features/Functions are:

  1. MAX Function – A basic function, well… you think it is?
  2. FREQUENCY Function – A not-so-well-known function for counting number of values to a specific bin.  Many people use COUNTIF(S) to do the same, because they don’t know about FREQUENCY
  3. Form Control – cool stuff to make your spreadsheet interactive (btw, I have many posts about it, check them out here)
  4. 3D Model – Something new in Excel 365

As you know, the true power of Excel never comes from a standalone function or feature. It comes from the ability of mixing function/feature together to do something great and impressive. Of course it requires out-of-the-box thinking from the people who use it. You will know what I am talking about after watching the six entries below.

The six contestants and their delicious dishes are: Continue reading →

Posted in General | Leave a comment

5 years of blogging

Posted on September 7, 2018 by

Totally forgot I put my first post here 5 years ago.  Time really flies! Thanks for flying with me. 🙂

Capture

Posted in General | Leave a comment