- Follow wmfexcel on WordPress.com
Top Posts & Pages
- When unhide row doesn't work...
- Perform VLOOKUP with 2 lookup values
- Dropdown calendar in Excel
- Copy data from strictly-protected sheet
- Show number in thousand (k) or in million (M) by using custom format
- Advanced vlookup - Text vs. Number
- Advanced vlookup - wildcard characters "?" and "*"
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- 2D SUMIF with two variables – one on column and one on row
- Open an embedded Excel file during a slide show in PowerPoint?
Category Archives: Excel Tips
You probably knew it… when we make a direct reference to a cell, i.e. input =A1 in A2, the cell A2 will then follow the number format of A1. Put it in other words, if the number format of A1 is … Continue reading
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
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
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
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
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
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
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
A well-drawn flowchart or a well-organized table helps you write a successful formula effectively Continue reading
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
Do not reference a label (be it on row or column) back to the calculation thread used when setting up Data Table. Continue reading
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. 😛
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
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
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
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.
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