Contents

Acknowledgments

About the Author

Introduction

Who this book is for

The Step by Step approach

Download the practice files

Ebook edition

Sidebar: Adapt exercise steps

Get support and give feedback

Errata and support

Stay in touch

Part 1: Create and format workbooks

1   Set up a workbook

Explore the editions of Excel 2019

Excel 2019

Excel Online

Excel Mobile Apps

Become familiar with new features in Excel 2019

Create workbooks

Modify workbooks

Modify worksheets

Merge and unmerge cells

Customize the Excel 2019 app window

Zoom in on a worksheet

Arrange multiple workbook windows

Add buttons to the Quick Access Toolbar

Customize the ribbon

Skills review

Practice tasks

Create workbooks

Modify workbooks

Modify worksheets

Merge and unmerge cells

Customize the Excel 2019 app window

2   Work with data and Excel tables

Enter and revise data

Manage data by using Flash Fill

Move data within a workbook

Find and replace data

Correct and expand upon data

Define Excel tables

Skills review

Practice tasks

Enter and revise data

Manage data by using Flash Fill

Move data within a workbook

Find and replace data

Correct and expand upon data

Define Excel tables

3   Perform calculations on data

Name groups of data

Create formulas to calculate values

Sidebar: Operators and precedence

Summarize data that meets specific conditions

Set iterative calculation options and enable or disable automatic calculation

Use array formulas

Find and correct errors in calculations

Skills review

Practice tasks

Name groups of data

Create formulas to calculate values

Summarize data that meets specific conditions

Set iterative calculation options and enable or disable automatic calculation

Use array formulas

Find and correct errors in calculations

4   Change workbook appearance

Format cells

Define styles

Apply workbook themes and Excel table styles

Make numbers easier to read

Change the appearance of data based on its value

Add images to worksheets

Skills review

Practice tasks

Format cells

Define styles

Apply workbook themes and Excel table styles

Make numbers easier to read

Change the appearance of data based on its value

Add images to worksheets

Part 2: Analyze and present data

5   Manage worksheet data

Limit data that appears on your screen

Manipulate worksheet data

Select list rows at random

Summarize data in worksheets that have hidden and filtered rows

Find unique values within a data set

Define valid sets of values for ranges of cells

Skills review

Practice tasks

Limit data that appears on your screen

Manipulate worksheet data

Define valid sets of values for ranges of cells

6   Reorder and summarize data

Sort worksheet data

Sort data by using custom lists

Organize data into levels

Look up information in a worksheet

Skills review

Practice tasks

Sort worksheet data

Sort data by using custom lists

Organize data into levels

Look up information in a worksheet

7   Combine data from multiple sources

Use workbooks as templates for other workbooks

Link to data in other workbooks and worksheets

Consolidate multiple sets of data into a single workbook

Skills review

Practice tasks

Use workbooks as templates for other workbooks

Link to data in other workbooks and worksheets

Consolidate multiple sets of data into a single workbook

8   Analyze alternative data sets

Examine data by using the Quick Analysis Lens

Define an alternative data set

Define multiple alternative data sets

Analyze data by using data tables

Vary your data to get a specific result by using Goal Seek

Find optimal solutions by using Solver

Analyze data by using descriptive statistics

Skills review

Practice tasks

Examine data by using the Quick Analysis Lens

Define an alternative data set

Define multiple alternative data sets

Analyze data by using data tables

Vary your data to get a specific result by using Goal Seek

Find optimal solutions by using Solver

Analyze data by using descriptive statistics

9   Create charts and graphics

Create charts

Perform business intelligence analysis using charts

Customize chart appearance

Find trends in your data

Create combo charts

Summarize your data by using sparklines

Create diagrams by using SmartArt

Create and manage shapes

Create and manage mathematical equations

Skills review

Practice tasks

Create charts

Perform business intelligence analysis using charts

Customize chart appearance

Find trends in your data

Create combo charts

Summarize your data by using sparklines

Create diagrams by using SmartArt

Create and manage shapes and mathematical equations

Create and manage mathematical equations

10 Use PivotTables and PivotCharts

Analyze data dynamically by using PivotTables

Filter, show, and hide PivotTable data

Edit PivotTables

Format PivotTables

Create PivotTables from external data

Create dynamic charts by using PivotCharts

Skills review

Practice tasks

Analyze data dynamically by using PivotTables

Filter, show, and hide PivotTable data

Edit PivotTables

Format PivotTables

Create PivotTables from external data

Create dynamic charts by using PivotCharts

Part 3: Collaborate and share in Excel

11 Print worksheets and charts

Add headers and footers to printed pages

Prepare worksheets for printing

Fit your worksheet contents to the printed page

Change page breaks in a worksheet

Change the page printing order for worksheets

Print worksheets

Print parts of worksheets

Print charts

Skills review

Practice tasks

Add headers and footers to printed pages

Prepare worksheets for printing

Print worksheets

Print parts of worksheets

Print charts

12 Automate repetitive tasks by using macros

Enable and examine macros

Set macro security levels in Excel 2019

Examine macros

Create and modify macros

Click a button to run a macro

Run a macro when you open a workbook

Insert form controls into a worksheet

Skills review

Practice tasks

Enable and examine macros

Create and modify macros

Run macros when you click a button

Run a macro when you open a workbook

Insert form controls into a worksheet

13 Work with other Microsoft Office apps

Include Office documents in workbooks and other files

Link Office documents to Excel workbooks

Embed files in Excel and other Office apps

Create hyperlinks

Paste charts into documents

Skills review

Practice tasks

Include Office documents in workbooks and other files

Create hyperlinks

Paste charts into documents

14 Collaborate with colleagues

Save workbooks for electronic distribution

Manage comments

Protect workbooks and worksheets

Finalize workbooks

Authenticate workbooks

Save workbooks for the web

Import and export XML data

Work with OneDrive and Excel Online

Skills review

Practice tasks

Save workbooks for electronic distribution

Manage comments

Add protection to workbooks and worksheets

Finalize workbooks

Authenticate workbooks

Save workbooks for the web

Import and export XML data

Work with OneDrive and Excel Online

Part 4: Perform advanced analysis

15 Perform business intelligence analysis

Define a Data Model

Define relationships between tables

Analyze data by using Power Pivot

View data by using timelines

Bring in external data by using Power Query

Skills review

Practice tasks

Define a Data Model

Define relationships between tables

Analyze data by using Power Pivot

View data by using timelines

Bring in external data by using Power Query

16 Create forecasts and visualizations

Create forecast worksheets

Define and manage measures

Define and display key performance indicators

Create 3D maps

Skills review

Practice tasks

Create forecast worksheets

Define and manage measures

Define and display KPIs

Create 3D maps

Keyboard Shortcuts

Glossary

Index

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.82.21