EXCEL 2022 COMPLETE GUIDE: The Concise Step-by-Step Practical Guide to Master Everything About Excel in 7 Days or Less

CONTENTS
BOOK 1:
EXCEL FOR BEGINNERS
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
CHAPTER FIVE
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 FORMULAS & FUNCTIONS
CHAPTER ONE
INTRODUCING FORMULAS AND FUNCTIONS
	UNDERSTANDING FORMULA BASICS
	Using Operators in Formulas
	Using functions in your formulas
	Examples of formulas that use functions
		Functions arguments
		More about functions
		Priority for the master operator
		Show Formulas
		Fix Formulas
		Use absolute references wherever possible
		Apply conditional formatting to your documents
		Validate your data
	ENTERING FORMULAS INTO YOUR WORKSHEETS
		Entering formulas manually
		Entering formulas by pointing
		Pasting range names into formulas
		Inserting functions into formulas
	Function entry tips
		Editing Formulas
	USING CELL REFERENCES IN FORMULAS
		Changing the types of your references
		Referencing cells outside the worksheet
		Referencing cells in other worksheets
		Referencing cells in other workbooks
	USING FORMULAS IN THE TABLE
		Summarizing data in a table
		Using formulas within a table
		Referencing data in a table
	CORRECTING COMMON FORMULA ERRORS
		Handling Circular references
		So, how do you discover a circular reference?
		Specifying when formulas are calculated
	USING ADVANCED NAMING TECHNIQUES
		Using names for constants
		Using range intersections
		Applying names to existing references
	WORKING WITH FORMULAS
		Not hard-coding values
		Using the formula bar as a calculator
		Making an exact copy of a formula
		Converting formulas to values
CHAPTER TWO
USING FORMULAS FOR COMMON MATHEMATICAL OPERATIONS
	Calculating Percentages
		Calculating percent of goal
		Calculating percent variance
		Calculating percent variance with negative values
		Calculating a percent distribution
		Calculating a running total
		Applying a percent variance with negative values
		Dealing with divide-by-zero errors
	ROUNDING NUMBERS
		Rounding numbers using formulas
		Rounding to the nearest penny
		Rounding to significant digits
		However, there are a few guidelines that you should follow:
		COUNTING VALUES IN A RANGE
		Explanation:
		Explanation:
		USING EXCEL’S CONVERSION FUNCTIONS.
		This function has the following arguments:
CHAPTER THREE
USING FORMULAS TO MANIPULATE TEXT
	WORKING WITH TEXT
		Using Text Function
		Joining text strings
		Settings text to sentence case
		Removing spaces from a text string
		Extracting parts of a text string
		Finding a particular character in a text string
		Substituting text strings
		Counting specific characters in a cell
		How does this work?
		Adding a line break within a formula
		Cleaning strange characters from text fields
		Adding leading zeros in Excel
		Using the DOLLAR function
CHAPTER FOUR
USING FORMULAS WITH DATES AND TIMES
	UNDERSTANDING HOW EXCEL HANDLES DATES AND TIMES
		How Excel stores dates:
		How Excel stores time:
		Understanding dates serial numbers
		Example of a serial number in Excel Date and Time
		Entering dates
		Using Two-Digit Years to Enter Dates
		Understanding time serial numbers
		ENTERING TIMES
		FORMATTING DATES AND TIMES
	PROBLEMS WITH DATES
		Excel’s leap year bug
		Pre-1900 dates
		Inconsistent date entries
	USING EXCEL’S DATE AND TIME FUNCTIONS
		Getting the current date and time
		Calculating age
		Calculating the number of days between two dates
		DATEDIF(start_date, end_date, "d").
		Calculating the number of workdays between two dates
	USING NETWORKDAYS.INTL
		Generating a list of business days excluding holidays
		Count Workdays excluding Holidays and Particular Weekends
		Extracting parts of a date
		Calculating the number of years and months between dates
		Converting dates to Julian dates formats
		Returning the last date of a given month
		To find the last date of a given month, follow the steps below:
	USING THE EOMONTH FUNCTION
		Calculating the calendar quarter for a date
		What this formula entails
		Here's what it's all about:
		Calculating the fiscal quarter for a date
		What is the mechanism behind it?
		Returning a fiscal month from a date
		Calculating the date of the Nth weekday of the month
		Calculating the date of the last weekday of the month
		Calculating elapsed time
		Calculating the amount of time that has passed in years
		Months of elapsed time
		Weeks that have passed
		The amount of time that has passed in days.
		In working days, the amount of time that has passed.
		Time elapsed in hours
		Time elapsed in minutes
		In seconds, the time has elapsed.
		Rounding time values
		Converting hours, minutes, or seconds to a decimal.
		Adding hours, minutes, or seconds to a time.
CHAPTER FIVE
USING FORMULAS FOR CONDITIONAL ANALYSIS
	UNDERSTANDING CONDITIONAL ANALYSIS
		Checking if a simple condition is met
		Checking for multiple conditions
		Validating conditional data
		Checking if Condition1 AND Condition2 are met
		Checking if Condition1 OR Condition2 are met
	PERFORMING CONDITIONAL CALCULATIONS
		Using SUMIFS
		Summing all values that meet a certain condition
		Summing greater than zero
		Summing all values that meet two or more conditions
		Based on OR logic:
		Based on AND logic:
		Summing if values fall between a given date range
		Getting a count of values that meet a certain condition
		Getting a count of values that meet two or more conditions
		Finding nonstandard characters
		Getting the average of all numbers that meet a certain condition
		EXAMPLE:
		Getting the average of all numbers that meet two or more conditions.
CHAPTER SIX
USING FORMULAS FOR MATCHING AND LOOKUPS
	Introducing Lookup Formulas
		Leveraging Excel’s Lookup Functions
		Looking up an exact value based on a left lookup column
		Looking up an exact value based on any lookup column
		Looking up values horizontally
		There are things you need to consider when applying this vector Lookup.
	FINDING THE CLOSEST MATCH FROM A LIST OF BANDED VALUES
		Finding the closest match with INDEX and MATCH functions
		Looking up values from multiple tables
		Looking up a value based on a two-way matrix
		Finding a value based on multiple criteria
		Finding the last value in a column
		Finding the last number using LOOKUP.
CHAPTER SEVEN
USING FORMULAS FOR FINANCIAL ANALYSIS
	Performing common business calculations
		Calculating markup
		Steps in doing so:
		Calculating EBIT and EBITDA
		Calculating cost of goods sold
		Calculating return on assets
		Calculating return on equity
		Keywords
		Calculating break-even
		The formula for break-even
		Calculating the average customer lifetime value
		The formula is as follows:
		How do you figure out a company's LTV?
		Calculating employee turnover
		Leveraging Excel’s Financial functions
	CONVERTING INTEREST RATES
		Computing effective rate with FV
		Creating an amortization schedule
	CALCULATING DEPRECIATION
		Calculating accelerated depreciation
		Calculating the net present value
		Calculating the positive and negative cash flows
		CALCULATING AN INTERNAL RATE OF RETURN
		Calculating non-periodic future cash flows
		Things to note down when using these functions
		Performing financial forecasting.
CHAPTER EIGHT
USING FORMULAS FOR STATISTICAL ANALYSIS
	Working with weighted averages
		Procedures in carrying out weighted average.
		Smoothing Data with moving averages
		Where to find this tool:
		Applying exponential smoothing to volatile data
		Using functions to create descriptive statistics
		How do you calculate this?
		Getting the largest or smallest value
		Calculating mean, median, and mode
		Identifying statistical outliers with an interquartile range
		Creating a frequency distribution
		Steps in doing this:
		An alternative to the Frequency function
CHAPTER NINE
USING FORMULAS WITH TABLES AND CONDITIONAL FORMATTING
	Highlighting cells that meet certain criteria
		Follow the steps below:
		Explanation
		Highlighting values that exist in List1 but not List2
		Highlighting values that exist in List1 and List2
		Highlighting based on Dates
		The first process is by using built-in conditional formatting
		Highlighting days between two dates
		Highlighting days between a due date.
		Conclusion.
CHAPTER TEN
UNDERSTANDING AND USING ARRAY FORMULAS
	Understanding Array Formulas
		To enter a multi-cell array formula, follow these steps:
		There are a few characteristics that differentiate multi-cell array formulae:
		A single cell array formula
		1. A formula for a single-cell array:
		2. An Excel formula for a multi-cell array:
		3. Returning a multi-cell array utilizing an Excel array function
		Creating an array constant
	UNDERSTANDING THE DIMENSIONS OF AN ARRAY
		One dimensional horizontal array
		One dimensional vertical array
		Naming array constants
	WORKING WITH ARRAY FORMULAS
		Entering an array formula
		Selecting an array formula
		Editing an array formula
		Expanding or contracting a multi-cell array
	USING MULTICELL ARRAY FORMULAS
		Creating an array constant from values in a range
		Performing operations on an array
		Counting characters in a range
		Summing the three smallest values in a range
		Here's a faster and more practical array formula:
		Counting text in a range
		Eliminating intermediate formulas
CHAPTER ELEVEN
MAKING YOUR FORMULAS ERROR-FREE
	Finding and correcting formula errors
		Mismatched parenthesis
		Cells are filled with hash marks
		Blank cells are not blank
		Extra Space Characters
		Formulas returning an error
		#DIV/0! Errors
		#N/A errors
		#NAME? errors
		#NULL! errors
		#REF! errors
		#Value! Errors
		Operator Precedence problems
		=1+A1*A2
		=1+(A1*A2)
		=-32% =0-32%
		Formulas are not calculated
		“Phantom link” errors
		Using Excel Auditing tools
		Viewing formulas
	TRACING CELL RELATIONSHIPS
		Identifying precedents
		Identifying dependents
		Fixing circular reference errors
		Using the background error-checking feature
		Using Formula Evaluator
		Searching and Replacing
		Spell-checking your worksheets
		Using Autocorrect
BOOK 3:
EXCEL PIVOT TABLES & DASHBOARDS
CHAPTER ONE
INTRODUCTION TO PIVOT TABLES
	What are Pivot Tables?
	What are the main parts of a Pivot Table?
	Importance of Pivot Table
	Conclusion
CHAPTER TWO
BUILDING A BASIC PIVOT TABLE & CHART
	First, choose the cells for the pivot table:
	Summarizing Numbers
		Below are the summarize options that you can use alongside the function.
	How to Drill-Down Pivot Table Data
	When utilizing slicers, use extreme caution!
	Watch out for the source data
	Drill-down PowerPivot Data Model
	Adding Additional Rows (categories) to your Pivot Table
	Charts: How to create a basic Pivot Table Chart
	Conclusion
CHAPTER THREE
DISPLAYING PERCENTAGES
	Percentage of Grand total
	Percentage of Column Total
	Conclusion
CHAPTER FOUR
RANKING RESULTS AND DISPLAYING AVERAGES
	Displaying Averages
	How does this calculation work?
		Below are the procedures in creating pivot tables and measures (showing average):
	Measures: Implicit vs. Explicit
	Ranking Data
	In Source Data, Using RANK.EQ and RANK.AVG
	In a Pivot Table, create a separate Rank Column.
	Conclusion
CHAPTER FIVE
SLICERS (INTERACTIVE ANALYSIS) AND ADVANCED FILTERING
	Timeline Slicer
	Adding a Timeline to a pivot table
	Slicer
	Adding a Slicer to a Pivot Table
	Advanced Filtering
	Create a calculated field
	Using one Pivot Table to generate many pivot-tables
	Hiding and unhiding subtotals
	Refresh your Data
	Conclusion
CHAPTER SIX
INTRODUCTION TO DASHBOARDS
	Adding Multiple Pivot Tables to a Worksheet
	Formatting the Dashboard.
	Adding Charts to the Dashboard
	Parameters for Charts
	Notes:
	Conclusion
CHAPTER SEVEN
ADDING SLICERS AND PERFORMANCE SYMBOLS TO YOUR DASHBOARD
	Slicers
	Performance Symbols (up/down arrows and other indicators)
	Conclusion
CHAPTER EIGHT
REFRESHING PIVOT TABLE AND DASHBOARD DATA
	1. Existing Data, the values vary considerably, while the numbers of rows of data remain constant:
	2. The size of the data in Excel changes
	3. Refresh automatically when the Pivot Table is opened.
	Conclusion
CHAPTER NINE
PROTECTING YOUR DASHBOARD
	Hiding your Pivot Table Source Data
	Protecting the Dashboard or any other Worksheet
	Conclusion
CHAPTER TEN
GROUPING PIVOT TABLE DATA
	Grouping Records
	Group the selected data.
	Name a group.
	Ungroup data that have already been grouped.
	Errors Troubleshooting (Grouping)
	Multiple Consolidation Ranges and Grouping
	Count Function
	Count the field
	Conclusion
CHAPTER ELEVEN
CALCULATED FIELDS IN PIVOT TABLES
	COGS = Total Sales multiplied by a percentage
	Adding a basic calculated field
	Changing the display of formula error messages
	Removing or changing calculated fields
	Conclusion
CHAPTER TWELVE
CREATING PIVOT TABLES FROM IMPORTED FILES – USING THE DATA MODEL
	Activate the data model
	Import the data models
	Define the Relationship
	Construct the Pivot Table
	Conclusion
CHAPTER THIRTEEN
TROUBLESHOOTING
	Pivot Table displaying duplicate values
	Formula – LEN
	Formula – TRIM
		A few remarks about the TRIM Function:
	Conclusion
CHAPTER FOURTEEN
TROUBLESHOOTING
	How to resolve common Pivot Table Errors.
	Correcting the Source Reference not Valid Error in a Pivot Table
	Old items in the Drop-Down Menus
	Overlap Errors
	How to fix it
		Solution One
		Solution Two
		Solution Three
		How Do I Figure Out Which Pivot Tables Overlap?
	CONCLUSION
BOOK 4:
EXCEL POWER PIVOT & POWER QUERY
CHAPTER ONE
INTRODUCING POWER PIVOT
	Understanding the Power Pivot Internal Data Model
	Activating the Power Pivot Ribbon
	Linking Excel Tables to Power Pivot
		Preparing the Excel Tables
		Adding your Excel tables to the data model
	selecting more tables.
		Creating relationships between your Power Pivot Tables
		Finding a related Column
		Modifying your active relationship.
		Managing existing relationships
		Using Power Pivot data in reporting
		Power PivotChart-based reports
		Loading data from other data sources
	Loading data from a relational database
		Loading data from SQL Server
		Loading data from flat files
		Loading data from Excel files
		Loading data from text files
		Loading data from the clipboard
	Refreshing and managing external data connections
		Manually refreshing your Power Pivot data
		Setting up automatic refreshing
		Editing your data connection
	CONCLUSION
CHAPTER TWO
WORKING DIRECTLY WITH THE INTERNAL DATA MODEL
	Managing Relationships in the Internet Data Model
		Removing a Table from the Internal Data Model
	CONCLUSION
CHAPTER THREE
ADDING FORMULAS TO POWER PIVOT
	Enhancing Power Pivot Data with Calculated Columns
		Creating your first calculated column
	Formatting your calculated columns
		Giving names to Calculated Columns
		Changing the Type of Data
	Hiding calculated columns from end-users
	UTILIZING DAX TO CREATE CALCULATED COLUMNS
		Identifying DAX functions safe for calculated columns
		DAX Formulas: What You Need to Know
	Building DAX-driven calculated columns
		Month sorting in Power Pivot-driven Pivot Tables
	Nesting functions
		Fields in Nesting Order
		Changing the Order of Nesting
		Understanding calculated measures
		Calculated Field Implicit
		Calculated Field Explicit
	Editing and deleting calculated measures
		Naming a Measure
		Make a Change to an Existing Measure
	Using Cube Functions to Free your data
		Introduction to cube using
		Using the cube function
		Formula Conversion
	Conclusion
CHAPTER FOUR
INTRODUCING POWER QUERY
	Understanding Power Query Basics
		Understanding query steps
		Power Query experiences
		Transformations
		DataFlows
		Power Query M Language
	Viewing the Advanced Query Editor
	Refreshing Power Query Data
	Managing existing queries
	Merging columns using combine
	Defining a query using the Query Wizard
		Getting Data from External Sources
	Importing data from files
		Getting data from Excel Workbooks
		Getting data from CSV and text files
	Importing data from database systems
		Importing data from relational and OLAP databases
		Importing data from the Azure database
		Importing data from ODBC connections to nonstandard databases
		Getting data from other data systems
		Managing data source settings
	Editing data source settings
	Conclusion
CHAPTER FIVE
TRANSFORMING DATA WITH POWER QUERY
	Performing common transformation tasks
	Removing duplicate records
	Filling in blank fields
	Concatenating columns
	Changing case
	Finding and replacing specific text
	Replacing Text Values
	Replace Number, date, time, or logical values
	Trimming and cleaning text
	Extracting characters
	Extracting the left, right, and middle values
	Syntax
	Extracting first and last characters
	Splitting columns using character markers
	Unpivoting columns
	Unpivoting other columns
	Creating custom columns
	Understanding data type conversions
	Understanding the present data type
	Adding conditional logic to custom columns
	Grouping and Aggregating Data
	Selecting groups for Aggregation
	Selecting an Aggregation Function
	Conclusion
CHAPTER SIX
MAKING QUERIES WORK TOGETHER
	Reusing Query Steps
		Understanding the Append Feature
		Creating the needed base queries
		Appending the data
		Outcome
	Understanding the Merge Feature
		Understanding the Power Query joins
		Merging queries
		Outcome
	Conclusion
CHAPTER SEVEN
ENHANCING POWER QUERY PRODUCTIVITY
	Implementing some Power Query Productivity Tips
		Organizing queries in group
		Selecting columns in your queries faster
		Renaming query steps
		Copying queries to save time
		Setting a default load behavior
		Preventing automatic data types changes
		Types of Data
		Settings
		Avoiding Power Query Performance Issues
		Using views instead of tables
		Benefits of Using Views
		Disabling privacy settings to improve performance
		Avoiding data transfer by negligence
	Conclusion
CHAPTER EIGHT
TEN TIPS FOR WORKING WITH POWER QUERY
	Select the appropriate connection.
	Filtering early.
	Performing hard operations last
	Working on a portion of your data for the time being.
	Making use of the appropriate data types
	Moving or deleting columns to reorganize data.
	Creating a column based on examples
	Utilize a modular strategy.
	Future-proofing queries
	Making functions that may be reused.
	Conclusion
BOOK 5
EXCEL DATA ANALYSIS
CHAPTER ONE
LEARNING BASIC DATA-ANALYSIS
	What is Data Analysis Anyway?
		Sorting
		Filtering
		Conditional Formatting
		Charting
		Tables
		Pivot Tables
		What-If Analysis
		Solver
		Analysis Toolkits
		Descriptive Statistics
	Cooking raw data
		Cleaning of data
		Exploration of Data Using Pivot Tables
	Dealing with data
		Defining Requirements for Data
		Data Gathering
		Processing of Data
		Cleaning of data
		Analyzing the data
		Communication
	Building data models
	Performing what-if analysis
		One-variable Data Tables
		Two-Variable Data Table
		Analyzing Data with Conditional Formatting
		Instance
	Highlighting cells that meet some criteria
	Showing pesky duplicate values
	Highlighting the top or bottom values in a range
	Analyzing cells values with color scales
	Analyzing cells values with data bars
	Analyzing cells values with icon sets
	Creating a custom conditional-formatting rule
	Editing a conditional-formatting rule
	Removing conditional-formatting rules
	Summarizing Data with Subtotals
		How are subtotals calculated?
		Grouping related data
	Consolidating Data from Multiple Worksheets
		Consolidating by position
		Consolidating by category.
	Conclusion
CHAPTER TWO
WORKING WITH DATA-ANALYSIS TOOLS
	Working with data tables
	Creating a basic data table
	Creating a two-input data table
		Analyzing data with Goal Seek
		Analyzing Data with Scenarios
		Create a Scenario
		Edit a Scenario
		Delete a Scenario
	Optimizing Data with solver
		Understanding solver
	The advantages of solver
		Linear Algebra
		Optimization
		Education
	Loading the Solver add-in
	Adding constraints to Solver
	Conclusion
CHAPTER THREE
INTRODUCING EXCEL TABLES
	What are a Table and its importance?
		Understanding a table’s structure
		Building a Table
	Converting a range to a table
	Analyzing Table Information
	Adding a column subtotal
	Sorting table records
	Filtering table records
	Filtering a table with slicers
	Changing the table’s appearance
	Clearing a filter
	Applying a predefined AutoFilter
	Applying advanced filters
	Conclusion
CHAPTER FOUR
GRABBING DATA FROM EXTERNAL SOURCES
	What is All this about External Data?
	Importing External Data into Excel
		Importing data from an Access Table
	Importing data from a Word table
	Introducing text file importing
	Importing a fixed-width text file
	Importing data from a web page
	Importing an XML file
	Querying External Databases
		Defining a data source
	Conclusion
CHAPTER FIVE
ANALYZING TABLE DATA WITH FUNCTIONS
	The Database Functions: Some General Remarks
	Summing a column’s Values
	Counting a Column’s Values
	Averaging a Column’s Values
	Multiplying a Column’s Values
	Conclusion.
CHAPTER SIX
CREATING AND USING PIVOT TABLES
	Understanding Pivot Tables
	Exploring Pivot Table features
	Importance of Pivot Table
	Building a pivot table from an Excel Range or Table
		Creating a Pivot Table from External data
	Building a Pivot Table from Microsoft Query
		Building a Pivot Table from a New data connection
		Refreshing Pivot Table Data
		1.
		Refreshing Pivot Table Data Manually
		Refreshing Pivot Table data automatically
		Adding multiple fields to a pivot table area
		Pivoting a field to a different area
		Multiple Row Fields
		Multiple Value Fields
		Multiple Report Filter Fields
		Grouping pivot table values
		Grouping Records
		Grouping selected data
		Assigning a name to a group
		Ungrouping data
		Error troubleshooting (grouping)
	Filtering pivot table values
		Applying a report filter
		Filtering row or column items
		Filtering pivot table values
		Filtering a pivot table with a slicer.
		Adding a Slicer to a Pivot Table
	Conclusion
CHAPTER SEVEN
PERFORMING PIVOT TABLE CALCULATIONS
	Messing around with Pivot Table summary calculations
	Changing the Pivot table summary calculations
		Trying out the difference summary calculation
		Applying a percentage summary calculation
		Percentage of Grand Total
		Percentage of Column total
		Adding a running total summary calculation
		Creating an index summary calculation
		Working with Pivot Table Subtotals
		Turning off subtotals for a field
		Displaying multiple subtotals for a field
		Introducing Custom Calculations
		Checking out the custom calculation types
		Inserting a custom calculation field
		Inserting a custom calculation item
		Editing a custom calculation
		Deleting a custom calculation
		Conclusion
CHAPTER EIGHT
BUILDING PIVOT CHARTS
	Introducing the Pivot Chart
		Understanding Pivot Chart pros and cons
		Understanding Pivot Chart Limitations
		Creating a Pivot Chart
		Creating a Pivot Chart from a Pivot Table
	Working with Pivot Chart
		Moving a Pivot Chart to another sheet
		Filtering a Pivot Chart
		Changing the Pivot Chart type
		Adding data labels to your Pivot Chart
		Sorting the Pivot Chart
		Adding Pivot Chart Titles
		Displaying a data table with Pivot Chart.
	Conclusion
CHAPTER NINE
UNDERSTANDING EXCEL DATA MODELS
	Elaboration
	Dealing with Data Models
	Creating a relationship between tables
	Importing related external data tables
	Import Access Data
	Import Web Data
	Import Text Data
	Import Data from Other Sources
	Basing a Pivot Table on multiple related tables
	Managing a Data Model with Power Pivot
	Enabling the Power Pivot Add-in
	Adding a table to the Data Model
	Selecting more tables.
	Creating a relationship between tables with Power Pivot
	Creating a Pivot Table or Pivot Chart from your Data Model
		Activating the data model
		Importing the data models
		Defining the Relationship
		Construct the Pivot Table
CHAPTER TEN
TRACKING TRENDS AND MAKING FORECASTS
	Plotting a Best-Fit Trend Lines
	Calculating Best-Fit Values
	Plotting Forecasted Values
	Extending a Linear Trend
	Calculating Linear Forecasted Values
	Plotting an Exponential Trend Line
	Exponential Trend Values Calculation
	Plotting a Logarithmic Trend Line
	Plotting a Power Trend Line
	Plotting a Polynomial Trend Line
	Creating a Forecast Sheet.
CHAPTER ELEVEN
ANALYZING DATA USING STATISTICS
	Counting Things
		Counting Numbers
		Counting Nonempty cells
		Counting empty cells
		Counting cells that match criteria
		Counting cells that match multiple criteria
		Counting Permutations
		Counting combinations
		Averaging Things
		Calculating an average
	Calculating a conditional average
	Calculating an average based on multiple conditions
		Getting the average of all numbers that meet a certain condition
		Getting the average of all numbers that meet two or more conditions.
		Calculating the median, mode, variance, standard deviation
		Finding the Rank
		In Source Data, Using RANK.EQ and RANK.AVG
		Determining the Nth Largest and Smallest Value
		Creating a Frequency Distribution using Groups
		Finding the correlation
CHAPTER TWELVE
ANALYZING DATA USING DESCRIPTIVE STATISTICS
	Loading the Analysis ToolPak
		Calculating a Moving Average
		Determining Rank and Percentile
		Generating Random Numbers
		Creating a Frequency Distribution
CHAPTER THIRTEEN
ANALYZING DATA USING INFERENTIAL STATISTICS
	Data Sampling
	Using t-Testing Tools
	Determining the Regression
	Correlation Calculation
	Calculating the Covariance
	Using Anova Tools
	Performing an f-test
CHAPTER FOURTEEN
TEN THINGS YOU OUGHT TO KNOW ABOUT STATISTICS
	Descriptive statistics are simple to understand.
	Deviation from the mean Explains the concept of dispersion.
	Standard Deviations Describe Dispersion
	An Observation is an Observation
	A Sample is a Subset of Values
	Inferential Statistics Are Interesting, but They're Also Complicated
	Probability Distributions aren't always difficult to understand.
	Uniform Distribution
	Normal Distribution
	Parameters aren't as complicated as they seem.
	Skewness and Kurtosis Describing a Probability
	Confidence Intervals May Appear Difficult at First, but They Are Beneficial.
CHAPTER FIFTEEN
TEN WAYS TO ANALYZE FINANCIAL DATA
	Calculating Future Value
		Calculating Present Value
		Calculating the positive and negative cash flows
		Calculating non-periodic future cash flows
		Things to note down when using these functions
		Calculating Loan Payments
		Calculating the Principal and Interest on a Loan Payment
		Cumulative Loan Principal and Interest Calculation
		Identifying the Minimum Interest Rate
		Determining the Internal Rate of Return
CHAPTER 16
HOW TO IMPROVE YOUR PIVOT TABLE GAME
	Activating and deactivating the Pivot Table Fields Task Pane
	Change the Pivot Table Fields Task Pane Layout
	Showing the Details Behind the Data in Pivot Tables
	Use the Pivot Table Style
	Making Your Own Pivot Table Style
	Preserve a Pivot Table
	Renaming a PivotTable.
	Disable Grand Totals
	Workbooks with Pivot Tables Can Be Resized.
	Using a Pivot Table Value in a Formula
	Conclusion
INDEX