Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
Excel 2003 Essential Training with Mark Swift is a movie-based workshop for users who are new to working with spreadsheets, or those wanting to improve their skills. This workshop begins with a basic overview of the application and quickly advances to cover useful formulas, functions, techniques for enhancing spreadsheets, charts, and much more. Exercise files accompany the training, allowing you to follow along and learn at your own pace.
People often associate formulas with numbers only, and that's a big misconception. Here in Microsoft Excel, you can work with text and perform certain calculations, checks, and really enhance the power and flexibility of your spreadsheet. Let's open up the Student Folders again, and here in Chapter 16 you'll find another version of Quarterly_Sales. The first thing we're going to look at is comparing two pieces of text, and in order to do that, let's go to the Bonuses tab. You'll remember that this is a summary of bonuses for an individual employee. Well, if we want to check who that employee is and then take an action on that, we can use the If statement combined with this formula, but first let me just show you the formula. You could say equals, there starts our formula, A1, so the value that's in A1, does it equal, and you can refer to another cell, or in this case I'm going to refer to my name, and the value is true. This statement, this version of the formula is not case sensitive. So if I change the value in A1 to mark swift, it remains true. Let me put that back.
There's a function you can call on that is case sensitive. If you'll go up here and change the layout of the statement to be EXACT, and within brackets we just need to separate the two statements we're comparing with a comma, and Enter. Again that's true because the text I'm comparing against is capital M, a, r, k, space, capital S, w, i, f, t. Let's go up here and change the value in A1 to the all lowercase version of my name, and now you'll see that it's false. And we'll return that. I hope you can see the power of this formula even though it only returns a true or a false, which is a 1 or a 0, a yes or a no, that decision can go a long way towards creating an action. Let's combine it with the If statement. Let's say =if brackets, now we have the logical task, I love this help dialog box, a1 = Mark Swift, then make it 1000. If it's false make it 10. Close that off in brackets. So essentially what it says is if this comparison is true, then do this, and if it's false do this. If the value in A1 equals Mark Swift, then return the result 1000. If not return the result 10.
Right now the value is 1000, and since it's not case-sensitive, it won't matter if I change it to lowercase, so let's change it to different name, and now the value is 10. This is an oversimplified example, but I'm sure you can see how you can apply this within your own spreadsheet to look at text values and based on the result, return different values that get used in formulas.
There are currently no FAQs about Excel 2003 Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.