Are you ready to turn boring Excel reports to Business Intelligence?

Posted on June 27, 2017 by

Well, I know.  You may have read similar topics before in my blog.   You are now seeing it again because the awesome Excel Dashboard Course by Mynda Treacy is now open.  What’s more exciting?   Mynda puts her popular courses “Excel Dashboard” and “Power BI” together so that you get the most out of it.  I like her table comparing pros and cons of Excel vs Power BI.

Excel vs Power BI Pros and Cons

The table below compares key points between Excel and Power BI:

What a great summary!  Nevertheless, I don’t agree on the “Cross” being put next to “Easy to learn” for Power BI.  It really depends.  If you possesses skills and knowledge about Power Query / Power Pivot, you will find Power BI is super easy to learn.   Having said that, if you are advanced Excel users but new to Power Query / Power Pivot, eager to acquire Dashboard skills, the Excel Dashboard Course would be a good start to next level.  Let’s see what are offering by Mynda.

Free Webinars

There’s no denying that Dashboard skills, whether in Excel or Power BI, or both are a huge advantage to Excel users around the world and in a vast range of industries. Just look at any job website and you’ll plenty of jobs that mention ‘Dashboards’ in the description.

To get you off to a running start Mynda is hosting a series of free webinars to teach and demonstrate Excel and Power BI for building dashboards.

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.

Click here to register for the webinar.

Many Excel users will already have the skills to build this dashboard, and seeing Mynda put them together will be exciting and inspiring when you realise how quickly you can get up and running with Excel dashboards.

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

Excel is evolving; in the last few years Microsoft have added new tools like Power Query and Power Pivot, to name a couple.

Embracing these new tools will not only enable you to get your work done more quickly, but you’ll also have a huge competitive edge in the workplace because less than 1% of Excel users know these tools.

It’s easy to ignore these developments and continue doing things the way you always have, but that won’t get you ahead in your career …you know that, right?

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 all versions of Excel 2010, 2013 and 2016. For Office 365 users with Excel 2013, Power Query is only available in Office 365 ProPlus. Power Pivot is available with the desktop version of Excel 2010 or Excel 2013/2016 Office Professional, Office 365 ProPlus, or in the standalone editions of Excel 2013/2016. Power Query and Power Pivot are not available for Mac.

Click here to register for the webinar.

Webinar 3 – Power BI Dashboard Reports (Replay)

See what all the hype is about first hand in this replay of Mynda’s Power BI webinar where she builds these two interactive dashboards in under one hour:

Click here to see the Power BI webinar replay.

Who is Power BI for?

Power BI is for the Excel user who is ready for more. More features, because Power BI Dashboards can do things you simply can’t do in Excel, and more skills to add to your résumé.

So, if you’re wanting more interactivity from your Dashboard reports and you’re prepared to learn some new skills then check out Power BI.

What people are saying about the webinars

Over 25,000 people have attended Mynda’s 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

Invite a Friend

Feel free to share this post to your friends and colleagues and invite them to attend Mynda’s free Dashboard webinars. They’ll thank you for what they can learn from the webinars.

Webinar Prerequisites

All you need is an internet connection, a web browser and speakers or headphones.

You don’t need Power BI or Excel to attend the webinars.

20% Off Excel Dashboard and Power BI Courses until July 7

If you’re ready to take the next step and add these skills to your repertoire then don’t miss out on the discounts on Mynda’s Excel and Power BI Dashboard courses. You can even get multiple courses in discounted bundles.

Click here for the Excel Dashboard course and bundles

Click here for the Power BI course and bundles

Both the free webinars and Mynda’s Dashboard and Power BI courses are available for a limited time, so hurry.

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.

Advertisements
Posted in General | Tagged , , | Leave a comment

The basic of Form Controls

Posted on June 24, 2017 by

Want to work like an Excel Pro?  Learn more about Form Controls.

Have you seen someone using Check box, Option Buttons, Scroll Bars on their worksheets and be impressed?  You may wonder how they made it and think that is something requiring professional Excel skills…

 

Indeed, it is easy to insert Form Controls and use them to interact with data on worksheet.

You may download a Sample File to follow through.

First thing first,

Where is Form Controls?

Continue reading →

Posted in Excel Tips | Tagged | Leave a comment

Excel Humor #10 – WannaCry moments in Excel

Posted on June 17, 2017 by

 

 

 

There must been many others.  Please share in comments. 🙂

Posted in General | Tagged | Leave a comment

The amazing SUMPRODUCT

Posted on June 3, 2017 by

Advanced use of SUMPRODUCT for multi-conditional SUM

In the previous posts, we talked about the basic of SUMPRODUCT, the behaviors of logical  values (i.e. TRUE/FALSE) when they are put into basic mathematics operations.  These set the foundation to understand the advanced use of SUMPRODUCT for multi-conditional SUM.

Indeed, the construction is simple:

=SUMPRODUCT(Conditions, sum_range)

What we need to be careful is to set the conditions correctly.

In short, multiplying arrays of TRUE/FALSE would return a single array of 1/0 simulating “AND” condition; while adding arrays (with a little trick) simulates an “OR” conditions.

Excel Tips - logic gate 1

Want more examples to illustrate? Continue reading →

Posted in Formula | Tagged , | 2 Comments

The logic gate

Posted on May 27, 2017 by

Have you heard about Logic Gate?  I learned logic gate in high school physics.  Decades ago… so don’t expect I can remember what it is.  😛

Having said that, I do remember the fundamental concept about AND gate and OR gate, which is very helpful for me to master logical functions in Excel.  Before we move to discuss advanced use of SUMPRODUCT as a function to do conditional SUM, we need to understand the behavior of  TRUE and FALSE in Excel first.

You may download a sample file to follow through.

What is TRUE and FALSE in Excel?

Boolean…?  Let’s skip this kind of jargon (the truth is I don’t know it either)…. TRUE or FALSE is simply a result of a logical test:Excel Tips - logic gate 0.PNGTip: Did you notice that by default, TRUE/FALSE is center-aligned; while number is right-aligned; text is left-aligned. Continue reading →

Posted in Formula | Tagged , | 4 Comments

SUM the PRODUCTs or SUMPRODUCT? That’s the question.

Posted on May 20, 2017 by

The basic of SUMPRODUCT

Excel Tips - SUMPRODUCT basic 1

Suppose we have a column showing retail price of different items; another column showing the units sold (illustrated above).  To get the total sales, most people will deploy a helper column to get the sales of each item first, and then get the total at the end of the helper column, as shown in the following screenshot.

Excel Tips - SUMPRODUCT basic 2

This makes sense especially when we are also interested in looking at the total sales by item.  However, if we are only interested in grand total, we may get the answer directly by using SUMPRODUCT.

You may download a sample file to follow through. Continue reading →

Posted in Formula | Tagged | 2 Comments

CrossTab SUM with 3 variables

Posted on May 13, 2017 by

This post is trying to answer a question by Laura in the post of 2D SUMIF with two variables – one on column and one on row.

Here’s part of the question:

……I want to look up the department in the vertical list and the month in the horizontal list, but I want to say sum that month and x many months before… … the only issue is that I want to be able to specify how many months are picked up in the solution. So if I’ve got 100s of months across the top of my data set, and departments down the side, in my formula I want to pick up the last 12 months of data for a selected department ending in a selected month. So my formula needs to have the potential to pick up all the months, but then depending on the month I select, I want to sum just the 12 months to that month. Is that possible? …

If I have understood correctly, Laura is looking for something like the following screen-cast:Excel Tips - CrossTab SUM with 3 variables

To tackle the request of “be able to specify how many months“, I suggest to use two variables: “From” and “To” to specify the sum range required.  Nevertheless, this approach bases on an assumption that the header (in our example Period) are sorted.   Otherwise the formula won’t work.

Here’s the formula:  Continue reading →

Posted in Formula | Tagged | 3 Comments

Sort Warning?!

Posted on May 6, 2017 by

Sorting is one of the basic features in Excel.  I believe most Excel users, if not all, know how to sort in Excel.  Without any scientific research, I think most users would click the “Sort A to Z” (ascending order) or “Sort Z to A” (descending order) icon directly.

Excel Tips - Sort Warning 1.PNGThat’s the case for myself unless I need to do multilevel sorting or Sort by row.

When we do single level sorting, i.e. sort based on one column only, we don’t have the motivation to click on the “Sort” as it initiates another dialogue box which requires more actions to perform the same task…Excel Tips - Sort Warning 3.PNG

A rational lazy people like me won’t do that. 😛

It was not until one day I read the comments from Sabine and Michael (Micky) Avidan to the post Copy and Paste can do so much. Continue reading →

Posted in Excel Tips | Tagged | 1 Comment

[Guest Post] 5 things you should start doing with Power Query

Posted on April 29, 2017 by

This is a guest post by Puneet Gogia.

Power Query is a revolution. It’s a (part of a) complete self-service BI tool which can help you in importing, connecting, shaping, cleaning and sharing your data with little efforts.

It has the capability to automate every single data process of your work. And, you should start using it for your little/big all kind of data tasks.

If you are using Excel 2016 then power query is already there as “Get & Transform” or otherwise you can download it from here. So today, in this post, I will show you the 5 basic tasks which you can perform with power query with very little efforts.

Table of Content

  1. Combine Files
  2. Unpivot Data
  3. Merge Columns
  4. Split Columns
  5. Convert Date In Quarter

Featured Download: Get this cheat sheet to learn these 5 power query tips. Continue reading →

Posted in Excel Tips | Tagged | 11 Comments

Adding bullet to each line in cell

Posted on April 16, 2017 by

Excel Tips - Bullet Point 1

Does it sound familiar to you? Are you using Excel as word processing tool?

If you have experienced the task shown above, you should know how tedious and time-consuming it could be as you cannot convert each line into bullet point by clicking one button as if in Word or PowerPoint.

Luckily enough, there is a workaround trick for this task.  (Supposed you have input each new line by using Alt+Enter) Excel Tips - Bullet Point

The trick is about Find and Replace, obviously.  What is not so clear to you is what I input in the dialogue box for “Find what” and “Replace with:”.  😛 Continue reading →

Posted in Excel Tips | Tagged , , | 4 Comments

Quick Tip: Undo the “Undo”

Posted on April 8, 2017 by

Undo the “Undo”?  This sounds silly, I know.  But silly things happen (frequently). 🙂

If you undo a step, you cannot undo the undo by applying another undo.   (Because Excel allows 16 undos by default, although you can modify this setting.)

So how to undo the undo?

Continue reading →

Posted in Excel Tips | Tagged , | Leave a comment

Remove hidden sheets with “Inspect Document” or VBA

Posted on April 1, 2017 by

This post is an extension of the previous one – we talked about hiding worksheets is not a good way to prevent others from seeing the data on hidden sheets.  Since you have read that post, you decided to remove all hidden sheets.  Then you realized you had to unhide them one by one before you could delete them… Is that really the case?

Well… it depends.
Indeed there is a way to remove all hidden sheets (just hidden, not very hidden) quickly. Continue reading →

Posted in Excel Tips | Tagged | 4 Comments

Simple trick to look (hack) into hidden sheet

Posted on March 18, 2017 by

This post is inspired by the previous post Excel Humor #9 – You do not see me

Situation

Suppose you are going to share an Excel workbook to your colleagues.  However there is a worksheet with sensitive data that you don’t want them to see.  So you hide the worksheet.  As you know Excel well, you further Protect Workbook, with password set, in order to avoid user from unhiding the worksheet.

Excel Tips - Look into hidden sheet

Since you are also a careful person, you test to make sure no one can unhide the worksheet unless one knows the password to unprotect workbook:Excel Tips - Look into hidden sheet 1.gif

Now you are pretty sure that no one can unhide the worksheet, without the password.

YES! BUT… ARE YOU SURE NO ONE CAN SEE THE DATA ON IT?

Here’s a simple trick to look into the data on the hidden sheet.  Indeed I don’t even need to unhide the worksheet to get the data on it. Continue reading →

Posted in General | Tagged | 11 Comments

Excel Humor #9 – You do not see me

Posted on March 11, 2017 by

This is a true story with modification… I can’t believe it happened when I first heard it, but then I realize some changes made on Excel 2016 (not sure if also in Excel 2013).

Here’s today humor:

Excel humor - you do not see me

If you are working with Excel 2010 (which I am most most most of the time), you probably know that when you select a range, the cells selected turn dark (grey) and you will see any texts in white, if any, in the selection.

To my surprise (in a positive way), that behavior get improved in Excel 2016.  The following screen cast demonstrates the scenario in Excel 2016:Excel humor - you do not see me.gif

Having said that, we may still read the content from the formula bar.  So don’t turn your text in white and think that no one can read them. ;p

Joke aside… NEVER put sensitive / confidential data on a workbook that you are going to share with someone who are not supposed to see those data.  Next week I will show you a simple way to “look” into hidden worksheets even the worksheets cannot be unhidden.

Stay tuned!

 

 

 

Posted in General | Tagged | Leave a comment

Interactive Chart with navigation panel [NO VBA]

Posted on March 4, 2017 by

Excel Tips - Interactive chart with navigator panel.gif

Isn’t it nice?  Don’t think that this is difficult.  No VBA is required.  Indeed you only need to know a few Excel skills in order to create an interactive chart  with navigation panel like this.

The skills required:

  1. Conditional formatting
  2. A few functions: INDEX, ROW, basic logical test
  3. Scroll bar
  4. Create a bar chart (of course)
  5. Linked picture (aka Camera)

That is all.

You may download the sample file to do “reverse engineering”, with the following hints. Continue reading →

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

Last Chance for Power BI and Excel Dashboard courses by Mynda Treacy

Posted on February 28, 2017 by

 

As an Excel user you’ve probably created the occasional report. You may even regularly create Excel Dashboard reports.

After all, Dashboards are the most value-adding element for Excel users. However, if you’ve used Excel to build dashboards then you probably wish they could do more.

For example, if you wish you could:

  • Click on a chart and drill down to see more details
  • Apply filters to your dashboards by clicking on a chart element and have all charts filter or highlight the selected item
  • Click play and see an animation of your data over time

     

  • Create one dashboard and then send it to all of your regional/department managers and have it automatically filter so they only see their data
  • Get real-time updates of your reports on your mobile phone or tablet
  • Come into work in the mornings knowing that your reports have already been automatically refreshed
  • Easily share your dashboards with your colleagues and have them accessible on any device

Then Microsoft’s new Power BI Dashboard tool is for you. It can do all of those things plus more, and it’s drag and drop easy!

screen-shot-2017-02-05-at-5-25-51-pm

 

 

Start using Power BI Today

Microsoft Excel MVP, Mynda Treacy demonstrated the awesomeness of Power BI in some webinars last week. If you missed it you can catch a replay here if you’re quick.

And if you’re interested in getting up to speed with Power BI fast then check out Mynda’s online Power BI course. Registration closes March 2nd, so don’t miss out.

As an Excel user herself, Mynda aims the course at helping fellow Excel users make the transition to Power BI. She knows you don’t have time to waste so she doesn’t ramble on. Her video tutorials are to the point so you’re up to speed quickly.

Not ready for all that Power

Alternatively if you’re not ready for all that Power then start with Excel Dashboards. You can do some amazing things with regular Excel, and you may already have most of the skills required and just need me to show you how to pull them together to create something amazing.

Registration for both courses closes March 2nd, so don’t miss out.

 

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

Want some pies?

Posted on February 25, 2017 by

First of all, this blogpost is about Pie Chart, but not about how to make a Pie Chart.  Indeed, I am not going to show the steps of making Pie Chart here.   Also I am not intended to discuss whether Pie Chart is good or not to visualize data here. 🙂

One day my brother asked me how to show the data of “Jun” for the below Pie Chart:Excel Tips - Stacked Pie 1.png

As you can see from the above screenshot, the data contains 6 series.  My question to him  was:  “How are you going to plot that into Pie Chart by using Paper and Pencil?”

 

A flash in my mind is something like this:Excel Tips - Stacked Pie 2.png

Seriously??? How are we supposed to visualize the data? Continue reading →

Posted in Chart | Tagged , , | 2 Comments

Copy and Paste can do so much…

Posted on February 18, 2017 by

Answer to the 5 little Tips (tests) – Part 5/5

The task: To sort the following dates in descending order.  Sound silly but…

Excel Tips - Trick #5.png

The first thing you would probably do is to go to Data tab and then click the Sort Z to A icon?

Excel Tips - Sorting Dates.PNG

And get the following result… without even looking at it (as you trust Excel as reliable tool), believing Excel gets the job done.

Excel Tips - Sorting Dates1.PNGYes. Excel gets it done…

BUT are you sure the dates are sorted in the way you want?  In the in-house training in my previous company, many participants really thought so…  

I always remind myself:  No matter how familiar I think I am with Excel, CHECK the result before sending out.

Get thing done ≠ Get thing right

The date is not sorted correctly because they are not input as numeric date.  Instead, they are input (actually downloaded from SAP) as text.

excel-tips-sort-trouble-date

You may want to see my other post: Number Stored as Text…

So how to fix the problem?

Continue reading →

Posted in Excel Tips | Tagged , , | 22 Comments

Go to Special + Copy and Paste do the magic

Posted on February 11, 2017 by

Answer to the 5 little Tips (tests) – Part 4/5

The task: To convert “1” to “Tick” in a table like the following one:

Excel Tips - Trick #4.png

Answer:  Copy and Paste with a simple trick …

What? Not Find and Replace?  Yes, you may do it by using Find and Replace.  As it is Excel, there are different ways to accomplish the same task.  Either way, the first thing we need to know is how to insert a “Tick” mark.  Sound silly? But are you sure every one knows how to do that??  I have a blogpost to describe how to perform a similar task.

The following screencast shows you how to accomplish the task: Continue reading →

Posted in Excel Tips | Tagged , , | 4 Comments

Free webinar on Power BI by Mynda Treacy is coming soon

Posted on February 8, 2017 by

Have you heard about Power BI from Microsoft?

Probably yes.  However I guess most people are not yet aware of this amazing BI tool from Microsoft that enables you to build stunning interactive dashboards and share them on any device.screen-shot-2017-02-05-at-5-25-51-pm

If you want to know more about Power BI, you may want to join the free webinar by Mynda Treacy coming soon.  Before that, here’s a quick overview on Power BI. Continue reading →

Posted in General | Tagged , | Leave a comment