Total Training for Microsoft Excel 2007 Essentials and Advanced + Shortcuts

One of the best courses I’ve done. How many of us use excel day in and out? I use it for my SMART/SWOT analysis, goal setting, workout progress, clocking my hours when I had to… all sorts. And now I feel like Super Excel Man! I’ve saved a couple of videos below which were just wow moments for me. Great teacher and Total Training as usual delivers another great course.

Shortcuts You Need to Know
F2 : Edit selected cell
F4 : Repeat last edit
ALT + = : Automatically SUM() selected
ALT + Enter : Start a new line in the same cell
ALT + o-c-a : Auto size columns
PG+UP/PG+DOWN : Go to next/previous worksheet
CTRL + ` : Display Formulas
CTRL + Backspace : Show active cell
CTRL + Shift + # : Change Date format with day, month and year.
CTRL + K : To insert Hyperlink.
CTRL+ Shift + $ : Applies the currency format to the selected cells.
CTRL + Shift + & : Applies border to cells
CTRL + Shift + # : Date style
CTRL + Shift + @ : Time Style
CTRL + Shift +! : Number Style
CTRL + F12 : Open
CTRL + Spacebar : Select the entire column
Shift + Spacebar : Select the entire row
CTRL + [ : Select all cells directly referenced by formulas in the selection
CTRL + ; : Insert actual date in active cell
CTRL + : : Insert actual time in active cell
CTRL + D : Copy equation down
CTRL + F : Find
CTRL + H : Find&Replace
CTRL + Enter : Same data in multiple cells
Shift + F3 : Find Previous

#ads

Advanced DVD
Lesson 1: SUMMARIZING DATA (58 min)
Advanced Subtotals
Formatting the Subtotal Rows
Adding and Copying with Subtotals
Consolidating Data
Introducing the SUMIF Command
Working with the SUMIF Command
Using Array Formulas
Looking at AutoSum Tricks
Utilizing Fill Handle Tricks

Lesson 2: WORKING WITH PIVOT TABLES (52 min)
Creating a Pivot Table
Rearranging Fields in a Pivot Table
Explaining the Report Layout Options
Using the Report Filters Feature
Using Top 10 & Date Filters
Handling Blank Cells
Drilling Down in the Pivot Table
Sorting a Pivot Table
Formatting a Pivot Table
Creating Custom Formats
Explaining the Grouping Options
Adding Formulas to a Pivot Table
Changing a Calculation in a Pivot Table
Replicating a Pivot Table
Counting with a Pivot Table
Using Pivot Charts

Lesson 3: CHARTING IN EXCEL (75 min)
Charts Refresher
Moving, Sizing & Copying Charts
Formatting Charts
Formatting a Series
Exploring the Home & Format Ribbons
Deciding What Chart Format to Use
Show a Time Series with Column or Line Charts
Using Combination Charts
Using Line Chart Accessories
Using Bar Charts to Show Comparisons
Using Component Charts
Using Correlation Charts
Exploring Other Charts
Chart Lies Revealed & Advanced Chart Types
Creating a Custom Layout

Lesson 4: GETTING VISUAL (28 min)
Using Conditional Formatting
Using Advanced Conditional Formatting
Highlighting, Sorting & Filtering Columns
Using SmartArt Functions
Using Different SmartArt Graphics
Embedding a Formula into a Shape

Lesson 5: CREATING POWERFUL FORMULAS (85 min)
Using Goal Seek to Find an Answer
Auditing Formulas
Shrinking the Formula Bar
Understanding Errors
Joining Text Columns
Using Custom Formatting Codes
Pasting Values
Using Paste Special
Breaking Apart Text
Converting Text to Numbers
Using Date Functions
Using Bizarre Date Functions
Using Exponents
Using the VLOOKUP Function
Using Index Match
Using the Offset Function
Using the Convert Function
Looking at Interesting but Obscure Functions
Using the Rand Function
Working with Regression
Ranking & Sorting Formulas

Lesson 6: RECORDING MACROS (18 min)
Displaying the Developer Tab
Recording a Simple Macro
Simplifying the Macro
Recording Using Relative References

Lesson 7: TIPS & TRICKS (28 min)
Showing Off New Tips in Excel 2007
Tips for Handling Worksheets
Using Excel as a Word Processor
Miscellaneous Tips
Validating Data
Changing Text Entry Direction
Using Speak Cells
Final Comments & Credits

AND SO MUCH MORE

#ads