Category Archives: Excel Tips

Move a chart object with arrow keys

If you do chart in Excel, you may have encountered this: You clicked on a chart object, tried to move it just a little bit to the right by pressing Right Arrow key a few times… Instead of the chart being moved … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | 2 Comments

Excel Humor #7 – Interactive Report

I hope you will never run into a situation like this… 🙂

Rate this:

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

Insert “tick in a box” in Excel

Recently, I’ve been collecting Excel files from colleagues regarding implementation status of some activities in the region.  Part of the template looks like the screenshot below: Quite simple and straight forward, right? I expected I would get something like the following in … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 4 Comments

Move cells with data to the top

Did you spend too much time to move cells so that there are no blank cells in each column? Again, a picture tells thousand words.  Here’s the task… Would you do it this way? Or this way? What did I do … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | 1 Comment

Insert or move a field into Pivot table without drag and drop

If you do Pivot Table, the following won’t be a stranger to you as you must do a lot drag and drop to pivot. But did you know that you may insert a field name and / or  swap the … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 2 Comments

Custom List is so good

  Did you know… you can create a list of Month and Day of Week by drag and drop?  You probably knew it on the first week of your Excel journey…  But did you know that, Excel may also give … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 3 Comments

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 , , | 6 Comments