A Comprehensive guide on Advanced Data Analysis with Workbook Samples.
The contents of this App include the following: functions and methods for Data Science & Analysis and more:
Introduction+
1 Range: AutoFill | Fibonacci Sequence | Custom Lists | Hide Columns or Rows | Skip Blanks | AutoFit | Transpose | Split Cells | Flash Fill | Move Columns | ROW function
2 Formulas and Functions: Most Used Functions | Subtract | Multiply | Divide | Square Root | Percentage | Named Range | Dynamic Named Range | Paste Options | Discount
Basics+
1 Ribbon: Formula Bar | Quick Access Toolbar | Customize the Ribbon | Developer Tab | Status Bar | Checkbox
2 Workbook: Themes | View Multiple Workbooks | AutoRecover | Merge Excel Files | Save in 97-2003 Format
3 Worksheets: Zoom | Split | Freeze Panes | Group Worksheets | Consolidate | View Multiple Worksheets | Get Sheet Name | Comments | Spell Check | Unhide Sheets | Chart Sheet
4 Format Cells: Decimal Places | Fractions | Currency vs Accounting | Text to Numbers | Numbers to Text | Custom Number Format | Format Painter | Cell Styles | Wrap Text | Merge Cells | Strikethrough | Superscript and Subscript | Check Mark | Remove Leading Zeros
5 Find & Select: Find Features | Wildcards | Delete Blank Rows | Row Differences | Copy Visible Cells Only | Search Box
6 Templates: Budget | Calendar | Holidays | Meal Planner | Invoice | Automated Invoice | Default Templates | Time Sheet | BMI calculator
7 Data Validation: Reject Invalid Dates | Budget Limit | Prevent Duplicate Entries | Product Codes | Drop-down List | Dependent Drop-down Lists | Cm to inches | Kg to lbs
8 Keyboard Shortcuts: Function Keys | Insert Row | Save As | Delete Row | Formula to Value | Scroll Lock | Bullet Points | Line Break | Show Formulas | Paste Special
9 Print: Workbook Views | Page Breaks | Headers and Footers | Page Numbers | Print Titles | Center on Page | Print Gridlines & Headings | Print Area
10 Share: Embed | PDF | Share Workbooks | OneDrive | Online | Import Access Data | Microsoft Query | Import/Export Text Files | XML
11 Protect: Protect Workbook | Protect Sheet | Lock Cells | Read-only Workbook | Mark as Final
Functions+
1 Count and Sum: Countif | Count Blank/Nonblank Cells | Count Characters | Not Equal To | Count Cells with Text | Sum | Running Total | Sumif | Sumproduct
2 Logical: If | Comparison Operators | Or | Roll the Dice | Ifs | Contains Specific Text | Switch | If Cell is Blank | Absolute Value | And
3 Cell References: Copy a Formula | 3D-reference | Name Box | External References | Hyperlinks | Union and Intersect | Percent Change | Add a Column | Absolute Reference | Address
4 Date & Time: DateDif | Today's Date | Date and Time Formats | Calculate Age | Time Difference | Weekdays | Days until Birthday | Last Day of the Month | Add or Subtract Time | Quarter | Day of the Year | Days between Dates
5 Text: Separate Strings | Count Words | Text to Columns | Find | Search | Change Case | Remove Spaces | Compare Text | Substitute vs Replace | Text | Concatenate | Substring
6 Lookup & Reference: Vlookup | Tax Rates | Index and Match | Two-way Lookup | Offset | Case-sensitive Lookup | Left Lookup | Locate Maximum Value | Indirect | Two-column Lookup | Closest Match | Compare Two Columns | Xlookup | Xmatch
7 Financial: PMT | Loans with Different Durations | Investment or Annuity | Compound Interest | CAGR | Loan Amortization Schedule | NPV | IRR | Depreciation | Profit Margin
8 Statistical: Average | Negative Numbers to Zero | Random Numbers | Rank | Percentiles and Quartiles | Box and Whisker Plot | AverageIf | Forecast | MaxIfs and MinIfs | Weighted Average | Mode | Standard Deviation | Frequency
9 Round: Chop off Decimals | Nearest Multiple | Even and Odd | Mod | Rounding Times
10 Formula Errors: IfError | IsError | Aggregate | Circular Reference | Formula Auditing | Sum Range with Errors | Floating Point Errors | IFNA
11 Array Formulas: Count Errors | Count Unique Values | Count with Or Criteria | Sum Every Nth Row | Sum Largest Numbers | Sum with Or Criteria | Most Frequently Occurring Word | Dynamic Arrays | LET function | Array Manipulation | Lambda | TextSplit
Data Analysis+
1 Sort: Custom Sort Order | Sort by Color | Reverse List | Randomize List | SORT function | Sort by Date | Alphabetize
2 Filter: Number and Text Filters | Date Filters | Advanced Filter | Data Form | Remove Duplicates | Outlining Data | Subtotal | Unique Values | FILTER function
3 Conditional Formatting: Manage Rules | Data Bars | Color Scales | Icon Sets | Find Duplicates | Shade Alternate Rows | Compare Two Lists | Conflicting Rules | Heat Map
4 Charts: Column Chart | Line Chart | Pie Chart | Bar Chart | Area Chart | Scatter Plot | Data Series | Axes | Trendline | Error Bars | Sparklines | Combination Chart | Gauge Chart | Thermometer Chart | Gantt Chart | Pareto Chart
5 Pivot Tables: Group Pivot Table Items | Multi-level Pivot Table | Frequency Distribution | Pivot Chart | Slicers | Update Pivot Table | Calculated Field/Item | GetPivotData
6 Tables: Structured References | Table Styles | Merge Tables | Table as Source Data | Remove Table Formatting | Quick Analysis
7 What-If Analysis: Data Tables | Goal Seek | Quadratic Equation
8 Solver: Transportation Problem | Assignment Problem | Capital Investment | Shortest Path Problem | Maximum Flow Problem | Sensitivity Analysis | System of Linear Equations
9 Analysis ToolPak: Histogram | Descriptive Statistics | Anova | F-Test | t-Test | Moving Average | Exponential Smoothing | Correlation | Regression
VBA+
1 Create a Macro: Swap Values | Run Code from a Module | Macro Recorder | Use Relative References | FormulaR1C1 | Add a Macro to the Toolbar | Enable Macros | Protect Macro
2 MsgBox: MsgBox Function | InputBox Function
3 Workbook and Worksheet Object: Path and FullName | Close and Open | Loop through Books and Sheets | Sales Calculator | Files in a Directory | Import Sheets | Programming Charts
4 Range Object: CurrentRegion | Dynamic Range | Resize | Entire Rows and Columns | Offset | From Active Cell to Last Entry | Union and Intersect | Test a Selection | Font | Background Colors | Sort a Range | Areas Collection | Compare Ranges
5 Variables: Option Explicit | Variable Scope | Life of Variables | Type Mismatch
6 If Then Statement: Logical Operators | Select Case | Tax Rates | Mod Operator | Prime Number Checker | Find Second Highest Value | Sum by Color | Delete Blank Cells
7 Loop: Loop through Defined Range | Loop through Entire Column | Do Until Loop | Step Keyword | Create a Pattern | Sort Numbers | Randomly Sort Data | Remove Duplicates | Complex Calculations | Possible Football Matches | Knapsack Problem
8 Macro Errors: Debugging | Error Handling | Err Object | Interrupt a Macro | Subscript Out of Range | Macro Comments
9 String Manipulation: Separate Strings | Reverse Strings | Convert to Proper Case | Instr | Count Words
10 Date and Time: Compare Dates and Times | DateDiff Function | Weekdays | Delay a Macro | Year Occurrences | Tasks on Schedule | Sort Birthdays | Date Format
11 Events: BeforeDoubleClick Event | Highlight Active Cell | Create a Footer Before Printing | Bills and Coins | Rolling Average Table
12 Array: Dynamic Array | Array Function | Month Names | Size of an Array
13 Function and Sub: User Defined Function | Custom Average Function | Volatile Functions | ByRef and ByVal
14 Application Object: StatusBar | Read Data from Text File | Write Data to Text File | Vlookup
15 ActiveX Controls: Text Box | List Box | Combo Box | Check Box | Option Buttons | Spin Button | Loan Calculator
16 Userform: Userform and Ranges | Currency Converter | Progress Indicator | Multiple List Box Selections | Multicolumn Combo Box | Dependent Combo Boxes | Loop through Controls | Controls Collection | Userform with Multiple Pages | Interactive Userform