LiveLessons - Complete Excel 2007
Language: English | Series: LiveLessons | Publisher: Que; 1 edition (2009) | 5.91 GB
Bill Jelen is the host of MrExcel.com and the author of twenty books on Microsoft Excel including Special Edition Using Excel 2007, Pivot Table Data Crunching, VBA & Macros for Microsoft Excel, Excel for Marketing Managers, and Guerilla Data Analysis Using Microsoft Excel. He has made more than 75 guest appearances on TechTV with Leo Laporte and was voted guest of the year on the Computer America radio show. He has produced more than 600 episodes of his daily video Learn Excel podcast. Bill will entertain you while showing you the powerful tricks in Excel. Before founding MrExcel.com in 1998, Jelen spent twelve years “in the trenches,” as a financial analyst for the accounting, finance, marketing, and operations departments of a publicly held company. Since then, his company automates Excel reports for hundreds of clients around the world. The website answers more than 30,000 questions a year[md]for free[md]for readers all over the world.
Excel 2007 Interface
Excel 2007 offers a dramatically different interface. Microsoft removed the old menu and toolbars and replaced it with the Ribbon interface. This part introduces the various components of the Excel 2007 interface.
In this part, there are ten lessons:
Lesson 1: Introducing the Ribbon
Lesson 2: Quick Access Toolbar
Lesson 3: Finding Icons
Lesson 4: Keyboard Shortcuts
Lesson 5: Mini Toolbar and Live Preview
Lesson 6: Zoom Slider and Page Layout
Lesson 7: Recent Documents
Lesson 8: Save as PDF
Lesson 9: Excel Options
Lesson 10: Opening Templates, Closing Excel
Excel 2007 New Features
Excel 2007 offers many new jaw-dropping features, including a big grid with 1.1 million rows and 16 thousand columns. This is a 102,300% increase from the Excel 2003 grid with 65 thousand rows and 256 columns.
This part has eight lessons:
Lesson 11: Unlocking the Big Grid
Lesson 12: Cell Styles and Document Themes
Lesson 13: Data Visualizations
Lesson 14: Tables and Table Formulas
Lesson 15: Sort and Filter by Color
Lesson 16: Duplicates
Lesson 17: Picture Tools and WordArt
Lesson 18: Other New Features
My favorite tip in this part is on using the data visualizations in Lesson 13, "Data Visualizations." This feature allows you to bring a worksheet to life to assist those managers whose eyes glaze over when presented a table of numbers in black and white.
While most people think of Excel as a program to deal with numbers, there is always some text to help frame the numbers in context. In this part, you learn how to quickly enter text, join text, break apart text, or even create text from dates or numbers.
This part contains nine lessons:
Lesson 19: Drag the Fill Handle to Extend a Series
Lesson 20: Create Your Own Series
Lesson 21: Joining Text
Lesson 22: Rapid Formula Copy
Lesson 23: Convert to Proper Case
Lesson 24: Joining Dates
Lesson 25: Converting Formulas to Values
Lesson 26: Breaking Apart Text
Lesson 27: Parsing Fixed Width
My favorite tip in this part is in Lesson 22, "Rapid Formulat Copy." By far, the biggest collective gasp in my Power Excel seminar comes when I show people how to stop dragging the fill handle to copy a formula.
In this part, you learn how to customize the Excel environment, how to customize all future workbooks, and how to quickly copy worksheet templates.
This part contains five lessons:
Lesson 28: Data Entry Tricks
Lesson 29: Customize All Future Workbooks
Lesson 30: Copy Worksheets
Lesson 31: Change All Worksheets
Lesson 32: Arranging Windows
For the most useful technique in this part, watch Lesson 29, "Customize All Future Workbooks,"[md]you will never have to adjust your margins or footers again.
Sorting and Subtotals
Lesson 33 starts off with some basic rules that you need to follow so that the techniques and tricks offered in parts 5 and 6 work correctly. After that, you learn some cool ways to sort and subtotal data.
This part has twelve lessons:
Lesson 33: Data Preparation
Lesson 34: Sorting Basics
Lesson 35: Random Sort
Lesson 36: Sorting by Weekday
Lesson 37: Rearranging Columns
Lesson 38: Subtotals
Lesson 39: Collapsing Subtotals
Lesson 40: Copy Subtotals
Lesson 41: Formatting Subtotals
Lesson 42: Multiple Subtotals
Lesson 43: Filling in Data on Subtotal Rows
Lesson 44: Subtotal Tricks
My favorite lesson in this part is Lesson 38. It may seem like basic Excel blocking and tackling, but it is the one lesson with the potential to replace a multihour process with a few clicks. Lesson 40 is the lesson that seems like it should be easy, but requires incredibly obscure steps.
Pivot tables are my favorite feature in Excel. You can take 500,000 rows of data and reduce them to a useful summary in six mouse clicks. This part takes you through everything you need to know to use pivot tables successfully.
This part contains fifteen lessons:
Lesson 45: Creating a Pivot Table
Lesson 46: Changing a Pivot Table
Lesson 47: Pivot Gotchas
Lesson 48: Pivot Sorting
Lesson 49: Drill Down
Lesson 50: Summarizing Dates
Lesson 51: Top 10 Report
Lesson 52: Pivot Calculations
Lesson 53: Pivot Formatting
Lesson 54: Unique List
Lesson 55: Ad-Hoc Queries
Lesson 56: New Filtering Options
Lesson 57: 50 Reports in 5 Seconds
Lesson 58: Pivot Compatibility with 2003
Lesson 59: Making Reports Pivotable
Lesson 57 is the most powerful lesson in this part. Lesson 59 contains painful techniques, but they are important techniques if your original data set is not suitable for creating pivot tables.
Charting and SmartArt
After 15 years, the charting engine was redesigned in Excel 2007. You can now create great-looking charts in just a few clicks. Also new, create sharp-looking business diagrams with the SmartArt tools.
This part contains nine lessons:
Lesson 60: Creating and Formatting a Chart
Lesson 61: Adding or Removing Chart Data
Lesson 62: Interactive Chart
Lesson 63: Charting Differing Orders of Magnitude
Lesson 64: Pie Chart Tricks
Lesson 65: XY and Bubble Charts
Lesson 66: Saving Favorite Chart Settings
Lesson 67: Creating SmartArt
Lesson 68: Smart Art Formulas
Lesson 66 shows you how to create a perfectly formatted chart with one keystroke. Lesson 68 addresses how to create interactive SmartArt, something that Microsoft says cannot be done.
Formulas are the miracle of Excel. Enter one formula, and copy it to 50,000 rows. Change one number, and all the formulas recalculate. In this part, you learn fast ways to enter formulas, some arcana of formula references, and how to instantly discover an answer. You also learn about the powerful VLOOKUP and array formulas.
This part has 13 lessons:
Lesson 69: AutoSum Tricks
Lesson 70: Formula Auditing
Lesson 71: Three Ways of Entering Formulas
Lesson 72: Relative Versus Absolute Formulas
Lesson 73: Discovering Functions
Lesson 74: Goal Seek
Lesson 75: Unusual Functions
Lesson 76: IF Formulas
Lesson 77: VLOOKUP
Lesson 78: IFERROR
Lesson 79: SUMIFS
Lesson 80: Super Formulas
Lesson 81: Date and Time
If you are relatively new to Excel, you must watch Lesson 72. It is a crucial point that no one would learn by just sitting down to use Excel. Lesson 77 teaches you the details of using my most-used function: VLOOKUP. If you are looking to be amazed, watch Lesson 80 to learn about a secret class of formulas.
VBA macros allow you to automate any process in Excel. Microsoft provides the macro recorder to help create macros, but there are a myriad of undocumented rules that you must follow to have the macro recorder work successfully. In this part, you learn how to successfully record a macro that can deal with data sets of any size.
This part has four lessons:
Lesson 82: Recording a Macro
Lesson 83: Recording Navigation
Lesson 84: How Many Rows
Lesson 85: Event Macros
I don't have a favorite lesson in this part. If you want to use macros, you have to watch all the lessons. If you want to get beyond the macro recorder and write your own code, check out QUE's VBA and Macros for Microsoft Excel 2007.
Tips and Tricks
This part includes all of my favorite tips that defy classification into one of the other parts of the DVD.
This part has ten lessons:
Lesson 86: Finding Records with Filter
Lesson 87: Web Queries
Lesson 88: Copying Column Widths
Lesson 89: Page Numbering
Lesson 90: Turn Data
Lesson 91: & in Header
Lesson 92: Choose from a List
Lesson 93: ToolTips for a Cell
Lesson 94: Word Processing in Excel
Lesson 95: Have a Talk with Excel
Lesson 92 is probably the coolest lesson in this part, particularly if you have to send worksheets out for other people to fill out. Lesson 93 gets the prize for the most unusual. Lesson 95 has the funniest prank.