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.

Excel Tips - Move field by direct input.gif

But did you know that you may insert a field name and / or  swap the position of field name in the layout of a Pivot table by directly input?  See below:   Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Writing a long formula in steps

Writing a long formula is not easy, even for an advanced Excel user.  What I mean long is  a formula with many nested FUNCTIONS within a single formula.  The difficulty I am talking about is not related to whether you understand the functions.  It is more about the concentration and carefulness required for writing the formula.  Why is that?

Excel Tip - Writing long formula.png

If you have encountered the above message frequently, you know why!🙂

You need to make sure you have corresponding pair of brackets () for each function; not to mention the comma (,) required for different arguments involved.  For instance, if you have 7 FUNCTIONS in your formula, you should have 7 pairs of brackets plus N commas spread around your formula.

The frustration usually comes from an attempt to write the long formula in one go.  When you are having the above error message popped up non-stop, maybe it’s time for you to break your long formula into bit-size formulas, with helper columns.  Let’s use the Text manipulation example discussed before to illustrate how a long formula is written through many small steps. Continue reading

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

Transform boring Excel reports into Business Intelligence 

Dashboard Course by MYNDA TREACY is now open

Last week, I shared the news about the free Excel Dashboard Webinars by Mynda.  I hope you did find a time to join the webinars.

If you haven’t watched the Webinars and are not sure what a dashboard is or how these skills might be relevant to you, then you can attend Mynda’s FREE Excel Dashboard Webinars, but hurry as they’re only on for a few more days.

If you’ve watched Mynda’s FREE Excel Dashboard Webinars, and you’re ready to take your dashboard skills to the next level then check out Mynda’s Excel Dashboard Course.

It will teach you how to build amazing interactive dashboards that impress your boss, get you noticed and help you stand out from the crowd.  And the best part, aside from the praise from your boss and colleagues😉, is you’ll also learn tons of tips you can use in your everyday Excel work to get things done more efficiently.

What to Expect in the course:

  • The course is video based and available online 24/7
  • It comes with comprehensive Excel workbooks complete with notes
  • 5 example Excel dashboards to keep
  • There’s also an option to download the videos,
  • Plus Mynda personally provides support for the first 6 weeks of the 12 month membership.

By the way, the 6 weeks of personal support is the reason the course is only open for a limited time (registration closes Aug 18). Mynda limits the number of classes to allow time for other projects and a break every now and again. That’s not to say she won’t support you after the first 6 weeks, it’s just that it might take her a few days or a week to reply to you if she’s out of town or busy working on another course etc.

Click here for more information and a sample video from the course (it’s worth watching just to learn this tip).

Who is the course for?

Dashboards are an incredibly valuable tool in today’s market for consultants, analysts and managers. And when you know the tricks it can be straightforward to build highly professional and interactive Excel dashboards.

If your work requires preparation of reports in Excel (or you’d like it to) then you will find this type of training worthwhile.

Mynda teaches you some simple rules (that most people don’t know), which you can use in all of your Excel work that will get your job done more quickly and efficiently, and of course she’ll teach you how to make killer dashboards including animated charts and other cool interactive elements.  

I would highly recommend the course but don’t take my word for it. You can read further comments from past students and find out more here.  However, this course is not for Excel beginner.

20% Early Bird Discount

You will enjoy a 20% early bird discount if you register Mynda’s  Excel Dashboard Course  by Thursday August 11 (at 8pm in Los Angeles to be exact).  Just to remind, the course will be closing on Thursday August 18.

So, do yourself a favour and check out the course.  The course is awesome and it’ll transform your Excel reports and possibly even your Excel career.

Disclosure: I make a small commission for students who join Mynda’s course via my site, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that.  Oh, and just watching the course videos won’t transform your career, like you won’t become a Master Chef by reading cook book only.  You have to actually put it into practice, but then you know that.

Posted in General | Tagged , | Leave a comment

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

Free Excel Dashboard Webinars by MYNDA TREACY is coming soon

About two months ago, I shared the news of FREE Excel Dashboard Webinars by Mynda Treacy.  If you had joined the Webinar, I am pretty sure you have better understanding on an Excel Dashboard and you should realise that it not difficult to build, as long as you know how.  If you did not join the Webinars before, no worry as here’s another chance.  Mynda is going to open her FREE Excel Dashboard Webinars in early August.

If you’re not sure what a dashboard is, or how these skills might be relevant to you, or you think Excel Dashboard is too advance for you, then you can attend one or both of Mynda’s FREE Excel Dashboard Webinars.  You will be surprised that it is less difficult than you may have thought about.  Nevertheless, you are expected to know Pivot Table.

 

Free Excel Dashboard Webinars

Webinar 1 – Excel Dashboard for Excel 2007/2010/2013/2016

The first webinar will teach you how to use Excel to build this interactive dashboard below, no additional software or add-ins required, just plain old Excel and some data.

 

 

 

Webinar 2 – Excel Dashboard for Excel 2010/2013/2016 using Power Query and Power Pivot

If you have Excel 2010, 2013 or 2016* then this webinar will showcase how you can use Excel’s Power Query tool to get data from multiple sources, mash it up with Power Pivot, analyse it in PivotTables to create this interactive Excel Dashboard.

 

* Power Query is available with the desktop version of Excel 2010/2013/2016, or Office 365 ProPlus. Power Pivot is available with the desktop version of Excel 2010 or Excel 2013/2016 Office Professional Plus, Office 365 ProPlus, or in the standalone editions of Excel 2013/2016.

By the way, did you know that Power Query is “renamed” to Get and Transform in Excel 2016?

I don’t know why there is such change, but my interpretation is “Get it and Transform your career”.  :)

So, do yourself a favour and Click here to watch the free webinars.

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