everydayExcel Newsletter Header

Advanced Microsoft® Excel Tips and Tricks

The recent Tips and Tricks sessions organised for over 600 employees from an established bank in Singapore was a roaring success. The top 3 most loved tips were the Camera function, Date Formatting and Conditional Formatting. The highly positive feedback on the session on the Tips and Tricks has inspired us to organise a special public run for you so that more could benefit and discover what more could possibly be done with Excel. And to reward you for subscribing to our newsletter, I am going to sweeten the deal for you. When you sign up for the Tips and Tricks session, enter the promo code "2007" in the order form and you can bring along another person for free. This is good only for this run. Click on the Advanced Excel Tips and Tricks link and find out what you will be getting from the seminar.


Hidden Secrets in Excel Quick Access Toolbar

Excel Quick Access Toolbar is a very powerful tool yet for various reasons many people including Excel gurus tend to underutilize it. This toolbar gives you all the build-in functions available in Excel including those that are not seen in Excel ribbon (hence not known by most of the people). This article will show you one example on how this toolbar can drastically change your Excel experience.... continue

 


 Leave Planner

As promised, I am going to share with you this leave planner which can help you effectively track the leave of your team members so that you know who is on leave and when they are coming back from their leave.... continue


 Question and Answer

Question: I'm currently having difficulty in removing duplicated text within a cell. I've been deleting texts manually cell by cell. This is time consuming and definitely not productive.

Answer: You can eliminate the duplicated text with the following steps:

  1. Text to Column

    After selecting Column A, go to Data -> Text to Column.
    Select "Delimited" & click "Next"
    Check only "Space" & click "Next"
    "Destination" should not be on the original cell if you do not want to overwrite. I used $C$2.

  2. Find Unique Element

    After separating the individual words in the green columns, I count the number of the word repeats using the countif formula, at the same time, returning those words that have one count.

  3. Join to get the final results

    The End Result column is done by combining all the words in the blue column with spaces. I then use the TRIM formula to clean up unnecessary spaces.

The attached solution can be found in the duplicated text within a cell file attached.

If you have any question regarding Excel, please feel free to send it to us.


Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.


Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.


Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"

 

Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.