How to start working in Excel from scratch without taking courses

Excel for beginners often seems like some kind of heavy program. After all, there are so many functions that it is not clear how to apply them correctly, or how to remember all this. But in practice, it turns out that everything is simple and intuitive. You just need to know what features this program has and what they can be used for.

Therefore, in fact, you can not take any expensive courses and learn Excel from scratch on your own. How to do this, and will be discussed today.

Excel Basics for Beginners

An Excel worksheet is called a workbook, which in turn consists of parts called sheets. What it is? If we open the program, we will see a large number of cells arranged in a grid. This is what the sheet is. 

Here is a screenshot describing the different parts of an Excel sheet.

Each cell contains certain values. To make changes to it (or add some information to it), you need to click on it once with the left mouse button, and then just start typing. If you need to edit the text itself, and not replace it, then double-click the mouse, after which a full-fledged input field will appear in place of the cell, in which you can perform all standard actions: copy, paste, delete part of the text, and so on. To confirm the changes in each of these cases, you need to press the “Enter” button.

Each value is of a specific type. It can be numbers, text, money, percentage, date and time, and so on. 

As a rule, the format is automatically determined, but in some situations errors are possible. Therefore, it is desirable to check whether the format is displayed correctly so that later there are no errors when trying to execute the formula. If you need to change or change it manually, you need to right-click on the cell, and select “Format Cells”. The same goal can be achieved by pressing the key combination Ctrl + 1.

If the cell format is set to “number”, then the user can configure how many decimal places should be displayed. In our example, we made 2 characters.

Setting the number format is also possible using the key combination CTRL + SHIFT + 1.

You can also customize the format for displaying the date and time in cells of the corresponding type.

Changing Cell Values

Let’s change the value of the cells. To do this, perform the following sequence of actions:

1. We make a click on the cell containing the word, right-click and enter any value. Let’s say this is a number. 
2. To leave the value that was before, just double-click on this cell and delete part of the text and add the necessary one.
   

   

3. Also, editing the cell value is possible through the line for formulas. It is located at the top of the sheet near the small fx button. To do this, you need to click on the cell and make changes directly in the formula bar in the same way as in any other input field (for example, in the address bar of the browser). After the changes are made, press the “Enter” button.
   

   

It may turn out that you need to make a cell containing a certain value empty. To do this, left-click on it and press the Delete key.

Moving a cell

Another common operation that Excel users perform is moving a cell containing a specific value. To do this, you need to use the usual “cut-paste” combination, as in other programs. Specifically for Excel, this is done like this.

First, the required cell is selected. After that, on the top panel (which is also called the ribbon), the button with scissors is pressed, highlighted in the screenshot below with a red square. You can also use the familiar CTRL + X key combination. Next, the cell will be surrounded by a dotted line, and its contents will be on the clipboard.

After that, you need to click the “Insert” button, after clicking on the desired cell. Well, or again, use the standard key combination Ctrl + V.

In this way, it is possible to move not only one cell, but several at once. And absolutely anywhere in the workbook: another cell, sheet. Moreover, moving to another book is also possible.

If you need to move several cells, they must also be pre-selected. But this is done in a different way.

1. A click is made on the upper left cell of the range to be selected (yes, the collection of cells is called a range, now you know the new word).
2. After that, press the Shift button, hold it down and select adjacent cells using the arrows on the computer keyboard.
   

   

You can also select a range by grabbing the top left cell with the left mouse button, and then move the cursor to the bottom right cell of the range, which will then move (the same must be done to select a set of cells to perform any other operations).

Often it may be necessary to highlight individual columns or rows. To do this, you need to click on the corresponding number (for a row) or name (for a column). 

Resizing cells

To modify the size of cells, you need to move the boundaries of the corresponding rows or columns on the coordinate panel. In this case, the cursor will resemble a plus sign with arrows, as shown in the figure.

Sometimes it happens that the value does not fit completely in the cell. In this case, it is much easier to automatically adjust its dimensions so that it becomes the same as the length of the line that enters it. To do this, double-click on the right border of the corresponding column.

To make a line taller, but at the same time keep the same column width, you must click on the “Text Wrap” button in the “Alignment” group.

It is also possible to adjust the text alignment horizontally and vertically using the corresponding buttons in the “Alignment” group. For example, let’s move the border of column E a little, and then align the text exactly to the center of the cell along all axes.

To create a table, you often have to merge multiple cells. To do this, select them and click the “Merge and Center” button in the same group.

Excel autocomplete

One of the most requested features in Excel is autocomplete. Suppose we need to create a table where there is a row with a list of months. In this case, it is enough just to write the word “January” in the first cell, and then using the box in the lower right corner, drag the value to the appropriate number of cells to the right. After entering the first name of the month, the cell format automatically changes to “Date”. Therefore, Excel intuitively understands what the next cells should be.

The autocomplete feature can also be used for numeric values. To do this, you need to insert the number 1 in one cell, and the number 2 (or any other that describes a certain pattern) in the second cell. After that, both cells must be selected and using the autofill marker, drag the selection a certain number of cells in the desired direction. We will fill the cells in the downward direction.

If only one cell is selected and dragged down in this way, then a lot of identical numbers will simply appear.

You can also copy a column using autocomplete to an adjacent one. To do this, select the entire column, and then drag it to the side using the autocomplete handle. The same is done with strings.

Operations on columns and rows

One hundred columns and rows can carry out a large number of actions. For example, delete them or insert another column between the other two. For the first operation, you must first select the column, then right-click on the corresponding letter and click on “Delete”.

Inserting a column is done in a very similar way. First you need to click the mouse (left button) on the column that is on the right, then call the context menu and go through the items “Insert” – “Column”. You can also use the CTRL + SHIFT + = keys. After that, a new column will appear on the left.

You can also insert a line that will appear below. This can be done using the key combination SHIFT + SPACE (with its help the line is selected), then the right mouse click is made, and the options “Insert” – “Line” are selected in the context menu. Everything is simple. Similarly, you can use the above combination that was used to insert the column.

The concept of formulas and functions

Formulas and functions are one of the most important concepts in Excel. They are needed for a variety of tasks, from mathematical calculations to conditional formatting and complex scripts.

A function is an operation that always returns some value. A formula is a collection of functions, logical and arithmetic operators. Everything is simple. 

To enter a formula, you need to write an “equal” sign in a cell, and then start entering directly a mathematical expression or other command. 

For example, =(2+3)*5. In this case, the numbers 2 and 3 are first added, after which their sum is multiplied by 5. After the formula is entered, you need to press the “ENTER” key, and Excel will display the result.

That is, you can enter a standard mathematical expression, and all the counting rules are carried out in the same way (first, operations are performed in brackets), then multiplication and division, and then addition and subtraction.

In addition to numeric values, the formula can store references to cells with values. For example, =(A1+B1)*5.

Similarly, you can use the autofill handle to copy the formula to other cells. In our example, we are grabbing this formula and dragging it down to get similar values ​​for the following cells.

Yes, the formula automatically changes to make it fit. That is, if in line 1 the sum of cells 1 and 2 is made, then when the formula is transferred to line 2, similar cells will be added, but already in the next line. And the same principle applies to more complex formulas.

It is possible to pin the link so that it does not change while copying the formula or autocomplete cells. To do this, you need to put a dollar sign in front of the letter or numeric address of the cell.

In the formula shown in this screenshot, cell B1 is constant, that is, it does not change and remains constant. This can be useful in situations where, for example, each customer’s purchase needs to be multiplied by a specific customer in order to calculate the bonus amount. Well, or in any other case, when it becomes necessary to multiply by some one constant number.

Using functions

A function is a ready-made logical or mathematical formula that is used in calculations. To access the list of functions, you must press the fx button or use the key combination SHIFT + F3.

After that, such a window will appear.

At the same time, the list of functions is initially very large. To make it smaller, you need to choose the category that is most suitable for a specific task. The choice of function is confirmed with the OK button, after which a window opens where you need to enter the parameters. It’s called Function Arguments.

You can enter functions directly from the keyboard. To do this, you need to enter its name (Excel will suggest it, starting with the first letters), and then specify the arguments in brackets. Like in this screenshot.

Conclusions

These are all the basic features of Excel. Of course, there are a huge number of functions, and it is also possible to expand the functionality through macros and add-ons. There are many complex examples of how you can flexibly use such an extensive functionality of this program. Do not worry if something is not clear at the first stages. Over time, everything can always be mastered, there would be a desire. Of course, self-taught Excel is a little more difficult, but often self-taught people became more successful than those who took expensive courses.