[Affiliate]
Blog Stats
- 3,268,504 hits
-
Join 3,244 other subscribers
- Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?
-
Recent Posts
Top Posts & Pages
- Calculate number of "*day" in a given period
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- Rename column names in a dynamic way with #Excel #PowerQuery
- How to lookup an image in #Excel
- Quick Tip - Edit #DAX formula in #PowerBI Desktop efficiently
- About
- =SUM('???'!C3) Is it a valid formula?? No. It is magical indeed!
- The INDIRECT trick of using Structured Reference in Conditional Formatting
- Dropdown calendar in Excel
- Perform VLOOKUP with 2 lookup values
My YouTube Channel
My Facebook page
- My Tweets
Author Archives: MF
Create Sequential Numbers in #Excel Easily
There are multiple ways to create a list of sequential numbers in Excel. The most common one should be simply type 1 in a cell, followed by drag and drop (fill-series). Another common approach would be using a simple formula … Continue reading
#Excel IMAGE function is great, but…
If you are using #Excel365, you have the privilege of many powerful functions. IMAGE is one of them! This function works like a charm with just one argument required. Think about a simple formula like this: =IMAGE(A1) It returns the … Continue reading
Personal update
Why am I so lazy in blogging in the past year? If you have been following my blog or YouTube channel, you may have noticed I have been inactive for almost a year… don’t worry about me. I am all … Continue reading
Posted in General
2 Comments
Combine contents in a range of cells into a singe cell, with TEXTJOIN function in #Excel365
A decade ago, I wrote a blog post about the same in an no-formula approach. Ten years has past and Excel has evolved so much. With Excel 365, the same task can be performed in an efficient and effective manner … Continue reading
Quick #Excel Tip – Display only the range you want to show on a worksheet…
by hiding all unwanted columns and rows Did you know there are 16384 columns and 1048576 rows on a worksheet? We have the flexibility to navigate any cells within this large range! Nevertheless, this is NOT what we want some … Continue reading
VLOOKUP across tables is so easy in #Excel365
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 … Continue reading
Dependent Dropdown menu using OFFSET in #Excel
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 … Continue reading
Posted in Formula
Tagged COUNTIF, Data Validation, Dependent Dropdown, excel, formulas, MATCH, OFFSET, UNIQUE
Leave a comment
Contents being stolen…
As an Excel blogger, I feel happy when my blog posts got shared, reposted, quoted, referenced etc in a proper manner on any platform. As most creators expect, a proper manner includes sharing via proper sharing functions, such as Share … Continue reading
Posted in General
Leave a comment
Use case of TEXTSPLIT in #Excel 365
We talked about the new function – TEXTSPLIT in the previous post. Let’s share a use case of myself in this post. From time to time, I receive email with a long list of recipents. Occassionally, I may want to … Continue reading
Posted in Excel Tips
Leave a comment
Split text with delimiter(s) into columns or rows and even table with TEXTSPLIT in #Excel365
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 … Continue reading
Elevate Your Career Through Excel MVP Leila Gharani
One day, in a causal conversation with a colleagus, he asked me if there is any good Excel training courses recommended. Face-to-face classroom training was on top of his mind, but I asked him why not considering online training, which … Continue reading
(Kind of) Paginated report in #Excel
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 … Continue reading
Quick #Excel 365 Tip – Combine values from a range of cells into comma separated text
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 … Continue reading
Unpivot Columns with #Excel #PowerQuery
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 … Continue reading
Pad a column to fixed length with #Excel formula
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 … Continue reading
Got bitten by an undefined data column in #PowerQuery
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! 😅 This query has been working well … Continue reading
Add Index Column in #Excel using #PowerQuery
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: This can be demonstrated in a 1-minute … Continue reading
Add Column From Examples with #Excel Power Query
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 … Continue reading
Add conditional columns with #Excel Power Query
Situation: We are given a simple table on the left. We need to add three additional columns based on the prefix of the StoreID, and the sales values. The table on the right is the expected outcome. And here’s the … Continue reading
Simple Basket Analysis – Formula approach with #Excel 365
The challenge – How many transactions with just Brand A and B together? In my previous post / video, I solved this problem with Power Query. If you are using #Excel 365, did you know you can solve this problem … Continue reading
Posted in Excel 365, Excel in Action, Formula
Tagged ARRAYTOTEXT, Filter, Sort, UNIQUE
Leave a comment
wmfexcel 