EXCEL 2023 CRASH COURSE: Master Excel 2023 With This Complete Crash Course In 7 Days

CONTENTS

CHAPTER ONE

INTRODUCING EXCEL

UNDERSTANDING WHAT EXCEL IS USED FOR

LOOKING AT WHAT’S NEW IN EXCEL 2022

UNDERSTANDING WORKBOOKS AND WORKSHEETS

MOVING AROUND A WORKSHEETS

Navigating with your keyboard

Navigating with your Mouse

USING THE RIBBON

Customizing the Ribbon

Ribbon Tabs

Inserting a new tab

Creating a new group

Adding a command

Changing the names of ribbon tabs, groups, and commands

Changing the position of tabs, groups, and instructions

Contextual Tabs

Types of Command on the Ribbon

Accessing the Ribbon by using your Keyboard

USING SHORTCUT MENUS

Shortcuts for formatting data

Data entry shortcuts

Data View and Navigation

Data selection

CUSTOMIZING YOUR QUICK ACCESS TOOLBAR

WORKING WITH DIALOG BOXES

USING TASK PANES

CREATING YOUR FIRST EXCEL WORKBOOK

Getting Started on Your Worksheet

Filling in the Month Names

Entering The Sales Data

Summing the Values

Making Your Worksheet Look a bit Fancier

Creating a Chart

Formatting the Numbers

Printing your Worksheet

Printing from a Selection

Using a Print Area

Saving Workbook

CHAPTER TWO

ENTERING AND EDITING WORKSHEET DATA

Exploring Data Types

Numeric values

Text Entries

Entering Text and Values into your Worksheets

Entering Numbers and Text

Entering Dates and Times into your Worksheets

Entering date and time values

MODIFYING CELL CONTENTS

Deleting the contents of a cell

Replacing the contents of a cell

Learning some handy data-entry techniques

Automatically moving the selection after entering data

Selecting a range of input cells before entering data

Using CTRL + Enter to place information into multiple cells

Changing modes

Entering decimal points automatically

Using Autofill to enter a series of values

Using Autocomplete to automate data entry

Forcing text to appear on a new line within a cell

Using Autocorrect for shortcut data entry

Entering numbers with fractions

Using a form for data entry

Applying Number Formatting

Using shortcut keys to format numbers

Formatting numbers by using the Format Cells dialog box

Add your own custom number formats.

CHAPTER THREE

PERFORMING BASIC WORKSHEET OPERATIONS

Learning the Fundamentals of Excel Worksheet

Working with Excel windows

Moving and Resizing Windows

To move a window

Switching Windows

Closing Windows

Activating a worksheet

Adding a new worksheet to your workbook

Deleting a worksheet

Changing the name of a worksheet

Changing the tab color

Rearranging your worksheet

Hiding and unhiding a worksheet

CONTROLLING THE WORKSHEET VIEW

Zooming in or out for a better view

Viewing a worksheet in multiple windows

Comparing sheets side by side

Splitting the worksheet window into panes

Keeping the titles in view by freezing panes

Monitoring cells with a watch Window

WORKING WITH ROWS AND COLUMNS

Inserting Column

Deleting rows and columns

Changing column widths

Changing row heights

Hiding rows and columns

CHAPTER FOUR

WORKING WITH EXCEL RANGES AND TABLES

UNDERSTANDING CELLS AND RANGES

Select Single Cell Range

Selecting complete columns

Selecting complete rows

Selecting noncontiguous ranges

Selecting multi-sheet ranges

Selecting special types of cells

Selecting cells by searching

COPYING OR MOVING RANGES

Copying by using Ribbon commands

Copying by using shortcut keys

Copying or moving by using drag-and-drop

Copying to adjacent cells

Using the Office Clipboard to paste

Pasting in special ways

Using the Paste Special Dialog box

Skipping blanks when pasting

USING NAMES TO WORK WITH RANGES

Creating range names in your workbooks

Using the Create Names from Selection dialog box

ADDING COMMENTS TO CELLS

Formatting comments

Changing a comment’s shape

Resizing comments

Hiding and showing comments

Editing comments

Deleting comments

WORKING WITH TABLES

Understanding a table’s structure

Creating a table

Sorting a table

Filtering a table

Filtering a table with slicers

Changing the table’s appearance

FORMATTING WORKSHEETS

GETTING TO KNOW THE FORMATTING TOOLS

Using the formatting tools on the Home tab

Using the Format Cells dialog box

Formatting your worksheets

Using fonts to format your worksheet

Wrapping or Shrinking text to fit the cell

Purpose of Wrap Text

Shrinking text

Merging worksheet cells to create additional text space

Displaying text at an angle

Using colors and shading

Adding border lines

Using conditional formatting

Using graphical conditional formats

Using data bars

Using color scales

Using icon sets

Creating formula-based rules

Understanding relative and absolute references

Absolute references

Conditional formatting formula examples

Identifying weekend days

Highlighting a row based on a value

Displaying alternate-row shading

Creating checkerboard shading

Working with Conditional formats

USING NAMED STYLES FOR EASIER FORMATTING

Applying styles

Modifying an existing style

Creating new styles

Merging styles from other workbooks

Controlling styles with templates

UNDERSTANDING DOCUMENT THEMES

Applying a theme

Customizing a theme

CHAPTER SIX

UNDERSTANDING EXCEL FILES AND TEMPLATES

Creating a new workbook

Opening an existing workbook

Choosing your file display preferences

Enabling AutoRecover in Excel

Recovering versions of the current workbook

Recovering unsaved work

Configuring Auto recover

Password-Protecting a Workbook

Organizing your files

OTHER WORKBOOK INFO OPTIONS

Protect Worksheet options

Check for issues options

Manage workbook option

Browser view options

Compatibility mode section

Closing Workbooks

Safeguarding your work

Working with templates

Exploring Excel templates

Viewing templates

Creating a workbook from a template

Creating a worksheet template

Editing your template

Resetting the default workbook

Using custom workbook templates

Creating custom templates

Saving your custom templates

CHAPTER SEVEN

PRINTING YOUR WORK

Doing Basic Printing

Changing your Page View

Normal view

Page layout view

Page break preview

ADJUSTING COMMON PAGE SETUP SETTINGS

Choosing your printer

Specifying what you want to print

Changing page orientation

Specifying paper size

Printing multiple copies of your reports

Adjusting the page margins

Understanding page breaks

Inserting a page break

Removing manual page breaks

Printing row and column titles

Scaling printed output

Printing cell gridlines

Using a background image

Adding a header or a footer to your reports

Inserting a header

Inserting a footer

Understanding header and footer element codes

Exploring other header and footer options

EXPLORING OTHER PRINT-RELATED TOPICS

Copying page setup settings across sheets

Preventing certain cells from being printed

Preventing objects from being printed

Creating custom views of your worksheet

Creating PDF files.

Excel print's Limitations

CHAPTER EIGHT

CUSTOMIZING THE EXCEL USER INTERFACE

About the Quick Access Toolbar

Customizing the Quick Access Toolbar

Adding new commands to the Quick Access Toolbar

Other Quick Access Toolbar actions

Customizing the Ribbon

Why you may want to customize the ribbon

What can be customized

What cannot be customized

How to customize the ribbon

Creating a new tab

Creating a new group

Adding commands to a new group

Resetting the ribbon.

CHAPTER NINE

GETTING STARTED WITH EXCEL CHARTS

What’s Chart

How Excel Handle charts

Embedded charts

Chart sheets

Parts of a chart

Chart limitations

BASIC STEPS FOR CREATING A CHART

Creating the chart

Switching the row and column orientation

Changing the chart type

Applying chart layout

Adding and deleting chart elements

Formatting chart elements

MODIFYING AND CUSTOMIZING CHARTS

Moving and resizing a chart

Converting an embedded chart

Copying a chart

Deleting a chart

Copying a chart formatting

Renaming a chart

Renaming a data series in a chart

Printing charts

UNDERSTANDING CHART TYPES

Choosing a chart type

Column charts

Bar charts

Line charts

Pie charts

XY (scatter) charts

When in doubt, use a scatter chart when:

Area charts

Radar charts

Surface charts

Bubble charts

Stock charts

NEW CHART TYPES FOR EXCEL

Histogram charts

Pareto charts

Waterfall charts

Box & whisker charts

Treemap charts

Sunburst charts

Funnel charts

Map charts

CHAPTER TEN

USING ADVANCED CHARTING TECHNIQUES

SELECTING CHART ELEMENTS

Selecting with the mouse

Selecting with the Keyboard

Selecting with the chart element control

EXPLORING THE USER INTERFACE CHOICES FOR MODIFYING CHART ELEMENTS

Using the format task pane

Using the chart customization buttons

Using the ribbon

Using the Mini toolbar

Modifying the chart area

Modifying the Plot area

Working with Titles in a chart

Working with Legends

Working with Gridlines

Modifying the Axes

WORKING WITH DATA SERIES

Deleting or hiding a data series

Adding a new data series to a chart

Changing data used by a series

Using the Edit series dialog box

Editing the Series formula

Displaying data labels in a chart

Handling missing data

Adding error bars

Creating combination charts

Displaying a data table

Creating Chart Templates.

CHAPTER ELEVEN

CREATING SPARKLINE GRAPHICS

What is a Sparkline

Sparkline Types

Importance of Using Sparklines

Creating Sparklines

Customizing Sparklines

Handling hidden or missing data

Changing Sparkline types

Changing Sparkline colors and line width

Merging and Sizing Sparklines cells

Highlighting certain data points

Grouping and Ungrouping Sparklines

Deleting Sparklines

Adjusting Sparkline axis scaling

Specifying a date axis

Auto-Updating Sparklines

Displaying a Sparkline for a Dynamic Range

SUMMARY

CHAPTER TWELVE

VISUALIZING WITH CUSTOM NUMBER FORMATS AND SHAPES

Visualizing with Number Formatting

Doing basic number formatting

Using the formal cell dialog box to format numbers

Using shortcuts keys to format numbers

GETTING FANCY WITH CUSTOM NUMBER FORMATTING

Formatting numbers in thousands and millions

Hiding and suppressing zeros

Applying custom format colors

Formatting dates and time

Using a symbol to enhance reporting

Now let’s use the symbols in our formatting

USING SHAPES AND ICONS AS VISUAL ELEMENTS

Inserting a shape

Inserting SVG icon graphics

Enhancing Excel reports with shapes

Layering shapes to save space

Constructing your infographic widgets with shapes

Creating dynamic labels

Creating linked pictures

Some do ask, the importance of creating a linked picture and when is it needed?

USING SMARTART AND WORDART

SmartArt basics

WordArt basics

WORKING WITH OTHER GRAPHICS TYPES

About graphic files

Inserting screenshots

USING THE EQUATION EDITOR

CONCLUSION

BOOK 2:

EXCEL TIPS AND TRICKS

CHAPTER ONE

EXCEL TIPS

Why Excel?

What Excel Version do I have?

Ten Excel Double Mouse Clicks

Show or hide the Excel Ribbon

Use Format Painter as much as you'd like.

Rename Worksheets

Vertically fill the formulas

Quickly Edit a Shape

Auto-Adjust Column Widths

Moving Your Data Across Cells

Formatting Options for Charts Quickly

Data from a Pivot Table

Close Workbook

Power Users' Excel Keyboard Hints

Auto Recover in Excel

Recovering versions of the current workbook

Recovering unsaved work

Configuring Auto Recover

Convert Excel to PDF and PDF to Excel

Choose an Excel file

View & download your PDF file

Using Shapes

Inserting a Shape

About the Drawing Layer

Adding text to a Shape

Selecting and Hiding Objects

Formatting Shapes

Stacking Shapes

Grouping objects

Aligning and spacing objects

Reshaping Shapes

Using SmartArt

Inserting SmartArt

Customizing SmartArt

Changing the layout and style

Using WordArt

Working with Other Graphic Types

About graphics files

Fill Handle Tips

Fill Adjacent Cells with a Linear Series

Using the Fill Command, fill a Linear Series into Adjacent Cells.

Double-click the Fill Handle to fill a Linear Series.

Insert a Watermark

Adding a Header or a Footer to Your Reports

Selecting a predefined header or footer

Understanding the header and footer element codes

Other header and footer options

Macros: How to Use Macros

Inserting A Bunch of Text

Automating a task, you perform frequently

Automating repetitive operations

Making your own command

Creating a custom button

Developing new worksheet functions

Creating custom add-ins for Excel

Creating complete, macro-driven applications

Macros: Enabling VBA in Excel

Macros: Add Macros to Quick Access Toolbar

Quick Access Toolbar

Adding new commands to the Quick Access toolbar

Sharing User Interface Customizations

Move the Quick Access Toolbar

Replacing the contents of a cell

Using a form for data entry

Status Bar Metrics

Synchronous Scrolling

Worksheet Navigator

Using the Keyboard

Using the Mouse

CHAPTER TWO

FORMATTING AND LAYOUT

Ways to merge cells in Excel

Potential problems with merged cells

How to Fix a #REF Error in Excel

Solution

Conditional Formatting: Adding to Pivot Tables

Performance Symbols (up/down arrows and other indicators)

Conditional Formatting: Cell’s value

Conditional formatting: Data Bars, Color Scales, and Icon sets

Data Bars

Color scales

Icon sets

Conditional formatting: Drop-Down List

Making your own rules

Conditional formatting: Highlighting Cells

Highlighting cells that meet certain criteria

Highlighting cells based on the value of another cell

Explanation

Highlighting values that exist in List1 but not List2

Highlighting values that exist in List1 and List2

Highlighting based on Dates

Highlighting days between two dates

Highlighting days between a due date.

Conditional Formatting: Highlight Alternative Rows

Conditional formatting: Pivot Tables with Data Bars

Custom number formats

About Number Formatting

Creating a Custom Number Format

Find blank cells in Excel with a color

Fill Justify Tool

Format cells: Special Numbers

Format Painter in Multiple Cells

Freeze Panes in Excel

Lock cells in Excel

Lock Specific Cells

Lock and Protect formula cells

Number Format: Make Negative Red Numbers

Number Format: Thousands and Millions

Custom Formatting

Printing Settings

Printing from a Selection

Using a Print Area

Doing Basic Printing

Changing your Page View

Normal view

Page layout view

Page break preview

Choosing your printer

Specifying what you want to print

Changing page orientation

Specifying paper size

Printing multiple copies of your reports

Adjusting the page margins

Understanding page breaks

Inserting a page break

Removing manual page breaks

Printing row and column titles

Scaling printed output

Printing cell gridlines

Changing a Format to a Different Format

Emails – Text to Column

Split Names: Text to Columns

How to Convert Text Dates into Excel Dates

Turn Text to Values with Paste Specials

Unhide Rows and Columns in Excel

CHAPTER THREE

FORMULA TIPS

Add leading Zeros

Create a named range

Creating range names in your workbooks

Using the Create Names from Selection dialog box

Getting to Know Some AutoSum Techniques

Understanding the Difference Between Absolute and Mixed References

Making use of absolute references

Using mixed references

Calculating a Person’s Age

Method number two

About the DATEDIF function

Converting Metric System to Metric System

Using the AGGREGATE Function

Formula Error Displays: How to Avoid Them

Using the IFERROR function

Using the ISERROR command

Check your Match with F9

Step-by-step evaluation of formulas

Fill down formulas

Find the best formula

Remove formulas in Excel

Dividing the formula into rows.

Show and hide formulas in excel

VLOOKUP in an Excel Table

Excel's XLOOKUP function

XLOOKUP!

Breakdown of the formula:

In Excel, how do you utilize XLOOKUP?

CHAPTER FOUR

TABLE AND PIVOT TABLE TIPS

Excel Tables: Autofill formulas

Excel Tables: Styles

TIP

Filter and Search in Excel Tables

Filter Unique Records in Excel Tables

Excel Tables: Go to Blanks

Excel Tables: Headers Show in Columns

Linking Excel Tables to Power Pivot

Preparing your Excel Tables

Adding your Excel tables to the data model

SELECTING MORE TABLES.

Excel Tables: How to Insert

Excel Tables: Remove duplicates

Excel Tables: Row Differences

Excel Tables: Slicers

New

Excel Tables: Subtotal Feature

Excel Tables: Summarize data with subtotals

Total Row Calculations in Excel Tables

Pivot Tables: Connect Slicers to Multiple pivot tables

Adding a Slicer to a Pivot Table

Pivot Tables: Distinct count

Filter by Dates in Pivot Tables

Pivot Tables: Filter the top 5 customers

Show report filter pages in pivot tables

Pivot Tables: Sorting a pivot table.

CHAPTER FIVE

WORKING WITH DATA

11 Excel Data Entry Tips

How can I customize the Ribbon?

Protected View: An Overview

Working with a Workbook in a Web Browser

Save as a Read-Only File

Send a PDF version of the document as an electronic copy.

Generating a Filenames List

Making use of the Style gallery

Changing the size of the Formula Bar

Monitoring Formula Cells from Any Location

About the Watch Window

Customizing the Watch Window

Navigating with the Watch Window

Create Form in Excel

Add to Quick Access Toolbar (QAT)

Look through the Records

Changing an Existing Record

Criteria for the search

Recovering a Record

Data Validation in Forms

Add the calculator to the Excel Toolbar

How to Include a Calculator in a QAT

AutoSum an Array of Data in Excel

Charts: Change the Axis units

Value axis

Category axis

Charts: copying and moving charts

Resizing a chart

Moving a chart

Copying a chart

Deleting a chart

Adding chart elements

Moving and deleting chart elements

Formatting chart elements

Charts: Logarithms Scale

Using Charts to Present Information

Guidelines

Dynamic Charting

Understanding Recommended Charts

Creating a Combination Chart

Inserting a combo chart that has already been set

Using High-Low Lines in a Chart

Using Category Labels with Multiple Levels

Connecting the Text of the Chart to the Cells

How to Freeze a Chart

Creating an image from a chart

Templates

Consolidate tool in Excel

Text to Speech Conversion

Copy the Cell Above in Excel

Dropdown list with data validation

Dropdown menu

Dynamic data list

Dynamic data validation list

Filter by selection

Find and replace

Find and highlight duplicates

Find errors with the Go to Special Constants

How to Use Flash Fill in Excel

Add hyphens to serial numbers using a flash fill.

Flash Fill: Convert Values to Dates

Fix Incorrect Formatting with Flash Fill

Selecting Cells Efficiently

Using the Shift and arrow keys to choose a range

Choosing the current location

Shift + click to choose a range

Selecting noncontiguous ranges

Choosing whole rows

Selecting entire columns

Choosing multiple sheet ranges

Filling a Range with a Series Automatically

Restricting Cursor Movement to Input Cells

Transforming Data with and Without Using Formulas

Data transformation without the use of formulas

Using temporary formulas to transform data

Using Conditional Formatting to Compare Two Ranges

Working with Credit Card Numbers

Manually entering credit card numbers

Importing credit card numbers

Identifying Unused Areas

Filling the Gaps in a Report

Observing the outcomes of a distant cell

Calculating a formula in slow motion

Adding a symbol to a cell.

Editing an equation

Repeat the last command with F4

Using a delimiter to separate text

Using Inquire to audit worksheets

Inserting and exploring 3D models

Using the Action Pen and the inking tools

Statistics in Workbooks and Smart Lookup

Validate your data using data entry rules.

Goal Seek to find formula result

Goal seeks to meet your profit goal

Group Worksheet in Excel

How to create a Custom List in Excel

Hyperlinks: Fix Links to a Named Range

Power query: Consolidate Multiple worksheets

Consolidate Multiple Workbooks Using Power Query

Power Query: Unpivot Data

Quick Reports with Custom Excel Views

Excel's Smart Lookup

Sparklines: Column

Sparklines: Lines

Sparklines: Win or Loss

View Multiple Worksheets in Excel

Creating Names for Worksheet Levels

The Use of Named Constants

Wingdings Symbols in Excel

CONCLUSION

INDEX