In today’s business scenario use of Excel is widespread in the industry. It is a very powerful data analysis tool and almost all big and small businesses use Excel in their day to day functioning. The course is designed to impart basic operations skills such as reading data into excel using various data formats, organizing and manipulating data, to some of the more advanced functionality of Excel. All along, Excel functionality is introduced using easy to understand Hands-on skill to students with the Modern tools and techniques used in an Organization.
At the end of the course student should be able to perform data analysis tools and techniques using MS Excel required in business analytics.
Getting to Know Excel
Identify the terminology and elements of the Ribbon.
Utilize the keyboard or mouse to select cells and ranges in a spreadsheet.
Create your first Excel file, enter data and create a table.
Format cells by selecting fonts and color fills to make information more attractive.
Basic Math& Math Functions
Utilize basic mathematics including multiplication and division in Excel. Learn basic math functions including SUM, ROUND and SUBTOTAL.
Essential Formula Knowledge
Cell Referencing,Learn about working with absolute and relative cell referencing, and techniques for copying formulas.
Learn to build standalone logical IF functions, including COUNT, COUNTA.
Learn to use SUMIF, COUNTIF and SUMPRODUCT to add cells only when certain conditions are met.
Basic Statistics & Test Statistics
Learn basic statistical functions Histogram, Line Graph, Scatter plot, AVERAGE, STDEV, MAX, MIN, MEDIAN and MODE. Correlation, Test Statistics, T-test, Chi Square test
LOOKUP, HLOOKUP, VLOOKUP
Learn to use LOOKUP functions to find an approximate match and return it or the corresponding value from another column.
Using PMT Formula & Goal Seek Function PV, FV, NPV, IRR, IPMT & PPMT Formulas CUMIPMT, CUMPRINC, Calculating EMI (Loan Calculator)
Learn to sort data in Excel by a single column or by multiple columns.
Learn to filter Excel data for specific words, dates, and apply multiple filters to a single data table.
Learn about creating Excel tables, and their advantages.
Create diagrams by using Smart Art
Create shapes and mathematical equations
Paste charts into other documents
Insert Picture etc
Learn to record basic Excel macros to automate your actions (instead of doing them manually).
Learn to create and modify basic PivotTables (for quickly summarizing and highlighting data).
Enable and examine macros,Create and modify macros
PivotTables, Pivot Chart
Learn to control formatting and other options in PivotTables.
Calculated Fields in PivotTables
Learn to create and modify basic calculated fields for PivotTables.
Learn to perform what-if analysis in Excel using the Scenario Manager and Goal-Seek.
Learn to work with the Format Cells dialog to apply text rotation and borders and to center data across multiple cells.
Learn to create top/bottom and highlight cell rules, as well as to apply icon sets and data bars.
Conditional Formats with Custom Rules
Learn to create custom conditional formatting rules based on formulas.
Building Column Charts
Learn how to create an effective column chart by reducing ink and 'noise' that distract from the main messages.
Data Validation Lists
Use to restrict users ability to enter invalid data in cells by providing them with a drop down list of valid options.
Learn about the setup and weaknesses of Excel’s security systems.
Text Books Reference Book:
1. Microsoft Excel 2016 - Data Analysis and Business Modeling Paperback – 1 May 2017 by Wayne L. Winston
2. Advance excel 2016 training guide Paperback – Import, 1 Nov 2017 by Ritu Arora ;BPB Publication