MS Excel: Let's Advance to The Next Level

In this Book

• #0101–0109—Super Essential Keyboard Shortcuts
• #0201—Used in Financial Modeling and Tax Computation
• #0202—Used in Pricing Discovery Processes
• #0203–0204—For Rounding Numbers
• #0205—For Counting
• #0206–0207—For Weighted Average and Compounding/Discounting
• #0301–0302—Formatting Tricks Incl. Special Custom Formats [Shortcut: Ctrl 1]
• #0303–#0304—Using Cell Styles for Automating Formatting for MIS Reporting and Financial Models
• #0305–#0307—Cell Drag–n–Drop Auto Fill Options
• #0308 Paste Special—Transpose vs. TRANSPOSE()
• #0401—0402—Absolute and Relative Referencing Using $ (Locking the Cell/Range)
• #0501–0506—Go To—Special (Ctrl + G or F5)
• #0601 Vertical Sort—1-Level and 2-Level
• #0602—Custom Sorting
• #0603 Sort Trick—Add Alternate Blank Rows In-Between Existing Rows
• #0604—Horizontal Sorting (Left to Right)
• #0605–0606—Filter—Choosing the Dataset Correctly
• #0607—Filter Analysis w. Shortcuts
• #0608–0609—Using =SUBTOTAL() for Calculations w. Filtered List
• #0610—Filter—Applying 2 or More Filters Simultaneously on the Same Sheet
• #0611—Filter—Color Filter and Text Filter
• #0612–0614—Advanced Filter
• #0701–0702—Every Valid Date (i.e., Date That Can Be Understood by Excel) Is a Number
• #0703—Extracting Date Information Through Formulas—DAY(), MONTH(), YEAR(), DATE()
• #0704—Extracting Date Information
• #0705—Date Formulas—WEEKDAY(), WORKDAY(), NETWORKDAYS()
• #0706—WORKDAY.INTL() for Deadline/Due Date Calculations w. Custom Weekends/Holidays
• #0706—NETWORKDAYS.INTL() for No. of Business Days Calculations w. Custom Weekends/Holidays
• #0708—Date Formulas—TODAY() and NOW() w. Shortcut
• #0709—Date Formulas—EOMONTH() for Financial Modeling, Budgets, Due Dates
• #0710—Date Formulas—EDATE() for Financial Modeling, Budgets, Due Dates
• #0801–0802—Data Validation—Drop Down List and Range Naming
• #0803—Data Validation—Numbers w. Error Alert and Input Message
• #0804—Data Validation—Dates w. Error Alert and Circle Invalid Data
• #0805—Data Validation—Whole number, Text Length, Date (MM/DD/YYYY)
• #0806—Data Validation—Custom w. Formula Logic
• #0901–0902—Grouping/UnGrouping Columns and Rows
• #0903—Grouping Trick: Changing Placement of Grouping Button
• #0904—Cell Gridlines: Turning On/Off
• #0905—Hide/Unhide Rows and Columns
• #0906—Freeze Panes (Incl. Both Row and Column Simultaneously)
• #1001–1003—Pivot Tables—Prerequisites, How to Create
• #1004—Pivot Tables—Exploring Pivot Table Grid (Fields)
• #1005—Pivot Tables—Value Field Settings for Sum, Average
• #1006–1007—Pivot Tables—Value Field Settings for % Calculations
• #1008–1009—Pivot Tables—Grouping Dates and Numbers (Automatic)
• #1010—Pivot Tables—Grouping Text (Manual)
• #1011—Pivot Table—Refresh vs. Refresh All, Change Data Source
• #1012—Pivot Table—Auto Refresh
• #1013—Pivot Chart Shortcut (F11) and Sparklines
• #1014—Pivot Table—Drill Down Option
• #1015—Report Filter—Generating 100s of Reports in Few Seconds
• #1016—Slicer vs. Report Filter
• #1101—VLOOKUP() for Starters
• #1102—VLOOKUP w. TRUE vs. FALSE and Applications of TRUE
• #1104—HLOOKUP() vs. VLOOKUP()
• #1105–1106—MATCH()—Basics and Match_Type: -1 vs. 0 vs. 1
• #1107–1111—2-D Lookup (Vertical + Horizontal)—VLOOKUP w. MATCH
• #1112—2-D Lookup (Horizontal + Vertical)—HLOOKUP w. MATCH
• #1113–1114—INDIRECT()—Basics Along with Range Naming—Applications ["RE-DIRECTION"]
• #1115—1116—3-D Lookup—VLOOKUP() w. MATCH() w. INDIRECT()
• #1117–1119—3 Reverse Lookup—INDEX() w. MATCH()
• #1120–1121—SUMIFS(): Conditional Summation
• #1122—SUMIFS()–Conditional Summation (Three Criteria) w. Date Range
• #1123—SUMIFS()–Condition-Based Selective Cumulative Running Total
• #1124—COUNTIFS()—Single/Multiple Criteria: Duplicate Count, Instance No.
• #1201–1206—Text Formulas: UPPER(), PROPER() and LOWER(); TRIM(), VALUE(), T(), N(), REPT()
• #1207—Joining Data Strings Using CONCATENATE, &
• #1208–1209—Find & Replace
• #1210—Find and Replace—Neutralizing Wildcard Characters to Remove Them from Data
• #1211—Find & Replace—Word vs. Excel
• #1212—Find and Replace—Cell Format
• #1213–1214—Text to Columns—Delimited vs. Fixed Width
• #1214—Text to Columns—Tricks
• #1215–1216—Text to Columns—Cleaning up Numbers w. Trailing Minus Sign; Replacing Dr/Cr w. +/-
• #1217–1218—Text to Columns—Correcting Invalid Dates
• #1219–1221—LEFT(), RIGHT(), MID()
• #1219–1221—SEARCH() vs. FIND()
• #1301—Lowgical Formulas—Generally Used with IF()
• #1302–1304—Logical Formulas—AND(), OR(), IF()
• #1401–1403—Conditional Formatting
• #1403—Conditional Formatting: Data Bars, Color Scales, Icon Sets
• #1404—Conditional Formatting: Blanks, Errors, Values, Duplicates
• #1405–1407—Conditional Formatting: Formula Based
• #1501—Activating Developer Tab in v. 2007
• #1501—Activating Developer Tab in v. 2010–13
• #1501–1502—Using Form Control Buttons from Developer Tab (Spin Bar, Scroll Bar) + Limitations
• #1504—PMT
• #1504—What IF Analysis—Goal Seek
• #1505–1506—What IF Analysis—Data Tables (Sensitivity Analysis)
• #1507–1508—Data Tables (Sensitivity Analysis)—Two Inputs and multiple Output
• #1601–1604A—Category-Wise SubTotal with Groupings
• #1605–1606—Consolidate - Two and Three Dimensions
• #1701–1702—Cell Level Security
• $703—Sheet Level Security [Protect Workbook Structure]
• #703—Sheet Level Security [Sheet Properties—"Very Hidden"]
• #704—File Level Security
• #1801—Page Set Up
• #1801, 1802, 1804—Print Tricks
• #1805—1806—Print Tricks for Financial Analysts—Check Underlying Formulas
• #1807—Print Entire Workbook
• #1901—Comments—Shortcuts, Inserting Picture in Comment Box
• #1902—Split Windows, Viewing Multiple Windows—Simultaneously Working with Different Workbooks, Worksheets and Scattered Cell Ranges Simultaneously
• #1903—Hyperlinking (Ctrl + K)