Download Abot Excel Sheet Formulas and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity!
Top 10 Excel Tricks for Interview SN Name House Score / 5 (7) IF (1) 1 Anurag Kumar Saffron 2.9 COUNTA() 17 2 Santosh Kr White 2.4 COUNTBLANK() 3 Anshu Abhishek Saffron 3.4 COUNT() 4 Anand Agrawal White - 5 Akashdeep Patel White 3.9 (2) 6 Shubham Kumar Saffron 4.5 SUM() 7 Ankur Duhoon White 3.7 MAX() 8 Avirup Dasgupta White 4.7 MIN() 9 Aayush Gupta Saffron 4.3 AVERAGE() 10 Amit Kumar Green 1. 11 Gaurav Kumar Green 4. 12 Gaurav Solanki Green - 13 Aditya Kumar Saffron 5. 14 Gourav Garg Green 4. 15 Chandan Kumar Saffron 5. 16 Divyank Mittal Saffron - 17 Anuj Gargava Green 3. Name Anurag Kumar Santosh Kr Anshu Abhishek Anand Agrawal Akashdeep Patel Shubham Kumar Ankur Duhoon Avirup Dasgupta Aayush Gupta Amit Kumar Gaurav Kumar Gaurav Solanki Aditya Kumar Gourav Garg Chandan Kumar Divyank Mittal Anuj Gargava www.excelsuperstar.org
(3) VLOOKUP: (4) CONCATENATE:
Santosh Kr Anuj Gargava Anurag Kumar (5) COUNTIF: (6) SUMIF: Saffron White Green (8) Drop-Down list: (9) Conditional Formatting (10) Text to Column
(3) VLOOKUP: (4) CONCATENATE:
Santosh Kr 2.4 Santosh Kr 2. Anuj Gargava 3.8 Anuj Gargava 3. Anurag Kumar 2.9 Anurag Kumar 2. (5) COUNTIF: (6) SUMIF: Saffron 7 3. White 5 2. Green 5 2. (8) Drop-Down list: Shubham Kumar (9) Conditional Formatting (10) Text to Column
SN Section Name 1 Excel Shortcuts 2 Excel Shortcuts 3 Excel Shortcuts 4 Excel Shortcuts 5 Excel Shortcuts 6 Essentials for Beginners 7 Essentials for Beginners 8 Essentials for Beginners 9 Essentials for Beginners 10 Essentials for Beginners 11 Essentials for Beginners 12 Essentials for Beginners 13 Essentials for Beginners 14 Essentials for Beginners 15 Essentials for Beginners 16 Essentials for Beginners 17 Essentials for Beginners 18 Essentials for Beginners 19 Essentials for Beginners 20 Essentials for Beginners 21 Essentials for Beginners 22 Essentials for Beginners 23 Essentials for Beginners 24 Essentials for Beginners 25 Essentials for Beginners 26 Essentials for Beginners 27 Essential Formatting Tricks 28 Essential Formatting Tricks 29 Essential Formatting Tricks 30 Essential Formatting Tricks 31 Essential Formatting Tricks 32 Essential Formatting Tricks 33 Essential Formatting Tricks 34 Essential Formatting Tricks 35 Essential Formatting Tricks 36 Essential Formatting Tricks 37 Essential Formatting Tricks 38 Essential Formatting Tricks 39 Essential Formatting Tricks 40 Essential Formatting Tricks 41 Essential Formatting Tricks 42 Essential Formatting Tricks 43 Essential Formatting Tricks 44 Essential Formatting Tricks 45 Essential Formatting Tricks 46 Essential Formatting Tricks
95 Data Cleaning 96 Data Cleaning 97 Data Cleaning 98 Data Cleaning 99 Data Cleaning 100 Data Cleaning 101 Data Cleaning 102 Data Cleaning 103 Data Cleaning 104 Data Cleaning 105 Data Cleaning 106 Data Cleaning 107 Data Cleaning 108 Data Cleaning 109 Data Cleaning 110 Data Cleaning 111 Data Cleaning 112 Data Cleaning 113 Data Cleaning 114 Data Cleaning 115 Data Cleaning 116 Pivot Table 117 Pivot Table 118 Pivot Table 119 Pivot Table 120 Lookup Formulas (A-Z) 121 Lookup Formulas (A-Z) 122 Lookup Formulas (A-Z) 123 Lookup Formulas (A-Z) 124 Lookup Formulas (A-Z) 125 Lookup Formulas (A-Z) 126 Lookup Formulas (A-Z) 127 Lookup Formulas (A-Z) 128 Lookup Formulas (A-Z) 129 Lookup Formulas (A-Z) 130 Lookup Formulas (A-Z) 131 Lookup Formulas (A-Z) 132 Lookup Formulas (A-Z) 133 Lookup Formulas (A-Z) 134 Lookup Formulas (A-Z) 135 Lookup Formulas (A-Z) 136 Lookup Formulas (A-Z) 137 Lookup Formulas (A-Z) 138 Lookup Formulas (A-Z) 139 Lookup Formulas (A-Z) 140 Lookup Formulas (A-Z) 141 Lookup Formulas (A-Z) 142 Lookup Formulas (A-Z)
143 Lookup Formulas (A-Z) 144 Lookup Formulas (A-Z) 145 Lookup Formulas (A-Z) 146 Lookup Formulas (A-Z) 147 Lookup Formulas (A-Z) 148 Lookup Formulas (A-Z) 149 Conditional Aggregation 150 Conditional Aggregation 151 Conditional Aggregation 152 Conditional Aggregation 153 Conditional Aggregation 154 Conditional Aggregation 155 Conditional Aggregation 156 Conditional Aggregation 157 Conditional Aggregation 158 Conditional Aggregation 159 Conditional Aggregation 160 Conditional Formatting 161 Conditional Formatting 162 Conditional Formatting 163 Conditional Formatting 164 Conditional Formatting 165 Conditional Formatting 166 Conditional Formatting 167 What-If Analysis 168 What-If Analysis 169 What-If Analysis 170 What-If Analysis 171 What-If Analysis 172 What-If Analysis 173 Report Consolidation 174 Report Consolidation 175 Report Consolidation 176 Report Consolidation 177 Report Consolidation 178 Report Consolidation 179 Printing 180 Workbook & Worksheet Security 181 Workbook & Worksheet Security 182 Workbook & Worksheet Security 183 Workbook & Worksheet Security 184 Special Tricks 185 Special Tricks 186 Special Tricks 187 Special Tricks 188 Special Tricks 189 Advanced Excel Projects - Finance 190 Advanced Excel Projects - Finance
New lecture name Best Excel Shortcuts - Part 1 of 4 Best Excel Shortcuts - Part 2 of 4 Best Excel Shortcuts - Part 3 of 4 Best Excel Shortcuts - Part 4 of 4 How to create custom shortcuts using Quick Access Toolbar (QAT)? Three easy ways to do SUM or total of numbers Basics of MAX & MIN formula Use of MAX formula in Finance (Tax & Billing) Use of SMALL & LARGE formula in Finance Using MEDIAN formula to remove outliers Using Paste Special (Value, Transpose) for daily work Paste Special (Transpose) vs TRANSPOSE Formula Using COUNTBLANK to count blank cells Difference between COUNT vs. COUNTA Using Status Bar for Quick Calculations Using SUMPRODUCT Function in Finance & Costing Easiest way to calculate YoY Growth Percentage What is POWER formula How to calculate the maturity amount of Fixed Deposit Basics of ROUND formula Basics of ROUNDUP & ROUNDDOWN formula Uses of ROUNDUP & ROUNDDOWN formula in Finance Basics of MROUND formula Advanced Trick of Round Formula in Finance Using Autofill feature to create automatic list of Dates Using Autofill feature to create automatic list of Numbers 7 ways to use Format Cells How to hide a text without coloring the cell white? What is - Numbers stored as Text? How to write full cheque numbers with missing zeroes? What is Superscript inside Format Cells? Basics of formatting tabular datasets How to format lengthy headings using Wrap Text & Alignment? How is Format Painter used to copy & paste formats? How to use Format Painter multiple times? How to remove all formatting in one click? Working with Tables How to color multiple sheet tabs? How to use Freeze Panes for Rows & Columns at the same time? Grouping Gridlines How to create a simple drop-down list with Data Validation? How to remove a drop-down list? How to activate the Developer Tab in 2007-2019 in Excel? How to create an interactive Spin Button for an Excel Dashboard? How to create an interactive Scroll Bar for an Excel Dashboard?
7 Tricks of Comments - Basic to Expert 4 Tricks of Hyperlinking - Basic to Expert How to hide a sheet so that no one can unhide easily? How to Sort numbers from largest to smallest? How to Sort text alphabetically (A-Z)? How to Sort dates oldest to newest? How to Sort rows by cell color? Best Practice before you sort any dataset How to do 2-level Sorting? The hidden trick of Horizontal Sorting The hidden trick of Sorting using a custom list The hidden trick to add a blank row between every row using Sort Basics of Filter & Calculating SUM on the filter list The most common mistake Filter users make while choosing datasets Importance of SUBTOTAL formula in a Filtered dataset SUBTOTAL formula's confusing parameters - Mystery Solved Number Filter option in Filter How to navigate a Filtered dataset using Shortcuts? How is Advanced Filter different than General Filter? The hidden trick to apply seprate Filters on two datasets of the same worksheet? The hiddent trick to paste values on a Filtered dataset How to use Slicer on a Filtered dataset? Use of Cell Referencing ($) in formulas - Explained Why does a Date in Excel looks like a random number? How to calculate the duration using two dates with timestamp? Date formulas - DAY, MONTH, YEAR & DATE - Part 1 Date formulas - DAY, MONTH, YEAR & DATE - Part 2 Date formulas - TEXT & WEEKDAY - Part 1 Date formulas - TEXT & WEEKDAY - Part 2 Why is EDATE referred as Expiry Date? Why is EOMONTH referred as End of Month? Date formulas - TODAY & NOW with Shortcuts The hidden trick to rectify dates format using Text to Columns How to use WORKDAY.INTL formula to calculate project deadline? How to use NETWORKDAYS.INTL to calculate working days between two dates? The hidden formula of DATEDIF How to find the 1st, 3rd and 5th Saturday of any month Basics of Logical Formula - Part 1 Basics of Logical Formula - Part 2 Basics of Logical Formula - Part 3 Quick Recap of Logical Formulas with advanced exercises Using multiple Logical formulas - IF, AND, OR in an advanced exercise Logical Check Formulas for Text, Numbers, & Errors including IFERROR How to remove duplicate values? Using UPPER, PROPER, & LOWER formulas to change the case of the texr Using TRIM formula to remove excess spaces in a sentence Using VALUE formula to convert numbers stored as text to a number format Using LEN formula to calculate the number of characters in a cell
Create a Dashboard from 25+ worksheets using INDIRECT formula Use VLOOKUP & INDIRECT formula to pull data from multiple sheets How to do 3D Lookup using VLOOKUP, MATCH, INDIRECT & name ranges? Basics - Using OFFSET formula to create Dynamic Ranges Using OFFSET to create dynamic calculations - Part 1 (Intermediate) Using OFFSET to create dynamic calculations - Part 2 (Advanced) Basics of COUNTIF formula Basics of SUMIF formula Basics of AVERAGEIF formula COUNTIFS & SUMIFS - Part 1 (Intermediate) COUNTIFS & SUMIFS - Part 2 (Advanced) Exercise - Using SUMIFS & COUNTIFS with 3 criteria Exercise - Using SUMIFS to find sum of values between two dates Using COUNTIFS to find duplicates and reconcile two lists The hidden trick of COUNTIFS to calculate running count based on the ID value The hidden trick of COUNTIFS to do VLOOKUP for duplicate values The hidden trick of SUMIFS to calculate running sum total based on the ID value Conditional Formatting - Color cells based on errors, blanks, duplicates, user-defined value Conditional Formatting - Color cells using Data Bars, Icon Sets, Color Scales Create a Dashboard for comparing Actual vs Budget using Conditional Formatting Using Conditional Formatting for changing the cell color based on list value chosen Formula-based Conditional Formatting - color the entire row based on user-selected value Formula-based Conditional Formatting - color the cell that matches two user criterias Formula-based Conditional Formatting - Highlight cells that have prohibited values Basics of Data Table feature of What-If Analysis Using Data Table feature of What-If Analysis with INDIRECT formula Advanced use case of Data Table feature for Loan EMI payments Limitations of using Scroll Bar & Spin Button Basics - Using Goal Seek to back-calculate Basics - Scenario Manager for Best, Worst & Base Case The hidden trick of Consolidation using SUM for multiple sheets How to use CONSOLIDATE feature of Excel? - Part 1 How to use CONSOLIDATE feature of Excel? - Part 2 How to use SUBTOTAL feature of Excel using one criteria? How to use SUBTOTAL feature of Excel using two criteria? The hidden trick of SUBTOTAL feature & Go To (Special) to merge cells into blocks Top 10 Printing Tricks & Settings Level of Password Security - File vs. Sheet vs. Workbook Structure How to hide a sheet so that no one can unhide easily? How to remove “Protect sheet” Password with Notepad? How to remove remove “Protect workbook” Password with Notepad? The hidden trick to Compare two Excel files The hidden trick to join cell values without using CONCATENATE The hidden trick to activate Data Entry Form How to Copy Text from Image | OneNote Tutorial PDF to Word Converter | Without Software Convert "Dr" & "Cr" suffix of Tally dayasets into positive & negative values Clean-up complex Bank Statement sheet of Borrowers
Analyse payments made to Suppliers using FlashFill, VLOOKUP & Pivot Table Extract multiple voucher nos. from a cell to create a list Create a Frequency Distribution of sales ticket size using Pivot Table? Calculate Stock Revaluation write-off amount using VLOOKUP with TRUE and MATCH Form 26AS - TDS | What is Form 26AS | Format PAN Assessee Status - using MID & VLOOKUP Calculate Sales Commission using advanced SUMIFS and VLOOKUP with TRUE Allocate sales value of a project to the individual team members Sales Visualization - without Charts UNIQUE Concat & TextJoin Project - TextJoin & IF IFS & SWITCH MAXIFS & MINIFS Filter SORT SORTBY Sequence LET Why do we learn Excel VBA Macros? How to learn Excel VBA Macros? How to enable Developer Tab in Excel? Basics of Developer Tab in Excel Important Macro Security Settings in Excel How to save Macros in Excel for future use? How to create a Macros in Excel? How to run a Macro in all open Workbooks? Using a Button to run a Macros in Excel Running a Macro in different ways in Excel Understanding VBA Workspace in Excel (Basics) Watch a Macro being recorded Searching Google for VBA Codes - using the 3 magic words Searching Google for VBA Codes - in Blogs vs Forum
- 5:
- 5:
- 4:
- 4:
- 2:
- 1:
- 2:
- 4:
- 4:
- 1:
- 4:
- 2: 6:32 This Course Includes:
- 3: 1:44 Excel Mastery eBook
- 2: 3:17 Get 2-Year Access
- 3:
- 2:
- 2:
- 1:
- 3:
- 4:
- 3:
- 4:
- 4:
- 3:
- 2:
- 3:
- 1:
- 3:
- 2:
- 2:
- 3:
- 2:
- 2:
- 1:
- 4:
- 3:
- 0:
- 3:
- 2:
- 3:
- 4:
- 3:
- 7:
- 6:
- 3:
- 3:
- 1:
- 2:
- 1:
- 2:
- 2:
- 4:
- 4:
- 3:
- 3:
- 1:
- 4:
- 4:
- 3:
- 2:
- 8:
- 1:
- 3:
- 2:
- 6:
- 4:
- 3:
- 1:
- 1:
- 4:
- 1:
- 4:
- 3:
- 1:
- 6:
- 3:
- 3:
- 4:
- 6:
- 3:
- 5:
- 4:
- 12:
- 9:
- 4:
- 3:
- 2:
- 1:
- 2:
- 1:
- 3:
- 5:
- 5:
- 2:
- 2:
- 5:
- 3:
- 4:
- 5:
- 4:
- 4:
- 4:
- 3:
- 8:
- 7:
- 7:
- 2:
- 6:
- 8:
- 7:
- 10:
- 27:37:
- 3:
- 7:
- 13:
- 6:
- 5:
- 7:
- 5:
- 5:
- 8:
- 4:
- 6:
- 5:
- 4:
- 5:
- 5:
- 4:
- 6:
- 5:
- 7:
- 6:
- 8:
- 5:
- 6:
- 5:
- 4:
- 9:
- 10:
- 4:
- 7:
- 6:
- 9:
- 10:
- 3:
- 4:
- 3:
- 11:
- 8:
- 11:
- 6:
- 3:
- 7:
- 6:
- 7:
- 6:
- 3:
- 7:
- 2:
- 7:
- 7:
- 4:
- 11:
- 16:
- 6:
- 7:
- 6:
- 5:
- 4:
- 9:
- 10:
- 7:
- 8:
- 8:
- 11:
- 7:
- 3:
- 6:
- 4:
- 3:
- 0:
- 2:
- 2:
- 3:
- 6:
- 9:
- 8:
- 7:
- 3:
- 10:
- 7:
- 1:
- 13:
- 9:
- 7:
- 3:
- 4:
- 8:
- 7:
- 4:
- 5:
- 6:
- 2:
- 2:
- 2:
- 4:
- 1:
- 2:
- 1:
- 2:
- 3:
- 5:
- 2:
- 3:
- 4:
- 3:
- 2:
- 8:
- 6:
