Category Archives: Excel Tips

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

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.

Rate this:

Posted in Excel Tips | Tagged | 6 Comments

=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

Rate this:

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

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

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

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

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

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

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Where are Filter drop-down buttons?

Have you ever encountered a filtered range without Filter drop-down buttons available on the top?  No idea what I am talking about?  Take a look at the following screenshot. Without the Filter buttons on the top, I tried to show hidden … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 12 Comments

Interesting bug – Filter with SUBTOTAL

In the process of writing my previous post, I found something wrong with Filter…  when Column A contains formula starts with SUBTOTAL, Auto Filter refuses to take the last row.

Rate this:

Posted in Excel Tips | Tagged , | 3 Comments

Sequential number for visible rows only

How to display a column of sequential number to visible rows only? Got this question from a friend.  Her task is simple as shown below: I guess many people wanted to achieve the same thing but have no idea at all.  Are … Continue reading

Rate this:

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

Copy data from strictly-protected sheet

How to copy data from a range where you cannot select, due to worksheet protection? Here’s the situation: You receive a worksheet with data but not insight.  Well, it happens and you get use to it already.  You plan to … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | 29 Comments