Insert “tick in a box” in Excel

Posted on September 17, 2016 by

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:

Excel Tips - Insert Tickmark.png

Quite simple and straight forward, right?

I expected I would get something like the following in return: Excel Tips - Insert Tickmark 1.png

But in reality, I got somethings like these:Excel Tips - Insert Tickmark 2.png

Franky, I don’t blame them.  On the contrary, I appreciate their efforts in communicating the results.  Especially the one who used “circle” as the indicator.  I can imagine how much time they spent on it.

Why couldn’t they simply click on the checkboxes?

Because the “checkbox” on the original template was not true checkbox.  It is just a plain “rectangle” in a cell, meaning that you cannot “check” or “uncheck” it…. not to even think about putting a tick on it.

So how can I put a tick on the box?

Excel Tips - Insert Tickmark 1.png Continue reading →

Posted in Excel Tips | Tagged | 2 Comments

Three years of Excel Blogging – A quick review

Posted on September 10, 2016 by

Note: This is not an Excel post…

I totally forgot that today is the 3rd Anniversary of my Excel blog.  Time really flies.  Thanks WordPress for sending this 3rd Anniversary notification to me.  What’s a pleasant surprise!🙂

Screen Shot 2016-09-10 at 4.17.54 PM.png

Did you know how many posts are here in wmfexcel.com?

Screen Shot 2016-09-10 at 4.23.32 PM.png

I can’t believe it.  It’s 185 posts! Including this post, it turns to 186 posts.  That means I published more than 1 post per week since I started it.  I didn’t know I am so hard working.

So which posts got most views in 2016 so far?

Post Views
When unhide row doesn’t work… 50409
Dropdown calendar in Excel 47196
Perform VLOOKUP with 2 lookup values 43654
Advanced vlookup – wildcard characters “? and “*”” 19038
Advanced vlookup – Text vs. Number 16149

Obviously, VLOOKUP is still the most common function that people like to explore.  But you know what? The more I know Excel, the less I use VLOOKUP indeed.

Also tell you this…

I am actually a bit complacent when I Google some key words like “cannot unhide row in Excel“, “Dropdown calendar in Excel“, “vlookup 2 values“, “copy from protected excel sheet” and see my posts appear on the first result page, sometimes even on the top.  :)

Where are my readers coming from?

Screen Shot 2016-09-10 at 4.44.02 PM.png

<Top 10> 

Not surprisingly, majority of readers come from United States.  Even less surprisingly is the number from Hong Kong, where I come from and live in.  I doubt if all the views from Hong Kong are coming from my friends or people who knows me.  :)

The journey starts at low… but rewarding!

Screen Shot 2016-09-10 at 4.30.39 PM.png

Honestly, the first year was difficult.    What I mean is not the writing process but the emotional one.  I kept writing but no one was reading… not such a good feeling, right?

Nevertheless we have a proverb “萬事起頭難” in Chinese.  It means “The first step is the hardest. As long as you persevere, you are bound to succeed.”

I wouldn’t say I have succeeded.  But frankly, this journey is quite rewarding.  I learn so much in the progress.

If you are (still) reading this, I sincerely thank you!  You are the motivation for me to keep on writing on this blog @ wmfexcel.com, aka BeExcellence!

I hope you enjoy it as much as I do.  Let’s Excel together!

Posted in General | 4 Comments

Move cells with data to the top

Posted on September 3, 2016 by

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…Excel Tips - Move cells with data to the top 0.JPG

Would you do it this way?Excel Tips - Move cells with data to the top 1.gif

Or this way?Excel Tips - Move cells with data to the top 1.1.gif

What did I do in the second animated screenshot?

Continue reading →

Posted in Excel Tips | Tagged , | 1 Comment

Excel Humor #6 – Probably the shortest and the best Excel Proficiency Test

Posted on August 27, 2016 by

Definition of Advanced Excel user varies according to expectation.  Someone thinks advanced users should know all the VBA, Pivot Tables and be able to write super long formula.  Someone considers oneself “Advanced” for knowing the basic of VLOOKUP.   On the contrary, your boss may consider Power Pivot or Power Query as “Basic” Excel skill although h/she may have no idea what it is…😦

Is this something bothering you?

No worries.  I am going to share with you probably the world shortest and the best Excel Proficiency Test that gives good indication of someone’s Excel skill in 3 seconds.

Want the test?  Here we go (Just for Fun): Continue reading →

Posted in General | Tagged | 2 Comments

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

Posted on August 20, 2016 by

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

Posted on August 13, 2016 by

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 

Posted on August 6, 2016 by

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?

Posted on August 6, 2016 by

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

Posted on July 30, 2016 by

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

Posted on July 23, 2016 by

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???…

Posted on July 16, 2016 by

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

Posted on July 9, 2016 by

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

Posted on July 3, 2016 by

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

Posted on June 25, 2016 by

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…

Posted on June 18, 2016 by

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

Posted on June 11, 2016 by

 

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+↓

Posted on June 4, 2016 by

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

Posted on May 28, 2016 by

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

Posted on May 21, 2016 by

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…

Posted on May 14, 2016 by

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