Category Archives: Excel Tips

Extract 6 digits from a string and convert it into date

Got a question about how to extract the date of birthday (DOB) portion from an ID number like 63102400965, where the first 6 digits represent the DOB, so that we could use the date for other calculation.  In this example the DOB … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Copy customized Ribbon and QAT to another PC

Customized Ribbon and QAT is very common.  I believe all serious Excel users will have their own customized Ribbon and QAT according to their own working habits. I rarely share my customization with others and I am not going to … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Page Right and Page Left… why no such keys?

As an Excel user, have you ever wondered why there are Page Up and Page Down keys on keyboard, but not Page Right and Page Left keys?  I do. You may probably know that you may navigate to the right … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | 2 Comments

Reference to a cell without source number format

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

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

[April fool] – Invisible Gridlines?

Well… if you get bored in work on April fool, if you want to have a little fun with your colleague who is busying with his/her Excel files, here’s a minor trick for you to play with… Cautions: Take your … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

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

Rate this:

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

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

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

Rate this:

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

Rate this:

Posted in Excel Tips | Tagged , | 11 Comments

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

Rate this:

Posted in Excel Basic, Excel Tips | Tagged | Leave a comment

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

Rate this:

Posted in Excel Tips, Formula | Tagged , | Leave a comment

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

Rate this:

Posted in Excel Tips, Formula | Tagged , | 4 Comments

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

Rate this:

Posted in Excel Tips | Tagged | 5 Comments

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

Rate this:

Posted in Excel Tips | Tagged , , | 4 Comments

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

Rate this:

Posted in Excel Tips | Tagged | 2 Comments

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

Rate this:

Posted in Excel Tips | Tagged , | 8 Comments

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.  😛

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

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

Rate this:

Posted in Excel Tips, Formula | Tagged , , , | 4 Comments

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

Rate this:

Posted in Excel Tips, Formula | Tagged , , | Leave a comment

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

Rate this:

Posted in Excel Tips, Formula | Tagged , , , , , , , | 6 Comments