Advanced Excel Training in Bangalore targets to make the IT sectors task more easier and simpler. Excel Next is the most populous firm in Bangalore for training people in Advanced Excel Course. We all know, Bangalore is known as the Silicon Valley mainly referred to as the IT Capital of India, it is very important for the employees to excel in the IT Sector and know the Advanced Excel Techniques. The structure and course curriculum of Advanced Excel Training in Bangalore, is designed in a way that it helps the employees to do all the calculations and visualization of the data at a fast pace.
Excel Next has been associated with many companies for providing the best Classroom Training in Bangalore. Few of our reassuring clients are Exl Service, Flipkart, Wildcraft, PWC, Tata Motors and Consulates of Canada in Bangalore where we have provided Advanced Excel Training on Regular basis.
Excel Next is a team of such an amazing league of trainers who’ll help you make your daily complicated task look easier. It helps the employees to use such tools which maximizes the amount of time saved. The employees review the Advanced Excel Training saying it is one the finest MS Excel Training Firms in the city of Bangalore. Over the years the Employees have realized that these training methods have helped them to overcome the obstacles they were facing during the working hours. Excel Training tells you about using end no. of visualization tools and techniques that not only makes your work error free by give you accurate result and even saves the extra hours which employees invested in making their Worksheets and Presentations.
Our firm doesn’t just expertise in providing Advanced Excel or VBA training but is populous in giving PowerPoint Presentation Trainings as well in Bangalore. This is helpful for the people who wants to make their Presentations attractive and well defined by the add-on tools present in the PowerPoint Presentations. To be a part of the PowerPoint Training Program, check our course curriculum by clicking here: http://www.excelnext.in/powerpoint-presentation-training-bangalore
ADVANCED EXCEL TRAINING COURSE OUTLINE
Program Name: Advanced Excel Ninja
Software: Microsoft Excel
Level: Advanced (L2 and L3)
Duration: 2 days
Document Update Date: 20-Dec-2017
Introduction: This 2-days program focuses on advanced lookups and aggregation formula. It dives in advanced data cleaning tricks, data analysis, and basics of macros. Additionally, uncommon shortcuts and time saver tricks are included.
Note: This program is ideal for learners who use Sort & Filter extensively. The program may seem fast-paced for a user who find it difficult to use Sort & Filter. It is advisable to attend both days of the program.
→ Day 1: Module #1 to #4 (part); #6
→ Day 2: Module #4 (part) to #5; #7 to #8
1) Essential Shortcuts | 0:30 min
→ Learning pre-requisites: Paste Special – Value
→ Aim: Work faster with effective shortcuts
- Data sets Navigation, Selection & Deletion
- Show all formulas (Ctrl ~)
- Inserting today’s date (Ctrl 😉
- Date format (Ctrl Shift 3)
- Using $ effectively using (F4)
2) Data Cleaning | 1:15 min
→ Learning pre-requisites: Basics of Find & Replace, Text to Columns, Filter; LEFT & RIGHT functions
→ Aim: Data cleaning precedes analysis
- Using GO TO Special to detect/select cells which are blank, hard-coded, formula driven
- Using GO TO Special with Ctrl-Enter to fill intermittent blank cells
- Using Machine Learning in Excel to clean data sets in seconds – FlashFill (v. 2013 feature)
- How to train the machine of FlashFill for lightning fast complex data cleaning
- Using TEXT function to extract day from a date. E.g. Sun, Mon…
- Using VALUE function to cleanse date, time, nos stored as text
- Using SUBSTITUTE function – the formula version of Find and Replace
- Using Find & Replace with wildcard character *
3) Data Analysis using Pivot Table | 1:00 min
→ Learning pre-requisites: Output of the data used in the Module “Data Cleaning” above
→ Aim: Using Pivot Table along with data visualization tricks to analyze data
- Essential Setting (Classic View)
- Date Grouping
- Move column field headings to right / left
- Calculating Count, Sum, %
- Color-scales based Conditional Formatting (Heat Map)
- Sparklines (in-cell trendline chart)
- Slicer (user-driven filter) – Creating them and editing the no. of columns in a Slicer
- PivotChart Shortcut (F11)
- Creating 100s of worksheets in seconds
4) Lookup Functions | 2:00 min
→ Learning pre-requisites: Basics of VLOOKUP, $ (a 5-mins. fast paced brush-up can be included)
→ Aim: Gain absolute mastery over data lookup formulas
- Overview of different forms of Lookup (use-case) – 1D exact, slabs-based, 2D, reverse, fuzzy
- VLOOKUP with TRUE – doesn’t mean approximate match
- VLOOKUP with TRUE vs. complex Nested IF statements
- VLOOKUP with MATCH for 2D lookup
- Reverse Lookup with INDEX and MATCH
- Fuzzy Lookup for similar names with spelling variations. E.g. India Motors Pvt Ltd vs. India Motor Private Ltd
→ Case Study Practice | 1:30 min
5) Aggregation Functions | 0:45 min
→ Learning pre-requisites: Basics of SUMIF, COUNTIF, $ (a 5-mins. fast paced brush-up can be included)
→ Aim: Gain absolute mastery over formulas for data consolidation and aggregation
- SUMIFS with 2 criteria
- SUMIFS with range logic (E.g. from-to date)
- SUMIFS vs. AVERAGEIFS
- Using COUNTIFS to calculate running count based on any primary key. E.g. 1st, 2nd & 3rd-time occurrences of any key – Used for developing VLOOKUP models for duplicate entries
6) Use case of important functions | 1:15 min
→ Learning pre-requisites: None
→ Aim: Understanding the use case of several Excel functions which are simple and yet powerful.
- EDATE – Calculating expiry date. E.g. 3 months contract
- EOMONTH – Calculating 10th of next month for due dates.
- LEFT, RIGHT, MID – Extract characters from a cell
- TRIM, SUBSTITUTE – Cleaning data of a cell
- PROPER, UPPER, LOWER – Change case of the text
- IF, AND, OR – Combination
- Joining strings
- Text to Columns to clean dates & change format
7) Leveraging Google to locate readymade Macros code and editing them to automate tasks | 3:00 min
→ Learning pre-requisites: Internet connection for Google search
→ Aim: Hands-on approach to creating customized Macros to fully / partially automate repetitive tasks
- Why learning Macros is a “MUST”
- Record & Run a Macros
- Linking a button to a Macros
- Understanding the basic architecture of a VBA code (Sub, End Sub, Dim)
- The right technique to search Google for readymade codes and choosing the best amongst them
- Forum vs. Blogs
- How to draft appropriate search terms
- Sub Procedure vs. User defined function (UDF)
- Basic DOs & DONTs while searching for VBA codes on Google
- Create an index of links to worksheets with addl. customization & Worksheet activate event
- Split a table in multiple sheets based on filter
- Insert rows based on cell value
8) Non-Excel time saver tricks | 0:30 min
→ Learning pre-requisites: None
→ Aim: Select non-Excel tricks
- Word Macros to automate pressing Delete-Space on PDF copied text
- Extract text from scanned images without downloading any software or uploading any files to 3rd party sites
→ Case Study Practice | 2:00 min
9) Wrap-Up – Link to post-training recap video (16 mins.) Post Training
- Trainer shares a link through which participants can sign up and get lifetime access to the post-training recap video.
What are the topics covered under the Basic to Intermediate Excel training?
Sort, Filter, $, VLOOKUP, Pivot Table, Date Formulas, Charts, Logical and Error Functions, Data Cleaning Techniques such as Text to Columns, Find and Replace,
What are the topics covered under the Advanced Excel training?
Formulas such as VLOOKUP with MATCH, INDEX with MATCH, SUMIFS, COUNTIFS, Power Query, Pivot Table, Dashboards, Macros Basics
How is our Excel training program different than others?
We provide pre-training prep videos, post-training recap videos, a 1-page summary of the full-day program, 300+ case studies customized to domains such as finance, sales, marketing, hr, audit, consulting, analytics.
How much is the training cost?
It depends on the topic, level, venue location, number of learners.