Microsoft Excel & Access For Beginners & Pros. 2021: A Complete Guide to Master Excel and Access 365 for All Users

CONTENTS
BOOK ONE
Microsoft Excel 365 together with Excel 2021
INTRODUCTION
CHAPTER ONE
MICROSOFT OFFICE 365 APPLICATION
UNDERSTANDING OFFICE 365 APPLICATION
EXCEL 365 AND MICROSOFT OFFICE
ACCESSING OFFICE 365 APPLICATION
SIGNIFICANCE OF ONLINE EXCEL (EXCEL 365)
DIFFERENCES BETWEEN EXCEL 365 AND OFFLINE EXCEL (2016, 2019 & 2021)
GETTING STARTED WITH EXCEL 365 & 2021
CREATING AN EXCEL WORKBOOK FROM THE SCRATCH AND PRESET TEMPLATE
UNDERSTANDING EXCEL WINDOW INTERFACE
RECOGNISING ROWS, COLUMNS, AND CELLS
COMPARING WORKBOOK AND WORKSHEET
ENTERING DATA IN A WORKSHEET
METHODS OF ENTERING DATA
ESSENTIAL TECHNIQUES OF ENTERING DATA
ENTERING EXCEL TEXTS
ENTERING EXCEL FIGURES
ENTERING EXCEL DATE AND TIME
ENTERING SPECIFIC LIST AND SERIAL DATA QUICKLY WITH AUTO-FILL COMMAND
QUICKLY COMBINING DATA WITH FLASH FILL COMMAND
QUICKLY EXTRACTING DATA WITH FLASH FILL COMMAND
FORMATTING EXCEL NUMBERS, DATES, AND TIMES
SETTING DATA VALIDATION GUIDE
CHAPTER TWO
WORKING WITH THE WORKSHEET
EDITING WORKSHEET CONTENTS
MOVING HERE AND THERE WITHIN A WORKSHEET
CHANGING APPEARANCE OF THE WORKSHEET
FREEZING ROWS AND COLUMNS
SPLITTING ROWS AND COLUMNS
HIDING ROWS AND COLUMNS
ATTACHING COMMENT TO THE WORKSHEET
VARIOUS WAYS OF SELECTING CELLS IN A SHEET
MOVING, COPYING, AND DELETING CELL CONTENTS
CONTROLLING THE WORKSHEET INSIDE A WORKBOOK
PREVENTING OTHERS FROM INTERFERING WITH YOUR WORKSHEET
CHAPTER THREE
DATA COMPUTATION WITH FORMULAS AND FUNCTIONS
UNDERSTANDING EXCEL FORMULAS
USING CELL REFERENCE IN FORMULAS
POINTING TO FORMULAS RESULT IN FORMULA
OPERATORS OF EXCEL FORMULAS
UNDERSTANDING ORDER OF EXCEL OPERATOR PRECEDENCE
BYPASSING ORDER OF PRECEDENCE
BASICS METHOD OF ENTERING FORMULAS
DIVERSE WAYS OF ENTERING CELL FOR FORMULAS
ENTERING CELL REFERENCE VIA CLICKING
ENTERING A CELL RANGE
USING CELL RANGE NAME RATHER THAN CELL REFERENCES
USING ANOTHER WORKSHEET CELL CONTENTS IN FORMULAS
COPYING EXCEL FORMULAS TO THE OTHER CELL
IDENTIFY AND RECTIFYING ERROR IN EXCEL FORMULAS
RECTIFYING ERROR AS IT APPEARS
RECTIFYING WITH ERROR CHECKER
RECTIFYING ERROR VIA TRACING CELL REFERENCES
DIGGING DEEP INTO FUNCTIONS
FORMULATING ARGUMENT IN FUNCTIONS
USING EXCEL HELP TO ENTER ARGUMENT FOR EACH FUNCTION
BEGIN ENTERING AN EXCEL FORMULA
BRIEF DESCRIPTION OF NEW EXCEL AND OTHER INDISPENSABLE USED FUNCTION
COUNTING DATA ITEM WITH “COUNT” AND “COUNTIF” FUNCTION
CONCAT FOR MERGING TEXT STRINGS
“TEXTJOIN” FUNCTION FOR PERFECT JOINING CELL VALUES, AND RANGE OF CELLS
“SWITCH” FOR SWITCHING CELL VALUES
COMPARISON OF SWITCH AND IFS FUNCTION
IFS FUNCTION COMPARES TO IF AND NESTED IF WHEN TESTING MULTIPLE CONDITION
IF FOR PERFECT CONDITION ANALYSIS
“MAXIFS” AND “MINIFS” FOR  SEARCHING SPECIAL VALUE
MAXIMUM AND MINIMUM FOR OBTAINING HIGHEST AND LOWEST VALUE
AVERAGE, AVERAGEA, AND AVERAGEIF FOR AVERAGING DATA
PMT FOR  CALCULATING THE PERIOD OF THE LOAN PAYMENT
NETWORKDAY AND TODAY FOR DAY ASCERTAINING
UPPER, PROPER AND LOWER FOR CHANGING TEXT CASE
ISOLATING DATA WITH LEFT, MID, AND RIGHT FUNCTIONS
LEN FOR DEFINING CHARACTER LENGTHS
LARGE AND SMALL FOR NUMERICAL VALUE COMPARISON.
RANK.EQ AND RANK.AVERAGE FOR RANKING NUMERICAL VALUE
CHAPTER FOUR
CREATING AN IMPRESSIVE WORKSHEET FOR EFFECTIVE READING
STRUCTURING A WORTHWHILE WORKSHEET
APPROPRIATELY ALIGN TEXTS AND NUMBERS INTO ROWS AND COLUMNS
MERGING AND CENTER-SPECIFIC TEXT ACROSS OTHER CELLS
ADJUSTING THE SIZE OF ROWS AND COLUMNS
INSERTING AND DELETING ROWS AND COLUMNS
BEAUTIFYING A WORKSHEET WITH COLOR AND BORDER
SPEAK NEW LIFE TO WORKSHEET WITH EXCEL CELL STYLE FORMATTING
USING EXCEL PRESET CELL STYLE
CUSTOMIZING ONE’S CELL STYLE
USING TABLE STYLE FORMATTING
ADD BORDER TO THE WORKSHEET CELL
SPLASHING COLOR ON THE WORKSHEET
WHAT TO DO BEFORE PRINTING A WORKSHEET
PRINTING PORTION OF THE WORKSHEET
PRINTING WORKSHEET IN LANDSCAPE FORMAT
RELATING ACCURATELY WITH THE PAGE BREAK
OFFER A COMPELLING AND CHARMING WORKSHEET
ROWS AND COLUMNS HEADINGS TO REPEAT ON EVERY PAGE
CHAPTER FIVE
POWERFUL TOOLS FOR ANALYZING DATA
UNDERSTANDING SPARKLINE FEATURE
CONDITION FORMATTING FOR SPECIFIC DATA THAT NEED UNIQUE ATTENTION
HANDLING THE DATA LISTS
SORTING A DATA LIST
FILTERING A DATA LIST
GOAL SEEK COMMAND ESTIMATION ANALYSIS
DATA TABLE TECHNIQUE ANALYSIS
ONE VARIABLE DATA TABLE ANALYSIS
TWO VARIABLE DATA TABLE ANALYSIS
PIVOT TABLE DATA ANALYSIS
INSERTING RECOMMENDED PIVOT TABLE
CREATING A NEW PIVOT TABLE FROM THE BEGINNING
FORMATTING PIVOT TABLE AND IMPROVING ITS LAYOUT
CHAPTER SIX
ESSENTIAL EXCEL 365 SHORTCUTS CODES
NAVIGATING WITHIN A WORKSHEET SHORTCUTS
NAVIGATING ACROSS A WORKSHEET
ROWS AND COLUMNS SHORTCUTS
OPEN CREATE DIALOG BOX SHORTCUTS
FUNCTIONS
RIBBON SHORTCUTS
DATA SELECTION SHORTCUTS
FORMATTING SHORTCUTS
BORDERS SHORTCUTS
CALCULATION SHORTCUTS
CLEAR SHORTCUTS
DATA EDITING SHORTCUTS
FILE SHORTCUTS
CHAPTER SEVEN
AMAZING EXCEL 365 TIPS AND TRICKS
APPROPRIATE USE OF ABSOLUTE AND RELATIVE REFERENCE
USING PASTE SPECIAL COMMANDS
PERFECT TRANSPOSITION
TEXT TO COLUMN COMMANDS
UTILIZE QUICK ANALYSIS TOOL (QAT)
CONCLUSION
BOOK TWO
Microsoft Access 365 together with Access 2021
INTRODUCTION
CHAPTER ONE
GETTING STARTED WITH MICROSOFT 365
THE TERM “MICROSOFT 365”
BENEFITS OF USING MICROSOFT 365
RELATIONSHIP BETWEEN ACCESS 365, ,MICROSOFT 365, AND TRADITIONAL ACCESS
SIGN IN TO MICROSOFT WEBSITE TO OPEN ACCESS 365
LAUNCHING INTO MICROSOFT ACCESS APPLICATION 2021
CHAPTER TWO
GETTING STARTED WITH ACCESS 365
BASIC ELEMENT OF STARTING WITH ACCESS
WHY DO YOU HAVE TO USE ACCESS?
WHAT IS AN ACCESS DATABASE?
A MUST KNOW (10) TERMINOLOGY FOR MASTERING ACCESS DATABASE
COMPOSITION OF DATABASE OBJECT
USING TABLES TO STORE DATABASE DATA
GENERATING FORMS TO ENTER AND MAINTAIN DATABASE DATA
SELECTING QUERIES TO EXTRACT DATABASE DATA
USING REPORTS FOR DATA PRESENTATION AND INSPECTION
MACRO
MODULE
CREATING A DATABASE FILE
CREATING A BLANK DATABASE FILE
CREATING DATABASE FILE FROM THE PREFORMATTED TEMPLATE
WORKING WITH THE NAVIGATION PANE
OPEN/CLOSE AND RESIZING THE NAVIGATION PANE
SELECTING A PREDEFINED CATEGORY
FINDING AN OBJECT WITH THE PANE
SELECTING DATABASE OBJECT
SORT OBJECTS
DESIGNING A DATABASE
ASCERTAINING KIND OF DATABASE INFORMATION
DISTRIBUTING INFORMATION INTO THE DIFFERENT DATABASE TABLES
ASSIGNING FIELDS TO THE DATABASE TABLES
SELECTING A PRIMARY KEY FIELD FOR EACH DATABASE TABLE
ESTABLISHING RELATIONSHIPS AMONG THE DATABASE TABLES
CHAPTER THREE
CONSTRUCTING DATABASE TABLES
CREATING A DATABASE TABLE
CREATING A DATABASE TABLE FROM THE SCRATCH
CREATE A DATABASE TABLE WITH THE IN-BUILT TEMPLATE
IMPORTING A DATABASE TABLE FROM ANOTHER DATABASE TABLE
TWO WAYS OF OPENING DATABASE TABLE
DETERMINING DATA TYPE
DATA TYPE FOR ENTERING FIELDS
ENTERING AND MODIFYING FIELDS OF A DATABASE TABLE
ENTERING A FIELD INTO A BLANK DATABASE FILE
ADJUSTING THE FIELDS FOR THE TABLE CREATED WITH TEMPLATE
DO YOU KNOW WHAT THE PRIMARY KEY FIELD IS?
IMPORTANCE OF A PRIMARY KEY
LAW OF A PRIMARY KEY
CREATING A PRIMARY KEY
RENAMING AND DELETING A  FIELDS
MOVING A FIELD
FIELD PROPERTIES A GUARANTEE FOR CORRECT DATA ENTRIES
EXAMINING EACH FIELD PROPERTIES SETTINGS
ACTIVATING LOOKUP WIZARD FOR DATA ENTRY LIST
INDEXING FOR FASTER QUERIES
WHEN TO INDEX AND WHICH FIELD IS TO BE INDEXED
CREATING AN INDEX
CREATING MULTIPLE-FIELD INDEX
BUILDING DATABASE TABLES RELATIONSHIP
CATEGORIES OF RELATIONSHIPS
MANAGING TABLES WITHIN THE RELATIONSHIP WINDOW
ESTABLISHING RELATIONSHIP BETWEEN TABLES
MODIFYING TABLE RELATIONSHIP
CHAPTER FOUR
ENTERING DATA INTO THE DATABASE TABLE
DATA ENTERING APPROACHES
DATA ENTERING BY SWITCHING TO DATASHEET VIEW
TIPS AND TRICKS EXPOSURE FOR ENTERING DATA IN A DATASHEET
AMENDING THE LOOK OF THE DATASHEET
ENTERING DATA WITH THE HELP OF A FORM
CHAPTER FIVE
FINDING, SORTING, FILTERING, AND QUERYING YOUR DATA
FINDING MISSING DATA WITH THE FIND COMMAND
SORTING RECORDS ALPHABETICALLY AND NUMERICALLY
SORTING A DATABASE FIELD
FILTERING FOR SPECIFIC INFORMATION
METHODS OF FILTERING A DATABASE TABLE
IMPORTANT NOTE FOR USING ALL FILTER METHODS
FILTER BY FIELD CONTENT
FILTER BY SELECTION
FILTER BY FORM
ADVANCED FILTER/SORT
GETTING STARTED WITH QUERY
CREATING A NEW QUERY
OPENING QUERIES AND SWITCHING VIEW
WORKING WITH THE QUERY DESIGN WINDOW
ADDING THE NEEDED TABLE TO THE QUERY DESIGN WINDOW
SELECTING THE FIELDS NEEDED IN QUERY
ARRANGING QUERY RESULTS WITH SORT
SELECTING THE FIELDS THAT WILL BE DISPLAYED IN THE QUERY RESULT
UNDERSTANDING QUERY CRITERIA
FORMAT FOR NUMERIC CRITERIA
FORMAT FOR TEXT CRITERIA
FORMAT FOR DATE CRITERIA
SAVING YOUR QUERY
RUNNING A QUERY
OTHER FORMS OF QUERIES
SELECT QUERY
CALCULATION QUERY
SUMMARY QUERY
CHAPTER SIX
PRESENTING DATABASE RESULT IN FORM OF REPORT
CREATING A PROFESSIONAL REPORT
OPENING AND INSPECTING A REPORT
REFINING THE APPEARANCE OF YOUR REPORT
CHAPTER SEVEN
APPROPRIATE SHORTCUTS, TIPS, AND TRICKS
DO AWAY WITH DATA MISMATCH
WRONG CRITERIA BRING WRONG OR NO QUERY RESULT
THERE SHOULD BE A LINK BETWEEN THE TABLES IN RELATIONSHIP
EACH TABLE SHOULD HAVE A PRIMARY KEY AND IT SHOULD BE A NUMERIC DATA TYPE
VALIDATING ACCESS DATA TYPE
SHORTCUTS AND CONTROL KEYS
SHORTCUTS FOR ENTERING DATA IN DATASHEET VIEW
GENERAL SHORTCUTS
GRID PANE SHORTCUTS
CONCLUSION
INDEX