Excel 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.
Cover
Excel 2019 All-in-One
Copyright
Dedication
About the Author
Acknowledgements
Preface
Table of Content
Chapter 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
Chapter 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
Chapter 3 : Transforming and Managing Data
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
Chapter 4 : Formulas and Functions
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
Chapter 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
Excel 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.
Table of contents
Cover
Excel 2019 All-in-One
Copyright
Dedication
About the Author
Acknowledgements
Preface
Table of Content
Chapter 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
Chapter 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
Chapter 3 : Transforming and Managing Data
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
Chapter 4 : Formulas and Functions
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
Chapter 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
Facebook
Twitter
Linkedin
Whatsapp
