Excel for Beginners 2023 E-Book

Gerald Stroud

• Herausgeber: Michael B. Byrd
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Veröffentlichungsjahr: 2023

Beschreibung

Do you wish to improve your Excel proficiency and improve your potential for employment in the data-driven workforce of today? Search no further than Excel for Beginners 2023, the most comprehensive resource for learning Excel's essentials. This comprehensive and step-by-step guidebook is designed to assist you establish a solid foundation in Excel, whether you're new to the program or simply want to brush up on your expertise.

Excel for Beginners 2023 is going to guide you through all the basics of Excel, from building a spreadsheet to organizing data, from using formulae and functions to generating graphs and charts. It accomplishes this with simple explanations, comprehensible screenshots, and practical examples. You'll discover how to work with cells, ranges, and Excel's built-in tools for analyzing information and visualization.

However, there's more to it; Excel for Beginners 2023 also covers more complex concepts like conditional formatting, pivot tables, and macros. You can take on any Excel task with confidence if you use this book.

Why then wait? Download Excel for Beginners 2023 right away and make an investment in your future. This book will undoubtedly turn into your primary reference for learning Excel due to its useful and engaging approach.

Leseprobe

Excel for Beginners 2023

A Step-by-Step and Comprehensive Guide to Master the Basics of Excel, with Formulas, Functions, & Charts

Gerald Stroud

Table of Content

Introduction

Chapter 1: What is Microsoft Excel

1.1 Uses of Microsoft Excel

1.2 History and Future of MS Excel

1.3 Basic Features of the Microsoft Excel

1.4 Terminologies and Components

1.5 Basic Excel Capabilities

1.6 Advanced Excel Capabilities

1.7 Excel Keyboard Shortcuts

1.8 XLS and XLSX File

Chapter 2: Why Learn Excel 2023?

2.1.1 Table and Spreadsheets Design

2.1.2 Macros and Shortcuts

2.1.3 Graphs and Charts

2.1.4 Sparklines and pivot tables

2.1.5 Templates

2.1.6 Forecasting

2.1.7 Collaboration

2.1.8 Analog to digital conversion

2.2 Microsoft Excel advantages

2.2.1 Benefits of Using MS Excel

2.3 Hidden Excel Gems

Chapter 3: Basic Excel Formulas

3.1.1 SUM

3.1.2 Subtraction

3.1.3 Multiplication

3.1.4 Division

3.2. IF

3.3 Percentage

3.4 Date

3.5 Array

3.6 COUNT

3.7 AVERAGE

3.8 SUMIF

3.9 TRIM

Chapter 4: Modifying the Worksheet

4.1.1 Modify Column Width

4.1.2 AutoFit Column Width

4.2 Modify Row Height

4.3 Modify all Columns or Rows

4.4.1 Insert rows

4.4.2 Insert Columns

4.5.1 Deleting a Column or Row

4.5.2 Cleaning Column or Row Content

4.6 Move a Column or Row

4.7 Hide and Unhide a Column or Row

4.8 Wrapping text and merging cells

4.9 Center Across Selection

Chapter 5: Five Ways Excel Can Improve Productivity During Your Work Home

5.1 Process a Large Amount of Information

5.2 Utilizing Fill Handles

5.3 Crosschecking Functions at a Glance

5.4 Leverage the Goal Seek Formula

5.5 Automate Recurring Tasks with VBA

Chapter 6: Types of Cell References

6.1.1 Relative Cell Reference

6.1.2 Mixed Cell Reference

6.1.3 Absolute Cell Reference

6.1.4 Function of the Dollar ($) Sign in a Cell Identifier

6.2.1 Changing from a Relative Reference to an Absolute or Mixed Reference

6.2.2 Cell References and Automatic Updating

6.2.3 Using Different Workbooks to Reference Cells

6.2.4 Cell Range: A Quick Primer

6.2.5 Duplicating Equations and Different Cell References

6.3 Creating a Multiplication Table

6.3.1 Setting the Information Up

6.3.2 Populating the Multiplication Table Using Mixed References

6.3.3 Using Array Formula to Create a Multiplication Table

6.4 Selecting Several Cells in Excel

6.4.1 How to choose several cells at once in Excel

6.4.2 Using the Shift Key to Select Multiple Cells

6.4.3 Use the Shift and Arrow keys to select many cells.

6.4.4 How to use the Control key to select multiple cells at once

6.4.5 How to choose several columns

6.4.6 How to choose several rows at once in Excel

Chapter 7: The Workday .INTL Function in Excel

7.1 How can I utilise Excel's WORKDAY.INTL function

7.2 Examples of Workday.Intl

7.3 Problems in Workday.Intl

7.4 The RANBETWEEN Function

7.4.1 Ways you can use RANDBETWEEN Function

7.4.2 Observations regarding the RANDBETWEEN Function

7.5 The RAND Function

Chapter 8: The VLOOKUP Function

8.1 VLOOKUP Formula

8.2 Using VLOOKUP in Excel

8.3 VLOOKUP in financial research and modelling

8.4 Notes worthy about VLOOKUP Function

8.5 The HLOOKUP Function

8.5.1 The Syntax

8.5.2 HLOOP Function Parameters

8.6 Type of Function

8.6.1 Worksheet Function

8.7 Approximate Match & Exact Match

8.8 Scenario that require Absolute Referencing

8.9 Handling #N/A Errors

Chapter 9: The Transpose Function

9.1 The Syntax

9.1.1 TRANSPOSE function parameters

9.1.2 Returns

9.2 Transpose as WS Function

9.3 The COUNTBLANK Function

9.4 How can I utilise Excel's COUNTBLANK function?

Chapter 10: The Data Entry Form

10.1 Excel Date Entry Form

10.2 Adding the Form tool to Microsoft Excel

10.3 Creating a Data Entry Form

10.4 Adding a New Record

10.5 Performing a Record Search

10.6 Restoring and Updating Records

10.7 Combining Data Validation with Data Entry Form

10.7.1 Data Validation Limitations

10.7.2 Forms Formulas

10.8 Data Entry Form Error

10.8.1 Too Many Fields

10.8.2 Can’t Extend Database or List

10.8.3 Cursor is Outside the Table

10.8.4 A Named Range Database

Chapter 11: Excel Valuation Modelling

11.1 Why you should Utilise Valuation Modelling

11.2 Ways to perform Valuation Modelling

11.2.1 Discounted Cash Flow Modelling

11.2.2 Comparable Trading Multiples

11.2.3 Precedent Transaction Modelling

11.3 Necessary Skills required for Valuation Modelling

11.4 Careers that Require Valuation Modelling

11.5 Advantages of Valuation Modelling in Excel

Chapter 12: Tables in Excel

12.1 Advantages & Disadvantages of using Tables in Excel

12.1.1 Advantages

12.1.2 Disadvantages

12.2 Creating an Excel Table

12.2.1 Get the Information Ready

12.2.2 Creating the Table

12.2.3 Filtering & Sorting the Information

12.2.4 Naming the Excel Table

12.3 Styles in Excel Table

12.3.1 Changing Styles in Excel

12.3.2 Creating a Table with Specified Style

12.3.3 Creating a Custom Style

12.3.4 Update Custom Style

12.3.5 Apply Custom Style

12.4 Totals in Excel Table

12.4.1 Display Totals in the Table

12.4.2 Calculation Total Rows

12.4.3 Filtered Data Totals

12.4.4 Modifying and Adding Totals

12.4.5 Shutting off the Total Row

12.4.6 Using Total Row to Add Data

12.4.6.1 Add a Single Row

12.4.6.2 Add Multiple Rows

12.5 Formula in Table

12.5.1 Column Reference using Formula

12.5.2 Column Reference using Formula from Outside

12.5.3 Error in Copying Formulas with References

12.5.4 Solution to the Aforementioned Error

12.6 Table Expansion Issues & Fixes

12.6.1 Extra Rows Beneath the Table

12.6.2 Table's Hidden Rows

12.6.3 Enable the table Autoformat options

12.7 Fixing Data Validation Issues

Introduction

The spreadsheet, Microsoft Excel is utilized for storing and examining statistical and numerical information.

The spreadsheet program Excel provides multiple features that allow you to carry out various operations, such as computations, pivot tables, graphing tools, macro coding, etc. It runs on multiple operating systems, including macOS, Windows, Android, and iOS.

An array of columns and rows linked together to form a table can be referred to as an Excel spreadsheet. Numbers are often used to identify rows and letters of the alphabet are usually assigned to columns. A cell is the intersection of a column and a row. The character denoting the column and the number indicating the row together form the address of a cell.

There are a lot of spreadsheet applications out there, however, Excel is the one that is widely used. It has been in service for over thirty years now, and throughout that time, it has undergone upgrades with a growing variety of functions.

The most appealing aspect of Microsoft Excel is that it can be utilized for many different company functions, such as tracking inventory and billing, forecasting, finance, data management, and business intelligence. Some of the things it is able to do for you are listed below:

●  Calculating

●  Changing Text

●  Charts and Graphs

●  Automating Workflows

●  Templates and Dashboards

●  Keeping and Importing Data

We all interact with figures in some capacity. Each individual has everyday costs, that everyone has to deal with the earnings that we make every month. Understanding one's earnings and expenditures is a prerequisite for reasonable budgeting.

Microsoft Excel can be helpful for gathering, analysing, and organizing such numerical information.

Chapter 1: What is Microsoft Excel

Microsoft Excel is a computer application that allows you organise, format and calculate information using several formulas on a spreadsheet system. It was designed by Microsoft and a part of the very popular Microsoft Office Suite and can be purchased via the internet with a subscription to the Office 365.

Microsoft Excel is a spreadsheet system that works on both the Microsoft Windows operating system and Apple’s Mac operating system. The Microsoft Excel is a combination of cells categorised into rows and columns you can use to arrange and handle data. Subsequently, you can also use it to create macros, pivot tables, display information as charts, histograms and line graphs.

The Microsoft Excel is a powerful tool that lets you to present data in various formats.

1.1 Uses of Microsoft Excel

Microsoft Excel an application that is used across companies and business functions to arrange and present data as well as some perform financial analysis.

Below is a list of some of the basic uses of the Microsoft Excel.

●  Data entry

●  Accounting

●  Programming

●  Financial analysis

●  Task management

●  Financial modelling

●  Time management

●  Data management

●  Charting and graphing

●  Customer relationship management (CRM)

1.2 History and Future of MS Excel

The Microsoft Excel application was released in 1985 with the original name of Multiplan. Competing with several other similar products back then such as the Lotus 1-2-3 from the Lotis Development Corp, Microsoft Excel played a central role in bookkeeping and record-keeping for enterprise operations.

Microsoft Excel, then Multiplan made it very easy to handle, process and present spreadsheet information. Although Lotus 1-2-3 introduced cell names and macros to spreadsheet, Multiplan introduced a graphical user interface (GUI) that allowed users to locate and point making use of a mouse.

Multiplan initially lost its popularity on the Microsoft Disk Operation System when compared to the Lotus 1-2-3 but with the release of its version 2.0 in 1987, 2 years after it initial release the new version began to outsell Lotus 1-2-3 and other competing spreadsheet products. By 1990, a third version was released with new features such as 3D charts, toolbars, outlining and drawing capabilities.

A 4th version came in in 1992 which introduced the auto fill function and a 5th version in 1993 that introduced the Visual Basic for Applications macros, enabling common day tasks to be automated.

Regular version releases and updates kept happening until a major one in 2007 that came with the version 12 release. With this release, Microsoft Excel introduced a new Ribbon menu system, functions that had been worked on, including a SmartArt set of diagrams, improved management of name variables, and more flexibility when it comes to graphs formatting.

Version 15, released in 2013 introduced more tools such as the Power Pivot, Power View, and flash fill, as well as a better multithreading capability. The most recent version, version 16 released in 2019 cements Microsoft Excel as the leading spreadsheet software tool with an estimated monthly user of 750 million to 1.2 billion worldwide.

1.3 Basic Features of the Microsoft Excel

●  Home: This presents the user with several functions to customise such as font styles, font size, font colour, alignment, background colour, formatting styles and options, insertion and deletion of rows and columns and other editing options

●  Insert: This feature allows the user to insert several elements such as images and figures, charts, graphs, table, style and sparklines. It also allows the users to format the header and footer option, add equation and symbols

●  Page Layout: This feature allows the user to change the page themes, orientation and customize the page setup

●  Formulas: This feature allows you to easily add several formulas to your table

●  Data: With data, you can easily add external information, access other data tools and filtering options

●  Review: Easily add comments to the spreadsheet and proofread your data table

●  View: Zoom in, zoom out, choose different view to present your data in, rearrange your pane and many more

1.4 Terminologies and Components

●  Active cell: This is indicated by an outlined green box and shows the currently selected cell

●  Cell: The cell is an intersection of a column and row that you input data into

●  Cell Reference: This is a combination of numbers and letters used to pinpoint the location of a cell. Columns are vertical and assigned a letter, rows are horizontal and assigned a number.

●  Worksheet: Worksheet is the individual documents within an excel file

●  Worksheet Tab: These tables are shown at the bottom left corner of the spreadsheet and allows easy navigation between worksheets

●  Workbook: A workbook is a combination of several worksheets

●  Column and Row Headings: These are the lettered and numbered cells just outside of the columns and rows. Clicking a header highlights the entire column or row

●  Formula: Formulas are functions, cell references or mathematical equations that are used to process data

●  Formula Bar: This is the input field used to enter formulas or values in a cell. It is found at the top of the worksheet, beside the "fx" label

●  Address Bar: This shows the letter and number of the active cell and is located to the left of the formula bar

1.5 Basic Excel Capabilities

●  Filter: Filter is located under the sort & filter tab and can be set by the user to select certain rows or columns that he or she wants displayed in a worksheet

●  AutoFill: The autofill feature allows the user to copy data into more than one cell at a time

●  AutoSum: AutoSum allows a user to add multiple values by selecting the cells they want to add then pressing the Alt and Equal keys

●  Source Data: is the data is used to create the PivotTable

●  PivotTable: is a data summarisation tool that automatically sorts and calculates data and is found under the insert tab on the far left

●  PivotChart: is a visual aid to the PivotTable, that provides a graphical representation of the data and is found under the middle of the insert page, next to the maps