Excel Humor #5 – Are you relying on Excel too much?

Are you an Excel Dependency? Do you use Excel even for simple maths? ¬†Sometimes I do. ūüôā

Excel Humor (5) - Excel Dependency.png

 

Posted in General | Tagged | Leave a comment

Text manipulation with Excel functions

We talked about RIGHT, LEFT, MID, UPPER, LOWER, PROPER, FIND, SEARCH, REPLACE and SUBSTITUTE in the past few weeks. ¬†Now it’s time to put every thing together to solve a problem. ¬†This is actually the most amazing part of using Excel functions: A single function may not solve your problem. ¬†A combination of functions could¬†do a job that is out of your imagination. ¬†ūüôā

Here’s the task:Excel Tips - Text Manipulation

Continue reading

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

Case-insensitive SUBSTITUTE???…

We talked about REPLACE and SUBSTITUTE last week.  At the final note, I said that SUBSTITUTE is case-sensitive.  If we need to perform a case-insensitive SUBSTITUTE, it is indeed not an easy job unless we are talking a single letter only.

Using the same example we used last time, if we want to substitute “E” with “_” regarding the case of “E”, i.e. all occurrences of “E” and “e”, ¬†it is total doable without too much effort. ¬†What we need to do is simply use SUBSTITUTE functions twice, in a nested formula.

=SUBSTITUTE(SUBSTITUTE(A1,"e","_"),"E","_")

Excel Tips - SUBSTITUTE case-insensitive1.png

The inner¬†SUBSTITUTE changes “e” to “_”; the outer¬†SUBSTITUTE changes “E” to “_”. ¬†As a result, all “e” and “E” will be replaced by “_”. ¬†As simple as that! ūüôā

However, if we want to replace a “text string” that contains more than one letter, and hence could come in various case combinations,¬†then it could be a real headache. Continue reading

Posted in Formula | Tagged , , | 6 Comments

REPLACE vs. SUBSTITUTE in Excel

Another pair of functions that has very similar meaning literally. ¬† Although I am not able to tell you the differences between “replace” and “substitute” in English, I can show you the differences of the REPLACE and SUBSTITUTE in Excel. ¬†Are you ready?

Let’s start with¬†the function. ¬†Both functions, as you may guess, look into a text string, then based on your instruction, replace part(s) of a string with any text(s)/character(s) specified.

The key difference between REPLACE and SUBSTITUTE

REPLACE requires you to specify a staring position and length of character you want to replace with something

SUBSTITUTE looks for specific texts/characters and then substitute it¬†with something. Sound like FIND? ¬†If you don’t know FIND, take a look at FIND vs. SEARCH.¬† Continue reading

Posted in Formula | Tagged , | Leave a comment

FIND vs. SEARCH

In many situations, we use either FIND or SEARCH together with LEFT, RIGHT, MID to achieve what we need.  A typical example is to extract the first name and the last name from a full name where a coma acts as the separator, like the example below:

Excel Tips - Find Search 1.png

To get the Last Name:

=MID(A2,FIND(",",A2)+2,99) 
'Why 99 in the last argument? You may want to read the previous two post.

To get the First Name:

=LEFT(A2,FIND(",",A2)-1)

So I think it makes sense to talk about FIND and SEARCH. Continue reading

Posted in Formula | Tagged , | Leave a comment

UPPER, lower, Proper

I am wondering why these functions are not available in Word, but Excel?

Don’t make me wrong. ¬†I like these functions. ¬†Nevertheless I am not a big fan (nor a small fan) of using Excel as word processor. ¬†Whenever possible, use Excel to handle data; Word to handle words. ¬†That are¬†what¬†the softwares designed for!?

But the fact is, many people use Excel in their own ways and can somehow create forms and documents with Excel even better than they do the same in Word.  Agree?   I would say: Excel is so great that it handles not just numbers but texts equally effective.

Well, too much personal talks… let’s go back to the subject: UPPER, lower, Proper. Continue reading

Posted in Formula | Tagged , , | 8 Comments

RIGHT. I LEFT. In the MID of…

Obviously this post is about the most popular text related functions in Excel. ¬†Meanwhile, it is also a message from me…

Yes, you are RIGH! I LEFT my ex-company and have landed on a new job which is much more busier… and I am in the MIDdle of probation. ¬†Although I am trying hard to update this blog every weekend, I am not sure if that can be achieved. ¬†Don’t be surprised if you don’t see new post every week as it used to be. ¬†On the positive side, I’ve got more chances working with data and Excel in my new job. ¬†That would absolutely give me more ideas on writing. ¬†ūüôā

Let’s get back to the functions now.

Attention! LEFT! RIGHT! LEFT! RIGHT!… and MID!¬† Continue reading

Posted in Formula | Tagged , , | Leave a comment

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…¬†Excel Tips - Custom List.gif

But did you know that, Excel may also give you a list of your favorite fruits?Excel Tips - Custom List1.gif Continue reading

Posted in Excel Tips | Tagged | 3 Comments

Open sesame with Alt+‚Üď

Alt+‚Üď is absolutely one of my frequently used shortcuts when using Excel.

What it does? ¬†“Open sesame” under four common circumstances. ūüôā

When you are at the bottom of a column

Excel Tips - Alt Down 1¬†¬†Alt+‚Üď displays the list of items on the column. ¬†

So good for inputting value without retyping –> Minimize chance of typo

When you are on the top row of a table with auto filter on  

Continue reading

Posted in Shortcut | Tagged | Leave a comment

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 is Oct 24, 1963.

To solve this question, the first thing on my mind is the formula approach I described in the post Convert an 8-digit number into Excel-recognizable Date. ¬†Soon enough, I realized that I am dealing with 6 digits only, where we need to fix another issue of the year… where 63 seems logical to be 1963; and 10 seems logical to be 2010. ¬†It means I need a longer formula to entertain the year portion, like if first two digits is less than or equal to 29, then it should be¬†the 21st century…

Wait, why do I need to reinvent the wheel? ¬†Let’s do it a non-formula approach¬†– Text to Columns. Continue reading

Posted in Excel Tips | Tagged , | Leave a comment

Display single letter Day of week in Excel

Is it achievable?  Not by formatting but formula.

Excel Tips - Single Letter Day of Week 1 Continue reading

Posted in Format, Formula | Tagged , , , | Leave a comment

Number Stored as Text…

It is something basic and commonly seen.  Nonetheless,  it is really important to understand the different behaviors between a number, and a number stored as text, especially if you want to move forward to write effective formula.  This post is intended to explain

  • What it is?
  • How to know if a number is a text or a number?
  • Why do we have¬†number stored as text?
  • Why does it matter?
  • Ways to fix it

Continue reading

Posted in Excel Basic, Formula | Tagged , , | 5 Comments

Copy customized Ribbon and QAT to another PC

Excel Tips - Copy Ribbon and QAT

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 share here, as I just said, it’s really up to your working habits. ¬†You won’t find my customized QAT valuable to you at all… I guess :p

Nevertheless, you should need to know how to copy your customized Ribbon and QAT to another PC, especially when you switch job which I recently did. ¬†ūüôā Continue reading

Posted in Excel Tips | Tagged , | Leave a comment

Excel Humor #4 – Excel is the perfect tool for…

Excel Humor (4) - Perfect Tool for

Like and/or share this post if you agree it. ¬†ūüôā

Posted in General | Tagged | 1 Comment

Page Right and Page Left… why no such keys?

Excel Tips - Page Right and Page Left.PNG

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 of a¬†“large” table by holding Ctrl key with Right Arrow. ¬†However, this will lead you to the right-most of your table. ¬†In some occasions, it just goes too far… and that’s the time you want a Page Right key. Continue reading

Posted in Excel Tips | Tagged , | 2 Comments

Boost Excel Skill with Pivot Table

I think I must have missed the email from Mynda Treacy regarding the Xtreme PivotTables Course on her site: myOnlineTrainingHub, otherwise you should have read this post earlier.

I use Pivot Table a lot and consider it a must-have skill for Excel, although I have not written many posts on Pivot Table in this blog.   Pivot Table is something better learnt from basic and in an organised way, should you want to truly master it.  That’s why I am recommending the Xtreme PivotTables Course.

Take a look at the course outline.  Isn’t it comprehensive?

Screen Shot 2016-04-19 at 10.51.58 am

You may go through each topic at your own pace as the course consists of video tutorials delivered in bite size chunks (average is 3 minutes long).

Moreover, there is a 20% discount for the Xtreme PivotTables Course.  The coupon code for the discount is: PIVOTWEB.  The discount ends Thursday, April 21 at 8pm Pacific Time, i.e. very soon. :p

Oh, and in the spirit of full disclosure, you need to know that I do make a small commission if you decide to join the Xtreme PivotTables Course.  I don‚Äôt just recommend anything and everything; I believe the Xtreme PivotTables Course has the quality and value that make me happy to recommend it without hesitation.  Even though you decide not to join the course at the end, you may still find the contents on myOnlineTrainingHub a valuable source of Excel skills and knowledge.

Posted in General | Tagged | 1 Comment

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 Date, then the format of A2 will turn into Date automatically once you make a direct reference to it (by inputting =A1). ¬†As usual, a picture tells thousand words; an animated picture tell even more: ūüôā

Excel Tips - Trick of referencing a cell without its format.gif(Please pay attention to the “Number Format” on the top…)

But did you know that you may make the reference without using source number format?

I couldn’t find a way to turn that option off. ¬†Nevertheless, a formula trick would do.

Excel Tips - Trick of referencing a cell without its format1.gif

So what’s the formula trick?¬† Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Three different ways to do case-sensitive lookup

If you do VLOOKUP, you probably know that one of the limitations of VLOOKUP is case-insensitive.  It means case is not a consideration in the lookup process, where PETER is essentially the same as peter. Hence VLOOKUP will consider that they are the same and return the first matched.  See below demonstration:

Excel Tips - case sensitive lookup.gif

As¬†you see from above, VLOOKUP always returns “FN” as a result for different “peter”s. Nevertheless, the formula in E3 to E6 manage to perform case-sensitive lookup correctly. ¬†Then the question is HOW?¬† Continue reading

Posted in Formula | 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 own risk… ūüôā

Excel Tips - Gridline color.gif

Well, you may think that either the Fill Color or Border Color is¬†set to “White” to the whole worksheet, which is not uncommon indeed.

 

Excel Tips - Gridline color1

But this is not the case… ūüė¶¬† Continue reading

Posted in Excel Tips | Tagged , | Leave a comment

Lost in colors

Which one of the following charts use better colors?

Excel Tip - Lost in Color 1.PNG

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 charts printed in black and white? Continue reading

Posted in Excel Tips, General | Leave a comment