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
Advertisement
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 | 3 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 , | 7 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