wmfexcel

Input data on one worksheet… Get summary on another worksheet in #Excel

Posted on October 17, 2020 by MF

It is a common task that we want to input and maintain all data on one worksheet, and display summary on different worksheets. A typical layout will be similar to the screenshot shown above. One “Input” sheet, and then one sheet for displaying summary for each month. In this way, a total of 13 worksheets will be displayed on your workbook. Indeed, this was a question to me…

Can it be done?

Of course it can be done. BUT we could do it doesn’t mean we should do it.

My suggestion is to hold only ONE sheet to display the monthly summary on which users have the flexibility to select any month (or any period) by using Pivot Table Timeline.

Here’s the layout of the proposed solution:

You may download a sample file to follow along.

Sample File Download

Note: The file is saved as .xlsm (macro-enable) format. Please enable content when prompted if you wish to see the auto-refresh action.

Continue reading →

Posted in Excel in Action | Tagged Macro, Pivot Table, Timeline | Leave a comment

SUM the last X non-blank values in a column in #Excel

Posted on September 27, 2020 by MF

Got this question from my brother. He is tracking scores for game he plays with friends. The game can be played with up to four players. He wants a formula to calculate the last 10 scores of a player. He showed me the above layout and asked for the formula to SUM and AVERAGE the last 10 records of each player…… As he called me Excel guru, there is no reason I do not help him. 😁

For demonstration purpose, I change the last 10 records to 5. Indeed, I have assigned a variable (from 3-10) for user’s selection. The expected result is to obtain the SUM and AVERAGE of the highlighted cells, shown below:

And new records will come from time to time. That means the table will expand. The best way to deal with it is of course (not whole-column-referencing but) turning the data range into Excel Table.

You may download a sample file to following along.

Continue reading →

Posted in Excel in Action, Formula | Tagged Excel Table, IF, LARGE, SUMIFS | 2 Comments

Understanding Find and Replace in #Excel

Posted on September 19, 2020 by MF

Find and Replace is a very handy tool in Excel. It helps you to find a value quickly. You may then replace the value found with another value, either one by one or replace all at one batch.

To use the basic of Find and Replace, it requires no special reading as it is quite straight-forward. Perhaps the most practical tip to regular user is the shortcut keys:

Ctrl+F ==> Find
Ctrl+H==> Find and Replace

However, did you know… you can also

specify the search condition to look for “partial” or “exact” match?
ask for “case sensitive” search?
look into values only (not formula)?
even search for a format?

If you are eager to learn more about Find and Replace, and hence boost your productivity with Excel, please continue to read this article. 😉

You may download a simple file to follow along.

Continue reading →

Posted in Excel Tips | Tagged FIND, Find and Replace, REPLACE | Leave a comment

Learn how to build effective #Excel Dashboard from Mynda Treacy

Posted on August 15, 2020 by MF

Did you know… you can create such a beautiful dashboard using Excel?

Did you also know… this visually appealing dashboard was built in just more than 30 minutes?

No kidding! Check it out HERE and you will see.

Want to elevate your Excel skills??

Excel MVP – Mynda Treacy offers a wide range of Excel course online @MyOnlineTrainingHub where you can learn practical Excel skills at your own pace.

And from now through August 20, there will be 20% off for both Excel Dashboards and Power BI courses. So what you are waiting for? 😉

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.

Posted in General | Tagged Dashboard, Training | Leave a comment

Combine all visible worksheets from multiple #Excel files in a folder

Posted on August 11, 2020 by MF

It is a common task for us to combine all worksheets in a workbook. It could be a time consuming task without Power Query. With Power Query, it’s piece of cake. 😁

Wait? What about if you want to combine all visible worksheets from all Excel files in a folder? It requires a little more Magic from Power Query but it’s totally achievable without deep-diving into M code… just touching the surface would do. 😉

Let’s watch it in action:

You may download the sample files HERE to follow along:

(note: suggest you put the Q4 file aside for “refresh” later)

Step-by-Step instructions with screenshots

If you prefer reading to watching, please continue to read the step-by-step instructions below.

Continue reading →

Posted in Power Query | Tagged Combine Workbook, Invoke Custom Function, Remove Top Rows, Unpivot column | Leave a comment

Quick Tip – Probably the quickest way to open txt file with #Excel

Posted on July 25, 2020 by MF

Are you tired of seeing the (Text Import) Wizard 🧙‍♂️ every time you open txt file with Excel?

In this super short video, you will see probably the quickest way to open a txt file with Excel. Enjoy! 😉

Posted in Excel Tips | Leave a comment

Hide and Seek – Scroll Bars and Worksheet Tabs in #Excel

Posted on July 18, 2020 by MF

Did you encounter this? All the worksheet tabs and scroll bars are gone in your Excel workbook!!!

Excel Tip - Hide and Seek Scroll bars and worksheet tabs

🎶🎶 Tell me… how am I supposed to work without you…🎶🎶

No worries! They are not gone. They are just hidden (by someone somehow 🤔).

Let’s get them back to work! Continue reading →

Posted in Excel Tips | Tagged Hide and Seek | Leave a comment

Lovely stickers in #Excel 365

Posted on July 12, 2020 by MF

Do you use stickers when you use communication apps like WhatsApp? I guess that’s almost a must-have features right now for communication apps.

Would it be nice if you can use stickers in Excel too?

Excel Tips - Insert Stickers

Yes… you can, if you are using Excel Microsoft 365.

Excel365

Go to View -> Insert -> Icons -> Stickers

Excel Tips - Insert Stickers1

Explore it and have fun!

Learn more here:

https://support.microsoft.com/en-us/office/insert-images-icons-and-more-in-microsoft-365-c7b78cdf-2503-4993-8664-851085c30fce

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

Hide and Seek – Formula Bar, Column and Row Headers

Posted on July 11, 2020 by MF

Have you ever experienced a shocking moment like this in #Excel?

Excel Tip - Display the formula bar and column row headers

We are so getting used to see the formula bar, Column and Row headers that we take it for granted. They should always be there for me…. aren’t they?

What’s wrong with my Excel worksheet? 😨 Continue reading →

Posted in Excel Tips | Tagged Formula Bar, Headings, Hide and Seek, View | Leave a comment

Should I link to other workbooks…

Posted on June 21, 2020 by MF

…when writing #Excel formula

Note: This post is not about “security” issue 🤷‍♂️

Is the following message familiar to you?

Excel Tip - External links

I wonder… when you see this message, which button would you click? Please let me know by leaving comments below.

From my observation, most people would click “Don’t Update” when they are dealing with a workbook that was prepared by others.

Why is that?

Because most of the time they don’t have access to a “workbook” that is saved in someone else PC. Even the workbook (the external link) is saved in a shared folder, are you sure your counterparts have access right to that shared folder?

If a user cannot “update” the value the first (few) time they opened the file, he/she would have a tendency to click “Don’t Update” the next time they open the same file…. and worst still, he/she builds a habit of “Don’t Update” every time they see the message, which is super dangerous as he/she may not be working with the updated data that they need.

So, the question is:

Shall I link to external workbooks when writing Excel formula?

Continue reading →

Posted in Excel Tips | Tagged External Link | Leave a comment

Quick tip – Zoom in and out of the #PowerQuery editor

Posted on June 13, 2020 by MF

In both #Excel and #PowerBI

Did you know… we can ZOOM IN or zoom out everything but ribbon in the Power Query Editor in both Excel and Power BI Desktop…

Zoom InOut in the PQ Editor in Excel

… by pressing

CTRL+SHIFT+=
CTRL+SHIFT+-

respectively.

The story behind the discovery

Continue reading →

Posted in Excel Tips, Power BI Desktop, Power Query | Tagged Power BI, Power Query | Leave a comment

Insert blank columns to table in #PowerBI Desktop

Posted on May 31, 2020 by MF

In Excel, it is a common (but not necessarily a good) practice for people to add a thin blank column in a table to give a visual effect of divider. Like the screenshot below:

To insert a blank column in Excel is super easy… probably the reason of making such practice a common one, i guess. But when you try to do that in Power BI Desktop, you will find it super hard. After a little of Googling, you still have no clue how to do that.

Indeed I did not and do not recommend my colleague to do so when he asked me to replicate a report from Excel to Power BI. I said “NO” many many times. And then one day, he sent me a Power BI page that shows blank columns as divider (see below) and kindly asked me to replicate that for him…

Continue reading →

Posted in Power BI Desktop | Tagged BLANK(), Format, Table | Leave a comment

More #Excel videos with English Voice Over…

Posted on May 23, 2020 by MF

Well… I know I know, I haven’t written new posts for a while. I was lazy in previous weeks; enjoying summer breezes. You know what, if I were not doing Excel on weekends, I would probably have been enjoying hiking or camping. 😎😎

Let me share some nice photos of Hong Kong.

Isn’t it nice?

Continue reading →

Posted in General | Tagged Excel Video | Leave a comment

Replace blanks with zero in #Excel

Posted on May 2, 2020 by MF

Following the techniques used in the previous post , we can perform many Excel magics for different scenarios. On top of my mind is to replace blank cells with zero.

Excel tip - Replace blanks with zero.png

The first task is to select all blank cells in the range. We can do so by using the following shorcuts:

Ctrl+A => Ctrl+G => Alt+S => k => Enter

(Note: select any cell in the range first) Continue reading →

Posted in Excel Tips | Tagged Ctrl Enter, Find and Replace, Go to special, Keyboard Shortcut | Leave a comment

Delete rows with empty cell(s) in #Excel

Posted on April 26, 2020 by MF

Excel Tip - Delete rows with empty cells 1

Guess what? We are going to do it with a sequence of keyboard shortcuts only. Believe it or not, it requires only 8 key strokes:

Ctrl+A => Ctrl+G => Alt+S => k => Enter => Ctrl+- (minus sign) => r => Enter

You may download a Sample File to follow along.

Two major steps involved:

Select blank cells in the range
Delete “Entire row” of those selected blank cells

The keyboard shortcuts for different actions are listed below: Continue reading →

Posted in Excel Tips, Shortcut | Tagged Delete Sheet Rows, Keyboard Shortcut | Leave a comment

Transform and Combine all worksheets in an #Excel workbook with #PowerQuery

Posted on April 19, 2020 by MF

Power Query is magically powerful, yet it is not well known to majority of Excel users. I think most Excel users are not even aware of its existence… So, let’s talk about how Power Query could solve a real-life problem commonly found at workplace. 😉

One day, I got an IM message from a colleague who attended a few of my in-house Excel Training courses… (I didn’t hold any Power Query session yet; so she has no idea of Power Query at all.) She asked:

If there is a quick way to combine (append) 100+ worksheets in a workbook.

I replied:

“Of course… only if you know how!” 😁 I was bad, I know.

I then asked her to share her workbook with all sensitive information removed.

Here’s the content of the worksheets (all data are fake… of course):

Excel Tips - Transform and Combine all worksheets

What she wanted to extract from the worksheets is the range highlighted above, with the metadata “Employee Name” and “Employee ID” being put together.

Here’s the layout of the expected outcome:

Excel Tips - Transform and Combine all worksheets2

No empty columns; no empty rows… just a simple table!

The real challenge

You may be thinking… that’s easy… it can be done in just a minute. BUT remember this, there are more than 100 worksheets in the workbook and we need to do that to each worksheet.

Excel Tips - Transform and Combine all worksheets3

Then we have to combine all transformed worksheets into a single table:

Excel Tips - Transform and Combine all worksheets4

AND needless to say, we may have more worksheets (employees) in the workbook that we want to apply the same transformation and consolidation in the future.

How much time we need to do it manually?

I guess it could take a full day, or even more…

No worry! Power Query comes to rescue!

Continue reading →

Posted in Power Query | Tagged Custom function, Power Query, Table.Combine | Leave a comment

My first video with English Voice Over

Posted on March 28, 2020 by MF

Power Query – Full Outer Join Revisit Part 2

If you have been following my blog for a while, you should probably know that I’ve started my YouTube channel about one year ago. Up to this moment, I’ve uploaded 54 videos. Almost one video per week. Not bad as a part-time YouTuber, I guess. 😅

And you know, most of the videos (in the Excel Magic in One Minute) are silence, without any voice over. For the rest, I recorded them in Cantonese, which is my mother tongue.

I know Cantonese is a very niche segment for Excel learning. And I know majority of YouTube viewers do not understand Cantonese. Therefore I put English subtitles on all my videos with Cantonese voice over. I planned to continue in this way until one day I read the following comment from bebuger bebuger:

Comments

Thank you very much for your kind words, bebuger! It’s very encouraging! 😉

With your encouragement, here comes my first video in English:

I hope you like it.

Here’s the Cantonese version:

It turned out that recording English Voice Over is much more efficient. For a 10-minute video, it took me 5+ hours in preparing the English subtitles. I had to write the script, put it onto the video, and sync it with the video. Indeed it’s a quite tedious and time consuming.

This time, I recorded the video in Cantonese first. After editing the video, I recorded English voice over on it. Although it took me many times for the recording… although my fluency and intonation was not good, I finished it in just more than one hour… Cool. 😎 I should have done this earlier.

If you like my videos, please give a thumb up 👍, share it and SUBSCRIBE. 😉

Thanks again, bebuger. I hope I will have more subscribers as a result.

Posted in General | Tagged Excel Video, Power Query | Leave a comment

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

Want to elevate your Excel skills??

Rate this:

If you like it, please share:

Like this:

Step-by-Step instructions with screenshots

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

🎶🎶 Tell me… how am I supposed to work without you…🎶🎶

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

…when writing #Excel formula

Shall I link to external workbooks when writing Excel formula?

Rate this:

If you like it, please share:

Like this:

In both #Excel and #PowerBI

The story behind the discovery

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

Rate this:

If you like it, please share:

Like this:

Two major steps involved:

Rate this:

If you like it, please share:

Like this:

The real challenge

No worry! Power Query comes to rescue!

Rate this:

If you like it, please share:

Like this:

Power Query – Full Outer Join Revisit Part 2

Rate this:

If you like it, please share:

Like this:

Append Queries + Group By = Another alternative for a better Full Join

Rate this:

If you like it, please share:

Like this:

Blog Stats

Follow Blog via Email

Search wmfexcel

Want Google to translate this page?

Recent Posts

Top Posts & Pages

My YouTube Channel

Blogroll