Data Analysis and Visualization using Excel

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

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

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()

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

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()

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()

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

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

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

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

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

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