Working with Discounts

• How to calculate a discounted price
• What % of Discount Did I Receive?

Charting Data

The video lessons in this section show you how to add and modify Charts in Excel. Learning how to choose the proper chart type is essential to communicating your message about the information in your Excel data.

(Learn the 50 Best Tips for Excel 2007 at your own pace. Follow this link to shop for my 50 Best Tips for … series of DVD s)

Create and Modify Excel Charts

• Create a chart with one keystroke
• Click and Drag a Data Series in Your Chart to Seek a New Goal
• How to Create a Combination Chart in Excel – When there is a large “gap” between your data series values
• How to Explode a Pie Slice in Your Chart
• Visualizing Your Data in Excel 2007

Commands

The video lessons in this section are designed to save you time when working in Excel. I love keyboard shortcuts and I demonstrate how to use my favorite ones. Learning how to tap into the power of the Paste Special command is another powerful way to save you time and maintain consistnecy with other Excel workbooks.

(Learn the 50 Best Tips for Excel 2007 at your own pace. Follow this link to shop for my 50 Best Tips for … series of DVD s)

Keyboard Shortcuts

• Alt+; – Edit, Go to, Special, Visible cells only
• Ctrl+a single letter – Ctrl+A through Ctrl+Z
• Ctrl+tilde (Ctrl+˜) – To reveal all of the formulas in a worksheet
• Navigate to worksheets w/ Hyperlinks and Named Ranges
• Select Data w/ Keyboard and Mouse-click Shortcuts – Practice file available to download: select-data-w-shortcuts
• The Function Keys – F1 through F12
• Undo and Redo using Ctrl+Z and Ctrl+Y
• Use MS Office’s AutoCorrect Options to Create Shortcuts

Paste Special

• Add
• Multiply
• Transpose- Change data from vertical to horizontal and vice versa
• Validation (Not Yet Available)
• Values – When you want to paste the results of a formula
• An Amazing Shortcut courtesy of Bill Jelen, Mr. Excel – Quickly Convert Formulas to Values in a Range of Cells)

Excel Tips

Data

The video lessons in this group show you how to learn how to use Excel’s more advanced features for entering, manipulating and analyzing your data. Pivot Tables are both the most powerful feature in Excel and also one of the easiest tools to use – but you need to learn how to use tem in order to get the most from them. I show you how to do this!

(Learn the 50 Best Tips for Excel 2007 at your own pace. Follow this link to shop for my 50 Best Tips for … series of DVD s)

Consolidation

• Consolidate data by category
• Consolidate data by position
• Consolidate data as a quick alternative to Subtotals
• How to Combine Changes Made to Multiple Excel 2007 Workbooks

Entry

• Data Forms (Not Yet Available)
• Minimize Data Entry Errors
• Save Time & Maintain Consistency

External Data

• Import External Data in Excel 2007

Filters

• Advanced Filter
• AutoFilter
• Custom Filter
• Filter and copy Unique Records to another location
• Lists – A new feature added in Excel 2003

Lists

• Custom Lists – To Use with AutoFill and Custom Filter

Pivot Tables

• Introduction to Pivot Tables - Practice file to download: introduction-to-pivot-tables
• Hide & Show Details in Pivot Tables - Practice file to download: hide-show-details-in-pivot-table
• Introduction to Pivot Tables in Excel 2007
• Create a Calculated Field in a Pivot Table
• Create a Calculated Field in Excel 2007 Pivot Table
• Be Careful with Calculated Items in a Pivot Table
• Creating “Show as Values” Calculations in Pivot Tables – For example, % of Row or % of Column
• Changing Pivot Table Summaries in Excel 2007
• Group Dates to Produce a Summary by Year, Quarter & Month
• How to Group Dates by Month, Quarter & Year in Excel 2007 Pivot Table
• Group Months into Quarters in a Pivot Table
• How to Create a Fiscal Year Summary in Excel 2007 Pivot Tables

Sorting

• Sort data using a Custom List
• Rearrange Your Data – Sort Across Columns – Practice file to download: rearrange-columns

Subtotals

• Create Subtotals – To summarize your data
• Format the Subtotal Lines – To make your subtotals stand out!
• Nest Subtotals – Nest the 2nd subtotal inside your 1st subtotal
• Use Consolidate as a Quick Alternative to Subtotals

Tables

• How to take advantage of Excel 2007 Tables

Validation

• Use Data Validation to minimize data entry errors
• Use =INDIRECT() to Join Two Pick-Lists with Data Validation
• Use =COUNTIF() to ensure that only “unique values” are accepted
• Save Time and Maintain Consistency When Entering Data in Excel

What-if” Analysis

• Create a 2-Input Data Table – See multiple results by changing 2 variables – Practice file to download: cr-tutorial-20b-2-input-table
• Goal Seek – “Back into” the result you want by changing one variable at a time
• Click & Drag a Data Series to Goal Seek in a Chart
• Scenarios
• Solver (Not Yet Available)

Intermediate Tips

Formatting

• Conditional Formatting - to easily spot cells that meet certain conditions
• Format Painter (Not Yet Available)
• Time Formats
• Three Frustrations When Calculating Time in Excel
• Visualizing Your Data in Excel 2007
• Zip Codes for a Mailing Label

Formulas

The videos in this section show you how to construct Functions that perform calculations in Excel. You will benefit by watching me demonstrate how to Audit Excel formulas.

(Learn the 50 Best Tips for Excel 2007 at your own pace. Follow this link to shop for my 50 Best Tips for … series of DVD s)

• Audit your formulas – to better understand how the formula is built
• Preventing Formula Errors from Displaying in Excel 2007
• Use Excel 2007′s Go To Special Command to Spot Formula Errors
• Cell References
• Absolute Cell References
• Link to cells in other worksheets or other workbooks
• Mixed Cell References
• Relative Cell References (Not Yet Available)

Functions

The video lessons in this section walk you through Excel’s Built-in Functions. There are several hundred Functions built into Excel. It is important that you understand how to follow the proper syntax to supply the arguments for Excel to evaluate in order to get the proper result. I share my secrets for understanding Excel’s Functions.

(Learn the 50 Best Tips for Excel 2007 at your own pace. Follow this link to shop for my 50 Best Tips for … series of DVD s)

Arithmetic Functions

• COUNTIF() - To see how many name tags have been claimed
• Running TotalSubtotalsSUMIFf()  – a great way to SUM only the values that meet a single criterion

Common (“Most Popular”) Functions

• AutoSumQuickSum Calculator on Status BarUse =TODAY() Function to find past due invoices

Conditional Functions

• COUNTIF() (Being Revised)

Date & Time Functions

• DATE()
• DAY() MONTH() and YEAR()
• Use Date() Year() Month() Day() to create a series of Dates
• Networkdays()
• NOW() – Returns the Current Date & Time (Being Revised)
• Time() – Returns the Current Time
• TODAY() – Use TODAY() to Identify Past-Due Invoices
• Viewer Request: How does Excel calculate Future Dates?
• WEEKDAY()

Financial Functions

• FV() – Calculate the Future Value of Your Investment - Practice file to download: future-values
• Click & Drag to Goal Seek a New Future Value in a Chart
• PMT() – Calculate Your Monthly Payment for a Loan

Logical Functions

• IF()
• AND()

Lookup Functions

• Hlookup() (Not Yet Available)
• Indirect()
• Match() – How to Compare Two Excel Lists for Differences
• Simplify Data Lookups with Intersection Operator – Practice file to download: simplify-data-lookups
• Vlookup()
• Vlookup() in a Payroll Statement form

Math & Trig

• Randbetween() – Create Random Numbers within a Range of Values

Text Functions

• Ampersand – To Join Text Strings – or cell contents – Together
• Concatenate()
• Istext()
• Left()
• Lower()
• Mid()
• Proper()
• Right()
• Text to Columns - Separate First Name and Last Name in a cell
• Text to Columns – Convert text to an Excel Date
• How to Use Excel 2007′s Transpose & Text to Columns Tools to Reorganize Your Data
• Text to Numbers
• Trim()
• Upper()

Excel Tips

Named Cells, Ranges & Constants

The video lessons in this section show you how to create and use Named Ranges for cells in Excel. Learn how using Named Cell References in Excel formulas makes them easier to understand and easier to explain to others.

(Learn the 50 Best Tips for Excel 2007 at your own pace. Follow this link to shop for my 50 Best Tips for … series of DVD s)

• Use a Named Constant to Save Time & Maintain Consistency in Excel Data
• Use Names in Formula - Make your formulas more intuitive – Practice file to download: use-named-cells-in-formulas

Sharing Excel Data with Other Applications

The videos in this section demonstrate techniques for sharing information with other applications in the Microsoft Office Suite of programs. These tips will really save you time!

(Learn the 50 Best Tips for Excel 2007 at your own pace. Follow this link to shop for my 50 Best Tips for … series of DVD s)

• Paste, Link & Embed Data in PowerPoint

Publish Worksheets as Webpages

• How to Enable Interactivity for a Workbook
• How to Publish an Excel Workbook as a Webpage