[Affiliate]
Blog Stats
- 3,287,212 hits
-
Join 3,246 other subscribers
- Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?
-
Recent Posts
Top Posts & Pages
- Copy data from strictly-protected sheet
- When unhide row doesn't work...
- Dropdown calendar in Excel
- Bug with copy and paste in Excel Table
- Convert an 8-digit number into Excel-recognizable Date
- Simple trick to look (hack) into hidden sheet
- Sequential number for visible rows only
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- How to lookup an image in #Excel
- Limitation (or bug?) with Filter by Color
My YouTube Channel
My Facebook page
- My Tweets
Author Archives: MF
The INDIRECT trick of using Structured Reference in Conditional Formatting
When setting up conditions for Conditional Formatting in Excel, have you ever tried to set a formula that refers to a Excel Table, but failed?
First of all, why we want to refer to Excel Table in Conditional Formatting? There is one simple answer: To accept new data automatically.
To use Structure Reference in Conditional Formatting, we need an INDIRECT approach which is discussed here. Continue reading
Highlight weekends and holidays using Conditional Formatting in #Excel Part 2
Conditional Formatting with formula could be tricky (or difficult), especially when the data layout is bad because you have to very clear and careful on the “applied to” range and the absolute/relative references set in the formula. Otherwise, it won’t work and can be quite confusing, if not frustrating. 😛
In this post, I will talk about a case that you will see how empty rows in a data set would complicate the process in setting up conditional formatting. And then I will show three different approaches to tackle the issues. Hope you find it helpful. Continue reading
Highlight weekends and holidays using Conditional Formatting in #Excel
In this post, I will show you step by step how to set up conditional formatting to highlight weekends and public holidays with Excel. The key is to set the formula right. Having a proper layout will make the whole thing more easier, which will be discussed in the next post. Continue reading
[Share] Introduction of #Power Query M code by ExcelIsfun
Happy New Year! Wish you all an Excel-lent year of 2019! As an Excel nerd, I’d like to spend my holidays on learning Excel… haha… I know I am abnormal. ;p My recent love of Excel is Power Query (M) … Continue reading
Highlighting Top X values with Icon Set in #Excel
This post is a continuation of the previous one – Highlighting Top X values with Conditional Formatting in #Excel So I will go straight to the point. For background information, please read the previous post. 🙂 To insert Icon Set Select … Continue reading
Highlighting Top X values with Conditional Formatting in #Excel
Well… think this topic is too simple? How about doing this with a dropdown menu to make the Top X a dynamic one? with Icon Set? You may download a Sample File to follow along. Let’s start with the basics … Continue reading
Posted in Excel Tips
Tagged Conditional Formatting, Custom Format, Data Validation, LARGE, \
Leave a comment
Customize Alt 1 to 4 to become the favorite #Excel shortcuts of your own
When talking about keyboard shortcuts, there is always discussion on what’s the most time-saving shortcuts, what are the top X shortcuts you should learn, what is the most frequently used shortcuts, etc… Just to name a few, Ctrl+S to save, … Continue reading
Using Skip blanks in Paste Special
What the Skip blanks does? Have you ever had this question? From my experience, 99.9% users (I work with) have no idea what it is or even are not aware of that option in Paste Special. Are you one of … Continue reading
The making of the bee…
Did you know… the bee you see on top of this page is drawn with Excel using shapes? Yes… these are the shapes I used to draw the bee. What I did was to put them together… Watch this:
Dynamic Shrinking Dropdown with Dynamic Arrays in #Excel 365
The recent HOT topics about Excel should be the new Dynamic Arrays! After watching all the amazing demonstrations about the new Dynamic Arrays, I’ve decided to sign up to the Office Insider (Fast) programme (for Office 365 subscriber only). … Continue reading
Posted in Excel Tips
Tagged Data Validation, Dynamic Arrays, Dynamic Dropdown, Excel 365, MATCH
Leave a comment
Pinned Folder for #Excel 2016 – A workaround
Dear Excel 2016, you have made so many enhancements that help me a lot in work… but why you took away my favorite Pinned Folder by giving me a half page of white space? My workplace just got upgrade to … Continue reading
Posted in Excel Tips
7 Comments
Prepare yourself to be an #Excel Dashboard Pro…
Have you ever had an Excel moment like this? If you’ve ever felt like: “My Excel reports take way too long to update each month/week” “I spend hours collating and cleaning data, updating formulas and charts and then no one … Continue reading
Trick or Treat 2018
♪♫♬ This is Halloween, this is Halloween ♪♫♬ ♪♫♬ Halloween! Halloween! Halloween! Halloween! ♪♫♬ Please join me to welcome our special guest – Jack Skellington, appears on wmfexcel’s Excel sheet. ♪♫♬Halloween! Halloween! Halloween! Halloween! In this town we call home Everyone … Continue reading
Exciting Dynamic Array functions in #Excel 365
Have you heard of the new Dynamic Array functions recently available in Excel 365 (currently for Insider Fast users only)? If you are an advanced Excel user, you will be impressed by these new functions. They are simply awesome, super … Continue reading
Learn something new from something that I think I knew well…
Today is a public holiday in Hong Kong. A perfect break in the middle of a busy week. What did I do on a public holiday? Excel, Excel and Excel. 🙂 I watched a few videos from my favourite YouTube … Continue reading
Calculating CAGR with Goal Seek in #Excel
when the starting point is a negative number… Calculating CAGR is not difficult, all we need is the starting value, ending value and the number of periods. Then we use the formula: CAGR = (Ending Value / Beginning Value ) … Continue reading
Excel Humor #7 just happened to me…
Two years ago… I posted this: which turned out a real case to me last week… I printed 99 pages of report out of one worksheet by selecting 99 items on a slicer one by one. @_@
Adding worksheet background in #Excel
How to add worksheet background in #Excel? If there is a secret recipe for an interesting Excel training, it would probably be starting with something interesting… 🙂 So in a recent in-house training, I’ve prepared a special Sheet1 in the … Continue reading
Posted in Excel Tips
4 Comments
Three days in paradise…
Note: This is not an Excel post. I am back from holiday, physically 🙂 I went to a very remote village called YuBeng in Yunnan, China. It’s a village 3200M above sea level. To get to there wasn’t easy. Not … Continue reading
Posted in General
Leave a comment
[Share] – Excel Hash
Have you heard about the Excel Hash competition? It is a contest initiated by Oz du Soleil at Excel On Fire. Six well-known Excel MVPs, including Oz himself, show you what they can do with four designated Excel features/functions, and … Continue reading
Posted in General
Leave a comment
wmfexcel 