Spread Sheets are one of the simple and widely used applications where information is gathered, analysed and exchanged. In our day to day life, we spend a lot of time processing the information in spreadsheets. Microsoft Excel is one of the powerful tools which has various inbuilt Features and Functions which helps in analysing the data on a real time. The Understanding and usage of these capabilities will help a user to comprehend the information and report information quickly and take advantage of the advanced capabilities provided in the Tool.
The Course if useful for somebody who has no or very little knowledge on MS Excel, we start the training explaining about spreadsheets, Various version of Excel, Different tools and features, A start of Formulas, Building basic formulas and other various other things.
User Interface of Excel
- Different Things in Excel and various versions.
- Adding and Customizing things to match your need
- Workbook
Working with Data
- Cell data
- Entering comments
- Selection
- Auto Fill
- Find and Replace
- Sorting the Data
- Filtering the data
AutoFilter Tool
- Number Filters
- Text Filters
- Date Filters
- Filter by Color
- Search Box
- Manual choosing single and multiple items using checkbox.
- Keyboard shortcuts for Auto filter.
Linking Data
- Add, Edit and remove a hyper Link
Formatting data
- Formatting of the Cell.
- Introduction to Conditional Formatting
Operating Worksheet
Operating workbook
- Minimize, maximize restore workbooks
Operating Worksheet and Workbooks
- Adding Rows and Columns to a wor
- Hide and unhide worksheets
- Freeze panes and split window
- Merging and Emailing Workbook.
- About Protection and Need for Protection
- Different things in protecting sheet
- Different Things in protecting workbook
Enhancing Worksheets with Graphics
- Inserting a Word Art Object.
- Drawing Auto shapes
Reviewing, Preparing and Printing
- Performing Spell Check
- Sharing of Workbook
- Page Setup up techniques
Using Formulas and Functions
- Introduction to Formulas and Functions
- Operation Precedence
- Creating and Editing a Formula
- Assigning a Named Range
- Trace Precedents and Dependents
- Linking formulas to different sheets
- Linking formulas to different workbooks
- Edit Links, Change and break source.
Various Excel Functions.
Reporting tools
Others
- Overview of various tools and features in Excel. Review of various Ribbons in Excel.
- Different Excel Settings
- How Excel can be useful in your day to day work in office or at home?
- Using Keyboard and Shortcuts for navigation in Excel.
The Training Program is useful for somebody who has been working on MS Excel but only to a normal level, they know the basics of Excel but would like to know all the Tools and Features available in Excel to help him or her work faster and smarter, perform better analysis and prepare better reports.
Understanding Ribbons
- Overview of Standard Tabs and Ribbons.
- Understanding of Groups in Tabs and dialog box launcher for all tools.
- Minimizing Ribbons
- New Tabs for accessing Tools when objects are created.
- Developer Tab and Tabs from Add-ins
- Creating Customized Tab
- About Excel Options
About Shortcut Keys
- Advantages of using Keyboard Shortcuts.
- How to Find out Shortcuts?
- Keyboard access to the Ribbon.
- Quick Access Tool Bar.
- Navigation Keys
- Keys from Right Click, Filter Search Key
- Hidden Shortcut keys[Help]
Data Maintenance
- Data in Presentable way V/s Data in Excel understandable way.
- Right ways to maintain or organize data in Excel.
- Techniques to correct data Using Different tools.
- How does Excel understands Date and Time?
- Converting non dates to dates.
Tools to make your data right.
- Tips on Find and Replace Tool.
- Go To Special
- Import Data Text To Columns
- Overview of Data Validation.
Analysing Data Tools
- Overview of Sorting on basic level.
- Sorting with Multiple Levels and Colours.
- Custom Sort
- Data Reversing Using Sort.
- Removing Duplicates
Creating a Table
- Basic Advantages by creating Table.
- Advantages of Table in Pivot Table and other tools.
- Advantages when we use a Table range in a Formula.
New Tools in Excel
- Sparklines for line item graphical representation
- Slicers to filter interactively on Tables and Pivot Tables.
- Magic of Flash Fill/li>
Formatting data
- Basics of Conditional Formatting.
- Different tools in Conditional formatting
- How to Add, edit or delete rules.
- Need and usage of Advanced Conditional Formatting using Formulas.
Using Formulas and Functions
- Introduction to Formulas and Functions.
- Operation Precedence
- Creating and Editing a Formula
- About Relative and Absolute Reference
- Trace Precedents and Dependents
- Techniques to identify the error and fix a cell reference in a formula.
- Creating and Assigning a Named Range
- Name Manager, Naming Range from Selections.
- Techniques to build complex formulas.
- Evaluate Formula
- Watch Window
About Excel Functions
- Why do we need to know functions?
- Tips to explore new Excel Functions based on requirement.
- Tips to Learn New Functions and apply.
Common Functions
- Count,CountA,CountBlank,Countif,Countifs
- Sum,Sumif,Sumifs,Sumproduct, Subtotal
- Average,Averageif,Averageifs, Maxifs and Minifs
- Ranking and Sorting Functions, Randbetween, Rand
- About Wild Characters.
- Other Commonly used Functions.
Text Functions
- Concatenate,Trim,Clean and various useful functions.
Date and Time Functions
- Today,Date,Eomonth,Edate, Networkdays,Workday and various useful functions.
- Performing Time Calculations
Logical Functions
- If, Nestedifs/IFS Function
- AND,OR Functions
- IFError, IFNA
Lookup and Reference Functions
- Basics of Vlookup Function
- Understanding the parameters of the Function.
- How is Vlookup designed to work?
- Why does Vlookup sometimes does not give me results?
- How to check Multiple columns using vlookup?
- Fixing issues in Number matching with Number stored as text.
- Use Vlookup for Non Exact or Contains Search
- Overview of Hlookup Function
- “Using Match Function to Make the column index in a VLOOKUP() function dynamic”
- Exploring ways to tackle in situations where vlookup cannot get the results.
New Functions in 2010/13/19
- Aggregate,Networkdays.INTL
- FormulaText,Days,IFNA,SHEET,SHEETS,ISFORMULA Etc.
- Texjoin, Concat, IFS, Switch, Maxifs, Minifs
Functions-Other topics
- Overview of Other category of Functions
- Understanding of different Errors in Excel Formulas and how to handle.
- Introduction to Array Formulas
Working with Charts
- Creating and Editing Charts
- Understanding Different Objects of charts and Formatting objects
- Need and how to create Secondary Axis Charts
Reporting Functionalities
- How to Create Pivot Table?
- Understanding the different field list tools and other tools.
- Handling of report in case of data modification or addition.
- Create Different Reports and using different layouts
- Changing Calculations in Value field list, Get % of totals.
- Sorting and Filtering of Pivot Table Report
- Grouping of Date and Number fields in pivot
- Creating a Calculated Field
- Creating reports in situations where related data is in different range.
- Pivot Table Options and Show Report Filter pages
- GetPivotdata Function
- Creating Pivot Chart
- Changing Default Pivot Layout[Excel 2019+]
New Features of Pivot Table
- Table Integration in Pivot Tables
- Introduction to Power Query and Power Pivot
- Get Distinct/Unique Count Calculation
Analytical Tool
Automation Techniques in Excel
- Introduction about Macros and Automation Techniques in Excel.
Others
- Various Tips in Excel to help work faster.
- Various Tips to help you how to think in different ways and come out with logical solutions for a reporting requirement.
- Various Tips given the preparations and planning required before working with a data for a desired result.
Want to automate the daily repetitive tasks you are performing in Excel and save time? Want to create your own formula in excel? Want to design and implement an automation system in your process or team? Want a Macro which can prepare a report in Excel in seconds, Present the same in PowerPoint and forward via Email? And all this thru a click of a button. This is all possible. Excel can be programmed to meet our needs.
This training will help you get there, The Course starts with getting expertise in Recording macros successfully, how to edit the codes and how to build the VBA codes to carry out an automation process. Trainings will cover sessions on How to build a successful Automation project and showcasing of various projects and case studies.
Understanding of Macros
- Introduction of Macros.
- Planning for Automation.
- How to record a macro.
- How to edit a macro.
- How to debug a macro.
- About Macro Security.
- Different Ways of Execution of Macros.
- Tips on analyzing the requirement and recording a successful macro.
- Using Absolute and Relative Reference in Recording.
- How to record in a good way to ensure macro works rightly irrespective of different volumes of data.
- Tips to Avoid unnecessary codes in macro due to wrong or unwanted actions.
- Understanding of different Codes generated by Mouse / Keyboard Shortcut Actions.
- About XL Start Folder and its advantages.
- Locking of Macro or code protection.
- Commenting a line in Visual Basic Editor.
Introduction to VBA
- Declaring Variables and Constants.
- Different data types in VBA.
- Assigning Values to Variables.
- Different ways to check values stored in variables.
- Input and Output facilities in programming.
- Msgbox, Inputbox and application.inputbox.
- Visual Basic Editor Overview.
- Creating, Renaming and Deleting modules.
- Debugging tools like breakpoint, Step-in and Step over.
Condition Checking in VBA
- If Statements.
- Different ways if Statements can be built based on different logics.
- Overview of Select Case.
- Error Handlers.
- Goto Statement.
Loops
- Introduction of Loops, How is it useful in excel programming?.
- Iteration Loop-For Counter Loop.
- Iteration Loop-For next Loop.
- Logical Loop-Do while Loop.
- Logical Loop-Do until Loop.
- Loops – Best Practice sharing.
Functions
- VBA Functions.
- Excel Accessible Functions.
- About User Defined Functions.
- How to create and use a user defined function?.
- About Conversion Functions.
Methods, Properties and Events
- Excel Object Model.
- Thisworkbook and active workbook.
- Methods and properties for application, workbook, sheets and cells.
- Introduction to Events.
- Creating Events in Excel.
Arrays
- About Arrays, Need for Arrays.
- Creating Single, and Multi Dimensional Arrays.
- How to create dynamic Arrays.
- How to preserve previous store array element values.
End to End Automation
- Working of End to End Automation Projects in Excel.
- Calling Another Macro.
- Dialog boxes.
- Creating a Folder.
- Project Life Cycle.
- Need for a good Documentation for the Macro.
Interaction of Excel with other application.[20 Mins]
- How to take control of External applications from excel.
- Excel to outlook/power point/notepad.
- Connection to application using References in VBE.
Introduction to Userforms
- Basic Introduction of User forms[10 Mins].
- Building of a Simple User form to understand objects[20 Mins].
Others
- Sharing and Reviewing of Recording and Coding Tips.
- In the Classroom You are also going to learn things required to make a successful automation project, To build a automation project what are all the different things to be taken care, Understand customer requirements, How to come up with a idea, Find out the pros and cons of each logical way, Understanding customer knowledge level and system capabilities, developing the automation, testing, working on fixing the issues, User acceptance testing, production release and maintenance.
Overview of New Charts released in Excel 2016
- Water Chart[2016]
- Histogram Chart[2016]
- Paretto Chart[2016]
- Sunburst Chart [2016]
- Tree Map Chart [2016]
- Box and Whisker Chart [2016]
New Charts in Excel 2019
- Funnel Chart [2019]
- Map Charts[2019]
New Tools
- Smart Data Types -Stock and Geography
- Ideas tool
- Get New Add-ins, E.g.->Wikipedia, Calendar Chart Etc.
- Icons from Insert Tab [2019]
Pivot Table Layout
- Changing Default Pivot Table Layout.
New Functions In Excel 2019
- CONCAT
- IFS
- MAXIFS
- MINIFS
- SWITCH
- TEXTJOIN
New Functions In Excel 365
- LET*[Yet to be released]
- FILTER
- RANDARRAY
- SEQUENCE
- SORT
- SORTBY
- UNIQUE
- XMATCH
XLOOKUP Function
- Overview of XLOOKUP Function
- Advantages of Using XLOOKUP Function
- Working on different scenarios
Others
- List of Functions that returns array
- Spill Result and Spill Operator
- Replacement of Array Formula stroke
It’s a regular session that happens on Every Sundays. The Course is useful for students who cannot manage time to undergo a structured training on Excel or students who have done the course in the past and need a regular refresher. It’s a platform where students can pose the challenges they are facing while working with Excel at their office and get the solutions from the Trainer. A Skill once acquired can save manhours at your office. The Duration of the course is for 1 year.
Google sheets is a free online spreadsheets app accessible to all Google users. Learn different tools and functions in Google sheets.
Understanding of Basics
- Evaluating present knowledge levels of Google Sheets.
Interface of Google Sheets
- Overview of Menubar and Toolbars
- About Shortcut keys
- Enable compatible spreadsheet shortcuts
Converting Excel Files to Google Sheets.
- “Difference between opening a excel file and google
sheet file in Google sheets?”
- How to Convert excel files to Google sheets file and Vice Versa?
Challenges compared to Microsoft Excel
- Goto Special tool.
- Split Text to Columns
- Sorting Data on Colors and custom sort.
- Tables
- Sparklines, Slicers, Flashfill tools
- Missing Functions from Excel and New functions in Google Sheets.
- Limitations in Pivot Table, Power query , Power Pivot and Charts.
- Evaluate Formula
Data Maintenance
- Data in Presentable way V/s Data in Spreadsheets understandable way.
- Right ways to maintain or organize data in Google Sheets.
- Techniques to correct data Using Different tools.
- How does Google sheets understands Date and Time?
- Converting non dates to dates.
Different Tools
- Goto Range Tool by Searching in Help[F5 Key*]
- Number Formatting and Custom Formatting.
- Sort by 1 and Many levels using Sort Range
- Slicers for Data and Charts.
- Aternating Colors
- Data Validation to Control Input Data, Checkbox option
- Data Validation with Custom Formula.
- Conditional Formatting, Adding, Editing and deleting rules.
- Alternate Coloring
- Overview of Google Forms
- Creating Filter Views
- Protect Sheet and Ranges
- Split Text to Columns
- Remove Duplicates
- Trim Whitespaces
- Grouping and Ungrouping Rows and Columns
- How to Import huge External data to Google Sheets?
Using Formulas and Functions
- Introduction to Formulas and Functions.
- Operation Precedence
- Creating and Editing a Formula
- About Relative and Absolute Reference
- Techniques to identify the error and fix a cell reference in a formula.
- Creating and Assigning a Named Range
About Function
- Why do we need to know functions?
- Tips to explore new Functions based on requirement.
- Tips to Learn New Functions and apply.
Common Functions
- Count,CountA,CountBlank,Countif,Countifs
- Sum,Sumif,Sumifs
- COUNTUNIQUE, COUNTUNIQUEIFS
Text Functions
- Concatenate,Trim,Clean,Value and various useful functions.
Date and Time Functions
- Today,Date,Eomonth, Edate
Logical Functions
Lookup and Reference Functions
- Basics of Vlookup Function
- Understanding the parameters of the Function.
- How is Vlookup designed to work?
- Why does Vlookup sometimes does not give me results?
- Vlookup on different sheets and Files Using Import Range
Filter Functions
- Filter Function
- Conditions with And/Or Operators and custom formula
- Unique
- Sort
Array Functions
- Array Formula Function
- Sumproduct
Information Functions
Formulas for Tools
- Enhancing the capability of tools using Formulas
- For Conditional Formatting, DataValidation
Formulas to Data organising/shaping
- Fill up blanks
- Converting non dates to dates.
- Split Data to extract Number with leading zeroes
Working with Charts
- Creating and Editing Charts
- Dynamic charts with Checkbox
Pivot Table Reports
- Introduction to Pivot Tables
- Creating Pivot Tables
- Working on creating different Report
- Refresh and Change Data Source
- Show as % of GrandTotal
- Calculate Unique Count Values
- Create Pivot Data Group for Text, Date and Number Pivot Items.
- Renaming Pivot Data Group Name
- Sort and Filter on Rows,Columns and Values
- Using Slicers
- Creating a Calculated Field
- GetPivotData Function
- Creating Pivot Charts
About Addons
- Benefit of Add-ons
- Yet another mail merge.
Automation Techniques
- Introduction about Macros
- Recording a Simple Macro
- Overvew of Script Editor
Excel Power Query can create connections to different databases, Transform the data, create specific automations, Combine all files in a folder and many more things. Imagine searching a data in many sheets or many files. Power query can do it easily.
In this course learn the basics of Power query Tool, learn how to import data from multiple courses. How to Append and merge queries, How to Transform the data, filtering, Sorting, Unpivot, Remove duplicates etc.
Introduction to Power Query
- Challenges Faced in data analysis in Excel.
- Need for Power Query
- Setting up Power Query
- About Power Query Add-In in Excel 2013, Inbuilt in Excel 2016+.
Getting Started
- How to access tools of power query in Excel Application.
- Introduction to different power query tools in Excel.
- Outlook of Power query window.
- Query Editor
- Rows and columns.
- Tabs and Ribbon
- Formula Bar
- Navigator pane
- Preview Section
- Status Bar
- Query Settings
How to Setup a Query?
- How to take inputs for Query?
- Copy, Reference, Delete Queries.
- Changing Query Name
- Different ways to output Query.
- Refreshing Query
- Query to capture dynamic data.
- Viewing Steps in Query
- Edit and delete steps.
- Setting up Data types
- Pivot table usage from Pivot Query.
- Understanding of Data Model.
- Different ways to close and Load a Query
- Using Query Settings
- Changing Data Source
Transformation Process
- Unpivoting Columns
- Split Columns into Other Columns
- Filtering Data
- Sorting Data
- Transforming and Adding Columns Based on Existing Columns
- Selecting many columns at once.
- How to fill up blank and null values in data?
Importing data
- Importing many tables from a workbook.
- Importing data from multiple sheets
- Importing data from multiple excel files.
- Importing data from Excel, Txt , CSV / database files at once.
- Importing many excel or text files from a folder.
Different Tools in Power Query Window.
- Selecting and working on columns.
- Split Column
- Using First row as headers.
- Replace Values.
- Index column.
- Duplicate Column.
- Different options available @ right click on column select, whole table select.
- Filtering and sorting of data.
- Advanced Editor
Appending Query
- Appending Tables Individually
- Appending Many Queries
Merging Query
- Merging Basics
- Merging of Tables or Queries.
- About Dimension Table and Fact Table
- Expanding the table after merging.
Activity based learning
- How to consolidate data from may sources and combine in 1 place?
- How to remove out repeated headers in a data?
Learn how to create custom functions, Making query flexible via inputs from Excel cell. Basic exposure to reading M Language.
Different Tools in Power Query Window.
- Group By -> Summarising records, create separate Table.
- About Parameters , Adding and Managing.
- Creating Custom Column with Function.
- Query Dependencies
Merging Query
- Join Types
- Many Columns Matching with Power Query Merge
Parameters and Custom Functions
- Manual Creation of Custom Functions-Introduction
- Excel.Workbook([Content])
- Table.PromoteHeaders([Get Excel Data.Data])
Taking inputs for a query
- Taking Inputs from Excel Cell
Activity based learning How to take sheet names and include as columns?
- How to make input field in power pivot to be taking from Excel cells
- How to sort on multiple levels?
- Combine multiple rows into one value or get top 3 list items in a cell value.
- Challenges in Pivot table when using Power query data as a query[Excel 2013]
- How to Refresh query automatically when opening the file
- Converting data from bad shape to Tabular form.
- Converting data in rows as 1 entry in excel cells.
- How to ignore unwanted sheet while importing many sheets?
Pre-requisites for attending the Training.
- Participant should have undergone Power Query and Power Pivot Basic Training.
- Participant should have situations of working with large data and from many sources.
- Participant should be well versed with Pivot Table tool in Excel.
- Participant should have Microsoft Excel 365 or 2016 or later Version.
We have come to stage wherein we need to handle millions of records from multiple source, the classic pivot table and excel sheets cannot handle this. Power query and Power pivot helps us to import data or create a connection to millions of records from different sources to Excel.
In this course learn the basics of Power Pivot Tool. How to load data to data model. Create relationships, derive Pivot table interacting with multiple tables.
Introduction to Power Pivot
- Need for Power Pivot
- To Add Power Pivot in Excel Application
- Excel Front End Tools in Power Pivot
- About Data Models and adding data to Data Model
- How to Open Power Pivot Window
- Explanation of useful tools in Power Pivot Window
Getting External Data to Power Pivot
- From a Data model already created
- Importing from Different Data Sources
- Importing Data from Power Query
Formatting and set the data right
- Setting the Columns with the Right Data formats.
- Sort features
- Find Tool
Creating relationships
- How to Create relationships in Excel.
- Diagram view in Power Pivot
Working with Pivot Report
- How to use Pivot Table Reports after setup in Power Pivot
Working on columns
- Need for creating New columns
- How to perform Calculations
DAX Functions
- SUM, COUNT, SUMX, COUNTX
- DISTINCTCOUNT
- COUNTROWS
Learn building formulas skills in PowerPivot with DAX Formulas, Working on different functions.
Getting External Data to Power Pivot
- Add additional table from a source file which is already imported
Working with Pivot Report
- How to create Pivot table from Power Pivot Data models being in Excel?
Working on columns
- How to hide Columns from Client Tools
Adding Formulas
- Need to create formulas.
- Formulas on Calculated Column.
- Creating Calculated Measures.
- Different Operators used
- &&/AND, ||/OR Operators
DAX Functions
- YEAR, MONTH,DAY
- RELATED
- DIVIDE
- FORMAT
- BLANK
- IF, IFERROR,AND,OR
- CALCULATE
- FILTER
- SAMEPERIODLASTYEAR,TOTALYTD,TOTALMTD and TOTALQTD
- ISFILTERED
- ALL
Activity based learning
- How to do Vlookup Approximate match
- How to Perform 3 Column Lookup
Pre-requisites for attending the Training.
- Participant should have undergone Power Query and Power Pivot Basic Training.
- Participant should have situations of working with large data and from many sources.
- Participant should be well versed with Pivot Table tool in Excel.
- Participant should have Microsoft Excel 365 or 2016 or later Version.
