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

Advertisements
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 , | 8 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

Excel Magic in One Minute

Last Friday was a public holiday in Hong Kong.  Yes, a long weekend!  Guess what I dd over the long weekend?  I’ve created a playlist – “Excel Magic in One Minute” on my YouTube Channel.

The name says it all – it’s about bite-size Excel tricks that you can consume in ONE MINUTE. Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Apply Conditional Format for alternate row color based on groups…

…that works even when filter is applied.

Excel Tip - CF to make color bands

The situation:

We have a table that we would like to apply color banding based on groups.  We can achieve this by inserting a helper column to identify the sequence of each group, with the following formula:

In C2, input

=IF(A1=A2,C1,SUM(C1,1)) ‘copy down

and then apply conditional formatting based on the sequence.

Excel Tip - CF to make color bands1

Select the range A2:C29, then apply this formula to conditional formatting:

=ISEVEN($C2) ‘note the usage of $

Here’s the result!  It works great… until you apply a filter on subset.

Excel Tip - CF to make color bands

Why is that?

Because we are referring to a static (even) number for the color banding.  The number won’t response to the filter.  After the filter, only subsets of even number will be highlighted.  @_@

The desired result

We want the color banding to be responsive to filter, like the screen cast below:

Excel Tip - CF to make color bands0

With some twists to the formula for the helper column, this can be done magically.

You may download Sample File to follow along.  Continue reading

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

Unstack uneven data across columns with #Excel #PowerQuery

Excel Tip - Unstack uneven data with Power Query

This is a continuation of the previous post, in which I showed you how to unstack two columns of data by using Pivot Table as a helper, and together with a couple of excel tricks.  As mentioned, that approach is only good for one-off as the result is static.  When you expect the data to be changed frequently; or will have new data added to it on regular basis, you would absolutely look for a more robust solution that can be updated easily.  With Power Query, a simple act of Refresh will bring you the result in a flash.  Continue reading

Posted in Power Query | Tagged | Leave a comment

Unstack data across columns with #Excel Pivot Table

Excel Tip - Unstack data

Another commonly seen problem in daily work.  Many hours are spent on reshaping data, we all know that.  Nevertheless, if you are good in Excel, doing this task is not difficult at all.   Indeed, there are many ways to solve this problem.

In the following video, I am going to demonstrate how to solve this problem using a Pivot Table as helper steps.   This approach is quick and dirty, which can be accomplished in a minute or two.  However, as I said, it’s quick and dirty so you will expect limitation for this approach – it’s for one-off only.  Whenever your data changes, you have to do it again.

Let’s watch it in action: Continue reading

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

Waterfall chart is just a few clicks away with #Excel 2016

Excel Tips - WaterFall4

Did you know, you can plot a Waterfall chart in #Excel in less than a minute…Provided that you are using Excel 2016 or later! 🙂

No Kidding! You may download a Sample File to follow along. Continue reading

Posted in Chart | Tagged , , | Leave a comment

Split cell contents separated by line feed into rows with #Excel #PowerQuery – Part 2

Power BI Course.

Last week, I showed you how to split cell contents separated by line feed into rows with Power Query.  That was easy when we are dealing with only one column.  If you don’t know how, you may read the post here before you continue.

When we have two columns, like the screenshot below, it’s getting a bit complicating… but still very easy with Power Query.  It just requires a few more steps.

Split cells with linefeed (end)

Continue reading

Posted in Excel in Action, Power Query | Tagged , | 4 Comments

Free Excel Dashboard Webinars by Mynda Treacy

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

“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

Split cell contents separated by line feed into rows with #Excel #PowerQuery

After an in-house training, a colleague came to me and asked if there is a way to do the following:

Split cells with linefeed (Beginning)

The problem here is the contents in cells are separated by a line feed (line break).  A cell may contain two to five items and we want to split them into individual cells, into rows.

It reminded me of an old post How unorganized data could drive you crazy which I wrote about four years ago.  That’s the era before I know Power Query.

Although I used a trick of using Text to Columns to solve the problem before, this time the situation is different.  First, I want the cell contents to be split into rows, and unfortunately, we don’t have Text to Rows in Excel.  Second, we have more than one cells with this kind of unstructured data. 😦

This could be a nightmare for most Excel users.

But you know what, with Power Query, this can be solved at ease. 🙂

You may download a Sample File to follow along.
Continue reading

Posted in Power Query | Tagged , | Leave a comment

Identify missing files in a folder with Power Query

It is a common task to compare two lists in Excel.  This can be achieved with MATCH function.  But what if you want to compare a list of filenames stored in your spreadsheet, with the files you’ve got in a folder?

Excel Tip - Power Query to identify missing files in a folder

Will you do it manually, by eyeballing? @_@

The first challenge is to get the list of files you have in the folder.  The second challenge is that fact that you will receive files and put them into the folder from time to time.  In other words, the folder content is changing.  You absolutely want a dynamic solution that helps you to monitor the status at ease, so that you know which files are missing.

Here comes Power Query to rescue. Continue reading

Posted in Excel in Action, Power Query | Tagged | Leave a comment