[Affiliate]
Blog Stats
- 3,288,183 hits
-
Join 3,246 other subscribers
- Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?
-
Recent Posts
Top Posts & Pages
- Perform VLOOKUP with 2 lookup values
- Sequential number for visible rows only
- Show number in thousand (k) or in million (M) by using custom format
- =SUM('???'!C3) Is it a valid formula?? No. It is magical indeed!
- Dropdown calendar in Excel
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- A trick to SUM visible columns only (without VBA)
- Copy data from strictly-protected sheet
- Advanced vlookup - wildcard characters "?" and "*"
- #Excel Add current selection to filter. What it does?
My YouTube Channel
My Facebook page
- My Tweets
Category Archives: Excel Tips
Advantages of having date input correctly in Excel
In previous posts, what do we need to know about inputting date in Excel was discussed. Suggested solutions for fixing trouble dates were also given. But WHY having date input correctly in Excel is so important? Why should we care? Answer: It’s … Continue reading
Fixing trouble dates
Different ways to convert dates stored as text or other unrecognized date formats to real dates in Excel. From time to time, we work with dates in Excel files. Well, if dates were input correctly as serial number, everyone is happy. … Continue reading
Clear or Delete? A hassle-free way to clear everything in a cell
Do you have a habit of right-click a cell and Delete it rather than pressing the “Delete” button on keyboard? If you do have this habit, I guess you possibly are frustrated by the following experience before:
Copy and Paste table while keeping column widths
A simple trick (click) can save you lots of time. Below is a commonly seen scenario in my work life: A user wanted to copy a table to a new worksheet/workbook, what he/she normally did was CRTL C then CTRL V. The … Continue reading
Move cells by Right-click Drag and Drop
How to swap adjacent cell quickly? How to insert new items in the middle of a table? How to move cells more efficiently?… by using mouse… Last week, I learned from Debra Dalgleish to convert formula into value by using mouse. What … Continue reading
Allow time input at 15-min intervals only – Data Validation
How to limit user to input time at 15-min intervals, i.e. 00:15, 00:30, 00:45, 01:00, etc.? We talked about how to limit user to input Time in the previous post. Let’s go one step further with Custom Data Validation, where you … Continue reading
Data Validation – TIME
How to limit user to input TIME only? Sometime, actually too many times, users may not input cell content in a way you want them to. As a result, the formula you set for calculation may go wrong; and you … Continue reading
Fill Series
Auto Fill is a very handy feature in Excel. I believe many users use it every day. A simple example is to fill a list of sequential numbers, say from 1 to 18. What you need is to input 1 … Continue reading
Remove gridline
By default, gridline is not printed on paper. Nevertheless, I would usually remove gridline when I submit my report, or paste part of a spreadsheet into PowerPoint or Word. Why? Simply for better look and feel. See the screenshots below to … Continue reading
Remove Line Break instantly
Two different ways to replace Line Break with a space QUICKLY If you are trying to build a table of data which is analysis-friendly, DO NOT use line break in your fields and labels. Otherwise, you will create unnecessary steps … Continue reading
Input line break in a cell – ALT ENTER
In Word, it is so simple to input a line break by pressing Enter. However in Excel, pressing Enter will take you to the cell below. To input line break in Excel requires just a simple key combination. What you … 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
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
wmfexcel 