EXCEL 2021 FOR BEGINNERS THE COMPLETE DUMMY TO EXPERT PRACTICAL GUIDE WITH EXAMPLES THAT TEACHES EVERYTHING YOU NEED TO KNOW… (James Jordan) (Z-Library)
Other
EXCEL 2021 MADE EASY JUST FOR YOU! Microsoft has added over a hundred new features and enhancements to Excel over the past years! The tools outlined in this guide book are among those that offer some of the greatest opportunities to all levels of Excel users to improve their efficiency and proficiency. Therefore, as you gain access to these tools – and others sure to follow – be sure to consider how you and your team members can and should take advantage of them to boost productivity. This book is specially made for business men, corporate bodies, organization, individuals, etc. who are looking for a means of learning Excel from basics to advanced (formulas, functions, VBA, Macros,etc.) level.
330
Views
0
Downloads
0.00
Total Donations
Registered users can read the full content for free
Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.
Page
1
(This page has no text content)
Page
2
EXCEL 2021 FOR BEGINNERS THE COMPLETE DUMMY TO EXPERT PRACTICAL GUIDE WITH EXAMPLES THAT TEACHES EVERYTHING YOU NEED TO KNOW ABOUT MICROSOFT EXCEL 2021 (FORMULAS, FUNCTIONS, VBA & MACROS INCLUSIVE) JAMES JORDAN
Page
3
ALL RIGHT RESERVED This manuscript may not be reproduced in part or in full or stored in a retrieval system or transmitted in any form or in any means; electronic, mechanical, photocopying, recording or otherwise except for brief quotation in critical articles or review-without the prior written consent of the copyright owner and the publisher. This book is sold subject to the condition that it should not by way of trade or otherwise be lent, hired out or otherwise circulated without the copyright owner consent, in any form of binding or cover that in which it is published and this condition being imposed on the subsequent purchaser. ISBN: 9798743142422 Copyright © 2021 JAMES JORDAN
Page
4
CONTENTS PREFACE INTRODUCTION CHAPTER ONE INTRODUCTION TO MICROSOFT EXCEL 2021 WHAT IS EXCEL? USES OF EXCEL WHAT’ S NEW IN EXCEL 2021? MICROSOFT EXCEL TERMINOLOGIES EXCEL TOOLS EXCEL FILE TYPES WHERE CAN I GET MICROSOFT EXCEL? CHAPTER TWO THE BASIC OPERATIONS ON EXCEL 2021 LAUNCHING AND QUITTING EXCEL 2021 FROM THE WINDOW STARTING YOUR EXCEL FROM WINDOW 10 QUITTING EXCEL EXCEL’S START SCREEN EXCEL’S RIBBON USER INTERFACE UNDERSTANDING THE WORKBOOK CREATING A NEW WORKSHEET RENAMING A WORKSHEET INSERTING A NEW WORKSHEET DELETING A WORKSHEET COPYING OR MOVING A WORKSHEET CHANGING THE WORKSHEET COLOUR SAVING YOUR DOCUMENT HOW TO RECOVER YOUR DOCUMENT USING AUTOSAVE EXPORTING WORKBOOKS HOW TO SHARE EXCEL WORKBOOK UNDERSTANDING THE CELL WHAT’S A CELL? INSERTING CONTENTS IN A CELL HOW TO DELETE A CELL COPYING AND PASTING CELL CONTENT DRAGGING AND DROPPING THE CELL USING THE FILL HANDLE IN A CELL FORMATTING THE CELL CHANGING THE FONT CHANGING THE FONT SIZE CHANGING THE FONT COLOR USING THE BOLD, ITALICS, AND UNDERLINED COMMANDS ADDING BACKGROUND COLOR USING THE FILL COLOR ADDING BORDERS TO CELLS CHANGING THE TEXT ALIGNMENT OF YOUR BORDER MODIFYING THE COLUMNS, ROWS, AND CELL IN A WORKSHEET
Page
5
MODIFYING THE COLUMN WIDTH MODIFYING THE ROW HEIGHT INSERTING NEW ROW INSERTING NEW COLUMN DELETING OF ROWS AND COLUMNS HOW TO HIDE AND UNHIDE ROWS OR COLUMNS WRAPPING TEXT AND MERGING CELLS CUSTOMIZING THE DEFAULT WORKBOOK HOW TO CHANGE THE WORKSHEET THEME HOW TO CHANGE THE WORKSHEET THEME COLOUR HOW TO CHANGE THE WORKSHEET THEME EFFECTS ZOOMING IN OR OUT OF THE SPREADSHEET ADDING MORE WORKSHEETS TO THE WORKBOOK CHAPTER THREE EXCEL TABLES AND CHARTS WHAT IS AN EXCEL TABLE? CREATING A TABLE SORTING A TABLE FILTERING A TABLE CREATING PIVOT TABLE AND CHARTS IN EXCEL THE PIVOT TABLE THE PIVOT CHART USING SLICER ON TABLES OR PIVOT TABLES WORKING WITH CHARTS TYPES OF EXCEL CHART HOW TO INSERT A CHART IN EXCEL HOW TO ADD TITLE TO A CHART HOW TO CHANGE CHART TYPE IN EXCEL HOW TO CHANGE CHART STYLE IN EXCEL HOW TO CHANGE CHART LAYOUT IN EXCEL HOW TO SWITCH ROWS AND COLUMNS OF DATA IN A CHART HOW TO MOVE A CHART HOW TO RESIZE A CHART ON YOUR WORKSHEET CHAPTER FOUR CELL REFERENCING IN EXCEL MEANING OF CELLS REFERENCING TYPES OF CELL REFERENCING RELATIVE REFERENCES ABSOLUTE AND MULTIPLE CELL REFERENCE HOW TO CREATE AND COPY A FORMULA USING THE ABSOLUTE REFERENCE REFERENCES TO OTHER WORKSHEET REFERENCES TO WORKSHEETS IN OTHER WORKBOOK CHAPTER FIVE INTRODUCTION TO EXCEL FORMULAS & FUNCTIONS WHAT’S A FORMULA? HOW TO INSERT FORMULAS IN EXCEL 2021 WHAT’S A FUNCTION?
Page
6
HOW TO INSERT A FUNCTION INTRODUCING THE FORMULAR TAB THE FORMULA BAR WHERE IS THE FORMULA BAR LOCATED IN MICROSOFT EXCEL? EXPANDING THE FORMULA BAR CONTRACTING THE FORMULA BAR ENTERING AND EDITING DATA IN THE FORMULA BAR THE INSERT FUNCTION DIALOGUE BOX THE FUNCTION ARGUMENT DIALOG BOX CONTROLLING THE DISPLAY OF THE FORMULA BAR WHAT TYPE OF DATA DOES THE EXCEL FORMULA ACCEPT? METHODS OF ENTERING AND EDITING OF FORMULAS METHODS OF ENTERING OR INSERTING FORMULAS EDITING A FORMULA USING THE FORMULA OPERATORS ORDER OF FORMULA OPERATORS’ PRECEDENCE THE NESTED PARENTHESES WORKING WITH FUNCTIONS WHY SHOULD YOU USE FUNCTIONS? THE FUNCTION ARGUMENTS USING FUNCTIONS WITH NO ARGUMENTS USING FUNCTION WITH ONE OR MORE ARGUMENTS USING FUNCTIONS WITH BOTH REQUIRED AND OPTIONAL ARGUMENT LOCATING A FUNCTION’S ARGUMENTS ERRORS IN FORMULAS AND FUNCTIONS DIFFERENCES BETWEEN AN EXCEL FORMULA AND FUNCTION USING CALCULATION OPERATORS IN EXCEL FORMULAS MATHEMATICAL ORDER OF OPERATION IN EXCEL ARITHMETIC IN EXCEL CHAPTER SIX MUST KNOW EXCEL FORMULAS & FUNCTIONS MATH FUNCTIONS THE SUM FUNCTION USING THE SUM FUNCTION THE SUMIF FUNCTION USING THE SUMIF FUNCTION THE SUMIFS FUNCTION USING THE SUMIFS FUNCTION THE MOD FUNCTION USING THE MOD FUNCTION RANDBETWEEN FUNCTION USING THE RANDBETWEEN FUNCTION THE ROUND FUNCTION USING THE ROUND FUNCTION THE ROUNDUP FUNCTION USING THE ROUNDUP FUNCTION THE ROUNDDOWN FUNCTION USING THE ROUNDDOWN FUNCTION THE SORT FUNCTION
Page
7
USING THE SORT FUNCTION CHAPTER SEVEN STATISTICAL FUNCTIONS THE COUNT FUNCTION USING THE COUNT FUNCTION THE COUNTIF FUNCTION USING THE COUNTIF FUNCTION THE COUNTIFS FUNCTION USING THE COUNTIFS FUNCTION THE COUNTA FUNCTION USING THE COUNTA FUNCTION THE COUNTBLANK FUNCTION USING THE COUNTABLANK FUNCTION The AVERAGE Function USING THE AVERAGE FUNCTION THE AVERAGEIF FUNCTION USING THE AVERAGEIF FUNCTION THE AVERAGEIFS FUNCTION USING THE AVERGAEIFS FUNCTION THE MIN FUNCTION USING THE MIN FUNCTION THE MAX FUNCTION USING THE MAX FUNCTION THE MEDIAN FUNCTION USING THE MEDIAN FUNCTION CHAPTER EIGHT THE FINANCIAL FUNCTION THE PV FUNCTION USING THE PV FUNCTION THE FV FUNCTION USING THE FV FUNCTION THE NPV FUNCTION USING THE NPV FUNCTION THE PMT FUNCTION USING THE PMT FUNCTION THE SLN FUNCTION USING THE SLN FUNCTION The SYD Function USING THE SYD FUNCTION THE DB FUNCTION USING THE DB FUNCTION THE DDB FUNCTION USING THE DDB FUNCTION THE SEARCH FUNCTION THE FIND FUNCTION CHAPTER NINE LOGICAL FUNCTIONS
Page
8
THE IF FUNCTION USING THE IF FUNCTION THE NESTED IF FUNCTION THE IFS FUNCTION USING THE IFS FUNCTION The IFFEROR Function USING THE IFERROR FORMULAS THE AND FUNCTION USING THE AND FUNCTION THE OR FUNCTION USING THE OR FUNCTION CHAPTER TEN LOOKUP AND REFERENCE FUNCTIONS THE VLOOKUP FUNCTION USING THE VLOOKUP FUNCTION THE HLOOKUP FUNCTION USING THE HLOOKUP FUNCTION THE CHOOSE FUNCTION USING THE CHOOSE FUNCTION THE MATCH FUNCTION USING THE MATCH FUNCTION THE TRANSPOSE FUNCTION USING THE TRANSPOSE FUNCTION THE FORMULATEXTS FUNCTION USING THE FORMULATEXT FUNCTION THE COLUMN FUNCTION USING THE COLUMN FUNCTION THE ROW FUNCTION USING THE ROW FUNCTION THE INDEX FUNCTION THE ARRAY FORMAT OF THE INDEX FUNCTION USING THE ARRAY FORMAT OF THE INDEX FUNCTION THE REFERENCE FORMAT OF THE INDEX FUNCTION USING THE REFERENCE FORMAT OF THE INDEX FUNCTION CHAPTER ELEVEN TEXT FUNCTIONS THE FIND FUNCTION USING THE FIND FUNCTION CONCATENATE FUNCTION USING THE CONCATENATE FUNCTION IN EXCEL ADDING SPACE, COMMA, QUOTATION MARK, HYPHEN USING THE CONCATENATE FUNCTION THE TEXTJOIN FUNCTION USING THE TEXTJOIN FUNCTION TRIM FUNCTION USING THE TRIM FUNCTION THE UPPER FUNCTION USING THE UPPER FUNCTION
Page
9
THE LOWER FUNCTION USING THE LOWER FUNCTION THE PROPER FUNCTION USING THE PROPER FUNCTION LEN FUNCTION USING THE LEN FUNCTION THE LENB FUNCTION USING THE LENB FUNCTION MID Function USING THE MID FUNCTION THE MIDB FUNCTION USING THE MIDB FUNCTION LEFT FUNCTION USING THE LEFT FUNCTION THE LEFTB FUNCTION USING THE LEFTB FUNCTION THE RIGHT FUNCTION USING THE RIGHT FUNCTION THE RIGHTB FUNCTION USING THE RIGHTB FUNCTION CHAPTER TWELVE DATE AND TIME FUNCTION THE DATE FORMAT DATA PARAMETER CUSTOMIZING A DATE THE DAY FUNCTION USING THE DAY FUNCTION THE MONTH FUNCTION USING THE MONTH FUNCTION THE YEAR FUNCTION USING THE YEAR FUNCTION THE DATE FUNCTION USING THE DATE FUNCTION THE DATEDIF FUNCTION USING THE DATEDIF FUNCTION THE DAYS FUNCTION USING THE DAYS FUNCTION THE EDATE FUNCTION USING THE EDATE FUNCTION THE DATEVALUE FUNCTION USING THE DATEVALUE FUNCTION THE NETWORKDAYS FUNCTION USING THE WORKDAYS FUNCTION THE NOW FUNCTION USING THE NOW FUNCTION THE TODAY FUNCTION USING THE TODAY FUNCTION THE TIME FUNCTION USING THE TIME FUNCTION
Page
10
CHAPTER THIRTEEN THE EXCEL POWER QUERY WHAT IS POWER QUERY? WHERE IS THE POWER QUERY LOCATED? What Can Power Query Do? SOURCES OF POWER QUERY DATA IMPORTING DATA SOURCE USING POWER QUERY IMPORTING A SINGLE DATA SOURCE FROM THE WORKBOOK IMPORTING DATA FROM MULTIPLE DATA SOURCES IN A WORKBOOK IMPORTING DATA FROM A CSV FILE IMPORTING DATA FROM A TEXT FILE POWER QUERY EDITOR CHAPTER FOURTEEN EXCEL SHORTCUTS, TIPS & TRICKS EXCEL SHORTCUTS CHAPTER FIFTEEN EXCEL TIPS AND TRICKS HOW TO USE IDEAS HOW TO REMOVE BLANKS FROM A WORKSHEET HOW TO REMOVE DUPLICATE DATA FROM EXCEL WORKBOOK TRANSPOSING ON YOUR WORKSHEET HOW TO ADD TEXT TO COLUMNS HOW TO INSERT SCREENSHOT TO YOUR EXCEL WORKBOOK HOW TO INSERT MULTIPLES ROWS HOW TO CREATE PEOPLE GRAPH HOW TO HIGHLIGHT TEXT AND NUMBERS HOW TO HIGHLIGHT CELLS THAT HAVE FORMULAS HOW TO GET DATA FROM THE INTERNET RENAMING A SHEET WITH A DOUBLE CLICK CHANGING THE CASE OF A TEXT FORMING A TEXT WITH & HOW TO MAKE EXCEL SHOW LEADING ZERO EXTENDING FORMULA DOWN CHANGING HOW ENTER WORKS QUICK SELECT FORMULAS DISABLING THE EXCEL START SCREEN ACTIVATING CURRENT DATE AND TIME CUSTOMIZING THE STATUS BAR DELETING ERRORS CELLS STRIKING THROUGH TEXTS IN EXCEL HOW TO CLEAR FORMATTING SHARING DATA ONLINE ON EXCEL EXCEL 2021 BOOK 2 VBA AND MACROS INTRODUCTION
Page
11
CHAPTER SIXTEEN INTRODUCTION TO EXCEL MACROS WHAT ARE MACROS IN EXCEL? WHY USE EXCEL MACROS? UNDERSTANDING WHICH FILE TYPES ALLOW MACROS THE DEVELOPER TAB HOW TO ENABLE THE DEVELOPER TAB OPTIONS IN THE DEVELOPER TAB RECORDING A MACRO TESTING THE RECORDED MACRO EXAMINING THE MACRO SAVING WORKBOOKS THAT CONTAIN MACROS MACRO SECURITY HOW TO ENABLE MACROS FOR INDIVIDUAL WORKBOOKS? ENABLE THE MACROS VIA SECURITY WARNING BAR OR MESSAGE BAR ENABLE THE MACRO IN THE BACKSTAGE VIEW TRUSTED DOCUMENTS IN EXEL THE TRUST CENTER THE MACRO SETTINGS SETTING UP TRUSTED LOCATION UNDERSTANDING TRUSTED PUBLISHER WHAT IS A TRUSTED PUBLISHER? ADDING TRUSTED PUBLISHER ADDING A TRUSTED PUBLISHER WHEN SECURITY WARNING APPEARS ADDING A TRUSTED PUBLISHER VIA TRUST CENTER REMOVING A TRUSTED PUBLISHER CHAPTER SEVENTEEN HOW VBA WORKS WITH EXCEL WORKING WITH THE VISUAL BASIC EDITOR OPENING THE VB EDITORS USING THE KEYBOARD SHORTCUT USING THE DEVELOPER TAB USING THE WORKSHEET TAB UNDERSTANDING THE VBE COMPONENTS WORKING WITH THE PROJECT WINDOW ADDING VBA CODE IN MODULE ADDING A MODULE IN THE VB EDITOR REMOVING A VBA MODULE EXPORTING AND IMPORTING OBJECTS WORKING WITH A CODE WINDOW MINIMIZING AND MAXIMIZING CODE WINDOWS GETTING VBA CODE INTO A MODULE ENTERING THE CODE DIRECTLY
Page
12
USING THE MACRO RECORDER COPYING THE VBA CODE CUSTOMIZING THE VB EDITOR EDITOR TAB EDITOR FORMAT TAB THE GENERAL TAB DOCKING TAB CHAPTER EIGHTEEN INTRODUCING THE EXCEL OBJECT MODEL WHAT IS EXCEL OBJECT MODEL? THE EXCEL OBJECT MODEL HIERARCHY REFERENCING AND ACCESSING OBJECTS IN EXCEL NAVIGATING THROUGH THE HIERARCHY SIMPLIFYING OBJECT REFERENCES DIVING INTO OBJECT PROPERTIES AND METHODS OBJECT PROPERTIES OBJECT METHODS OBJECT EVENTS USING THE VBA’S HELP SYSTEM USING THE OBJECT BROWSER USING THE AUTO LIST MEMBER CHAPTER NINETEEN UNDERSTANDING EXCEL VBA PROCEDURES WHAT IS A PROCEDURE? TYPES OF PROCEDURE THE SUB PROCEDURES THE FUNCTION PROCEDURES THE DIFFERENCE BETWEEN THE SUB AND THE FUNCTION PROCEDURES HOW TO WRITE A SUB PROCEDURE IN EXCEL HOW TO WRITE A FUNCTION PROCEDURE IN EXCEL NAMING THE SUBS AND FUNCTION PROCEDURES EXECUTING THE SUB PROCEDURES EXECUTING THE SUB PROCEDURE DIRECTLY EXECUTING THE PROCEDURE FROM THE MACRO DIALOG BOX EXECUTING THE SUB PROCEDURE FROM A BUTTON OR SHAPE EXECUTING FUNCTION PROCEDURES CALLING THE FUNCTION FROM A SUB PROCEDURE CALLING A FUNCTION FROM A WORKSHEET FORMULA CHAPTER TWENTY USING THE EXCEL MACRO RECORDER WHAT IS AN EXCEL MACRO RECORDER? BASICS OF USING THE MACRO RECORDER LIMITATION TO THE USE OF THE MACRO RECORDER RECORDING IN THE ABSOLUTE OR RELATIVE MODE RECORDING IN THE ABSOLUTE MODE
Page
13
RECORDING IN RELATIVE MODE RECORDING OPTIONS MACRO NAME SHORTCUT KEY STORE MACRO IN CHAPTER TWENTY-ONE ESSENTIAL VBA LANGUAGE ELEMENTS USING COMMENTS IN THE VBA CODE USING VBA VARIABLES, CONSTANTS, AND DATA TYPES WHAT ARE VBA VARAIBLES? WHAT ARE THE DATA TYPE OF VARIABLES? DECLARING VARIABLES USING THE OPTION EXPLICIT WHY DO YOU NEED TO DECLARE YOUR VARIABLES CONSTANTS IN VBA USING ASSIGNMENTS STATEMENTS THE EQUAL SIGN SMOOTH OPERATORS WORKING WITH ARRAYS DECLARING ARRAYS ONE DIMENSIONAL ARRAY MULTIDIMENSIONAL ARRAYS DYNAMIC ARRAYS CHAPTER TWENTY-TWO WORKING WITH RANGE OBJECT REFERRING TO EXCEL RANGE OBJECT THE CELLS PROPERTY THE OFFSET PROPERTY THE RANGE OBJECT PROPERTIES THE VALUE PROPERTY THE TEXT PROPERTY THE COUNT PROPERTY THE COLUMN AND ROW PROPERTIES THE ADDRESS PROPERTY THE HASFORMULA PROPERTY THE FONT PROPERTY THE INTERIOR PROPERTY THE FORMULA PROPERTY THE NUMBER FORMAT PROPERTY THE RANGE OBJECT METHODS THE SELECT METHOD THE COPY AND PASTE METHODS THE CLEAR METHOD THE DELETE METHOD CHAPTER TWENTY-THREE
Page
14
THE VBA AND WORKSHEET FUNCTIONS WHAT IS A FUNCTION? USING THE BUILT-IN FUNCTIONS IN VBA DISPLAYING THE SYSTEM DATE OR TIME FINDING A STRING LENGTH IDENTIFYING THE TYPE OF A SELECTED OBJECT THE VBA FUNCTION THAT DOES MORE THAN JUST RETURN A VALUE COMMONLY USED BUILT-IN FUNCTIONS IN VBA VBA MESSAGE FUNCTIONS VBA TEXT FUNCTIONS VBA INFORMATION FUNCTIONS VBA ERROR HANDLING FUNCTIONS VBA PROGRAM FLOW FUNCTIONS VBA CONVERSION FUNCTION VBA DATE AND TIME FUNCTION VBA MATH AND TRIG FUNCTION VBA FINANCIAL FUNCTIONS VBA ARRAY FUNCTIONS VBA FILE MANAGEMENT FUNCTIONS USING THE WORKSHEET FUNCTION IN VBA THE WORKSHEET FUNCTION EXAMPLES FINDING THE MAXIMUM VALUE IN A RANGE FINDING THE SUM VALUE IN A RANGE FINDING THE PRODUCT VALUE IN A RANGE INPUTTING THE WORKSHEET FUNCTIONS IN THE VBA MODULES USING CUSTOM FUNCTIONS CHAPTER TWENTY-FOUR THE USER DEFINED FUNCTION (UDF) IN EXCEL VBA WHY SHOULD USE THE USER DEFINED FUNCTION LIMITATIONS OF USER DEFINED FUNCTIONS WRITING FUNCTIONS ARGUMENTS IN A USER DEFINED FUNCTION IN VBA A FUNCTION WITH NO ARGUMENT A FUNCTION WITH ONE ARGUMENT A FUNCTION WITH MULIPlE ARGUMENTS CHAPTER TWENTY-FIVE ERROR HANDLING TECHNIQUES TYPES OF ERROR THE ON ERROR STATEMENT IN VBA ON ERROR GOTO 0 ON ERROR RESUME NEXT ON ERROR GOTO ( LABEL) ON ERROR GOTO -1
Page
15
THE ERR OBJECT ERR OBJECTS METHODS CHAPTER TWENTY-SIX SIMPLE DIALOG BOXES THE MSGBOX FUNCTION THE ANATOMY OF A MsgBox IN EXCEL SYNTAX OF THE VBA MSGBOX FUNCTION THE EXCEL VBA MSGBOX BUTTONS CONSTANTS MsgBox BUTTON (vbOKOnly) MsgBox BUTTONS (OK & Cancel) MsgBox BUTTONS (Abort, Retry, and Ignore) MsgBox BUTTONS ( Yes and No ) MsgBox BUTTONS ( Yes, No, and Cancel ) MsgBox BUTTONS (Retry and Cancel) MsgBox BUTTONS (Help) EXCEL VBA MSGBOX ICON CONSTANTS MsgBox ICONS (Critical) MsgBox ICONS (Question) MsgBox ICONS (Information) THE INPUTBOX FUNCTION THE InputBox SYNTAX AN InputBox EXAMPLE THE GETOPENFILENAME METHOD THE SYNTAX OF GetOpenFilename METHOD A GetOpenFilename EXAMPLE THE GETSAVEASFILENAME METHOD THE SYNTAX OF GetOpenFilename METHOD A GetOpenFilename EXAMPLE CHAPTER TWENTY-SEVEN USERFORM BASICS WHAT IS USERFORMS WHY SHOULD YOU USE THE USERFORMS HOW TO CREATE AN EXCEL USER FORM HOW TO INSERT A NEW USER FORM INTO THE EXCEL WORKSHEET ADDING CONTROLS TO A USER FORM CHANGING PROPERTIES FOR A USERFORM CONTROL NAMING OF NAME USERFORMS ASSIGNING THE VBA CODE TO THE USER FORM TOGGLING BETWEEN THE CODE AND THE USERFORM WINDOWS THE USERFORM EXAMPLES ADDING THE CommandButtons ADDING A TextBox BUTTON ADDING A LABEL BUTTON
Page
16
CHAPTER TWENTY-EIGHT THE USERFORM CONTROLS THE CONTROL PROPERTIES THE USERFORMS DIALOG BOX CONTROL CheckBox CONTROL CommandButton CONTROL ComboBox CONTROL Frame CONTROL Image CONTROL Label CONTROL ListBox CONTROL Multipage CONTROL OptionButton CONTROL RefEdit CONTROL ScrollBar CONTROL SpinButton CONTROL TextBox CONTROL ToggleButton CONTROL CHAPTER TWENTY-NINE THE EXCEL ADD-INS WHAT IS EXCEL ADD-IN WHY SHOULD YOU USE ADD-INS? CHANGING OR CONVERTING AN EXCEL WORKBOOK TO AN ADD-IN USING THE SAVE AS OPTION TO CONVERT A FILE TO AN ADD-IN USING THE VB EDITOR TO CONVERT A FILE TO AN ADD-IN OPENING THE ADD-IN PROTECTING THE VBA CODE EDITING THE ADD-IN CHAPTER THIRTY EXCEL VBA SHORTCUTS, TIPS AND TRICKS ADD COMMENTS TO YOUR CODE DECLARE ALL VARIABLES NAME VARIABLES AND FUNCTIONS USING CLEAR AND NAMES BREAK YOUR WORK INTO SMALLER CHUNKS DISPLAY ONE PROCEDURE AT A TIME USE THE OPTION EXPLICIT RE-USE COMMONLY USED FUNCTION TO SAVE TIME TEST YOUR CODE REGULARLY TO AVOID RUN-TIME ERRORS error. ENSURE TO BACKUP YOUR FILES USE IMMEDIATE WINDOW LEARN HOW TO DEVELOP YOUR CODE USE PROPER LINE INDENTATION AVOID WRONG SPELLINGS USE THE VBA TO VIEW ALL BUILT-IN FUNCTIONS AND CONSTANT LOWER CASE ALL CODE USE SHORTCUT KEYS
Page
17
CONCLUSION ABOUT THE AUTHOR
Page
18
PREFACE As you will be taking your time to go through this user guide for Excel 2021, there are lots of stuffs to learn. The truth is, you don’t have to be a computer expert in other to make use of this user guide for Excel 2021. Here in this book, you will be learning the fundamentals of Excel which entails what Excel is all about, its uses, and how it can be manipulated. In the course of going through this book, you will be exposed to the basic operations on Excel which include understanding the worksheet, working with cells and also how to format the cells. Not only that, you will be exposed to variety of formulas and functions used in Excel and the new functions added in Excel 2021. You will also get to learn how to insert formulas and functions while working on the Excel sheet and other formatting features available to the use of formulas and functions. Features like creating of charts, changing of chart layouts and types, how to add title in charts, resizing the chart sizes and other formatting features associated with charts will be explicitly explained as you take your time to go through this guide. Finally, there are tips and tricks to learn which will enhance your effectiveness in the use of this software. Some of the tricks and trips are shortcuts and some operations that seem very hard but in the real sense, they are very easy to perform. Having stated what you get to gain by going through this user guide, I am sure you don’t want to miss out of this golden opportunity. See you in the next section! INTRODUCTION Microsoft Excel is one of the most used software applications in the world today and it comes together with some other applications found in the Microsoft office suite. This app replaced Lotus 1-2-3 as the industry standard for spreadsheets majorly because of its adaptive and flexible features. The Microsoft Excel has become an application that is sought after in big organizations in the world today because of its features to carry out some mathematical, financial and statistical operations that could have become
Page
19
cumbersome when done manually and with the use of Microsoft Excel, time and energy are saved for more productive works. Currently, with the way the world is evolving, business organizations are not just looking for a person who is computer literate rather a person who has vast knowledge on how to use Microsoft Excel and more of a reason as an individual, you can’t afford to lose a job opportunity because of you lack the skills to use of Microsoft Excel. With researches made so far, it has been discovered that Microsoft excel is one of those applications’ executive heads and heads of departments must train their staff to get accustomed to ensure the smooth running of such organization and not only that, to boost the work efficiency of the organization within and outside the organizations. Truth be told, Microsoft Excel is not an easy application to learn and use and most people find it difficult to use but guess what? In this user guide, Microsoft Excel will be taught in a way that you can easily comprehend and use. I wish you a safe trip as you take a ride to exploring this book
Page
20
CHAPTER ONE INTRODUCTION TO MICROSOFT EXCEL 2021 Excel 2021 is one of the latest applications developed by Microsoft Corporation itself with some features that have made it stand out from every other version that has been developed so far. In case you are a novice, let’s quickly take a tour to what Excel is all about before diving into Excel 2021 and its basic operations. WHAT IS EXCEL? Excel is an application software that provides worksheets with rows and columns that helps to organize data. Space where the rows and columns intersect is known to be the cell. The Excel software was created by Microsoft Corporation and comes with the Microsoft Office Suite. This tool is used for organizing and performing calculations and data, calculate statistics, generate pivot tables and display data in form of line graphs, histograms, and charts with a very limited three-dimension graphical display. With the Excel software, you can calculate monthly budgets, prepare payroll, track associated expenses and sort out data based on their categories or criteria. This software is widely used these days by almost everyone because of its capacity to save time and energy and this application gives room for the updating of its features as well as introducing new features through Office 365 from time to time in a year. Researchers have proven so far that big organization in the world are ardent users of this application. Initially, Excel was code named to be Odyssey while it was being developed before it was released on September 30, 1985. USES OF EXCEL Excel is one of the apps in the Microsoft Office Suite has a lot of advantages which cannot be overemphasized and here we will be talking about a few of
The above is a preview of the first 20 pages. Register to read the complete e-book.
AI Reading Assistant
Whole-book reading guide from stratified index samples; jump to passages in the text
AI guide
# Reading Guide: EXCEL 2021 FOR BEGINNERS
## 【One-Line Pitch】
A comprehensive, hands-on tutorial that walks complete beginners from Excel's basic interface through advanced formulas, Power Query, and VBA programming—ideal for business professionals, students, or anyone needing to master Excel 2021 for work or personal productivity.
## 【Book Arc】
- **Opening (~0%–7%)**: Introduces what Excel is, its uses, new 2021 features, key terminology, and file types—plus a full tour of the interface: launching Excel, the Ribbon, workbooks, worksheets, and the Start screen.
- **Early (~7%–21%)**: Covers core operations: working with cells (entering, copying, formatting), modifying rows/columns, tables, charts, and pivot tables—then moves into cell referencing (relative vs. absolute) and the fundamentals of formulas and functions, including operator precedence and error handling.
- **Early–Middle (~21%–36%)**: Dives into specific function families: math (SUM, SUMIF, ROUND, MOD), statistical (COUNT, AVERAGE, MIN/MAX), financial (PV, FV, PMT, depreciation), logical (IF, IFS, AND/OR), lookup (VLOOKUP, HLOOKUP, INDEX/MATCH), and text functions (CONCATENATE, TRIM, UPPER/LOWER, LEFT/RIGHT).
- **Middle (~36%–43%)**: Covers date and time functions (TODAY, NOW, DATEDIF, NETWORKDAYS), then introduces Power Query for importing and transforming data from multiple sources.
- **Middle–Late (~43%–64%)**: Transitions to automation: macros (recording, security, trusted locations), the Visual Basic Editor, the Excel object model, VBA procedures (Sub vs. Function), the Macro Recorder, and essential VBA language elements (variables, data types, arrays, Range objects).
- **Late–Ending (~64%–93%)**: Advanced VBA topics: built-in worksheet functions in VBA, user-defined functions (UDFs), error handling (On Error statements), message boxes and input boxes, UserForms and controls, Excel add-ins, and a final chapter of VBA tips and best practices.
## 【Key Takeaways】
- **Excel 2021's new features are the entry point** (Early): The book highlights that Microsoft added over a hundred enhancements, and understanding what's new helps you leverage the latest tools for productivity gains.
- **Master cell referencing before formulas** (Early): Relative vs. absolute references (using `$`) is the foundation for building scalable, copyable formulas—get this wrong and everything downstream breaks.
- **Functions are organized by category for a reason** (Early–Middle): Math, statistical, financial, logical, lookup, and text functions each solve specific problem types; knowing which category to reach for is half the battle.
- **Power Query is the modern data-import tool** (Middle): Instead of manual copy-paste, Power Query lets you import from CSV, text files, and multiple sources with a repeatable, editable process.
- **Macros are recorded, then refined** (Middle): The Macro Recorder generates VBA code you can examine and edit—it's the bridge between "doing things manually" and "writing code."
- **VBA is built on the object model** (Middle–Late): Excel's hierarchy (Application → Workbook → Worksheet → Range) determines how you reference and manipulate anything in code; properties and methods are your tools.
- **Error handling is non-negotiable in VBA** (Late): `On Error Resume Next`, `On Error GoTo`, and the Err object let you control what happens when things go wrong—essential for professional-grade macros.
- **UserForms turn macros into tools** (Late): Adding buttons, text boxes, and other controls lets you build simple interfaces so non-technical users can run your VBA solutions.
## 【Reading Tips】
- **Skim Chapters 1–2 if you've used Excel before**: The interface tour and basic cell operations are standard; jump ahead to tables, charts, and referencing if you're already comfortable navigating a spreadsheet.
- **Deep-read Chapters 5–12 for formula mastery**: These are the meat of everyday Excel work. Practice each function with the book's examples—don't just read the syntax, type it into a real workbook.
- **Treat Chapters 16–19 as a "VBA orientation"**: The Macro Recorder, VB Editor, and object model concepts are the foundation; don't rush these even if you're eager to code.
- **Use Chapters 21–25 as a reference, not a cover-to-cover read**: Variables, arrays, Range objects, and error handling are dense; come back to them when you're writing actual code and hit a wall.
- **Skip the tips chapters (14–15, 30) on first read**: They're useful as a quick-reference later, but they interrupt the learning flow if you're following the book sequentially.
## 【Coverage Limits】
This guide covers the book's structure and key concepts based on its table of contents and preface. The excerpts do not include worked examples, detailed function syntax, or actual VBA code samples, so specific implementation details should be verified in the book itself.
##
Passage locations
Page 4
2021 WHAT IS EXCEL? USES OF EXCEL WHAT’ S NEW IN EXCEL 2021? MICROSOFT EXCEL TERMINOLOGIES EXCEL TOOLS EXCEL FILE TYPES WHERE CAN I GET MICROSOFT EXCEL? CHAP...
View in text
Page 6
NTHESES WORKING WITH FUNCTIONS WHY SHOULD YOU USE FUNCTIONS? THE FUNCTION ARGUMENTS USING FUNCTIONS WITH NO ARGUMENTS USING FUNCTION WITH ONE OR MORE ARGUMEN...
View in text
Page 10
? WHERE IS THE POWER QUERY LOCATED? What Can Power Query Do? SOURCES OF POWER QUERY DATA IMPORTING DATA SOURCE USING POWER QUERY IMPORTING A SINGLE DATA SOUR...
View in text
Page 12
NG THE EXCEL MACRO RECORDER WHAT IS AN EXCEL MACRO RECORDER? BASICS OF USING THE MACRO RECORDER LIMITATION TO THE USE OF THE MACRO RECORDER RECORDING IN THE...
View in text
Recommended for You
{{#thumbnailUrl}}
{{/thumbnailUrl}}
{{^thumbnailUrl}}
{{/thumbnailUrl}}
Loading recommended books...
Failed to load, please try again later
Tip the Site
Scan the WeChat Pay or Alipay code to tip. No login required.
WeChat Pay
Alipay