[Affiliate]
Blog Stats
- 3,289,902 hits
-
Join 3,246 other subscribers
- Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?
-
Recent Posts
Top Posts & Pages
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- #Excel Add current selection to filter. What it does?
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- Limitation (or bug?) with Filter by Color
- About
- Show number in thousand (k) or in million (M) by using custom format
- Dropdown calendar in Excel
- Contact
- Create Sequential Numbers in #Excel Easily
- SUM vs. SUBTOTAL
My YouTube Channel
My Facebook page
- My Tweets
Author Archives: MF
First thing to do when switching from Excel 2003 to 2007/2010
is to know where are the old menus and toolbar icons!!! I guess there are still a lot of of Excel 2003 users in Hong Kong, mainly in workplaces. No idea if it is also common in other cities. There are … Continue reading
Perform VLOOKUP with 2 lookup values
4 different ways to perform LOOKUP with 2 lookup values We know that VLOOKUP is very useful. At the same time, we know that VLOOKUP has its limitations. E.g. VLOOKUP only looks from left to right; VLOOKUP only handle one … Continue reading
Format part of a formula result… A workaround (non-VBA) solution
How to format part of a formula result? In a normal text string, we may format part of the text to highlight a specific information. For example, if we input the text string “Today is Thursday, 01/05/2014” in A1, we … Continue reading
3D VLOOKUP – Perform VLOOKUP from more than one table
Pls have your 3D glasses ready… only if you are going to watch a 3D movie. You don’t need it for a 3D VLOOKUP. We talked about how to do a 2D VLOOKUP by using MATCH together with VLOOKUP. With that … Continue reading
Time Conversion (2)
How to convert 24hr input as 4-digit number into TIME in Excel? i.e. turn 500 into 05:00 correctly in Excel. Answer: =TEXT(Your 4-digit number,”00\:00″)+0 ‘Format the result as TIME
Convert an 8-digit number into Excel-recognizable Date
Have you ever encountered a situation that date is input as 8-digit number instead of the correct date value in Excel? You cannot perform any proper date related calculation / functions to the 8-digit number in Excel because it is … Continue reading
Calculate number of a specific day between two dates
Answer: =SUMPRODUCT(–(TEXT(ROW(INDIRECT(B1&”:”&B2)),”DDDD”)=”Sunday”)) ‘where B1 is start date; B2 is end date. Excuse me? What it says?
SUM across different worksheets (aka 3D SUM)
Did you ever make a formula like the one below? After reading this post, I hope you will never make a formula in this way again.
How to get rid of the error message?
Did you know… there are 7 (+1 if you consider #### an error) types of error message in Excel? In my experience, most users do not care about the meaning of the errors. They just want them “disappear” on their … Continue reading
Data ==> Subtotal
How to insert thousands of rows for each change in item in a column? And then give a subtotal of each item? Long long time ago (>10years) when I was only an Excel newbie, a friend asked for help. She … Continue reading
SUM vs. SUBTOTAL
How to SUM a range but ignores SUM result in between?, i.e. How to get a Grand Total with SubTotals in between? How to SUM but ignores hidden cells or filtered cells? SUM could be the most popular function used in … Continue reading
SUM ignoring errors in the range
Different ways in getting sum of a range with “Error” Thanks to the comments made by a friend, I would like to share a few workarounds to ignore errors with SUM. 1) Use a helper column This would be an … Continue reading
Excel Basic – Sum vs. “+”
+ – x / would be the four basic operators you learned in Excel basic. When you learned to use SUM instead of +, did you ask yourself or your teacher why? 1) SUM is much easier to input and … Continue reading
Paint the Lily
Well, this is not an Excel post. This is something about Visual Presentation at a glance, so I think it’s sort of related to Excel. 🙂 One day, I visited a Japanese Fast Food Restaurant and saw the above sticker … Continue reading
Dynamic Dependent Dropdown by Data Validation
So many D. 😀 This is a continuation of the previous post: Not all teachers are eligible to all classes. e.g. only Iris and David are eligible to deliver Class F, would it be feasible to have only Iris and … Continue reading
Dynamic Dropdown – Least hours assigned always on the top
How to use Data Validation to show a dynamic drop-down list to facilitate job allocation problem? I learned about using Data Validation long time ago. It’s quite handy and useful when you want to restrict user to enter items only … Continue reading
Arrange multiple Windows of the same Workbook!
How to view different worksheets of the same workbook side by side? It’s quite normal to have two workbooks opened; and more than common to view the two workbooks side by side:
How to navigate cell in “Z” direction?? Tab Tab Tab Enter
Have you ever thought of how nice would it be if you can navigate cell in a “Z” direction?
Input special characters in Excel by pressing Alt + Numpad
Perhaps you know that you can input some special characters/symbols by pressing Alt + Numpad together. If you google “ALT Numpad”, you will probably find many extensive lists of special characters input in this way. Well, what to do with … Continue reading
wmfexcel 