SUMIF vs. GETPIVOTDATA in terms of performance in #Excel

Did you experience a slow workbook? How slow? Let’s say it takes more than 2 minutes to just open the file.

This is actually what a colleague asked me for help. When I opened the workbook, I saw LOTS of SUMIFS across tens of worksheets. And all SUMIFS refer to a common dataset on the workbook.

Then I asked: “Would you consider using Pivot Table instead of so many SUMIFS?”

The answer was “NO” as she needed to use formula to return results that fit into the “pre-set” table layouts, which I totally understood… such a common workplace’s scenario!

Then I proposed GETPIVOTDATA instead of SUMIF(S).

Her responses told me that she didn’t know about GETPIVOTDATA. Maybe I should write a blogpost about GETPIVOTDATA. Before that, let’s test if GETPIVOTDATA is more efficient than SUMIF.

Continue reading
Posted in Excel Tips | Tagged | Leave a comment

The very hidden gem of AutoFilter in #Excel

This is a hidden feature called “AutoFilter”. You have to look it up and put it on QAT intentionally.

Don’t get me wrong. I am not talking about the Filter button sitting on the Data Tab of ribbon,

This is “Filter” icon on Data Tab

although they look almost exactly the same.

I am pretty sure that most people who use Excel five days a week is not aware of “AutoFilter”.

You may help strengthen/correct my understanding by a quick poll: 😁

Well, maybe you know about AutoFilter… but are you sure you know the “hidden” features of it?

There are two features, a standard one and a special one, of AutoFilter that I find really helpful. Let’s check it out.

You may download a sample file to follow along:

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

The proper way to filter values not equal to zero in #Excel

I didn’t think of blogging about this topic before. I thought that it is so straight-forward. Nevertheless, there is another way of doing so which is even more straight-forward BUT may give you unexpected results later on. And to my surprise, that is the way most people (I encountered) do. Maybe it’s worth blogging about it.😁

You may download a sample file to follow along.

Situation

It is not uncommon to filter out all values that are not equal to zero. Let’s say we want to filter all records with Unit Sold not equal to zero, it is very common for us to simply uncheck 0 from the list of values. I did that (long before) too. πŸ˜…

By unchecking “0” from the list, we thought we have told Excel to keep all values but 0. However, Excel interprets it differently. Let’s see by hovering on the filter icon for unit sold:

Excel keeps the records that is equal to 100 or 19 or 24 or 31 or 42…… etc.

What??? πŸ€”πŸ€”

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

Reapply Filter in #Excel

Did you notice that there is a “Reapply” button next to Filter on the Data Tab of ribbon? And wondering what it does? πŸ€”

Indeed, the “Reapply” button is dim until you have applied filter to at least one column.

To illustrate how to “Reapply” filter, let’s start with a simple example.

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

Append tables with inconsistent column names with #Excel #PowerQuery

In the previous post Rename column names in a dynamic way with #Excel #PowerQuery, I talked about the key steps involved to rename column names in a dynamic approach; and the inspiration as well as the thinking process.

As mentioned at the end of that post, we could apply the techniques for a practical and common problem – Append tables with inconsistent column names, as shown in the diagram above.

As promised, here’s the video to illustrate how to do it. I hope you like it. πŸ˜‰

Continue reading
Posted in Power Query | Tagged , | Leave a comment

Rename column names in a dynamic way with #Excel #PowerQuery

How can we do it with Power Query without advanced M code? πŸ€”

Situation:

We have many different tables to be appended. It should be a simple task with Power Query. You may refer to my blogpost here for the basic of appending tables with Power Query. Nevertheless, life could be challenging in workplace. What if the tables do not have consistent column names? We need to convert all the column names into common names before appending tables. Are we going to read and rename all column names table by table, manually? Of course not! And this is exactly the reason for this post! πŸ˜‰

Inspirations and the Thinking Process:

Before jumping to the solution, I would like to share with you the inspirations and the thinking process behind the scene.

Continue reading
Posted in Power Query | Tagged , | 2 Comments

Data Cleansing is never easy, even with #Excel Power Query

Don’t make me wrong. I am not complaining Power Query at all. If you have followed me for a while, you should know that I am a big fan of Power Query indeed. It is simply powerful! With Power Query, lots of data cleansing, reshaping, consolidating tasks become easy and efficient. If you want to learn more what Power Query could do, have a look at my blogposts and playlist of videos related to Power Query.

To me, data cleansing is a process with seven stages:

  1. Understanding the expected outcome
  2. Studying the data on hand
  3. Finding the pattern(s)
  4. Cleansing the data (this is the stage most people interested in)
  5. Checking the results
  6. Refining the cleansing steps (most of the cases required, and need to go back to stage 2)
  7. Repeat the above cycles until the expected outcome is obtained

Most of the time, we focus on stage 4, i.e. Cleansing the data itself. Nevertheless, stages 5 to 7 are critical to success, although often got unnoticed.

Let me show you an example here. (Note: this post is not a step-by-step tutorial).

Continue reading
Posted in Excel Tips, General, Power Query | Tagged | Leave a comment

Combine different ranges of data across worksheets in #Excel using Power Query

In the previous blogpost, I showed you how to use INDIRECT to prepare a summary table based on values from different ranges across worksheets. In this post, I am going to show you how to do that with Power Query.

Why Power Query? Because it is simply powerful! 😁

With INDIRECT, the summary table won’t update by “Refresh”. When new sheet is added, you have to add the sheet name and extend the formulas manually. With Power Query, a click of refresh would do.

You may download a sample file to follow along.

Let’s watch it in action:

Video with Cantonese VO is under production. Stay tuned. now ready:

Do you think it will be even better if we can do it across workbooks in a folder? Of course we can. Check this out and see if you can applied what you learned here. πŸ˜‰

Posted in Power Query | Tagged , , | Leave a comment

#Excel INDIRECT function explained with a use case

There are many #Excel functions that you may not find them useful when you first learn them. Sometimes you may even doubt why there are such functions. INDIRECT should be one of them. πŸ€”

What INDIRECT does?

It returns the reference specified by a text string.

The syntax is simple:

=INDIRECT(ref_text, [a1])
where ref_text is required.  It is the text string describing the cell reference
[a1] is optional.  When it is omitted or TRUE, Excel treat ref_text as A1-style reference; when it is FALSE, Excel treats ref_text as R1C1-style reference.

Common examples are in the simplest form.

Say in A1, we input something like B2, B4, D10, etc. (any valid cell reference). And then in another cell, say B12, we reference to A1 using

=INDIRECT(A1)

When we change the value in A1, the formula in B12 returns another value based on the text input in A1. The following screencast shows the effect:

Now I hear you:

Why don’t we simply use a direct reference? We can do the same by inputting the simple direct cell reference in B12

=B2 

and we can change the formula to =B4, B6, D10, or whatever valid cell reference, easily whenever needed. What’s the point of using INDIRECT and reference it somewhere else?

I had the same question too… until I found a valid use case of it.

You may download a sample file to follow a long.

Continue reading
Posted in Formula | Tagged | Leave a comment

Filter: It’s easy and not easy

Applying filter in Excel is a very common task. We used to filter a dataset and focus on a subset of data that we are interested in. It is super easy to filter. The problem is, are you sure you have the right dataset being filtered?

Situation

I have a list of files stored in a folder. All files are generated automatically with the same filename convention: “FiscalYearWeekyyyyww.csv”, where yyyy = year; ww = week. The file started from week 01 of fiscal year 2018, all the way to the latest week. The latest file, say, is “FiscalYearWeek202105.csv”.

Consistent filename convention. Looks good! Right?

The task is to combine all files but those for FiscalYear 2018, using Power Query of course. (note this post is not about Power Query, but you may refer to other related post here).

Easy! We can exclude files of fiscal year 2018 by filter rows where ‘Name’ does not contain 2018, as shown in the following screenshot.

If this is what you do, please pause for 10 seconds and think carefully…

Do you really want to do this?

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

What’s new in Excel 365?

Are you using Excel 365 but not sure what’s new?

Have you heard about all the amazing new functions/features like Dynamic Arrays in Excel 365 but not sure what they are?

Or you have no idea at all how Excel 365 is different from standalone versions of Excel?

If you answer “Yes” to any of these questions, I suggest you take some time to read the blogpost Personal Stock Portfolio Dashboard by MYNDA TREACY , and more importantly watch the video there.

Why?

Because you will see how to build such a beautiful Excel Dashboard in less than 30 minutes using some of the new features available in Excel 365.

Personally, I think this is an excellent video demonstrating some core new features like Excel Data Types and Dynamic arrays. Not to mention how easy an Excel Dashboard can be built in 30 minutes (when you get the skills required). 😁

At the end of the blogpost, you will also see more Excel Dashboard post by MYNDA. You will learn a lot of Excel skills, tricks and tips by reading those posts and watching the videos associated with them.

If you are planning to uplift your skills in Excel Dashboard, consider the popular Excel Dashboard Course by MYNDA TREACY. There is a 20% discount until Feb 11, 2021. πŸ‘

So, do yourself a favor andΒ check out the course. The price is incredibly fair, the course is awesome and it’ll transform your Excel reports and possibly even your Excel career. πŸ˜‰

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 , , , | Leave a comment

Combine files of (slightly) different layouts in a folder with Power Query in #Excel

Part 2 – What if we have inconsistent table structures across files?

Note: this is a long post. πŸ˜‰

This is the continuation of previous post. Suggest you read that post first if you are new to Power Query. Moreover you may download sample data files there to follow along.

In the previous post, we used Power Query to combine all files in a folder. With that built, we can get new data file by a simple click of Refresh. The following screencast shows you that we have combined three data files. When a new file – Week 4 data, comes into the folder, we can easily get the new data by a click of Refresh.

Watch this:

Before Week 4 data, there were 63 rows of data. After we have 84 rows of data.

Isn’t it great? It is awesome! 😎

Then next week, we have data file for Week 5. We feel great as we expect a consolidated table by putting the data file into the same folder, followed by a click of Refresh… BUT…

we didn’t see the date of week 5, but (Blank)? What happened? 😨

Let’s have a closer look at the Queries & Connections Pane

Continue reading
Posted in Power Query | Tagged , | Leave a comment

Combine files in a folder with Power Query in #Excel

Note: This is a long post… πŸ˜‰

Part 1 – What Power Query does for us in the process?

Combining data in different files in a folder is super easy with Power Query!

If you know about Power Query, you should know that combining files in a folder is a powerful yet simple task using Power Query, especially when there is a consistent pattern among all files. Like in the example above, there is only one worksheet “Sheet1” in each file. Moreover, all files share consistent layout with common column headers, which reside on row 1.

When new files come in week by week, a simple click of “Refresh” would get us the combined table for further analysis. As simple as that.

It worked like a charm until one day, your I.T. team changed the headers slightly… without getting consent from you (and all other users) πŸ™„

For whatever reason, the headers had been changed to below:

Excuse me? Breaking the headers from one row into two rows??? What’s the point of doing this? I have no idea but it happened. 😭

And you knew it… the query was broken.

The luckiest part is, the change was minor AND it’s confirmed that it will not changed anymore. Put it in other way, all the coming files will have two rows of headers, with the same data layout in the four columns in the same order in the future.

To fix the broken query, we could:

  1. Modify the query to fit table structure for “new” files and manually change the headers in all old files for consistence or
  2. Modify the query to fit the table structure for both new and old files

Which option would you go for?

If there is only one or two “old” files, I would probably pick option 1. What if there are tens of old files in place already? Option 2 is the preferred way. No doubt! 😁

Either way, in order to modify the query for combining files, we need to know what to modify, and more importantly where to modify.

Therefore it’s better to understand what is happening when we try to combine files in a folder using Power Query. This will be the focus of this blogpost.

Continue reading
Posted in Power Query | Tagged | Leave a comment

Add emoji to your numbers 😁 in #Excel

Is it possible in #Excel? Of course, as you have just seen it! 😁

Indeed, it’s easier than you may have thought about. We can do it by custom format.

First of all, select the cells that you want to apply custom format and then right click…

or simply press Ctrl+1 to open the Format Cells dialog box:

Continue reading
Posted in Excel Tips, Format | Tagged | 2 Comments

Farewell 2020… Happy New Year!!

2020 comes to an end in just a few hours . I guess it’s the first time majority of people shares the same new year wish.

It was a though year. Keep in faith! Tomorrow will be a better day!

Wish you all a healthy and happy new year of 2021!

Posted in General | Leave a comment

Quick Tip – Close all #Excel workbooks at once

Have you ever had more than 10 workbooks opened at the same time?

Did you spend too much time to close them all one by one? If so, you will love this simple trick to close all workbooks simultaneously!

The trick is to hold Shift key while clicking the “X” on any opened workbook.

Let’s watch it in action:

p.s. Remember to save changes to your work before closing. πŸ˜‰

Posted in Excel Tips | Tagged | Leave a comment

Dense Ranking with Power Query – Unexpected Behavior and Workarounds

This post is inspired by the article – Dense Ranking in Power Query, written by PHILIP TREACY from myOnlineTraingHub.

The first part of this post is a summary of what I read from Philip’s post. While the second part is an alternate way to solve the problem, using just a slightly different approach.

What is Dense Rank?

Again, a picture tells thousand words. What we want to achieve is to add a column of “Dense Rank” according to the scores by course. See below:

It could be achieved by creating a “helper table” with unique scores by course in descending order.

The straightforward way is to load the table into Power Query, and then sort the “Course” in ascending order; followed by “Score” in descending order.

Continue reading
Posted in Excel Tips | Tagged , , | 2 Comments

Hide and Seek in #Excel – PivotTable Fields

Have you ever encountered a situation like this? How are we supposed to work with a PivotTable without seeing the data fields?

I was so lost when I saw this from a workbook on my friend’s notebook. He came to me for PivotTable help but I was not able to even work with the PivotTable he opened and presented to me… How embarrassing… πŸ˜‘πŸ˜…

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

Replace values in filtered ranges using Copy and Paste trick in #Excel

Situation:

In a filtered range of data, we made few changes on the side. Then we want to replace the original values with the updated values.

What action appear on top of your mind? Copy and Paste of course.

But… if you had tried, you knew it… a regular Copy and Paste does NOT work. πŸ˜“

Did you see the problem?

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

Have you encountered any weird formula in #Excel?

Happy Halloween! πŸ‘»

Let’s do something fun.

You are invited to leave your comments below. For what? For sharing with us any “weird” formula that you have ever seen in Excel. Real cases please. 😁

Wait… what do I mean “weird”?

Let’s define it in this way:

A weird formula is one that is written in a “strange” manner. It could be the uses of functions, or simply the way it is written.

Here’s my examples:

=SUM(VLOOKUP(A1,D1:F10,2,false))
=SUM(A1+A2+A3)
=SUM(A1*A2)

The above examples are real. I received a workbook where all formula are wrapped with SUM, for no particular reasons. All formula work perfectly fine without SUM.

So now is your turn. What weird formula you’d encountered? Please leave your comments. πŸ‘»

Posted in Formula | Tagged | 1 Comment