background top icon
background center wave icon
background filled rhombus icon
background two lines icon
background stroke rhombus icon
header logo icon

Download "Comment créer un tableau de bord des ventes sur Excel en 40mn ? Cours Excel complet tous niveaux"

input logo icon
"videoThumbnail Comment créer un tableau de bord des ventes sur Excel en 40mn ? Cours Excel complet tous niveaux
Table of contents
|

Table of contents

0:00
Introduction
0:55
La base de données
1:29
Préparation du tableau de bord
7:32
Utiliser des tableaux croisés dynamiques pour alimenter le tableau de bord
13:59
Dupliquer un TCD en une seconde
16:33
Utiliser les segments pour sélectionner les données à afficher
23:08
Afficher l'évolution comparative des ventes
27:58
Indicateur : TOP5 des commerciaux
31:34
Indicateur : TOP5 des produits vendus
32:33
Indicateur : Matrice des ventes trimestrielles par commercial
36:00
Contrôle du champ d'application des segments
Similar videos from our catalog
|

Similar videos from our catalog

Video tags
|

Video tags

excelformation.fr
formation excel
didacticiel excel
excel
tuto
cours
gratuit
formation
débutant
apprendre
tuto excel
apprendre excel
tutoriel
tableau de bord
tableau croisé dynamique
tcd
tableau croisé dynamique excel
tableau de bord des ventes
analyse des ventes
matrice
segments
excelformation
tableaudebord
Subtitles
|

Subtitles

subtitles menu arrow
  • ruRussian
Download
00:00:01
hello everyone it's Alex from Excel
00:00:03
formation.fr and in this video we
00:00:06
will see how to set up a
00:00:07
complete dashboard which will allow us
00:00:09
to visually and
00:00:11
summarize the data which
00:00:13
correspond to the sales of a company
00:00:14
for this we will start from a
00:00:16
database in which we have the
00:00:18
summary of all sales and
00:00:20
we will therefore want to generate this
00:00:22
dashboard directly from
00:00:23
the database we will also
00:00:25
want that this table is easily
00:00:27
modifiable using a series of
00:00:29
buttons and that it is animated,
00:00:31
that is to say that the values ​​react
00:00:33
according to what we select
00:00:34
and finally we will want to build
00:00:36
this dashboard without needing to
00:00:38
develop macro-commands in VBA
00:00:40
then you will find
00:00:41
new videos on the channel very regularly so
00:00:43
remember to subscribe and above all to
00:00:45
activate the notification bell
00:00:46
in order to be sure not to miss any
00:00:48
tricks and to quickly become a pro
00:00:50
Excel
00:00:53
[Music]
00:00:56
as we have just seen in
00:00:58
the introduction to this video we
00:01:00
will therefore see how to create a
00:01:01
dashboard here a dashboard
00:01:03
which summarizes the sales of the year and
00:01:06
as a starting point we will therefore
00:01:07
use the database that we
00:01:09
currently have in front of us which
00:01:10
lists each sale made during
00:01:13
the years 2021 and 2022 and in this
00:01:15
database we
00:01:17
first find the product reference the name
00:01:19
of the salesperson who made the sale the
00:01:22
date of this sale the number of
00:01:24
quantities that this represents and the
00:01:26
amount of the total turnover to be
00:01:27
able to build our dashboard
00:01:29
we will use three
00:01:31
main spreadsheets the first is therefore the
00:01:33
sheet in which the database is located
00:01:35
we will also have a
00:01:36
spreadsheet in which we
00:01:38
will carry out the processing and finally the
00:01:40
third which will be the
00:01:43
main spreadsheet will be the one in which we
00:01:45
will generate the dashboard so
00:01:46
the first thing to do will be
00:01:48
to create this spreadsheet which
00:01:51
we will call dashboard and
00:01:53
now we're going to get it ready to
00:01:54
start first we're going to
00:01:56
want to remove the grid that we
00:01:57
have on this spreadsheet for
00:01:59
this we go to the
00:02:00
grid tab and we uncheck its
00:02:03
grid we're also going to
00:02:04
separate this spreadsheet in two
00:02:06
parts a part located on the left
00:02:08
on which we will insert the
00:02:10
buttons which will allow us to
00:02:11
select the data displayed in
00:02:13
the dashboard and a part located
00:02:15
on the right on which
00:02:16
the dashboard will really be located and therefore to
00:02:18
visually represent this
00:02:20
separation we will first
00:02:21
start by selecting all the
00:02:23
cells of the spreadsheet and we
00:02:25
will apply a background which will
00:02:26
correspond to the overall color of the
00:02:28
dashboard for this we
00:02:29
go to the fill tab and
00:02:31
we are going to select another
00:02:33
color here in the custom tab
00:02:35
we are going to use the
00:02:37
hexadecimal field in order to define the color
00:02:39
that we want to use it
00:02:41
will be the color 137.9 BF which we
00:02:45
will call in the rest of this tutorial
00:02:46
the color light blue then we
00:02:48
select only the cells
00:02:50
in columns A and B and this time
00:02:52
we are going to use another color
00:02:54
which we will call here dark which
00:02:57
will be the color of E9 EEA then we
00:03:01
validate by pressing OK and like that
00:03:03
the demarcation is well marked we
00:03:05
are going to enlarge the
00:03:06
columns at B a little bit and now we are going to be
00:03:08
able to create our dashboard
00:03:10
itself for this we are going to
00:03:11
insert shapes in which we
00:03:13
will include the visual elements
00:03:16
of our dashboard and we are going to
00:03:17
use the rectangle shape with
00:03:20
rounded corners that we find here
00:03:22
in the rectangle category we are going to
00:03:24
draw a first shape which will be
00:03:25
located at the very top and in which
00:03:27
we will simply enter
00:03:29
the title of our dashboard,
00:03:31
namely here dashboard
00:03:32
sales sales dashboard then we
00:03:35
are going to delete the outline of the shape
00:03:37
by dropping down the outline menu that
00:03:40
we find in the Format menu of
00:03:42
the shape of the ribbon menu and we click
00:03:44
on its outline for the background color
00:03:45
we are going to use the color that we
00:03:47
called light blue that we
00:03:49
find here in the
00:03:50
recently used colors to finish we are
00:03:52
going to reduce
00:04:53
we are going to pull down the aligned menu first
00:04:55
of all we are going to align the three
00:04:56
shapes starting from the
00:04:59
highest shape and we are also going to
00:05:00
distribute the width of it by
00:05:03
performing a horizontal distribution
00:05:05
in this way the space
00:05:07
between the shapes will be the same everywhere
00:05:09
now we are going to
00:05:10
duplicate the three shapes again by pressing
00:05:12
control d once again then we
00:05:14
are going to place them just below we
00:05:16
will delete the shape if you are in the
00:05:18
middle so that it is the shape located
00:05:20
on the left which comes to
00:05:22
occupy the location which corresponds to the
00:05:24
first two shapes then we
00:05:25
select the two shapes and we
00:05:27
will be able to increase the size
00:05:28
knowing that in this we will be able to
00:05:30
insert a graph and also a top
00:05:33
5 so here salespeople as you
00:05:35
can see given that the
00:05:36
shapes are larger Excel has
00:05:38
also enlarged the rounding that we find
00:05:40
at the angle we are going to so
00:05:41
having to come and correct these roundings one by
00:05:44
one so by correcting the first and
00:05:45
then the second so
00:05:47
now all we have to do is
00:05:48
insert shapes again if this
00:05:51
time just below in selection
00:05:53
the two women that we have just to create
00:05:54
by pressing control d and coming to
00:05:57
place them so stands here we are going to
00:05:59
invert the small shape will be located on
00:06:01
the left so we will be able to
00:06:02
reduce the size that we
00:06:04
gave it and we will enlarge the shape
00:06:06
on the left which will therefore occupy two spaces
00:06:08
so here we are going to lower
00:06:09
the last two shapes very slightly and
00:06:12
here we now have the spaces
00:06:13
which will occupy each of the elements that
00:06:16
we are going to integrate into our
00:06:17
dashboard to finalize so now
00:06:19
each of these shapes we
00:06:21
select them by pressing the
00:06:22
Control key on the keyboard pressed and from the
00:06:25
Shape Format menu we are going to
00:06:26
insert a very light effect, namely here
00:06:29
a drop shadow no we will be able to
00:06:31
define the titles of each of these
00:06:33
shapes here first of all the
00:06:34
main title of the dashboard we will be
00:06:36
able to enlarge it by selecting the
00:06:38
text and going to the
00:06:40
Home menu in order to increase the size
00:06:42
until it occupies
00:06:43
sufficient size for each of the other
00:06:44
elements we will first of all
00:06:46
modify the title here from the second KPI
00:06:50
namely that we will want to call it
00:06:51
quantity sold on the third KPI we
00:06:54
will insert the average selling price
00:06:56
so average selling price here we will have
00:06:59
a graph which shows the evolution of the
00:07:01
turnover over the two years therefore
00:07:04
evolution of turnover here we will
00:07:07
find the Top 5 salespeople so
00:07:09
top 5 salespeople here the top 5 but
00:07:12
this time products sold top 5
00:07:14
products sold and to finish in the
00:07:16
last meeting we will find
00:07:18
this time the quarterly turnover by
00:07:21
product that's it and so to finish we
00:07:23
are going to put each of these titles in
00:07:24
bold by selecting them once
00:07:26
again and pressing control g
00:07:29
now we are going to have to feed the
00:07:31
dashboard for this as I
00:07:33
explained to you earlier time we are going to
00:07:34
start from the database which includes
00:07:36
all of the sales and we are going to
00:07:38
want to extract data
00:07:39
using pivot tables
00:07:41
and therefore the prerequisite for this
00:07:43
will be to transform this
00:07:44
database into an Excel table which we
00:07:46
will greatly simplify the exploitation
00:07:48
of the data contained inside
00:07:50
it for this we select
00:07:52
any cell of the base we
00:07:54
press control l here Excel
00:07:56
displays us a dialog box in
00:07:58
which it asks us to specify
00:08:00
the coordinates which correspond to
00:08:01
our table no problem here Excel has
00:08:04
selected the data which
00:08:05
correspond knowing that to
00:08:07
help it as much as possible we must avoid that there are
00:08:09
empty rows and columns
00:08:10
then we check the option my
00:08:12
table contains headers
00:08:14
given that we actually find
00:08:15
titles at the top of each column
00:08:16
this having been done we validate by
00:08:18
pressing OK Excel we transform
00:08:20
our table into an Excel table which
00:08:22
we will now be able to listen to
00:08:24
very easily for this we will
00:08:25
go to the Insert menu and
00:08:27
we're going to click on
00:08:29
PivotTable here Excel is picking up so the
00:08:31
table that we just created as the
00:08:32
feed source for the
00:08:34
PivotTable we
00:08:36
actually want to insert this
00:08:38
PivotTable onto a new
00:08:39
worksheet all we have left is so all we have to do is
00:08:41
validate the creation of the latter by
00:08:43
pressing the OK button here Excel
00:08:45
has therefore inserted a new sheet which it
00:08:47
has called sheet 2 we are going to
00:08:49
rename it immediately to TCD if we
00:08:51
click on the cross table dy inserted
00:08:53
by Excel we will be able to see that this one
00:08:55
is called pivot table
00:08:57
1 we will start by
00:08:59
renaming it so that we can
00:09:01
find our way easily when we have
00:09:03
several pivot tables
00:09:04
inside our workbook here this
00:09:06
pivot table will us
00:09:08
allow us to recover the total turnover so
00:09:10
we learn it TCD for pivot table
00:09:12
total turnover this is in fact there
00:09:15
will be one last thing left for us to
00:09:17
do which will be to modify the type of
00:09:19
pivot table for this we
00:09:21
will right click on the
00:09:22
pivot table we click on
00:09:24
pivot table option and
00:09:27
directly from the View menu
00:09:29
we will click on
00:09:30
Classic layout of the pivot table this
00:09:33
is the old format of
00:09:34
pivot tables which is a little
00:09:36
less sophisticated a visual point of view
00:09:38
but which is much more practical to
00:09:40
use and above all which will simplify
00:09:42
the use of the data before
00:09:44
validating we will also ask
00:09:46
Excel not to modify the
00:09:48
width of the columns so as not to
00:09:49
break the formatting that we
00:09:51
could set up this is
00:09:52
actually we Val pressing OK and
00:09:54
now our pivot table
00:09:56
ready to go as I told
00:09:58
you in this one we're
00:10:00
just going to want to get the
00:10:02
total sales amount so we're
00:10:03
taking the amount field that we
00:10:05
find on the panel located on the
00:10:07
right and we will simply
00:10:08
drag it into the Value area so
00:10:11
that exel therefore recovers the
00:10:13
total amount of the riser which
00:10:15
corresponds here as you can see
00:10:16
to one million 748000 euros except that here
00:10:19
you have to be careful we find
00:10:21
the data which correspond to the two
00:10:23
years which we find in our
00:10:24
database that is to say 2021 and
00:10:27
2022 but here we will want to
00:10:28
recover only the sales which
00:10:31
correspond to the year 2022 for this you
00:10:33
just have to take the date field and
00:10:35
drag it to the
00:10:37
line field in this way and it will
00:10:39
retrieve our dates and it will
00:10:40
also group the
00:10:42
field which will allow us to find
00:10:44
the year field here which will
00:10:46
interest us that we will be able to
00:10:48
drag up on the filter field for
00:10:49
the moment the quarter and date fields
00:10:51
do not interest us we will therefore be
00:10:53
able to uncheck them directly at the
00:10:55
top from the list of fields that is
00:10:57
how using the thread that
00:10:59
we find at the top our
00:11:00
cross table mix to select the year
00:11:02
2022 and validate by pressing OK the
00:11:05
amount that we will therefore want to
00:11:06
recover in our first KPI it is to
00:11:09
Total and therefore here the 717,000 € and to
00:11:12
be able to recover quickly the value
00:11:13
that we have just calculated here we
00:11:15
are not going to integrate it directly
00:11:17
into a cell but we are going to create
00:11:19
a text box which will have as a
00:11:20
reference the value of the cell in
00:11:22
which the total sales are located
00:11:24
for this we go to the
00:11:26
Insert menu we click on the
00:11:27
text box button the mouse cursor
00:11:30
for their the shape of a cross which will
00:11:32
allow us to draw a
00:11:34
text box at the desired size and location
00:11:35
then to be able to
00:11:37
recover a value contained in a
00:11:39
cell we are not going to
00:11:40
enter directly in the text box
00:11:42
but we are going to go to the
00:11:43
formula bar we are going to type the
00:11:45
equal symbol and then we will be
00:11:48
able to select the cell
00:11:49
in which the value is located value that
00:11:51
we want to recover
00:11:53
we click directly on B4 we
00:11:55
will be able to see that Excel will
00:11:57
insert a formula to recover the
00:11:59
value or if we want
00:12:00
to recover only the reference of
00:12:02
this cell we just have to click
00:12:04
anywhere and modify the reference
00:12:06
to fetch not cell
00:12:08
B6 but cell B4 in which
00:12:10
the result to be retrieved is located, we
00:12:13
validate and Excel has retrieved the
00:12:15
turnover amount so here
00:12:17
if we want to format the
00:12:18
number we return to the cell we
00:12:21
select cell B4 and here we
00:12:23
are going to select a French monetary format
00:12:25
in order to have the data in
00:12:27
euros if we return to the
00:12:29
dashboard as you can see
00:12:30
Excel has made the modification,
00:12:33
now all that remains is to
00:12:34
format the text area by pasting the
00:12:37
content on the right part of the
00:12:38
cell then we will remove the
00:12:40
background color and the border by
00:12:42
right clicking and clicking
00:12:44
on format the shape
00:12:46
then all that remains is to unroll the
00:12:47
fill menu and select no
00:12:49
fill we are going to do the same
00:12:51
for the outline by choosing none and
00:12:53
now so we are going to be able to
00:12:54
format the way the text
00:12:57
is displayed here we are going to want
00:12:58
this to be displayed in white we are
00:13:01
also going to want that this
00:13:02
is displayed in bold and then we are
00:13:04
going to want to increase the size of the
00:13:06
text in order to be able to occupy all
00:13:08
the space reserved for it and
00:13:09
here we are, our first KPI is ready
00:13:11
to make it a little more
00:13:13
aesthetic we will also want to
00:13:15
insert an icon which therefore represents the
00:13:18
total turnover for this we are going to
00:13:20
go to the Insert menu drop down
00:13:21
the middle images and click on this
00:13:24
device in this way and that
00:13:26
displays us a dialog box which will
00:13:27
allow us to select
00:13:29
the image that we want to insert we
00:13:31
select it and we
00:13:32
simply click on Insert here the image is
00:13:35
obviously resized so that
00:13:37
it can be integrated directly
00:13:38
into the area which corresponds
00:13:41
here it is therefore a question of representing the total turnover
00:13:43
when we have finished we will be
00:13:44
able to select these two elements by
00:13:49
holding down the Control key on the keyboard as we are now used to and then we
00:13:51
press control d for we
00:13:53
move the shape here to the second
00:13:55
insert in which we will want to
00:13:57
obtain the quantities sold then we
00:13:59
could completely repeat
00:14:00
the operation as we did
00:14:02
just before or if we want to
00:14:03
go faster we select
00:14:05
columns A and B we are going to copy them
00:14:07
by pressing control C we place
00:14:09
on the right and we
00:14:11
now press control V to
00:14:12
duplicate them then all that will remain is to
00:14:14
remove the amount and instead
00:14:17
display the quantities here when you
00:14:18
see it Excel has also
00:14:20
kept the format of the names, that is to say
00:14:22
that 'it considers that it is a
00:14:24
monetary value obviously this is not
00:14:25
the case so we will simply
00:14:27
click on the thousands separator
00:14:29
so that they have the euro unit and we
00:14:32
will also click on reduce the
00:14:34
decimal places in order to remove the two
00:14:36
digits that were located behind the
00:14:38
decimal point here if we look at the cell
00:14:40
that we are going to want to recover is
00:14:42
located at coordinates E4 back in
00:14:44
our dashboard we click on
00:14:46
the text box in which
00:14:47
the value to be found is located modify and we replace
00:14:49
the reference B4 with the reference 4
00:14:51
when we validate pixel will
00:14:53
take this time the value of the
00:14:56
new cell however as you
00:14:57
can see given that we
00:14:59
have modified the reference Excel has
00:15:02
also reset the format none
00:15:03
problem, just select the
00:15:06
previous text box, click on
00:15:07
reproduce formatting and then
00:15:09
click on this text box, we
00:15:12
will now modify the
00:15:13
shape which therefore corresponds to this insert
00:15:15
by right clicking then
00:15:18
selecting change the image on this
00:15:20
device here we select the
00:15:22
new image for Excel
00:15:24
immediately does the replacement now
00:15:25
to get the average selling price there is
00:15:28
no need to create a new
00:15:30
pivot table since
00:15:32
the average selling price simply matches
00:15:34
to the amount of
00:15:35
turnover that we have in
00:15:37
cell B4 divided by the number of units
00:15:39
sold that we have this time
00:15:41
in cell E4 we validate XL
00:15:43
informs us that the average sales price
00:15:45
of this year 2022 and of 93.42 euros
00:15:49
that here we enter average selling price
00:15:51
in order to know what
00:15:53
the value corresponds to and we will put it
00:15:55
in form knowing that it is a
00:15:57
monetary value which is located on
00:15:59
the cell G4 which will therefore allow us
00:16:01
to recover this value very quickly
00:16:03
by proceeding as we did
00:16:05
just before we select the two
00:16:06
shapes we press control d then
00:16:09
we will modify the reference
00:16:10
used it is not cell 4 it is
00:16:13
cell G4 as just now we
00:16:16
are going to duplicate the formatting of a
00:16:18
text box on which the
00:16:19
formatting is well preserved and we are going to
00:16:21
apply it therefore on the average selling price
00:16:22
to finish it only goes to us
00:16:24
just modify the image by
00:16:26
right clicking change the image
00:16:28
this device and we select
00:16:30
the image which corresponds to the average selling price
00:16:32
and now we
00:16:34
have understood the logic of creating the
00:16:36
dashboard starting from from a
00:16:37
database through a pivot table
00:16:39
and by retrieving the elements
00:16:41
let's see how to very easily modify
00:16:43
the parameters used for this we
00:16:45
will be able to reuse segments
00:16:47
which allow us to modify the values
00:16:49
of the pivot table directly
00:16:51
on the fly whatever 'it is possible to
00:16:53
connect a segment on several
00:16:55
pivot tables in order to
00:16:57
directly modify the same parameter
00:16:59
on several pivot tables
00:17:00
to insert a segment it's
00:17:02
very simple just select
00:17:04
one of the two pivot tables
00:17:05
then go to us in the
00:17:07
analysis menu of the pivot table
00:17:09
in order to click on Insert a segment
00:17:12
and it then displays a
00:17:13
dialog box which will allow us to
00:17:15
select the fields on which
00:17:17
we will want to intervene using
00:17:18
the segments here we will obviously
00:17:20
have the product field the
00:17:22
commercial field the date field which
00:17:24
will therefore allow us to modify the months
00:17:26
displayed and to finish the year field
00:17:29
this is finally we validate by pressing
00:17:31
OK and XL inserts the four
00:17:33
segments as you can see here
00:17:34
on the year segment the year 2022 is
00:17:37
selected but if we click on
00:17:38
2021 then the year used on the
00:17:41
pivot table here has been
00:17:43
modified if we want the segment to
00:17:45
impact the two
00:17:47
pivot tables we are going to click on
00:17:48
it to go to the
00:17:50
segment menu and click on Report Connection
00:17:52
here we will now be
00:17:53
able to select the pivot table
00:17:55
TCD CA total we validate by
00:17:58
pressing OK and now as you
00:18:01
can see Excel shows us
00:18:02
the year 2021 for both
00:18:04
pivot tables if we click on
00:18:06
2022 it is indeed the two
00:18:08
pivot tables which will be modified
00:18:09
this is in fact we will be able to
00:18:10
carry out the same operation for
00:18:12
all the segments that we have
00:18:14
inserted so we select it we
00:18:16
click on Report connection and
00:18:18
we select the second
00:18:19
pivot table we repeat this
00:18:21
same operation for the
00:18:23
commercial segment and then to finish with the
00:18:25
product segment now we will
00:18:27
see how to modify the appearance of a
00:18:29
segment in effect as you can
00:18:30
see here the segment such as a tight by
00:18:33
Excel does not correspond at all to
00:18:34
the graphic purchase that we have set
00:18:36
up on our dashboard to
00:18:38
create a new graphic charter,
00:18:39
we select a segment, we
00:18:41
click on the segment menu and here we are
00:18:43
going to expand the segment style menu.
00:18:45
we will then
00:18:47
right click on the segment style used and
00:18:49
click on Duplicate here Excel will therefore
00:18:51
insert us easy personalized which will be
00:18:54
based on the style therefore of the segment
00:18:56
currently being analyzed it will suffice to click
00:18:58
on one of the elements for the modify
00:18:59
so here we click on
00:19:01
entire segment then on format which will therefore
00:19:03
allow us to modify the appearance of the
00:19:05
segment we will first
00:19:07
go to the fill menu then
00:19:09
we will click on other color
00:19:11
in order to find the color that
00:19:13
we had called dark blue as a
00:19:15
reminder the hexadecimal code of dark blue
00:19:17
and 2 E9 ea this is in fact we
00:19:21
validate by pressing OK and we
00:19:23
are going to remove the border that there may be
00:19:25
around the segment box
00:19:26
by clicking on none this
00:19:28
being done we validate by pressing
00:19:30
OK and now we will be able to
00:19:31
select the segment which is therefore
00:19:33
active, that is to say the segment in blue
00:19:35
so we select we click
00:19:37
on format we will also remove
00:19:39
the border which may have been put in place and
00:19:41
we are going to select this time
00:19:43
another color which will correspond to
00:19:45
the light blue color this light blue color
00:19:47
which corresponds to the hexadecimal code
00:19:50
237 9 BF then validate by pressing
00:19:53
OK and we also validate a
00:19:55
second time when we have finished
00:19:57
we we can click on always
00:19:58
use this segment style for the
00:20:00
document and validate by pressing OK
00:20:02
now we can see here in the
00:20:04
list of styles that our new style
00:20:06
is now proposed it will be enough to
00:20:08
click on it to apply it
00:20:10
automatically so at segment that is
00:20:12
selected here we can see one
00:20:14
last little detail given that
00:20:16
we are in dark blue here we
00:20:17
may want the writing to
00:20:19
also be displayed in white so we
00:20:20
right click on
00:20:22
the style again we choose modify we
00:20:24
choose l 'selected element with
00:20:26
data then format here we are going to
00:20:28
go to the font tab and
00:20:30
select a
00:20:32
white font then we validate and we
00:20:34
validate and now therefore the value
00:20:36
is clearly displayed in white we will
00:20:37
now be able to use our style
00:20:39
for each of the segments by
00:20:40
selecting them one by one and
00:20:42
therefore clicking on the new style that we
00:20:44
have just defined now to finish
00:20:45
creating our segment we will
00:20:48
want to remove the header as well as the
00:20:50
data that cannot be
00:20:51
used for this selecting a
00:20:53
segment we click on Segment parameters
00:20:55
we will first of all
00:20:56
hide the antenna so by unchecking
00:20:58
the option displayed at the top and we will
00:21:00
click on Hide elements without
00:21:02
data once we have carried out
00:21:03
this operation on a first segment
00:21:04
we select the next one and we are
00:21:07
going to repeat the same operation
00:21:08
so now that our segments are
00:21:10
ready we select them all then
00:21:12
we press control x in order to
00:21:14
cut them and back in our
00:21:16
dashboard we will now be able to
00:21:17
paste them we will now
00:21:19
place on the space that we have
00:21:20
reserved here first of all we are going to
00:21:22
want to display the years so we
00:21:24
move the box containing the two
00:21:27
segments here to display them one side
00:21:29
of the other we are going to go to
00:21:30
the segment menu and we will choose
00:21:32
to display them on two columns
00:21:34
then we will resize the
00:21:35
box and just below we will be
00:21:37
able to display the months in the same
00:21:38
way we will want to display them
00:21:40
on two columns so that they
00:21:42
take up a little less space
00:21:43
so it you have to be careful here on
00:21:45
the year 2022 the data goes until the
00:21:47
month of August but if we select
00:21:48
the year 2021 then the data
00:21:51
goes until December then we
00:21:52
display the products that we will
00:21:55
place just below here like you
00:21:57
can see the box which contains
00:21:58
the segments of the products is located so
00:22:00
below the months to bring them
00:22:01
above we are going to go
00:22:03
to the segment menu drop down move forward and
00:22:05
move the box to the foreground so that
00:22:07
it is located above and then
00:22:09
to finish we will only have to
00:22:10
place the box which contains the names of
00:22:12
the salespeople which we will
00:22:14
also place just below so
00:22:15
move to the foreground and we will
00:22:18
raise it slightly we are finished
00:22:20
so we can adjust the position of
00:22:22
each of the elements so that
00:22:23
visually it appears as best as
00:22:25
possible and now that
00:22:26
our segments are correctly inserted on our
00:22:28
dashboard, all you have to do is
00:22:29
select the elements by clicking
00:22:31
on the buttons that correspond to
00:22:33
obtain the KPIs which therefore correspond
00:22:35
to the options which are chosen so here
00:22:37
the sales of January 2021 you can
00:22:40
also filter on the a and only
00:22:42
on the sales of Annie when we
00:22:44
have selected an item and we
00:22:46
want all the other items
00:22:47
to display just hold the
00:22:50
Control key of the keyboard pressed by
00:22:51
clicking on one to select all
00:22:54
the elements of the segment box here
00:22:56
to display all the salespeople we
00:22:58
press the Control key and we
00:22:59
click on Annie we will be able to
00:23:01
perform the same operation to
00:23:02
redisplay all the months so here we can
00:23:04
now move very
00:23:05
easily from the year 2021 to the year
00:23:07
2022 and that's it, we will
00:23:09
now be able to complete our
00:23:10
dashboard by first displaying
00:23:12
the graph which will represent
00:23:14
the evolution of the turnover over the two
00:23:16
years for this we let's return to our
00:23:18
table we select
00:23:21
columns A and B as before and we
00:23:23
are going to paste them here at the
00:23:25
column level here we will be able to modify
00:23:26
our dynamic cross table in order to
00:23:29
show the sales this
00:23:30
time for the two years we are going
00:23:32
to move the years field from the
00:23:34
filter zone to the column zone and we
00:23:36
are also going to show the
00:23:37
different months for this we are going to
00:23:39
drag the date field into the
00:23:41
ball zone then we are going to remove the
00:23:43
totals which will not interest us
00:23:44
by carrying out a right click then by
00:23:47
going to the single crosstab option
00:23:49
in the Toto and filter tab we
00:23:51
will uncheck display autodides and
00:23:53
display the column totals this
00:23:55
being done to recover its values ​​and
00:23:58
transform them into a graph we will
00:23:59
want to recover them so here on the
00:24:01
right part in order to ensure that
00:24:03
when there is a blank Excel does
00:24:05
not display a value 0 which would therefore correspond
00:24:07
to a flat curve located on the abscissa axis
00:24:09
we will instead wish to
00:24:11
display na which will indicate Excel
00:24:14
that 'there is no value to display
00:24:15
so here we select cell
00:24:18
M4 we go to the bottom and we
00:24:20
also place ourselves on the right on
00:24:22
three columns then we will
00:24:23
retrieve the value of the cell located
00:24:26
at coordinates i4 in order to retrieve
00:24:28
all the values ​​of our
00:24:30
pivot table it will
00:24:31
then only remain to select the
00:24:34
numerical values ​​and to therefore include the call to
00:24:37
the reference J5 within a formula if
00:24:39
a formula if which allows to carry out a
00:24:42
test and to retrieve a value when
00:24:44
the result of this test is equal to true
00:24:46
and another value when it is
00:24:48
equal to false we will simply
00:24:49
look if the value of cell J5
00:24:52
is equal to zero when this is the case
00:24:54
we will therefore display a error na and
00:24:56
on the other hand if J5 is different from 0
00:24:58
then we will be able to display this
00:25:00
value without the slightest problem then
00:25:02
we validate by pressing the
00:25:04
controlled keys entered which will
00:25:05
allow us to validate the formula on
00:25:07
all the selected cells
00:25:08
as you can see when here
00:25:10
we had values ​​equal to 0 which
00:25:13
therefore corresponded to empty cells
00:25:14
now Excel displays na
00:25:16
to finish we will want to
00:25:18
ensure that the values ​​displayed here are
00:25:20
cumulative values ​​which
00:25:22
will therefore allow us to obtain the
00:25:24
total amount as
00:25:26
the months progress, for this it is very simple
00:25:27
we select the data from
00:25:29
the month of February and here rather than
00:25:31
simply recovering the value of
00:25:33
cell J6 we will recover
00:25:35
this value which we will add
00:25:37
with the value that we had for the
00:25:39
previous month so here + N5 and we
00:25:42
are going to validate as before by
00:25:44
pressing control enter now
00:25:45
that our data is ready we can
00:25:47
select them and we are going to insert
00:25:49
a graph by going to the
00:25:51
Insert menu
00:25:52
choosing the style of graph that
00:25:53
interests us and we will for
00:25:55
example simply select a
00:25:57
2D curve once our
00:25:58
graph has been created so we must
00:26:00
press control x in order to cut it
00:26:01
then we will paste it on the
00:26:03
table on board we are going to place it
00:26:05
roughly here above
00:26:07
the location of your final which will be
00:26:08
yours and we are going to start by
00:26:10
choosing a style from the graphic creation menu
00:26:12
no for example choose
00:26:15
style 3 here this being done we are going to
00:26:17
delete the background color by
00:26:19
right clicking on the
00:26:21
graph and choosing format of the
00:26:23
graph area not us sideways we
00:26:24
will be able to choose no fill
00:26:26
and no line and we will have to
00:26:28
repeat the operation this time for
00:26:30
the plotting area of the graph it
00:26:32
will now be enough to select it and
00:26:34
click on no filling and
00:26:37
no lines here we can see
00:26:39
that the vertical lines which had been
00:26:40
created are no longer necessarily readable
00:26:42
so we select them and we
00:26:44
will be able to modify their color
00:26:45
directly once again from the
00:26:47
side panel by choosing a
00:26:49
white color which will be much more
00:26:51
visible this is we will be able to
00:26:53
delete the title of the graphic which is
00:26:55
therefore duplicated with the title that we
00:26:57
have just below and we will be
00:26:58
able to change the fonts
00:27:00
to white so we select here
00:27:02
the vertical axis we go to the
00:27:04
Home menu and we are going to select
00:27:06
a white color we repeat
00:27:08
the operation this time for the
00:27:10
horizontal axis which we also change to
00:27:12
white and we are going to carry out the same
00:27:14
operation for the legend now if
00:27:17
we want to display the values ​​that
00:27:18
we have here in the middle of euros we
00:27:21
select the vertical axis again
00:27:22
then on the side panel from
00:27:24
axis option we are going to pull down the
00:27:26
number menu and we are going to choose a
00:27:28
custom format and here in
00:27:31
format code we are going to enter the format
00:27:32
pound space pound number 0 space and
00:27:36
in quotes kilos euro this is in
00:27:39
fact we click on Add so
00:27:40
that exel automatically modified the
00:27:42
format of the vertical axis then we
00:27:45
will be able to resize the area
00:27:46
so that that -this can use
00:27:48
the entire space
00:27:50
reserved for it and to finish we are going to change
00:27:52
the texts to bold by pressing the
00:27:54
bold button here Excel to remove the
00:27:56
white color
00:27:57
simply reapply it and
00:27:59
so now there is the turnover evolution insert
00:28:01
is finished we will be able to move on
00:28:03
to the top 5 salespeople insert for
00:28:05
this we will create a new
00:28:06
pivot table
00:28:08
always starting from the pivot table that
00:28:10
we have on columns A and B which
00:28:12
we will paste here in column Q and
00:28:14
to obtain the sales made by
00:28:16
salespeople we will simply
00:28:18
drag the sales field to the
00:28:20
row level then we will
00:28:22
want to order this data in
00:28:24
descending order, that is to say the
00:28:26
most important values ​​at the beginning
00:28:27
to do this we drop down the
00:28:28
commercial menu we will choose
00:28:31
additional sorting option we choose
00:28:32
the descending order and here we will
00:28:35
choose according to the amount we
00:28:37
validate by pressing OK and
00:28:39
now the values ​​are properly sorted
00:28:40
to be able to retrieve these values ​​on
00:28:42
our cross tables dynamic we
00:28:43
are going to place ourselves on the cells
00:28:45
located just to the left we are going to
00:28:46
select a range of cells
00:28:48
containing 5 rows and columns on
00:28:51
which we are going to retrieve its
00:28:52
values ​​so by selecting cell
00:28:55
Q5 and we are going to be pressing
00:28:57
control enter in this way we
00:28:58
will be able to
00:28:59
directly format these cells here we will
00:29:02
therefore select the cells in
00:29:03
which the name of the salespeople is located
00:29:05
we will apply a background color
00:29:07
which will correspond to our
00:29:09
light blue and we will display the texts
00:29:11
in white for what is
00:29:12
numerical values ​​we will first
00:29:13
modify the format by selecting them
00:29:16
then right-clicking in order to
00:29:18
choose Cell format in the
00:29:20
custom field we will enter the
00:29:21
format that we used just
00:29:23
before on our graph and allowed
00:29:25
to display values ​​in kg euros,
00:29:27
namely the Diaz format space pound pound
00:29:30
0 space and between quotation marks kilos we
00:29:33
validate by pressing OK and we
00:29:35
will very slightly remove the
00:29:37
border values ​​by inserting a space
00:29:39
on the right and performing the same
00:29:41
operation with the names of the salespeople
00:29:43
which we are going to shift slightly to
00:29:45
finish we are going to select all
00:29:47
the lines which correspond to these
00:29:48
cells by pressing capital letter and
00:29:51
space and the balloon increase the height
00:29:53
of each cell we will now be
00:29:55
able to center the different values
00:29:57
contained in these cells in the middle by
00:29:59
adding a space here so cells
00:30:01
are again pasted on the borders
00:30:02
and we will be able to modify this by
00:30:05
repeating the alignment operation that
00:30:07
we saw just before now
00:30:08
to be able to retrieve its values ​​on
00:30:10
our dashboard we select
00:30:12
the cells then we copy them by
00:30:14
pressing control C and back on
00:30:16
our dashboard we will be able to
00:30:18
paste them by carrying out a
00:30:20
special paste of type I majolier in this
00:30:22
way we therefore recover an image
00:30:24
which includes the values ​​of the cells and
00:30:26
if we modify these cells then the
00:30:29
modification will also be carried out
00:30:30
here on our image to
00:30:32
realize this we will modify the
00:30:33
background color of the cells on which
00:30:35
the numerical values ​​are located which we
00:30:37
will display in very light gray and
00:30:39
we will also modify the
00:30:41
color of the numerical values which we
00:30:43
will display in dark blue if we
00:30:45
return to our dashboard the
00:30:47
modifications have been made
00:30:49
so that the values ​​are a little
00:30:50
more readable we are going to
00:30:53
select them and to very
00:30:55
slightly increase the size of the characters to
00:30:57
also enlarge a a little bit the
00:30:59
height of the cells and enlarge the
00:31:01
width of it which means that if
00:31:03
we come back here Excel has made
00:31:05
the modifications but it has kept the
00:31:08
size of the image as it was
00:31:09
just before so to be able to
00:31:11
restore it we go to the
00:31:12
formal image menu we drop down size and
00:31:15
we can click on Restore
00:31:17
now the values ​​have the
00:31:19
original size as we
00:31:20
find it on the TCD tab to finish
00:31:22
with this top 5 sales cards
00:31:24
if we wish we
00:31:25
can slightly modify the
00:31:27
formatting of the area by choosing for
00:31:29
example to apply a reflection type effect
00:31:31
with a very light reflection
00:31:34
and so now we have
00:31:35
seen how to create a top 5 of
00:31:36
salespeople there is no will have no
00:31:38
difficulty in creating a top 5 of the
00:31:40
products sold we place ourselves on
00:31:41
TCD we select the columns Q
00:31:44
up to u we carry out a check C
00:31:47
we colonize them from column W
00:31:49
and here we will therefore just have to delete
00:31:52
the salespeople and instead of inserting
00:31:54
the products in this way we
00:31:55
will find the products except
00:31:57
that as you can see there are
00:31:58
only four products on our database it
00:32:00
will therefore be a top 4 we select
00:32:02
these four cells we copy them
00:32:04
we let's go to the Dashboard menu
00:32:06
and we make a collage
00:32:09
from a linked image we place
00:32:10
the image in the desired location then to
00:32:12
retrieve the same formatting that we
00:32:14
have here on the top 5 salespeople
00:32:16
we select the image we
00:32:18
go to the Home menu and we
00:32:20
click on the Brush tool, that is to
00:32:21
say reproduce the formatting the
00:32:23
mouse cursor for then the shape
00:32:24
of a brush and we click on
00:32:26
the image on which we we will want to
00:32:28
duplicate the format here we are going to
00:32:29
modify the title it will be a top 4 and we
00:32:32
will now we only have one
00:32:34
last element left on our
00:32:35
dashboard which will be the matrix which
00:32:37
shows the quarterly sales by
00:32:39
salesperson for this we are going to create
00:32:41
one last pivot table which
00:32:43
we can therefore base on the
00:32:45
pivot table which we have just
00:32:46
created which we will therefore paste here
00:32:48
from the AC column as we
00:32:50
saw a little earlier in this
00:32:52
table dynamic crossword we will
00:32:54
want to recover the amount of sales
00:32:56
by product and by salesperson for this
00:32:58
we will simply
00:32:59
drag the product field from the
00:33:01
row area to the column area and we
00:33:03
will display the name of the salespeople
00:33:04
at the row level as
00:33:06
above time to be able to
00:33:07
easily take the data that we
00:33:09
find here we are going to select
00:33:10
a cell located on the right in
00:33:12
which we are going to select
00:33:14
a number of cells which
00:33:15
corresponds to our table, namely
00:33:17
that there must be 9 rows and 6 columns
00:33:20
then we will insert
00:33:21
the values ​​which correspond to
00:33:23
cell AC4 and we will validate by
00:33:25
pressing control enter we will
00:33:27
now be able to modify the format of
00:33:29
this table first of all we will
00:33:30
want to standardize the height of the
00:33:32
cells so for this we
00:33:34
select the cells 4 up to 12
00:33:36
and we are going to recover the same width
00:33:38
that we have so on cells 5 to
00:33:41
9 that is to say a width of approximately
00:33:42
30 pixels we select 30 pixels
00:33:45
and we release then we are going to
00:33:46
enlarge the width of the column in
00:33:49
which the name of the salespeople is located
00:33:50
we will display all the
00:33:52
numerical values ​​in kg euros so by
00:33:55
selecting them by
00:33:56
right clicking Cell format and now if
00:33:58
we go to customize we
00:34:00
will find at the bottom the
00:34:02
personalized format that we created just
00:34:03
before namely the one which therefore allows us
00:34:05
to display the values ​​in euro kilos
00:34:07
we validate by pressing OK and
00:34:09
we will only now have to
00:34:11
format the different values
00:34:13
of our table we select the
00:34:15
headers that this either the
00:34:16
column line headers which we will
00:34:18
display with a light blue background color
00:34:20
and with a white text color
00:34:22
then for what concerns the
00:34:24
general rates we select them and
00:34:27
this time we will display them
00:34:28
in dark blue and still with a
00:34:30
white color to finish we are going to
00:34:32
vertically center all these values
00:34:35
in the middle of the cell and we are going to
00:34:37
apply a slight indent knowing
00:34:39
that here the numerical values ​​have
00:34:41
also been aligned to the left of
00:34:42
the cell so we select them
00:34:44
and display them on the left then
00:34:46
we can delete the value of
00:34:48
cell AC4 and here we will replace
00:34:50
the grand total simply with a
00:34:52
total and proceed in the same way here
00:34:55
on the total of the lines if we
00:34:57
wish we can also
00:34:58
display the values ​​which correspond
00:34:59
to the Toto in bold so that it is
00:35:01
a little more readable we
00:35:03
select them and we press
00:35:04
control I then we will be
00:35:06
able to export the values ​​of this
00:35:08
table by selecting the cells by
00:35:10
pressing control C and from our
00:35:12
dashboard we are going to insert
00:35:15
a linked image we place the image
00:35:17
above the insert that we have
00:35:18
reserved for it and we can possibly
00:35:20
adjust the size so that it
00:35:22
fits best once we
00:35:24
are finished if we want to give a
00:35:26
visual impact to this data we can
00:35:28
display what we call a
00:35:30
heat map for this we return to
00:35:32
our original table we
00:35:33
select the numerical values
00:35:35
excluding the totals then we are going to
00:35:37
go to the Conditional formatting menu
00:35:38
and click on color shade
00:35:40
then we will be able to
00:35:42
choose the color scale that
00:35:43
interests us knowing that on this one
00:35:45
the highest values ​​will therefore be
00:35:47
in blue while the lowest values
00:35:49
will be in red and the
00:35:51
higher or lower the value and the darker
00:35:54
these will be so now if
00:35:55
we go back to our dashboard
00:35:57
and give will actually be displayed
00:35:59
based on this color grid
00:36:01
and so before we leave the last
00:36:02
element that we are going to want
00:36:04
to control is that all the segments
00:36:05
impact all the elements that they
00:36:07
must impact so here we
00:36:09
choose for example the year 2022
00:36:11
we will be able to see that at the
00:36:12
level of the KPIs therefore the three elements
00:36:14
that we have here no problem if the
00:36:16
elements are well impacted on the other hand
00:36:18
on the graph of the evolution of the turnover
00:36:20
we will want to
00:36:21
systematically keep the two years
00:36:23
to display so for this we will
00:36:24
go again to TCD in order to
00:36:27
find the dynamic cross table
00:36:28
which allows us to control the curves
00:36:31
displayed this is the one that we
00:36:33
are going to rename to TCD ca by month and
00:36:37
year in this way if we return
00:36:39
to the segment we can
00:36:41
select it then go to
00:36:43
segments report connection check
00:36:45
that all the pivot tables
00:36:46
are checked except the pivot table
00:36:48
TCD by month and by year we
00:36:51
will carry out the same check on
00:36:53
the segment with the months on which all
00:36:56
the pivot tables must
00:36:57
actually be checked except TCD by
00:36:59
month and year so be careful here
00:37:01
given that we had deactivated
00:37:03
the year 2022 before making this
00:37:05
modification it will not be
00:37:07
reflected we must return to our
00:37:09
pivot table and display the
00:37:11
data which corresponds to 2022 in
00:37:13
this way we will
00:37:14
find it on the graph but when
00:37:16
we select
00:37:17
different years this will not be
00:37:19
modified so here we can
00:37:20
now see that if we
00:37:21
select product a we will
00:37:23
find the sales of 2021 because the
00:37:27
2021 segment is checked only to
00:37:29
occur so the sales by
00:37:31
salespeople correspond to the sales
00:37:33
made on this product and only
00:37:35
on this one so be careful as you
00:37:37
can see here given that
00:37:39
we have selected a single product
00:37:40
all the data that we find
00:37:42
on the pivot table have been
00:37:43
reported and we therefore find here the
00:37:45
general total we are going to go
00:37:47
to TCD then find the
00:37:49
pivot table on which
00:37:51
the by product is located we select it
00:37:53
in order to right click and
00:37:55
choose pivot table option
00:37:57
and we are going to deactivate the
00:37:59
totals we are going to perform the same
00:38:00
operation on the pivot table
00:38:02
located just on the left so that
00:38:04
when we select a single
00:38:06
salesperson Excel does not show us the
00:38:08
line which shows the general total so
00:38:10
we deactivate the Toto fools and the row
00:38:13
totals then we will
00:38:14
carry out the same operation for our
00:38:16
matrix by right clicking and
00:38:19
selecting the pivot table option
00:38:20
we are going to deactivate
00:38:22
the totals so obviously you should
00:38:24
know that by carrying out this operation
00:38:26
there are no more Totos which are calculated
00:38:28
on our table no problem we
00:38:30
select the cells and we
00:38:32
delete the values ​​and then we
00:38:34
select all the
00:38:35
numerical values ​​and we press Alt =
00:38:37
so that exel
00:38:39
automatically displays the totals of the lines and
00:38:41
here as you can see
00:38:42
when a value is equal to zero Excel
00:38:45
displays this in red which would
00:38:47
therefore mean that it would be a
00:38:49
low value to avoid that we will
00:38:51
select the numerical value and we
00:38:53
will ask Excel to only display the
00:38:54
value of the cell that we have
00:38:56
when it is not empty so
00:38:58
we let's use a function if as
00:39:00
we saw earlier we
00:39:02
look if the value of Ad5 is
00:39:04
different from nothing at all when this is
00:39:06
the case we display this
00:39:08
value clearly but when this is
00:39:10
not the case we will display nothing at all
00:39:11
also we validate by pressing
00:39:13
control enter and now only the
00:39:16
values ​​which actually contain
00:39:17
a value will be displayed here we
00:39:19
will be able to perform the same
00:39:20
operation so by selecting the
00:39:22
cells then while holding the
00:39:23
Control key on the keyboard pressed we
00:39:25
select the cell to K5 then we
00:39:28
enter the formula bar and
00:39:29
we validate by pressing control
00:39:31
enter in order to reproduce the formula on
00:39:33
all the selected cells and
00:39:35
so if you now return
00:39:37
to our dashboard we can
00:39:39
see that indeed this one is
00:39:41
very dynamic and reacted according to
00:39:43
what we select on the
00:39:44
left side so if you ever want to
00:39:46
download the sales dashboard
00:39:47
that we have just built
00:39:48
follow the link available in the
00:39:50
description of the video and take advantage of
00:39:52
the introductory offer available until
00:39:54
Friday evening so as usual you
00:39:56
can download the emergency support
00:39:57
by following the link that you will find
00:39:59
in the description of the video if
00:40:01
you liked the video don't forget to
00:40:02
like it and share it with your friends colleagues
00:40:03
and friends on all of your
00:40:05
social networks and you can also
00:40:06
leave me your impressions in the
00:40:07
comments area I will read each of
00:40:09
them and they respond with pleasure
00:40:10
obviously if it has not yet been done
00:40:12
subscribe to the channel so as not to miss
00:40:13
any tips it is also very
00:40:15
important to help me gain
00:40:16
visibility in order to be able to develop the
00:40:18
channel and continue to offer you
00:40:20
lots of new videos thank you for
00:40:22
following this video and see you very soon for
00:40:23
a next tutorial

Description:

❤️ S'abonner à la chaîne : https://www.youtube.com/channel/UCXyZxDPY_d-ehSYV0CH3W9A/?sub_confirmation=1 💻 LIEN DE L’ARTICLE ET DE TELECHARGEMENT : https://www.excelformation.fr/tableau-de-bord-des-ventes-excel-no.html 🗨️ Dans ce tutoriel, nous allons créer un tableau de bord de ventes complet. Nous allons partir d’une base de données reprenant les ventes réalisées par une entreprise pour construire un tableau de bord complet et dynamique. 😻 Si cette vidéo vous plaît, je vous invite fortement à la liker et à vous abonner à la chaîne. C’est ce qui va me permettre de développer ma chaîne, pour pouvoir continuer à vous proposer des contenus que celui-ci ! 📢 Posez-moi également toutes vos questions dans l’espace des commentaires, je me ferai un plaisir d’y répondre ! ============================================= 📚 #FormationExcel : - ❤️ Les meilleures astuces pour débutants et pro d’Excel (OFFERT) ❤️ : https://www.excelformation.fr/astuces.html - ❤️ Formation : Les bases d’Excel (Grands débutants) ❤️ : https://www.excelformation.fr/formation-bases-excel-grands-debutants-no.html - ❤️ Formation : Maîtriser Excel (Intermédiaire) ❤️ : https://www.excelformation.fr/formation-maitriser-excel-no.html - ❤️ eBook : VBA pour les grands débutants ❤️ : https://www.excelformation.fr/formation-vba-complete.html - ❤️ eBook : Les tableaux croisés dynamiques, c’est facile ❤️ : https://www.excelformation.fr/tcd-methode-complete-excel-no.html ============================================= 📄 SOMMAIRE : 00:00 Introduction 00:55 La base de données 01:29 Préparation du tableau de bord 07:32 Utiliser des tableaux croisés dynamiques pour alimenter le tableau de bord 13:59 Dupliquer un TCD en une seconde 16:33 Utiliser les segments pour sélectionner les données à afficher 23:08 Afficher l'évolution comparative des ventes 27:58 Indicateur : TOP5 des commerciaux 31:34 Indicateur : TOP5 des produits vendus 32:33 Indicateur : Matrice des ventes trimestrielles par commercial 36:00 Contrôle du champ d'application des segments ============================================= ❓ LIENS UTILES : Comment afficher des données en milliers d'euros (k€) = https://www.excelformation.fr/video-comment-afficher-des-donnes-en-milliers-d-euros-k.html Comment réaliser une somme de cellules en 1 clic sur Excel = https://www.excelformation.fr/somme-automatique-excel.html Comment lancer des macros automatiquement avec les évènements VBA : Les objets Workbook = https://www.excelformation.fr/evenements-formation-vba-excel-complete.html Comment identifier les doublons et triplets automatiquement sur Excel = https://www.excelformation.fr/identifier-doublons-triplets-mis-en-forme-conditionnelle.html 108 astuces pour grands débutants sur Excel ! = https://www.excelformation.fr/astuces.html Comment effectuer un test sur Excel : la fonction SI() (ainsi que ET() et OU()) = https://www.excelformation.fr/la-fonction-si.html Les raccourcis clavier = https://www.excelformation.fr/les-raccourcis-clavier.html Comment faire des recherches avancées dans un classeur Excel = https://www.excelformation.fr/recherche-ctrl-f-excel.html Formation VBA d'Excel complète = https://www.excelformation.fr/formation-vba-complete.html L’ENCODEUR – La protection ultime pour vos données ! = www.excelformation.fr/lencodeur.html SAVEMYFILES – Comment ne plus jamais perdre de données sur Excel ? = https://www.excelformation.fr/savemyfiles-no.html ============================================= 👨‍👨‍👧‍👦 Réseaux sociaux : - Facebook : https://www.facebook.com/unsupportedbrowser - Twitter : https://twitter.com/excelformation - Instagram : https://www.facebook.com/unsupportedbrowser

Preparing download options

popular icon
Popular
hd icon
HD video
audio icon
Only sound
total icon
All
* — If the video is playing in a new tab, go to it, then right-click on the video and select "Save video as..."
** — Link intended for online playback in specialized players

Questions about downloading video

mobile menu iconHow can I download "Comment créer un tableau de bord des ventes sur Excel en 40mn ? Cours Excel complet tous niveaux" video?mobile menu icon

  • http://unidownloader.com/ website is the best way to download a video or a separate audio track if you want to do without installing programs and extensions.

  • The UDL Helper extension is a convenient button that is seamlessly integrated into YouTube, Instagram and OK.ru sites for fast content download.

  • UDL Client program (for Windows) is the most powerful solution that supports more than 900 websites, social networks and video hosting sites, as well as any video quality that is available in the source.

  • UDL Lite is a really convenient way to access a website from your mobile device. With its help, you can easily download videos directly to your smartphone.

mobile menu iconWhich format of "Comment créer un tableau de bord des ventes sur Excel en 40mn ? Cours Excel complet tous niveaux" video should I choose?mobile menu icon

  • The best quality formats are FullHD (1080p), 2K (1440p), 4K (2160p) and 8K (4320p). The higher the resolution of your screen, the higher the video quality should be. However, there are other factors to consider: download speed, amount of free space, and device performance during playback.

mobile menu iconWhy does my computer freeze when loading a "Comment créer un tableau de bord des ventes sur Excel en 40mn ? Cours Excel complet tous niveaux" video?mobile menu icon

  • The browser/computer should not freeze completely! If this happens, please report it with a link to the video. Sometimes videos cannot be downloaded directly in a suitable format, so we have added the ability to convert the file to the desired format. In some cases, this process may actively use computer resources.

mobile menu iconHow can I download "Comment créer un tableau de bord des ventes sur Excel en 40mn ? Cours Excel complet tous niveaux" video to my phone?mobile menu icon

  • You can download a video to your smartphone using the website or the PWA application UDL Lite. It is also possible to send a download link via QR code using the UDL Helper extension.

mobile menu iconHow can I download an audio track (music) to MP3 "Comment créer un tableau de bord des ventes sur Excel en 40mn ? Cours Excel complet tous niveaux"?mobile menu icon

  • The most convenient way is to use the UDL Client program, which supports converting video to MP3 format. In some cases, MP3 can also be downloaded through the UDL Helper extension.

mobile menu iconHow can I save a frame from a video "Comment créer un tableau de bord des ventes sur Excel en 40mn ? Cours Excel complet tous niveaux"?mobile menu icon

  • This feature is available in the UDL Helper extension. Make sure that "Show the video snapshot button" is checked in the settings. A camera icon should appear in the lower right corner of the player to the left of the "Settings" icon. When you click on it, the current frame from the video will be saved to your computer in JPEG format.

mobile menu iconWhat's the price of all this stuff?mobile menu icon

  • It costs nothing. Our services are absolutely free for all users. There are no PRO subscriptions, no restrictions on the number or maximum length of downloaded videos.