MS Excel: Let's Advance to The Next Level

  • 21m
  • Anurag Singal
  • Business Expert Press
  • 2017

MS Excel: Let's advance to the Next Level is an attempt to take you to the next orbit of competence in this fascinating world. It is a ready reckoner for any practitioner who seeks to further his/her understanding of the tools and utilities. The author offers to make life easier for a data analyst, thus, it will be of relevance to students, academicians, the ones pursuing corporate careers as well as entrepreneurs preparing business plans for their startups. The book explains a wide array of complex functions, ranging from V-LOOKUP to MATCH/INDEX and pivot tables in very practical, simple, and implementable manner. The segments on conditional formatting and printing tricks will help the reader present the output to stakeholders in a more efficient manner. Do try the features Excel has on offer for controlling data input as well as creating checks and balances to ensure cell/sheet/file-level security.

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)
SHOW MORE
FREE ACCESS

PEOPLE WHO VIEWED THIS ALSO VIEWED THESE

Rating 4.5 of 353 users Rating 4.5 of 353 users (353)
Rating 4.5 of 455 users Rating 4.5 of 455 users (455)
Rating 4.4 of 1162 users Rating 4.4 of 1162 users (1162)