Hello!
Many people who do not use Excel do not even know what opportunities this program provides! ☝
Just think: automatically add values from one formulas to others, search for the required lines in the text, create your own conditions, etc. - in general, in fact, a mini-programming language for solving "narrow" problems (to be honest, I myself have not considered Excel as a program for a long time, and almost never used it).
In this article I want to show several examples of how you can quickly solve everyday office tasks: add something, subtract something, calculate the amount (including with a condition), substitute values from one table into another, etc.
That is, this article will be something mini guide by learning the most necessary for work (more precisely, to start using Excel and feel the full power of this product!).
It is possible that if I had read a similar article 17-20 years ago, I myself would have started using Excel much faster (and would have saved a lot of my time for solving "simple" tasks ... 👌
*
Excel Basics Tutorial: Cells and Numbers
Note: all screenshots below are from Excel 2016 (as one of the newest to date).
Many novice users, after starting Excel, ask one strange question: "Well, where is the table here?"... Meanwhile, everything cellswhat you see after starting the program - this is one big table!
Now to the main thing: in any cell there can be text, some number, or a formula. For example, the screenshot below shows one illustrative example:
- left: cell (A1) contains the prime number "6". Note, when you select this cell, the number "6" is shown in the formula line (Fx).
- on the right: in cell (C1) it looks like a simple number "6", but if you select this cell, then you will see the formula "=3+3" - this is an important feature in Excel!
Just a number (left) and a calculated formula (right)
👉 The bottom line isthat Excel can count as a calculator if you select a cell and then write a formula, for example "= 3 + 5 + 8" (without quotes). You do not need to write the result - Excel will calculate it itself and display it in a cell (as in cell C1 in the example above)!
But you can write in formulas and add not just numbers, but also numbers that have already been counted in other cells. In the screenshot below, in cell A1 and B1, the numbers 5 and 6, respectively. In cell D1, I want to get their sum - you can write the formula in two ways:
- the first one: "= 5 + 6" (not very convenient, imagine that in cell A1 - our number is also calculated according to some other formula and it changes. You won’t substitute a number instead of 5 every time ?!);
- the second: "= A1 + B1" - but this is ideal, just add the value of cells A1 and B1 (even if there are numbers in them!).
Adding cells that already have numbers
Extending the formula to other cells
In the example above, we added the two numbers in column A and B in the first row. But we have 6 lines, and most often in real problems you need to add numbers in each line! To do this, you can:
- in line 2 write the formula "= A2 + B2", in line 3 - "= A3 + B3", etc. (this is long and tedious, this option is never used);
- select cell D1 (which already has a formula), then move the mouse pointer to the right corner of the cell so that a black cross appears (see the screen below). Then hold down the left button and stretch the formula to fill the entire column. Convenient and fast! (Note: you can also use Ctrl + C and Ctrl + V combinations for formulas (copy and paste, respectively)) .
Stretching a formula
By the way, please note that Excel has substituted the formulas for each row itself.That is, if now you select a cell, say, D2, you will see the formula "= A2 + B2" (i.e. Excel automatically substitutes the formulas and returns the result immediately) .
How to set a constant (a cell that will not change when copying a formula)
Quite often it is required in formulas (when you copy them) that some value does not change. Let's say a simple task: convert prices in dollars to rubles. The cost of the ruble is set in one cell, in my example below it is G2.
Next, in cell E2, write the formula "= D2 * G2" and get the result. Only if we stretch the formula, as we did before, we will not see the result in other lines, because Excel will put the formula "D3 * G3" on line 3, "D4 * G4" on the 4th line, and so on. It is necessary that G2 remain everywhere G2 ...
To do this - just change cell E2 - the formula will look like "= D2 * $ G $ 2". Those. dollar icon $ - allows you to set a cell that will not change when you copy the formula (i.e. we get a constant, example below).
Constant / in a formula cell does not change
*
How to calculate the amount (SUM and SUMIFS formulas)
You can, of course, create formulas manually by typing "= A1 + B1 + C1" and the like. But Excel has faster and more convenient tools.
One of the easiest ways to add all selected cells is to use the option auto sums (Excel will write the formula itself and paste it into the cell).
What you need to do to calculate the sum of certain cells:
- first select the cells (see the screen below 👇);
- then open the "Formulas" section;
- the next step, press the "AutoSum" button. The result from the addition will appear under the cells you selected;
- if you select the cell with the result (in my case it is the cell E8) - then you will see the formula "= SUM (E2: E7)".
- thus, writing the formula "= SUM (xx)" where instead of xx put (or select) any cells, you can read a wide variety of ranges of cells, columns, rows ...
Autosum of selected cells
How to calculate the amount with some condition
Quite often, when working, you need not just the sum of the entire column, but the sum of certain rows (i.e., selectively). Suppose a simple problem: you need to get the amount of profit from some worker (exaggerated, of course, but the example is more than real).
I will use only 7 rows in my table (for clarity), while the real table can be much larger. Suppose we need to calculate all the profit made by "Sasha". What the formula will look like:
- " = SUMIFS (F2: F7, A2: A7, "Sasha") "- (note: pay attention to the quotes for the condition - they should be as in the screenshot below, and not as I have written on my blog now.) Also note that Excel, when typing in the beginning of a formula (for example," SUM .. . "), he himself suggests and substitutes possible options - and there are hundreds of formulas in Excel !;
- F2: F7 - this is the range over which the numbers from the cells will be added (summed);
- A2: A7 - this is the column by which our condition will be checked;
- "Sasha" - this is the condition, those lines in which column A will contain "Sasha" will be added (pay attention to the exemplary screenshot below).
Sum with condition
Note: there can be several conditions and they can be checked by different columns.
*
How to count the number of rows (with one, two or more conditions)
Quite a typical task: to count not the sum in cells, but the number of rows that satisfy some condition.
Well, for example, how many times the name "Sasha" appears in the table below (see screenshot). Obviously 2 times (but this is because the table is too small and taken as an illustrative example). How can you calculate this as a formula?
Formula:
"= COUNTIF (A2: A7, A2)"- where:
- A2: A7 - the range in which the lines will be checked and counted;
- A2 - a condition is set (note that it was possible to write a condition of the "Sasha" type, or you can simply specify a cell).
The result is shown on the right side of the screenshot below.
Number of rows with one condition
Now imagine a more extended problem: you need to count the lines where the name "Sasha" appears, and where the number "6" will appear in column "B".Looking ahead, I will say that there is only one such line (screenshot with an example below).
The formula will look like:
= COUNTIFS (A2: A7, A2, B2: B7, "6") - (note: pay attention to the quotes - they should be like in the screenshot below, not like mine), where:
A2: A7; A2 - the first range and search condition (similar to the example above);
B2: B7; "6" - the second range and the search condition (note that the condition can be set in different ways: either specify a cell, or simply text / number is written in quotation marks).
Counting lines with two or more conditions
*
How to calculate the percentage of the amount
This is also a fairly common question that I often come across. In general, as far as I can imagine, it arises most often - due to the fact that people are confused and do not know what they are looking for a percentage from (and in general, they do not understand the topic of interest very well (although I myself am not a great mathematician, and still ... ☝)).
👉 To help!
How to calculate interest: from a number, from the sum of numbers, etc. [in the mind, on a calculator and using Excel] - a note for beginners
The easiest way, in which it is simply impossible to get confused, is to use the "square" rule, or proportions.
The whole point is shown in the screenshot below: if you have a total amount, let's say in my example this number is 3060 - a cell F8 (ie this is 100% profit, and some part of it was made by "Sasha", you need to find what ...).
In proportion, the formula will look like this:= F10 * G8 / F8 (i.e. cross by cross: first we multiply two known numbers along the diagonal, and then divide by the remaining third number).
In principle, using this rule, it is almost impossible to get confused about percentages 👌.
An example of solving problems with interest
*
PS
ActuallyThis concludes this article. I am not afraid to say that having mastered everything that is written above (and only "heels" of formulas are given here), you will then be able to independently learn Excel, leaf through the help, watch, experiment, and analyze. 👌
I will say even more, everything that I described above will cover many tasks, and will allow you to solve all the most common, which you often puzzle over (if you do not know the capabilities of Excel), and does not even know how quickly it can be done ... ✔
*
Additions on the topic are welcome!
Good luck!
👋
First publication: 01/21/2017
Correction: 01/05/2020
