Data Analysis with Excel: Tips and tricks to kick start your excel skills
- Length: 345 pages
- Edition: 1
- Language: English
- Publisher: BPB Publications
- Publication Date: 2019-08-27
- ISBN-10: 9388176677
- ISBN-13: 9789388176675
- Sales Rank: #3049328 (See Top 100 Books)
Simplify the Usage of Excel Features and Functionalities, with the Help of Examples
Key Features
- Basic components of excel, the available menu options and tools, some customizations working with multiple workbooks and worksheets, most frequently used and useful keyboard shortcuts.
- An initial introduction to what are formulas, functions, and their components.
- Usage of multiple examples to explain the application in real world scenario.
- Explaining of different chart types available in Excel and how best it can be used for creating different views of data, good graphics, visualizations for dashboards, and so on.
- Different ways and methods to consolidate data, complex analysis, and prediction or forecast based on trends.
- Excel features that help in automating tasks.
Description
Book is written in a lucid manner to explain the concepts in details, with adequate emphasis on examples. The intent of this book is to explain and simplify the usage of Excel features and functionalities, with help of examples. Book will enable users in working efficiently and will increase their workplace productivity. It explains:
- Functions for calculations
- Graphing tools
- Pivot tables
- Data analysis tools
- Micro programming
- Add-ins that enable core complex operations.
What You Will Learn
- Performing functions with shortcut keys.
- Formulas and functions
- Data visualization with new chart types
- Gantt and Milestone Chart
- Smart Art and Organization Chart
- Putting data in perspective with Pivots
- Complex data analysis using Tool Pak
- Forecasting in Excel
- Mail Merge using Excel
- Macros in Excel
- What is in Excel 2019
Who This Book is For
Anyone who wants to learn Excel, do the mundane task easily and quickly, do complex calculations, analysis huge data from internal or external sources, take decisions based on predictions, do forecasting, create plans and charts, monitor dashboards for progress and status, and so on.
About the Author
Manisha Nigam is a seasoned management professional with twenty plus years of information technology experience, working with multinationals across the globe. Her expertise in Excel comes from the vast experience she has in using and understanding the software over the years, that helped her in working efficiently and managing complex programs. A certified PMP (PMI – Project Management Professional), CSM (Scrum Alliance – Scrum Master), TOGAF 9.1 (The Open Group – Enterprise Architecture) and holds a post-graduate degree in computer science and business management from prestigious universities in India.
Her LinkedIn Profile: linkedin.com/in/mnigam10
Cover Page Title Page Copyright Page Preface About the Author Table of Contents 1. Getting Started with Excel 1.1 Workbook and Worksheets 1.2 Navigation with Keyboard 1.3 Tabs and Ribbons 1.3.1 Contextual Tabs 1.3.2 Tab and Ribbon Visibility 1.4 File Menu 1.5 Quick Access Toolbar 1.6 Excel Options 1.7 Create a New Workbook, Print and Save 1.7.1 Create New File 1.7.2 Print File 1.7.3 Save and Save As File 1.8 Understanding Worksheet Basics 1.8.1 Commands for Entering/Editing Data 1.8.2 Insert/Delete Cells 1.8.3 Formatting Cells 1.8.4 Data Tools 1.8.5 Working with Excel tables 1.8.6 Controlling View of Worksheet 1.9 Protecting Excel Workbook and Worksheet 1.9.1 Protect Workbook 1.9.2 Protect Sheet 1.10 Importing Data into Excel 1.10.1 Importing from Text 1.10.2 Importing from Web 1.10.3 Import from Database 1.11 Exporting Data from Excel 1.11.1 Export to File 1.11.2 Export table to SharePoint List 1.12 Sharing in Excel 1.12.1 Co-authoring (new) 1.12.2 Shared Workbooks (legacy) 2. Perform Functions with Shortcut Keys 2.1 Keys for Menus 2.2 Move on a Worksheet or Workbook 2.3 Select Cells, Columns, Rows or Objects 2.4 Select Cells with Special Characteristics 3. Formulas and Functions 3.1 Understanding Formulas 3.2 Operators in Formula 3.3 Defined Names 3.3.1 Create New, Delete, Edit Named Range 3.3.2 Using Named Range 3.4 Calculations 3.5 Functions in Formula 3.6 Relative and Absolute Addressing 3.7 Referencing Cells Outside the Worksheet 3.8 Referencing Cells Outside the Workbook 3.9 Logical Functions 3.9.1 Using IF 3.9.2 Using nested IF 3.9.3 Writing Conditional Expressions: IF Combined with AND / OR 3.10 Summarizing Functions 3.10.1 COUNT, COUNTA, COUNTBLANK 3.10.2 COUNTIF, COUNTIFS 3.10.3 SUM, AVERAGE, PRODUCT 3.10.4 SUMIF, SUMIFS 3.10.5 SUMPRODUCT 3.10.6 Calculations using SUM with OFFSET function 3.10.7 SUBTOTAL 3.11 Text Functions 3.11.1 TEXT 3.11.2 FIND 3.11.3 LEN and TRIM 3.11.4 CONCATENATE 3.11.5 LEFT, MID and RIGHT 3.12 Lookup and Reference functions 3.12.1 VLOOKUP, HLOOKUP 3.12.2 MATCH, INDEX 3.12.3 OFFSET 3.12.4 INDIRECT 3.13 Date and Time Functions 3.13.1 TIME 3.13.2 DATE 3.13.3 TODAY 3.13.4 EOMONTH 3.13.5 NETWORKDAYS 3.13.6 YEARFRAC 3.14 Math Functions 3.14.1 INT 3.14.2 ROUND, ROUNDDOWN, ROUNDUP, MROUND 3.14.3 CEILING, FLOOR 3.14.4 ABS, SQRT, SUMSQ 3.14.5 EXP, LN 3.14.6 RAND, RANDBETWEEN 3.15 Statistical Functions 3.15.1 MIN, MAX 3.15.2 RANK.EQ, RANK.AVG 3.15.3 MEDIAN, MODE.SNGL, MODE.MUTL 3.15.4 QUARTILE.INC(/.EXC), PERCENTILE.INC(/.EXC) 3.15.5 PERCENTRANK.INC, PERCENTRANK.EXC 3.15.6 STDEV.P(/.S), VAR.P(./S) 3.15.7 CORREL, COVARIANCE.P(/.S) 3.16 Financial Functions 3.16.1 PV and FV 3.16.2 PMT, IPMT and PPMT 3.16.3 NPV and XNPV 3.16.4 IRR and XIRR 3.17 Error Handling 3.17.1 Error Types 3.17.2 Error Handling Functions 3.18 Formula Auditing 3.18.1 Show Formulas 3.18.2 Trace Precedents, Trace Dependents, and Remove Trace Arrows 3.18.3 Trace Error 3.18.4 Error Checking, Evaluate Formula 3.18.5 Watch Window 4. Data Visualization with New Chart types 4.1 Chart Types and When to Use Them 4.2 Waterfall Chart 4.3 Histogram 4.4 Pareto Chart 4.5 Box & Whisker Chart 4.6 Treemap Chart 4.7 Sunburst Chart 4.8 Sparkline Chart 4.9 3D Map 4.9.1 Create a Tour 4.9.2 Create a New Scene 4.9.3 Play Tour/Scene, Create Video 5. Gantt and Milestone Chart 5.1 Creating a Gantt Chart in Excel 5.2 Creating a Milestone Chart in Excel 6. SmartArt and Organization Chart 6.1 SmartArt Graphics 6.2 Organization Chart 6.2.1 Insert Graphic 6.2.2 Change Layout, Color & Text 6.2.3 Add/Delete Boxes 6.2.4 Move Boxes 7. Putting Data in perspective with Pivots 7.1 Verify the Source 7.2 Format Data for Sync 7.3 Recommended Pivot Tables 7.4 Adding Slicers & Timelines 7.5 Adding Calculated Fields in Pivot 7.6 Group Field in Pivot 7.7 Interactive Dashboard with Pivot Tables, Charts and Slicers 7.8 Consolidate Data from Different Sources in Pivot 7.8.1 Method #1: Data with Identical Column Structure using Pivot Wizard 7.8.2 Method #2: Data with Identical Column Structure using Microsoft Query 7.8.3 Method #3: Data with Different Column Structure using Pivot 7.9 Using Power Pivot Add-in 7.9.1 Add to Data Model 7.9.2 Create a Relationship between Datasets 7.9.3 Adding Calculated Column 7.9.4 Creating Perspectives in Power Pivot 7.10 Get and Transform / Power Query tool 7.10.1 Different Merging Options 8. Complex Data Analysis using ToolPak 8.1 Enabling Analysis ToolPak in Excel 8.2 Descriptive Statistics in Excel 8.3 ANOVA in Excel 8.3.1 ANOVA: Single Factor 8.3.2 t-Test following ANOVA 8.3.3 ANOVA: Two Factor with Replication 8.3.4 ANOVA: Two Factor without Replication 9. Forecasting in Excel 9.1 Forecast Sheet - One-Click Forecasting 9.1.1 Create Forecast Worksheet 9.1.2 Customize Forecast using Options 9.2 FORECAST Functions 9.2.1 FORECAST.ETS 9.2.2 FORECAST.ETS.CONFINT 9.2.3 FORECAST.ETS.STAT 9.3 What-if Analysis Tools 9.3.1 Scenario Manager 9.3.2 Goal Seek 9.3.3 Data Table 9.3.4 Solver Add-In 10. Mail Merge using Excel 10.1 Mail Merge from Word using Excel 10.1.1 Preparing the Database 10.1.2 Creating the Template 10.1.3 Generating the Merged document 10.1.4 Merged Field Codes 10.2 Mail Merge from Excel (without Word) 10.2.1 Prepare Database in Excel 10.2.2 Prepare Mail Merge Template in Excel 10.2.3 Print Preview the merged documents 11. Macros in Excel 11.1 VBA Quick Overview 11.1.1 Basic Language Constructs 11.2 Enabling Developer Tab 11.3 Create Macro 11.4 Record Macro 12. What’s in Excel 2019
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.