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.

Part 1

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

Part 2

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.

Part 3

Text

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.

Part 4

Excel Customizations

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.

Part 5

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.

Part 6

Pivot Tables

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.

Part 7

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.

Part 8

Formulas

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.

Part 9

Macros

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.

Part 10

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.

http://www.mrexcel.com/livelesson07.html

**Buy Premium To Get Resumable Support & Max Speed!**

**Rapidgator Links**

http://rapidgator.net/file/aa76ecd3d50fd84176f744fd4ed31fad/Exl2007.part01.rar.html

http://rapidgator.net/file/43e994562e899787ac949fed6ddd84b4/Exl2007.part02.rar.html

http://rapidgator.net/file/dfaa4e39bb3e9c3d7d0f7cd35b9629c3/Exl2007.part03.rar.html

http://rapidgator.net/file/81987f20f0cfcae0cd621ea922b57dc1/Exl2007.part04.rar.html

http://rapidgator.net/file/676a9d1e74756a3130d8e280bbfd7a46/Exl2007.part05.rar.html

http://rapidgator.net/file/da10b2789579d4c6bde756c8c627bc80/Exl2007.part06.rar.html

http://rapidgator.net/file/d51f483155fbfa1c3d02deda1a056e70/Exl2007.part07.rar.html

http://rapidgator.net/file/46ab6a501045767a7946ff9fc4bc525b/Exl2007.part08.rar.html

http://rapidgator.net/file/e779b37befe4f9fc00e71e4cc16914a0/Exl2007.part09.rar.html

**JunoCloud Links**

http://junocloud.me/fwje0r2rqe7o/Exl2007.part01.rar.html

http://junocloud.me/8ic7tq9frjnh/Exl2007.part02.rar.html

http://junocloud.me/v4lchre5ktyy/Exl2007.part03.rar.html

http://junocloud.me/59927yx1g4ok/Exl2007.part04.rar.html

http://junocloud.me/94s3fak8lfhw/Exl2007.part05.rar.html

http://junocloud.me/s43pxerng8wi/Exl2007.part06.rar.html

http://junocloud.me/yassh5e2o4vk/Exl2007.part07.rar.html

http://junocloud.me/7eujruh67f0y/Exl2007.part08.rar.html

http://junocloud.me/69v0zcjds08a/Exl2007.part09.rar.html

**Uploaded Links**

http://uploaded.net/file/4ror5gbl/Exl2007.part01.rar

http://uploaded.net/file/rhcjz060/Exl2007.part02.rar

http://uploaded.net/file/rrbxsub0/Exl2007.part03.rar

http://uploaded.net/file/6ysg9j97/Exl2007.part04.rar

http://uploaded.net/file/clyo2ffn/Exl2007.part05.rar

http://uploaded.net/file/9qvmh5bf/Exl2007.part06.rar

http://uploaded.net/file/5no51ajc/Exl2007.part07.rar

http://uploaded.net/file/uqbk1va7/Exl2007.part08.rar

http://uploaded.net/file/rrq67dtx/Exl2007.part09.rar

**Site BBcode/HTML Code:**