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
- Column Headers
- 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
- Advanced Charts
- 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
- Designing your dashboard
- Distributing your dashboard
- Printing your dashboard
