… or BDD Excel for noobs
Since the database is a super tool, offering remarkable possibilities, it is normal to find on the Internet a lot of tutorials to get started with databases on Excel, For example
https://www.cours-gratuit.com/tutoriel-excel/tutoriel-excel-creer-une-base-de-donnees
Curiously, CCM's “Practical” section did not offer any, until now.
To react to this shortcoming, we will try to write some technical sheets, intended for beginners on Excel, and for those who have been put off by explanations that are too "academic" or too theoretical. Let's take our first steps together ... 
Definitions in Excel
First of all, you have to agree on the vocabulary…
Spreadsheet
Your Excel file is a binder which, at its creation, includes 3 leaves of calculation, whose names are registered in the tabs of each sheet (bottom).
Each sheet contains cell ; the cell is the basic element of the leaf; she receives the data (fixed and variable) and the formulas, and displays the results operations, respecting certain formats (decimals, percentages, date, time, font color, fill color, bold, italics, underline ...).
A cell sits at the intersection of a line (1, 2, 3…) and a column (A, B, C…).
Current versions of Excel offer 16 columns and 384 rows, which allows the use of more than 1 billion cells per sheet!
Several cells can be brought together beach.
All the cells appearing in the sheet are grouped into one or more paintings.
Data base
A spreadsheet can be used for many purposes, as you may not know:
display an image or a graph, present a menu or a concert program, establish a list of commissions, draw a schedule, set up calculation tables for payroll or retirement simulations, etc.
We will focus here on a very specific type of sheet, the structure of which will allow it to be manipulated much like the tables Access (Microsoft Office) or Basic (Open / Libre Office), by easily performing sorting, filters, searches, personalized displays and pivot tables, in particular.
Rather than rows and columns, we will speak here ofrecordings and champs.
Definition of a database
It's relatively simple, but these rules must be respected for the table to be recognized by Excel as being a database:
- No row or column empty
- No cells merged
- The first line reserved for the title of the columns, i.e. the names of champs.
- The first column reserved for the heading of the lines, i.e. the names of the recordings.
- No duplicate is not allowed in field names.
- It is strongly recommended that you do not have duplicates in record names either. We often have to create a first column of key, if no field without duplicate is available (reference, registration, etc.)
- Everything must be done not to assign two spellings different to the same data: St-Pierre, Saint Pierre and Saint-Pierre are 3 distinct data. The copy paste and especially the cell drop-down list will therefore be preferred.
BDD example
This is not a data table, but an ordinary spreadsheet with several independent tables ...
Here is a database, which respects all the rules:
https://www.cjoint.com/c/JFnwukqCxgw
to be continued ...
- The following practical sheet will deal with the manipulations that take place directly in a database: Sort, filter, search.
- A subsequent practical sheet will deal with PivotTables and custom displays, and the use of cell drop-down lists in a database.
