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
