Excel 2019 All-In-One

Lalwani Lokesh

Description

100% practical guide to understand and use basic to advance level Excel 2019 Key features What's New in Excel 2019 Office 365 Putting Data into Excel Transforming and Managing the Data Formulas and Functions Data Analysis techniques Data Visualization techniques Multiple ways of Data Extraction Automation in Excel through Macros DescriptionExcel is, by far, the most preferred spreadsheet program on this planet. People love it because of its simplicity and easy user interface. The primary focus of Excel is to perform basic to advanced level numeric calculations. Every industry, department, job role is dependent on Excel to perform day to day duties as well as data analysis and visualization. Microsoft Excel is available in both offline (Excel 2019) and online (Office 365) versions. If you are new to Excel or use this program day in day out, then this book will surely clear your logics and concepts of excel.This book starts with an introduction to Excel program and an overview of its interface and move towards an explanation of new features being introduced in Excel 2019 and Office 365, then an in-depth discussion on entering and transforming data. Understanding different formulas and functions with practical exercises. Afterwards, how to perform data analysis and present it using different data visualization tools. Finally, extracting data and automation of tasks through Macros.After going through this book, you will become conceptually strong in using various features of Excel. You will increase your productivity by understanding and using the right tool for the related data set.What will you learnBy the end of the book, you will come across many case studies to put your knowledge to practice and understand many tools to solve real-life business problems such as importing data into Excel from different sources, data cleaning through various tools including flash fill, bringing data together into one place using lookup functionality, analysing it to get insights through pivot tables & what if analysis, generating forecast based on past trends, exporting final reports, understanding Power features like Power Pivot/Query/Map and automating manual processes through Macros . Remember to practice along with sample data files provided in the exercise files bundle of the book to master these techniques.Who this book is forThis book is for anyone who is either new to Excel or daily Excel user. This book will take you from basic concepts of Excel to Advanced level. Whether you belong to any industry or serving any department, as an Excel user, this book will make you a Pro in Excel.Table of contents1. What's New in Excel 2019/Office 3652. Entering Data in Excel3. Transforming and Managing Data4. Formulas and Functions5. Data Analysis6. Data Visualization7. Data Extraction8. Automation in Excel through MacrosAbout the authorLokesh Lalwani, is a Microsoft Certified Office Expert with over a decade of experience in the field of training. He is a seasoned entrepreneur and Co-founder of a renowned corporate training firm - Nurture Tech Academy. He has conducted 1,000 plus corporate workshops on various technologies like Advanced Excel, Business Presentations, Power BI etc. and trained over 12,000 professionals. He is also sharing his knowledge through 5 pre-recorded courses ,on above mentioned technologies, over world's most popular E-learning platforms with an average rating of 4.5/5 from over 15,000 learners. He believes in "life is learning".His Blog links: https://www.nurturetechacademy.in/blog/His LinkedIn Profile: https://in.linkedin.com/in/lokesh-lalwani-7a6b6b55

Sujets

• Informatique

Informations

• Publié par: BPB Publications
• Date de parution: 20 septembre 2019
• Nombre de lectures: 12
• EAN13: 9789389328042
• Langue: English
• Poids de l'ouvrage: 5 Mo

Extrait

EXCEL 2019 ALL-IN-ONE
Master the new features of Excel 2019 / Office 365
by Lokesh Lalwani
FIRST EDITION 2019
Copyright © BPB Publications, India
ISBN: 978-93-88511-582
All Rights Reserved. No part of this publication may be reproduced or distributed in any form or by any means or stored in a database or retrieval system, without the prior written permission of the publisher with the exception to the program listings which may be entered, stored and executed in a computer system, but they can not be reproduced by the means of publication.
LIMITS OF LIABILITY AND DISCLAIMER OF WARRANTY
The information contained in this book is true to correct and the best of author's & publisher's knowledge. The author has made every effort to ensure the accuracy of these publications, but cannot be held responsible for any loss or damage arising from any information in this book.
All trademarks referred to in the book are acknowledged as properties of their respective owners.
 
Distributors:
BPB PUBLICATIONS 20, Ansari Road, Darya Ganj New Delhi-110002 Ph: 23254990/23254991
MICRO MEDIA Shop No. 5, Mahendra Chambers, 150 DN Rd. Next to Capital Cinema, V.T. (C.S.T.) Station, MUMBAI-400 001 Ph: 22078296/22078297
BPB BOOK CENTRE 376 Old Lajpat Rai Market, Delhi-110006 Ph: 23861747
DECCAN AGENCIES 4-3-329, Bank Street, Hyderabad-500195 Ph: 24756967/24756400
 
 
Published by Manish Jain for BPB Publications, 20 Ansari Road, Darya Ganj, New Delhi-110002 and Printed by him at Repro India Ltd, Mumbai
Dedication
 
 
 
 
Almighty God, My Family & Friends
About the Author
Lokesh Lalwani is a Microsoft Certified Office Expert with over a decade of experience in the field of training. He is a seasoned entrepreneur and the co-founder of a renowned corporate training firm, Nurture Tech Academy.
He has conducted over a thousand corporate workshops on various topics like advanced Excel, business presentations, Power BI, etc., and has trained over twelve thousand professionals. He has also shared his knowledge on these topics through five pre-recorded courses on world's most popular e-learning platforms, with an average rating of 4.5/5 from over fifteen thousand learners. His motto is, "Life is learning."
Acknowledgements
First and foremost, I would like to thank God for giving me the courage to write this book. I would like to thank everyone at BPB Publications for giving me this opportunity to publish my book.
I would also like to thank my loving and caring mother, Poonam Lalwani, my beautiful wife (my life), Mayanka Lalwani, and my awesome and always supportive brother, Sameer Lalwani, for their endless support and helping me in numerous ways.
Lastly, I would like to thank my critics. Without their criticism, I would have never been able to write this book.
Preface
If you look around in any organization, be it any department, professionals are using Microsoft Excel. This is the most commonly used spreadsheet program worldwide. It is used by professionals for basic to complex calculations and for data analysis. It is also by far the best for data visualization when compared with other spreadsheet programs. Excel helps businesses clean their raw data and analyze it using some great tools. With the help of Macros, one can even record repetitive tasks, which saves a lot of time on daily basis.
Chapter 1 What's New in Excel 2019/Office 365. It introduces to the new features, functions, charts & graphs, which has been introduced in Excel 2019/Office 365.
Chapter 2 Entering Data in Excel. It describes the ways to enter data into Excel spreadsheets. This can be either typing it manually or importing it from other sources.
Chapter 3 Transforming and Managing Data. This focus on the techniques to transform the raw data and how to manage it. Protection of worksheets/workbooks will also be discussed in this chapter.
Chapter 4 Formulas and Functions. This has the detailed discussion on formula writing and various categories of formulas and functions available in Excel.
Chapter 5 Data Analysis. It describes the most powerful tools to analyze data quickly and effectively. Tools like Pivot Table, Power Pivot, Power Query, Forecast sheet etc.
Chapter 6 Data Visualization. It introduces you to the world of data visualization tools like charts, graphs, Slicers, sparkline, timeline, conditional formatting, Power View etc.
Chapter 7 Data Extraction. It explains data extractions methods from Excel spreadsheets.
Chapter 8 Automation In Excel through Macros. It describes Macros, a tool through which one can record our repetitive actions and run them when needed, to save time and efforts.
Downloading the code bundle and colored images:
 
Please follow the link to download the Code Bundle and the Colored Images of the book:
https://rebrand.ly/ffdbc
 
 
Errata
 
We take immense pride in our work at BPB Publications and follow best practices to ensure the accuracy of our content to provide with an indulging reading experience to our subscribers. Our readers are our mirrors, and we use their inputs to reflect and improve upon human errors if any, occurred during the publishing processes involved. To let us maintain the quality and help us reach out to any readers who might be having difficulties due to any unforeseen errors, please write to us at :
[e-mail]
Your support, suggestions and feedbacks are highly appreciated by the BPB Publications' Family.
Table of Contents
1. What!’s New in Excel 2019/Office 365
Structure
Objective
Why use Excel
New interface of Excel 2019/Office 365
Workbooks and Worksheets
New Functions in Excel 2019/Office 365
Exercise file
CONCAT
TEXTJOIN
SWITCH
MINIFS
IFS
New charts in Excel 2019/Office 365
Exercise file
Map charts
Easier sharing in Excel 2019/Office 365
Insert recent links
View and store previous versions of Workbook
Swiftly store workbook in recently used folders
New features in Excel 2019/Office 365
Accuracy in selection of cells and ranges
Adding superscript and subscript to QAT
Enhanced autocomplete
New office themes
Ease of using Microsoft translator
No annoying warnings when saving CSV files
Now CSV (UTF-8) also supported
Data Loss Protection (DLP) in Excel
Enhancements in pivot table
Personalized pivot table
Field search enabled
Auto relationship detection
Drill Down buttons in Pivot Chart
Multi-select option (Slicer)
Publish to Power BI
Summary
2. Entering Data in Excel
Structure
Objective
Entering data manually
Entering data using data form
Importing Data using Get & Transform data
Applying data validation
Summary
3. Transforming and Managing Data 47
Structure
Objective
Sort, Filter and Advanced filter
Exercise file
Sorting data in Excel
Filtering Data in Excel
Applying Advanced Filter
Exercise file
Converting data into table
Exercise file
Creating a table
Choosing the correct design
Adding columns and rows
Deleting columns or rows
Enabling total row
Sorting in a table
Filtering in a table
Converting header row to column title
Reconverting table to data set
Protecting Worksheet and/or Workbook
Worksheet - protect and unprotect
Workbook - Protect and unprotect
Summary
4. Formulas and Functions 83
Objective
Writing Excel formulas and functions
Exercise file
Summing values
Subtracting values
Functions
Understanding syntax and arguments
Exercise file
Basic calculations
COUNT functions (COUNT, CountA, CountBlank)
COUNT
COUNTA
COUNTBLANK
Conditional calculation
Exercise file
SUMIF
SUMIFS
COUNTIF
COUNTIFS
AVERAGEIF
Logical functions
Exercise file
IFS
AND
OR
NOT
Text functions
Exercise file
UPPER, LOWER, and PROPER
LEFT, RIGHT, and MID
CONCATENATE, "&"
TRIM
FIND, and SEARCH
Date & Time functions
Exercise file
DAY, MONTH, YEAR
TODAY and NOW
EDATE
EOMONTH
networkdays and NETWORKDAYS INTL
NETWORKDAYS
NETWORKDAYS INTL
Lookup functions
Exercise file
Understanding references and use of $ sign
Vlookup
Hlookup
LOOKUP
INDEX and MATCH
Summary
5. Data Analysis
Structure
Objective
Pivot tables
What is Pivot Table?
Exercise file
How to create a pivot table
Creating a pivot table for a real scenario
Calculation and grouping options
Calculation options
Grouping options
Report filter pages
Calculated field
Power Pivot and Power Query
How and where to use Power Pivot and Power Query
Uploading million plus data records into Power Query
Exercise file
Accessing data in Power Pivot
Exercise file
Building relationships
Importing data from Power Pivot in to Excel sheet
What-If Analysis
Exercise file
Goal Seek (a reverse approach)
Data Table
One-variable data table
Two-variable data table
Scenario Manager
Solver
Analysis ToolPak
Exercise file
Activate Analysis ToolPak add-in
Components of Analysis ToolPak
Analyzing data using ToolPak
Forecast Sheet
Exercise file
How to use this feature
Summary
6. Data Visualization
Structure
Objective
Charts
Exercise file
Insert a chart
Add or remove chart elements
Different types of charts
Column and Bar charts
Line chart
Pie and Doughnut chart
Area chart
X Y (Scatter) and Bubble chart
Stock chart
Surface chart
Radar charts
Treemap chart (only Office 2016 and above)
Sunburst chart (only Office 2016 and above)
Histogram charts (only Office 2016 and above)
Waterfall chart (only Office 2016 and above)
Combo charts (only Office 2013 and above)
Pivot Charts
Exercise file
Slicer
Exercise file
How to insert a Slicer
Slicer options
Slicer Settings
Timeline
Exercise file
How to insert a timeline
Timeline options
Sparklines
Exercise file
How to insert a sparkline
Highlighting points in Sparklines
Sparkline styles
Handle empty or hidden cells
Conditional Formatting (CF