[Affiliate]
Blog Stats
- 3,288,173 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
- Show number in thousand (k) or in million (M) by using custom format
- Sequential number for visible rows only
- =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 "*"
- Calculate number of a specific day between two dates
My YouTube Channel
My Facebook page
- My Tweets
Category Archives: Excel Tips
Lost in colors
Which one of the following charts use better colors? It is not surprising if you prefer the one on the left as the colors used are of high contrast. However, will you change your mind after you read the same … Continue reading
Posted in Excel Tips, General
Leave a comment
Input fraction number in Excel
This title sounds a bit silly, but I do believe many users must have experienced frustration when they tried to input a fraction number as simple as 1/2 in Excel. When we input something like x/y, Excel assumes that we are … Continue reading
Convert .xls to .xlsx in a few clicks
Let’s bring the Excel file from stone age back to modern world. A few years ago, my company switched from Office 2003 to Office 2007. I was one of the very few who felt excited about the change. On the … Continue reading
Posted in Excel Basic, Excel Tips
Leave a comment
Limitation (or bug?) with Filter by Color
Did you know that you can filter by color since Excel 2007? Yes, that’s almost 10 years. I wont’ say it is a new feature notwithstanding the fact that many people are not aware of it yet. 😛 Filter by … Continue reading
Sort by row (i.e. from left to right)
Sorting is easy, when the orientation of data is vertical. Although we are expecting data in a vertical layout, it is not uncommon that we still encounter data in a horizontal layout in real world. Don’t you? With data in a row, … Continue reading
Display % of column and % of row in a dynamic way
This post is intended to give you an introduction of CHOOSE function and Option Button (Form Control). Here’s the situation: We want to display the % of total in a 2-D table. Nevertheless the % can be of column total … Continue reading
How to limit time interval input in a single cell?
Got the following question from a reader: Under data validation, is it possible for me to restrict the time duration (in a cell) to be 30mins or less? Example: 9.00 – 9.30 (accepted) 10.15 – 10.50 (rejected) Obviously, the answer … Continue reading
A simple trick to go to a specific picture quickly
Here’s the situation: You have many pictures on a spreadsheet, how can you go to a specific picture quickly? Selection and Visibility Pane is on the top of my mind…… but as you see from the above screenshot, you won’t succeed … Continue reading
Nested IF vs. VLOOKUP – Which one to use?
A well-drawn flowchart or a well-organized table helps you write a successful formula effectively Continue reading
Get rid of the GETPIVOTDATA without disabling it
If you do Pivot Table, I believe you should have experienced the following too. GETPIVOTDATA is good. But sometimes we just want a simple cell reference. We may disable the GETPIVOTDATA easily by going to Pivot Table Option –> Uncheck … Continue reading
Be careful when using Data Table
Do not reference a label (be it on row or column) back to the calculation thread used when setting up Data Table. Continue reading
Mouse Tips – Move cell to other sheets
If you have tried to move a cell from one sheet to another sheet by dragging it to the Sheet tab, you would be frustrated it just keeps you (going) DOWN. 😛
A trick to SUM visible columns only (without VBA)
We know that SUBTOTAL allows us to perform some basics functions like SUM, COUNT, AVERAGE, etc. that apply to visible rows only. However, there is no similar function for visible columns only. If we need to SUM visible columns only, we will need … Continue reading
Put a dynamic hint for showing or hiding rows or columns hidden by Data Group
This is about using SUBTOTAL and CELL function to detect whether a row and a column is hidden respectively. When rows are hidden by Data–>Group, you will see a +/- button on the leftmost of spreadsheet to remind you that … Continue reading
SUM Time in the end of text strings
Did you encounter a task like this? I did. Actually it is not as difficult as you may expect. It could be done in just a minute IF the data is not too bad, like the one shown above. I … Continue reading
Posted in Excel Tips, Formula
Tagged Array formula, Find and Replace, RIGHT, Sample File, SUBSTITUTE, SUMPRODUCT, Time, wildcard
6 Comments
Ctrl+A can be more convenient than you think of…
If you think Ctrl+A does merely Select All, then you are likely not using it frequently. Let’s me take you through how to boost your efficiency working with Excel with Ctrl+A under different situations.
=SUM(‘???’!C3) Is it a valid formula?? No. It is magical indeed!
This is about using wildcards in referencing cells on other worksheets. If you know the basic rule about naming a worksheet, you should know that we cannot use ? or * in any part of a worksheet’s name. So there … Continue reading
How unorganized data could drive you crazy!
This is about how to clean up data for a simple VLOOKUP task. To be more specific, how to separate data delimited by line break in a cell, as you see in the following screen shot. The one who input … Continue reading
When you forget to hold Ctrl key for inputting same value to a range…
… Ctrl+D or Ctrl+R is ready to help 🙂 Ctrl+Enter is one of my favorite shortcuts. It helps me to input a value or a formula to a range simultaneously. As usual, Ctrl+Enter won’t let me down. But many times, … Continue reading
Date Filter is good, but still with limitation
This is about how to filter a particular date, say 1st of January regardless year, from a list of dates. As a matter of fact, not many people are aware of the Date Filter feature in Excel. This is true at … Continue reading
wmfexcel 