#Excel Humor #12 – Power Voice

Did you experience the Power Voice for #Excel?

Excel Humor (12) - Power Voice  Like this post if you did.   😛

Advertisements
Posted in General | Tagged | Leave a comment

Data transformation with Power Query

Transform date input as “Excel-unrecognized” text string into date (number) usable by #Excel

This is a continuation of the previous post – How to turn “1st January, 2017” into #Excel recognizable date?   …using Get & Transform (better known as #PowerQuery).

Excel Tips - Date format from text to number with PQ

Note: All screenshots and steps in this post are based on Excel 2016.  The ribbon of Power Query for Excel 2010/2013 may be different a bit… but the steps and interfaces should be more or the same.

The problem was discussed in the previous post.  So let’s go straight to the solution:

Continue reading

Posted in Power Query | Tagged , | Leave a comment

How to turn “1st January, 2017” into #Excel recognizable date?

Quite a long time ago, I wrote a post to discuss a trick to format date with “st”, “nd”, “rd”, etc…

In this post, I am going to discuss the reversed way: Turning an English written date with “Dst MMMM, YYYY” into date (i.e. number) that Excel recognizes.  The following screenshot illustrates the situation:

Excel Tips - Date format from text to number.PNG

What’s the problem?

First of all, let’s understand what the problem we are facing here.  The date was presented as a real text string, not a number stored a text.  For instance, “21st December, 2017” is a real text string.  That means we cannot turn that text string with simple tricks discussed before.  Luckily, we are dealing with Excel problem that can be solved by formula.

Here’s the solution:

Continue reading

Posted in Formula | Tagged , , , , , , | 5 Comments

Repeat all item labels in Pivot Table (aka Fill in the blanks please)

Quite a long time ago, I wrote a post Fill in the blanks – quickly to talk about the tip to turn a human-reading-freindly table into an Excel-friendly table, as shown below:Excel Tips - 10.1

Have you ever thought about why we are doing this?  Of course you know because we need the “right” table for further analysis.  Let me rephrase my question: Where is that Excel-unfriendly table on the left coming from?  Bingo.  #PivotTable in #Excel itself.

Most of the time (in my experience) your colleagues summarized / aggregated data in a pivot table, then copy and paste it as value before sharing to you… Sound familiar?

This kind of problem is not uncommon, even nowdays.  Why I am saying this?  Because there is an easy way to fill in the blanks in Pivot Table itself but not many people are aware of it yet. Continue reading

Posted in Excel Tips | Tagged | 3 Comments

Extract part of text string with delimiter(s)… and more with Text to Columns

In the previous post, we talked about the basic of Text to Columns and focused on “Fixed width”.  In this post, we will continue to talk about three magical things that Text to Columns could do with ease:

  1. Extract part of text string with delimiter(s)
  2. Convert numbers with trailing minus, e.g. “123-” into number
  3. Convert numbers with “,” (comma) as decimal point into number

You may probably know about the first one,  but be honest did you also know the second and third one?  🙂  Let’s take a quick tour to all of them.

You may download the Sample File – Text to Columns (2) to follow through.

Extract part of text string with delimiter (s)

Situation: This is quite common, I guess.  Let’s continue with the example discussed in the previous post.  The task is to extract Latitude and Longitude from the text string in column A: Continue reading

Posted in Excel Tips | Tagged | 2 Comments

Extract part of a text string using Text to Columns

There are many not-so-easily-understood terms in Excel. “Text to Columns” should be one of them.  Probably due to the name of it, Text to Columns is not a common tool for many Excel users.  But indeed, if you know what it does, you will love it.

What is Text to Columns?

It is one of my best buddies in Excel.

It is a great tool to extract specific text from a text string without the use of a single formula.  It is a great news for those who are not familiar with formula yet, isn’t it?

When you hover your mouse on the icon of Text to Columns under Data Tab on Ribbon, you will see the description about it…

Excel Tips - Text to Columns

Is it much clear now?

Tip: You may see a better description and sometimes even tip of using a tool when you hover your mouse onto its icon on Ribbon.   Try the same to Format Painter.

Most of the time, we will use a “Delimiter” for Text to Columns.  And the “Fixed width” is somehow forgotten.  In some occassions, “Fixed width” is very handy and useful when we deal with text string with constant pattern.

In this blogpost, I will focus on using “Fixed width”.

Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Free Excel Dashboard Webinars by MYNDA TREACY is back

If you have been following my blog for more than three months, you should have read this before.  If you wonder why I am reposting it again and again, the answer is simple: it is awesome.  You could learn awesome Dashboard skills by watching the webinars alone.  If you are serious in taking your Excel skills to the next level, you should really take time to learn more about Excel Dashboard.

What is Excel Dashboard?

If you’re not sure what a dashboard is, or how these skills might be relevant to you, or you think Excel Dashboard is too advanced for you, then you can attend one or both of Mynda’s FREE Excel Dashboard Webinars.  You will be surprised that it is less difficult than you may have thought about.  Nevertheless, you are expected to know Pivot Table.  Let’s take just one hour to watch one of the webinars, you will see!

 

Webinar 1 – Excel Dashboard for Excel 2007/2010/2013/2016

Excel Webinar

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.

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

If you have Excel 2010, 2013 or 2016* 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, analyze it in PivotTables to create this interactive Excel Dashboard.

Excel Dashboard with Power Query

But hurry seats and sessions are limited. Click here to register for the free webinars.

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

What’s next after the webinars?

If you’ve attended Mynda’s free Dashboard webinar then you’ll know how powerful Excel is and how having Excel Dashboard skills will skyrocket your productivity and career.  If you want to acquire the Excel Dashboard skills, you may enroll Mynda’s course.

Loads of people have already registered and are on their way to wowing people with some killer reporting tricks, not to mention getting their work done faster.

20% early bird discount ends Thursday, October 5

So, go ahead and enroll here before the discount ends on October 5, at 8pm in Los Angeles to be exact.

Excel Dashboard Course

Disclosure: I make a small commission for students who join Mynda’s course via my site, 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 it 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, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.

Posted in General | Tagged , | Leave a comment

Data Validation to restrict user from inputting to future days

The situation

We need to input certain data on a daily basis.  For example, daily sales.  That is supposed to be an easy and routine task for many people.  BUT… it happened that we still have a chance to input data to a wrong date, e.g. tomorrow… like below:

Excel Tips - Data Validation to restrict input to future days

Seriously?  We need to worry about and protect our spreadsheet for such a simple task?

Yes.  Why not?  Indeed not many people know we can do that in Excel.  It is quite simple if you know how to use Data Validation – Custom.

Avoid mistaken input to future days

To avoid that, we may set a simple Data Validation to the cell range B2:B31: Continue reading

Posted in Excel Tips | Tagged , | 2 Comments

Lookup a specific combination of characters exclusively, e.g. 5A not 5AA

Got the following question:

hi, how do i search for a specific combination of characters exclusively i.e. if one cell contains 5A;3B&4C while another cell contains 5AA;3B&4C, and my search criteria is 5A, i want the results to return only the cell that contains 5A, not both cells because i am not looking for 5AA

To visualize the data described, I put a few more cells following similar pattern.

AABC;3B&4C
5AA;3B&4C
5A;3B&4C
5AA;3B&4CC
5A;ABCDE
5AC;3B&4C

And because of limited details given, I’ve made an important assumption: The search term followed by a common delimiter, i.e. “;” in this case.

If that’s the case, the request is doable with formula along:

Excel Tips - Lookup AA but not ABC.PNG

Continue reading

Posted in Formula | Tagged , , , , | 1 Comment

The Clipboard can be useful occasionally…

According to study (which was conducted in a totally unscientific way with zero level of confidence…, i.e. my gut feeling), 100% of Excel users use Copy and Paste every time they work with Excel. 🙂  However, 99.99999% of them never used Clipboard.  Agree?

But indeed, Clipboard could be helpful in some occasions:

For example:

Excel Tips - Clipboard

The top of mind thing should be something like selecting the cells and then Copy and Paste:

Excel Tips - Clipboard

However it doesn’t work as the selections are not continuous and not on the same row/column.

For situation like this, you may need to do the copy and paste one by one… manually.

Seriously?  Sort of.

Nevertheless, the Clipboard may help a little bit… just a little bit.  See below screen cast for illustration (Pls observe what’s happening in Clipboard): Continue reading

Posted in Excel Tips | Tagged , | 2 Comments

Free Template – Working Calendar

Today, I celebrate wmfexcel.com (aka BeExcellence) reaching One Million views by…  not going to work (because typhoon is hitting HK).  😉

Thank you very much for visiting my blog in the past.  Every single visit and page view from you constitutes my little achievement here.

As a token of appreciation, I want to share a template of working calendar with you.

Here’s a snapshot of the calendar.  Continue reading

Posted in General, Template | 4 Comments

Using MIN/MAX to answer whichever lower/higher questions

What is “Whichever higher”?

Situation: You call to your favourite restaurant to reserve a private room for family dinner on Saturday.  The restaurant manager tells you that there will be a minimum charge of $2000 for a private room.   That mean, if your orders exceed the minimum charge, you will pay for what you order.  On the other hand, if your orders just $1500 values of food, you still need to pay $2000.

Put it in other words…

IF $Order > Minimum Charge, Then $Order, Else Minimum Charge

In Excel, the top-of-mind function should be IF:

=IF(A1>B1,A1,B1)
where
A1 = $Order
B1 = Minimum Charge

Indeed, the same can be achieved by using MAX:

=MAX(A1,B1)

Isn’t it a nice alternative?

What is “Whichever lower”?

Situation: You want to subscribe a mobile data plan.  However, you are worry that you are spending too much than you can budget for.  Luckily, most mobile vendors offer what they called “Hassle-Free” data plan:  No matter how much data you use, your bill payment will be capped at a fixed amount, say $1000, i.e. a maximum payment. Continue reading

Posted in Formula | Tagged , , | Leave a comment

wmfexcel (aka BeExcellence) is now multilingual

<Note: There is no Excel tip in this post.>  🙂

Multilingual?? Really??  Not exactly.

The fact is I’ve just added a widget to the side bar on the right:

wmfexcel - Google translate.png Continue reading

Posted in General | Leave a comment

Filter a list of items from a long long list

A simple but practical tip of using Advanced Filter

I came across a blogpost from Jon of Excel Campus (one of my favorite Excel sites) last week.  In his post, he showed how to filter for a list of items using a reverse partial match lookup.  Obviously, it is a formula approach.  From his post, you will see how Excel formula can save you from tedious works at workplace.

In this post, I am going to replicate Jon’s example and achieve similar results without a single formula.  Thanks to Advanced Filter!

Where is Advanced Filter?

Yes.  It’s right there – just next to the Filter that most people have used it.

Excel Tips - Advanced Filter

But… I believe there is a high chance that you may have never used it before… 🙂  If that’s the case, please continue to read and you will find it super helpful.

Although with the term “Advanced”, Advanced Filter is indeed not difficult to use at all.

You may download a Sample File here to follow through. Continue reading

Posted in Excel Tips | Tagged , , | 5 Comments

Interactive Chart is not difficult to make – Part 3/3

Using Option Button to display different items on chart

So far, we have made an interactive chart with check box to highlight items with negative growth; with scroll bar to see more items in a confined space.  In this post, we will go one step further: Adding Option Button to display either “Values” or “Units”:

Excel Tips - Interactive Chart with Option Button

You may download a Sample File to follow through.

The data for “Unit Sold” resides on a separate worksheet <Data in Units>.  Luckily enough it follows exactly the same structure of  the “Values table”.

This makes the enhancement an easy job, if you know how to use CHOOSE function. Continue reading

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

Interactive Chart is not difficult to make – Part 2/3

Using Scroll Bar to show more

This is an extension of the previous post – Using Checkbox to highlight negative growth in a bar chart.  In that post, we had created a chart showing top 10 items, which allowed user to highlight item(s) / bar(s) with negative growth via a check box.

Excel Tips - Interactive Chart with Check Box5

You boss loved it and asked… can you show also the 11th, or 12th…… even to the 50th items?

Yes, we have a total of 50 items in total in the data.Excel Tips - Interactive Chart with Scroll Bar.PNG

Let’s add a scroll bar to enhance this interactive chart so that user can scroll to see all the items if they want (but 10 items in a time).

In this post, I am going to show you how to insert a scroll bar and use the INDEX function to retrieve the data you need for the chart.

You may download a Sample File to follow through.

Continue reading

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

Interactive Chart is not difficult to make – Part 1/3

Using Checkbox to highlight negative growth in a bar chart

In previous post, we talked about the basic of Form Controls.  I hope you had time to practice it and found useful way of building interactivity to your spreadsheet.

In the coming few posts, I will show you a few examples of using Checkbox, Scroll Bar and Option Buttons to make an Interactive Chart.  Below is a semi-finished product…

Excel Tips - Interactive Chart with Form Controls

What is Interactive Chart?

You may have heard about the terms “Interactive Chart”, “Dynamic Chart”, or even some other terms that are describing more or less the same thing.  Simply put, it’s a chart helping users visualize the data, with some kinds of Input.  The input can be from direct input to cell(s);  form controls, slicer, or it’s simply a pivot chart.  Whatever it is, the interactivity is a result of the change in the source data of a chart.  

Excel Tips - Interactive Chart with Check Box1

A chart is only a reflection of its source data.  Although we often use the terms “Interactive/Dynamic Chart”, what’s behind the scene is indeed the effort to make the source data for chart “Interactive/Dynamic”. 

In coming posts, let’s focus on using different Form Controls to make the chart (data) interactive.

In this post, I will show you two tricks (assuming you know the basic of chart creation, and basic functions like IF, TEXT):

  1. To show %chg as part of the label axis
  2. To add a Checkbox to highlight bars with negative growth

You may download a Sample File to follow through. Continue reading

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

The awesome Dashboard course by Mynda is closing

Hurry, Last Chance for Free Dashboard Webinars & Course

Just a quick reminder that registration for the awesome Excel Dashboard and Power BI courses, by Mynda Treacy closes on Thursday July 13, 8pm in Los Angeles, to be exact.

Learn how to make interactive Excel Dashboards in Excel, Power BI or both and open up job opportunities with your newfound skills.

There’s no denying that Dashboard skills are in demand for Excel users everywhere. Mynda’s courses will have you up to speed quickly so you can start benefiting ASAP.

Excel or Power BI?

If you’re not sure which course, then if you’re quick you can catch a replay of the free webinars and see both tools in action.  Indeed you will learn many Excel tricks from the webinars alone.

Click here for the webinar replays.

Mynda also put this list of Pros and Cons together to help you decide whether Excel or Power BI is the right tool for your needs:

If you are still not sure about which one to go for, I would suggest Excel Dashboard first. With no doubt, there is very high chance that you see Excel on every computer at your workplace; while Power BI is still behind in terms of penetration, although it is catching up at a high speed.

Registration Closes July 13

Find out more and compare Excel vs Power BI for building Dashboards on the course pages below:

Click here for the Excel Dashboard course

Click here for the Power BI course

Tip: you can get the Dashboard and Power BI courses in a discounted bundle on the Power BI course page here.

 

 

Disclosure: I make a small commission for students who join Mynda’s course via my site, 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, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.

Posted in General | Tagged | Leave a comment

Excel Humor #11 – CEO

A lazy Sunday in summer… let’s have some Excel humor. 🙂

Excel Humor (11) - CEO 1

Excel Humor (11) - CEO

Posted in General | Tagged | Leave a comment

Are you ready to turn boring Excel reports to Business Intelligence?

Well, I know.  You may have read similar topics before in my blog.   You are now seeing it again because the awesome Excel Dashboard Course by Mynda Treacy is now open.  What’s more exciting?   Mynda puts her popular courses “Excel Dashboard” and “Power BI” together so that you get the most out of it.  I like her table comparing pros and cons of Excel vs Power BI.

Excel vs Power BI Pros and Cons

The table below compares key points between Excel and Power BI:

What a great summary!  Nevertheless, I don’t agree on the “Cross” being put next to “Easy to learn” for Power BI.  It really depends.  If you possesses skills and knowledge about Power Query / Power Pivot, you will find Power BI is super easy to learn.   Having said that, if you are advanced Excel users but new to Power Query / Power Pivot, eager to acquire Dashboard skills, the Excel Dashboard Course would be a good start to next level.  Let’s see what are offering by Mynda.

Free Webinars

There’s no denying that Dashboard skills, whether in Excel or Power BI, or both are a huge advantage to Excel users around the world and in a vast range of industries. Just look at any job website and you’ll plenty of jobs that mention ‘Dashboards’ in the description.

To get you off to a running start Mynda is hosting a series of free webinars to teach and demonstrate Excel and Power BI for building dashboards.

Webinar 1 – Excel Dashboard for Excel 2007/2010/2013/2016

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 the webinar.

Many Excel users will already have the skills to build this dashboard, and seeing Mynda put them together will be exciting and inspiring when you realise how quickly you can get up and running with Excel dashboards.

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

Excel is evolving; in the last few 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 you’ll also have a huge competitive edge in the workplace because less than 1% of Excel users know these tools.

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 …you know that, right?

If you have Excel 2010, 2013 or 2016* 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 and 2016. For Office 365 users with Excel 2013, Power Query is only available in Office 365 ProPlus. Power Pivot is available with the desktop version of Excel 2010 or Excel 2013/2016 Office Professional, Office 365 ProPlus, or in the standalone editions of Excel 2013/2016. Power Query and Power Pivot are not available for Mac.

Click here to register for the webinar.

Webinar 3 – Power BI Dashboard Reports (Replay)

See what all the hype is about first hand in this replay of Mynda’s Power BI webinar where she builds these two interactive dashboards in under one hour:

Click here to see the Power BI webinar replay.

Who is Power BI for?

Power BI is for the Excel user who is ready for more. More features, because Power BI Dashboards can do things you simply can’t do in Excel, and more skills to add to your résumé.

So, if you’re wanting more interactivity from your Dashboard reports and you’re prepared to learn some new skills then check out Power BI.

What people are saying about the webinars

Over 25,000 people have attended Mynda’s 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 post to your friends and colleagues and invite them to attend Mynda’s free Dashboard webinars. They’ll thank you for what they can learn from the webinars.

Webinar Prerequisites

All you need is an internet connection, a web browser and speakers or headphones.

You don’t need Power BI or Excel to attend the webinars.

20% Off Excel Dashboard and Power BI Courses until July 7

If you’re ready to take the next step and add these skills to your repertoire then don’t miss out on the discounts on Mynda’s Excel and Power BI Dashboard courses. You can even get multiple courses in discounted bundles.

Click here for the Excel Dashboard course and bundles

Click here for the Power BI course and bundles

Both the free webinars and Mynda’s Dashboard and Power BI courses are available for a limited time, so hurry.

Disclosure: I make a small commission for students who join Mynda’s course via my site, 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, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.

Posted in General | Tagged , , | Leave a comment