Are we aligned?

A simple trick to align objects neatly.ūüôā

excel-tip-align-objects

Did you move the objects one by one and then realized that it took you forever to have them perfectly aligned?

excel-tip-align-objects-1

Why not using the “Align” under Drawing Tools?

Continue reading

Posted in Excel Tips | Leave a comment

Turn boring Excel reports into Business Intelligence – Excel Dashboard

Last week I told you about the awesome Excel Dashboard Webinar ¬†was back. ¬†I hope you had a chance to watch it and get something already. ¬†If not, it’s not too late. ¬†But hurry seats and sessions are limited with the last webinar on December 7. Click here to register for the free webinars.¬† Check it out, you will find something valuable to your daily Excel works.

If you want to have a deep dive into Excel Dashboard, the fantastic dashboard course that Mynda Treacy has put together over at My Online Training Hub should worth your time.

 

Mynda’s course will have you standing out from the crowd in no time, just like Fraser, with the skills to build interactive reports like this amazing Sales and Marketing dashboard:

 

 

Who is the course for?

If you’ve attended Mynda’s free Dashboard webinar then you’ll know how powerful Excel is and how having Excel Dashboard skills will skyrocket your productivity and career.

You really need to know Dashboards if:

  • Your current reports take forever to update or,
  • You feel like no one takes notice of your reports or they constantly come back with requests for a slightly different report or,
  • You want to be able to present informative and interactive reports or,
  • You need to take your Excel skills to the next level to impress your clients with more meaningful reporting or,
  • You want increase your chances of employment, or get that promotion you’ve been after.

What members are saying about the course:

Loads of people have already registered and are on their way to wowing people with some killer reporting tricks, not to mention getting their work done faster.

Mynda shared this email with me from Kerry (with her permission), a member from a previous class:

 

“I have to tell you that I just downloaded the Session 4 workbooks. They are fantastic. I thought I would have to take notes in order to recall your instruction. Not at all – it is all in the spreadsheet. Your online tutorials and download material are extraordinary. I am so impressed.

I will be recommending your online training to my colleagues. Your workbooks are easy on the eye and very easy to understand.

I am terrified by the thought of writing formulas. You explain them and make it look so easy.”

Early Bird Discount

I just wanted to remind you that the 20% early bird discount ends Thursday December 8, at 8pm in Los Angeles to be exact.

So, go ahead and enroll here before the discount ends on December 8.

 

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, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.

Posted in General | Tagged , | Leave a comment

Free Excel Dashboard Webinars by MYNDA TREACY is back

If you have been following my blog for more than three months, you should have read this before. ¬†If you wonder why I am reposting it again and again (Yes, this is the 3rd time I write about Mynda’s Excel Dashboard webinars and courses), the answer is simple: it is awesome. ¬†If you are serious in taking your Excel skills to the next level, you should really take time to learn more about Excel Dashboard.

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 or 2016, or Office 365 ProPlus. Power Pivot is available with the desktop version of Excel 2010 or Excel 2013/2016 Office Professional Plus, Excel 2016 Professional, 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 sign up for the Webinars.

What people are saying about the webinars

Over 25,000 people have attended her Excel Dashboard webinars and this is what some of them had to say:

 

“Thank you! This webinar has already saved tremendous time and effort. I tried some of the techniques you illustrated for a cumbersome report update that I can see will cut our preparation time by 2/3’s or more. ….awesome! Many thanks!!!!”

Cynthia Tashjian

 

“WOW, just WOW. Can’t type more now – am trying to create a dashboard. Thank you Mynda!”

Susan

 

“The webinar was fantastic! I really enjoyed every moment. As you said, it was fast paced, but I am so excited to go back and review the presentation. We have been trying to get a dashboard started for my department and this really motivated me to take the lead. Thank you so much!”

Kathryn Puskar

 

“Oh. My. God. I know nothing! My jaw dropped a couple of times as you casually mentioned a way to manipulate PivotTable data. That’s the problem with being largely self-taught – there are huge gaps in my knowledge.”

Ann

 

“I presented my first dashboard to the CEO yesterday and he was ecstatic. Since we are downsizing I was very worried about my job, but after seeing his reaction…I understand that my job is secure (for now). Thanks!”

Iris Stein

 

 

 

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, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.

Posted in General | Tagged , | Leave a comment

Be cautious when using SUMIF(s)

SUMIF is a handy but helpful function.  The syntax is simple:

=SUMIF(range,criteria,[sum range]

Excel Tips - SUMIF.png

What it does is quite straight forward indeed. ¬†In the example above, it instructs Excel to look into the range (A2:A7), look for the matching criteria, which is “A“, and then sum the “corresponding” value in the sum range (B2:B7). ¬†That’s how we get¬†5 as a result. ¬†Make sense?

However, if we point to incorrect reference for sum range, SUMIF may give you an unexpected (and also incorrect) result. ¬†What do I mean? ¬†Let’s say if I mistype (by human error of course) the formula to

=SUMIF(A2:A7,D2,B3:B8) 'Note the one cell shift in the sum range

Excel Tip - SUMIF 1.png

Do you expect a result of 4?  Continue reading

Posted in Excel Tips, Formula | 2 Comments

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 to right, different chart elements were selected one by one with each press of the arrow key.

Excel Tips - Move a chart with arrow key.png

OMG… How come it is so difficult to perform such a simple task?

There is a simple trick (although imperfect) to let you move a chart by using arrow keys…¬† Continue reading

Posted in Excel Tips | Tagged , | 2 Comments

The powerful Power Query and the interesting TechNet Virtual Labs

As you may be aware of, I haven’t written¬†a real blogpost recently. ¬†I was super busy at work in the past month. ¬†No surprise. ¬†There are moments Excel won’t help me¬†much at work, especially when bosses like PowerPoint (and PDF) more than Excel. ¬†You know what I mean!

Nevertheless, because of the tight schedule and heavy workloads, I did actually spend more time in exploring the Power Query, which is simply POWERFUL.

To be honest, I am just a beginner in using Power Query.  And what I used are mainly the most basic functions from the ribbon tabs (Home, Transform, Add column) of the Query Editor.   But you know what, it helps a lot already.  And it is indeed quite intuitive to an experienced Excel user.

What I really like about Power Query?

Continue reading

Posted in General | Tagged , | Leave a comment

Excel Humor #8 – iLOOKUP, the ultimate function in using Excel

Have you ever used “i”LOOKUP? ¬†I do it almost every day… ¬†ūüôā ¬† Continue reading

Posted in General | Tagged | Leave a comment

Excel Humor #7 – Interactive Report

Excel Humor - Interactive Report.PNG

I hope you will never¬†run into a situation like this…ūüôā

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

What is a “Container Chart”?

First of all, I would like to thank¬†Chandoo for suggesting this name for the chart I submitted to him for a charting contest a few months ago. ¬†What’s more surprising to me is I was one of the winners of the contest. What a honour of mine!ūüôā

Here’s a screenshot of part of the chart I prepared. ¬†You may view the full chart and download¬†a copy of the workbook from Chandoo’s site¬†here.

Excel Tips - Column Chart1.png

Don’t miss the entries from other participants as you will learn¬†a lot of Excel techniques from them.

What you see above is actually a Panel Chart… meaning more than one charts are putting together intentionally to visualise the data of interest. ¬†The upper part (showing the yearly profit) of the chart is a simple clustered column chart; the bottom part (showing the yearly costs with four components) may be something of your interest to know about, right? Continue reading

Posted in Chart | 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:

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 | 4 Comments

Three years of Excel Blogging – A quick review

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

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

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

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

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