Data Analysis & Dashboarding with Microsoft Excel
- Length: 289 pages
- Edition: 1
- Language: English
- Publication Date: 2022-02-07
- ISBN-10: B09S2XLLB5
- Sales Rank: #0 (See Top 100 Books)
With the world brimming with data, data analysis is one of the most in-demand digital skills today. Microsoft® Excel® remains the most popular number-crunching tool across all businesses of any size and type. This book will teach you a highly desirable combination of data analysis with Excel skills by walking you through the data analytics lifecycle. You will download practice spreadsheets and follow step-by-step instructions to learn powerful Excel features relevant to data analysis, including
- Cleaning and organizing data,
- Analyzing data with basic and advanced functions,
- Exploring aggregated data interactively with PivotTables and PivotCharts,
- Visualizing data with charts, and
- Creating professional and interactive dashboards.
The book is designed in a way that previously covered features are recalled in future chapters to ensure you solidify your understanding of how those features tie together as building blocks of the data analytics lifecycle. You will also develop a couple of cool dashboards from scratch!
About the Author
Jasmeen Kaur is an experienced business intelligence professional passionate about supporting management’s decision-making with quantitative evidence. She has worked with data for over eight years and is keen on sharing her knowledge of the most business-relevant Excel features with her readers. She has a master’s degree from McGill University, Canada and currently works in the public service.
Introduction Lesson 1: Organizing Data 1.1 Clean Data 1. Remove non-printable characters and extra spaces 2. Parse data Parse data with Convert Text to Columns Wizard Parse data with functions 3. Convert dates stored in text format into dates 4. Convert numbers stored as text into numbers With the error indicator Changing the cell format 5. Change text to desired case 6. Spell Check 7. Remove duplicates List of functions used 1.2 Ranges Creating Named Ranges Using the Named Ranges Managing the Named Ranges 1.3 Tables Creating a Table Naming a table Managing a Table Exploring important properties of a table 1. Calculated columns 2. Structured Table References 3. Sizing Handle 4. Convert to Range 5. Column Headers 6. Tables expand or contract automatically 7. Table totals 8. Table Styles 1.4 Conditional formatting Highlight Cells Rules Top / Bottom Rules Data Bars Color Scales Icon Sets New Rule - Formatting based on User-defined functions Manage Rules 1.5 Sort and Filter Data Sorting Filtering 1.6 Data Validation Lesson 2: Analyzing Data with Functions 2.1 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 2.2 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 2.3 Cell References Relative cell references Absolute cell references Mixed cell references 2.4 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 2.5 Forecasting Using exponential smoothing Using linear regression Lesson 3: Exploring & Visualizing Data 3.1 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 3.2 PivotTable Tips and Tricks 3.3 Visualize Data with Charts Inserting a chart Chart types 1. Column Charts 2. Line Charts 3. Bar Charts 4. Area Charts 5. Pie Charts 6. Donut Charts 7. Scatter Charts 9. Treemap 10. Combo Charts Formatting a chart 3.4 Advanced Charts 1. Picture Charts 2. Progress Doughnut Chart 3. Gantt Chart 3.5 Charting Best Practices Lesson 4: Dashboarding 4.1 Identify Key Performance Indicators KPIs Creating KPIs KPI reports Building a KPI report 4.2 Create a Process Dashboard 4.3 Slicers, Timeline and PivotCharts Create a named table Insert a PivotTable Insert a PivotChart Insert Slicers and a Timeline 4.4 Steps to create a KPI dashboard 4.5 Create a Sales Dashboard 4.6 Dashboard Best Practices Designing your dashboard Distributing your dashboard Printing your dashboard
Donate to keep this site alive
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.