Practical Excel Course Workbook Guide
Translated by
ScribdTranslationsPractical Excel Course Workbook Guide
Translated by
ScribdTranslationsPractical Excel Course
Presentation of the workbook [Link]
It is a binder that contains 7 sheets.
List of people
Simple calculations
Balance
Car Lists
Absolute Relative
Formats
Pay
AFM Course
In each of the sheets, the data is already entered to save time.
Exercise 1 sheet 'List of people'
On the first line you will enter:
Name
First name
Date of birth
No SS
Phone number
Postal Code
City
Resize the columns based on the size of the largest data by moving the
mouse cursor between column A and column B. The cursor turns into a cross and you will click
left twice. The column will resize automatically. Do the same for the others.
columns.
Sélectionnez le tableau et quadrillez-le en cliquant sur la flèche de l’outil « Bordures » et en
selecting the full grid.
You will select the header row of columns and click on the arrow of the tool
"fill" and select the pale yellow color.
Then, without clicking elsewhere, you will change the font color by clicking on the arrow.
de l’outil « couleurs de la police » et choisir la couleur bleue.
To finish, with the same selection, click on the 'Center' tool.
You will select the 'date of birth' column, then right-click and select
"Cell Format" in the "Number" tab, left click on "Date" and choose the format with
the day of the week. Then 'OK'
Select the 'phone number' column, left click, 'Cell format'
tab 'Number' then 'Special' and select 'postal code'. To be able to display a zero for
the first 9 departments.
Do the same with the SS number by selecting 'Social Security Number'.
Select the table, then the "Data" menu "Sort" click "Yes" on "My range of
data with a title line. The first criterion is 'Name'. Then select 'Then by' by choosing
First name.
Finally, grid the board as done before.
Exercise 2 sheet 'Simple Calculations'
Simply click on the sheet name to display it.
Center the title of the first column by merging the cells from column A to A using
the "Merge and Center" tool.
Also change the font to the one you want. Then, change its size and the fill.
as above.
There is a column 'Amount' for which we will total in cell C17. Place it-
you on this cell and click on the "AutoSum (Sigma sign) tool )». The formula becomes
=SUM(C4:C16). The colon means 'from to'.
Change one or two values in the column and observe the immediate result.
Exercise 3 sheet 'Balance'
We will calculate the balance on each line. How to calculate the balance (Previous balance +
entries - exits). In cell F4 enter the following formula: =F3+D4-E4. There are 2 ways to
to do
a) While in cell C4, type the sign = then click on cell F3, type the sign +
Click on cell D4, type the minus sign – then click on cell E4. Then confirm by the
green car of the formula bar.
b) You can directly type the formula after selecting cell F3, but it is
less fast and less accurate (possible error in the cell addresses)
The balance of line 4 is displayed. It remains to copy it or drag it from F4 to F19 using the
handle symbolized by the small black cross.
Now we will sum the 'entries' column by placing ourselves in cell E20. Then in
clicking on the "AutoSum" tool, look at the blue box surrounding the 3 cells D17 D18
D19.
The accumulation would only occur on these three lines. By clicking on the top corner (right or left), we
We can pull the selection frame down to cell D3. Then, click on the green checkmark on the line.
formulas.
Do the same for the next column.
Select cells F3 to F19. Click on the "Chart Wizard" tool. Click on
"Hold to view" then on "finish". You can then modify the
parameters of this graph.
You can move the graph by holding down the left click and moving the mouse.
Exercice 4 feuille « Listes auto »
In this sheet, we will look at the facilities offered by the automation of lists. Click
on cell A3. Drag the handle down about ten lines. You will see the months go by and you will
stop when you have the desired list.
Similarly from cell B3, then from C3.
Pour les heures, il faut deux valeurs pour que Excel détermine l’écart de 30 minutes (ou de 10).
Pull the handle. Do the same starting from E3:E4.
You can create your own lists like the one in cells F3:F10 using the menu.
"Tools" "Options" tab "Custom Lists" by entering the values one by one or by taking a list
existing with the "import" tool by then selecting the cells to keep.
Exercise 5 sheet 'Absolute Relative'
We have seen that when we pull the handle of a cell, the addresses of the cells evolve.
automatically. We will calculate the net amount in cell D5 by entering the following formula
=B5*C5. Then we drag the handle of cell D5 down to D18.
In this sheet, we also want to calculate the amount of VAT and the total including tax on each line.
If we want to continue with the same method, we would need a 'VAT' column to find the rate.
on each line. We have placed this VAT rate in cell B2. But it is necessary to indicate on each
line that we want to use this cell. In cell E5 we enter the formula "=D5*B2/100".
Then, to see our mistake, we pull down the handle of E5. There are inconsistent values because the
cell B2 is not fixed.
FALSE
To make it fixed, you need to modify cell E5 by putting a $ sign in front of B and a $ in front of.
Then pull the handle of E5 and the VAT is correctly calculated for each line.
Now calculate the total including tax in cell F5 with the formula "=D5+E5" and drag down.
Exercise 6 sheet 'Formats'
In this exercise, you have at the top of the sheet the result to be obtained and at the bottom the raw data.
without any format.
a) Cell A8 "EMPLOYEE". Select cells A8 to D8. Click on the icon
"Merge Center." Fill the new cell with the "fill" tool in
taking the green color. Click on the Bold tool and change the size to 12.
b) Merge cell A9 from A9 to D9, make it bold and size 12.
c) Cell A10. merge with A11. Right click for 'cell format' menu. In
In the alignment tab and the 'Vertical' box, choose 'Centered' and set it to 12 and font
Arial. Then merge B10 to D10 and B11 to D11.
d) Merge cells A12 to D12.
e) Merge cells A13 to D13. Then right-click with 'cell format'
« Nombre » « spécial » « numéro de sécurité social ».
f) Select A8 to D13 and click on the 'Borders' tool by selecting the grid.
total.
It should look like the model above.
Exercise 7 sheet 'Payroll' and Exercise 8 sheet 'Course-AFM'
In these sheets, only add hours in the "Number of hours" column and look at them.
Calculations will do themselves. You will be able to see the details at home by taking the exercise home.