by | Released November 2015 | ISBN: 9781771375481

In this Microsoft Excel Cookbook training course, expert author Guy Vaccaro will teach you everything you need to know to work efficiently in Excel. This course is designed for users that have a basic working knowledge of Excel.You will start by learning about dealing with time in Excel, including how to convert dates from imported data, building a timesheet calculator, and figuring out monthly sales. From there, Guy will teach you about errors in functions, creating pivot tables, working with data, and formulas. This video tutorial also covers creating charts, including creating a hyperlink, graphing with two Y axis, and display the sheetname. Finally, you will learn how to save as a CVS without warnings and create a random text string.Once you have completed this computer based training course, you will have learned everything you need to know to work faster and more efficiently in Microsoft Excel. Working files are included, allowing you to follow along with the author throughout the lessons. Show and hide more Publisher resources Download Example Code

1. Introduction
   • Is This Cookbook For You? 00:05:00
   • About Your Chef 00:01:30
   • How Big Is My Excel Oven? 00:04:43
   • The Included Working Files 00:02:35
2. Dealing With Time
   • Welcome To Excel - Date And Time 00:07:57
   • Formatting A Date 00:10:24
   • Converting Dates From Imported Data 00:05:38
   • Figuring Out Monthly Sales 00:08:06
   • Building A Timesheet Calculator 00:08:29
   • Working Out How Old Your Staff Are 00:07:56
   • Calculating Dates Using Functions 00:08:23
   • Prevent Negative Time From Displaying As Hashes 00:07:20
   • Highlighting Dates In The Current Week Or Month 00:06:52
   • Forcing The User To Input The Correct Time Format 00:06:15
   • What Will The Date Be This Friday? 00:04:35
3. Speeding Up Excel
   • Reasons And Solution For Slow Excel Files 00:05:21
   • Exploring File Reduction Methods 00:05:35
   • Ever Tried XLSB? 00:02:14
   • Using Keyboard Shortcuts 00:04:11
   • Creating Macros To Speed Up Repeated Formatting 00:06:21
4. Errors In Functions
   • Exploring Common Error Codes 00:07:00
   • Troubleshoot And Hide Error Messages 00:08:24
   • Errortype Function For Handling Errors 00:06:00
5. VLOOKUP Speciality Cake
   • The Basics 00:07:25
   • Perfoming A Two Column Lookup 00:08:05
   • Dealing With #N/A 00:04:25
   • When #N/A Is Not Really #N/A 00:06:39
   • Can We Lookup To The Left 00:06:02
6. Powerful Pivot Pie
   • Data Summaries In A Jiffy 00:07:46
   • Formatting, Labelling, And Sorting 00:06:35
   • Filtering The Data 00:07:10
   • Using The Recommended Pivots 00:04:57
   • Adding A Pivot Chart 00:07:20
   • Calculating Mean, Mode, And Median In Pivot Tables 00:10:10
7. Data Loaf
   • Summing Only The Filtered Results 00:05:48
   • Physically Changing Numbers Including Adding Leading Zeros 00:07:36
   • Creating An Auto-List Of Your Own Departments 00:04:23
   • Splitting A Cell By Text And Number 00:08:24
   • Filter Unique Values From Your Data List 00:05:24
   • Dragging Rows Or Columns To Change The Order Of Items 00:04:57
   • Creating A Comma-Separated List From A Column Of Data 00:04:50
   • Sorting Data By Row Values Instead Of Columns 00:03:53
   • Autonumbering For Record Rows 00:02:59
   • Filtering Rows By Color Rather Than By Value 00:03:20
8. Layout Tart
   • Creating Your Own Ribbon Tab 00:07:42
   • Splitting And Freezing Views To Help Navigate Large Sheets 00:05:53
   • Comparing Two Workbooks Using Synchronous Scrolling 00:04:19
   • How To Ctrl- Or Alt-Tab Between Worksheets 00:03:58
   • Merging Across Multiple Columns 00:03:29
   • Color One Cell Based On The Value Of Another 00:03:59
9. Mathematical Melange
   • Use An Array Formula To Build Multiple Cell Values 00:04:30
   • How To Reverse A Cells Contents 00:05:31
   • Creating A Squared Symbol Within A Formula 00:03:09
   • Count Values Based On Multiple Criteria 00:05:00
   • Find Missing Numbers From A Sequence 00:05:18
10. Quick Quiches
    • Force A Carriage Return Within A Cell 00:03:24
    • Column Row Insertion Deletion And Hiding With The Keyboard Only 00:05:14
    • Use A Formula To Calculate The Day Of The Week As A Name 00:05:01
    • Searching For The Special Question Mark And Asterisk Characters In Excel 00:04:25
    • One Command To Create A Chart 00:02:56
    • Display The Current Sheetname On The Sheet 00:04:53
    • Display A Yes-No Dialog Box When Running Macros 00:07:46
    • Creating A Hyperlink 00:05:08
    • Make Cell References Absolute Very Quickly 00:02:43
11. Spicy Charts
    • Graphing With Two Y Axes 00:04:17
    • Displaying A Set Of Smaller Values In A Separate Pie 00:05:51
    • Keeping A Continuous Line Even With Data Missing 00:03:24
    • Fill In Missing Date Labels On A Chart 00:03:54
12. Other Clever Titbits
    • Saving As A CSV Without Warnings 00:07:42
    • Create A Random Text String 00:08:32
13. The End
    • Good Luck 00:05:28
Show and hide more