VLOOKUP across tables is so easy in #Excel365
WMF Excel
by MF
2M ago
Again, a picture tells a thousand words. The following GIF shows what we want to achieve: Seriously? A simple VLOOKUP formula stated below would do. What’s the challenge? =VLOOKUP(G3,B:E,4,FALSE) Yes, you are right if all these tables reside on the same worksheet. What if they are sitting on different worksheets? or even on different columns? I would say it is super challenging to create a VLOOKUP formula to lookup values from different tables in the old days. Nevertheless, with Excel 365, it’s easy! You may download the sample workbook to follow along. sample-workbook-vlookup-across-tables ..read more
Visit website
Dependent Dropdown menu using OFFSET in #Excel
WMF Excel
by MF
3M ago
Quite a long time ago, I wrote about two different approaches of preparing dependent dropdown menu in Excel. The first approach was for non Excel365 users, while the second approach was for Excel365 users. You may refer to the post here. In this post, I will be using the same sample workbook, solving the same problem, but by a third approach using OFFSET, an Excel function that went unnoticed. You may wonder why? Because of the limitations of the first two approaches: For the first approach using INDIRECT function, it’s quite flexible indeed. For example, if we have four items in the first d ..read more
Visit website
Split text with delimiter(s) into columns or rows and even table with TEXTSPLIT in #Excel365
WMF Excel
by MF
4M ago
It’s a common task to split text into columns in Excel. We can do that with “Text to Columns” under Data tab. That’s very handy, however it returns only static result (good for one off task) and could not split text into rows. With the new function “TEXTSPLIT” in Excel 365 (or latest version of Excel), we can overcome these two limitations with ease. I will show you the common use cases for this function in the video. I hope you like this video. If you do, please give a thumbs up, share and subscribe to my channel. If you prefer reading to wach, please continue to read. Moreover, I will wal ..read more
Visit website
(Kind of) Paginated report in #Excel
WMF Excel
by MF
5M ago
A picture tells a thousand words. This is what we want to achieve. It is only “kind of” a paginated report because it works only on the application (Excel) but not for printing. With dynamic array and new functions in Excel 365, this task is not difficult at all. Only two functions involved. They are CHOOSEROWS and SEQUENCE functions. The spin button is not something new. It’s been in Excel for too long. Just that you may not have a chance to use it before. Let’s watch it in action. You may download a sample report to follow along. sample-workbook-paginated-report-on-sheetDownload ..read more
Visit website
Quick #Excel 365 Tip – Combine values from a range of cells into comma separated text
WMF Excel
by MF
6M ago
Before 365, this needs to be entered into the CONCATENATE function one by one, not to mention the commas in between. With Excel 365, use the function: ARRAYTOTEXT. Bingo! Say the range of text resides in A1:A9. All you need to input is =ARRAYTOTEXT(A1:A9) This makes our life so much easier ..read more
Visit website
Unpivot Columns with #Excel #PowerQuery
WMF Excel
by MF
7M ago
It is so common that we deal with crosstabs in Excel. It is good for displaying summarized data. However, if you need to further analyze the data, especially with PivotTable, you will find a crosstab not the right layout. What will you do then? Move the data one by one to the PivotTable-friendly layout? I saw my colleague did that before. No kidding. If you are talking about just a few rows and columns, no big deal. Silly but doable. What if there are hundreds of rows and tens of columns in the crosstab? Are you going to spend the entire day just moving cells around? Power Query comes to resc ..read more
Visit website
Pad a column to fixed length with #Excel formula
WMF Excel
by MF
10M ago
Here’s the situation: We have a column of values with various lengths, from 1 to 5 letters. We want to convert that into a fixed length of, say 5 letters, padding with zero(s). The following screenshot illustrates the requirement. How would you do it? If “IF” is on top of your mind, please continue to read as I am going to show you a few diverse ways to achieve this. The approaches I am going to show you are: Using TEXT (works best when we have numeric values only) Using nested IF (probably the most commonly used approach, but not necessarily the best) Using SWITCH (an alternative to nested ..read more
Visit website
Got bitten by an undefined data column in #PowerQuery
WMF Excel
by MF
10M ago
The importance of explicit data type in Power Query What could go wrong when we have a column of “ANY” data type in Power Query? Well, it depends. Depends on what? Sometimes luck! What’s wrong here? This query has been working well all the time until one day… A real story of myself One day a colleague called me for the following error message that stopped the queries from running in her Power BI report. I helped her build that Power BI report quite some time ago. She has been updating the data sources and running the report successfully every week without any error. One day, she was stu ..read more
Visit website
Add Index Column in #Excel using #PowerQuery
WMF Excel
by MF
11M ago
In Power Query, it is a super easy task to add a column of sequential numbers. We can do it by adding an Index Column. There are three options for adding Index Column: From 0 From 1 Custom (both starting and increment value are customizable), using this option may not give you a column of sequential numbers as you can customize incremental values other than 1. This can be demonstrated in a 1-minute video, even without narrative. (I was lazy indeed) However, adding an Index Column alone by itself may not be helpful, unless you simply want to assign a surrogate key to a table. (Tip: Adding I ..read more
Visit website
Add Column From Examples with #Excel Power Query
WMF Excel
by MF
11M ago
Use it with caution although it’s a powerful feature! In the previous post, we talked about how to add conditional columns in Power Query in which we identified the store type of a store by the prefix of the store number. Then we tried to define the store grade by its sales. We did that by adding conditional columns. In this blogpost, we try to do the same but with a different approach, using Column From Examples. Before we start, let’s see a few stunning examples about adding column from examples using Power Query. In a way, Column From Examples is remarkably similar to Flash Fill. The key di ..read more
Visit website

Follow WMF Excel on FeedSpot

Continue with Google
Continue with Apple
OR