First thing to do when switching from Excel 2003 to 2007/2010

is to know where are the old menus and toolbar icons!!!

I guess there are still a lot of of Excel 2003 users in Hong Kong, mainly in workplaces.  No idea if it is also common in other cities. There are so many reasons for not upgrading.  Not to my surprise, one of the most commonly heard reasons is the resistance to change.

As an ordinary user, I am sure you would feel more frustration than excitement on the first day you switched from Excel 2003 to 2007/2010/2013.  Before you could appreciate the benefits from (just to name a few)

  • the enhanced features in Conditional Formatting
  • the variety and functionality of new Functions
  • the introduction of Table in 2007
  • the introduction of Slicer in 2010
  • the improvement in Auto Filter
  • so on and so forth

you probably faced difficulties in basic stuff like Printing and Page Setup… 😦  Does it sound familiar?

If you just switched from Excel 2003, or are planning to do so, the first thing you should do is to spend time to get yourself familiarized with the new Interface – the Ribbon.

Let’s see where are those “File”, “Edit”, “Insert”, “Format” etc in the new Excel. (note: Excel 2002 and 2010 being used in the following screenshots).

Continue reading

Posted in Excel Basic | Tagged , | Leave a comment

Perform VLOOKUP with 2 lookup values

Excel Dashboard Course

4 different ways to perform LOOKUP with 2 lookup values

We know that VLOOKUP is very useful. At the same time, we know that VLOOKUP has its limitations. E.g. VLOOKUP only looks from left to right; VLOOKUP only handle one lookup value.  For a simple situation shown below, VLOOKUP doesn’t seem to work (directly).

Excel Tips - lookup 2 values 1
No worry. There are at least four workarounds!
Continue reading

Posted in Formula | Tagged , , , , | 38 Comments

Format part of a formula result… A workaround (non-VBA) solution

How to format part of a formula result?

In a normal text string, we may format part of the text to highlight a specific information.  For example, if we input the text string “Today is Thursday, 01/05/2014” in A1, we could  BOLD part of the text string by selecting the text string in the formula bar and then apply the format required.

Image

“Today is Thursday, 01/05/2014” is displayed in A1 as a result.

Continue reading

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

3D VLOOKUP – Perform VLOOKUP from more than one table

Pls have your 3D glasses ready… only if you are going to watch a 3D movie.  You don’t need it for a 3D VLOOKUP.

We talked about how to do a 2D VLOOKUP by using MATCH together with VLOOKUP.  With that technique, it’s not difficult to solve the problem below:

Image

However, what if we have one more variable (Market)? i.e. more than one table to look at like the example below:

Image

It is not uncommon that we have the same set of data for different markets, normally stored at different worksheets (one sheet for one market).  So how do we do the VLOOKUP in this case?

Relax, there will be at least 3 different ways to accomplish it.

  1. Using VLOOKUP, INDIRECT, MATCH
  2. Using VLOOKUP, CHOOSE, MATCH
  3. Using INDEX, MATCH

Continue reading

Posted in Formula | Tagged , , , | 3 Comments

Time Conversion (2)

How to convert 24hr input as 4-digit number into TIME in Excel? i.e. turn 500 into 05:00 correctly in Excel.

Image

Answer:

=TEXT(Your 4-digit number,”00\:00″)+0 ‘Format the result as TIME

Continue reading

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

Convert an 8-digit number into Excel-recognizable Date

Have you ever encountered a situation that date is input as 8-digit number instead of the correct date value in Excel?Image

You cannot perform any proper date related calculation / functions to the 8-digit number in Excel because it is NOT the correct way to input date in Excel.

Don’t ever think that you can simply change the cell format to Date (like DD/MM/YYYY) and make it work.  Try to turn the value 20140401 into date format, you will get ########## as the value is far beyond the Excel limit for date.

Image

No worry.  As long as all these 8-digit numbers follow the YYYYMMDD pattern, there is a way to turn it back to Excel recognizable Date with a formula.  How?

Continue reading

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

Calculate number of a specific day between two dates

Image

Answer:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"DDDD")="Sunday"))

‘where B1 is start date; B2 is end date.

Excuse me? What it says?

Continue reading

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

SUM across different worksheets (aka 3D SUM)

Did you ever make a formula like the one below?  After reading this post, I hope you will never make a formula in this way again.

Image

Continue reading

Posted in Excel Tips, Formula | Tagged | 9 Comments

How to get rid of the error message?

Image  Did you know…

there are 7 (+1 if you consider #### an error) types of error message in Excel?  In my experience, most users do not care about the meaning of the errors.  They just want them “disappear” on their worksheet because these errors do not look good.

IFERROR is a simple way to achieve this:

Continue reading

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

When unhide row doesn’t work…

This above video was made just recently.  You may read this post to understand more.  Note: Please turn on CC for English subtitles.

Hidden rows cannot be unhidden? Why?

Although today is April Fools’ Day and the question sounds like an April Fool’s question, this post is not about to fool anyone.  Just another real case to share.

For an unprotected sheet, hidden rows can be unhidden easily. This is so basic. I thought so too before I received a strange worksheet sent by a colleague. (I believe he had no idea what he did.)

In the screenshot below, rows 2:15 are hidden. It is so obvious. Isn’t it?

Image

Then I tried the normal ways to unhide the rows, but failed.

Continue reading

Posted in Excel Basic, Excel Tips | Tagged , , | 165 Comments

Data ==> Subtotal

How to insert thousands of rows for each change in item in a column?  And then give a subtotal of each item?

Image

Long long time ago (>10years) when I was only an Excel newbie, a friend asked for help.  She was desperate for the task given.  She had no clues and planned to do that manually.  You can imagine how long it would take – FOREVER.

I spent a while, maybe an hour, to record and test some Marcos for the repeating “Copy and Insert Copied Cells” actions. Finally I solved her problem in much less time than she expected.  And we were so happy the problem was solved.  The luck we had was there were always four rows for an item.  If it happened to have irregular number of rows for different items, like in our example, I am sure that I won’t able to solve her problem, at that time.

Today, I know that this kind of problem can be solved in less than a minute WITHOUT any hassle.  Thanks to Data->Subtotal

Continue reading

Posted in Excel Tips | Tagged | Leave a comment

SUM vs. SUBTOTAL

  • How to SUM a range but ignores SUM result in between?, i.e. How to get a Grand Total with SubTotals in between?

  • How to SUM but ignores hidden cells or filtered cells?

SUM could be the most popular function used in spreadsheet.  However there are situations that SUM may not be the best simple solution. For example, getting Grand Total in a column where many subtotals in between:

Excel Tips - SUM vs SUBTOTAL1

A simple SUM for the whole range may double count as it includes all the subtotals in between.

Continue reading

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

SUM ignoring errors in the range

Different ways in getting sum of a range with “Error”

Thanks to the comments made by a friend, I would like to share a few workarounds to ignore errors with SUM.

1) Use a helper column
Image

This would be an easy way if you have flexibility in adding helper column.

Continue reading

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

Excel Basic – Sum vs. “+”

+ – x / would be the four basic operators you learned in Excel basic.  When you learned to use SUM instead of +, did you ask yourself or your teacher why?

1) SUM is much easier to input and edit.

Image
Obviously =SUM(B2:B6) contains fewer characters than =C2+C3+C4+C5+C6
Not obviously enough??? Try =sum(B2:F6) by using =B1+B2+B3+B4+B5+B6+C1+C2……

Continue reading

Posted in Excel Basic, Formula | Tagged | 9 Comments

Paint the Lily

Well, this is not an Excel post.  This is something about Visual Presentation at a glance, so I think it’s sort of related to Excel.  🙂

Image

One day, I visited a Japanese Fast Food Restaurant and saw the above sticker on every table.  It means “100% No Fukushim Ingredient” (direct translation).  Just at a glance, I thought it is “100% Fukushim Ingredient”.

Continue reading

Posted in Format, General | Tagged | 1 Comment

Dynamic Dependent Dropdown by Data Validation

So many D. 😀

This is a continuation of the previous post:

Not all teachers are eligible to all classes.  e.g. only Iris and David are eligible to deliver Class F, would it be feasible to have only Iris and David on the dropdown list, in the ascending order of hours assigned?

Yes it is.

You may download a Sample File  to follow along.

Continue reading

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

Dynamic Dropdown – Least hours assigned always on the top

How to use Data Validation to show a dynamic drop-down list to facilitate job allocation problem?

I learned about using Data Validation long time ago.  It’s quite handy and useful when you want to restrict user to enter items only according to a pre-defined list.  As you can see from the example below, where you want to avoid Peter to be assigned as Peter is not a teacher on the list.

Image

Would it be nice IF the dropdown list always give you the teacher with the least hours assigned on the top; while the one with the highest hours assigned on the bottom of the list??

You may download a Sample File  to follow along.

Continue reading

Posted in Excel Tips | Tagged | 2 Comments

Arrange multiple Windows of the same Workbook!

How to view different worksheets of the same workbook side by side?

It’s quite normal to have two workbooks opened; and more than common to view the two workbooks side by side:

Image

Continue reading

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

How to navigate cell in “Z” direction?? Tab Tab Tab Enter

Have you ever thought of how nice would it be if you can navigate cell in a “Z” direction?

Image

Continue reading

Posted in Excel Basic | Tagged | 4 Comments

Input special characters in Excel by pressing Alt + Numpad

Perhaps you know that you can input some special characters/symbols by pressing Alt + Numpad together.  If you google “ALT Numpad”, you will probably find many extensive lists of special characters input in this way.

Well, what to do with it then?  Out of the hundreds special characters, how many of them are actually frequently used in daily work?

Depends on what your job is, you many find many special characters applicable and useful to you.  However there are just a few to me, as shown below:

Image(these codes are for Windows PC)

Continue reading

Posted in Excel Tips | Tagged | 2 Comments