Loading...

Follow WMF Excel on Feedspot

Continue with Google
Continue with Facebook
or

Valid

Did you know, you can plot a Waterfall chart in #Excel in less than a minute…Provided that you are using Excel 2016 or later!

No Kidding! You may download a Sample File to follow along.

A) Data layout

Note: The ending value should be computed with a simple SUM function.

B) Plot the chart
  1. Select the range of data
  2. Go to Insert tab
  3. Click Recommended Charts
  4. Go to the All Charts tab
  5. Select Waterfall
  6. OK

You should get the following waterfall chart.

Yes. You are right, the ending column should not be like that…

No worries.  All you need is just a few more clicks.

C) Setting the total column

Again, a picture tells a thousand words…

Note: The first click on the column will select all columns.  You have to click again to select a single column, followed by right-click on it to open the menu.

D) Optional, let’s do some makeup (formatting)

 Here you go!

Bonus – How about when I have subtotal?

You won’t believe how easy it is.  Watch this:

As simple as this.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Last week, I showed you how to split cell contents separated by line feed into rows with Power Query.  That was easy when we are dealing with only one column.  If you don’t know how, you may read the post here before you continue.

When we have two columns, like the screenshot below, it’s getting a bit complicating… but still very easy with Power Query.  It just requires a few more steps.

The steps involved are:
  1. Load the data to Power Query
  2. Make two branches of the query loaded
  3. Branch Query 1 is for split column A into rows
    • Add an index column as row identifier
  4. Branch Query 2 is for splitting column B into rows
    • Add an index column as row identifier
  5. Merge the two queries using the index column as mapping column; and Left Outer join
  6. Expand the result and get the values
  7. Remove the index column
  8. Load the result to worksheet
  9. Relax and Refresh (when data changes/updated)

As promised, here’s a video to demonstrate the above steps.

Split cells with line feed into rows with Power Query - YouTube

If you have already read the previous post and would like to jump to how to deal with two columns, please start watching from 03:33.

As you see, Power Query is such a powerful data cleansing tool that every serious Excel user should know about.

You may download the sample files to following along.

End note:

You may be thinking… what if we have more columns of this kind of “dirty” data.  The above method may work fine when we are dealing with 2 columns, or three columns at most.  When it comes to 5 columns, or even 10 columns, the method described could be really tedious and time consuming.  I totally agree with you.  Nevertheless, I don’t have a solution for that.  If you know or have any suggestions, please share with us by leaving comments.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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 an hour, or even three hours, to watch the free Excel Dashboard Webinars by Microsoft Excel MVP, Mynda Treacy.

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

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

I highly recommend the course, but don’t take my word for it. You can read further comments from past students and find out more here.

Early Bird Discount

If you register by Thursday, May 16 it’s 20% off.

So, do yourself a favour and check out the course.

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.

Disclosure: I make a small commission (at no additional costs to you) 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.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

It is a common task to compare two lists in Excel.  This can be achieved with MATCH function.  But what if you want to compare a list of filenames stored in your spreadsheet, with the files you’ve got in a folder?

Will you do it manually, by eyeballing? @_@

The first challenge is to get the list of files you have in the folder.  The second challenge is that fact that you will receive files and put them into the folder from time to time.  In other words, the folder content is changing.  You absolutely want a dynamic solution that helps you to monitor status at ease, so that you know which files are missing.

Here comes Power Query to rescue.

Note: All the screenshots are coming from Excel 365.  If you are using Excel 2010/2013, Power Query has its own tab on the ribbon.  Moreover, you need to download and install Power Query for Excel as add-ins.  You may download Power Query for Excel in the following link: 

https://www.microsoft.com/en-hk/download/details.aspx?id=39379

If you are using Excel 2016 or later, Power Query is already built in and resides in Data tab, but renamed as Get and Transform.

The following is the step by step approach to solve the problem.

A. Convert the list of files in an Excel Table

Select a cell of the data range, then press Ctrl+T to create an Excel Table

  1. Check the data range is correct
  2. Check “My table has headers”
  3. OK

B. Load the Table to Power Query
  1. Go to Data Tab
  2. Click “From Table/Range

Now you should have the Power Query Editor opened.

Note: The Power Query Editor is where all the magic happens.  Nevertheless, the intention of this post is not to explain in details all the magical stuff in Power Query.  Hence please follow the steps and focus on the solution to the problem stated.

  1. Rename the query.  As a good practice, always rename your query to something meaningful.  In our case, it’s renamed as “FilesRequired“.  That is the files we are expecting in the designated folder.

C. Load the list of files in the designated folder to Power Query
  1. On the Home tab of Power Query Editor
  2. Click the pull down menu of New Source
  3. Select Folder

Another dialog box opened.  From there,

  1. Either browse to the folder path, or input directly
  2. OK

In just a second, you should see the following.

  1. Simply click Transform Data

Note: If you are using Power Query for Excel 2010/2013, the button “Transform Data” is  labelled as “Edit”.

Now you should have the second query loaded to Power Query Editor.  This query showcases attributes of all files in the designated folder.

  1. Rename the query to “FilesReceived

A quick recap here:

By now, you should have two queries loaded to Power Query Editor.  And the queries are

  1. FilesRequired – From the Table on the spreadsheet, i.e. the list of files you are expecting
  2. FilesReceived – From the Folder, i.e. the files you’ve got in the designated folder
D. Merge the two queries with Left Anti Join

Now we are going to compare the Query FilesRequired to FilesReceived, in order to identify the files that are available in FilesRequired, but not yet appeared in FilesReceived.  This can be achieved by merging the two queries.

  1. On the Home tab of Power Query Editor
  2. Open the pull down menu of Merge Queries
  3. Select Merge Queries as New

In the subsequent Merge dialog box:

  1. Select FilesRequired
  2. Select FilesReceived
  3. Click the header “Files Required” under FilesRequired
  4. Click the header “Name” under FilesReceived
  5. Select Left Anti (rows only in first)

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Flash Fill was introduced in Excel 2013.  After 6 years, it is still a hidden gem of modern Excel that most regular Excel users do not even know its existence.  Whenever I show to my colleagues what Flash Fill is capable of, they are all impressed and think that this is their “Greatest Discovery” moment.

So in this post, I am going to show you in a flash what Flash Fill can do with 16 examples.

Note: CTRL+E is the keyboard shortcut for Flash Fill which had been used in all the GIFs in the coming demonstration.

Example 1 – Extract First Name and Last Name where coma is the separator

Example 2 – Similar to example 1 with no delimiter but CamelCase

Example 3 – Get the 3rd (or Nth) value from CSV

Example 4 – Separate Text from Value and vice versus

Example 5 – Swap the position of First Name and Last Name

Example 6 – Replace the coma with Space

Example 7 – Add Initiate in the middle

Example 8 – Change all texts to lower case

Example 9 – Change all texts to UPPER case

Example 10 – Change only Last Name of UPPER case

Example 11 – Get Initiate, and then add it as a prefix

Example 12 – Get Initiate, and then add it as prefix without the helper step

Example 13 – Get Initiate, and then add it as suffice

The following examples involve more than one pattern.  It requires you to “teach” Excel with more correct examples.

Example 14 – Swap First Name and Last Name without coma, and add initiate as suffice

Example 15 – Getting value from the inconsistent pattern

Example 16 – Getting value from the super messy pattern

Are you convinced now Flash Fill is simply amazing?  However, in order to have Flash Fill worked well, you need to:

  1. Study your data and find the pattern
    • Flash Fill works well when there is a consistent pattern
  2. Input examples next to the data (table)
  3. Review the suggested results
  4. Teach” Excel with correction, when the suggestion is not correct
  5. ALWAYS check your results, esp. when there is more than one pattern

Last but not least, explore Flash Fill on your own!

You may download a Sample File to follow along.

Please let us know if you have any other wonderful use cases of Flash Fill.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview