Data Analysis with Excel

Introduction

Data sample quick start

Review of Excel Foundational Concepts

Class Exercise

Set validation rules to specify what type of data can be entered

Create drop down lists for data entry

Connect drop down lists to data within a spreadsheet

Use input messages to help users enter correct data

Display error alerts when invalid data is entered

The use of keyboard for frequent commands

Customizing the Quick Access Toolbar

Using the Auto Fill tool to fill cells with data

Copy and paste content using the clipboard

Using print preview

Amend page setup options

Print worksheets

Use page layout view and page break preview

Add headers and footers

Zoom in and out on a spreadsheet

Freeze panes to simplify working with large spreadsheets

View different sheets and workbooks side by side on the same screen

Clearly structure and present worksheet content

User various tools to format text

Change number formats

Align text and numbers

Modify the size of rows and columns

Apply borders for clarity

Hide gridlines on a worksheet

Merge multiple cells into one cell

Wrap text within a cell

Apply styles to cells for quick formatting

Use the Format Painter tools for speed and consistency in appearance

Clear formatting and tidy up content

Check the spelling of text

Highlighting records that meet specific criteria

Draw attention to high and low figures

Use colors and icons to represent values

Use formulas to set advanced rules and highlighting

Modify conditional formatting rules

Manage conditional formatting steps

Using relative references within formulas

Using absolute references within formulas

Using mixed references within formulas

Create meaningful names for individual cells and ranges of cells

Use the name manager to edit named references

Use named references within formulas

Use named ranges within drop-down lists

Write formulas to add, subtract, multiply and divide

Edit formulas within a cell

Add and edit formulas within the formula bar

Copy a formula to other cells

View results of calculations in the status Bar

Use brackets to change the order of a calculation

Calculate the percentage of a given value

Calculate the percentage difference between values

Display percentages correctly

Use of SUM, COUNT, COUNTA, COUNTBLANK, MIN, MAX, AVERAGE, PRODUCT and SUMPRODUCT functions

Copy a function to other cells

Work with the tools in the formulas tab

Use the insert function tool to find and build functions

Use the CONCATENATE functions to join data held in different cells

Use the PROPER, UPPER and LOWER functions to change the case of text

Use the LEFT and RIGHT functions to extract characters from cells

Use the TEXT functions to convert values to text

Format dates and times correctly

Create sequences of dates, weekdays, months or years

Calculate date and time differences

Date and Time Functions

Use the IF function to ask questions of data and display information

Use the IF function to analyze data and perform calculations

Nest multiple IF functions within the same formula

Combine IF with the AND function

Combine IF with the OR function

Use the SUMIF and SUMIFS function

Use the COUNTIF and COUNTIFS Functions

Use the AVERAGEIF and AVERAGEIFS functions

Use the VLOOKUP function to search for and find data

Edit a VLOOKUP function

Use the MATCH function to search for Data

User the INDEX function to return Values

Combine the MATCH and INDEX functions

Use the HLOOKUP function

Using the ISBLANK function

Use the ISERROR function

Use the ISNUMBER function

Use the ISTEXT function

Use the ROUND function

Use the ROUNDUP function

Use the ROUNDDOWN function

Create and array function

Combine an array function with another function (

Use the FV function to calculate the future value of an investment

Use the PMT function to calculate loan payments

Use the RATE function to calculate necessary interest rates

Manually create groups of data to simplify working with large list

Automatically create an outline of data with groups

Use the subtotal tool to perform calculations on groups of data

Sort records into alphabetical or numerical order

Sort records by date

Sort records by color

Use drop-down filters to find specific records

Create customized filters to find records that meet criteria

Filter data using the advanced filter

Set multiple criteria

Copy filtered data to other cells

Filter data to display unique records

Use Goal seek to identify target figures

Create Scenario Manager reports to compares possible outcomes

Create Data Tables with one or two Variables

Hide rows and columns

Hide worksheets

Protect formulas within cells

Password protect worksheets and workbooks

Protect ranges of cells

Copying and pasting hidden data

Dealing with Missing Values

Cleaning Messy Data with Excel

Cleaning Messy Data with Power Query

Concepts Mastering Concepts Of Statistical Analysis

Create PivotTables

Change the layout of PivotTables

Sort data vertically and horizontally

Filter data within a PivotTable

Search for data within a PivotTable

Customize field names

Format data displayed within Pivot Tables

Drill down to display data

Create Pivot Charts

Set PivotTable options

Change the calculations performed

Work with subtotals within a PivotTable

Create top 10 reports

Group data into date periods

Group data into number ranges

Display values as percentages of total figures

Create running totals

Create calculated fields

Use slicers to filter multiple PivotTables

Apply conditional formatting to data within PivotTables

Use formulas to extract data from PivotTables

Create column, line, pie, area, scatter and radar charts

Change the chart type

Use the quick layout options

Draw attention to chart content with effective formatting

Apply styles to charts

Add trendlines to charts

Add, edit and format labels

Explode pie charts for visual effects

Customize charts in various ways

Dashboard creation

Link one cell to another

Link data across sheets in the same Excel workbook

Link data across two Excel files

Update and manage linked data

Format for Data Analysis Report

Work with the Developer tab

Record relative reference and absolute reference macros

Run macros using the keyboard

Create a new tab to hold macros

Edit VBA script to correct macros

Analysis project on Health, Sales , Ecommerce, HR etc

Relationships

Data Modelling

Considerations for Sample Size

Random Sampling

Understanding Confidence Intervals

95% Confidence Intervals

Using Excel’s “Exponential Smoothing” add-in for forecasting

Forecasting using Excel’s “Forecast Sheet” feature

Correlation analysis

Visual evaluation using scatter plots

Understanding the coefficient ranges

Simple and Multiple Regression Formulas and Analysis

Understanding the Coefficient of Determination (R²)

Interpreting Regression Results

Creating scatter plots with trendlines for simple regression visualization

Understanding the use of R² for comparing multiple models

Evaluating which model best explains the data based on R²

Formulate a Hypothesis

Interpret the results of your analysis

Consider the Limits of Hypothesis testing

One-Way ANOVA

Two-Way ANOVA

Understanding factors and parameters of ANOVA

Final Project to analyze hypothetical or Real world case data in business, finance, operations etc