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.
In a previous lesson, we talked about naming ranges and naming cells and the benefit of that inside a formula. Another aspect of naming ranges, and we covered it briefly before but I'd like to use this in an example here, is making a named constant. The value for your commission is an excellent place to make a named constant. Here you'll see if we look in cell C11, we have a value of 15% entered into that cell. When we want to change the value of our commissions we go down here, change the value in this cell, and all the formulas that refer to that cell are affected. That's not so bad, but if you look at the formulas themselves, you can see that we have an absolute reference to C11 times our annual sales in B3. That's not bad. This particular formula reads pretty well because of the title of that worksheet: Annual Sales. But it can get better and it should get better. Let's take the value for commission and move its title and its value outside of our printed area. The printed area is represented by this dotted line. Now you can imagine, let em just expand that a little bit. You can imagine that as your complexity grows, so does the need for clarity, and instead of having an actual value of 15% here, we should create a constant that we can use in formulas that'll make the formulas crystal clear, and it gives us one place to go back and change the value of our commissions. If we go to the Insert menu, down to Name, and Defined, here it's already looking at the value of 15% and suggesting for us the name Commission.
Let's go ahead and say Add. So now we have a defined name called Commission and the value to that name still refers back to the cell H6. Let's replace the cell reference with 15%. So now the term Commission actually refers to a constant static value of 15%. Say OK. Here within H6, instead of having 15%, we can have a value that equals the name Commission, which still represents 15%, but now this indication to the user of our commission rate is represented based on the constant. And we can do the same thing here. Instead of having H6 be our value, we'll replace that with the word Commission. Now the result didn't change. The value that the user sees in B3 didn't change, but somebody who's walking into this spreadsheet can look at the formula and easily read that it's Commission times Annual Sales for the value of B3. To complete this English-language translation, all we'd need to do is go back to B3, and name that cell West in First Quarter, and then you could look at Commission times Annual Sales for the West in First Quarter. Done. Using named constants and naming cell ranges is an excellent way to make your spreadsheet more palatable for somebody who's just stepping in to use it.
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.