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