Become Excel Pro in Just 7 DaysExcel is the most widely used spreadsheet application on the planet. It provides a plethora of functions for accomplishing the same goal. As a result, you may find yourself performing tasks that take a long time to complete. This program, like other significant applications, has a wide range of features for various purposes. You may personalize your working environment to make it more user-friendly. If you're not going to utilize the paint function because you're working on a spreadsheet with statistics, for example, you generally don't need it on your workspace. Items you don't need will merely get in the way and clutter up your workspace.
This book provides an in-depth Microsoft Excel lesson for anybody who is new to the program or hasn't used it in a long time. It contains chapters and steps with illustrations on how to use all Excel functions, shortcuts, formulae, and symbols to compute and create basic worksheets.
The objective of this book is to assist you gaining mastery of the systems of analyzing, manipulating, and presenting data in Excel.
You'll get a unique look at methods to make Excel more productive than you ever dreamed with this compilation of favorite Excel tips and techniques in this book. This book discusses enhanced techniques of speeding up application development with Excel and using the potential of Excel to construct comprehensive programs. It is loaded with easy-to-understand information covering all elements of Excel. Some of the things you'll learn from this book are listed below
Covers the substantial changes to the version of Excel, as well as tips and shortcuts for making your Excel experience as productive as possible.
Describes how to handle function parameters, build "impossible" charts, and control the Ribbon bar.
Covers absolute vs. relative references, data input orientation changes, and sorting more than three columns.
Shows how to insert fictitious data for testing reasons.
……and there's a lot more.
Author(s): BINN, CARTY; HENDRICK, TABINA
Year: 2022
Language: English
Commentary: Complete EXCEL 2023 Complete Crash Course In 7 Days
Pages: 640
Tags: Complete EXCEL 2023 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