Are you tired with data dump Excel Report?

that takes you long to prepare; and is hard for report consumers to get insight…

If you’ve ever felt like:

“My Excel reports take way too long to update each month/week”

“I spend hours collating and cleaning data, updating formulas and charts and then no one reads my reports anyway, what’s the point?”

“I need to get my Excel skills up to date so I can stand out from the crowd of other job applicants”

“I’m not sure how to approach setting up my Excel workbook the right way, so it’s easy to build and maintain”

Then you’re in for a treat because today I’m excited to tell you about Microsoft Excel MVP, Mynda Treacy’s

Free Excel Dashboard Webinars

If you’re not sure what a dashboard is, or how these skills might be relevant to you, and help you overcome the challenges above, then you can attend one or both of Mynda’s FREE Excel Dashboard Webinars.

Webinar 1 – Excel Dashboard for Excel 2007/2010/2013/2016/2019/Office 365

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 webinar 1.

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

Excel is evolving; in the last couple of 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 also since less than 1% of Excel users know these tools you’re going to have a huge competitive edge in the workplace.

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, but you know that right?

If you have Excel 2010, 2013, 2016, 2019 or Office 365* 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, 2016, 2019 and Office 365. Power Pivot is available with the desktop version of Excel 2010 and Excel 2013/2016 Office Professional, Office 2019, Office 365, or in the standalone versions of Excel 2013/2016. Power Query and Power Pivot are not available for Mac.

Click here to register for webinar 2.

What people are saying about the webinars

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

Invite a Friend

Feel free to forward this to your friends and colleagues and invite them to attend Mynda’s free Dashboard webinar too. They’ll thank you for it.

Dashboard Course

If you’ve seen Mynda’s free Dashboard Webinars and you’re ready to take your dashboard skills to the next level then check out Mynda’s Online 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, like this incredible le Tour de France dashboard she made, which is also included in the course:

 

The best part, aside from the praise from your boss and colleagues ;-), is you’ll also learn loads of tips you can use in your everyday Excel work to get things done more efficiently.

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. That’s why this type of training is so worthwhile.

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

Early Bird Discount

If you register by Thursday, Nov 14 it’s 20% off.  (Note: registration closes Nov 21)  

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 (at no additional costs to you) 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 it 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 power of Dynamic Arrays in #Excel 365

… in creating dynamic drop-down with conditions

Making a dynamic drop-down list is not difficult.  It may require multiple steps, helper formula and/or helper tables though.  And the number of helper tables depends on the complexity of the requirement.  I wrote a blogpost for a job-assignment task more than 5 years ago.  The requirement is simple: show the teacher with least hours of classes assigned to on top of a drop-down list… like the screen cast show below:

Excel tip - Dynamic dropdown with Excel 365_1

See!? The drop-down is changing according to the total hours assigned, as calculated on column F.

With dynamic arrays, this task is super easy.

You may download a Sample File to follow along.

Note: As of today, Dynamic Arrays is available to Excel for Office 365 Insider only

Continue reading

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

Get a list of A to Z in #Excel

Have you ever tried to input “A” and then drag it down with a hope of getting a list of A to Z?

Excel Tip - Fill A to Z

If you tried (and without success), you may be wonder how I did it?

You may find the trick (using Custom List) in the following video: Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Add Data Labels for Total to Stacked Columns in #Excel

When we create stacked columns in Excel, we exclude Total from the data as it may give an extra stack that we don’t want.  However, we may want a data label for Total on the stacked columns, like the one below:

Excel Tips - Add data label of total to stacked columns

So how can we do that?  This is what I am going to show you in a short video.

You may download a Sample File to follow along.

Continue reading

Posted in Chart | Tagged | Leave a comment

You think you know Copy and Paste in #Excel?

11 Excel Copy and Paste Tips and Tricks you should know

Perhaps Ctrl+C and Ctrl+V are the pair of shortcuts you use on a daily basis.  And probably you learned that on the first day your learned Excel.  Nonetheless, do you think you know Copy and Paste well?

In this post, I’d like to share 11 practical tips and tricks about Copy and Paste in forms of short videos.  Check them out!

Copy and Paste Visible cells only in Excel

 

Paste with column widths preserved in Excel

 

Multiply a value to a range of data in Excel

 

Convert text date to numeric date using Copy and Paste in Excel

 

And seven more… Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Suggestion: Adding a button for number formats in thousand/million/billion in #Excel

Did you know what you may submit your ideas to improve Excel through UserVoice?

The recent HOT news in the Excel community – the new XLOOKUP function is also resulted from an idea submitted in UserVoice.

While everyone is super exciting about the XLOOKUP function, I would like to suggest a minor thing that is very basic but super helpful to most all Excel users.

My humble suggestion:

Adding a button for number formats in thousand/million/billion

Number Units_v1

Why have I come up with this idea?

Long time ago, I wrote a post Show number in thousand (k) or in million (M) by using custom format, which to my surprise, have become one of the top 3 posts in my blog.  It reflects the truth that it is a very common demand from Excel users.  Although it is not really “difficult” to use the custom format to solve the problem, it is not obvious nor intuitive for sure.

Then I searched the UserVoice to see if anyone has the same idea.  Of course, there is.  I came across the idea submitted HERE.  I VOTED for that immediately and left my additional suggestions in comments.  However, the number of VOTE is surprisingly low (only 28 votes including mine).

So, if you like the idea, may I ask you to VOTE for it!  And more importantly, spread the word to invite more votes.  Every VOTE counts.  Sometimes, a tiny change would make a big difference.  🙂

 

 

 

Posted in General | Tagged | Leave a comment

CTRL A to Z – Learn 25 #Excel Shortcuts with demonstrations

Do you know how many keyboard shortcuts we have in Excel?  Honestly, I don’t know.  I just know the shortcuts that I need to use.  Believe it or not, from Ctrl+ A to Z, I use them all but M, because no action is assigned to Ctrl+M yet.  Having said that, I am using M codes more and more recently.  Maybe Microsoft could assign Ctrl+M as Open the Power Query Editor to complete the list.  😛

Let’s talk about the shortcuts.  Maybe you already know most of them, but do you know ALL of them?  Especially the secret use of CTRL+J? Check it out! (02:07)

You can learn all the 25 shortcuts in a 7-minute video embedded in the end of this post.

Here’s a list for quick reference.  

CTRL + A Select All (00:11)

CTRL + B Toggle BOLD (font) (00:34)

CTRL + C Copy (00:42)

CTRL + D Fill Down (00:49)

CTRL + E Flash Fill (Excel 2013 or later) (00:59)

CTRL + F Find… (01:11)

CTRL + G Go To… (01:28)   Continue reading

Posted in Shortcut | Tagged | Leave a comment

The proper way of copying worksheet in #Excel

It is not uncommon for people to copy entire worksheet to another sheet (be it on the same workbook, or another workbook, even a new workbook). Nevertheless, are we doing it the right way?

Many people would do so by selecting ALL cells in the worksheet, and then paste in a blank worksheet.  BUT this is not the proper way to do so…  We should do so by right-clicking the worksheet to be copied, followed by “Move or Copy…”

Excel Tips - Copy Worksheet1

Even if you have known the “proper” way to “Move or Copy…” a worksheet, did you know that you can do it with mouse drag and CTRL key quickly?

Excel Tips - Copy Worksheet

 

Let’s watch it in action:

Continue reading

Posted in Excel Tips | Tagged , | Leave a comment

Copy chart formats to other charts in Excel

Excel tip - Copy and Paste Chart Format

If you know Format Painter, the on top of your mind solution should be using Format Painter to copy Chart Formats to other charts.  However, it doesn’t work… 😦

No worries.  Copy and Paste comes to recuse!! Really?!

Continue reading

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

Four Text to Columns tricks you cannot miss in Excel

Text to Columns

Have your heard about “Text to Columns”?  A big icon grouped into Data Tools of Data Tab of Excel?  Be your answer Yes or No, you do not want to miss the following tricks that may help you transform data quickly.   Check it out. Continue reading

Posted in Excel Tips | Tagged , | Leave a comment

Multiple short videos about using Fill in #Excel

If you have followed my blog for a while, you should know that I have created my YouTube channel recently.   And a playlist – Excel Magic in One Minute was created in early June this year.  So far, I’ve created 25 videos in the playlist.   In this post, I would like to put those videos about using Fill in Excel for your reference.

The topics are

  1. The Auto Fill Options you should know about
  2. Fit multiple rows of text into selected column width with Fill Justify
  3. Fill a series of big number quickly
  4. Insert a series of weekdays
  5. Insert a series of month end dates
  6. Non-Formula Approach to get Serial Number Groups

The six videos are embedded here.   Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Free Excel Dashboard Webinars you should not miss

Have you ever had an Excel moment like this?

“My Excel reports take way too long to update each month/week”

“I spend hours collating and cleaning data, updating formulas and charts and then no one reads my reports anyway, what’s the point?”

“If I could just impress the boss I’d get that pay rise/promotion I deserve”

“I need to get my Excel skills up to date so I can stand out from the crowd of other job applicants”

“I’m not sure how to approach setting up my Excel workbook the right way, so it’s easy to build and maintain”

Then you should spend an hour, or even three hours, to watch the free Excel Dashboard Webinars by Microsoft Excel MVP, Mynda Treacy. Continue reading

Posted in General | Tagged , | Leave a comment

[Guest Post] DRAW – The New Tab in Office 2019/365 for INKING

This is a guest post by Aprajita of ExcelChamps.

Let me ask you something.

Suppose you are presenting data to the management and suddenly an important point strikes your mind.

Will you look around for a piece of paper and pen or ask someone around to note down the points of discussion?

Please share your answer in the comment section with me. If the answer to my question is yes, trust me you need this article.

In Excel 2019 and Office 365, Microsoft has introduced a new tab called “Draw”.

You know what, when I started making PowerPoint presentations, I wonder if I could just draw or write something on the screen…

…and it gets automatically converted into text or shapes.

With Draw TAB, all of these things are possible now, and in this post, we’ll be exploring all draw tab in detail and exploring it’s all the features.

Note: Draw Tab is the same in all the Office Applications (Word, Excel, and PowerPoint). 

So let’s get started… Continue reading

Posted in General | Tagged , , | 1 Comment

Interesting behavior of Copy and Paste

Excel Tip - Interesting behavior of Copy and Paste

This sounds silly, I know.  To achieve what is stated in the above scenario, what we need to do is simply

  1. Select “January to March” from the slicer
  2. Copy the range C4:C9
  3. Paste to C13

Then repeat the above steps with different slicer selection and then Copy and then Paste.

In short, we need to perform Copy and Paste three times.  The following demonstrates this typical approach:

Excel Tip - Interesting behavior of Copy and Paste

Nine steps in total.  No big deal.  And there are no quicker way to do this kind of manual work, except to move the mouse quickly, or to press the shortcut key faster…

If you think so, you should continue to read this post.

Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Updates on Excel Magic in One Minute

Six weeks ago, I created my YouTube playlistExcel Magic in One Minute.

As of today, I’ve already published 17 videos to the playlist.  Meaning 2-3 videos per week.  Not too bad as a weekend YouTuber (newbie), I guess.

Here’s the playlist.  Check it out!

I hope you like it.

Your comments are always welcome. 🙂

Posted in General | Tagged | Leave a comment

Do your random numbers stay the same when you copy and paste in Excel?

This is an extension of my previous post – Freeze a random result with mouse trick.

In my experience using Excel, from Excel 2000 to 2010, and recently 365 (I’d never used Excel 2013 though), I have experienced the same strange behavior when I tried to convert a random number to value by using copy and paste value – the random numbers got re-calculated before the paste action.   It happened to me in different PC (work, school, or home).  Therefore I thought it is a common problem until David N left his comments to my post.

It aroused my curiosity.  I tried again with four different ways to paste (random) values to see if I get different results.  Interestingly, it did.  See below:

Excel Tip - PasteValuewithMouse2

A) Using shortcut Ctrl+Alt+V to open the paste special dialog, then select Value.  It works! 🙂

B) Using the mouse trick discussed in previous post.  It works! 🙂

C) Using the “Paste”, “Values” under Home Tab.  It doesn’t work. 😦

D) Using Right-Click, Paste Value.  It doesn’t work. 😦

Tip: Using Clipboard also works! 🙂

Honestly I have no clues why it happened.   If you know, please leave your comments.

Anyway, it reminds me one thing that I always say to my audience when I conduct in-house Excel training:

Always check your result! No matter how confident you are!

Posted in Excel Tips | Tagged , | 10 Comments

Freeze a random result with mouse trick

Perhaps you have tried to use Excel to generate a random number for a lucky draw.

That is easy.  For example, if you want to generate a random number, you can use the function RANDBETWEEN.

=RANDBETWEEN(1,1000)

This will give you a random number between 1 and 1000.  However, RANDBETWEEN re-calculates whenever there is update to the worksheet.  In order words, it keeps giving you a new value when you work on the same worksheet.   That is the nature of “Random” but you don’t want this for a lucky draw.  As you want to “freeze” the draw result.

On top of your mind may be Copy and Paste Value…. BUT it doesn’t work 😦

Excel Tip - PasteValuewithMouse

Re-calculation occurs before the value is pasted…

No worry!  There is a mouse trick to perform the Paste Value action without triggering a re-calculation.  Watch this:  Continue reading

Posted in Excel Tips | Tagged , , | 6 Comments

How to sort bar chart in descending order?

Excel Tip - Sort bar chart

Sound like a silly question, but it is a bit confusing indeed.

One day, a friend asked me how to sort a bar chart in descending order.  I told her to sort the data.

She replied: “I did.  The data is sorted in descending order.”

Then I added: “Sort it in reverse order, i.e. ascending order”.

Excel Tip - Sort bar chart

As simple as this.  Yeah! Continue reading

Posted in Chart | Tagged , | Leave a comment

Send a cell content to Google search

This is a continuation of previous post, in which I talked about the potential use of Smart Lookup for an Excel  dashboard.  However there is limitation.  It drove me to think of an alternative.

Instead of asking user to trigger Smart Lookup, I created a dynamic hyperlink that sends user to Google Search result based on the content in a cell.

Excel Tip - Smart Lookup or Google it1

Here’s come this post.

Continue reading

Posted in Formula | Tagged | Leave a comment

Smart Lookup in #Excel 2016 or later

Have your heard of Smart Lookup?  Don’t confuse it with Excel Functions.  It is nothing related to formula writing in Excel.  Simply consider it is a web search engine (Bing) embedded in Excel.

Watch it in action, and you will see!

Excel Tip - Smart Lookup or Google it

HERE’s the description from Office.com about Smart Lookup.

This is something cool, with no doubt.  It will become great only when you’ve found a practical Use Case of it.

Who will look up something from a spreadsheet??

That’s my question…. until recently. Continue reading

Posted in Excel Tips | Tagged , | Leave a comment