REMY LENTZNER UPGRADING YOUR SKILLS WITH EXCEL French original title: Excel, remise à niveau et perfectionnement EDITIONS REMYLENT, Paris, 1ère édition, 2021 R. 399 397 892 Paris 25 rue de la Tour d’Auvergne - 75009 Paris REMYLENT@GMAIL.FR Excel is a registered trademark of Microsoft Inc. ISBN EPUB : 9782490275489 The Intellectual Property Code prohibits copies or reproductions intended for collective use. Any representation or reproduction in whole or in part by any means whatsoever, without the consent of the author or his successors in title or cause, is unlawful and constitutes an infringement, pursuant to articles L.335-2 and following of Intellectual Property Code.
This book is dedicated to Anna and Maryvone. I could not have written it without their support, advice, encouragements and proofreading. Graphic illlustration : Bruno CONQUET In the same collection Improve your PivotTables with Excel Improve your skills with Google Sheets Programming macros with Google Sheets Getting started with HTML Getting started with JavaScript Getting started with PHP & MySQL Google Gmail online Google Docs online Google Slides online TABLE OF CONTENTS CHAPTER 1 Calculating rightly 1.1 The working environment and formulas 1.2 Workshop : a body mass index calculation 1.3 Some reminders about the formulas 1.4 Extracting simple statistics 1.2 Formulas with dates 1.1 Difference between two dates 1.2 Information about formulas 1.3 Adding a number of months to a date 1.4 The DATEDIF function for seniority calculations 1.5 End of month 1.6 Calculation according to working days 1.3 The IF function 1.1 Comparing a budget and expenditures 1.2 Nesting IF functions 1.3 The Lookup function 1.4 IF and AND 1.4 The $ sign in formulas 1.5 Formulas with text 1.1 A strange date that turns into a valid date 1.2 The Flash-fill feature CHAPTER 2 Conditional formatting 2.2 Using a formula to highlight cells 2.5 Highlighting dates Workshop1 : displaying all the dates for year 2018 Workshop2 : Colouring the rows for year 2019 2.2 Top/bottom rules 2.3 Colouring empty cells 2.4 The Data Bars 2.6 Colour scales CHAPTER 3 Filtering, sorting out, layouts and printing 3.2 Data with a format as table 3.3 The Total row 3.3 Layouts and Printing 3.1 The viewing dialogue box 3.2 The layout settings 3.3 Setting the print area 3.4 Inserting page breaks 3.5 Viewing page breaks CHAPTER 4 Charts 4.8 The Sparkline chart CHAPTER 5 Advanced functions 5.1 The VLOOKUP function 5.1 Finding a text value 5.2 The #N/A error helps 5.3 Finding a number in a scale 5.4 Searching with a joker * 5.5 VLOOKUP with two conditions 5.2 The INDEX/MATCH functions 5.1 The INDEX function 5.2 The MATCH function 5.3 INDEX and MATCH together 5.3 Statistics with SUMIF and COUNTIF 5.4 The SUMPRODUCT function 5.1 Locking a sheet with the menu 5.2 Leaving cells free to edit 5.3 Locking formulas with matrix 5.6 Target value and data table 5.1 The target value 5.2 Data table Workshop 1 : A single-entry data table Workshop 2 : A double-entry data table CHAPTER 6 Pivot tables 6.1 The structure of the source table 6.1 Some structural constraints 6.2 Creating a Pivot Table 6.3 Designing a Pivot Table 6.4 Refreshing the PivotTable 6.5 Counting with a Pivot Table 6.6 Viewing the rows at the source of a result 6.2 Calculation functions INTRODUCTION Excel is a spreadsheet i. a calculation software that adapts to the different business lines of any company.
To make a comparison with the world of medicine, we could say that Excel is a generalist program rather than a specialised one in a particular eld. Indeed, Excel allows you to work with numbers such as turnover, amounts or quantities, but also with dates. In addition it is easy to manipulate string characters such as codes or groups of letters. Lots of functions are available to manipulate dates, numbers and texts.
The challenge is to learn how to use and memorize them. Sometimes specialized knowledge is required to perform very complicated calculations. Excel can help building engineers or architects who use complex formulas to nd out strength of materials or other designs. Powerful statistics functions may be used to analyze data.
But Excel is able to adapt to your needs whether you are a professional or a student. This book is organized in two parts. The rst part (chapters 1 and 2) is a refresher course about Excel fundamentals. It is intended for people who work with the spreadsheet occasionally without real training on the subject.
The aim is to review the Excel basics and carrying out formulas. You will study how to perform tests between cells with the IF function. The date formulas, the $ sign and the text manipulation will be described with lots of examples. Special attention will be paid to the notion of cells coloring thanks to the very powerful feature called "Conditional Formatting".
This tool allows you to color values according to the content of a cell. For instance, if a cell contains the word "paid", then the line is automatically colored in red. Filters will be studied because they represent a signi cant part of the daily work. Indeed, it is often necessary to groups lines according to criteria.
You will also enjoy the right-click that provides a contextual menu and saves a lot of time. Layouts will not be forgotten because it requires ne adjustments for printing. This part ends with the setting up of several interesting charts that can be handled with Excel. The second part (chapters 4, 5 and 6) is directed to people who already have an Excel experience.
It contains more powerful functions that can offer services for data management. You will discover the searching functions as SEARCH and INDEX/EQUIV. They are the basis for grouping data scattered in several sheets. The functions SUMIF and COUNTIF will show their power in calculations with logical conditions.
A small tour with SUMPRODUCT will show an original way of adding values with date conditions. You will be able to study the locking of formulas with the matrix mode and the Excel menus. For managers who like to carry out hypotheses and commercial prospective, the Target Value and Data Tables features will be real assets. Finally, the last chapter of this book will focus on Pivot Tables that are extraordinary tools for creating statistical reports.
This book is structured in six chapters. Chapter 1 refreshes knowledge in Excel in order to calculate properly. Chapter 2 studies conditional formatting with its highlighting rules, data bars and many other features. Chapter 3 looks at the formatting techniques to print data easily.
You will also study the lters. Chapter 4 deals with several charts : histograms, lines, trends, pie, bubble, map and others. Chapter 5 looks at advanced functions that provide multiple services. Chapter 6 presents the Pivot Tables where examples will show the power of this feature.
I hope that this book will allow you to progress with Excel and its numerous functions. Please do not hesitate to contact me at the address REMYLENT@GMAIL.COM if you have any comments about this book. Enjoy your reading. The author CHAPTER 1 Calculating rightly Excel is usualy dedicated to calculations and thanks to its various functions and formulas, it renders countless services.1 The working environment and formulas Excel provides several menus and icons bars to work with.
A set of sheets named Sheet1, Sheet2, Sheet3, etc are available. These sheets (also called tabs) can be renamed with the right-click. Each time Excel is started, a blank workbook with 3 sheets opens. You can switch from one sheet to another by simply clicking on the sheet name.1 shows the menu.1 : The Excel menu Figure 1.2 shows the working environment.2 : The working window.
A sheet has 1,048,576 rows and the last column is XFD. The columns are labelled from left to right and arranged alphabetically: A, B, C,.Z, AA, AB, AC,. The address A3 is the intersection of the srt column and the third row. The address A6:A10 is a range of cells from A6 to A10.
A cell can receive a numerical value, a text, a date and the the result of a formula. You can insert a comment (right-click) to describe its content. The comment can be deleted at any time. When you enter a value in a cell, you can change it either by double- clicking it or by pressing the F2 key.
A formula always starts with the = sign or the + sign. You could write in the cell A1 the simple formula =56*2 that would make Excel look like a nice calculator. But Excel is structured to calculate with cell addresses rather than with simple values, like =A3+A9 or =(C5-C8)/F6*2%. In a formula, you can mix the operation signs at will, parentheses, the % sign as like in mathematics.
To write a formula, you should manipulate the pointeur. It helps to create the different addresses. After the = sign, addresses will be displayed automatically. Don't forget to nish the formula with the Enter key.
When you enter a formula, it is also displayed in the formula bar. A small green sign con rms the entry, while the red cross cancels it. Caution: When you write calculations that depends on several formulas, any change in a parameter automatically triggers the recalculation of all the formulas. This is the main principle of the spreadsheet.
Let's see some examples of calculation.1 Simple calculations Figure 1.3 shows a table with months and sales revenues.3 : Sales revenues To see all the formulas, perform the below procedure: Formulas / Show formulas Figure 1.4 : Formulas in the sheet To review the values, click again on the Show formulas icon. To save time, you can copy-paste one formula with the copy handle as shown in gure 1.5 : Copying formulas The following expressions shows how to perform totals: =sum(B2:B6). Displays the total of the revenues for 2017. Displays the average of the revenues for 2018.
Calculates the sum of the sales for the two years. A semicolon separates the two ranges of cells.6 shows these formulas.2 Workshop : a body mass index calculation Objective: Calculating your BMI (Body Mass Index) and displaying the conclusion, using a LookUp function that handles ranges of values. The formula for the calculation is: BMI = weight/(size * size) Figure 1.7 : BMI calculation The weight and size data are distributed in cells B3 and B4 respectively. Cell B5 contains the formula for the calculation of the BMI taking the values addresses into account.
Cell B6 contains the formula for searching the conclusion. The formula =LOOKUP(B5; B9:B15; C9:C15) tells Excel to nd where stands the B5 value in the range B9:B15 then nd the match in the other range C9:C15. Because the value 28 is between 25 and 30, the result is "Overweight". If you change the weight or the size, the calculations will be redone immediately.
Another formula can be written: =LOOKUP(B5;B:B;C:C) In this case, Excel considers the values to search in both columns B and C.3 Some reminders about the formulas B7. It indicates the intersection of column B and row 7. It means the block of cells from A1 to B12. It displays the total of the two cells A1 and B2.
As many operators as required can be used. Addition of the group of cells from A1 to A4. Sum of the cells group A1 to B7 and cell K9. The semicolon is an "and".
It calculates the average of the range of cells from N1 to N8. It refers to the maximum value of the cell group from F1 to F6. It refers to the minimum value of the cell group from F1 to F6. Counts the number of times the word "YES" appears in the range of A1 to A7.
Counts the number of times the word "NOP" appears in column A. Counts the number of times the word "OK" appears in column in columns A to F.