Computerized Accounting: Module 2: Excel

Lesson 1 : Excel: the basics

We will now learn Excel and start with the basics: master the principles of the
spreadsheet and its interface before starting to create a table

1. Excel, what for?

Excel is defined as a spreadsheet. It is a software allowing to create data
tables in order to make analyzes and quickly bring out information. It is
a software very appreciated in business.
A spreadsheet is software for processing information in the form of a table that has the
ability to perform comparisons, complex calculations between the data in the cells of these
tables.
It is also very appreciated by students or individuals because it allows to carry out
schedules, scoreboards, statistical studies, task lists, budget or automated cost calculators ...
Excel can perform very complex, automated calculations and help in the decision, but it is also
a software which can be used in a very simple way at your level! This is what we will see in
this course.

[Link] interface
Excel has the same basic interface as Word, the ribbon is in the same place, but instead
of a blank sheet, is a large empty table.
Let's see the main elements of this software :
2.1 The ribbon
The ribbon is laid out the same as in Word, but the options and tools there are different,
and suitable for designing a table. There are, however, some identical tools such as bolding,
text color and alignment. The Excel ribbon contains all the tools

2.2 The formula bar

This is where we can write our mathematical formulas to create automated

calculations. We will see that a little later. The formula bar allows you to perform calculations

2.3 The table and the cells

When you open a new blank document, the main area of Excel appears as a giant
table. There is no particular formatting, just lines and columns. The goal is that you can
compose your table as you see fit! Each cell in this table is called a Cell .

Tip: Remember that a box is called a cell and that it is identified by a letter and a
number, for example A1 is the first box at the top left of the table.

1
2.4 The sheets, or tabs

Often times, Excel tables represent daily, weekly, monthly, or annual data. Instead of
creating an Excel file by time interval, tabs have been set up at the bottom of the screen. You
can therefore create as many as you want, for example a tab for each month of the year, and
duplicate your January table on the other months. The tabs allow you to manage different tables
in the same document

It is possible to rename each tab and apply a color to it. The small
button allows you to add a new tab. For now we won't need this feature but it's nice
to know it exists!

3. the fundamentals of Excel

3.1 Contact details

Have you ever played naval battle? The principle is the same here! The vertical columns
are named by letters: A, B, C ...

The lines, horizontal, are numbered: 1, 2, 3, 4 ...

So if I am talking about box C6, it is the cell (box) which is

at the intersection of column C and line 6!

To write something in a cell, you have to click or double-click on it. The selected cell
will be highlighted. You can then write text, numbers, dates ...

3.2 Movement between cells

Some keyboard keys are very useful when using Excel. It is possible to move from one
box to another using the keyboard arrows: Up, down, left, right .

When entering content in a cell, press Enter to move to the box below and
the Tab key to move to the right box. These little tips are very useful in order to avoid having
to switch all the time between the keyboard and the mouse to select a cell and enter content.

3.3 Selecting a complete line or column

2
 In Excel, always, we are going to need to select a whole column, a whole line, and for
that there is a simple technique. The Excel sheet has lines and column headers, on the left and
at the top, containing the names of the lines and columns ( A,
B , C… and 1,2,3…).

By positioning your cursor in this box, it will turn into

an arrow. Click. The entire line is selected. A click on the
column header D selects all the cells in the column. All cells
in that line or column are now highlighted in green!

3.4 Selecting a range of cells

In other cases, you will only need to

select certain cells in your table. The principle
is simple: position your mouse cursor on a
cell, click and, while maintaining the click,
move your cursor diagonally: Excel selects the
cells on a rectangle whose diagonal you have
just drawn. The selection will always have the
shape of a square or rectangle.

Cells from C4 to F9 are now selected

When you are satisfied, let go of the click. The selected boxes will appear highlighted .

Indeed, selecting a range of cells, a line or a complete column will allow you for example
to apply a background color to all the selected cells at the same time, or to apply a format to
them (say that it is a date, a value in €, in%…).

These basics are very important in order to use Excel more easily later. If you have
Excel on hand, practice a little bit before moving on to the next class, where we'll be creating
our very first table!

3
Lesson 2: My first table

1. Creation of a first simple table

1.1 Create a table

We are going to make our first table: We imagine a table that will allow, during a raffle,
to know who sold what and in what quantity in order to know the total winnings and the best
seller. You can position yourself where you want in the table to start. We first indicate the
names of the people, one person per
column. However, for our case, choose
cell B3 Then, we indicate the elements,
positioned vertically before the first
name. Here is the result:

Now we just have to fill in the

different values. Cell E7 is at the intersection of the Mbonjo column and the Milk line. This
box will therefore indicate the number of sardines sold by Raoul!

Excel was designed to automatically calculate things, but it's up to us to tell it what to
calculate. This is the subject of the next course. For now we are going to format this table.

2 Formatting the table

For now our table is not very readable, so we will see how to format it.

2.1 Apply a color theme

The first solution is to apply a theme provided by Excel . Click on Format as

table present in the ribbon of the home tab. Numerous formatting choices are then offered to
you : colors, alternating lines, borders… Many table formatting options are available to you.

Excel then asks you where the table is located, via a small window. Then select all the
boxes in your table, as we saw in the previous lesson. Usually, Excel will be able to define on
its own where the table is located in your sheet.

4
 First, position yourself on the
box furthest to the left (the one
between Ngum and Rice), click, and
while holding the click, go to the box
furthest to the right of your table (to the
left). 'intersection between (Fonmuyuy
and Biscuits): Select all the boxes of
your table (in green dotted lines)

Before validating, click on the box My table has headers, so that it understands that
Cookies, Rice… as well as Ngum, Levis… are not part of the values of the table but are titles.

Validate. And here is your beautiful picture!

Excel takes care of formatting your table on

its own

2.2 Customize your table manually

❖ Background colors

It is also possible to personalize your painting by hand. For example, select the items for
sale, then click the Fill color tool in the Font group on the Home tab. Choose a color for the
background. Our table is presented as follows:

Isn't this picture beautiful?

This formatting allows you to clearly delineate the headers and the body of the table. You can,
in the same way as in Word , center, put in bold, italics your texts in order to distinguish them
from other cells. This way it is easier to know what is a value and what is a header of the table!

5
 ❖ Borders

You can add borders to all of this to limit the different sections of the
table. The Border button offers a whole bunch of different borders: thin, wide. The All
Borders option lets you draw a sharper border on each box in your table. Try to train yourself
to create different types of borders: Many choices of curbs you are offered

Advice : Not all of the boxes you see in Excel exist when you print the document - they will be
white and borderless. You will therefore need to use the border tool to delimit your boxes
according to your needs.

Result

This is what your table can look like when personalized. This is just one example to
inspire you!

2.3 Change the size of lines and columns

You can very simply enlarge the size of the rows and columns to space your table a little
more. Click at the top of a column, where its letter is indicated, for example column G: position
your cursor at the edge (at the limit with column H): the cursor changes shape.

Click, and, while maintaining the

click, move your cursor to the right. The
Resize slider column is growing!

Click on an edge and drag to the right to enlarge

You can also first select several columns (click in the middle of the first and while
holding the click, move to other columns). Then repeat the above operation taking the edge of
any selected column.

6
2.4 Stretch values

There is a very handy little function in Excel when you make a series of consecutive
numbers. Type 1, 2, 3 in a row or column to start. Do this on another worksheet:

Step 1: write a start in a row

Step 2: select your start right away

Select the 3 cells using the mouse. They are then highlighted .

Position the cursor on the square

Notice the small black square at the bottom right of the selection. When approaching
the mouse, the cursor will change shape: from a white cross it will become a black cross,
smaller.

Step 3: click on the small square at the bottom right and pull to the right

Pull the square to the right

The technique consists of clicking on this small square, then while maintaining the click,
drag it to the right then release!

Step 4: Let go, the sequel is created !

7
 Release the click

This technique also works for dates and special series (eg 10 to 10 or only odd
numbers). Excel is able to understand the start of your list and complete it accordingly. This tip
will save you a lot of time!

2.5 Cell Formats

Excel is able to understand many formats of numbers, currencies, dates… To indicate

the type of format, select a cell (or a complete line / column) and right click , then Format cell.

You can then choose between many formats. Many formats are available: dates,
currency, percentages ...

For the currency you can choose the number of decimal places and the symbol. For dates
and times you can choose between several different display formats. Even if you choose the
full date format, so you can just type 02/01/2020 in the cell and Excel will display it
as February 1, 2020 .

It will then be easier

to do calculations if Excel
understands the type of data
you have inserted. You can
still after selecting, click on
the dialog box launcher
of the number group of the
home tab.

2.6 Inserting a line / column

Do you want to add an entry to a table without having to reorganize everything? You
will be able to simply insert a row or a column:

1. Right click on the header of the desired row or column

2. Choose Insert

3. The new line or column will be created just before

8
 4. Insert a line by right-clicking on the line header. Ditto for the columns

5. Repeat the operation as many times as you want!

2.7 Improve cell readability

Here are some very practical techniques to perfect your table:

❖ The Merging cells

It is possible to merge cells to make a bigger one, which can be useful to write a title at
the top of a table for example. To do this, select the cells to merge and then click Merge and
Center Cells in the Alignment group on the Home tab.

❖ Text alignment

When the cells are tall, you notice that the texts always stay aligned at the top. In order to
improve readability, it is sometimes useful to center them in the cell or to align them at the
bottom.

By clicking on , I get this:

9
In rather clicking on , I can give a direction that suits me.

However, if I am not
satisfied with the five proposals, I can
click on and in the dialog box that
will appear, you will choose the
orientation that suits you.

By positioning the mouse pointer here , at the end of

the small line, I can, by keeping the left click of the
mouse pressed, either: pulled up or pulled down. This
depending on the orientation I would like to give to
my text.

Once finished, I click on OK.

3. Remember to save often

If you have created a table in Excel while taking this course, now remember to save it by
clicking File > Save at the top of the window, so that you can find it later if needed. You can
also use the keyboard shortcut CTRL + S.

And There you go ! You now know how to create a simple table and format it in order to
optimize the readability of long texts and important values. Isn't it nice to work in Excel? Ah
yes it is beautiful and easy !!!

10
Lesson 3: Automated Calculations and Graphs

1. Automated calculations with Excel

Instead of inserting fixed values into a table, you can have Excel do a lot of calculations
for you. It is very convenient and it will save you time.

1.1 The formula bar

Once a cell is selected, it is either in the formula bar or in this cell that everything will
unfold.

It is in this bar that we indicate the formula to be calculated. This is where we will be
able to write formulas and use mathematical functions offered by Excel.

You can also write the formula in the

cell. However, it will appear in the formula
bar. Indeed, all the content of a cell always
appears in the formula bar.

By clicking on cell C3, it appears in the formula bar Ngum, which is indeed its
content. However what do you see?

1.2 My first Excel formula

We are going to do a very simple first calculation: an addition between 2 cells. First of
all, we will indicate 2 arbitrary values in 2 cells: Then, we will then select the next empty cell,
which will host the calculation and the result of the addition. Here is an essential point to
remember: any mathematical calculation, to be understood by Excel, must begin with the
sign = (equal).

Warning: It is imperative to put the equal sign (=) to ask Excel to do a calculation!

We can then write a mathematical formula like = 2 + 2 and we will get 4. But that is of
little interest. Instead we will ask to retrieve the value of our 2 cells.

Start the formula with the = sign (in cell D2), as we have just seen, then select the first
cell, it will take a color, add a +, then select the second cell.

Our formula looks like this = B2 + C2 : We therefore ask to calculate the sum of B2 and
C2.

11
 Thus, in addition to the value of B2 and C2, the result is displayed in D2. Validate by
pressing the Enter key on the keyboard , the result is immediate!

Now, if I modify one of the 2 values, the result is automatically recalculated!

Very practical, isn't it?

Modify one of the two values, the result updates automatically

And there you have it, you now know the basic technique for making formulas that will
allow you to automate operations and calculations. It is a simple tool, but it can be very
powerful, and save you a lot of time. This knowledge will serve you a lot in everyday life as
well as at work!

Questions: What is the difference between writing: = 15 + 20 and = B2 + C2 in a cell?

How to know if a cell displays a fixed value or the result of a formula?

1.3 Using the table from the previous lesson

We will now see this on a concrete example!

By taking the table that we have already done, we will now want to add a
new Totals line at the bottom of the table:

To make it stand out, we can put the line in Bold and if you wish to apply a text or
background color. Before embarking on calculations, it is also necessary to fill the table with
fictitious values.

We will start by selecting the cell at the intersection of Totals and Ngum . We want to
display the sum of Ngum's sales, all types of sales combined. Excel provides a formula
called Sum that lets you add up a whole range of cells.

To find formulas click on the F(x ) icon to the left of the formula bar then select Sum
and then OK . You see in the new dialog box that Excel has already selected cell range C4: C8

12
while showing you the answer. You can select this range to calculate yourself, by clicking and
dragging from C4 to C8. Validate on OK and the sum appears!

We will now be able to apply the same calculation for other people. Instead of bothering
to retype the formula, you will be able to stretch the box to the right by dragging the small
square at the bottom of the cell (as we saw in the previous lesson).

Dragging the result to the right extends the formula to other people. Because of this,
Excel understands that it must adapt the formula to the other columns (and not stay on column
C). Here is the result:

You now know the method to create

calculations and it will help you a lot !

1.4 Types of operations and formulas possible with Excel

There are many calculation formulas offered by Excel, from the simplest to the most
complex. We will see some of them :

 Addition, subtraction, multiplication, division

Excel handles all standard math operations very well. As we saw previously, you can
call the SUM ( ) function to add several cells at the same time.

For a multiplication use the star sign: * and for a division the slash sign: /

13
  Averages and Percentages

In order to further the statistical studies, Excel takes care of the averages, percentages…
The AVERAGE () function takes care of the calculation on its own.

2. Generate a graph

Generating data and calculations is one thing, but sometimes you need to have a more
simplified view of important data. This is where graphics come in! Whether they are in bars or
in sectors ... you will inevitably find your happiness to highlight the important data of your
tables.

To insert an Excel chart, first select the cells containing the values that will make sense
for the chart. In the context of our example we are going to select all the cells of our table except
for the totals line .

Then go to the Insert tab , and click on the type of chart you
want. A pie chart or histogram is suitable for the situation. In this case, we will choose the
grouped histogram.

And here's the

result :

For each product, we see the sales of each actor. The graphics are generated with a
legend, to make it easier to understand. It is located at the bottom of the graph. You can also
put a title at the top of the chart to give it meaning. You can move the graphic around in the
document, and even enlarge it by pulling the small handles in the corners.

This graph shows us some interesting information: The rice has been sold the most by
Levis. Lesley was the best performer in the sale of Biscuits.

14
 Many types of graphs are available, each adapted for a specific case. There is still a lot
to say about the graphics, but that will be for a future lesson.

Until then, try it out, have fun with it!

Sometimes we would like the results the other way around, that is, instead of seeing
the products, we would rather see the products Actors. Excel makes it easy to do this. Double
click on the graph, with the Design tab activated, click on Switch rows / columns.

It is now the actors who are displayed on the x-axis and no longer the members. We
now see the sales by product, which can also be useful to analyze the results in this specific
case.

We now see that rice was the best-selling product. And this by Paul followed by
Antoine. Soap was the least sold product (Jean).

Tip: You can also copy this graphic and paste it into a Word or PowerPoint document!

3. Filter and sort an array

I will show you one last feature that is easy to set up and very practical: Filters and sorts
of an array. Suppose we have a list of students with their GPA. It would be practical to be able
to sort the table in alphabetical order in other to have the students from A to Z, or to classify
them by decreasing GPA, in other to establish a classification.

To do this, select the cells in your table, click Sort and Filter,
then Filter. This in order to make your table filterable
and sortable. Small boxes with an arrow will appear on the headers of
your columns. Click on it to choose a sorting method!

Sort the notes in descending order. And here is the result, the
students are classified by their GPA:

The table begins with the best students

Do the same exercise by classifying the students in

alphabetical order.

15
 And that's it for Excel! There are many other functions and possibilities for this software
which can be very complex and fulfill a lot of needs. But we will stop there for the moment,
this knowledge will be more than enough for you for everyday use.

I advise you to come and re-read this course regularly to review each method in order
to understand them well.

We will, in addition to this, and it’s the Essential part of this module, make some
practices to automatize the calculations of an invoice and a sheet of management of stocks,
etc… . putting in relation at least 2 sheets …

We will do it practically

16
 Other
Lesson 1

A- DEFINITIONS OF SOME CONCEPTS OF BASE

- A workbook is an Excel document. It contains one or more worksheets, present in the form of tabs
at the bottom of the page. A workbook can contain up to 256 worksheets;
- A sheet is organized in rows and columns. The rows are identified by numbers (from 1 to 65536)
and the columns are identified by letters (from A to IV or 256 columns);
- The cell is the basic part of the sheet and is located at the intersection (meeting point) of a
row and a column. Each cell is identified by a specific reference, which refers to the row
and column numbers where it is found in a sheet. For example, in each sheet, the cell that
is at the intersection of column A and row 1 is therefore identified by its reference A1.
A cell can receive
✓ Numerical or alphanumeric data,
✓ Calculation formulas,
✓ Comments.
- The active cell is the one that appears highlighted on the screen. This is the selected cell in which
you are working. You can also see the reference of the active cell in the formula bar.

CONSOLIDATION PERIOD
All the subgroups will perform the operations previously developed.
1- What is a spreadsheet? quote some spreadsheets
2- Define the following terms: workbook, cell, worksheet, active cell
3- How is a cell referenced
4- Give the procedure for starting and stopping Microsoft EXCEL
5- After launching EXCEL , create a workbook, save it after renaming the sheet.
6- Add a new sheet named students and Delete sheet 3
7- Identify the different elements of the EXCEL screen .

Lesson 2: ENTERING AND MODIFYING DATA

I- ENTERING DATA
In MS Excel, data is entered into cells. When you enter data, it appears in the cell and in the
formula bar. The data entered can be numbers, letters, a combination of letters and numbers,
dates and calculation formulas.
To enter data in a cell:
- Select the cell which must contain the data by clicking on it with the mouse or by moving
the pointer with the arrows of the keyboard (direction key).
- Enter text, number or formula
- Validate the entry using the Enter or Tab key on the keyboard.

17
APPLICATION : entry of a simple invoice
Start Excel and enter the following values in the cells shown.
In the cell The content will In the cell The content will In the cell The content
be be will be
A3 Quantities A6 18 A8 16
B3 Description B6 Carrot bags B8 Mandarins
C3 Unit price C6 1350 C8 125
D3 Total price A7 17 A9 15
A5 15 B7 Grapefruits B9 Lemons
B5 Oranges C7 375 C9 175
C5 150
Save the workbook under the name invoice in my documents
Check the following facts:
- The text is inscribed on the left in the cells and the numbers on the right
- The text in some cells is cut for lack of space (column width)

II- CORRECTION OF DATA

For all kinds of reasons, it happens that an error occurs during data entry. The correction
consists in rewriting the content of the cell either by modifying its content or by replacing it.
1) Modify the content of the cell
To modify the content of a cell:
- Select the cell containing the entry to correct, click in the formula bar or press the F2
key on the keyboard.
- Using the editing keys (del., Backspace, arrow) delete the characters or position the
pointer and correct the text.
- Press the enter key when finished
2) Completely replace the contents of the cell
To replace the contents of a cell:
- Select the cell containing the entry to replace and enter the new cell content
- Press enter and the old content will be erased
3) Cancel an action
When correcting the data, actions may be performed unintentionally, for example deleting the
content of a cell.
As soon as a bad action has been carried out, to cancel it just:
- Click on the Edit menu then click on Cancel
- Click on the Cancel icon on the standard toolbar or use the Ctrl + Z key combination
on the keyboard

APPLICATION : open the invoice workbook created previously and make the following
corrections:
- Correct all the mistakes you may have made
- The quantity of oranges is 21
- The price of carrots is 1450

18
 - Instead of lemons, write bag of lemons
- Save the changes made

III- SELECT CELLS, LINES AND COLUMNS

We have already seen how to select a cell. Now what if we want to select several cells to apply
the same operation at the same time?
There are two cases:
- The cells to be selected follow each other: we speak of a contiguous range
- The cells to be selected do not follow each other: we speak of a non-contiguous range
1) Select a range of contiguous cells
Procedure with the keyboard:
- Select the first or the last cell of the cell range
- While holding down the Shift key on the keyboard , move the pointer over the cells that
must be part of the range with the arrows (direction keys) on the keyboard
Procedure with the mouse and keyboard
- Click on the first cell of the range
- By holding down the Shift key on the keyboard and clicking on the last cell of the range

APPLICATION : select the following cell ranges:

- From A3 to D3
- From B3 to B9
- From C3 to D7
- From A3 to D9

To deselect a selected range, simply select another cell by clicking on it.

2) Select a range of non-contiguous cells
To select separate cells:
- First select the first cell. Then press the Ctrl key on the keyboard while selecting the
other cells

APPLICATION : select the following cells

- A4, B3, A6, C7, D3
- A4, A5, A6, C4, C5, C6

3) Select rows and columns

It's not just cells that can be selected. To select a column, all you have to do is click on the letter
identifying the column. Likewise, to select a row, click on the row number.
IV- Move, copy, recopy data
1) Move data
It sometimes happens that we want to change the position of the cells, for this:
- Select the range of cells to move
- Then click on the Cut button in clipboard of Home (or make use of right click)
- Select the cell from which the data must be found

19
 - Click on the Paste button of the standard toolbar (or make use of right click)

APPLICATION: in the invoice workbook, make the following changes:

- In cell A1, enter the text VEGETABLE AND FRUITS GARDEN
- Then move it from A1 to C2

2) Copy the data

When we want to copy data from one cell to another instead:
- Select the cells of the data to copy
- Then click on the Copy button in the home tab, clipboard group
- Select the cell from which the data must be found
- Click on the Paste button in the home tab, clipboard group

3) Recopy the data

The data can only be copied when the source cell and the destination cells follow each other. In
the recopy, Excel can, if it recognizes something sequential in the source, increase each
destination cell according to the series. To copy the data:
- Select the cell or cells containing the start of the series
- Place the mouse pointer in the lower right corner of the selection, on the small black
square (called the fill handle). The cursor takes the form of a black cross
- Drag the pointer while keeping the mouse button pressed in order to select the entire
range of cells which must contain the series. Release the mouse button

APPLICATION: in the invoice workbook, select sheet 2. We will design the following table

A B C D E F
1 project start date January February March April
2 phase 1 01/01/2012 finished finished finished finished
3 phase 2 02/01/2012 finished in study active in study
4 phase 3 03/01/2012 in study active in study active
5 phase 4 04/01/2012 active in study in study in study
6 phase 5 05/01/2012 finished active in study active
NB: remember that in case of error you can cancel the action on the Cancel button
- Enter only the contents of cells A1, B1, C1, A2, B2, C2, B3, D3 and D4.
- Copy cell C1 into cells D1, E1 and F1
- Copy cell A2 into cells A3 to A6
- Select cells B2 and B3, copy the selection into cells B4, B5, and B6
- Copy cell C2 into cells D2 to F2
- Copy cell C2 into cells C3 and C6
- Copy cell D3 into the cells that should contain the same content. Do the same for cell
D4. Save Changes.

20
 V- ADD / DELETE, ENLARGE / REDUCE THE SIZE, OF A LINE OR OF A
COLUMN
1) Add or delete a row or a column
To add a column or a row:
- Select the column (or row) located to the right of the future column (or below the future
row)
- Click on the right mouse button. Then click on Insert in the contextual menu. Or in
the Insert menu click on Column or Row as appropriate
The column is inserted to the left of the selection and the row above the selection.
APPLICATION: in sheet 2 of the invoice workbook, make the following changes:
- Insert an end date column after the start date column
- Insert an empty line after line 1

To delete a column or row:

- Select the column or row to delete
- Click on the right mouse button, then click on Delete in the contextual menu. Or in
the Edit menu click on Delete.

APPLICATION: in sheet 2 of the invoice workbook remove the start date column (column B)
2) Enlarge / reduce the size of a column or row
Sometimes the text in a cell is cut off when it exceeds the width of the column. To correct it,
simply enlarge the width of the column. We have two methods:
- Place the mouse cursor on the row that marks the end of the column and the start of
the next column at the column name header . It must have the following form
- Click and keep the button pressed, move according to whether you want to enlarge or
reduce the width of the column (to the left or to the right)
- Proceed in the same way to enlarge or reduce the height of a row

Or if you want to give an exact size:

- Select the column or row to modify. Make a right click on the zone select and click on
column width and row height accordingly.
- In the dialog box that appears, enter the desired value for the width or height and
validate.
In this way we can give a common width to several columns.

APPLICATION: in sheet 2 of the invoice workbook, make the following changes:

- Increase the size of column B and reduce the size of column A
- Give column C size 13
- Simultaneously give columns D, E and F size 13
- Give lines 1 to 6 size 16

21
LESSON REVIEW EXERCISE
In sheet2 of the invoice workbook make the following changes:
1) Move the table so that it starts at cell B5
- Select the table (cell going from A3 to D9)
- Move it (cut then paste) so that it starts at cell B5

2) The customer also buys 20 fruit apples which cost the same as the oranges

- Insert an empty line after line 7

- Copy the line where the oranges are written (cells ranging from B7 to E7) and paste in the
empty line insert (in cell B8)
- Replace in cell D8 orange by fruit apples. Also correct the quantity

3) The customer no longer orders the mandarins. Delete the corresponding line.
4) Insert an empty column after column B (after the quantity column). In
cell C5 write Code.
5) In cell C7 write prod1. Copy this cell (C7) into cells ranging from C8 to C11
6) Give column D the size 20
7) Give columns C, D and E size 15
8) Give rows 7 to 11 size 18

Lesson 3: CALCULATION FORMULAS IN EXCEL

Excel is a calculation software that uses the values entered in certain cells to perform
calculations and stores the result in a cell.
The calculations are made on the basis of formulas. A formula is made up of values,
mathematical operator, cell address, built-in functions. Its role is to evaluate an expression and
display the result in the cell where it is located.
1) Construction of a formula
You must first think about your formula before typing it in the cell. I'm even recommended to
write it down on paper especially if it's complex.
A formula always starts with the equal sign: =, followed by the expression to be evaluated.
APPLICATION: Open the Excel workbook and enter the table below. Save it under the
name Average Calculation .
A B C
1 Quarter 1 12.50
2 Quarter 2 11.40
3 Quarter 3 9
4 Total
5 Annual average
6

We will calculate the total of the quarterly marks and the annual average. For it :
- Enter in B4 the formula: = 12.5 + 11.40 + 9
- Enter in B5 the formula: = 32.9 / 3
NB: the sign of the division is / and that of the multiplication *
22
 - Modify the grade value for Quarter 2 in B2 and enter the value 10.5
We note here that it is still necessary to recalculate the total and the annual average.
2) Use cell addresses in a formula
To be effective, it is important in a formula to use the addresses of cells rather than the values
in them. Thus, when modifying the content of a cell involved in the formula, Excel
automatically re-evaluates the result of the formula.
APPLICATION: In the average calculation workbook,
- Take the formula for the total of the means in B4 using the cell references. Enter the
expression: = B1 + B2 + B3
- Use the formula for the annual average in B5. Enter the expression: = B4 / 3
- Modify the content of cell B1 to 11 and that of cell B3 to 10.5.
We notice that cells B4 and B5 are automatically modifying.
3) Excel's built-in math functions
Sometimes for complex calculations it is easier to use Excel's built-in functions. For example,
if we want to find the sum or the average of the contents of 20 cells.
Some built-in Excel functions
Functions Descriptions
Sum (range) Returns the sum of the values of the given cell range
Average (range) Returns the average of the values in the specified cell range
Max (range) Returns the minimum of values in the specified cell range
Min (range) Returns the maximum of values from the specified cell range
Nb (range) Counts the number of cells containing a value within the specified
range

APPLICATION : in the average calculation workbook

- Edit formulas in cells B5 and B6 using built-in functions
✓ In B4 enter: = sum (B 1: B 3) or = sum (B1, B2, B3)
✓ In B5 enter: = average (B 1: B 3) or = average (B1, B2, B3)
- Move the table so that it starts at A2
We notice that Excel automatically corrects the formulas in B5 and B6 (for example in B5:
= sum (B 2: B 4))
- Complete the table by adding the values as indicated below:
Content

Content

Content

Content

content

content
Cell

Cell

Cell

Cell

Cell

cell

A1 TrimesterB1 John C1 Paul D1 Louis E1 Yves C2 14.5

D2 8.60 E2 11.60 C3 6.50 D3 11 E3 9.70 C4 12.80
D4 10.30 E4 9.60
- find out the total average and the annual average of Paul Louis and Yves
✓ Select cell B5 (Total of John)
✓ Copy this cell into cells C5, D5 and E5
✓ Perform the same for cell B6.
Note : when the calculation formulas are identical in the cells; you can use the copy and
recopy commands seen previously .

23
LESSON REVIEW EXERCISES
Exercise 1 :
Open the invoice workbook (click on menu-open then select the name of the workbook and
click on open).
We are going to modify the table of sheet1 and add the formulas necessary to calculate the total
of each row, the subtotal, the amounts of the discount, the tax and the final total.
1) Select cell F7 where you need to calculate the total amount of oranges. Enter the
formula: = B7 * E7
2) Copy the F7 cell into the cells from F8 to F12
3) Select the cells shown below and enter the specified values:
Content

Content

Content

Content

content

content
Cell

Cell

Cell

Cell

Cell

cell
D13 Sub D14 Discount D15 Tax D16 Total E14 10% E15 15%
total

4) Write the necessary formulas in cells F13 to F16. ( The discount and tax are calculated
on the total in

Exercise 2
Reproduce the following table. This is a timetable. Under each day of the week, write the
number of hours spent on each project.
A B C D E F G
1
2
3 Monday Tuesday Wednesday Thursday Friday Total
4 Project 1 6 6 3 4
5 Project 2 1 2 1 3
6 Project 3 2 1 3 2 1
7 Project 4 5 1 1
8 Project 5 4 1 2
9 Project 6
10 Other 4 5
11 Total
12 Max day
13 Max project

1) Enter the word Monday in cell B3. Use the copy to have the other days in cells from
C3 to F3. Perform the same operation for project 1 to project 6

24
2) In cell G4 enter the formula to obtain the total hours of the project1. Copy the formula
into cells from G5 to G10
3) In cell B11 enter the formula to obtain the total hours for a day. Copy this formula into
cells from C12 to G12
4) In cell B12 enter the formula to obtain the highest total hours per project
5) In cell B13 enter the formula to obtain the highest total hours per day
6) Save the workbook under the name schedule grid .

25