The missing link of HYPERLINK function in #Excel help

I seldom use the HYPERLINK function in #Excel.   Normally I insert hyperlink by CTRL+K, then setting the reference I want the link to go to.   That is super easy (or quick and dirty)!

Excel Tips - Hyperlink0

Note: You may go to Insert tab on ribbon –> Link

However, laziness comes with a price, i.e. limitation ==> Static link.  We cannot insert different hyperlinks based on the contents in a range of cell.  And that’s the reason I need the HYPERLINK function.    Here’s my story:

Excel Tips - Hyperlink

Recently, I created a workbook with more than 50 sheets.  For easy navigation for users, I created a summary page, with a table of content.  Well, 50 sheets!  And I am not going to insert 50 different links by doing the CTRL+K 50 times.  NO WAY.

Continue reading

Advertisements
Posted in Formula | Tagged | 2 Comments

#Excel Dashboard Course and Free Webinars by Mynda Treacy is closing soon

Just a quick reminder that registration for the awesome Excel Dashboard and Power BI courses, by Mynda Treacy closes on Thursday February 15, 8pm in Los Angeles, to be exact.

With demand for Data Visualization skills and Excel data analysis jobs set to explode, now is the time to get these skills so you can take advantage of these exciting opportunities. Demand for workers with these skills is also predicted to outstrip supply, and that means these jobs will command a premium salary.

Mynda’s courses will have you up to speed quickly so you can start benefiting ASAP.

Excel or Power BI?

Continue reading

Posted in General | Tagged | Leave a comment

#Excel Humor #13 – CEO(2)

Are you a CEO?

Excel Humor (13) - CEO(2)

Excel Humor (13) - CEO(2)1

Posted in General | Tagged | 2 Comments

20% off Excel and Power BI Dashboard Courses

Mynda Treacy’s popular Excel and Power BI Dashboard courses are back

Dashboard reports, are no longer a buzz word, they’re a standard reporting tool for all kinds of industries and they’re an in-demand skill for Excel users everywhere.

In fact, IBM project that the number of jobs for all data professionals in the United States will increase by 15% by 2020.

Demand for Data Visualization skills alone is projected to grow by 44% and PivotTable skills by 34%. That’s great news for us Excel and or Power BI users.

Until recently Excel was the go-to tool (for most of us) for building interactive dashboard reports like the one below:

The benefit in having Excel Dashboard skills, aside from the kudos from your boss and colleagues, is that these skills are often transferable to your everyday Excel work.

Continue reading

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

The making of an interactive CV in #Excel

What to do with Linked Picture to create something interesting in #Excel?

How about an Interactive CV to show off your Excel skills!?

First, why?

Once upon a time when I updated my CV, I was thinking…

Everyone states something like “Proficiency in Excel” in CV… ummm…  probably someone would put the word “Highly” in front of “proficiency” with an intention of standing out from competition.   Someone may even write a whole paragraph to explain what they do with Excel in plain texts; needless to say there would be lots of Excel jargon like VLOOKUP, PIVOT TABLE… etc.

While I was thinking how to describe my Excel skills in words, an idea flashed in my mind: “Why not showing my Excel skill in CV directly?” 

That’s why I came up with this Interactive CV.  By the way, it is so much FUN to do one, isn’t it?

Continue reading

Posted in Excel Tips | Tagged , , , , , , | 1 Comment

How to lookup an image in #Excel

Lookup an image using “Linked Picture” in #Excel

Excel Tips - Lookup image

Perhaps you have used VLOOKUP to return a value from a table very often.  However you cannot use VLOOKUP to return an image…  So how the above can be done?  You will need to know three tricks:

  1. Linked Picture
  2. INDEX & MATCH
  3. Named formula

You may download a sample file to follow along.

Let’s go through them one by one.  Continue reading

Posted in Excel Tips, Formula | Tagged , , | 3 Comments

#Excel Online Survey… Never heard about that!?

How to create an online survey with #Excel?

Did you know that we can conduct online survey by using Excel?  Yes, you heard me right.  I said: Excel!  I am not talking about setting up questionnaire in Excel and then send the Excel file to respondents.  Of course we can do that, but are you sure you want to do the consolidation afterwards.  I do not for sure!

What I am talking about is to create an online survey and then send a link to respondents to collect their responses.  (Access to internet is, of course, required)

Excel Tips - Online Survey7

And the cool thing is… all the responses submitted will be loaded into an Excel table stored in your One Drive.  You can imagine how many hours could be saved from consolidating the responses.  😛

So, how to create an online survey in Excel?  Continue reading

Posted in Excel Tips | Tagged , | Leave a comment

Season’s Greetings 2017

Ho…Ho…Ho… Merry Christmas!

Wish you and your beloved ones a Merry Christmas and a Happy New Year of 2018!

Seasons Greetings 2017

The above is made of #Excel of course… You may download a copy Seasons Greetings 2017 if you like it. 🙂

Posted in General | Tagged | 2 Comments

#,##0, k or #,##0.0, k ? Why not both?

Conditional Custom Formatting in #Excel

Got the following question:

This gives me £28.8 K (if 28800 is input) and £28.0 K if 28000 is input. I was rather hoping that if there was no decimal after the thousand, ie £28 K, this would end up as £28 K, and not £28.0 K, but I guess that is not possible.

Here’s a screenshot illustrating the question:

Excel Tip - Excel Format with Conditions

Is it not possible????? Nothing is impossible, especially during Christmas.  🙂

Before we dive into the steps to achieve it, let’s see the custom formats for $28.0 k and $28 k first: 

$#,##0.0, k --> $28.0 k

$#,##0, k --> $28 k

See the tiny difference?   Continue reading

Posted in Excel Tips | Tagged , , | 6 Comments

Get a list of pending tasks and sort by due dates

A common task – Get and sort a list of tasks that are not yet due according to due dates

Got the following question:

Suppose on column A I have entries and on column B i have due dates for the entries. Is it possible to have excel automatically rearrange the ROWS such that the nearest dates appear at the top and the furthest dates at the bottom? Thank you in advance

First thing on my mind: This can be done easily by sorting column B in ascending order.

Then I stop and rethink… maybe the reader wants to list the tasks that are not yet due… and probably on a separate table.  And even more, new tasks with new due dates will be added from time to time and Excel should be able to get him updated list of tasks not yet due “automatically”… That makes sense and I believe it’s a common task for many people.

The following screen cast visualize the request:Excel Tips - Due date final

And this is achieved with a simple helper column + Pivot Table.   Continue reading

Posted in Excel Tips | Tagged , | Leave a comment

Learn Excel Dashboard skills before end of 2017

Can’t believe it is already December.  Is learning Excel Dashboard one of your 2017 new year resolutions, that has not yet accomplished?

If so, here’s a good news for you: Mynda Treacy’s popular FREE Excel Dashboard Webinars are opened for the last time in 2017.

Free Excel Dashboard Webinar

You could learn awesome Dashboard skills by watching the webinars alone.  If you are serious about taking your Excel skills to the next level, you should really take time to learn more about Excel Dashboard.

What’s next after the webinars?

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.  If you want to acquire the Excel Dashboard skills, you may enroll Mynda’s 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.

20% early bird discount ends Thursday, December 7

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

Excel Dashboard Course

Want something hot to be cool?  Don’t miss the Free Webinar of Power BI too.

Power BI Webinar

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

Posted in General | Tagged | Leave a comment

Insert a text string into a specific position of another text string in #Excel

Turn REPLACE function into “INSERT” function

Sometimes, we want to add a text string, say “XX”, as a prefix or suffix to another text string.  This is quite easy with the function CONCATENATE, or even easier with the operator &.  However, what if we want to insertthe text string into a specific position in the middle of another text string ??

Excel Tips - Insert string

The function REPLACE comes to rescue.

Excel Tips - Insert string1

umm… REPLACE?

Excel Tips - Insert string2

According to its description,

it replaces (not insertspart of a text string with a different text string.

Shall we use a FUNCTION called “INSERT”?  Unfortunately there is no such “INSERT” function, as REPLACE could do it for us.   Continue reading

Posted in Formula | Tagged , | Leave a comment

#Excel Humor #12 – Power Voice

Did you experience the Power Voice for #Excel?

Excel Humor (12) - Power Voice  Like this post if you did.   😛

Posted in General | Tagged | Leave a comment

Data transformation with Power Query

Transform date input as “Excel-unrecognized” text string into date (number) usable by #Excel

This is a continuation of the previous post – How to turn “1st January, 2017” into #Excel recognizable date?   …using Get & Transform (better known as #PowerQuery).

Excel Tips - Date format from text to number with PQ

Note: All screenshots and steps in this post are based on Excel 2016.  The ribbon of Power Query for Excel 2010/2013 may be different a bit… but the steps and interfaces should be more or the same.

The problem was discussed in the previous post.  So let’s go straight to the solution:

Continue reading

Posted in Power Query | Tagged , | Leave a comment

How to turn “1st January, 2017” into #Excel recognizable date?

Quite a long time ago, I wrote a post to discuss a trick to format date with “st”, “nd”, “rd”, etc…

In this post, I am going to discuss the reversed way: Turning an English written date with “Dst MMMM, YYYY” into date (i.e. number) that Excel recognizes.  The following screenshot illustrates the situation:

Excel Tips - Date format from text to number.PNG

What’s the problem?

First of all, let’s understand what the problem we are facing here.  The date was presented as a real text string, not a number stored a text.  For instance, “21st December, 2017” is a real text string.  That means we cannot turn that text string with simple tricks discussed before.  Luckily, we are dealing with Excel problem that can be solved by formula.

Here’s the solution:

Continue reading

Posted in Formula | Tagged , , , , , , | 8 Comments

Repeat all item labels in Pivot Table (aka Fill in the blanks please)

Quite a long time ago, I wrote a post Fill in the blanks – quickly to talk about the tip to turn a human-reading-freindly table into an Excel-friendly table, as shown below:Excel Tips - 10.1

Have you ever thought about why we are doing this?  Of course you know because we need the “right” table for further analysis.  Let me rephrase my question: Where is that Excel-unfriendly table on the left coming from?  Bingo.  #PivotTable in #Excel itself.

Most of the time (in my experience) your colleagues summarized / aggregated data in a pivot table, then copy and paste it as value before sharing to you… Sound familiar?

This kind of problem is not uncommon, even nowdays.  Why I am saying this?  Because there is an easy way to fill in the blanks in Pivot Table itself but not many people are aware of it yet. Continue reading

Posted in Excel Tips | Tagged | 3 Comments

Extract part of text string with delimiter(s)… and more with Text to Columns

In the previous post, we talked about the basic of Text to Columns and focused on “Fixed width”.  In this post, we will continue to talk about three magical things that Text to Columns could do with ease:

  1. Extract part of text string with delimiter(s)
  2. Convert numbers with trailing minus, e.g. “123-” into number
  3. Convert numbers with “,” (comma) as decimal point into number

You may probably know about the first one,  but be honest did you also know the second and third one?  🙂  Let’s take a quick tour to all of them.

You may download the Sample File – Text to Columns (2) to follow through.

Extract part of text string with delimiter (s)

Situation: This is quite common, I guess.  Let’s continue with the example discussed in the previous post.  The task is to extract Latitude and Longitude from the text string in column A: Continue reading

Posted in Excel Tips | Tagged | 8 Comments

Extract part of a text string using Text to Columns

There are many not-so-easily-understood terms in Excel. “Text to Columns” should be one of them.  Probably due to the name of it, Text to Columns is not a common tool for many Excel users.  But indeed, if you know what it does, you will love it.

What is Text to Columns?

It is one of my best buddies in Excel.

It is a great tool to extract specific text from a text string without the use of a single formula.  It is a great news for those who are not familiar with formula yet, isn’t it?

When you hover your mouse on the icon of Text to Columns under Data Tab on Ribbon, you will see the description about it…

Excel Tips - Text to Columns

Is it much clear now?

Tip: You may see a better description and sometimes even tip of using a tool when you hover your mouse onto its icon on Ribbon.   Try the same to Format Painter.

Most of the time, we will use a “Delimiter” for Text to Columns.  And the “Fixed width” is somehow forgotten.  In some occassions, “Fixed width” is very handy and useful when we deal with text string with constant pattern.

In this blogpost, I will focus on using “Fixed width”.

Continue reading

Posted in Excel Tips | 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, the answer is simple: it is awesome.  You could learn awesome Dashboard skills by watching the webinars alone.  If you are serious in taking your Excel skills to the next level, you should really take time to learn more about Excel Dashboard.

What is 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 advanced 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.  Let’s take just one hour to watch one of the webinars, you will see!

 

Webinar 1 – Excel Dashboard for Excel 2007/2010/2013/2016

Excel Webinar

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, analyze it in PivotTables to create this interactive Excel Dashboard.

Excel Dashboard with Power Query

But hurry seats and sessions are limited. Click here to register for the free webinars.

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

What’s next after the webinars?

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.  If you want to acquire the Excel Dashboard skills, you may enroll Mynda’s 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.

20% early bird discount ends Thursday, October 5

So, go ahead and enroll here before the discount ends on October 5, at 8pm in Los Angeles to be exact.

Excel Dashboard Course

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

Posted in General | Tagged , | Leave a comment

Data Validation to restrict user from inputting to future days

The situation

We need to input certain data on a daily basis.  For example, daily sales.  That is supposed to be an easy and routine task for many people.  BUT… it happened that we still have a chance to input data to a wrong date, e.g. tomorrow… like below:

Excel Tips - Data Validation to restrict input to future days

Seriously?  We need to worry about and protect our spreadsheet for such a simple task?

Yes.  Why not?  Indeed not many people know we can do that in Excel.  It is quite simple if you know how to use Data Validation – Custom.

Avoid mistaken input to future days

To avoid that, we may set a simple Data Validation to the cell range B2:B31: Continue reading

Posted in Excel Tips | Tagged , | 2 Comments