Quick Tip – Edit #DAX formula in #PowerBI Desktop efficiently

Let me share a quick tip of Power BI today coz I work with Power BI Desktop a lot recently. 😎

What I am going to share in this post is my favorite shortcut key in Power BI Desktop. Honestly, I know just a few shortcuts in Power BI Desktop (much less when comparing to Excel). Nevertheless, the following shortcut is absolutely my all-time favorite, even a lifesaver! What is it?

CTRL+SHIFT+L

What it does?

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

Highlight differences in two ranges with Conditional Formatting in #Excel

Happy New Year! Wish you all an Excellent Year of 2022!

To kickstart the year, let’s share a trick to solve a common task – highlighting differences in two ranges, with conditional formatting.

Common enough, right?

The ingredients are simple, for most cases, what we need to know are

  1. The formula to compare two cells, e.g. =A1=F1 (in this example);
  2. The location to put this formula for Conditional Formatting

Are you ready? You may download a sample file to follow along:

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

Highlight different cells in two lists in #Excel

Finding and highlighting differences should be a common task in Excel. Most of the time, it’s solved by formula or conditional formatting. Either way, we need to construct a simple formula like the one below to compare the cell contents in two lists.

= A1 = B1 

In this post, I am going to show you a non-formula approach which is very handy, especially when we are dealing with a one-off task. What we need is just a few keystrokes, or mouse clicks.

Let’s watch it in action:

Prefer reading to watching? No problem. You may refer to the blogpost HERE or continue to read.

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

Fixing inconsistent data type in lookup table in #Excel

A common VLOOKUP problem with an easy fix

The situation – VLOOKUP fails…

Have you ever encountered something like this? This is quite common. We are sure that the VLOOKUP formula is correct. We are sure that the lookup values are available in the lookup table (101, 106 107 in our example above). What we are not sure is the reason of the error returned… What happened?

The answer is simple: The “101” in B5 is different from the 101 in F5. What?? What’s the difference? The former is text, while the latter is numeric value. This is a basic but important concept for all Excel users, which I had discussed in the post HERE.

To fix the problem, we need to fix the data type in the lookup table. In other words, we need to make sure that all values for Room No. in the lookup table should be text (because the list of the lookup values in column B is text.

You may be wondering; can we simply change the cell format to text. See below:

Unfortunately, it does not work!

We cannot simply convert a number to text by changing the cell format. If we want Excel to accept a numeric input as text, we should set the cell format to Text BEFORE, not after the value is input.

We can use the function ISTEXT to test whether a value is text or not. See below:

Now we understand the problem. It’s time to show various solutions using formula approach. The trick is to use the function TRIM to convert all values under the lookup column into text to make the formula work.

You may download a sample file to follow along.

Let’s start with something simple, with a helper column.

Continue reading
Posted in Formula | Tagged , , , , | 2 Comments

Do Not input “-” for zero. Use proper formatting in #Excel

Do you input a hyphen to represent zero? If you do, please stop doing so in the future. The proper way of displaying “-” for zero is to apply relevant cell format to it.

E.g. Using Accounting format with no currency symbol:

or apply the following custom format string if you want to display number with no decimal and the ordinary +/- sign:

#,##0; -#,##0; -

Note: The first, second, and third part indicates how we'd to format positive number, negative number and zero respectively.

Why does it matter?

It doesn’t matter only if you do not expect further processing / calculations of your data. That should rarely be the case… I guess.

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

Convert numbers of different digits into text of fixed digits using #PowerQuery

This is an extension of the previous post.

Here’s the task:

If the data is coming from a CSV file, the solution is super easy.

You may download the sample CSV file to follow along.

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

Convert numbers of different digits into text of fixed digits in #Excel

The situation

We have an extensive list of numbers in different digits, say from 10 to 13 digits. The problem is, they are supposed to be numbers in thirteen digits stored as text. (Of course, in our example, we work with a short list of twenty numbers only πŸ˜‰)

Our task is to convert them back to 13 digits as text, not numbers as shown below:

The solution is indeed quite simple. It can be solved with the following formula:

=TEXT(A2,"0000000000000")'copy down
where A2 resides the value we want to convert;
the 13 digits of "0" is the format string that we want

As simple as this. If 15 digits is required, simply revise the second argument to fifteen zeros (enclosed with a pair of quotation marks) accordingly. Nonetheless, have you wondered why it happened in the first place? This is indeed the main point of this blogpost. 😁

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

How to replace value with a value from another column in Power Query

It is a common task to replace a value with another value in Power Query. It can be easily done with “Replace Values”. However if you want to replace a value with the corresponding value in another column, it’s not that straight-forward in Power Query. Most of the time, we would do it by adding a conditional column, followed by removing the original column. It works, but it requires more steps.

Indeed, we could achieve this by using Replace Values in Power Query, followed by a simple modification to the formula generated.

Let’s watch it in action:

Want step-by-step instruction? Please continue to read.

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

The Power BI Journey

Note: This is not a "how-to" post.  In this article, I share my thoughts on how people start their Power BI journey, based on my observation and imagination. 😁

When you say you want to learn Power BI, what do you actually mean?

Power BI is a “relatively” new tool for analytics in business world. With no doubt, Power BI is getting more attention for its great power in visualizing data; in creating stunning dashboard; in getting insights from data by interacting with it, just to name a few. Many people think that Power BI is a visualization tool because this is what they “see” from a user’s perspective. This is super normal. However, what they don’t see, and probably are not aware of, are the building blocks behind the sense.

Hey, don’t get me wrong. These are not the building blocks I talked about.

These are the building blocks of reports/dashboard though

From my experience, these are the starting points most users user/learn Power BI. It happened because they are users of Power BI report that is created by IT (or someone else). They don’t need to worry about (indeed have no idea) how the data is being pull together from various data sources (where Power Query plays a crucial role); they don’t need to worry anything about Data Model or Power Pivot (how different tables are linked together through relationship); and they have never heard of DAX because they don’t have to. Without any knowledge just mentioned, they are still able to use the report to get information they need.

Continue reading
Posted in General, Power BI Desktop | Tagged | Leave a comment

7 Copy and Paste Tricks I wish I knew sooner in #Excel

Regardless of what we do with Excel, Copy and Paste is something we do on daily basis… or even hourly basis. 😁

You may be thinking, every one knows Copy and Paste. Yes, probably. But is every one using Copy and Paste efficiently? In this short video, I will show you 7 Copy and Paste tricks that I wish I knew sooner. Check it out!

You may download a sample file to follow along.

Posted in Excel Tips | Tagged | Leave a comment

Protect your cells in #Excel

Think about this… you have spent hours of work building your wonderful Excel template. Needless to say there are lots of formulas that took you hours of thorough thoughts and considerations, on top of the beautiful formats and layouts. You are very happy with it and you share it to your colleagues. Just a few minutes later, your colleague came to you and said your formula did not work; many errors came out; and seems that someone had “carelessly” messed up some formats and layouts…

You opened the workbook and you found that someone

  1. deleted rows and columns that they are not suppose to…
  2. inserted rows and columns at wrong positions…
  3. applied different color-filled and font sizes in an ugly way…
  4. and many more…

Your mood travelled from heaven to hell in just a few minutes.

Is it familiar to you? If it is, you have to know how to protect yourself cells from editing.

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

[Video] – Swap cell contents with a simple mouse trick

It is super easy to move cells around by drag and drop. The standard drag and drop action however replaces contents in the destination cells. What if we want to preserve destination cells and just shift them up or right? A simple mouse trick could help.

Check it out in this short video!

Prefer a short article instead? Click here.

Posted in Excel Tips | Tagged | Leave a comment

[Video] How to create a dependent dropdown list in #Excel

This is the follow up post from my previous post – How to create a dependent dropdown list inΒ #Excel. As mentioned, it could be easier to illustrate the concept using a video, especially for the use of Dynamic Arrays.

Here comes the videos, in both English and Cantonese. Hope you like it. πŸ˜‰

English Version:

Cantonese version:

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

How to create a dependent dropdown list in #Excel

…with and without Dynamic Arrays (which is available to Microsoft 365 users only)

Got a question from a friend recently:

Is it possible to show a different set of questions in a dropdown list depending on the answer input from a previous question using Excel?

Me:

Of course! It is just a dependent dropdown list using Data Validation.

I had blogged about the technique for different scenarios. However I have not yet written a post for the basic of it. So, let’s do it. 😁

What is dependent dropdown?

A GIF tells thousand words:

It is clear, isn’t it?

Then the question is how?

There are many different ways to achieve this because it is Excel. Instead of showing you many different ways, I am going to focus on two approaches. One is for majority of Excel users who are not accessible to Dynamic Arrays (Microsoft 365); and one for Microsoft 365 users.

You may download a sample file to follow alone.

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

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 | 2 Comments

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