Program Highlights
Assignments and Case Studies
Work hands-on with 20+ case studies, assignments, and labs with mentor support.
Real-life Excel Projects
Work on 5+ real-life projects with mentor support to get applied understanding.
Job Readiness
Intensive interview preparation from Day 1 to prepare candidates for interviews with our network of 2000+ hiring partners.
Lifetime Access to LMS
Access live class recordings, study materials, and quizzes to recap concepts.
Skills Covered
Data Cleaning
Data Analysis & Visualization
Interactive Dashboards
Macros & VBA
Projects
- The objective is to find out which variables drive good ratings for the restaurants. Also find out which 'Sponsor' has the most popular restaurant brands registered on Zomato. At the end, create a logical storyline for Pune residents and their preferences, along with a Dashboard.
- Do time series analysis on the data. Determine the trends in “confirmed”, “deceased”, and “recovered” cases as per location. Create a logical storyline at the end, with a Dashboard.
- You are in discussions with a consulting company for providing transport for their employees. For this purpose, you are tasked with understanding how their employees prefer to commute presently (between home and the office). What is the general behavior and preference, in terms of male vs female employees, for transport in the given dataset? Create a logical storyline at the end, with a Dashboard which can clearly give employee preferences.
Learning Path
Why ExcelR
Industry-Based Course Curriculum
Value-Added Course: Tableau (self-paced)
Work Hands-On With 5+ Real-Life Projects
Dedicated Placement Cell
Support through WhatsApp, Calls, & Emails
Lifetime eLearning Access
Course Curriculum
- Introduction
- MS office Versions(similarities and differences)
- Interface(latest available version)
- Row and Columns
- Keyboard shortcuts for easy navigation
- Dаtа Entry(Fill series)
- Find and Select
- Clear Options
- Ctrl+Enter
- Formatting options(Font,Alignment,Clipboard(copy, paste special))
- Referencing, Named ranges,Uses,Arithemetic Functions
- Mathematical calculations with Cell referencing(Absolute,Relative,Mixed)
- Functions with Name Range
- Arithmetic functions(SUM,SUMIF,SUMIFS,COUNT,COUNTA,COUNTIFS,AVERAGE,AVERAGEIFS,MAX,MAXIFS,MIN,MINIFS)
- Logical functions
- Logical functions:IF,AND,OR,NESTED IFS,NOT,IFERROR
- Usage of Mathematical and Logical functions nested together
- Referring dаtа from different tables: Various types of Lookup, Nested IF
- LOOKUP
- VLOOKUP
- NESTED VLOOKUP
- HLOOKUP
- INDEX
- INDEX WITH MATCH FUNCTION
- INDIRECT
- OFFSET
- Advanced functions
- Combination of Arithmatic
- Logical
- Lookup functions
- Dаtа Validation(with Dependent drop down)
- Date and Text Functions
- Date Functions:DATE,DAY,MONTH,YEAR,YEARFRAC,DATEDIFF,EOMONTH
- Text Functions:TEXT,UPPER,LOWER,PROPER,LEFT,RIGHT,SEARCH,FIND,MID,TTC, Flash Fill
- Dаtа Handling::Dаtа cleaning, Dаtа type identification, Remove Duplicates, Formatting and Filtering
- Number Formatting(with shortcuts)
- CTRL+T(Converting into an Excel Table)
- Formatting Table
- Remove Duplicate
- SORT
- Advanced Sort
- FILTER
- Advanced Filter
- Dаtа Visualization: Conditional Formatting, Charts
- Conditional formatting(icon sets/Highlighted colour sets/Dаtа bars/custom formatting)
- Charts:Bar,Column,Lines,Scatter,Combo,Gantt,Waterfall,pie
- Dаtа Summarization: Pivot Report and Charts
- Pivot Reports:Insert,Interface,Crosstable Reports;Filter,Pivot Charts,
- Slicers:Add,Connect to multiple reports and charts
- Calculated field, Calculated item
- Dаtа Summarization: Dashboard Creation, Tips and Tricks
- Dashboard:Types,Getting reports and charts together, Use of Slicers.
- Design and placement: Formatting of Tables,Charts,Sheets,Proper use of Colours and Shapes
- Connecting to Dаtа: Power Query, Pivot, Power Pivot within Excel
- Power Query: Interface, Tabs
- Connecting to dаtа from other excel files, text files, other sources
- Dаtа Cleaning
- Transforming
- Loading Dаtа into Excel Query
- Connecting to Dаtа: Power Query, Pivot, Power Pivot within Excel
- Using Loaded queries
- Merge and Append
- Insert Power Pivot
- Similarities and Differences in Pivot and Power Pivot reporting
- Getting dаtа from dаtаbases, workbooks, webpages
- VBA and Macros
- View Tab
- Add Developer Tab
- Record Macro:Name,Storage
- Record Macro to Format table(Absolute Ref)
- Format table of any size(Relative ref)
- Play macro by button
- shape
- as command(in new tab)
- Editing Macros
- VBA:Introduction to the basics of working with VBA for Excel: Subs, Ranges, Sheets
- Comparing values and conditions
- if statements and select cases
- Repeat processes with For loops and Do While or Do Until Loops
- Communicate with the end-user with message boxes and take user input with input boxes, User Form
Contact Our Team of Experts
