MICROSOFT ACCESS & EXCEL FOR BEGINNERS & POWER USERS

TABLE OF CONTENT

BOOK ONE

MICROSOFT ACCESS

INTRODUCTION

CHAPTER ONE

INTRODUCING ACCESS 365

What Is Access?

Why Should You Use Access?

CHAPTER TWO

MICROSOFT ACCESS OBJECTS

What Is A Database

Tables

Query

Form

Report

Macro

Module

Microsoft Access Data Types

CHAPTER THREE

CREATING A DATABASE FILE

Creating A Blank Database File

Creating Database From Template

The Navigation Pane

Managing The Navigation Pane

Show Or Hide The Navigation Pane

To Select A Predefined Category

Filter By Group

Sort Objects

Finding Objects In Database

Changing How Objects Are Displayed

How To Hide And Unhide Objects And Groups

To unhide or display hidden objects or groups

Customizing The Navigation Pane

Creating  A Custom Category

Renaming A Custom Category

Deleting A Custom Category

Creating A Custom Group

Renaming A Custom Group

Deleting A Custom Group

Creating A Custom Group From A Database Object

Designing A Database

Deciding What Information You Need

Separating Information Into Different Database Tables

Choosing Fields For Database Tables

Deciding On A Primary Key Field For Each Database Table

Mapping The Relationship Between Tables

CHAPTER FOUR

BUILDING YOUR DATABASE TABLES

Creating A Database Table

Creating a database table by importing database table from another database

Creating A Database Table From A Template

Importing A Table From Another Database

Opening And Viewing Tables

Opening Table In Design View

Opening Table In Datasheet View

Switching Between Views With The View Buttons

To switch between the view buttons

Switching Between Views On The Status bar

Switching Between Views By Right Clicking

Entering And Altering Table Fields

Creating A Field

Creating A Field On Your Own

Using The Ready-Made Fields

The Data Types

Designating The Primary Key Field

Moving, Renaming, And Deleting Fields

Moving A Field

Renaming A Field

Deleting A Field

Understanding Field Properties

Creating A Lookup Data-Entry List

Creating A Drop-Down List On Your Own

Getting List Items From A Database Table

Indexing For Faster Sorts, Searches, And Queries

The Indexed Property Settings

Indexing One Field In A Table

Indexing Bases On More Than One Field.

Deleting An Index

Viewing And Editing Index

CHAPTER FIVE

TIPS AND TRICKS ON ACCESS

Every Table Should Have A Primary Key

Keep Your Access Database Fields As Small As Possible

Don’t Over Index

Choose The Optimal Data Types

Validating Your Access Data

Use Simple And Direct Names In Access

Shortcut Keys And Controls

Frequently Used Shortcuts Keys

Navigating Through The Ribbons Shortcut Keys

The Shortcut Keys For Database Files

The Shortcut Keys For Access Workplace

The Shortcut Keys For Menus

The Shortcut Keys For Dialog Boxes

The Shortcut Keys For Wizard

The Shortcut Keys For Property Sheets

The Shortcut Keys For Text Box

The Shortcut Keys For Combo Or List Box

The Shortcut Keys For Working With Objects

The Shortcut Keys For  Working Design, layout, or Datasheet View

The shortcut Keys For Datasheet View

The Shortcut Keys For Subdatasheets In Datasheet View

The shortcut Keys To Navigate In A Design View

The Shortcut Keys For Editing Using Controls In the Form And Report Design View

The Shortcut Keys To Navigate Between Fields And Records

The Shortcut Keys To Navigate In Forms With More Than One Page

The Shortcut Keys To Navigate Between A Main Form And A Subform

The Shortcut Keys To Navigate In Print Preview And Layout Preview

The Shortcut Keys For The Diagram Pane

The Shortcut Keys For Grid Pane

The Shortcut Keys For The Field List Pane

The Shortcut Keys To Select A Field Or Record

The Shortcut Keys To Select Text In A Field

The Shortcut Keys To Extend A Selection

The Shortcut Keys To Find And Replace Text Or Data

The Shortcut Keys To Move The Insertion Point In A Field

The Shortcut Keys To Copy, Move, Or Delete Text

The Shortcut Keys To Undo Changes

The Shortcut Keys For Entering Data In A Datasheet Or Form View

The Shortcut Keys To Refresh Field With Current Data

The Shortcut Keys To Work Around In Tables And Cells

The Shortcut Keys For Get Help With Access

Miscellaneous Keyboard Shortcuts

CONCLUSION ON ACCESS

BOOK TWO

MICROSOFT EXCEL 365

PREFACE

INTRODUCTION

CHAPTER ONE

OVERVIEW OF MICROSOFT EXCEL

Origin of Excel

Meaning of Excel

Relevance of Excel

What Is Excel 365?

Differences Between Excel 365 And Traditional Excel Such As (2013, 2019 And Others)

Similarities Between Excel 365 And Traditional Excel (Such As 2013, 2019 And Others)

Importance of Excel 365

CHAPTER TWO

START YOUR EXPLOIT WITH EXCEL

Creating and Opening A New Excel Workbook

Getting Familiar with The Excel Interface

Understanding Rows, Columns, And Cell Addresses

Workbooks And Worksheet

Entering Data in The Worksheet Cell

The Basic Knowledge of Entering Data

Typing Your Text

Typing Numeric Value

Typing Dates and Time Values

Taking Advantages of Flash Fill And Autofill Commands By Entering Specific Lists and Serial Data

Applying Formatting to Numbers, Dates, Money, And Times Values

Essential Guide to Data Validation

CHAPTER THREE

IMPROVING YOUR WORKSHEET

Editing Your Worksheet Data

Navigating Around the Worksheet

Giving Your Worksheet A New Appearance

Freezing and Splitting Columns and Rows

Hide and Unhide The Columns and Rows

Comments for Documenting Your Worksheet

Selecting Cells (S) In A Worksheet

Deleting, Copying, And Moving Data

Managing the Worksheets in A Workbook

Restricting Others from Meddling with Your Worksheets

Hiding Your Worksheet

Protecting Your Worksheet

CHAPTER FOUR

COMPUTING DATA WITH FORMULAS AND FUNCTIONS

About Formulas

Referencing the Cells Via Formulas

Referencing Formula Results in Subsequent Excel Formulas

Operators and Precedence of Excel Formulas

Arithmetic Operators

Concatenation Operator

Reference Operator

Comparison operator

The Order of Operator Precedence in Excel Formulas

Changing Excel Order with Parenthesis

Foreknowledge of Entering A Formula

The Fast-Track Method to Observe in Entering A Formula

Reference Cells in The Worksheet by Clicking on The Cells

Inserting A Cell Range

Creating Cell Range Name for Formulas’ Use

Pointing to Cells in A Worksheet for Formula Purpose in A Different Worksheet

Ways of Copying Formulas from One Cell to Other Cell

Discovering and Adjusting Formulas Error

Frequent Message Error for Entering Wrong Formulas

Discover More About the Error and Adjusting It

Tracing Cell References

Making Use of Error Checkers Button

Stepping into A Function

Understand the Use of Argument in Function

Checking Out the Necessary Argument for A Given Function

ENTERING A FUNCTION FOR BUILDING A FORMULA

Glancing Through Generally Used Function

Using COUNT and COUNTIF To Count Data Item in A Cell Range

Joining Text with Value with Concatenate

Using Average for Averaging Point Value

PMT For Estimating Periodic Payment of Loan

Project Time Measuring with NETWORKDAY and TODAY

LEN For Counting Text Character

Compares the Range of Values with LARGE And SMALL

Text Capitalizing with PROPER Function

LEFT, MID, AND RIGHT for Data Extraction

IF For Analytical Identification

CHAPTER FIVE

CONSTRUCT WORKSHEET FOR EASY COMPREHENSION

Spreading Out Worksheet in An Orderly Manner

Numbers and Text Alignment in Rows and Columns

Text Merging and Centering Over Multiple Cells

Delete and Insert Rows and Columns

Deleting rows or columns

Adjusting Rows and Columns Size

Adjusting the Height of The Rows

Adjusting the Column Width

Furnishing A Worksheet with Borders and Colors

Quick Way of Formatting Worksheet with Cell Style

Making Use of Excel Built-In Cell Style

Customizing Your Cell Style

Using Table Style to Format A Cell

Creating Border on The Worksheet Cells

Colorize Your Worksheet

Be Prepared to Print A Worksheet

Setting Up A Worksheet to Fit the Page

Present A Worksheet in An Attractive Manner

Repeat Rows and Columns Heading on Every Page

Removing Row and Column Headings

CHAPTER SIX

TOOLS AND TECHNIQUES FOR DATA ANALYSIS

What Are the Sparklines?

Conditional Format Application for Certain Data That Need Important Attention

Taking Care of The Information List

Sorting List of a Data

Filtering List of Data

Exploiting Goal Seek Command

Analysing Data with Data Table and What If Analysis

Using A One Input Data Table for Analysis

Using A Two Input Data Table for Analysis

Using Pivot Table for Data Analysis

Using Recommended Pivot Table

Creating A New Pivot Table

ADDING FINAL TOUCHES TO THE PIVOT TABLE

CHAPTER SEVEN

EXCEL 365 SHORTCUTS, TIPS AND TRICKS

Useful Shortcuts

Formula Shortcuts

General Excel Shortcuts

Indispensable Tips And Trick For Quick Command

Absolute and Relative Reference

Quick Analysis Tool

Autofit Column Width

XLOOKUP Function

Remove BLANK

CONCLUSION

INDEX