Excel laboratory workshop. Workshop on Excel

Purpose of work: MS EXCEL 2010-2013. Gaining practical skills in creating, editing and formatting tables.

Exercise: By means table processor EXCEL2010-2013 create Table1 based on the below scenario.

1. Start the spreadsheet EXCEL2010-2013 .
2. Place the cursor in a cell A1 ( click on the cell) and enter the text: Revenue from sales of book products.
3. Enter the table according to the example provided in table1.

Table 1

4. Calculate the amount of revenue from the sale of book products in June in one of two ways:

table 2

5. Extend the sum operation to a range C7:F7 in one of the following ways:

6. Make sure the operation was performed correctly:

• select a cell AT 7 =SUM(B4:B6);
• select a cell C7. The formula bar should display the following expression: =SUM(C4:C6).

7. Calculate the total revenue from the sale of book products (column Total). For this:

8. Calculate the amounts in the remaining cells of the column Total. To do this: grab the cell G4 the lower right corner (autofill area) and, without releasing the mouse button, drag it to the cell G 7. B cells G5, G6, G7 total revenue from the sale of book products will appear.

9. Determine the share of revenue received from the sale of consignments of goods. For this:

10. Calculate the revenue share for other table rows using autocomplete.

As a result of autofill in cells H5, H6 and H7 a message will appear #DIV/0!(division by zero). This result is due to the fact that the denominator of the formula is relative the address of the cell, which, as a result of copying, will be shifted relative to the cell G 7 ( G8, G9, G 10 — empty cells). Change relative cell address G7 on absolute — $ G$7, This will lead to receiving correct result accounts. Try again to calculate the revenue share as a percentage. For this:

• clear range H4:H7;
• select a cell H4;
• enter the formula = G4/$ G$7 ;
• press the key Enter;
• Calculate the revenue share for other table rows using autocomplete.

As a result, in the range cells H4:H7 the percentage of revenue will appear.

11. Design the table as you wish.

12. Open Yandex.Disk and in the folder Documentation create a folder Excel.
13. Save the created table in a folder Yandex.Disk→ Excel under the name Student_name Assignment No.

14. Go to Execution 2 .

Invite friends to my site

Support the project! Select one of the payment options:

From a card, from a cell phone balance, from a Wallet

Thank you!

Practical work inMS E xcel

Laboratory workshop created for practical learning section, calculations in “SpreadsheetsMSExcel- 2007" within the discipline "Information technologies in professional activity» second-year students of various specialties of the State Budgetary Educational Institution of Secondary Professional Education Polytechnic College No. 42, Moscow.

The workshop consists of four practical works on the main topics of applicationMS E xcelin calculations, is aimed mainly at students studying in the specialties “Economics and Accounting (by Industry)”, “Operational activities in logistics" and "Installation and technical operation industrial equipment (by industry)". Some topics of practical work can be used in training by students of other specialties.

Each practical work contains theoretical information on the topic of work, detailed analysis test example and 3 variants of tasks on the topic.

1. Practical work

Subject:"Organization of settlements inMSExcel»

Purpose given practical work is to master the technology of organizing tables inMSExcelnamely copying, formatting cells, creating borders, presenting data, and organizing simple formulas calculations. Figure 1 shows a table in whichcolumn A organized by copying cell contentsA4 (date 04/01/13) down to the required cell,columns B And C filled with source data, also using copying and subsequent editing of values,column D , created by organizing a formula into a cellD4 (in the formula line, the type of formula is shown) and then copying it down.

Fig.1

The table in Fig. 2 is similar in creation to the previous table, with the addition of generating totals by column and row. The formula can be written differently.

Fig.2

Task optionson this topic« Organization of settlements inMSExcel»

Exercise 1 . Create a table according to task 1. ColumnMonth organize by copying cells the next three columns with the original data, fill and format the data in these columns. ColumnSupplement amount , create via formula.

Task 2 . Create a table according to task 2. Organize the column by copying cells.

Task 3 . Create a table according to task 3. Organize the column as follows, from the beginning fill in the value 1.0 in the cellI 4 and 1.1 per cell I5, then select a range of cells consisting of cellsI 4, I5 and copy the selected range down.

1. Practical work

Subject:"Statistical functions"

Purpose This practical work is to become familiar with the built-in statistical functions.

When processing statistical data quite often there is a need to determine various statistical characteristics. For such calculations inMSExcelA number of statistical functions are built in, for example:

AVERAGE(x 1 ,…,x n)

arithmetic mean (x 1 +…+x n )/n.

MAX(x 1 ,…,x n )

maximum value from the set of arguments (x 1 ,…,x n )

MIN(x 1,…,x n)

minimum value from the set of arguments (x 1 ,…,x n )

COUNT(x 1,…,x n)

number of numbers in argument list

COUNTA(x 1 ,…,x n )

number of values ​​in the argument list and non-empty cells

An example of completing a task using

statistical functions

Figure 4 shows a table of product sales in a store.

Fig.4

Note . An empty cell in the Sales Quantity column means that this product was not sold.

Guidelines to complete the task:

Calculate:

• revenue from sales of each product;

  total, average, maximum, minimum revenue from sales of all goods;

  determine the total number of types of goods in the store,

  how many types of goods were sold.

An example of completing a task on the topic “ Statistical functions»

enter in cell D2(in the first cell of the “Sales Revenue” column) formula: =B2*C2 (“Sales revenue” = “Price” * “Number of sales”);

copy the formula to the entire column;

enter formulas:

in D5 =SUM(D2:D4) - total revenue

in D6 = AVERAGE(D2:D4) - average revenue

in D7 =MAX(D2:D4) - maximum revenue

in D8 =MIN(D2:D4) - minimum revenue

in D9 =COUNT(A2:A4) - number of types of goods

(counting the number of non-blank values)

in D10 = COUNT(C2:C4) - the number of types of goods sold (counting the number numerical values)

Task optionson this topic"Statistical functions"

Exercise 1 . Organize the table “Rivers of Eurasia”.

Fig.5

Task 2 . The ages of ten people applying for vacancies in the company are known. Determine the maximum, minimum, average age of applicants?

Task 3 . The table contains information about the company's employees: last name, length of service. Determine the average, maximum, minimum length of service. How many employees are there in total?

1. Practical work

Subject:"Logical functionIF…»

Purpose This practical work is to become familiar with the most common function in logical expressions.

In practice, Boolean expressions are used to developbranching algorithm :

Algorithmic language

If condition ( logical expression)

action 1

otherwise

action 2

all-if;

condition

action 1

action 2

Block diagram

To build a branch inMSExcel has a logical IF function, its structure is as follows:

IF value of logical expressionTRUE ,

THAT statement 1 is executed ,

OTHERWISE statement 2 is executed .

Rice. 5 .

Example of specifying arguments to the IF function

(finding the maximum value of two numbers)

To call a functionIF , you have to press the buttonf x "Insert Function" located in the formula bar. will appearFunction Wizard in a cell Category you need to select a linebrain teaser and then select the functionIF , fill in three cells:

Logical expression

Value_if_true

Value_if_false

Figure 7 shows an example of using the functionIF Fig 7.

Task optionson this topic« Logical function IF...»

Exercise 1. In a cell D8 set the value to 800, that is, make Plan = Fact for V.V. Serov. Explain why the result has not changed?

Task 2 . Column A is an arbitrary number with a value of about 1000, column B is 2% of the number, column C (result), a logical IF function, provided that the number is greater than or equal to 1000, then the result will be = number + 2%, otherwise = number – 2%. Figure 8 shows the table.

Fig 8_1.

Task 3 . Column E – first number, columnF– second number, columnG(result), is formed as follows: if number1 is greater than number2, then the result will be their sum, otherwise the result will be their difference. Figure 8_2 shows the original table with the result.

Fig 8_2.

1. Practical work

Subject:"Histograms, graphs"

Purpose This practical work is to master the technology of presenting data in the form of diagrams inMSExcel. To generate histograms, initial data is required, then depending on the versionMSOffice, select the Insert menu and the right type histograms (graphics). Before inserting a chart, it is recommended to be in any cell of the source table with data. Fig 9_1.

The following figure is Fig 9_2. a diagram has been generated - a graph of functions

y= sin(x), y= cos(x), y= x 2 (parabola). To generate graphs, a column of values ​​is requiredX. Values ​​are formed from -6.28 to 6.28 in increments of 0.1 Columns for formationsin(x), cos(x) are selected via function insertion. The column for the parabola is organized by formula. Fig 9_2.

Task optionson this topic« Histograms, graphs"

Exercise 1 . Organize pie chart, according to Fig. 9_1.

Task 2 . Organize the graph of a functiony= x^3 (cubic parabola).

Fig 9_3

Task 2 . Organize changes in the dollar exchange rate against the ruble.

Tikhomirova A.A.MS Excel .

Workshop PRACTICAL TASK No. 1

Building a table

To complete the task, use the table (Fig. 1) as a sample.

Figure 1 - Visit record form

Enter the text “Statement” in cell A1

Enter in cell A2 the text “recording of visits to a clinic (outpatient clinic), dispensary, consultation at home”

Enter the text “Name and specialty of the doctor” in cell A3

Enter the text “for” in cell A4

Enter the text “workshop number” in cell E5

Create a table header:

Enter the text “Days of the month” in cell A7

Enter in cell B7 the text “Inspection accepted at the clinic - total”

enter the text “Including regarding diseases” in cell C7

enter the text “Home visits made” in cell E7

enter the text “Including children under the age of 14 inclusive” in cell F7

enter the text “adults and teenagers” in cell C8

enter the text “children under the age of 14 inclusive” in cell D8

enter the text “about diseases” in cell F8

enter the text “preventive and patronage” in cell G8

enter the text “A” in cell A9

number the remaining columns of the table

Format the table header as shown

PRACTICAL TASK No. 2

Calculations in tables. Autosum.

In the table built in the previous task, fill the columns with arbitrary data

In line 15, form the line TOTAL:(in cells B15, C15, D15, E15, F15 and G15) use AutoSum.

PRACTICAL TASK No. 3

Calculations in tables. Formulas

Construct and format the table according to the example shown in Fig. 2, leaving cells I6:J9 blank in columns 9 and 10 of the table.

Figure 2- Payroll calculation using formulas

Enter the calculation formula in cell J6 Amounts to be issued excluding tax: =G6+H6

Enter the formula for calculation Tax(column 9) : =$E$3*(G6+H6)

Copy the formula into the cells of the range I7:I14, pay attention to the automatic changes in the formulas that occur when copying

Change the formula in cell J6: = G6+H6-I6

Copy the formula into the cells of the range J7:J14, pay attention to the automatic changes in the formulas that occur when copying

Calculate the total values ​​in cells G16, I16, J16 using Autosum

Calculate the average of the Salary column in cell G18 using Function Wizard and the AVERAGE function (category Statistical).

Formula: = AVERAGE (G6:G14)

Copy the formula into cells I18 and J18, pay attention to the automatic changes in the formulas that occur when copying

PRACTICAL TASK No. 4

Building charts

Construct and format the table according to the example shown in Fig. 3.

Figure 3 - Chart table

Create diagrams based on the table data:

histogram of the dynamics of changes in the primary incidence of socially significant diseases in the population of St. Petersburg in the period 2006-2010.

graph of the dynamics of changes in the primary incidence of dysentery in the population in St. Petersburg in the period 2006-2010.

PRACTICAL TASK No. 5

Logical function IF

Convert the table from task No. 3 to the form in Fig. 4 by creating and filling out the column “Percentage of Plan Fulfillment”, and also set the bonus amount to 15% in cell H3.

Calculate the values ​​in the Premium column using the built-in logic IF function, based on the following condition:

“employees who exceed the plan receive a bonus of 15% of their salary.”

Recalculate the columns “Tax”, “Amount to be issued”, total and average values ​​in accordance with the changes in the table.

Compare the results obtained with the table in Fig. 5.

Figure 4 - Changes to the table of task No. 3

Figure 5 - Result of task 5

PRACTICAL TASK No.6

Calculations in tables. Formulas.

Using formulas that contain nested functions

Construct and format the table according to the example shown in Fig. 6.

Figure 6 – Table for determining student testing results

Using logical functions, create a formula for automatically determining student grades in accordance with the points scored, based on the following conditions:

Each student is asked to answer 100 questions. One point is awarded for each answer.

Based on the test results, grades are given according to next criterion: from 90 before 100 points - score " Great", from 75 before 89 - « Fine", from 60 before 74 – « satisfied.", from 50 before 59 - « unsatisfactory." , before 49 - « unit", less 35 - « zero" In other cases, the message “ error».

Before performing calculations, draw up an algorithm for solving the problem in graphical form.

3. Calculate GPA, setting the output of its value as an integer.

4. Sort the data contained in the table in descending order of points scored.

5. Compare the results obtained with the table in Fig. 7.

Figure 7 - Result of task 6

Goal of the work: development of practical skills in working with MS Excel 2010 spreadsheets

• Educational: generalization and consolidation of knowledge and practical skills in creating and designing tables, diagrams, and organizing calculations.
• Educational: development of attention and independence when working with a software product.
• Cognitive: development of interest in solving problems and interdisciplinary connections, strengthening cognitive motivation.

Equipment: computer class, software– MS Excel 2010.

Practical work No. 1
"Creating and formatting a table"

Purpose of work: Learn to format a table using the Format Cell command.

• Create new file. Give the first sheet a name Earth and make a table according to the example (Arial font, size 14):

2. Set the data format. Select cell C2 (place the cursor in it) with the right mouse button and select from the context menu Cell format... In the tab Number select format Numerical , the number of decimal places is 0. Click OK. In cell C2, type 149600000.

Similarly, select cell C3 and set the format Numerical , the number of decimal places is 0. In cell C3, type 384400.

Select cell C4 and set the format Time . In cell C4, type 23:56:04.

Select cell C5 and set the format Numerical , the number of decimal places is 3. In cell C5, type 365.256.

Select cell C6 and set the format Numerical , the number of decimal places is 1. In cell C6, type 29.8.

3. Format the table.

Merging cells. Merge the range of cells A1:C1 (cells A1, B1, C1). To do this, select the indicated cells with the left mouse button and context menu select Cell format... tab Alignment . Check the box in the line merging cells .

Alignment in a cell. Select in line horizontally in the dropdown list - horizontally meaning in the center .

In line vertically in the dropdown list - in the center .

Change the width and height of cell A1 using the left mouse button.

Write in several lines. Select cells B2:B6 with the left mouse button and select from the context menu Cell format... tab Alignment . Check the box in the line translate according to words . Nothing has changed in the table, because all information fits within the width of the cell. Reduce the width of Column B so that the text appears as shown in the example below. (If not all of the text in a cell is visible, it means that it is located outside the cell border - you need to increase the height of the cell using the left mouse button.)

The table will look like

Setting cell borders. Select cells A2:C6. From the context menu, select Cell Format tab Borders . Set external and internal boundaries.

The finished table will look like this:

4. Save the table in your folder under the name tables .

Practical work No. 2
"Building diagrams"

Goal of the work: Learn to create and format charts

Exercise 1.

1. Open the file tables . Give the second sheet the name Fur and make a table according to the example given.

2. Select the data range A2:Bll, including row and column headers

3. Select the Insert tab, the Chart command group, select the chart type - Histogram – Volume Histogram – Clustered Histogram.

4. Changing the name of the diagram.

After selecting the chart, the Working with Charts command line will be activated. Select in the Layout tab – Chart Title – Above Chart. Click on the chart title, erase the old title and type a new one Durability of the best quality furs. The diagram will look like

5. Chart formatting.

Right click on free space area of ​​the diagram and select the command from the context menu Chart area format. Set the design options you like. Approximate result

Task 2.

1. Go to the next sheet and give it a name Caves. Create a table using the following example:

2. Make a diagram. Approximate view diagrams

Task 3.

1. Go to the next sheet and name it Graph

2. Construct a type diagram Schedule and format the chart (to create axis labels and data labels, use the Chart Tools - Layout tab). Approximate view of the diagram

Practical work No. 3
"Table Autofill"

Goal of the work: learn how to fill table cells using the AutoFill function.

The AutoFill function allows you to automatically continue a series of cells if the filling of the latter follows a certain principle (arithmetic progression, days of the week, months). MS Excel searches for the filling rule, the entered data in order to determine the values ​​of empty cells. If you enter one initial value sample of filling, then one cell is selected; if the list has a data change interval, then it is necessary to select two cells filled with the corresponding data.

Exercise 1.

1. Open the file tables autocomplete.

2.Autofill numbers. In cell A2, type the number 1, and in cell A3, the number 2. Select cells A2 and A3. Drag the fill handle with the left mouse button to cell A7.

3.Fill the cells with the days of the week. In cell B1, type Monday. Drag the fill handle with the left mouse button to cell F1.

4. Fill in the remaining cells and format the table as shown.

Task 2.

1. Open the file tables. Go to new leaf and give it a name temperature.

2. Using the function Autocomplete, create a table based on the example.

3. Save the file.

Practical work No. 4
on this topic"Computing inMSExcel 2010"

Goal of the work: develop the skills of creating, editing, formatting and performing simple calculations in spreadsheets.

Calculations in MS Excel tables are carried out using formulas. The formula always starts with an = sign. The formula can contain numbers, cell addresses, mathematical signs and built-in functions. Parentheses allow you to change standard order performing actions. If a cell contains a formula, the worksheet displays the current result of that formula. If you make a cell current, the formula itself is displayed in the formula bar.

The rule of using formulas in MS Excel is that if the value of a cell really depends on other cells in the table, you should always use a formula, even if the operation can easily be performed in your head. This ensures that subsequent editing of the table will not violate its integrity and the correctness of the calculations performed in it.

Exercise 1.

1. Open the file tables. Create a new sheet and give it a name square.

2. Prepare a sheet for calculating the area of ​​a rectangle according to the sample

3. Set cells B2, B3, B4 to a number format (one decimal place).

4. In cell B2 enter the number 6, in cell B3 enter the number 7.

5. The area of ​​the rectangle is calculated in cell B4. Place the cursor in it. In order to calculate the area of ​​a square, you need to multiply the length of the first side of the rectangle by the value of the second side of the rectangle, i.e. multiply the value of cell B2 by the value of cell B3. Enter a formula in cell B4. For this

• print the sign = ;
• left-click on cell B2;
• print the multiplication sign *;
• left-click on cell B3;
• click Enter key.

The cell will display the result of the calculation using the formula =B2*B3, the number 42.0.

6. Change the value in cell B2, see what has changed. Change the value in cell B3 and see what has changed.

Task 2.

perimeter of a square.

2. Draw up a sheet for calculating the perimeter of a square according to the sample

3. Enter any number in cell B2

4. Enter the formula for calculating the perimeter in cell B3.

5. See the result.

Task 3.

1. Create a new sheet and give it a name amount of information.

2. The amount of information in bytes is known. Prepare a sheet to calculate the amount of information in other units of measurement of information.

Task 4.

1. Create a new sheet and give it a name geography.

2. Draw out a sheet for calculation according to the sample and fill in the empty cells of the table.

Literature.

1. Vasiliev A.V. Working in spreadsheets: workshop / A.V. Vasilyev, O.B. Bogomolova. – M.: BINOM. Knowledge Laboratory, 2007. – 160 p.
2. Zlatopolsky D.M. 1700 tasks for Microsoft Excel/ – St. Petersburg: BHV-Petersburg, 2003 – 544 p.

MINISTRY OF AGRICULTURE OF THE RUSSIAN FEDERATION

FSBEI HE "VYATSK STATE

AGRICULTURAL ACADEMY"

Department information technologies and statistics

Livanov R.V.

Workshop on working in spreadsheet Microsoft Office Excel 2007

for students of the Faculty of Economics

KIROV Vyatka State Agricultural Academy

Copyright by Livanov Roman, 2002-2014

Practical part

Laboratory work No. 1.

General familiarity with Microsoft Excel

Launch Microsoft Excel: “Start” “All Programs” “ Microsoft Office» "Microsoft Office Excel 2007" or using the corresponding shortcut on the desktop. This will open a new workbook containing several worksheets.

In the window that opens, find the following elements:

Copyright by Livanov Roman, 2002-2014

Exercise 1. Basics of working with spreadsheets.

1. Rename the worksheet title.

Right-click on the “Sheet1” shortcut at the bottom of the worksheet and select the command from the context menu "Rename".

Delete the old worksheet name, enter the new name “Printers” from the keyboard and press Enter.

2. Prepare table cells for input of initial data.

Select the cell range A1:D1 and set the context menu command

"Cell Format".

"translate according to words" and select the type of horizontal and vertical alignment - center.

On the Font tab of the dialog box, select the font style type -

bold italic and press the button"OK" .

3. Fill out the table with data according to the sample below.

4. Calculate sales volume as the product of quantity and price.

Select cell D2 and enter the = sign from the keyboard.

Click LMB on cell B2, enter the sign * from the keyboard and click LMB on cell C2. If everything is done correctly, the formula will appear in the formula bar the following type:=B2*C2 .

Press the Enter key - the result of the calculation using the formula will appear in the cell:

450000.

Copyright by Livanov Roman, 2002-2014

5. Copy the formula into the remaining cells of the column.

Select cell D2, which contains the result of the calculation.

Place your mouse cursor over the fill marker (the small square in the lower right corner of the selected cell).

Press LMB and, holding it, drag the cursor up to the 6th line inclusive. If everything is done correctly, then all cells of the “Sales Volume” column will be filled with calculated values.

6. Set for numbers in columns“Price” and “Sales Volume” monetary format.

Select the range of cells C2:D6 and set the context menu command

"Cell Format".

monetary, number of decimal places –0, designation –р. and click OK.

7. Insert a new column into the table.

LMB select any cell of the first column (for example A2

or A3).

"Insert columns into sheet"– as a result to the left of the table a new column will appear.

In cell A1, enter the title of the new column No. and set the horizontal and vertical alignment– in the center,

font type – bold italic.

8. Fill in the column“Item No.” using auto-completion.

In cell A2 enter number 1, in cell A3 - number 2.

Select the range of cells A2:A3.

Hover the mouse cursor over the fill marker in the lower right corner of the selected cells, click LMB and, while holding it, drag the cursor to the 6th

lines inclusive. As a result, numbers from 1 to 5 will appear in the column.

Copyright by Livanov Roman, 2002-2014

9. Insert into table new line to design the table title.

Select any cell of the first row by clicking LMB (for example B1

or C1).

On the Home tab, click the Insert button and select the command from the drop-down list "Insert rows into sheet"– as a result above the table a new line will appear.

Select the range of cells A1:E1 and set the context menu command

"Cell Format".

In the dialog box that appears, on the Alignment tab, select the checkbox "merging cells", select the horizontal alignment type

– in the center.

On the “Font” tab, select the font type – bold,

font color is red and click OK.

In the merged cell, enter the table title: Printer sales volume.

10. Set the border for table cells.

Select all table cells except the header (range

A2:E7) and set the context menu command “Format Cells”.

In the dialog box that appears, on the “Border” tab, select the type

11. Set the fill for table cells.

Select the table header (range A2:E2) and set the context menu command “Format Cells”.

In the dialog box that appears, on the “Fill” tab, select a cell fill color and click “OK”.

Fill the rest of the table in the same way using different colors.

Copyright by Livanov Roman, 2002-2014

12. Build a Column Chart "Product names" And

"Quantity".

Select the range of cells B2:C7, enter the command "Insert" "Histogram" and select the histogram type from the drop-down list –

histogram with grouping(first pattern in first line) – in

As a result, a diagram will be constructed.

On the Design tab, click the button "Row column"–

V As a result, the histogram will change the way the data series are displayed.

On the Layout tab, click "Chart title", V

in the drop-down list, select the placement of the name "Above the diagram"

and enter a title for the diagram Printers.

Using the button "Data signatures" on the "Layout" tab, set

V diagram numerical labels of data series with placement"In the center" .

On the Design tab, click the Move Chart button.

V In the dialog box that appears, select the placement of the charton a separate sheet and click the “OK” button - as a result, workbook A new worksheet will appear called “Diagram1”, on which the diagram will be placed.

13. Calculate string“Total” for the “Sales volume” column.

Go to the Printers worksheet, which contains a table with data.

Enter Total in cell B8, and put dashes in cells C8 and D8.

Place the cursor in cell E8 and click on the AutoSum button on the “Home” tab - as a result, a formula will appear in the cell

SUM(E3:E7).

Press the Enter key - as a result, the contents of the cells covered by the dotted frame will be summed.

Set the “Total” line to a border and your own fill color.

Copyright by Livanov Roman, 2002-2014

14. Change data in a table by column"Quantity" .

Select cell C3 and enter the value 30 in it - after pressing the key

Enter will automatically recalculate the values ​​in the “Sales volume” column.

Select cell C7, enter the value 7 in it and press Enter.

Make sure that due to changes in data in the table, the chart is rebuilt to take into account the new values.

As a result of all the above actions, formatted

the table should look like this:

Task 2. Using conditional formatting in calculations.

1. Go to the new worksheet “Sheet2” and give it a name

"Finance".

2. Select and merge the range of cells A1:E1, set horizontal alignment–center and enter the table title:

Flow of funds.

3. Select the range of cells A2:E9 and set external and internal borders for the selected cells using the button and template “All borders” on the “Home” tab.

Copyright by Livanov Roman, 2002-2014

4. Design the table header.

In cell A2, enter Month.

In cell B2, enter At the beginning of the period.

In cell C2, enter Revenue.

In cell D2, enter Expenses.

In cell E2, enter End of period.

Select the range of cells A2:E2 and set the display for them -

translate according to words, horizontal and vertical alignment –

centered, font style is italic.

5. Fill in the column with data"Month" using autocomplete.

In cell A3, enter the name of the month January.

Move the mouse cursor over the fill marker of cell A3 and, while holding LMB,

drag the cursor up to the 8th line inclusive. As a result, the names of the months from January to June will appear in the column.

In cell A9, enter Total for the half year and set this cell to word wrap.

6. Fill the table cells with the original numerical data.

In cell B3, enter the value 1000.

Fill in the data in the “Income” and “Expenses” columns according to the sample below.