Adding bullet to each line in cell

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

Advertisements
Posted in Excel Tips | Tagged , , | 4 Comments

Quick Tip: Undo the “Undo”

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

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

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

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]

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

 

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?

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…

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 , , | 18 Comments

Go to Special + Copy and Paste do the magic

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

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

The 5-minute Excel Dashboard Challenge @ Bacon Bits

Dashboard Challenge.gif

Have you ever thought of building an Excel Dashboard in 5 minutes?  Yes, only 5 minutes. That’s why it is a Challenge!   You may find the details of The 5-minute Excel Challenge at Bacon Bits.

Honestly, I haven’t thought of building a dashboard in such a short time.  In my experience, it takes hours (if not days) of work to create a dashboard.  Nevertheless, after reading the very last sentence of Mike’s post regarding the challenge:

“I’m very much looking forward to seeing all the cool things our Excel community can create!”

I’d decided to take the challenge.   And I managed to finish it in four and a half minutes! 🙂

As I am working in retail industry, I take the challenge with something I am familiar with: Store KPI Dashboard.   Continue reading

Posted in General | Tagged | 4 Comments

Paste special with simple mathematic operation + – * /

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

The task: Doing simple calculation (add, subtract, multiply, divide) without formula

Answer:  Copy and Paste Special…

excel-tips-copy-and-paste-with-operation Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Happy New Year of Rooster


Hi everyone,

Today we celebrate the Chinese New Year🐔. No Excel tips but CNY greetings and blessings.  Wish you a prosperous and rewarding new year ahead! Continue to excel.

Cheers, MF

Posted in General | Leave a comment

Fill so good…

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

Fill a series (of sequential numbers)

The task: Fill a big series of sequential number, say from 1 to 24567.

Answer:  You may find the answer to the question in this post:  Fill Series.

That post was indeed inspired by one of the winning MEME posts found in Bacon Bits.

Check this out for some Excel FUN:  http://datapigtechnologies.com/blog/index.php/excel-meme-contest-winners/

Want more?

Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Transpose Data (Static and Linked)

Answer to the 5 little Tricks posted in the beginning of the year – Part 1/5

Transpose Data – Result is static

This is actually a simple trick of using Copy and Paster (Special, Transpose).  See? It can be done in a second. Excel Tip - Transpose Data - opening.gif

Nevertheless, as I mentioned in previous post, many people are not aware of this simple trick even though they work with Excel every day (they work in accounting field).  What they did was to move cell one by one… @_@ Continue reading

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

5 Little Excel Tips (tests)

First of all, Happy New Year of 2017!  Wish you all an Excellent year ahead!

Let’s start the year with a simple “Game” which I developed for in-house Excel training in my previous company.  All participants were expected to have basic Excel knowledge.  Many of them worked with Excel on daily basis.  I had delivered the training to over 100 colleagues with this “Game” as warm up exercise.

The “Game” covers five Excel tricks.   To me, and probably to most of you, they are quite basic and simple.  However, to my big surprise, there was NO participant (out of the >100 participants) who could complete the “Game” successfully in 5 minutes.

You may see the short description of the five tasks below: Continue reading

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

Merry Christmas

Christmas Tree 2016.gif

Dear readers,

Merry Christmas!!!

Wishing you and your family a healthy, fruitful, rewarding and Happy New Year of 2017!

See you next year!

p.s.  Should you want to play around the Excel Card above,  here’s the christmas-tree!

Tip: Hold F9 for the snowing effect.  Hope you like it. 🙂

Posted in General | Tagged , | Leave a comment

A tip of using AutoCorret to input names quickly

This is a simple tip that most secretaries and executive assistants love. 🙂

Excel Tips - AutoCorrect0.gif

Did you ever look up someone’s full name in Outlook or email directory?  It happens especially if you are working in a multi-national company and you are working with people from around the world.  Some foreign names are really difficult to spell.  Right?

So why not we input them into AutoCorrect and let Excel/Word/PowerPoint to remember the spelling and type it lightening fast for us?  Isn’t it nice if we just need to input the initials?   Continue reading

Posted in Excel Tips | Tagged | Leave a comment

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