10 Excel Tips to Make Your Life Easier - Part 2
As I promised in my previous post, here is part 2 of my 10 Excel Tips to Make Your Life Easier video tutorial. Tip #10 is especially useful if you’ve ever hated working with strings and string/text functions in Excel. Also, if you haven’t had a chance to view part 1 of this series, you can find the video by clicking on the link below:
Also, if you’re new to Viddler, you can click on the time tags and comments (shown as dots in the time line) to access the different tips directly, or you can just watch the video in its entirety. Anyway, here’s part 2 for your viewing pleasure.
10 Excel Tips to Make Your Life Easier - Part 2
If you’re unable to see the video below, CLICK HERE to view it on Viddler.
Tip #6) Utilizing Quick Charts
- Create a Chart - F11
Tip #7) Easily Remove Repeated Data
Tip #8) Inserting Line Breaks & Today’s Date in a Cell
- Insert a line break into a cell - Alt + Enter
Insert today’s date in a cell - CTRL + ;
Tip #9) Insert a Picture in a Comment
Tip #10) Manipulating Strings with Functions
- Functions used in Tip #10:
- Adding Strings:
- CONCATENATE(text1,text2,text3) = text1text2text3
- Or by formula, =text1 & text2 & text3 = text1text2text3
- Remove leading or lagging spaces from text - TRIM(text)
- Grab specified number of characters from left or right side of string:
- LEFT(text,[number of characters]) for example LEFT(text1234,4) = text
- RIGHT(text,[number of characters]) for example RIGHT(text1234,4) = 1234
- Opposite of CONCATENATE - go to Data -> Text to Columns..
And as always, please feel free to leave a comment if you have a question about anything I’ve covered in this video. Also, feel free to leave any additional Excel tips that might be of use to Excel users!
Sincerely,
The Closet Entrepreneur
14 Comments
mark
Hey Tomas, nice videos. How do you create these videos? Software or with a camera?
Dec 29th, 2006
TOMAS
Hey Mark, the videos were created using SnagIt software. It’s an older version from 2001 that I had laying around. There’s an open source software called CamStudio that does the same thing, which btw I’ve downloaded but haven’t had a chance to mess around with it yet. There’s also another screen capture program called Camtasia that’s made by the creators of SnagIt. They have a trial version in case you want to try it out (I haven’t had a chance to try it myself).
I created a series of AVI’s using SnagIt then stitched them together using a free video editing program called AviTricks Classic. I tried using Windows Movie Maker and Ulead DVD MovieFactory 2 for video editing but they both had issues with the AVI’s I had created.
AviTricks lets you overlay jpeg and transparent gif images, so I used Photoshop to create the different splash screens; AviTricks also lets you insert audio tracks. Unfortunately, I wasn’t able to use the fade function to transition between clips, but that would’ve added a nice touch.
Anyway, I hope that answers your questions!
Dec 30th, 2006
Jen
Thank you for this
it really helped me a lot for my reports
How did you do this, hope you can teach me as well?
Have a nice day
Mar 31st, 2008
TOMAS
Hi Jen, the videos were created using screencapture software - I would recommend trying out Camtasia by TechSmith. You can learn more about it here: http://www.techsmith.com/camtasia.asp
I’m hoping this answers your question, yet feel free to drop another comment if I didn’t. BTW, glad to hear that you were able to make use of the tutorial!
Apr 1st, 2008
Jen
Hi Tomas,
Thanks for the information you shared with me regarding camtasia, Its a great application. I will try to work on the trial version first.
BTW, would it be possible for you to create another tutorial for pivot table and vlookup in excel in case you have time? I’m sure a lot of people will benefit from it as well
have a great day
Apr 3rd, 2008
TOMAS
Hi Jen, I have to be honest with you, my use of vlookup has been very minimal - I’ve used it before, but not sure how competent I am to actually make a tutorial out of it. Yet, anything is possible right?
As for Pivot Tables, anything in particular that you would like to know?
Apr 4th, 2008
Jen
Hi Tomas,
I appreciate your honesty regarding vlookup
I also agree with you that anything is possible…
As an example for the pivot table… would it be possible to know which items sold better in a financial quarter?lets say a store owner might list monthly sales totals for a large number of merchandise items in an Excel spreadsheet….i hope im not confusing you
Anyways thank u in advance
Apr 14th, 2008
TOMAS
Hey Jen, no problem. Actually, if you have some specific data or faux data in an Excel spreadsheet that you can share, feel free to email it to me at tomas[at]theclosetentrepreneur.com and I’ll be more than willing to take a look at it to see if I could answer your question. Again, it doesn’t have to be actual data but any data that is currently in the format and structure you’re using would be helpful.
Apr 15th, 2008
Dyllos
Hi
Just wondering if you or anyone else knows how to insert a line break inside the concatenate function in Excel?
Cheers
Dylan
Jun 15th, 2008
TOMAS
@Dylan - I’ve seen some people use the CHAR(10) command to signify a line break, although line wrapping has to be enabled through the format option and the cell width has to adjusted.
Here’s an example:
=CONCATENATE(A1,CHAR(10),A2,CHAR(10),A3)
Jun 16th, 2008
Dyllos
Excellent! That works very well, thanks a lot for your quick reply. I can now do a basic kind of “mailmerge” workround using Google spreadsheets: http://chocoholicsproject.blogspot.com/
Cheers
Jun 16th, 2008
Mani
Great tips..very useful. Although i do not use much of the parts in part2, part1 is very very handy. Thanks mate.
Jul 19th, 2008
TOMAS
@Mani - I’m glad you found the tips useful. I’ve been thinking of doing a new round of video tips by breaking up some of these tips and expanding on them - hopefully making them more accessible and easier to learn from. Of course, I say this when I’m super busy.
Jul 20th, 2008
Leave a Comment