by Guy Vaccaro | Released July 2011 | ISBN: 9781926873541

In this video tutorial for Microsoft Excel 2011 For Mac, expert author Guy Vaccaro teaches you to effectively utilize the features and functions of Excel through project based learning. You will complete various projects, and along they way learn to leverage the power of the most important features Excel 2011 has to offer the Mac user.Starting your training course with the creation of a spreadsheet to record and monitor sales data, you will learn the basics of what you can do with a spreadsheet. You will then move on to creating a Profit and Loss report, learning formulas along the way. Moving to score sheets for a sports day, you will discover conditional based formatting, lookups, and more. You then create a functional expense claim form, advancing your Excel expertise. Moving on to a sales contact management sheet, you will discover how you can manipulate text, and even create mail merges from Excel. Finally, you will utilize all your knowledge thus far to create a sales report, including charts, pivot tables, and importing data.By the conclusion of this Excel 2011 For Mac video training course, you will fully understand the capabilities of this powerful program. Not only will you understand the concepts, but how to properly apply them for your own spreadsheet needs. This training tutorial comes complete with working files, so you can use the exact same files that the author does as he proceeds through the course. Show and hide more

1. Getting Started
   • How Time Saving Are These Excel Projects? 00:07:49
   • Making Use Of The Project Working Files 00:01:51
   • About The Author 00:01:39
   • An Introduction To Math In Excel 00:09:30
2. Recording And Monitoring Sales Data
   • Creating A New Excel File To Record Data 00:05:33
   • Saving Your Workbook In The Correct Format 00:02:24
   • Improve Your Worksheet With Some Formatting 00:03:47
   • Adding Formulas To Total Rows And Columns 00:05:19
   • Adding Formulas The Quick Way 00:02:13
   • Working Out Our Average Consumption 00:03:41
   • Conditional Formatting To Improve Presentation 00:04:04
   • Renaming Sheets And Printing 00:05:27
3. Using Excel For A Profit And Loss
   • Creating The Data Collection Sheet 00:03:27
   • Setting Up A List Using A Named Range 00:06:01
   • Adding Formula To Create January Profit And Loss 00:09:13
   • Changing The Data Entry To A Single Sheet 00:08:11
   • Creating The Other Eleven Months - Chapter 3 00:05:15
   • Using 3D Referencing To Generate The Whole Years Profit And Loss 00:07:30
   • Exporting To PDF And Emailing To The Accountant 00:02:32
4. Score Sheets For School Sports Day
   • Setting Up The Scoresheet Structure 00:06:49
   • Looking Up The Competitor Name 00:07:52
   • A Near Match Lookup For The Event Scores 00:06:30
   • Using Sheet Replication To Create The Other Event Sheets 00:09:12
   • Adding Protection To Protect Your Formula 00:08:22
   • Data Entering The Actual Results 00:02:29
   • Completing The Competitor Summary 00:09:06
   • Creating An Error Check For Events Competed 00:04:12
   • Adding Up The Scores And Ranking For Position 00:04:26
5. Expenses Claim Form
   • Expenses Claim Document Setup And Header Page 00:05:55
   • Adding The First Month 00:04:31
   • Displaying The Month Name Instead Of The Number 00:03:03
   • Creating Destination Lookups For January 00:11:23
   • Protecting January And Setting Print Options 00:04:26
   • Creating The Other Eleven Months - Chapter 5 00:06:11
   • Adding A Summary Sheet 00:05:20
   • Using And Submitting Your Expenses Claim 00:03:03
6. Sales Contacts Management
   • Setting Up Excel To Store Data And Entering That Data 00:06:18
   • Creating A Unique ID And Freezing Titles 00:06:38
   • Calculating New Values Using String Manipulation 00:07:41
   • Sorting Your Excel Database 00:05:19
   • Filtering Your Dataset 00:05:56
   • Removing Duplicate Records From Your Dataset 00:03:04
   • Mail Merge Using Your Dataset 00:07:29
   • Form View In Excel 00:06:05
   • Summarizing The Dataset With Pie Charts 00:11:29
7. Stock Sales Summaries
   • Starting The Sales Data Record Sheet 00:06:09
   • Adding A Sales Chart To The Data 00:06:19
   • Replicate Sheets To Create Other Sales Regions 00:03:49
   • Summarizing The Sales Data 00:11:09
   • Importing CSV Format Sales Data 00:04:18
   • Adding Some Calculations To The Imported Data 00:01:54
   • Interrogating The Sales Data With A Pivot Table 00:09:32
   • Formatting The Pivot Table 00:05:56
   • Sorting And Filtering The Pivot Table 00:05:57
8. Next Steps
   • Reviewing Absolute And Relative Formulas 00:09:31
   • What Is The SkyDrive? 00:05:47
   • Saving An Excel File To SkyDrive 00:03:39
   • Informing Others That The SkyDrive Document Is There 00:02:18
   • Excel 2011 Summary 00:01:53
   • What Is Next? 00:04:49
Show and hide more