Instructor for this course
more

In this course Excel expert David Ringstrom, CPA shares many Excel tips and tricks that he uses to fly through Excel every day. He's fond of stating "Either you work Excel, or it works you!" This course is designed to help you use Excel more effectively. In this course David teaches primarily from Excel 2010, but covers differences in Excel 2003, 2007, and 2013 where applicable.

Topics Covered:

  • Discover how to quickly access folders and workbooks, whether on your computer or a network    
  • Convert Excel 97-2003 files to modern workbook formats with ease.
  • Quickly clean-up accounting data by way of Excel's Text to Columns feature.
  • When Text to Columns won't do, use the LEFT, MID, and RIGHT functions to extract text from a cell.
  • Transform text by way of Excel's UPPER, LOWER, PROPER, and TRIM functions.
  • Learn the most efficient way to link Word documents and Excel spreadsheets
  • Edit PDF documents in Word 2013--useful for unlocking data you wish to edit in Excel.
  • Learn the right and wrong ways to make Excel appear on two or more monitors.
  • Avoid starting spreadsheets from scratch: use prebuilt templates, or create your own!  

                                                                                                                                                                  

 

Intro Video Transcript

Excel Efficiency: Tips and Tricks By David Ringstrom Hello, I am David Ringstrom, A CPA from Atlanta. Welcome to this Illumeo course session titled “Excel Efficiency Tips and Tricks Part 1. This is the first part of a series, and in this series I will be discussing numerous ways to use Excel more effectively. I’m very fond of saying “Either you work Excel, or it works you.” And what I try to focus on in my sessions are the nuances of Excel that cause folks to get tripped up or spend more time doing work than they should. So, this session is designed to empower you to put more tools in your toolbox and to help you avoid frustration when you’re working with Excel spreadsheets. I’m going to be teaching from Excel 2010 today but pretty much everything I cover is going to be in the same location in Excel 2013 or Excel 2007, if there’s any big differences then I will make notes of that on the corresponding slides. The biggest difference that you will note is that Excel 2007 does not have a “file menu” so during this presentation if you see me click on the file menu or I mention the file menu and you’re using Excel 2007 just note that you will click on that little round button that called the “Office button” instead. If you happen to still be using Excel 2003 then some of the slides will have notes in blue along the bottom of the screen and those are the equivalent steps that you would carry out in your version of Excel. I have been using Excel for many more years than I care to recount and so of course that means I have a lot of files that are still in the old “XLS” format. Those are partly still around because only recently did Microsoft finally cut off all support for Excel 2003. So, up until recently we did have a lot of clients that were still using Excel 2003, now it’s a tiny fraction of users at least that I cross paths with, and we can certainly open up these Excel 2003 books in Excel 2007 later, they have an XLS extension, and two things we need to keep in mind; I have made myself crazy more times than I care to think about it, trying to do something in Excel 2007 and late in these old workbooks found that I couldn’t do it because the work-book is in what’s called “compatibility mode.” We can see where compatibility mode shows up on the screen and have a red tip. If you see compatibility mode, just know that translates to, you’re not going to be able to use certain features in Excel as long as you keep that workbook in the XLS format. So such as in Excel 2010 and later you won’t be able to use spark lines or the slicer feature and there’s a number of other features, the little subtle nuances where you’ll try to do something you do all the time, you won’t be able to do it because you’re in an Excel 2003 workbook but Excel won’t tell you that, the feature just won’t work the way you expect it. So, as you run across these old Excel 2003 workbooks, if we go to the Step 1: file menu in Excel 2007 and later, we can click the “convert” button. Step 2: It’s going to show up automatically under ‘info’ and when we choose convert; we’ll get a download box that will give us some yada, yada about the process, Step 3: We can click the ‘check box’ then we don’t have to see that prompt again and click the ‘okay’ button. But then step 4 is very important. Step 4: whenever you are changing a file type of a workbook, you always want to make sure you close the workbook and reopen it because that transition does not fully occur until you close and reopen the workbook. So, let’s say you’re in Excel and you have a workbook that’s in XLS format and you decide ‘I’ll just go to the file menu and click ‘save as’ and save it in the new format’ in that case you’re going to need two copies of the document. One of the benefits of the convert feature is that it eliminates the old XLS file, so it basically does a swap, you trade out the old format for a workbook that has the new format and as long as in “Step 4” you click yes and let Excel close and reopen it, then you’re going to have all the functionality in Excel and you’re not going to beat your head against the wall “why can’t I do this in Excel?” it will just be some silly thing, because you open up a workbook that is still saved in the Excel 2003 format that again, has that XLS extension. To get a sense of how the convert feature works, let’s take a look at this file here, it says compatibility mode at the top and has an XLS extension, that’s how I know my functionality is going to be limited. To give you a sense of that; if I go to the ‘insert’ tab, the table feature is disabled but that’s simply because this workbook has a table in it. So that’s not always necessarily an indication that a feature can’t be used, it just means that I’ve already used the table in this particular worksheet. If I go back to another worksheet, we see the table button lights up but conversely spark lines are disabled, slicers are disables and as I work across, we’ll find that different functions and features will be disabled. Very subtle, very frustrating when that happens, so in any case to avoid problems with this as you run across these of XLS workbooks. Go to the file menu and look for that convert command; it will ask us if we want to convert it, we click okay. Click the check box so that we don’t have to see this prompt again and then click okay. This is the key step, if I said no here, then I go back to insert tab, slicers are still disabled even though it says XLSX that’s because Excel is still in compatibility mode. The compatibility mode doesn’t go away until I actually close the file and reopen it. So, we’re going to try that again, we’ll open up a different file, close this one here. Go to file  menu  recent, for example fruit.xls click open, click on save as and save it back to the desktop Then go to file menu  convert, click okay, next prompt click yes. The file closed and reopened, it happens instantly, now my spark lines are lit up, my slicers are available and also, if I went back to that folder where the file was saved (example: desktop) and click on ‘open file location’ the original fruit.xls is no longer available, it was replaced with a fruit.xlsx version.

Course Series

This course is included in the following series:

2 CoursesTips and Tricks for Excel Efficiency

  • Excel Efficiency: Tips and Tricks I
  • Excel Efficiency: Tips and Tricks II

Learning Objectives

  • Identify approaches to avoid disabled features in Excel 2007 and later by converting XLS workbooks to the newest format
  • Combine text into one column or separate it into several by way of simple Excel techniques
  • Utilize Word 2013's ability to edit PDF documents to get report data into Excel
Last updated/reviewed: September 18, 2018

145 Reviews (404 ratings)Reviews

5
Member's Profile
Just a really great and special program, thanks for brining it to me.
3
Anonymous Author
I liked that I learned few simple tricks I didn't know before. I didn't like the "still picture" set-up used in the video, it was almost like “audio only” training. There's plenty of softwares that allow you to do webinar videos by recording every step you do in your screen. Learning with this latter method (fully utilizes audio and video features) is a lot easier than just using pictures in a video.
5
Member's Profile
The linking of Excel to Word was nifty and useful, Unfortunately it seems like my versions of Excel and Word were somewhat different and I was not able to get the desired results. Excel did its trick and I seem to update from the excel automatically. My problem was getting Word to produce a printout the same as Excel, all on one sheet.
4
Member's Profile
Good course with some helpful information. Especially helpful were the instructions for editing PDF documents without the expensive version of PDF and the extensive built-in Excel templates. It does get tedious trying to remember the changes between the different Excel versions, but it can be useful information.
4
Anonymous Author
The course is very informative. The speaker did a great job in showing and explaining all the tips and techniques so that Excel will be as easy as an ABC. The only downside is the audio, there are times in the presentation that you will hear an annoying sound and the listener will be distracted from it.
4
Member's Profile
Stated learning objectives were met Stated prerequisite requirements were appropriate and sufficient Program materials were relevant and contributed to the achievement of the learning objectives Time allotted to the learning activity was appropriate Instructor(s) was effective
3
Member's Profile
The video is outdated should follow the format of some of the more recent excel courses that have been uploaded to illumeo. Slides for a video is not very effective and hard to follow. The live feed of where the instructor's cursor was from other videos is much more effective.
4
Anonymous Author
Not a bad course - bit difficult to follow along on a practice spreadsheet at times but the pause button is a useful tool. Also, not all might have Excel / Word 2013 or better so some features or function explained either remain theoretical or can't be practiced.
5
Anonymous Author
I've taken several excel courses from David Ringstrom and they are always informative. In this one, I was hoping to be able to use the slides as a reference, but they are just too busy to follow compared with earlier courses. Still good material, though.
Anonymous Author
I really liked learning the excel tips in this course. I felt though that the slides weren't that easy to follow and the sound quality was a bit poor in different sections. Also the instructor was going a little too fast at times.
5
Member's Profile
Хочется больше видео непосредственной работы с Excel, а не разговоров об этом приложении. В целом считаю данный курс полезным
5
Anonymous Author
David does an excellent job of showing you useful tips in Excel. The slides are a little hard to follow as they have too much information on each one. The sound quality is up and down throughout the presentation.
4
Member's Profile
I liked the information on how to find templates and I liked the demonstration of what the speaker was doing. I was surprised that you could find templates that already existed. This is a course for beginners
4
Member's Profile
The instructor was great and the information very useful and well presented. The only negative was that most of the video was still shots. I learned a few new Excel tricks that will be very handy.
4
Anonymous Author
Learned areas of chart that I did not realize could be easily transformed for simplicity. Good to have a verbal, showing number sequence and then a follow through with Excel in motion. Good job!
4
Member's Profile
Great course in various tips and tricks of excel. Even though I have been a long-time user of excel, I was not aware of how you could edit pdf documents in Word and then bring them into excel.
3
Anonymous Author
Disappointed. Worst course I have taken from David to date. Slides were convoluted - hard to follow. Section 6 - Linking Excel-Word kept stopping and starting. Took almost 2 hours to complete.
4
Anonymous Author
Thanks for the nice course The final test does not really give a picture on how well you learned during the course I think that the solutions should be provided after the test is passed
5
Member's Profile
I liked some of the formulas I was unfamiliar with. One downside is all of the different versions of the software requires the lesson to be broken up a lot (not at the fault of the course)
5
Member's Profile
I was impressed by the number of tips I didn't already know. Thank you for increasing my Excel skill base. The sound quality could have been better I noticed a lot of background static.
4
Member's Profile
Recovering files was useful as was the pinning and unpinning of files. The examples sued for Text to columns were helpful. Overall learnt a few things that will help my efficiency
4
Anonymous Author
Great course. Even though I use Excel constantly, I found the course worthwhile as I got reminders of things I had forgotten and/or learned some tips to make me more efficient.
4
Member's Profile
Even though a lot of this was very basic, I picked up a lot of good tricks that will save me tons. I won't have to re-type in the future if I forgot to take off the cap locks!!!
5
Member's Profile
Great tips on excel, looking forward to Tips and Tricks 2, for someone who knows excel pretty well, it's always fun to learn things to make your experience faster and better!
5
Member's Profile
At some point during the presentation he moved away from the microphone and it was a little harder to hear him. Otherwise the presentation was great! I learned new things.
4
Anonymous Author
This course has some good tips for everyday use. The instructor has a very clear methodology on showing the tips and techniques. Will find other courses by this author.
5
Member's Profile
Quick course on many tips and tricks that helped eliminate extra work. There were multiple tips so if you already knew one you could skip through that one. Good course!
5
Anonymous Author
Great course. Love the 'interactive' presentation method. I always come away from David's courses with a tip, trick or tool that I can use to improve excel efficiency.
5
Member's Profile
Compared to other excel based courses, this course makes it a bit difficult to follow along as training is displayed with pdf slides as opposed to excel spreadsheets
4
Member's Profile
Very good course. Information was clear and concise. The activity sheets were effective. Instructor was engaging. I really enjoy the Excel courses on this website.
4
Anonymous Author
I thought overall it was a great course. I actually liked the format of first showing a still slide and then next capturing a video of how to navigate that slide.
5
Anonymous Author
Liked the formatting of the course - the slides are helpful to have for a reference and then the instructor goes through what was covered on the slides live.
4
Member's Profile
Very knowledgeable. I learned a lot. Only feedback to improve, there is a lot in the screen shots. Hard to see it all, and focus. Maybe break it up more?
5
Anonymous Author
I really like the concatenate, transforming functions and learning how to link word and pdf documents. This will make using excel muck more efficient.
4
Anonymous Author
Mentioned a lot of insignificant things that will assist me with my work: push pin recent files, use of two monitors and removing extraneous marks.
5
Member's Profile
I could not follow along on my computer from about mid-way as I have 2010. But I was able to retain the information as he explains things very well.
3
Member's Profile
Good course, learned some tips and tricks. Would be nice if you could choose your version of Excel to eliminate info that does not pertain to you.
4
Member's Profile
Introduced some new tips that were not necessarily formula based but useful all the same especially when dealing with complex text manipulation.
5
Member's Profile
Learning the differences between excel versions is helpful because not every work environment will have the same version that I am used to.
5
Member's Profile
I like all the Excel tips and tricks introduced in the materials. I am using Excel 2016. Hopefully the updated course will be here soon.
5
Member's Profile
I had done many of these functions shown in this course through many manual steps. I will keep these examples on hand for future use.
3
Anonymous Author
Some things were straightforward, other times, it was hard to hear him. Overall though, the formulas he taught us were very helpful!
5
Member's Profile
Liked that the instructor talked quickly. In addition, he said what he was going to show, and then went through the actual steps.
4
Anonymous Author
There were quite a few useful tips that I didn't know about. Definitely helpful intro training for people new to or rusty at Excel.
5
Member's Profile
Great pace and great visuals. There was a wide enough range in the tips provided to show useful tools, but it wasn't overwhelming.
4
Member's Profile
It was very informative, i actually really enjoyed the PDF to word to excel trick. Felt it was a bit long for what was covered.
5
Anonymous Author
As always, tips I can use most every day working with Excel. I will keep these close by so that I can refer to them in a pinch.
4
Member's Profile
Course was overall very informative. Great way to explain how to use the text to column and concatenate functions in excel.
3
Member's Profile
It would be better if the items were shown in motion (vs. the slide with numbers). Also the sound quality was a bit poor.
5
Anonymous Author
David Ringstrom is an awesome teacher for excel! He does help you with practical problems if you send him your worksheet.
3
Anonymous Author
I learned some good tricks in this course. There was a humming noise through parts of it that was distracting though.
5
Member's Profile
This course gives you useful efficiency tools to help with Excel. I wish I knew a few of them before I started my job!
3
Anonymous Author
Good Info but want to see more on screen not just still slides. Very poor sound quality on many of the slides.
5
Anonymous Author
This course covered some seemingly basic concepts that are huge time savers if you are unaware of them.
5
Anonymous Author
The upper, lower,etc and the trick of expanding Excel onto 2 screens without opening a second version.
4
Member's Profile
The professor game a lot of different examples and definitely showed me some shortcuts I didn't know.
4
Member's Profile
A little dull at points, but overall thorough look at elements of excel that will be useful for work.
3
Anonymous Author
Audio had quite a bit of feedback in places; slides were busy and in some cases difficult to follow.
5
Member's Profile
better than expected. I learned quite a few things and I am already a pretty advanced user of Excel.
5
Member's Profile
Mr Ringstrom has once again highlighted the features that can assist the everyday Excel user.
3
Member's Profile
I knew a lot of the content already, however there were some neat little tips incorporated.
4
Member's Profile
It was good to learn about the different years of excel and which years have what functions
3
Anonymous Author
Basic review of select Excel topics with some material perhaps not commonly encountered.
3
Member's Profile
I prefer to see every step being done and not looking at it from a numbers screenshot.
5
Member's Profile
I found this course is pretty useful. Some functions I've never used before. Thanks.
5
Member's Profile
I learned some new things! Well taught course that covers some basic tips and tricks.
4
Anonymous Author
Some really great time saving tips, but some of the screenshots were a little busy.
4
Member's Profile
Helpful overview of the material. Enjoy the concatenate function and use it often.
5
Anonymous Author
Good information on features I was not aware of in the various versions of Excel.
5
Member's Profile
Great help..do you have a good trick to compare text in two columns for matches?
4
Member's Profile
The materials are clear and detail. Use cases are simple and easy to follow.
4
Anonymous Author
Great review and reminders of things that I don't always use on a daily basis.
3
Member's Profile
The overlap of information on the slides makes it hard for reference purposes.
4
Anonymous Author
Very informative information that can be used on an daily basis in workbooks.
5
Member's Profile
Very helpful and presented in a way that would help me remember these tips
4
Anonymous Author
Wish I knew which question I got wrong and what the correct answer was.
4
Anonymous Author
I enjoyed the training and learned a lot of tricks and tips with excel.
5
Anonymous Author
Long time excel user and found many tips that I will now use regularly
3
Member's Profile
Some helpful hints but difficult to see and follow pictures presented.
5
Member's Profile
Very good for beginners and not so beginners. I strongly recommend it.
4
Member's Profile
Good review of some things I already knew and some new useful tips.
5
Member's Profile
Loved how the pages showed all the steps, with lots of graphics.
4
Anonymous Author
Great tips and tricks, especially the 2 screens tip. Thank you.
4
Member's Profile
Great course. I learned many tricks I can use in the future.
4
Anonymous Author
A little outdated at this point, but still very well explained.
4
Member's Profile
I liked the extra tip of deleting objects within a workbook.
5
Member's Profile
Learned quite a few new tips & ideas. Good refresher course.
3
Member's Profile
Okay course not great 123456789012334 . 45667 ttyytwrrttttwb
5
Member's Profile
Thanks for the great info, I enjoyed learning from the video
4
Anonymous Author
Very informative, basic look at what excel can help you do
4
Member's Profile
good tips to link excel and other files like word and pdf
4
Member's Profile
some of it went a little fast but overall a great help!!
4
Anonymous Author
I learned some quick easy tips that i could use daily
3
Member's Profile
works for what i need to renew my cpa and cia licenses.
2
Member's Profile
Not terribly relevant now that Excel 2016 is available.
4
Member's Profile
Some useful information, especially working with text.
4
Anonymous Author
Some pretty solid tips and tricks I will incorporate.
5
Anonymous Author
Great course for those just getting started in Excel
4
Member's Profile
Good if you're relatively new to Excel tips & tricks
4
Anonymous Author
Enjoyed learning a few tricks I hadn't used before.
5
Member's Profile
The instructor is very knowledgeable about Excel.
3
Anonymous Author
Needs more detail instead of a still screen shot.
4
Member's Profile
I would like to see what questions I got wrong.
5
Member's Profile
Audio wasn't the greatest with lots of "humming"
5
Anonymous Author
Everything presented was new to me and useful.
5
Anonymous Author
Good tips, some of which I had never heard of.
5
Anonymous Author
Provided very useful information and guidance.
5
Anonymous Author
Will definitely help with efficiency at work!
5
Member's Profile
Always learn something new form these courses
5
Member's Profile
Learned a few nice tips I didn't know about
5
Member's Profile
These are tips I can use on a daily basis.
5
Member's Profile
Clear discussion by presenter of material.
5
Member's Profile
Great details which will make job easier
3
Member's Profile
Most all of course was straight forward.
5
Anonymous Author
learned a lot, very easy to understand.
5
Anonymous Author
very good ideas for excel efficiency.
5
Anonymous Author
good review of Excel functionality.
5
Anonymous Author
Solid class - easy to understand.
3
Member's Profile
Very informative but a little fast.
5
Anonymous Author
Very good course. Would recommend.
4
Anonymous Author
Great tips for text to columns!
4
Member's Profile
Good tips that I did not know.
4
Anonymous Author
Practical tips and shortcuts
4
Member's Profile
Learned tips I didn't know.
4
Member's Profile
It was very knowledgeable.
5
Member's Profile
Love this one! So helpful.
4
Anonymous Author
Short Sharp and Practical
5
Anonymous Author
Interesting new tips.
4
Member's Profile
Good tips and tricks!
5
Member's Profile
liked the short cuts
4
Anonymous Author
Easy to comprehend.
5
Anonymous Author
Great tips! tanks
5
Anonymous Author
Good presentation
4
Member's Profile
Easy to follow.
5
Member's Profile
Good excel tips
5
Member's Profile
Good questions.
5
Member's Profile
Easy to follow
5
Anonymous Author
Great course
4
Anonymous Author
Very helpful
5
Anonymous Author
Good course!
5
Anonymous Author
Great tips!
5
Anonymous Author
Very good.
5
Member's Profile
Great!
5
Anonymous Author
Thanks!
4
Member's Profile
none

Prerequisites

Course Complexity: Intermediate

Prerequisite: Basic proficiency with Excel

 

Advanced Preparation: None

 

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .

1 QuestionCourse Questions and Answers

Member's Profile

Not specifically about this course, but is there a command to compare text from two columns to determine if they match? I can't seem to find in my searches

Course Syllabus
INTRODUCTION AND OVERVIEW
Tips and Tricks
  4:15Concatenating Text
  4:41Recent Items Menu
  9:00Text to Columns Wizard
  5:19Transformation Functions
  9:38Linking Excel-Word and Editing PDF Files
  6:35Span Two Monitors - Excel Templates
Conclusion
  2:06Book Recommendation - Closing Comments
Continuous Play
  48:37Excel Efficiency: Tips and Tricks I
SUPPORTING MATERIALS
  PDFSlides: Excel Efficiency: Tips and Tricks I
  PDFExcel Efficiency: Tips and Tricks I Glossary/Index
  xlsExcel Efficiency Tips and Tricks I-Excel 2003
  xlsxExcel Efficiency Tips and Tricks I-Excel 2007-2013
REVIEW & TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM