All-in-One:: Master the new features of Excel 2019

Name: All-in-One:: Master the new features of Excel 2019 | Master the new features of Excel 2019 / Office 365
Brand: De Gruyter
Price: 14.49 EUR
Availability: OnlineOnly

Master the new features of Excel 2019 / Office 365

Lokesh Lalwani(Autor*in)

De Gruyter (Verlag)

1. Auflage

Erschienen am 16. Juli 2019

284 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-93-89328-04-2 (ISBN)

14,49 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

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
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
Chapter 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)
Exercise file
Types of CF rules
Highlight cells rules
Greater Than /Less Than
Between /Equal To
Text that Contains
A Date Occurring
Duplicate Values
Top/Bottom rules
Top/Bottom n values
Top/Bottom n % values
Above/Below Average values
Data Bars
Color scales
Icon sets
Conditional formatting based on formulas
Clear conditional formatting rules
Power Map
Exercise file
Summary
Chapter 7 : Data Extraction
Structure
Objective
Print Excel Files
Exercise file
Normal view
Page layout view
Page break preview
Understanding page setup options
Setting print area
Page orientation
Paper size
Setting page margins
Printing report headers on each page
Printing report titles on each page
Export or Upload to Power BI
Upload
Export
Share files via Email
Export data in different file formats
Summary
Chapter 8 : Automation in Excel through Macros
Structure
Objective
Macros
Difference between Macros and VBA
Record a macro
Developer tab
View tab
Status bar
How to record a Macro
Exercise file
Name a macro
Shortcut key
Where to store a macro
Description
Save a Macro
Run a Macro
Assign Macro to an Object
Check the VBA Code
Edit or delete a Macro
Macro security settings
Summary
Index

Systemvoraussetzungen

Als PDF speichern Als Link merken