Excel 2010/2013 Excel 2007 Excel 2003 Tips & Tricks Excel Forum Course Index CIG Excel Book

 Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us More... What's New? Tips & Tricks Access Index Access Tips Excel Index Excel Tips Waiting List

Courses - Microsoft Excel 201
Description: Intermediate Microsoft Excel
Running Time: 67 minutes
Pre-Requisites: Excel 104 strongly recommended
Versions:
 We use Excel XP in this course, but the lessons are valid for all versions of Excel from 95 to 2003. There are cosmetic changes in Excel 2007. Order before 9/27/2018 to get a FREE upgrade to our 2007 version when released!

This is our first Intermediate level Excel class, so get ready for a lot of great, new, powerful features! We're going to start out by learning about the IF function - which allows Excel to make decisions for itself. We'll learn about relative v. absolute references. We'll see how to share data between multiple sheets. And, you will see how to paste-link and embed Excel sheets into Word documents.

We will begin with a look at the IF function. This is a very powerful function that we can use to make Excel decide what value to put in a cell based on the values in other cells. We'll talk all about the IF function and how it works.

Remember the student gradebook sheet we created back in Excel 101? We'll see how to use the IF function to give our students a pass or fail mark.

We'll do another example where we can make Excel decide whether or not to charge a customer sales tax based on what state they're from. Yes, I know it looks complicated, but once we show you how it works, it's really quite easy to use!

Next, we'll learn about relative v. absolute references in our sheets... what they are, and why we need them! Yes, it's that funky dollar-sign notation you can see below in the formula bar.

We'll do another example of absolute references with some sales figures. I like to give multiple examples of new techniques - just to make sure you get it.

Next I will show you how to refer to cell values on other sheets. This will let you share data between multiple worksheets.

We will learn about the Paste Special feature where you can paste a link to your data on different sheets, or just paste the values, or just paste the formats... the possibilities are many.

Next, we'll learn how to copy and paste information from Excel directly into Microsoft Word. First, I'll show you how to bring over Excel data as just a simple Word Table. This is real easy to do.

Then, we'll learn all about Object Linking and Embedding where we can actually paste a version of our Excel sheet into a Word document - and still treat it like a little piece of Excel that we can edit in-place (complete with calculations, formats, etc.)

Next, we'll see how a linked copy of your Excel data will automatically update in any Word documents it's pasted in if the original changes. This is great if you have one set of data you need to constantly update in multiple reports. This technique even works for PowerPoint.

Finally, we'll finish the class with a bunch of additional tips and tricks. We'll learn about Insert Cut Cells, moving sheet tabs around in our workbooks, using the Borders toolbar to draw manual borders, and more.

So, if you really... I mean really... want to learn how to make Excel do what you want it to do, this class is a must. It is the first class where we start making Excel think for itself with the IF function. Watch this course for no other reason than to learn how this function works... properly! We spend a lot of time on it, so don't miss it!

Try a FREE Demo Lesson

Student Interaction: Microsoft Excel 201

 Richard on 1/1/2007:  This is our first Intermediate level Excel class, so get ready for a lot of great, new, powerful features! We're going to start out by learning about the IF function - which allows Excel to make decisions for itself. We'll learn about relative v. absolute references. We'll see how to share data between multiple sheets. And, you will see how to paste-link and embed Excel sheets into Word documents.
 leroy diaz on 11/24/2007: WHY WHEN I ENTER TH = THEN I TYPE THE (IF) IT DOESN'T POP UP THE INFORMATION BAR IS THERE SOMETHING I NEED FOR THIS?
 Richard Rost on 11/27/2007: That depends on your version of Excel and what other features you have or don't have installed. Which Excel are you using?
 Don on 1/13/2008: I just finished Excel 201 and there is no test available and I WANTED a certificate of completion. Please advise.Don
 Richard Rost on 1/14/2008: Don, I'm sorry, but tests for ALL of the courses aren't available yet. I'm working on them. I hope to add more very soon.
 Jammy on 2/20/2008: Hi Richard,How can i practice while i am watching you at the same time?RegardsJR
 Richard Rost on 2/28/2008: Jammy, I've made my videos small so that you should be able to fit them on the screen at the same time as you're working with Excel. Just move the windows around on your desktop until they fit. You might need to resize them a bit, but unless you've got a REAL small monitor, they should fit fine.
 Dak Kanani on 8/25/2008: EXCELLENT COURSE AND TEACHING
 Maks Kelemina on 10/1/2008: Richard, this lessons were extremly helpful, ,especialy the links between sheets and Word - Excel combination, thanks
 Art  Kelly on 2/21/2009: A student caught cheeting is not a problem, a student caught cheating should be disciplined!Richard, this is an excellent course, as is all your training courses.I learn so much better because of the ability to repeat the sessions over and over. With your course I took my Church from manual pencil bookkeeping to a financial report generated by Excel entries. It's great! Many thanks. Eventually I will procure all the Excel courses.Art Kelly
 Charles Walters on 3/10/2009: In this lesson you could have also mentioned that you can press F4 att eh top of the keyboard to enter the dollar signs
 Charles on 3/10/2009: This is exactly what I have been looking for. I think your videos are great and have posted your link on Amazon.com in a book or rather DVD review I had posted on excel 2007 and also recommended your site to view. I have viewed many books on MS Excel, but never felt that they were worth the money. Just recently I bought Excel 2007 Data Analysis and Business Modeling from MS Press and it sucks. I really do hope as you are making your videos for excel 2007 you can incorporate ideas from this book that would be just AWESOME!! I will be buying more of you lessons. One more thing, I would also Like to see more detailed and complicated examples using the IF statement, VLOOKUP, SUMIF, and such. Taking a basic example and really building on it.
 Debbie on 5/9/2009: Wish you had used more complicated examples
 Richard Rost on 5/11/2009: Debbie, give me an example of a more complicated example, and I'll be happy to include some next time I revise this lesson. :)
 Joyce Jones on 5/21/2009: Very straight forward and easy to learn.
 Bill on 10/16/2009: Richard, suppose that sales tax changes....you wouldn't want all the previous calculations to reflect the new rate?....That would require a new formula?...am I right or at least an update....how can one best do that.....?Thanks
 Richard Rost on 10/18/2009: Bill, you are correct. You do NOT want the sales tax changing on all OLD records. When we get to the order entry system in Access 301/302 I'll show you an exception - when you want to save historical data. For example, when you add a PRODUCT to an invoice. If the product price changes, you don't want it updating all of your old invoices. It's the exact same scenario. We'll save the product price in the invoice table to keep it at the time of the sale.
 SHEILA on 3/15/2011: I am trying to use the IF feature; but to no avail. I want to know if excel sees the letters ST in ANY cell-this is my range(=\$G\$6:\$NG\$80) on my spreadsheet, then I want the number 2 to appear in cell F6 - Is this possible to do?Reply from Richard Rost:The cell will be JUST "ST"? If so, you could use the COUNTIF function to count the number of times it appears. If "ST" can appear anywhere WITHIN the cell, you'd need to use the FIND function to look inside of a text string.
 SGT Wilson on 6/6/2014: Okay here is one for the books. Can I link a word document to excel, but I want it to reference each system like it was a drop down menu. I have 56 systems with reference to numbers to that system how can I make the information show up in excel. If I get this then it will help me solve a lot of issues.Reply from Richard Rost:I'm fairly confident this is possible with the right VBA. I have never done it before, and I would have to research and figure it out, but I'm pretty sure it can be done.

 You may want to read these articles from the 599CD News:
 5/4/2014 Microsoft Access Expert 20 3/5/2013 Excel Tip: Price Markups 2/3/2013 Signed Copies of my Excel Book 9/27/2012 Excel Expert 10 Handbook Ready 9/18/2012 Two New Excel Tips: Date/Time Differences 6/20/2012 Excel Tip: Sum Cells Between Two Values 6/16/2012 New Excel Tip: Paste Link 3/26/2012 Excel Tip: 2D Matrix Lookups 11/18/2011 Dashboards in Excel 10/6/2011 Excel Expert 11 On Hold

Learn

 Access index Excel index Word index Windows index PowerPoint index Photoshop index Visual Basic index ASP index Seminars More...
Customers

Online Theater
Insider Circle
Student Databases
Change Email
Info

Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Help

Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services

Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order

Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact

Live Chat
General Info
Support Policy
Contact Form
Email Us
Phone Number
Fax Number
Course Survey