HIGHTECHLEARNING

Vocational training center accredited by the state under No. 11134914

Excel Training
Advanced

Hosted by: Mohamed Rjoubi

High Tech Learning–H.T.L

Versatile Training Center under No. 11134914
Address (Tunis): 83 Avenue 20 March, Bardo, Tunis
Tél: +216-98-128-968, GSM : +216-53-025-150, E-mail : [Link]@[Link]
Table of contents
General presentatione ................................................................................................................1
Presentation of the interface..........................................................................................................2
The spreadsheetl ..................................................................................................................2
Binder concept..................................................................................................................3
The "ribbon" ...........................................................................................................................3
Use of the mouse.............................................................................................................3
Keyboard usage................................................................................................................5
First table..........................................................................................................................7
Cell formatting ..........................................................................................................8
Page layout and print control.................9
The reference systems .10
Absolute referencess10
Relative referencess10
Fix the reference to a row or a column when copying a formulas ....................10
Functions............................................................................................................................11
The Round function ...............................................................................................................11
Statistical functions......................................................................................................11
Logical functionss ..........................................................................................................13
Functions for testing the cell content type ......................................14
The IF function .......................................................................................................................14
The [Link] function ..........................................................................................................18
The SumProduct function .......................................................................................................18
Date functions and time managements .19
Text functions...........................................................................................................21
Searching for values in an array..............................................................................23
Graphics ..............................................................................................................................26
Structure of a table..........................................................................................................26
Data representations ................................................................................................26
Construction of the graph....................................................................................................26
Modification of the graph.....................................................................................................27
Examples of graphs ......................................................................................................29
Databases ...................................................................................................................32
Sort the data
Filter the datas ................................................................................................................32
Use a criteria zone .................................................................................................34
Remove duplicatess .35
Plans and subtotals...............................................................................................................35
Database functionss ...........................................................................................36
Consolidate datas .38
Pivot table .......................................................................................................39
Data source ..............................................................................................................39
Build the table ............................................................................................................39
Grouping by periods .41
Dynamic cross charte .41
VBA Environment.................................................................................................................42
Appendix.....................................................................................................................................46

High Tech Learning–H.T.L

Multipurpose Training Center under No. 11134914
Adresse (Tunis) : 83 Avenue 20 mars, Bardo, Tunis
Tél: +216-98-128-968, GSM : +216-53-025-150, E-mail : [Link]@[Link]
General presentation

Excel is a calculation tool; it includes many built-in functions (scientific,

financial, statistical, etc.) and is a favorable substitute for a calculator.
Excel permet d’élaborer des modèles de calcul, et de les réutiliser.
Excel is a data presentation tool, in tables (it is a spreadsheet).
Excel is a graphical data presentation tool (it is a graphing tool).
Excel is a data management tool: it allows you to manage a local 'database' and
to perform sorting, selection, duplication removal, and extraction operations
data. Excel also allows connection to external databases. Excel has
of specific statistical functions for analyzing data from a database.
The pivot table function allows for data analysis across different axes or
analysis dimensions, to present different 'views' and to develop reports or statements
synthetic statistics.

Excel Course Support 1

Presentation of the interface
The spreadsheet
A spreadsheet includes:
14
–16 384 (2 columns, numbered from A to XFD ;
20
-1,048,576 (2 lines, numbered from 1 to 1,048,576.
There are about 17 billion cells (17,179,869,184 exactly), from cell A1 to cell
XFD1048576.

When working on large spreadsheets, it is possible to 'split' the view;

each part of the sheet is then displayed in an independent pane:

Excel Course Support 2

Binder concept
A single Excel file can contain multiple worksheets gathered in the same
"binder". The controls for managing the sheets are grouped at the bottom left. A
button allows you to create a new sheet. The sheets (named Sheet1, Sheet2, Sheet3, etc. by
defects) can be renamed (right click). The sheets are accessible through a
tab. You can reorganize the sheets by dragging the tabs. When a workbook includes a
large number of sheets, we can scroll through the tabs using the "buttons of
video recorder

The "ribbon"
Since Office 2007, a 'ribbon' replaces toolbars and menus; the most functions
tools are grouped into tabs (Home, Insert, Page Layout, Formulas, Data,
Revision, Display and Supplements.

Use of the mouse

Select a cell: click in the center of the cell; drag to extend the selection.

Select a column: click on the column header; drag to extend the selection.

Excel Course Support 3

Select a row: click on the row header; drag to extend the selection.

Sélectionner tout : cliquer à l’intersection des en-têtes de lignes et de colonnes.

Copy a cell: click on the fill handle and drag down or to the right. Hold the
Pressing Ctrl allows you to copy values with or without incrementing. Double-click on the
copying point to copy down in an already filled table.

Move a cell or range of cells: select the cell or range of cells, click
on the contour of the selection and drag.

Excel course materials 4

Resize the columns or rows: click between the columns or rows (in the areas
(headers) and drag; double-click to fit to content.

Insert a row or a column: select the row or column, right-click and Insert.
Select multiple rows or columns to insert multiple rows or columns.

Use of the keyboard

Moving around the table
Ctrl + → Last column
Ctrl + ← First column
Ctrl + ↓ Last line
Ctrl + ↑ First line
Ctrl + Start First cell
Ctrl + End Last active cell

Editing touches
Ctrl+ C Copier
Ctrl+ X To cut
Ctrl+ V Paste
Ctrl+Z Cancel

Copy
Ctrl + B Copy down
Ctrl+ D Copy to the right

Excel course support 5

Select
Ctrl + Space Current column
May + Space Current line
Ctrl + A Select all

Extend the selection

May + Click
Maj + → ↓ ↑ ←
Ctrl + Maj+→ ↓ ↑ ←

Add to selection
Ctrl + Click

Function keys
Entry Validate
F1 Help
May + F2 Add / Edit a comment
May + F3 Insert a function
F4 Repeat the previous action
F5 Show the dialog box Reach
Maj + F5 Display the dialog box Search and Replace
1
Ctrl + Maj + & Display the cell format dialog box

Excel course support 6

First table
The following table is to be created:
A B C D
1 Designation PU Qty Amount excluding tax

2 Office 250,00 € 1 250,00 €

3 Armchair 150,00 € 1 150,00 €
4 Lamp 50,00 € 1 50,00 €
5 Telephone 65,00 € 1 65,00 €
6 Computer 850,00 € 1 850,00 €
7 Printer 450,00 € 1 €450.00
8 90 g paper ream 15,00 € 5 75,00 €
9 Ink cartridge 55,00 € 2 110,00 €
10 USB drives 22.00 € 2 44,00 €
11 Agenda 18,00 € 1 18,00 €
12 Style 1.00 € 5 5,00 €
13 Gum €1.50 1 1,50 €
14 Scissors 2,50 € 1 2.50 €
15 Hill €0.75 3 2,25 €
16 Adhesive tape 0,95 € 3 2,85 €
17
18 Total HT 2 076,10 €
19 VAT 19.60% 406,92 €
20 Including tax 2 483,02 €
Amount excluding tax

In D2, enter the formula: =B2*C2 and copy it down (from D2 to D16).
You can use the arithmetic operators '+' (plus), '-' (minus), '*' (multiplied by) and
« / » (divisé par) et le symbole « ^ » (élévation à la puissance) dans les formules.
Total HT
In D18, enter the formula: =SUM(D2:D16).
SUM is a built-in function; to insert a function:
– to the left of the formula bar: click on surfx(insert a function), select the function, and
enter the arguments of this function;
– or: type "=" and select the function from the dropdown list Functions, to the left of the
formula bar. The list includes the 10 most recently used functions; if the desired function
is not listed in the selectedOtherfunctions...
– or: type the function name directly in the formula bar while respecting the
indicated syntax.
VAT and Total Amount Including Tax

In D19 (VAT) enter the formula =D18*C19.

In D20 (Total Amount including tax), enter the formula = D18+D19.

Excel course support 7

Cell formatting
This dialog box provides access to all cell formatting options (formats of
names, content alignment, fonts, borders, padding) as well as options
cell protection.

Excel course materials 8

Layout and print control
The ribbon layout gives access to the main layout and control functions
Printing, as well as in the Page Setup dialog box. You will find specific options there.
allowing in particular to automatically adjust the dimensions of the table to that of the sheet, and
to center the table on the page. For large tables, it will be possible to
define the print order of the pages, as well as, if applicable, the lines to repeat at the top and/or the
columns to repeat on the left.

In the Layout ribbon, buttons allow you to define the print area and insert
manually page breaks at the desired lines and/or columns.

Excel course support 9

The reference system
Absolute references
By default, cell references are noted in the form of absolute references (A1, B1,
C1… A2, B2, C2, etc.) :
A B C D
1 Designation PU Qty Amount
2 Apple 1.95 € 1 =B2*C2
3 Pear 2,35 € 2 =B3*C3
4 Apricot 3,25 € 1 =B4*C4
5
6 Total =SUM(C2:C4) =SUM(D2:D4)

Note: When copying down or to the right, Excel automatically increments the
row numbers and column letters in cell references.

Relative references
To display cell references in the form of relative references, you need to switch to mode
display "L1C1":
File: Options: Formulas: Formula manipulation. Check the L1C1 reference style box.
We then obtain:
A B C D
1 Designation PU Qty Amount
2 Apple 1,95 € 1 =LC(-2)*LC(-1)
3 Pear 2,35 € 2 LC(-2)*LC(-1)
4 Apricot 3,25 € 1 =LC(-2)*LC(-1)
5
6 Total =SUM(L(-4)C:L(-2)C) =SUM(L(-4)C:L(-2)C)

Fix the reference to a row or a column when copying formulas

To freeze the reference to a row (when copying down) or to a column (when copying
to the right), you need to insert the "$" character before the line number or the column letter.
To freeze the reference to both a row and a column (when copying down and across)
right), you need to insert the character "$" before the line number and the column number.
A B C C
1 Owner Surface Charges Charges
2 Peter Dupont 30 m2 3 000,00 € =C$6/SUM(B$2:B$4)*C2
3 Pauline Carton 90 m2 9 000,00 € =C$6/SUM(B$2:B$4)*C3
4 Jean Aymar 60 m2 6 000,00 € =C$6/SUM(B$2:B$4)*C4
5
6 Total amount of charges to be allocated 18 000,00 €

Tip: press the F4 key one or more times to place the character "$" in front of the
line number or column letter:
C6F4$C$6F4C$6F4$C2F4C6
In L1C1 display mode, we would obtain:
A B C C
1 Owner Surface Charges Charges
2 Peter Dupont 30 m2 3 000,00 € =L6C/SUM(L2C(-1):L4C(-1))*LC(-1)
3 Pauline Carton 90 m2 9 000,00 € =L6C/SUM(L2C(-1):L4C(-1))*LC(-1)
4 Jean Aymar 60 m2 6 000,00 € =L6C/SUM(L2C(-1):L4C(-1))*LC(-1)
5
6 Total amount of charges to be distributed 35 000,00 €

Excel course support 10

The functions
The Round Function
Formulas Result Let's apply a monetary format
A B C C A B C
1 Amount excluding tax VAT VAT 1 Amount excluding tax VAT
2 6 = B2 * 19.6% 1,176 2 6,00 € 1,18 €
3 5.4 = B3 * 19.6% 1.0584 3 5,40 € 1,06 €
4 Total 11.4 = SUM(C2:C3) 2.2344 4 Total 11,40 € 2,23 €

We get: 1.18 + 1.06 = 2.23 (instead of 2.24!)

To round the results to two decimal places, use the ROUND function!
Formulas Result
A B C A B C
1 Amount Excluding Tax VAT 1 Amount excl. tax VAT
2 6.00 € =ROUND(B2*19.6%;2) 2 6,00€ 1,18 €
3 5.40 € =ROUND(B3*19.6%;2) 3 5,40 € 1,06 €
4 Total 11.40 € =SUM(C2:C3) 4 Total 11.40 € 2,24 €

We now get: 1.18 + 1.06 = 2.24

Statistical Functions
The functions Sum, Average, Count, and CountA
SOME Sum of a series of numerical values contained in one [or more] ranges
of cells.
AVERAGE Arithmetic mean of a series of numerical values contained in a [or
several cell ranges.
NB Number of cells containing a numeric value in one (or more) ranges
of cells.
NBVAL Number of non-empty cells (which can contain text or a numeric value)
in one [or several] ranges of cells.
Syntax: FUNCTION (range1; [range2]; [range3]; etc...).
The following formulas are equivalent:
AVERAGE(range) = SUM(range) / COUNT(range)
Always use the AVERAGE function to perform an average calculation!

The Minimum and Maximum functions

MIN Minimum value of a series of numeric values contained in a [or
several cell ranges.
MAX Maximum value of a series of numerical values contained in a [or
several) ranges of cells.

Excel course materials 11

The functions Mean, Variance, and Standard Deviation

The standard deviation is an indicator of dispersion; it expresses the typical deviation of values in a series.
at the average of these values: the lower it is, the more homogeneous the population is—and the higher it is,
the more heterogeneous the population is. The standard deviation is defined as being equal to the square root of the
variance.
Mathematical definitions:

Average Variance Standard deviation

ni xi ni xi 2
mx Vx  m2x σx Vx
n n

Application :
A B C D A B C D
Age Effective 2 Age Effective
1 xi ni ni× xI ni× xi 1 xI ni nI× xi ni× xi2
2 17 1 17 289 2 17 1=B3*A3 =B3*A3^2
3 18 3 54 972 3 18 3… …
4 19 3 57 1083 4 19 3... …
5 20 2 40 800 5 20 2... …
6 21 2 42 882 6 21 2... …
7 7
8 Sum 11 210 4026 8 Sum=SUM(B2:B6) … …
9 9
10 Average 19.09 10 Average = C8/B8
11 Variance 1.54 11 Variance=D8/B8-B10^2
12 Standard deviation1.24 12 Standard deviation = SQRT(B11)

We have the built-in functions VAR.P.N (variance) and [Link] (standard deviation); that is
the following series:
A B C
1 xi xi 2
2 15 225
3 8 64
4 16 256
5 5 25
6 13 169
7 18 324
8 8 64
9 12 144
10 12144
11 8 64
12
13 Average 11.50
14 Variance15.65
Standard deviation 3.96
The following formulas are equivalent:

Variance Standard deviation

=AVERAGE(C2:C11)-AVERAGE(B2:B11)^2 =SQRT(B15)

=VAR.P.N(B2:B11) =STDEV.P(B2:B11)

Median, quartiles, deciles and percentiles

The median, quartiles, deciles, and percentiles are indicators of distribution of the
population (distribution of numbers).

Excel course support 12

The median (not to be confused with the average) is the value that separates the population into two:
– those who are above this value (50% of the workforce);
– and the one below (50% of the workforce).
A population is evenly distributed in:
– four quartiles each comprising 25% of the population;
– ten deciles each comprising 10% of the population;
– hundred percentiles, each comprising 1% of the population;
Given a data series, we can use the following functions:
Minimum =MIN(series)
Maximum =MAX(series)
Median MEDIAN(series)
Minimum QUARTILE(series;0)
1erquartile =QUARTILE(series;1)
Median (2equartile) =QUARTILE(series;2)
3andquartile =QUARTILE(series;3)
Maximum =QUARTILE(series;4)
Minimum =PERCENTILE(array;0%)
1ercentile =PERCENTILE(series;1%)
1erdecile (10e percentile) =PERCENTILE(series;10%)
1erquartile (25ecentile) =CENTILE(series;25%)
Median (50ecentile) =CENTILE(series;50%)
3andquartile (75epercentile) =PERCENTILE(series;75%)
9edecile (90epercentile) =PERCENTILE(series;90%)
99ecentile =PERCENTILE(series;99%)
Maximum =PERCENTILE(series;100%)

Logical functions
Excel reconnait les valeurs logiques VRAI et FAUX. On dispose également des opérateurs logiques
AND, OR and NOT in the form of functions.
Let there be two propositions P1and P2we can create the following truth table:

A B C D E
1 P1 P2 NON P1 P1ET P2 P1OR P2
2 FALSE FALSE =NOT(A2) =AND(A2,B2) =OR(A2,B2)
3 FALSE TRUE =NOT(A3) =AND(A3,B2) =OR(A3,B2)
4 TRUE FALSE =NOT(A4) =AND(A4,B2) OR(A4, B2)
5 TRUE TRUE =NOT(A5) =AND(A5,B2) =OR(A5;B2)
We obtain the following result:
A B C D E
1 P1 P2 NON P1 P1ET P2 P1OR P2
2 FALSE FALSE TRUE FALSE FALSE
3 FALSE TRUE TRUE FALSE TRUE
4 TRUE FALSE FALSE FALSE TRUE
5 TRUE TRUE FALSE TRUE TRUE

Excel course support 13

Functions for testing the content type of cells
La fonction ESTVIDE permet de savoir si une cellule est vide ounon.
The function: allows to know if a cell contains:
THIS TEXT ................... a string
ESTNONTEXTE ........... another value than a string
ESTNUM ...................... a numerical value
ESTLOGIQUE .............. a logical value
These functions return the value TRUE or FALSE.
The TYPE function returns a number corresponding to the type of data contained in the cell:
1 for a numerical value
2 for a string
3 for a logical value
4 for an error
5 for a matrix
Error management
In case of an error in a formula or a calculation error, Excel returns an error code in the
cell
No. Example Error
1 =SUM($A$1 $A$2) #NUL!
2 undefined #DIV/0!
3 1 + "Toto" #VALUE!
4 =#REF! #REF!
5 Toto #NOM?
6 =SQUAREROOT(-1) #NAME!
7 =VLOOKUP("Toto", $A$1, 1, FALSE) #N/A
The [Link] function returns a number corresponding to an error number (or #N/A if there isn’t one).
there is no error). The ISERROR function returns the value TRUE in case of an error. The function
ESTERR returns TRUE in case of an error, except for #N/A. The ESTNA function returns the
TRUE value in case of error #N/A.
The SIERREUR function allows you to display a personalized message in case of an error.

The IF function
The IF function is used when a result is subject to one or more conditions.
Syntax: IF (condition; [value if true]; [value if false])
IF functions can be nested to express complex conditions.

Simple example
A discount of 5% is granted for any order amounting to 5% or more.
A B C
1 Montant avant remise Remise Montant après remise
2 1 057,85 € 52,89 € 1 004,96 €
3 609,51 € 0,00 € 609,51 €
4 1 434,12 € 71,71 € 1 362,41 €

Excel course support 14

Expression of the rule using a pseudo-language
IF Amount < 1000 THEN
No discount
SINON
Discount = Amount x 5%
FIN IF
Excel formula
Calculation of the discount amount (cell B2):
=IF(A2<1000,0,A2*5%)
We get 0 when the order amount is less than €1,000; to avoid displaying the
values 0 in a spreadsheet:
File: Options: Excel Options: Advanced Options: Display options for this worksheet
Do not check the box for the option 'Display a zero in cells that have a zero value'.
We could also have used the following formula:
=IF(A2<1000,"",A2*5%)
In this case, we get "" (empty string) when the order amount is
less than €1,000. But we get an error message "#VALUE!" for calculating the amount
after discount; indeed, it is not possible to subtract an empty string from a
name; to avoid this error, the formula to use for calculating the amount after discount
(cell C2) is:
=IF(B2="";A2;A2-B2)
To avoid displaying a zero when the amount before discount is not filled in, simply do
a test ; the formula becomes :
IF(ISBLANK(A2),"",IF(B2="",A2,A2-B2))

Example with nested IF functions

A discount is granted under the following conditions:

Order amount Amount of the discount

Order < 1000 € No discount

1000 <= Order < 2000 5% discount

2000 <= Order 10% discount

Expression of the rule using a pseudo-language

IF Amount < 1000 THEN
No discount
SINON
IF Amount < 2000 THEN
Discount = Amount x 5%
OTHERWISE
Discount = Amount x 10%
FIN IF
FIN IF
Excel formula
Calculation of the discount amount (cell B2):
IF(A2<1000, 0, IF(A2<2000, A2*5%, A2*10%))

Excel course support 15

Operating mode:

Logic Test A2<1000

Value_if_true 0
Value_if_false With the cursor in this area, click on IF at the top left to nest.
e
the 2 IF function.

Logical Test A2<2000

Value_if_true A2*5%
Value_if_false A2*10%
Click on OK
Note: Observe the progression of the entry in the formula bar.
One can nest as many IF functions as necessary, depending on the number of conditions:
– An IF function allows for handling two cases.
– two IF functions allow to handle three cases
– three IF functions allow to handle four cases
– etc.

Excel course support 16

Parameter table
The discount conditions (thresholds and rates) may vary, so it is strongly recommended to create a table.
of parameters, as below:
A B C
1 Order amount ThresholdDiscount rate
2 less than 1 000 € 0%
3 less than 2 000 € 5%
4 beyond 10%
In the formulas, we will refer to cells $B$2 and $B$3 for the threshold values, and to the
cells $C$2, $C$3, and $C$4 for the rate values; the spreadsheet will be easier to maintain
in case of variation of these values.

Excel Course Support 17

The [Link] function
Let the following table be:
A B C D
1 VAT
Designation Amount excluding tax
2 Rate Amount
3 Alice Mutton 195,00 € 19.6% 38,22 €
4 Aniseed Syrup 50,00 € 5.5% 2,75 €
5 Boston Crab Meat 92,00 € 19,6% 18,03 €
6 Camembert Pierrot 170,00 € 5.5% 9,35 €
7 Carnarvon Tigers 312,50 € 5.5% 17,19 €
8
9 VAT at 5.5% 29,29 €
10 VAT at 19.6% 56,25 €
The SUMIF function allows for conditional summation.
Syntaxe : [Link](plage ; critère ; [somme plage])
An example is more telling:
Total VAT at 5.5%: SUMIF(C3:C7, 5.5%, D3:D7)
Total VAT at 19.60%: =SUMIF(C3:C7;19.6%;D3:D7)
We sum the values in column D when the specified rate is found in column C.

The SumProduct function

Calculate the sum of the product of the values.
Consider the following table:
A B C
1 Designation PU Quantity
2 Alice Mutton 195,00 € 3
3 Aniseed Syrup 50,00 € 2
4 Boston Crab Meat 92.00 € 5
5 Camembert Pierrot 170,00 € 10
6 Carnarvon Tigers 312,50 € 4
7
8 Total amount 4,095.00 €
Total amount: =SUMPRODUCT(B2:B6;C2:C6)

Excel course support 18

Date functions and time management
The integrated calendar
Excel includes a built-in calendar for managing [Link] 1900 to
December 31, 9999. Each day corresponds to a serial number.
Number
Date
of series
1 01/01/1900
2 02/01/1900
3 03/01/1900
4 04/01/1900
5 05/01/1900

5928/02/1900
6029/02/1900
6101/03/1900

41 292 18/01/2013
41 293 19/01/2013
41 294 20/01/2013

2,958,465 31/12/9999
Hours correspond to a fraction of a day, and thus to a decimal number:
Number
Date and time
of series
41,293.00 01/19/2013 00:00 The day starts at midnight
41,293.25 01/19/2013 06:00 Six o'clock in the morning
41,293.50 01/19/2013 12:00 PM
41 293,75 19/01/2013 18:00 Dix-huit heures
To calculate the number of days that have passed between two dates, simply calculate the difference between them.
dates :
A B C A B C
Name Name
1 Start End 1 Beginning End
of days of days
2 01/01/2013 02/01/2013 1 2 01/01/2013 02/01/2013=B2-A2
3 01/06/2013 30/06/2013 29 3 01/06/2013 30/06/2013=B3-A3
4 01/01/2013 31/12/2013 364 4 01/01/2013 31/12/2013=B4-A4
N.B. : Pour afficher correctement le nombre de jours, appliquer un format « # ##0 », ou, le cas
# ##0.00

The format for displaying dates and times

Date
Format Examples
dd/mm/yyyy 01/01/2013 31/12/2013
j/m/aa 1/1/13 31/12/13
jjj j mmm aaaa March 1, 2013 Mar 31 Dec 2013
jjjj j mmmm aaaa mardi 1 janvier 2013 mardi 31 décembre 2013
Date and time
Format Example
dd/mm/yyyy hh:mm 19/01/2013 18:19
dd/mm/yyyy hh:mm:ss 19/01/2013 18:19:30
dd/mm/yyyy hh:mm:ss,00 19/01/2013 18:19:30,20

Excel Course Support 19

Time
Format Examples
h:mm 6:00 18:00
h:mm AM/PM 6:00 AM 6:00 PM
Duration
Format Example
[h]:mm 160:00

Date functions
Today and now
=TODAY() Returns the integer corresponding to the current day.
=NOW() Returns the decimal number corresponding to the current day and time.
Année, mois, jour, heure, minute, seconde
A B A B
1 Date 19/01/2013 18:55:13 1 Date 19/01/2013 18:55:13
2 Year 2013 2 Year =YEAR(B1)
3 Month 1 3 Month =MONTH(B1)
4 Day 19 4 Day =DAY(B1)
5 Time 18 5 Time =HOUR(B1)
6 Minute 55 6 Minute MINUTE(B1)
7 Second 13 7 Second =SECOND(B1)
Date
A B C D A B C D
1 AnnéeMois Jour Date 1 AnnéeMois Jour Date
2 2013 1 January 19, 2013 2 2013 1 19=DATE(A2);B2;C2)
Weather
A B C D A B C D
1 HourMinuteSecond Weather 1 HourMinuteSecond Weather
2 18 19 30 18:19:30 2 1819 30 =TIME(A2;B2;C2)
Number of working days
=NETWORKDAYS(start date; end date; [holidays])
start date Mandatory. Date that represents the start date.
end date Mandatory. Date that represents the end date.
public holidays Optional. Represents a range of optional dates to exclude from
calendar of working days, such as holidays or other days
contractually unemployed.
Day of the week
=WEEKDAY(date; [return type])
type return:
1 or returns a number between 1 (Sunday) and 7 (Saturday).
2 Renvoie un chiffre compris entre 1 (lundi) et 7 (dimanche).

Excel course material 20

Text functions
Character codes
CODE Returns the numeric code of the first character of the specified string.
Example: =CODE("A") returns 65.
CAR Returns the character corresponding to the specified numeric code.
Example: =CHAR(65) returns "A".

Number of characters in a string

NBCAR Returns the number of characters in the specified string.
Example: =LEN("Hello") returns 7.

Position of a character in a string

SEARCH and FIND
Returns the position, in a text, of the first character of the searched text, starting from the
starting position indicated. The SEARCH function is case insensitive (it does not make
no distinction between uppercase and lowercase letters); the FIND function is
case sensitive.
Syntax:
SEARCH(search_text, text, [start])
=FIND(search_text, text, [start])
Example: let the text 'Argentina;Brazil;Venezuela' in cell A1; let's search
the positions of the semicolon character in this text:
=SEARCH(";",A1) returns 10, which is the position of the first occurrence of
semicolon
=SEARCH(";",A1,SEARCH(";",A1)+1) returns 17, which is the position of the second
occurrence of the semicolon character.

Extraction of a string
LEFT and RIGHT
Returns respectively the first characters or the last characters of a
string.
Examples:
=LEFT("Hello", 3) returns "Hel";
=RIGHT("Hello",4) returns "day".
Argentina;Brazil;Venezuela
=LEFT(A1;SEARCH(";";A1)-1) returns "Argentina";
=RIGHT(A1,LEN(A1)-FIND(";";A1,FIND(";";A1)+1)) returns
Venezuela

Excel course support 21

STXT Allows you to extract a substring starting from the indicated starting position.
Syntax: MID(text, start, number of characters to extract)
Example: if the text is "Argentina;Brazil;Venezuela" in cell A1, the formula
following allows to extract "Brazil":
=MID(A1, 11, 6)
or, by looking for the position of the separators " ; " :
=MID(A1;SEARCH(";";A1)+1;SEARCH(";";A1;SEARCH(";";A1)+1)-
SEARCH(";",A1)-1)

String concatenation
CONCATENATE("Dupont", ", ", "Pierre") returns "Pierre, Dupont".
You can also use the character '&': ='Dupont'&', '&'Pierre' returns 'Dupont, Pierre'.

Conversion to uppercase and lowercase

=MAJUSCULE("Argentine, Brésil, Venezuela") renvoie
"ARGENTINA, BRAZIL, VENEZUELA";
argentine, brésil, venezuela
Argentina, Brazil, Venezuela
ARGENTINA, BRAZIL, VENEZUELA
Argentina, Brazil, Venezuela
return "Argentina, Brazil, Venezuela".

Text cleaning
PURGING Removes any control characters that may be present in a string
characters: carriage return, line break, etc.
SUPPRESPACE
Remove multiple spaces inside a text, as well as spaces at the beginning and end
at the end of the text.

Search and replace strings

SUBSTITUTE
Allows searching for all occurrences of a string in a text and
replace them with another; it is possible to indicate the starting position.
SEARCH
Allows replacing a string with another by specifying the position of
departure and the number of characters to replace.
We ask you to accept, Sir, the expression of our most distinguished salutations.
"distinguished" in cell A1:
=SUBSTITUE(A1,"Monsieur","Madame")
allows you to replace all occurrences of 'Monsieur' with 'Madame';
=REPLACE(Q1;SEARCH("Mr";A1);LEN("Mr");"Mrs")
allows replacing the first occurrence of 'Monsieur' with 'Madame'.

Excel Course Notes 22

Conversion of a number into text
CTXT Round a number to the specified number of decimals and return the result in the form
of text, with or without thousands separator.
Examples:
=CTXT(1234,56789;2) or CTXT(1234,56789;2;FALSE) returns 1,234.57 (with
thousands separator) ;
=CTXT($A36;$B36;TRUE) returns 1234.57 (without thousand separator).
TEXT Allows converting a number into text, in the specified format.
=TEXT(1234.56789;"# ##0.00") returns 1,234.57.
DEVISE Allows you to convert a number into text, in monetary format, with the number of
specified decimals.
Example: =CURRENCY(1234.56789;2) returns "1,234.57 €".

Conversion of a text into a number

CNUM allows you to convert a string representing a number into a value.
digital.
Example: CNUM("1 234.56 €") returns 1234.56.

String comparison
EXACT allows you to compare two text strings; it returns the logical value TRUE
when the two strings are identical, and FALSE otherwise.
Note: The comparison operator '=' can also be used.

The function 'T'

T Returns the content of a cell when it contains text (and nothing in the case
opposite).

The function 'repeat'

REPT can be used to repeat text (or a character) the specified number of times.
Example: =REPT("I will always be wise. ";10) returns "I will always be wise. I will
always wise. I will always be wise. I will always be wise. I will always be wise. I
I will always be wise. I will always be wise. I will always be wise. I will always
wise. I will always be wise.

Search for values in an array

The VLOOKUP function allows you to search for a value in the first column of a table.
and returns the content of the cell corresponding to the specified column.
Syntax:
VLOOKUP (value to search; table; column number; [approximate match])
close value:
TRUE or omitted Search for the closest value in the first column of the table, this
column is sorted in alphabetical order (from A to Z) or numeric ascending.
FALSE Look for the exact value; returns the error message "#N/A" in case of
unsuccessful search.

Excel course materials 23

Search for the exact value
Here is the following table, located on a 'Clients' sheet:
A B C D
1 Name Address Postal code City
2 Blondel father and son 24 Place Kléber 67000 Strasbourg
3 Enjoy your meal 12 Butchers Street 13008 Marseille
4 From all over the world 67 rue des Cinquante Otages 44000 Nantes
5 Gourmet Delights 184 chaussée de Tournai 59000 Lille
6 France restoration 54 Royal Street 44000 Nantes
7 The cornucopia 67 Avenue of Europe 78000 Versailles
8 The House of Asia 1 Alsace-Lorraine Street 31000 Toulouse
9 Paris specialties 265 Charonne Boulevard 75012 Paris
10 World Specialties 25 rue Lauriston 75016 Paris
11 Supplies in stock 2 Commerce Street 69004 Lyon
12 Wines and Spirits Chevalier 59 Abbey Street 51100 Reims
=VLOOKUP("The house of Asia", Clients!$A$2:$D$12, 2, FALSE) returns "1 Alsace-Lorraine Street"
=VLOOKUP("The House of Asia", Clients!$A$2:$D$12, 3, FALSE) returns "31000"
=VLOOKUP("The House of Asia", Clients!$A$2:$D$12, 4, FALSE) returns "Toulouse"

Example of search using a dropdown list

Here is the following table located on a sheet titled 'Research':
A B
1 Value sought: The House of Asia ▼
2 1 Alsace-Lorraine Street
3 31000 Toulouse
Cells B2 and B3 contain the following formulas:
B2 = VLOOKUP(B$1, Clients!$A$2:$D$12, 2, FALSE)
B3 =VLOOKUP(B$1, Clients!$A$2:$D$12, 3, FALSE)&" "
=VLOOKUP(B$1, Clients!$A$2:$D$12, 4, FALSE)
To display a drop-down list area in cell B1:
– Données : Outils de données : Validation des données
– Options : Critères de validation : Autoriser : Liste
– Source : =Clients!$A$2:$A$12
Note: We could also have assigned the name "client" to the range Clients!$A$2:$A$12 and used this name.
as the source of the list:
– Source : =client

Search for a nearby value

Here is the following table showing the departure times for São Paulo:
A B C D E
1 Départ Destination Volume Company Airport
2 10:30 São Paulo AF456 Air France Paris CDG
3 20:30 São Paulo JJ8101 Brazilian Airlines Paris CDG
4 23:30São Paulo AF454 Air France Paris CDG
Note: This table is sorted in ascending order of departure times.

Excel course materials 24

Looking for a flight around 12:00:
A
6 Value sought
7 12:00
Consider the following table:
A B C D E
9 Départ Destination Vol Company Airport
10 10:30 São Paulo AF456 Air France Paris CDG
Cells A10 to E10 contain the following formula:
=RECHERCHEV($A$7;$A$1:$E$4;COLONNE();VRAI)
Note: The COLUMN function returns the current column number.
Résultat : Comme il n’existe aucun vol à 12:00 ; le vol de 10:30 est proposé (valeur proche = VRAI).

Excel course support 25

Graphics
A graph is a representation of a data table; it is dynamically linked to it.
data source.

Structure of a table
A data table typically appears as follows:
A B C
1 Hommes Femmes
2 North 593 839
3 South 663 768
4 East 813 619
5 West 808 558
This table includes:
Column A: The categories 'North', 'South', 'East', and 'West'
Column B: The series 'Men' includes the following values: {593; 663; 813; 808}
Colonne C : Lasérie« Femmes » comprenant les valeurs suivantes : {839 ; 768 ; 619 ; 558}
Each row corresponds to a category and each column to a series of data. The first
column contains the titles of the categories; the first line contains the titles of the series.
The table could have been presented the other way around:
NorthSouth East West
Men 944 571 605 860
Women 983 783 788 994
In this case, the table would include:
– two categories: 'Men' and 'Women'
– quatre séries de données : « Nord », « Sud », « Est » et « Ouest »

Data representation
For a 'histogram' type graph:
– the categories are represented on the x-axis;
– each series is represented by a series of bars of the same color parallel to the axis of
ordinates;
– Each value of a series is represented by a bar proportional to that value.

Construction of the chart

To build a graph:
– Select the data table
– Insertion: Graphics
– Choose a model (histogram, curves, sectors, bars, areas, scatter plots, stock market)
surface, ring, bubbles, radar...) and click OK
Excel then automatically builds a 'standard' chart conforming to the selected template.

Excel course materials 26

Modification of the chart
By selecting the chart, we then have a 'Chart Tools' menu that includes the
the following elements:

– Creation
– Disposition
– Formatting

Creation

In the 'Data' section, we find:

– Invert the rows / columns
– Select data

Invert the rows / columns

Allows the interchange of series and categories and to modify the "direction" of the graph.

Select the data source

Allows controlling the association of the graph with the data, in particular:

– to redefine the data range of the chart (which corresponds in principle to the selection made
during the creation of the graph);
– to swap the series and categories to modify the 'direction' of the graph (button
Change line or column
– to redefine the range containing the list of categories ("Edit" button of "Axis Labels")
horizontal (abscissas) » ;
– to redefine the series ("Add", "Modify" and "Delete" buttons of "Entries of
legend (Series)
To add or modify a series, you must specify:
– the cell containing the name of the series;
– the range of cells containing the values of the series.

Excel course support 27

Layout and formatting

A graph is made up of different objects (chart area, titles, legend, plotting area,
axes, grid, data series, data labels, etc.
To modify the properties of an element, click on the element to select it, click
secondary, then "Format...". Or in the "Active selection" area, select the object
in the drop-down list area, then click on 'Format Selection'.

Format of the vertical axis

Notably, the following options are available:
– axis options: minimum, maximum, primary unit, secondary unit...
– number formats

Format of the horizontal axis

The following options can be found:
– axis options: interval between the graduations and the labels, unit of the interval, position of
the axis (on the gradations, or between the gradations) ...

– number formats (when it comes to numbers)

Formatting data series

In 'Series Options', we find in particular:
– the overlay options (separate series or overlap);
– the adjustment of the interval width between the series (more or less wide).

Secondary axis
By default, the series are represented on a single Y-axis; it is possible to
represent a series on a secondary axis. To do this, after selecting the series, in 'Format'
in the series of data / Series options" you will find the option "Plot the series with: Axis
secondary

Excel course support 28

Examples of graphs
Histograms and bars
Grouped histogram
Stacked histogram
1,200

2 000
1,000

800 1,500

600 Women
Men 1,000
Men
400
Women
500
200

0
0 North Sud East West
North Sud Est West

100% Stacked Histogram 3D Histogram

100%
90%
1,000
80%
70% 800
60% 600
Women Men
50% 400
40% Men Women
200
30%
20% 0 Women
10% North Men
South Is
0% West
North South Est West

Thousands 1,000
500 400 300 200 100 0 100 200 300 400 500
900
1909 1909
800
1919 1919
1929 1929 700
1939 1939 600
1949 1949
1959 1959 500
1969 1969 400
1979 1979
300
1989 1989
1999 1999 200
2009 2009 100
500 400 300 200 100 0 100 200 300 400 500
0
Women Men 2009 1999 1989 1979 1969 1959 1949 1939 1929 1919 1909

Curves

120

100

80

Series 1
60
Series 2
40

20

0
January February Mars April I June July August September October November December

Excel course support 29

Air

3,000 5,000

4,500
2,500
4,000

3 500 West
2,000
2013 3,000 Est
1 500 2012 2,500
South
2,000
2011 North
1 000
1,500
Centre
1,000
500
500
0 0
Center North South East West 2011 2012 2013

Sectors

2012
2011
Asia
32% Europe Europe
Asia
36% 36%
32%
29%
36%
2010

35%

United States
United States 32%
32%

Scatter and bubble chart

100 100

90 90

80 80

70 70

60 60

y 50 50

40 40

30 30

20 20

10 10

0 0
0 20 40 60 80 100 0 20 40 60 80 100
x

Stockholder

100,000 25
90,000 24
80,000 23
70,000 22
60,000 21
50,000 20
40,000 19
30,000 18
20,000 17
10,000 16
0 15
Monday Tuesday Wednesday Thursday Friday

Excel Course Support 30

Curve x, y

2
1.8
1.6
1.4
1,2
1
0.8
0.6
0.4
0.2
0
0 10 20 30 40 50 60 70 80 90 100

Radar

Bulbs Definition Photax

Jan.
10000 5
Seeds Photix
Dec. Feb.
8000 4
Flowers
6000 3
nov. Mars Trees and shrubs Speed Optics
4000 2

2000 1

Oct. 0 avg. 0

September I Respect for

Autofocus
colors

August June

July Sensitivity

Excel course support 31

Databases
For Excel, a database is a table that includes the list of fields in the first row.
Each column corresponds to a field, and each row to a record.
Note: The first row can be frozen to keep the display of the fields when scrolling.
vertically the table. The same goes for the first column, which can include, for example, a
registration identifier.

Sort the data

One criterion
Click on a cell in the column corresponding to the desired sorting criterion, for example, to sort
by country:
Number Date Commercial Client Country City Amount excluding tax

1 02/11/2006 Buchanan Wines and Spirits Chevalier France Reims 2 830,00 €

2 03/11/2006 Suyama Tom's Specialties Germany Münster 11 646,25 €
3 11/06/2006 Peacock Hanari Carnes Brazil Rio de 11,336.25
Janeiro €

– Data: Sort and filter

– Click on one of the "A" buttons Z » (increasing) or « Z A » (decreasing).

Several criteria
– Click on a cell in the table
– Data: Sort and filter
– Click on the 'Sort' button
– Check that the checkbox 'My data has headers' is checked
– Define, in order, the sorting criteria; for example:
Column Sort by Order
Sort by Country Values From A to Z
Then by City Values From A to Z
Then by Amount excluding tax Values From the largest to the smallest

You can use the buttons 'add a level', 'delete a level', 'copy a level'
et les flèches « déplacer vers le haut » et « déplacer vers le bas » pour modifier les critères de tri.

Sorting options
Respect case (yes / no): takes into account – or not – accented characters.
Orientation :
– From top to bottom: to sort the rows of the table (each row corresponding to a
recording in the database, and each column to a field
– From left to right: to sort a database where each record
would correspond to a column and each line to a field (non-conventional)

Filter the data

– Click on a cell of the table.
– In the 'Data' menu, click on the 'Filter' button (funnel).
– For each field, there is a dropdown area allowing to specify the
selection criteria.
– To remove the filter, click the "Filter" button again.

Excel course material 32

Selection of values using checkboxes
It is possible to select values using checkboxes.

Example for a field of type "text": Example for a field of type 'date':
(Select all) (Select all)
Germany 2008
January
Argentine 01
Austria 02
etc. 03
etc.

Custom filters
Depending on the type of data, there are numerical, chronological, or textual filters.
See the available options for the fields:
– Country or City (text filters)
– Amount excluding tax (digital filters)

– Date (chronological filters)

It is also possible to define custom filters:
– Text filters: Custom filter
– Digital filters: Custom filter
– Chronological filters: Custom filter
For each field, it is possible to specify two selection criteria and choose the operator.
logic (AND / OR).
For text fields, we can use placeholder characters:
– the generic character "?" replaces a character
– the wildcard character "*" replaces a string of characters (from 0 to n characters)
Select the customer orders where the country is 'Argentina' or 'Brazil':
Country
is equal to Argentina
ORis equal to Brazil
Select customer orders where the country name starts with 'A':
Country
starts with A
Germany and Austria.
On aurait pu indiquer :
Countries
is equal toA*
Select the orders from customers whose third letter of the country name is 'n':
Country
is equal to ??n*

We find: Canada, Denmark, Finland, and Venezuela.

Excel Course Support 33

Select all orders from January 2008:
Date
is greater than or equal to 01/01/2008
ETis less than 01/02/2008
Select all orders from January 2008, from German or Austrian customers,
where the amount is between €5,000 and €15,000:
Date Country Amount excluding tax

>= 01/01/2008 ET = Germany ET >= 5,000

ET < 01/02/2008 OU = Austria ET <= 15,000
When using multiple columns, the criteria are combined (AND operator between the columns).

Use a criteria area

Create a new sheet and name it "Criteria".
Create the following table:
A B C D E
1 Date Date Country Amount Excl. Tax Amount Excl. Tax
2 >=01/01/2008 <01/01/2009 Argentine greater than or<=2000
equal to 1000
3 >=01/01/2008 <01/01/2009 Brazil >=1000 <=2000
4 >=01/01/2008 <01/01/2009 Venezuela >=1000 <=2000
Note: The field titles must correspond to the field titles of the database.
By convention :
– operator "AND" between the columns
– operator "OR" between the lines
Reading the table:
Date Date Country Amount excluding tax Amount excluding tax

>=01/01/2008 ET <01/01/2009 ET Argentine ET>=1000 ET<=2000

OU>=01/01/2008ET<01/01/2009ETBrazil ET >= 1000 ET<=2000
OU>=01/01/2008 AND <01/01/2009 AND Venezuela AND >=1000 ET<=2000
The table is read line by line; the operator 'AND' takes precedence over the operator 'OR':
(date >= 01/01/2008 AND date < 01/01/2009 AND country = Argentina, etc.)
OU (date >= 01/01/2008ETdate <01/01/2009ETpays = Brésil, etc.)
OUetc.

Filter the database

Click in a cell of the database (sheet "Orders").
In the 'Data' menu, 'Sort and Filter', click on 'Advanced' and fill in the fields.
following :
– Action: Filter the list on site
– Ranges: $A$1:$G$831 (default selected range)
– Criteria area: select the criteria table in the 'Criteria' sheet
(Criteria!$A$1:$E$4)
Click on the OK button
The database is filtered; to remove the filter, in the 'Data' menu, 'Sort and Filter',
click on "Clear".

Excel course support 34

Extract the data from the database
Create a new sheet that you will name 'Result'.
Click on a cell in this new sheet that will become the 'active' sheet.
In the 'Data' menu, 'Sort and Filter', click on 'Advanced' and fill in the fields.
following:
– Action: Copy to another location
– Beaches: select the database (Orders!$A:$G)
– Criteria zone: select the criteria table in the 'Criteria' sheet
(Criteria!$A$1:$E$4)
– Copy to: select a cell in the destination sheet (Result!$A$1)
– To eliminate duplicates (if applicable), check the box "Extract from duplicate".
Click on the OK button

Remove duplicates
Copy the 'Orders' database to a new sheet.
Delete the columns "Number", "Date", "Salesperson", and "Amount excluding tax".
The database only contains the columns 'Client', 'Country', and 'City'; this database of
data contains duplicates, as a client may have placed multiple orders.
To remove duplicates:
– click on a cell in the table
– in the 'Data' menu, 'Data Tools', click on 'Remove Duplicates'
– Make sure the box "My data has headers" is checked.
– Check that the boxes for the columns 'Client', 'Country', and 'City' are checked.
– click on 'OK'
Excel displays the following message: '741 duplicate values found and removed. Remaining '
89 unique values.

Plan and subtotals

We want to know the revenue generated by country, then by city (subtotal of the amount excluding tax)
of orders).
Sort the database 'Orders' by country, then by city.

Subtotal by country
In the 'Data' menu, 'Plan', click on 'Subtotal'.
Please fill in the following information:
– At each change of: Country
– Use the function: Sum
– Add a subtotal to: Amount excluding tax
– Check the boxes "Replace existing subtotals" and "Summary under the data."
Click on 'OK'.
Excel creates a plan and inserts a subtotal line at each change of country; you can
Click on the buttons of the map (at the top and left) to display different levels of detail.

Excel course materials 35

Subtotal by country and city
To add a subtotal by city, in the "Data" menu, "Outline", click again on
"Subtotal" and fill in the following information:
– At each change of: City
– Use the function: Sum
– Add a subtotal to: Amount excluding tax
– Uncheck the box 'Replace existing subtotals.'
Click on 'OK'.
Excel inserts a subtotal row at each change of city; you can click on the
plan buttons (at the top and left) to display different levels of detail.

Remove subtotals
To remove subtotals, in the 'Data' menu, 'Outline', click on 'Subtotal', then
on "Delete all".

Manual creation of a plan

To create a plan manually, select the rows to group, and in the 'Data' menu,
"Plan", use the "Group" and "Ungroup" buttons.

Database functions
For example, consider the following database (located on a sheetOrders):
A B C D E F G
1 Number Date Commercial Client Country City Amount excluding tax

2 11627/03/2007 Peacock Dragon's Blood Delicacies Germany Aachen 2 795,00 €

3 April 4, 2007 Leverling Dragon's Blood Delicacies Germany Aachen 540,00 €
4 55024/04/2008 King Dragon's Blood Delicacies Germany Aachen 2 100,00 €
5 57809/05/2008 Davolio Dragon Blood Delicacies Germany Aachen 5 153,80 €
6 etc.
And the following criteria table (located on a Criteria sheet):
A B C
1 Date Date Country
2 >=01/01/2008 <01/01/2009 Argentine
3 >=01/01/2008 <01/01/2009 Brazil
4 >=01/01/2008 <01/01/2009 Venezuela
The criteria table reads as follows:
Date≥01/01/2008ETDate < 01/01/2009 ETPays =“Argentine”
OUDate≥01/01/2008ETDate < 01/01/2009ETPays = “Brésil”
OUDate≥01/01/2008ETDate < 01/01/2009ETPays = “Venezuela”
Reminder: the logical operator AND takes precedence over the logical operator OR.

Excel course support 36

The BdSum function
The BDSOMME function will allow you to know the revenue generated in 2008 in Argentina.
Brazil and Venezuela.
Syntax: =DSUM(database; field; criteria)
Arguments :
database The beach containing the database.
Here: Orders!$A:$G (columns A to G)
champ The cell containing the name of the field we want to sum, or the name of
field in quotation marks, or the column number in the database.
Here: Orders!$G:$1 (cell G1), or 'Amount Excl. VAT',
or 7 (the amount excluding tax is the 7th column of the database).
Criteria The range of cells containing the criteria
Here: Criteria!$A$1:$C$4 (from A1 to C4)

Other functions
The syntax is identical to the SUMIFS function.
For the indicated field:
BDNB Returns the number of cells containing a numeric value.
BDNBVAL Return the number of non-empty cells.
BDAVERAGE Return the average of the values.
BDMAX Return the maximum value.
BDMIN Return the maximum value.
BDVARP Return the variance of the series of values.
BDECARTYPEP Return the standard deviation of the series of values.

Excel course support 37

Consolidate data
Here are the following tables located on three spreadsheets:
Designation Amount Designation Amount Designation Amount
Rent 1 500,00 € Rent 1,500.00 € Rent 1 500,00 €
Insurances 100,00 € Assurances 100,00 € Assurances 100,00 €
Heating 25,00 € Telephone 10,00 € Gaz 12,55 €
Water 10,00 € Electricity 8.35 €
Gas 10,00 €
Electricity 10,00 €

Total 1 655,00€ Total 1 610,00 € Total 1 620,90 €

January February Mars

Data: Data Tools: Consolidate

Consolidate Dialog Box:

– select the function: Sum
– select the ranges to consolidate (click on Add)
– Labels in: check the box Left column
We obtain:
Designation
Rent 4,500.00 €
Insurance 300,00 €
Telephone 10,00 €
Heating 25,00 €
Water 10,00 €
Gas 22,55 €
Electricity 18,35 €
Total 4 885,90 €

Course support Excel 38

Pivot table
This very important feature of Excel allows you to develop report or statement templates.
Statistics. A pivot table is a state dynamically linked to a data source;
this data source can be an Excel table, but more often, it will be a source of
external data (database) which will be accessed via a 'driver' (ODBC)1by
example).

Data source
A data source is presented in the form of a table, each column corresponding to a
field, and each line has a record. By grouping identical values,
most fields can be used as an axis or dimension of analysis; thus we can
proceed with regrouping:
– chronological (by date, year, quarter, month, week, day of the week, time span of the day,
day, time of the day...
– geographical (by country, region, city...)
– by actor, external (client, supplier…), or internal (service, manager…)
– by type or category (of user, of product...)
– etc.
Les champs contenantdes « valeurs » (quantité vendue, montant hors taxes…), auxquelles on
will apply a statistical function (sum, count, average...), will be used as fields of
synthesis.
For example, consider the following database:
Date Country City Category Amount excluding tax

01/01/2013Allemagne Francfort Drinks 120 €

01/01/2013Autriche Vienne Condiments 90 €
01/01/2013Allemagne Berlin Desserts 85 €
01/01/2013Allemagne Stuttgart Desserts 110 €
01/01/2013Allemagne Berlin Drinks 75 €
01/01/2013Autriche Salzburg Condiments 80 €
…
Develop a cross table showing the turnover excluding taxes achieved by year, country, city and
product category.
The year, the country, the city, and the product category represent the different axes of analysis; the
The revenue generated will be calculated based on the net amount (Total of Net Amount).
Example of table layout:
– The year, calculated from the date, will be used as a 'filter' for the report;
– countries and cities will be listed online in the table;
– the product categories will be listed in columns;
– The revenue generated will appear at the intersection of each row and each column.

Build the table

To create a pivot table from an Excel table, click in a cell of the
table, then:
– Insertion : Tableaux : Insérer un tableau croisé dynamique

1
ODBC: Open Database Connectivity.

Excel course support 39

We get the following dialog box:

Create a pivot table

Choose the data to analyze

❑ Select a table or a range
Table / Range: [cell range containing the source data]
❑ Use an external data source
Choose the connection...

Connection name:
Choose the location of your pivot table report
❑ New spreadsheet
❑ Existing spreadsheet
Location:

OK| Annuler

On accède ensuite au volet permettant de construire le tableau ; il suffit faire glisser les champs
as indicated:

List of fields

Date
Country
City
Category
Amount excluding tax

Year

Report filter Column labels

Year Category

Line labels Values

Country Total Amount Excluding Tax

City

Note: The 'Year' field is a calculated field; to create it:

Pivot Table Tools: Options: Calculations: Fields, items, and sets: Calculated field…
Insertion of a calculated field
Nom : Année
=YEAR(Date)
Note: By clicking on the field labels in the Filter areas of the report, Column Labels,
Row labels, and Values, we access field settings; for the fields of
"values" we can notably choose the function to apply (sum, count, average, etc.) and the
number format.

Excel course support 40

We obtain the following result:
Year (All)

Category
Country / City
Beverages Condiments DessertsTotal
Germany 1,920 1,200 1,680 4,800
Berlin 1,400 875 1,225 3,500
Frankfurt 280 175 245 700
Stuttgart 240 150 210 600
Austria 740 463 6471 850
Salzburg 60 38 52 150
Vienna 680 425 5951 700
Total 2,660 1,663 2 327 6 650

Grouping by periods
For typedate fields, we have different grouping options by years, quarters,
mois, jours, nombre de jours, heures, minutes et secondes.
Example:
– Take the 'Date' field and place it as a row label.
– In the table, select a date.
– Secondary click: Group...
– Select Year and Month.
This results in a hierarchical list Year / Month.

Pivot chart
To insert a pivot chart from a pivot table:
– PivotTable Tools: Options: PivotChart
– Select a template and click OK
In pivot table tools, the tools are grouped in:
– Creation
– Disposition
– Formatting
– Analyze

Excel course support 41

VBA Environment
In this course, we will learn to program and test simple programming algorithms.
in Visual Basic language under Excel.

1–Simple VBA Form

Exercice : Tester la validité d’une note (entre 0 et 20)
In this example, we will enter a note and test its validity.
It must be between 0 and 20.
If it is outside of these bounds, we will display the message: Note
Incorrect, otherwise we will display the message. Correct note.

We are going to program the following form:

Subject: Zonenote
Objet : Bouton de commande
Subject: Result Display Area
Note:
A Visual Basic program in Excel is created as part of a form on which are
parametered the necessary objects for programming (See above).
•Visual Basic is an object-oriented programming language. As a result, there are as many
programs that objects present on the form. In this case, there are three programs that
concerning 3 objects.
The Zonenote objects and the display zone are input labels and text labels, their
settings mainly concern the name and formatting.
The control button contains the program lines corresponding to the processing to
to be carried out when activated.
The transcription of the algorithm will be done on the control button. In VBA, it will become the following:
Algorithm VBA Remark
The entered notes are by default in
Grade test algorithm Private Sub Test_Click() text mode.
To use them in calculations, it is necessary to
VAR Note: Simple Dim Note As Single to
préalable les convertir en nombre c’est
Start Note = CSng([Link]) the object of
Read (Note) If Note >= 0 And Note <= 20 Then the order:
If Note <= 0 and > +20 then [Link] = "Note correcte" Note = CSng([Link])
Incorrect note
» Otherwise
[Link] = "Note
Otherwise incorrecte"
Write 'Correct note' End If
End if End Sub
End

Procedure to be carried out in Excel:

1 - Launch Visual Basic Editor

Open Excel
Tools–Macro–Visual Basic Editor

2 - Create a new form

->Insertion-UserForm
A new form is
displayed as well as a box
tools
Properties window
Excel course support 42
3–Set up the form
31 - Expand the form
-> Click and drag the border of the form

32–Name the form

Click the form
-> Click on the property window line
(Name) and enter: Form_test_note(the
spaces are prohibited)

33–Form Title
-> Click the line Caption and enter Note

4–Create and configure the input area

-> Click the tool in the palette
Tools -> Draw the input area on
the form
Click on the line: (Name) and enter the name of the object:
Zone note
-> Click the lineFont and set it
font size to display in the
zone (12 Grass)

5–Create the command button

-> Click the tool

Draw the button on the form
-> Click the line: (Name) and enter the name of the object:
Test_button
Click the line: Caption and enter the text that will be
Displayed on the button: Test
-> Click the lineFont and configure it
button font size (16 Bold)

Excel Course Material 43

6–Create and configure the area
of displaying the result
-> Click the tool in the
tool palette -> Draw the
text zone on the
form
-> Click the line: (Name) and enter the name of the object:
Result
-> Click the lineFont and configure
the size of the characters to display
in the area (14 Italic bold)
-> Click the line Border Style
and set the border
framed
-> Click on the line Caption and delete
its content. The content of this
zone will be the one that will be the text to
display who will be set in the
control button frame.

7–Set up the control button

Double click the
control button
Enter the commands
next: -> Close the
setting window

8–Test the form

Execution - Execute Sub/User Form
Where
-[F5]
Enter a note and press
on the buttonTest=> The
result is displayed:

Excel course support 44

Test the operation with a score higher or lower
within accepted limits

The form is not working:

Check the field names
-Vérifier les noms des formulaires et des objets
Check that the names of the objects and their name in the program are identical.
Check the syntax of each command

9–Save the workbook

Excel course materials 45

 Annex

Some VBA Commands in Excel:

Sub properties()

change the contents of cells A1 and C1

Sheets("Ali").Range("A1,C1").Value = "tatttt"
Clear contents of columns B and F
Clear contents of range B:B,F:F
Display Sheet3 and hide value 0
Sheet("Sheet3").Visible = True
Change contents of cells A1:A5 to bold
Sheets("Imed").Range("a1:a5").[Link] = True
Highlight the content of cells E5 F1 and D3
Sheets('Sheet3').Range('E5,F1,D3').[Link] = True
Draw the border of the cells F5:F10
Sheets("Sheet4").Range("F5:F10").[Link] = 1
Change the name of the sheet Feuil2 to Imed
Workbooks("[Link]").Sheets("Sheet2").Name = "Imed"
Change the font size of the active cell to 36
[Link] = 36
Change the font to italic of the active selection of all the cells of the
active sheet
[Link] = True

End Sub

Excel course materials 46