Menu

Workshops

Projects

Blogs

Careers

Find Jobs


For Business / Universities

Corporate Training

Hire from US

Academic Up-skilling


All Courses

Choose a category

Loading...

All Courses

All Courses

logo

Loading...

FOR BUSINESSES

Corporate Upskilling

Hire from Us

FOR Universities

Academic Training

More

Data Analysis and Visualization using Excel

Learn the ins and outs of Microsoft Excel to effectively analyze and visualize data according to industry standards.

12 weeks long course | 100% Online

Learn from leading experts in the industry

Project based learning with 2 industry level projects that learners can showcase on LinkedIn.

Learn Key Tools & Technologies MS Excel

Book a Free Demo Session

Enter your phone number and book a FREE Demo session on your favourite courses now!
Please enter a valid email
Please enter a valid number

Syllabus

This course is full of best-in-class content by leading faculty and industry experts in the form of videos and projects

Course Overview

Data analysis and visualization are at the core of business operations in organizations across many industries. As a result of this, there’s an ever-growing demand for engineers skilled in data analysis using tools such as Microsoft Excel. 

By taking this course, you will learn about basic Excel functions, data formatting, inserting tables and graphs, conditional functions, and Pivot tables. This course will also elaborate on advanced Excel features such as analysis of large data sets, list functions, Visual Basics for Applications (VBA), and setting up email automation.

Throughout this course, you will complete projects that require extensive use of Microsoft Excel to analyze large datasets as seen in the industry.

Who can enrol in this course?

Students and working professionals seeking to gain a fundamental understanding of analyzing and visualizing data sets using Microsoft Excel.

Course Syllabus

On a daily basis we talk to companies in the likes of Tata Elxsi and Mahindra to fine tune our curriculum.

Week 1 - Course Introduction and Fundamentals

To use Excel for data analysis and visualization, learners must first understand how to navigate the UI. It is also important to know about the basic formatting, shortcuts, and features of Microsoft Excel.

In this week, you will learn about

  • Launching Excel and Version Check
  • Basics of the Interface
  • Saving an Excel Document
  • Common Shortcuts
  • Entering and Editing Data
  • Relative and Absolute Cell References
  • Formatting 
  • Customizing the Quick Access Toolbar
  • Excel Self Help

Week 2 - Basic Excel Functions and Modification of Worksheets

Functions in Excel can be used to execute logical, mathematical, or statistical calculations on data in the spreadsheet. For ease of analysis and interpretation, data should be arranged in a legible manner using rows and columns. Learners must understand the basic Excel features to effectively work with data on the spreadsheet.

In this week, you will learn about 

  • Basic Functions
    • Design of Excel Functions
    • Understanding SUM(), MIN() & MAX()
    • Understanding AVERAGE()
    • Understanding COUNT()
    • AutoSum and Autofill/Flashfill
  • Modification
    • Inserting New Sheets, Rows and Columns
    • Deleting Sheets, Rows and Columns
    • Moving or Copying Sheets
    • Sheet formatting
    • Excel Options
    • Customize Ribbon

Week 3 - Data Formatting, Working with Shapes & Images, Creating your First Chart

When working with data sets, it is important to format the information according to a given set of specifications. This lets users organize, understand, and process the information with ease. Users can also insert charts to visualize the information in the data set. Knowing how to organize and present data is a vital skill for learners. 

In this week, you will learn about 

  • Data Formatting
    • Cell formatting – Merge, Alignment
    • Borders
    • Currencies and Percentages
    • Format Painter
    • Conditional Formatting
    • Find & Search
  • Shapes & Images
    • Inserting Shapes & Images
    • Formatting Excel Shapes
    • SmartArt
  • Creating your First Chart
    • Creating Chart
    • Chart Options
    • Types of Charts
    • Chart formatting
  • Date Functions
    • DATE() & DATEVALUE()
    • NETWORKDAYS()
    • EOMONTH() & EDATE()

Week 4 - Excel Templates, Excel Options and Printing your Excel Worksheets, Introduction to Tables

Excel Templates let users create spreadsheets with a common format. It is important to understand how to use these templates, as well as how to format the layout of a spreadsheet to print it. In addition to this, learners must also know how to insert tables into their spreadsheet to present their data and findings. 

In this week, you will learn about  

  • Excel Templates
    • Importing Excel Template
    • Custom Template
  • Printing Worksheets
    • Print Preview
    • Margins and Scaling
    • Page Layout
    • Headers and Footers
    • Specific Range
  • Tables
    • Table Options
    • Table Formatting
    • Table References
    • Working with Formulas
    • Benefits of Tables

Week 5 - Conditional Functions and Other Functions

Conditional functions are commonly used in logical operations when cells must fulfill specific conditions. When working with data, it is important to understand how conditional functions are integrated into an Excel spreadsheet. 

In this week, you will learn about 

  • Conditional Functions
    • Understanding IF()
    • Understanding SUMIF() & SUMIFS()
    • Understanding COUNTIF()
    • Use of AND & OR with IF()
    • Understanding IFERROR()
  • Other Functions and Text Functions
    • Understanding COLUMN() & ROW()
    • Understanding TEXT()
    • Understanding LEFT(), RIGHT() & MID()
    • Understanding LEN(), CONCAT()
    • Understanding TRIM(), PROPER(), FIND()

Week 6 - Pivot Tables and Lookup Functions

Pivot Tables are used to summarize, calculate, and study a dataset in order to identify patterns or draw comparisons. When sorting through large sets of data, Lookup functions are used to identify values of data in a column or row that matches the value in a particular position. It is important for learners to understand the use of Pivot Tables and Lookup Functions when working with data sets in Excel.

In this week, you will learn about 

  • Pivot Tables
    • Data source
    • Pivot Table Structuring
    • Pivot Table Options
    • Pivot Table Formatting
  • Lookup Functions
    • Understanding VLOOKUP()
    • Understanding HLOOKUP()
    • Understanding INDEX & MATCH
    • Understanding XLOOKUP()
    • Understanding OFFSET()

Week 7 - New Functions, Data Tab, and Introduction to Power Query

Power Query  is a tool in Excel that lets users import data from a variety of sources. Using this tool, users can organize, reshape, and transform the data as needed in order to then create reports or visual charts. Learners must understand how the Power Query tool works as it is essential in business and industries that are driven by data. 

In this week, you will learn about

  • New Functions
    • Understanding UNIQUE()
    • Understanding FILTER()
    • Understanding IFS()
    • Understanding SORT(), SORTBY()
    • Understanding SWITCH()
  • Data Tab
    • Data Import
    • Data Cleaning and Transform
    • What Power Query is
    • Use of Power Query

Week 8 - Advanced Pivot Table Functions and PowerPivot Tools

Using Power Pivot in Excel, users can import large sets of data, and analyze it without reductions in speed or functionality. This feature allows users to develop data models and merge data from different sources. Power Pivot is an essential skill for learners to have when it comes to working with large data sets.

In this week, you will learn about 

  • Advanced Pivot Table Functions
    • Fields, Items & Sets
    • Pivot Charts
    • Slicers and Timeline
    • Group & Ungroup
    • GETPIVOTDATA()
  • PowerPivot Tools
    • Basics of Excel Power Pivot
    • More Tables and Relationships
    • Creating Data Models
    • KPIs

Week 9 - Large Data Sets, File Protection, Named Ranges, and More in Data Tab

Information collected in the industry is often in the form of large data sets with raw information. It is important for learners to know how to sort through this data, and validate it to ensure maximum quality. 

In this week, you will learn about 

  • Working with Large Data Sets
    • Freeze Panes
    • Grouping Data
    • Consolidating Data from Multiple Worksheets
  • File Protection
    • Protecting Specific Cells in a Worksheet
    • Protecting the Structure of a Workbook
    • Adding a Workbook Password
  • Data Tab
    • Custom Sorting
    • What-If Analysis
    • Text to Columns
  • Data Validation
    • Understanding Data Validation
    • Custom Data Validation Error
    • Dynamic Formulas in Validation

Week 10 - List Functions and Excel Automation using Macros

Operations can be performed on lists of data which are often found in rows or columns of an Excel spreadsheet. Repetitive tasks can also be automated using macros in Excel. It is important for learners to know the functions used to analyze data in lists, as well as the use of Macros to automate tasks. 

In this week, you will learn about 

  • List Functions
    • Understanding DSUM()
    • Understanding DSUM() with AND/OR()
    • Understanding DAVERAGE()
    • Understanding DCOUNT()
    • Understanding SUBTOTAL()
  • Excel Automation using Macros
    • Understanding Macros
    • Creating Macro with Macro Recorder
    • Editing Macros with VBA
    • Creating Buttons

Week 11 - VBA Basics

Using Visual Basic for Applications (VBA), users can automate tasks by making macros. This optimizes the efficiency of a spreadsheet, and lets users carry out many functions at once. It is important for learners to understand how and why VBA is used in Excel. 

In this week, you will learn about 

  • Basics of VBA
  • VBA Editor
  • Adding Code to VBA procedure
  • VBA variables
  • IF Statements
  • Loops
  • Automating Tasks
  • Input and User Messages
  • Handling Errors

Week 12 - Email Automation and Industry Application

With Excel, users can send out emails to a set of people automatically through email automation. This feature optimizes time and efficiency, which is important in the industry when working with a large number of people. 

In this week, you will learn about 

  • Email Automation
    • Basics of Emailing Section
    • Understanding Email Routine
    • Email loop
  • Excel Tips and tricks
  • Industry Standards
  • How to Apply your Learning

Our courses have been designed by industry experts to help students achieve their dream careers

Industry Projects

Our projects are designed by experts in the industry to reflect industry standards. By working through our projects, Learners will gain a practical understanding of what they will take on at a larger-scale in the industry. In total, there are 2 Projects that are available in this program.

Data Cleaning and Transformation using Pivot Table and Charts

In this project, you will be given a large set of data from ABC Private Limited that is manufacturing and selling various types of automobiles. With this information, you will have to format the data and summarize product sales (in terms of total sum of sales, average sales, standard deviation of sales, variance of sales, maximum sales, and minimum sales). In addition to this, you will also have to create a spreadsheet with information on fields status, product line, and sales using Pivot Table. From the concepts learned in the course, you will have to make customized ribbons, analyse the ordered quantities, present the data appropriately, clean the data, and highlight the top 10% of values using conditional formatting.

Report Creation using PowerQuery, Macro VBA, List functions, and Data Validation Functions

You will be given a set of data about loan applicants that will have to be analysed and presented with Dashboard in Excel. With the given data, you will have to make separate spreadsheets as required for each feature of the dashboard (for example, gender ratio, client income type, housing type, family status, occupation type, and more). You will also be required to create a Macro for data formatting, create a VBA code to convert selected data into PDF from the applicant data, and check spelling mistakes in the workbook using a VBA code.

Our courses have been designed by industry experts to help students achieve their dream careers

Ratings & Reviews by Learners

Skill-Lync has received honest feedback from our learners around the globe.

Google Rating
4.6

Instructors profiles

Our courses are designed by leading academicians and experienced industry professionals.

image

1 industry expert

Our instructors are industry experts along with a passion to teach.

image

12 years in the experience range

Instructors with 12 years extensive industry experience.

image

Areas of expertise

  • Full Stack development
  • SQL

Similar Courses

Got more questions?

Talk to our Team Directly

Please fill in your number & an expert from our team will call you shortly.

Please enter a valid email
Please enter a valid number
Try our top engineering courses, projects & workshops today!Book a FREE Demo