Improve your skills with Google Sheets - Rémy Lentzner - E-Book

Improve your skills with Google Sheets E-Book

Rémy Lentzner

0,0

  • Herausgeber: Remylent
  • Sprache: Englisch
  • Veröffentlichungsjahr: 2020
Beschreibung

Welcome to Google Sheets, your free worksheet on line. This book outlines useful possibilities if you want to work with this application. As with any spreadsheet, you can achieve simple or more complex calculations using functions. You will learn how to filter and sort out information creating statistics with Pivot tables. You will define page layout settings to print data easily. Charts will facilitate data analysis. Because Google Sheets is designed for sharing data, forms will enable you to create online questionnaires or surveys to get to know your contacts better. You will create macros that automate keyboard or mouse actions.
This book will improve your knowledge about Google Sheets and how to use it in your work.

Sie lesen das E-Book in den Legimi-Apps auf:

Android
iOS
von Legimi
zertifizierten E-Readern
Kindle™-E-Readern
(für ausgewählte Pakete)

Seitenzahl: 61

Das E-Book (TTS) können Sie hören im Abo „Legimi Premium” in Legimi-Apps auf:

Android
iOS
Bewertungen
0,0
0
0
0
0
0
Mehr Informationen
Mehr Informationen
Legimi prüft nicht, ob Rezensionen von Nutzern stammen, die den betreffenden Titel tatsächlich gekauft oder gelesen/gehört haben. Wir entfernen aber gefälschte Rezensionen.



REMY LENTZNER

IMPROVE YOUR SKILLS WITH GOOGLE SHEETS

French original title : Google Sheets, le tableur en ligne

EDITIONS REMYLENT, Paris, 1ère édition, 2019

R.C.S. 399 397 892 Paris

25 rue de la Tour d’Auvergne - 75009 Paris

[email protected]

www.REMYLENT.FR

Google Sheets is a registered trademark of Google Inc.

ISBN EPUB : 978-2-490275-22-9

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 Isabelle, Anna, Hélène and Lucy.

I could not have written it without their support, advice, encouragements and proofreading.

Graphic illlustration : BRUNO CONQUET

INTRODUCTION

Welcome to Google Sheets, your free on line worksheet. This book outlines useful possibilities if you must work with this application. As with any spreadsheet, you can achieve simple or more complex calculations using functions.You will learn how to filter and sort out information creating statistics with Pivot tables. You will define page layout settings to print data easily. The graphs will facilitate data analysis. Because Google Sheets is designed for sharing data, forms will allow you to create online questionnaires or surveys to get to know your interlocutors better. You will insert complementary modules adding features, such as the SOLVER add-on which solves problems of calculation. Finally, you will create macros that automate keyboard or mouse actions.

This book is divided into 7 chapters :

Chapter 1 shows the online spreadsheet work environment and how to present, print, sort and filter all data. You will handle files with the DRIVE, a fundamental element of Google Sheets.

Chapter 2 looks at simple and more complex calculations that use functions. You will study classic functions such as SUM, AVERAGE, MAX, MIN, the operator $ that enables you to block cells when you copy a formula. You will appreciate the text functions UPPER, MID, LEFT, RIGHT that deal with characters.

You will discover the conditional functions such as functions IF, IFS, SWITCH, VLOOKUP, INDEX/EQUIV, SUMPROD, COUNTIF, SUMIF and finally the function IMPORTRANGE that facilitates the recovery of data from an external source. Other date functions will be outlined such as YEAR, MONTH, DAY, EMONTH and others.

Chapter 3 focuses more particularly on available graphs. You will discover SPARKLINE chart, LINE and TREND charts, PIE chart, GEO chart, WATERFALL chart, BAR and DOUGHTNUT charts, RADAR and TIMELINE charts, GAUGE and ORGANISATIONAL charts according to your needs.

Chapter 4 shows PivotTables that create statistical reports from data lists. Lots of sums and counts can be carried out according to many criteria.

Chapter 5 refers to FORMS that allows to create questionnaires or surveys on line. This feature enables you to study people answers. Information is stored in a worksheet with a special field that indicates the exact time-stamp of the answer.

Chapter 6 shows you how to insert complementary modules created by Google partners. You will study the complement SOLVER to solve complex problems.

Chapter 7 finishes this book describing how to create macros that automate the tasks, you carry out manually. The code created is based on Javascript language.

You can modify it in the Google Sheets development environment. In this programming context, you will be able to write your personalized re-using functions.

I hope this book will enable you to make progress with Google Sheets. If you have any remarks or questions, do not hesitate to contact me at [email protected]. I will not fail to answer you.

Enjoy your reading.

The author

TABLE OF CONTENTS

Chapter 1 : Fundamentals

1.1 Starting Google Sheets

1.2 Several checkboxes in a sheet

1.3 Save and move a file in the DRIVE

1.4 Data layout

1.4.1 How to increase text size

1.4.2 How to copy a lay out with the paint roller

1.4.3 How to border cells

1.4.4 How to align data

1.4.5 How to clear formatted data

1.4.6 Your best friend : the right button

1.5 Printing data

1.5.1 How to modify page scaling

1.5.2 How to modify margins

1.5.3 How to align a print table

1.5.4 How to define headers and footers

1.5.5 Repeating header row on all print pages

1.6 Conditional formatting

1.6.1 How to color all budgets > 2000

1.6.2 How to color an entire line for a budget > 2000

1.6.3 How to color the dates > now()

1.7 Filtering

1.7.1 Applying filters

1.7.2 Using filters

1.7.3 Remove filters

1.7.4 Creating a filter view

1.7.5 Using a filter view

1.8 The DRIVE

1.8.1 Opening a file stored on the DRIVE

1.8.2 Opening a file stored on the computer

1.8.3 Copying-pasting data from Excel to Google Sheets

1.8.4 Handling files in the DRIVE

1.9 Working with sheets (tab)

1.9.1 How to extract a sheet in a new file

1.9.2 How to modify tab color

1.9.3 Hiding tab and view again

1.9.4 Seeing a list of all tabs

Chapitre 2 : Calculation and Functions

2.1 Calculation

2.1.1 Functions SUM, MAX, MIN and AVERAGE

2.1.2 Where to find other functions

2.1.3 Using $ to block cells

2.1.4 Tips to copy a formula quickly

2.1.5 Inserting an image into a cell

2.2 Text functions

2.2.1 Where to find text functions

2.2.2 Commonly used functions

2.2.3 An odd date transformed into a valid date

2.2.4 Other date functions

2.3 Tests with IF function

2.4 IFS function

2.5 SWITCH function

2.6 Research functions

2.6.1 VLOOKUP function

2.6.2 Searching numbers in a scale

2.6.3 VLOOKUP with a joker

2.6.4 VLOOKUP with two conditions

2.6.5 INDEX/MATCH Functions

2.6.6 How to sort out a range of data

2.6.7 SUMPRODUCT Function

2.7 COUNTIF and SUMIF functions

2.7.1 Counting with conditions

2.7.2 Add up with SUMIF and conditions

2.8 Calculation with matrices

2.8.1 Creating a matrix calculation

2.8.2 Deleting a matrix calculation

2.8.3 Protecting cells

2.9 Data Validation

2.9.1 Creating a customized list

2.9.2 Controling a date entry

2.9.3 Controling a number entry

2.9.4 Using a custom formula such as Data Validation

2.9.5 Inserting tick boxes

2.10 Calculation between sheets and files

2.10.1 Calculation between sheets

2.10.2 Importing data from another file

Chapter 3 : Charts

3.1 Sparkline chart

3.2 Line chart

3.3 Pie chart

3.4 Geo chart

3.5 Waterfall chart

3.6 Bar chart

3.7 Doughtnut chart

3.8 Radar chart

3.9 Timeline chart

3.10 Gauge chart

3.11 Organisational chart

3.12 Smooth line chart

3.13 Trend line

3.14 Logarithmic scale

Chapter 4 : Pivot tables

4.1 What is the point of a Pivot table ?

4.2 How to create a Pivot Table

4.3 Calculation with Pivot table

4.3.1 Displaying an average of revenues

4.3.2 Calculation functions

4.3.3 How to display percentages (%)

4.3.4 Counting with items

4.3.5 Displaying details from a result

4.3.6 Calculated fields

4.3.7 Displaying dates in a Pivot table

Chapter 5 : Forms

5.1 Designing a form

5.1.1 Short answer

5.1.2 Paragraph

5.1.3 Multiple choice

5.1.4 Checkboxes

5.1.5 Drop-Down

5.1.6 File upload

5.1.7 Linear scale

5.1.8 Multiple-choice grid

5.1.9 Tick box grid

5.1.10 Date

5.1.11 Sending form and viewing answers

Chapter 6 : Templates and Solver Add-on

6.1 Existing templates

6.1.1 To-do list

6.1.2 Purchase order

6.1.3 Daily schedule

6.1.4 Monthly budget

6.2 Solver Add-ons

6.2.1 Using Solver Add-on

6.2.2 Using Solver with a single constraint

6.2.3 Using Solver with several constraints

6.2.4 Solver options

Chapter 7 : Macros

7.1 Creating macros

7.2 Executing a macro

7.3 Editing a macro

7.4 Information on Class Sheet