Basic Excel 2023 E-Book

Borrego Robert

• Herausgeber: AMD
• Sprache: Englisch

Veröffentlichungsjahr: 2023

Beschreibung

Learn how to uncover the stories behind the number with Excel!
Learning the most popular program for data representation and analysis in a world of tremendous change and overwhelming complexity in data management, equips you as a valuable person whose skill is vital in almost every office jobs.
Excel has become fundamental in today’s fast-changing world for efficiency and effectiveness in modern office task. Unfortunately, a lot of people with little knowledge of this handy calculative application, sees it as a very complicated tool for calculation, while some others perceive it as too much brainwork, hence the lack of drive to learn.
This guide to Excel is detailedly and adequately equipped to take you on a step-by-step journey on the fundamentals of Excel, thereby making you work smarter.
This book is a great starting point to everyone who desires to get into the Excel world, and for those who are already in it, this handy guide is one book you will want to keep within arm’s reach when working, you can never tell what knowledge you might be missing to give your work that cutting edge.
Waste no more time struggling with figures; Scroll up and hit the ‘BUY NOW’ button to start excelling!

Leseprobe

Basic

Excel

2023

An Essential Guide to Foundational Excel

Robert Borrego

Copyright

Basic Excel 2023

Copyright © 2023 Robert Borrego

All rights reserved. No part of this publication may be reproduced or transmitted in any form or by any means, electronic, or mechanical, including photocopying, recording, or by any information storage and retrieval system, without permission in writing from the publisher.

While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein.

Disclaimer

This book is geared towards providing information in regards to the topic and issue covered. The publication is sold with the idea that the publisher is not required to render accounting, officially permitted, or otherwise, qualified services. If advice is necessary. Legal or health professional, a practiced individual in the profession should be ordered.

Printed on acid-free paper.

Table of Content

Copyright

CHAPTER ONE

INTRODUCTION

Understanding Microsoft Excel

Why Microsoft Excel?

Uses of Excel

Benefits of Excel

Understanding Excel Terminologies

CHAPTER TWO

EXCEL STARTUP INTERFACE

Excel Workspace

Quick Access Toolbar

Cell Basics

Ribbon, Tabs and Menus

Ribbon Tabs

Modifying Columns, Rows and Cell

Saving, Sharing and Exporting

CHAPTER THREE

DATA ENTRY AND FORMATTING

Formatting Cells

Number Formats

Multiple Worksheet

Data Entry

Page Layout and Printing

CHAPTER FOUR

MANAGING AND ORGANIZING DATA

Groups and Subtotals

Conditional Formatting

Sorting

Filtering

Text Functions

Text to Columns

Mixed Type Charts

Data Validation

Inspect and Protect Workbook

Track Changes and Comment

Data Entry Form

CHAPTER FIVE

FORMULAS

What are Formulas in Excel?

Adding Formulas to multiple Cells

Creating Complex Formulas

Cell References

CHAPTER SIX

FUNCTIONS

Building Blocks of Excel Functions

Function Arguments

Predefined Excel Functions

Essential Excel Functions

CHAPTER SEVEN

TABLES AND PIVOTTABLE

Tables in Excel

Formatting Table

PivotTables

Changing Rows and Columns

Report Filters

Slicer

PivotCharts

CHAPTER EIGHT

EXCEL CHARTS AND GRAPHS

Insert Charts

Edit Chart

Improving Charts

Adding More Series

Creating Combination Charts

CHAPTER NINE

COMMON EXCEL ERRORS

#### Error

#VALUE! Error

#SPILL! Error

#NAME? Error

#REF! Error

#DIV/0! Error

#N/A Error

#NUM! Error

#NULL! Error

CHAPTER TEN

EXCEL SHORTCUTS, TIPS AND TRICK

Keyboard Shortcuts

Find Inconsistent Data

Quicker Fill Handle

Flip Data with Transpose

Using Format Painter

Using Quick Analysis

Avoid Merging Cells

About the Author

CHAPTER ONE

INTRODUCTION

Understanding Microsoft Excel

Microsoft Excel is a spreadsheet software developed by Microsoft corporation. It is available for Microsoft Windows, Mac OS X and iOS operating systems. It features calculation, graphing, graphic tools, pivot tables and a macro programming language called Visual Basic for applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 and 1993. Also, it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office suite. Microsoft Excel has the basic features of all spreadsheets; it uses a grid of cells arranged in numbered rows and lettered columns to organize data manipulations like arithmetic operations. An Excel spreadsheet is a series of rows and columns that create a table. Numbers are often given to rows, while alphabetical letters are typically assigned to columns. A cell is the intersection of a row and a column. The letter denoting the column and the digit denoting the row together form the address of a cell. Excel has a battery of supplied functions to answer statistical engineering and financial needs.

In addition, it can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display. It also allows sectioning of data to view its dependencies on various factors for different perspectives, using pivot tables and the scenario manager. It has a programming aspect – Visual Basic for applications allowing the user to employ a wide variety of numerical methods. For instance, for solving differential equations of mathematical physics and then reporting the results back to the spreadsheet. It also has a variety of interactives features, allowing user interfaces that can completely hide the spreadsheet from the user, so the spreadsheet present itself as a so-called application or decision support system via a custom designed user interface, for example, a stock analyzer or in general as a design tool that asks the user questions and provides answers and reports.

Why Microsoft Excel?

When you have a mountain of data; wouldn’t it be great if you could find the numbers behind it on your own? That is why Excel is of great importance. As you enter data, Excel recognizes patterns, so that it can automatically fill in the remaining info for you. Then, after you have entered your data, Excel helps you spot trends and patterns using data bars, sparklines, color-coding and icons. Excel even helps you summarize and illustrates your insides by recommending premade pivot tables and charts. And when you are all done, you can let others view and edit your work by saving to the cloud on one drive. And you don’t necessarily need a PC; you just need a browser or the Excel mobile app.

Uses of Excel

❖ Scheduling: Businesses can create basic employee and resource schedules with Excel that can be color-coded and designed to automatically update as the schedule changes. For instance, weekly worksheets can be created with column headings with each day, and name the rows based on hour slots or work shifts.

❖ Basic Accounting: Small businesses often used Microsoft Excel as a basic accounting program or checkbook ledger. You can enter deposits and expenditures onto each row of the sheet as you would enter them into a check register.

❖ Product Sales: You can track product sales using Excel on a daily, weekly, monthly or quarterly basis. Collecting the sales data onto the Excel spreadsheet enables you to compare progress over time.

❖ Return on Investment: Tracking total business sales in dollars along with total advertising and marketing expenditures with Excel allows you to see your return on investment for each campaign.

Benefits of Excel

▪ Improve Efficiency and Productivity: Excel is an important tool for speeding up productivity and empowering workers to be more effective when administering large amounts of data and calculations.

▪ Analyzes very Large Data: A pivot table enables you to summarize data as averages sums or counts in Excel from data that is collected in another spreadsheet or table. It is great for quickly building reports because it helps to sort and visualize data quickly.

▪ Best Tool to Organize Numerical Data: Excel is a common tool for managing and organizing data in spreadsheets. It is a spreadsheet software that enables you to systematically organize all of your data while giving you the ability to sort the information in any way that you choose. New data can be strong and hard to analyze, but with the high-level capabilities of Excel, you will be able to organize your data better, make calculations when needed and sort the information so that it can be properly investigated and transferred to graphs or charts for better viewing.

▪ Easier and Faster Work: The more proficient at Excel you become, the more speedily you will be able to operate the system. Microsoft Excel features various shortcuts that can allow you to work faster and even learn more complex Excel strategies that can be applied across the entire suite of Microsoft Office products. It helps to use the data in your Excel sheet over a variety of programs and lets you make your work flow better. Thus, stronger skills open the door of opportunities in multiple fields like business analytics, people management, managing operations, office administrations and strategic analysis.

Understanding Excel Terminologies

Like all other areas of computers and fields of study, spreadsheets have their own ‘language’. This listing of common terms is provided to serve as a reference for you when working with spreadsheets.

Cell – The intersection of a row and column. Information is stored in a cell.

Cell Pointer – This is similar to the Word processing insertion point. It selects or marks the current cell (where the next activity is going to take place).

Cell Address/References – The address (consisting of the column and row IDs) of a specific cell. A cell reference identifies the location of a cell or groups of cells in the spreadsheet. A cell reference consists of the column letter and the row number that intersect at the cell’s location. Note that when listing a cell reference, the column letter is always listed first.

Label – A cell that contains text or a combination of numbers and text (or only numbers that cannot be used in a calculation). Labels cannot be used for calculation.

Column – A vertical group of cells within a spreadsheet.

Range – A group of cells. Ranges are often referenced for formulas, printing and for designating a group of information to a copied or cut. Ranges can be selected by dragging (sometimes referred to as painting) over the cells. Using the mouse, keyboard or name box, a number of cells can be selected to create a range. Commands executed by Excel will affect the entire range. An example of a range of adjacent cells would be; B5:D10.

Sheet – A layer of the spreadsheet. Excel spreadsheets can have multiple sheets (this can be helpful if you want one spreadsheet that contains the gradebooks for all sections that you teach, each section can be on a separate sheet).

Value – A number in a cell that can be used on Excel calculations.

Active Cell – The active cell is the cell surrounded by a thick border. The thick border is referred to as the active cell highlights.

Formula Bar – The formula bar in Excel is located next to the name box above the worksheet area. The formula bar displays the data or formula stored in the active cell. It can be used to enter or edit a formula, a function, or data in a cell.

Workbook – A spreadsheet program file that you create in Excel. A workbook contains worksheets of columns and rows in which you can enter and calculate data. A workbook is basically the program file of a worksheet or collection of worksheets.

Worksheet – The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook. A collection of cells that is grouped on a stage layer of the spreadsheet. A worksheet is just a single tab in a workbook.

Workspace File – A file that saves display information and open workbooks, so that you can later resume work with the same window sizes, print areas, screen magnification, and display settings. A workspace file doesn’t contain the workbooks themselves.

Fill Handle – The small block square in the lower-right corner of the selection outline. When you point to the fill handle, the pointer changes to a thick cross.

Scroll Bars – Horizontal and Vertical scrollbar that appears on the bottom and right side of the worksheet window and enables you to quickly move to a different area of a worksheet.

Default Startup Workbook – The new, unsaved workbook that is displayed when you start Excel.

CHAPTER TWO

EXCEL STARTUP INTERFACE

When you open the Excel application, the starting interface is known as a splash screen or backstage view. On the left-hand side of the splashscreen are the Startup Options. Next to the Startup Options is a Blank Excel Workbook, after which you have the Templates/Tutorials and some Additional Options at the bottom left corner, while Recent Files are displayed at the center of the screen. Among the sections at the bottom-left corner are Account, Feedback and Options. Account is where you find useful information related to your account – user information, customized settings, connected service etc. On the right-hand side are product information about the version of Microsoft Office that is being used. Still on the right-hand side, you can check to see if there are any updates available. Underneath the Account section is the Feedback entry, which enables you to send feedback about a particular feature of Excel to Microsoft. The Option section is a very important section that would be frequently referred to in this guide. The Option section is basically where you customize how your copy of Excel works. So, it is effectively all of your settings. Although your interface might look a little bit different based on the version you are working on. If you are on a Mac, it is going to look a little different beyond what is in the image below. But you don’t need to worry about those differences, as the same working process is applicable in all versions.