Work in excel with formulas and tables for dummies

The formula prescribes to the program Excel the procedure with numbers, the values in the cell or group of cells. Without formulas spreadsheets are not needed in principle.

The structure of formula includes: constants, operators, references, functions, range names, parentheses are contained arguments of other formulas. Let us consider the practical application of the formulas for newcomer users.



The formulas in excel for dummies

For setting the formula for the cell, it is necessary to activate it (to put the cursor) and insert the equal sign (=). Similarly you can insert the equal sign in the formula bar. After putting, press Enter. The result of calculations will appear in the cell.

In Excel employs standard mathematical operators:

Operator	Operation	Example
+ (Plus sign)	Addition	=В4+7
- (Minus sign)	Subtraction	=А9-100
* (Asterisk)	Multiplication	=А3*2
/ (Slash)	Division	=А7/А8
^ (Circumflex)	Degree	=6^2
= (Equal sign)	Equal
<	Less
>	More
<=	Less than or equal
>=	Greater or equal
<>	Not Equal

The symbol «*» is used necessarily during multiplying. To pass it, as it is accepted in written arithmetic calculation is unacceptable. That is the record (2+3)5 Excel won't understand.

The program Excel can be used as a calculator. That is insert numbers in the formula and operators of mathematical calculation and get the result immediately.

But more often entered addresses of cells. That is, a user enters the reference of the cell with the meaning which will operate the formula.

During changing the values in the cells the formula automatically recalculates the result.

References can be combined in one formula with simple numbers.

The operator multiplies the importance of B2 in 0,75.

In our example:

1. Insert the cursor in B3 and enter «=».
2. Click on the cell B2 – Excel it (the name of cell appeared in the formula, around the cell formed a rectangle).
3. Input the symbol «*», setting 0. 5 from the keyboard and pressed ENTER.

If in the same formula is used several operators, the program will process them in the following order:

• %, ^;
• *, /;
• +, -.

For changing the sequence you may by parentheses: in the first order Excel calculates the cost of the expression in parentheses.



How in the excel formula to assign the constant cell

There are 2 types of cell references: relative and absolute ones. When copying the formula those references reveal itself in different ways: the relative references changing, absolute references remain constant.

All cell references the program considers as relative, if the user has not settled another criterion. With relative references you may multiplied the same formula to several rows or columns.

1. Manually complete in the first graphs of the training table. We have this option:

2. Recollect from math: to determine the cost of a few items, you need the price for 1 unit multiplied on the quantity. For calculating the cost, need to enter a formula in the cell D2: = unit price * quantity. Constants formula – are cell references with the appropriate importance’s.

3. Push ENTER – and the program displays the importance of the multiplication. The same procedure should be done for all cells. How in Excel to install the formula for a column? You need to copy the formula from the first cell in other rows. Relative references - in helping ones.

To find in the lower right corner of D2 of the column the marker of autocomplete. You need to click on this point with the left mouse button, hold it and down the column.

In order to release the mouse button - the formula is copied to selected cells with relational references. That is, in each cell will be the formula with their argument.

Links in the cell are associated with the line.

Formula with the absolute reference refers to the same cell. That is when you AutoFill or copy something, the constant is unchanged (or constant).

For pointing to Excel on the absolute reference, the user must place a dollar sign ($). The easiest way to do this with helping the F4 key.

1. Create a row «total». Find the total cost of all goods. Allocate numerical relevance’s of the amount column «value», plus one more cell. This is the range D2:D9.

2. Use the autocomplete function. The button «AutoSum» is on the tab «HOME» in the tool group «Editing».
3. After clicking on the icon «AutoSum» (or combination of keys ALT+ «=») folding the selected number and displaying the result in the empty cell.

Create one more column, where we calculate the share of each good in the total cost. To do this:

1. Devide the cost of a single product on the cost of all goods and multiply the result by 100. The cell reference with the meaning of of the total cost must to be absolute, that at copying it will stay unchanged.

2. To get percents in Excel, don`t have to multiply the quotient on 100. Allocate the cell with the result and press «Percent Style» (CTRL+SHIFT+%). Or press the hot key combination: CTRL+SHIFT+5.

3. Copy the formula throughout the entire column: only changes the first calculated value in the formula (a relative reference). The second one (an absolute reference) remains the same. To test the correctness of calculations and to find the result. 100%. All right.

When creating formulas, used the following formats of absolute links:

• $B$2 – when copying column and row stay constant;
• B$2 – when copying a string stay constant;
• $B2 – the column is not changed.

How to make the table in excel with formulas

For saving time with the introduction of similar formulas in table cells, applyed the markers autocomplete. If you need to fix the link, make it absolute. For changing the values during copy a relative reference.

The simplest formulas of filling tables in Excel:

1. Before the names of goods we will put one more column. Allocated to any cell in the first column, click with the right mouse button. Click «Paste». Or press at first the key combination CTRL+SPACEBAR to choose an total column of the sheet. And then the combination: CTRL+SHIFT+«+» to insert a column.
2. Let's call the new column «№». Put in A1 number «1», A2 – «2». To allocate the first two cells with the left mouse button the marker autocomplete – pull down.

3. The same rule may be filled, for example, dates. If the intervals between them the same – a day, a month, an year. Enter in G1 «oct.15», in the second «nov.15». To allocate the first two cells and for a marker down.

4. To find the average price of the goods. Allocate the column with the prices + one more cell. Open menu of button «AutoSum» – select a formula for auto calculation of the average value.

To test the correctness, make double-click on the result.

Formulas in Excel for dummies: rules of introduction, operators, using of absolute and relative references. How to set formula for column? Autocomplete of the tables.