Author

Language

Pages

Size

Format

Category

# Data Analysis and Dashboarding with Microsoft Excel

PDF Free Download | Data Analysis and Dashboarding with Microsoft Excel by Jasmeen Kaur

## Contents of Data Analysis and Dashboarding with Microsoft Excel

• Introduction
• Lesson : Organizing Data
• Clean Data
• Remove non-printable characters and extra spaces
• Parse data
• Parse data with Convert Text to Columns Wizard
• Parse data with functions
• Convert dates stored in text format into dates
• Convert numbers stored as text into numbers
• With the error indicator
• Changing the cell format
• Change text to desired case
• Spell Check
• Remove duplicates
• List of functions used
• Ranges
• Creating Named Ranges
• Using the Named Ranges
• Managing the Named Ranges
• Tables
• Creating a Table
• Naming a table
• Managing a Table
• Exploring important properties of a table
• Calculated columns
• Structured Table References
• Sizing Handle
• Convert to Range
• Tables expand or contract automatically
• Table totals
• Table Styles
• Conditional formatting
• Highlight Cells Rules
• Top / Bottom Rules
• Data Bars
• Color Scales
• Icon Sets
• New Rule – Formatting based on User-defined functions
• Manage Rules
• Sort and Filter Data
• Sorting
• Filtering
• Data Validation
• Lesson : Analyzing Data with Functions
• Basic Functions
• SUM
• SUMIF
• SUMIFS
• AVERAGE
• AVERAGEIF
• AVERAGEIFS
• COUNT
• COUNTIF
• COUNTIFS
• MEDIAN
• MAX
• MIN
• LARGE
• SMALL
• STDDEV P
• RANK EQ
• QUARTILE INC
• PERCENTILE INC
• List of basic functions
• Logical, Date & Time, and Information Functions
• Logical Functions
• AND
• OR
• NOT
• XOR
• IF
• IF and AND/OR
• NESTED IF
• IFERROR
• Date and Time functions
• DATE
• DATEDIF
• NETWORKDAYS
• DATEVALUE
• DAY, MONTH, YEAR
• WEEKDAY, WEEKNUM
• TODAY
• TIME
• TIMEVALUE
• HOUR, MINUTE, SECOND
• NOW
• Information functions
• Cell References
• Relative cell references
• Absolute cell references
• Mixed cell references
• Lookup Functions
• VLOOKUP
• Using VLOOKUP for exact matches
• Using VLOOKUP for approximate matches
• Using VLOOKUP to merge two tables
• HLOOKUP
• Using HLOOKUP for exact matches
• Using HLOOKUP for approximate matches
• INDEX
• MATCH
• INDEX and MATCH
• TWO-WAY INDEX and MATCH
• Array Formulas
• Forecasting
• Using exponential smoothing
• Using linear regression
• Lesson : Exploring & Visualizing Data
• Create and Analyze a PivotTable
• Creating a blank PivotTable
• Adding fields to the blank PivotTable
• Nesting PivotTable fields
• Adding Filters to the PivotTable
• Adding Slicers to the PivotTable
• Making changes to a field in the Values Area
• Adding multiple copies of a field in the Values Area
• Changing PivotTable Design
• PivotTable Tips and Tricks
• Visualize Data with Charts
• Inserting a chart
• Chart types
• Column Charts
• Line Charts
• Bar Charts
• Area Charts
• Pie Charts
• Donut Charts
• Scatter Charts
• Treemap
• Combo Charts
• Formatting a chart
• Picture Charts
• Progress Doughnut Chart
• Gantt Chart
• Charting Best Practices
• Lesson : Dashboarding
• Identify Key Performance Indicators
• KPIs
• Creating KPIs
• KPI reports
• Building a KPI report
• Create a Process Dashboard
• Slicers, Timeline and PivotCharts
• Create a named table
• Insert a PivotTable
• Insert a PivotChart
• Insert Slicers and a Timeline
• Steps to create a KPI dashboard
• Create a Sales Dashboard
• Dashboard Best Practices