Basic to Advanced
Become a Pro !!
Highlights
Carrying varied experience of more than 17 years, Suneet Arora has been a Software Engineer prior to becoming a full time Microsoft Excel Trainer and Automation Consultant under ITmistic.
As a Trainer, Suneet has given training to a variety of high-profile candidates from highly reputed companies like IBM, Tata Motors, TIENS, Samco Securities, Basware, Kalra Investments, Nilkamal Ltd. to name a few. Candidates belong to different nationalities across the globe.
What is Covered ?
- Terminology
- Interface
- Quick navigation techniques
- Copy or move data
- Status bar settings
- Customize ribbon
- Quick access toolbar
- Using ribbon
- Custom lists
- Write a formula
- Autosum
- Find and Replace
- Hyperlinks
- Most used shortcut keys
- For high efficiency
- Movement and Selection
- Formatting
- Insert / delete, show / hide
- Miscellaneous
- Basic formatting
- Format cell dialog
- Cell styles
- Find & Replace formatting
- Themes
- Conditional formatting
- Apply data validation (DV) rules
- Identify cells with DV rules
- Show custom messages
- Using custom formulas
- Entering date and time
- Date functions
- Date and time formats
- Date & time calculations
- DATEDIF function
- Lookup functions
- Logical functions
- Statistical functions
- Information functions
- Text functions
- Flashfill
- Date and Time functions
- Database functions
- Nesting of functions
- Range Names
- Calculation modes
- Linking sheets & files
Some of the advanced functions include:
VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, XMATCH, IF, IFS, MAXIFS, MINIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, SUMPRODUCT, IFERROR, ISERROR, SEQUENCE, SUBTOTAL, AGGREGATE, FILTER, SORT, UNIQUE, NETWORKDAYS, DATEDIF, OFFSET, INDIRECT, FIND, SEARCH, REPLACE, SUBSTITUTE, DSUM, DCOUNT, DAVERAGE, DMAX, DMIN, DGET, SMALL, LARGE, RANK, FREQUENCY, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, LET, and many more.
- Basic sorting
- Advanced sorting
- Auto filters
- Advanced filters
- Benefits of using a Table
- Extending table
- Formatting table
- Total row
- Removing duplicates
- Outlines (group / ungroup)
- Working with large data
- Using slicers
- Copy data from a text/CSV file
- Remove blank rows
- CLEAN function
- Introduction to PowerQuery
- Import data from various sources
- Transform data
- Data cleaning
- Combing data
- Text to columns
- Preparing data for pivot table
- Creating pivot tables
- Creating Pivot charts
- Pivot table options
- Using slicer and timeline
- Ways to filter pivot table
- Sorting data in pivot table
- Pivot cache
- Refresh pivot table / chart
- Formatting pivot table /chart
- Grouping data in pivot table
- Conditional formatting on Pivot table
- GetPivotData function
- Creating hierarchy of data
- Report filter pages
- Show values as percentage
- Adding custom fields
- Improving performance
- What-if Analysis
- Scenario Manager
- Goal seek
- Data table
- Consolidating data
- Subtotals
- Creating charts
- Various types of charts
- Chart options
- Chart templates
- Fancy and Hybrid charts
- Printing charts
- Find and fix errors in formulas
- Using watch
- Add comments and notes
- Using functions to handle errors
- Protect whole workbook
- Make workbook read-only
- Protect selective sheets
- Protect specific range
- Hide formulas
- Co-authoring / sharing
- Data recovery techniques
- Create and edit macros
- Assign shortcut keys to macros
- Assign macros to objects
- Macro-enable your files
- Create ready-to-use templates
- Introduce uniformity
- Reduce the probability of errors
- Write formulas for one and all
- Save time and effort
- Prepare sheet for printing
- Apply header/footer
- Print titles
- Customizing sheet tabs
- Delete/add sheets
- Hide/unhide sheets
- Explore various views
- View side-by-side
- Synchronous scrolling
- Various view options
- Best Practices
- Improving efficiency
- Other file formats
- Excel file behind the scenes