Skilled data analysts are some of the most sought-entry level professionals in the world. The demand is growing, and the supply of people who can truly do this job well is so limited,

Data analyst jobs are in demand throughout a diverse mix of companies and industries. Nowadays, almost every organization uses data and they need data analysts to analyze it. Data analysis job involves using data to make investment decisions, target customers, assess risks, or decide on capital allocations.

In this program, you will learn how to perform data analysis using Excel. You will also learn, how to create pivot tables from a range with rows and columns in Excel. Millions of users use Pivots for reporting the performance of companies and organizations.

Areas Covered:-

• Prepare weekly and monthly reports for the management 
• Automate and simplify various reporting trackers using VBA, Macros, and SQL 
• Extract existing data to format into presentable reports, charts, and graphs 
• Prepare ad hoc analyses and data reports for internal circulation 
• Create visualizations and innovative dashboards using Power BI and Tableau 
• Create powerful presentations in PowerPoint for the management 

Week 1 – Introduction to Spreadsheet & Dashboard

Date - Feb 8th & 9th at 1 PM EST, 90 mins each day 

• Eliminating duplicates from a list with just a few mouse clicks.
• Getting control of long lists of data by filtering instead of sorting.
• Discovering the Recommended PivotTables feature.
• Exploring the Recommended Charts feature in Excel 2013 and later.
• Using a keyboard shortcut so you’ll never have to manually type $ signs in a formula again.
• Getting oriented with Excel’s grid of rows and columns.
• Navigating large workbooks with ease by way of a hidden menu as well as keyboard shortcuts.
• Getting an overview of the different types of files you can create in Excel.
• Managing column widths within your spreadsheets.
• Understanding how to enter dates in an Excel spreadsheet.
• Bringing Excel’s green error-checking prompts under control by managing the underlying rules.
• Learning the nuances of copying formulas within Excel spreadsheets.
• Learn to use Excel dashboards to quickly assimilate large amounts of data.
• Create a dashboard that looks less like an Excel spreadsheet.
• Use Microsoft Query to create self-updating links to databases, spreadsheets, text files, and other data sources. 

Week 2 – Pivot tables

Date - Feb 11th & 12th at 1 PM EST, 90 mins each day

• Identifying the requirements of ideal data sets to be analyzed within your pivot tables.
• Initiating a pivot table from a list of data.
• Understanding the differences in pivot table interfaces between Excel 2010 and earlier and Excel 2013 and later.
• Adding fields to pivot tables.
• Avoiding frustration by understanding the nuances of pivot table formatting.
• Seeing multiple ways to remove fields from a pivot table.
• Understanding the nuances of formatting numbers within pivot tables.
• Learning the basics of creating pivot tables.
• Learning the nuances associated with subtotaling data within a pivot table.
• Understanding why pivot tables sometimes display amounts as text or count amounts instead of summing.
• Learning how to expand and collapse pivot table elements, thereby avoiding information overload.
• Understanding the nuances of sorting pivot tables.
• Getting past the “PivotTable field name already exists” prompt once and for all.
• Determining at a glance if you’re seeing all available data for the fields included on a pivot table or not.
• Drilling down into numbers with a double-click—or preventing other users from being able to do so.
• Discovering how pivot tables differ from worksheet formulas and learning the importance of the Refresh command.
• Discovering the Recommended Pivot Tables feature.
• Creating a macro in Excel that will set any pivot table to automatically format the number of fields that you add.
• Creating a macro that will automatically remove the words “Sum Of” from your pivot table fields.
• Drilling down into numbers with a double-click—or preventing other users from being able to do so.
• Embedding frequently used lists, such as employees, departments, or key customers, into Excel’s interface for use with any spreadsheet.
• Filtering data faster by way of the Slicer feature in Excel 2010 and later.
• Launching macros that clean up pivot tables with a single mouse click.
• Learning how to control multiple pivot tables and charts instantly with the Slicer feature in Excel 2010 and later.
• Learning how to utilize the PowerPivot feature in Excel 2010 and 2013.
• Learning the nuances associated with subtotaling data within a pivot table.
• Staving off frustration by filling blank cells within any columns that contain numbers with zeros before you create pivot tables.
• Summarizing information from Access databases and other sources.
• Using the Linked Picture feature to place pivot tables in close proximity to each other without posing conflicts.
• Wrangling unruly pivot table data by creating a macro that can automatically transform all count fields into sums and apply number formatting in one fell swoop.
• Learn about DAX formulas
• Learn about Power Query’s data sources
• Learn how to clean, transform, merge and unpivot data in Excel

Week 3 – Formulas & Functions

Date - Feb 15th & 16th at 1 PM EST, 90 mins each day

• Making VLOOKUP lookup data from the left by using the CHOOSE function.
• Learning about the MINIFS function available in certain versions of Excel 2016.
• Discovering how to use wildcards and multiple criteria within lookup formulas.
• Seeing what types of user actions can trigger #REF! errors.
• Learning about the IFNA function available in Excel 2013 and later.
• Using the IFERROR function to display something other than an #N/A error value when VLOOKUP can’t find a match.
• Avoiding the complexity of nested IF statements with Excel’s CHOOSE function.
• Learning about the MAXIFS function available in certain versions of Excel 2016.
• Performing dual lookups, which allow you to look across columns and down rows to cross-reference the data you need.
• Seeing how the HLOOKUP function enables you to perform horizontal matches.
• Comparing the MIN, SMALL, MAX, and LARGE functions.
• Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
• Restricting users to enter dates within a given range or before/after a given date.
• Learning about the MINIFS function available in certain versions of Excel 2016.
• Using the SUMIF function to summarize data based on a single criterion.
• Discovering the capabilities of the SUMPRODUCT function.
• Saving time when aggregating data from multiple worksheets with Excel’s INDIRECT function.
• Learning how VLOOKUP stops looking after it finds an initial match within a list.
• Seeing how the HLOOKUP function enables you to perform horizontal matches.
• Using Excel’s OFFSET function to dynamically reference data from one or more accounting periods.
• Learning why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP.
• Learning about the IFNA function available in Excel 2013 and later.
• Seeing how to view two worksheets from within the same workbook at the same time.
• Learning about the MAXIFS function available in certain versions of Excel 2016.

Learning Objectives:-

• Create flexible data aggregations using pivot tables
• Learn about Data Models
• Represent data visually using pivot charts
• Apply the structural basics of Excel worksheets and workbooks
• Learn to use Excel dashboards to quickly assimilate large amounts of data.
• Create a dashboard that looks less like an Excel spreadsheet.
• Create dynamic and interactive graphs with Excel’s PivotChart feature.
• Create aggregate reports using formula-based techniques

Who Should Attend?

• Freshers interested in starting their career in the Data Analytics Industry 
• People from finance, sales, supply chain, customer satisfaction who work with Data
• Professionals from logistics, accounting, marketing, trading, administration, manufacturing, research, and development
• Anyone who wants to get started in the industry of Data Visualization, Data Science, or Analytics