Microsoft Excel Cookbook

Video description

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.

Table of contents

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

Product information

  • Title: Microsoft Excel Cookbook
  • Author(s):
  • Release date: November 2015
  • Publisher(s): Infinite Skills
  • ISBN: 9781771375481