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  
 
NEW Release: Access Advanced Level 2    dismiss
 
 
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 1/23/2017 to get a FREE upgrade to our 2007 version when released!
 

 Click here for the Teacher's Outline
 
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!

 

 

 Click here for the Teacher's Outline
 
 


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?

Regards

JR

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:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
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
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP