Excel 101 E-Book

Johannes Wild

• Herausgeber: 3DTech
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 69

Veröffentlichungsjahr: 2021

Beschreibung

Sick of seeing "#VALUE!" ? Have you never worked with Microsoft Excel before or do you already know basic functions and elements? Would you like to master the most important features and functions of Microsoft Excel?

Well, if you feel addressed by even one of these questions, this book is the right choice for you! Learn the essential functions of Microsoft Excel (applicable for the versions: 2019, 2016, 2013, 2010 and Office 365). Whether you want to create professional tables, charts, data sheets, analyses or forecasts you will literally master Excel with this guide in no time!

The author of the book is an engineer & Excel expert, who guides you from the basics such as: tables, charts and calculations to the more advanced Developer tools. Step by step and with numerous illustrations as well as colored markings & other didactic hints, you will learn Excel´s most important features.

With this book you can become an Excel professional with minimal (time) effort! After a short introduction to the basic terms, simple functions as well as advanced features are explained in detail throughout this book. Although all functions of the Microsoft Excel ribbon are discussed in detail all features are explained on about 90 pages, avoiding any unnecessary information in order to save your precious time!

Content of the book (short form):

1) Excel Interface & recommended settings        

2) Start, Basics Tips & Shortcuts       

3) Insert                                              

4) Draw                                               

5) Page Layout                                   

6) Formulas                                        

7) Data

8) Review

9) Developer

10) Excel error messages

This book is generally intended for all people who are interested in mastering Excel or who (have to) work with it in private or professional life. No matter if you use Microsoft Excel 2019, 2016, 2013, 2010 or Excel from Microsoft Office 365, this book offers the essential functions - the quintessence - of the spreadsheet and calculation program. Use this book to start or expand your private and professional (Excel) success.

Take a look at the book now and order your copy right away!

Leseprobe

Legal information

© 2022

Author and Editor: M.Eng. Johannes Wild

A94689H39927F

Email: [email protected]

The complete imprint of the book can be found on the last pages!

This work is protected by copyright

The work, including its parts, is protected by copyright. Any use outside the narrow limits of copyright law without the consent of the author is prohibited. This applies in particular to electronic or other reproduction, translation, distribution and making publicly available. No part of the work may be reproduced, processed or distributed without written permission of the author! All rights reserved. All information contained in this book has been compiled to the best of our knowledge and has been carefully checked. However, the publisher and the author do not guarantee the timeliness, accuracy, completeness and quality of the information provided. This book is for educational purposes only and does not constitute a recommendation for action. The use of this book and the implementation of the information contained therein is expressly at your own risk. In particular, no warranty or liability is given for damages of a material or immaterial nature on the part of the author and publisher for the use or non-use of information in this book. This book does not claim to be complete or error-free. Legal claims and claims for damages are excluded. The operators of the respective Internet sites referred to in this book are exclusively responsible for the content of their site. The publisher and the author have no influence on the design and contents of third party internet web sites. The publisher and author therefore distance themselves from all external content. At the time of use, no illegal content was present on the websites. The trademarks and common names cited in this book remain the sole property of the respective author or rights holder.

Software, screenshots and brand names are used with permission from Microsoft. The trademarks “Microsoft” and “Excel” remain the sole property of Microsoft. There is no sponsorship or business relationship with Microsoft.

Preface (Important Information about the Book!)

How to use this book and what you can expect from it

Thank you very much for choosing this book!

Probably everyone knows that Microsoft Excel is a powerful spreadsheet program. But most people only know the basic functions and cannot use the hidden treasures in Excel. This misery is over now. In this book you will learn how to make full use of Excel's top functions. This book equips you with the most important tools in your daily struggle with the flood of data and information that we are exposed to in our private and professional lives today.

Bring order to your tables and shine with sophisticated Excel knowledge. Not only will you impress your superiors, but you will also have helpful tools at your fingertips in your private life. For the administration of personal property, real estate, planning birthday parties and much more.

To provide a clear introduction at all levels, the book is divided into "Basic" and "Expert" topics within the chapters. The "Basic" sections explain basic functions that provide everyday help. In the "Expert" chapters we go a little deeper into the subject and learn advanced functions for the corresponding topic. Each chapter contains theoretical instructions (illustrated & step by step) as well as many practical examples.

The font color of the chapter headings reflects the difficulty level "Basic" or "Expert". As a beginner you should read all chapters from the beginning, especially chapters with green writing ("Basic"). Chapters with violet font colour are interesting for beginners as well as for advanced users. And as an advanced Excel user you can skip green chapters and start immediately with the blue chapters ("Expert").

Of course this is only a classification according to the author's feeling. You have to find out for yourself what level you are at in Excel. So please take this classification only as a recommendation.

The "Ribbon", which will be mentioned frequently, can be found in Excel at the top, just below the green bar including well-known functions such as "Save", "Undo", etc.

This book is suitable for Microsoft Excel 2010, 2013, 2016 & 2019 as well as Microsoft Office 365. The information in this book was created using Excel from Microsoft Office 365, so some features of the Excel versions may differ from earlier versions (especially 2010 & 2013). This also applies to design elements. Don't let it throw you off track. In the end you will certainly learn many great functions!

Try to actively practice the examples and theoretical considerations in Excel, rather than just reading the book. This is very important to internalize the functions as you know for sure: Only practice makes perfect!

Let´s get started!

Table of Contents

Legal information 1

Preface (Important Information about the Book!) 2

Table of Contents 4

1 Excel Interface & Settings 6

1.1 Excel Interface & Important Sections 6

1.2 Recommended Settings 10

2 Start, Basic-Tips & Shortcuts 12

2.1 Shortcuts 12

2.2 Quick-Analysis Tool 14

2.3 Formatting in a Nutshell 16

2.3.1 Adjust Column & Row Width automatically 16

2.3.2 Insert Options 16

2.3.3 Autofill & Pattern Recognition 18

2.3.4 Cell Name &Hide Cell Value19

2.4 Unit Conversion20

2.5 Find and Replace 21

2.6 Sort 22

3 Insert 25

3.1 Tables 25

3.1.1 Simple Tables 25

3.1.2 PivotTables 26

3.2 Illustrations and Add-Ins 29

3.3 Charts 32

3.4 Sparklines 37

3.5 Slicer 38

4 Draw 40

5 Page Layout 41

6 Formulas 42

6.1 Relative and Absolute Cell References 42

6.2 Function Library, Define Names & Formula Evaluation 45

6.3 Basic Formulas & Important Functions 49

7 Data 54

7.1 Get/Transform Data and Queries & Connections54

7.2 Filter 58

7.3 Data Tools 64

7.4 What-If Analysis 68

7.5 Forecast Worksheet 73

8 Review76

9 Developer 78

9.1 Create Code & Macros 78

9.2 Creating more complex Macros 83

9.3 Add-Ins: "Data Analysis" & "Solver" 86

10 Excel Error Messages92

10.1 Error #####92

10.2 Error #NUM!92

10.3 Error #VALUE!93

10.4 Error #NA93

10.5 Error #NULL!93

10.6 Error #NAME?94

10.7 Error #DIV/0!94

10.8 Error #REF!94

10.9 Editing locked & Links, Macros without Function94

10.10 Circular Reference95

10.11 Number becomes Date95

11 Closing Statement96

1 Excel Interface & Settings

At the beginning you will get a first overview of Excel's user interface and the basic functions & settings. In this section we will discuss very basic things, i.e. if you have already used Excel a few times, you can skip chapter 1.1. However, you should follow the recommended settings in chapter 1.2.

1.1 Excel Interface & Important Sections

We start with the red framed area in Fig. 1, where you can navigate through the ribbon. If some sections are not displayed in the ribbon, you have to activate them first, as explained in Chapter 1.2. The section "Home" includes basic functions.

In the orange framed area you will find the cell "name box". By default, this is specified with an alphanumeric reference (A1,B2,...). However, you can also name the cell as you wish. To do this, select the orange-framed cell field and write the name, then confirm with "Enter". Note that you must now use the new name for each reference to this cell.

The area framed in blue is used to enter a function (but normal text or numbers can also be entered). For a selection and description of possible functions click on the small fx symbol.

In the green framed section in the lower area you will find the possibility to add and manage further worksheets. In the yellow area you can scale the display (zoom) and choose between "normal display", "page layout" or "page break view". Just try out the different display options!

Figure 1: Excel Interface and worksheet

In area 1 (see Fig. 1) you can copy, paste or cut content and use "Format painter". This function is very helpful if you want to transfer the formatting of a cell - for example: text size, bold text, italic text or even text or cell color - to one or more other cells in exactly the same way.

In area 2, make settings for the font of texts, i.e. elements such as: italic, bold, underlined,