Jump into Excel. Training Course from Beginner to Intermediate in two hours

скачать книгу бесплатно


The labels with the Names of the sheets are at the very bottom of the window. Sheets can be renamed, added, deleted, renamed again.

Names of the sheets

There can be hundreds of sheets in a single Excel file. But in practice, they are usually limited to 10, maximum 20 sheets. The thing is that when there are a lot of sheets, it becomes inconvenient to search for information.

Scrollbars allow you to see adjacent areas of the sheet that are not currently visible. The row scroll bar is on the right, and the column scroll bar is at the bottom of the sheet. We hover the mouse cursor over the viewing direction, click the left mouse button and do not let go until we find ourselves in the place that we are interested in seeing. If we scroll through several rows or columns, we stop seeing the active cell, but at the same time, we will still see which cell is active in the object name field.

we will still see cell «A1» in the object name field

Please note: the active cell itself does not shift at the same time. It’s still A1.

To make another cell active, use the mouse or keyboard arrows instead of scroll bars. To select another cell with the mouse, just hover the mouse cursor over another cell, for example D1, and click the left mouse button.

To select cell «D1», just hover the mouse cursor and left-click

You can also move the active cell using the arrows on the keyboard, which are located in the lower right corner of the keyboard.

Filling in the file with the initial information

Now let’s go back to our task and start filling in the file with the initial information.

Let’s open the first of the sent files «Information from the director’s secretary».

As you can see, there is not complete information on the task: there is no Date of birth, no Date of employment, no Experience years and Age. But at the same time, there is information that was not required by the assignment: a Work phone and an E-mail.

file «Information from the director’s secretary»

We will copy all this information into our «Employees» file on a separate sheet, which we will call «Information from secretary».

In order to rename «Sheet1» to the name «Information from secretary», hover the mouse cursor over «Sheet1», press the right mouse button, select «Rename» and type in «Information from secretary», then press the «Enter» key.

select «Rename»

and type in «Information from secretary», then press the «Enter» key.

type in «Information from secretary»

Copy operation

Before copying, it is necessary to highlight the cell that we are going to copy. «Highlight» means to stand on a cell with a tabular cursor. This can be done using the arrows located on the keyboard in the lower right corner.

You can also select a cell using the mouse cursor. Hover the mouse cursor, for example, on the cell «G26» and click the left mouse button. To highlight the entire copied range, we stand on its corner cell, for example «G26». Click on the left mouse button and, without releasing this button, drag the cursor to the opposite corner. And only after bringing it to the end – we release the left mouse button pressed.

Thus, the entire block of cells is highlighted.

the entire block of cells is highlighted

Let’s repeat it again. We stand on the cell «A1», press the left mouse button and drag the cursor now to the lower opposite corner. When dragged – release the left mouse button.

and drag the cursor now to the lower opposite corner

There is another way to select the desired cells. We can select for copying. Not only the range A1:G26, which is a part of the columns A:G, that is, but all the cells of the columns A:G at once. To do this, hover the cursor over the name of column «A», above cell A1, click on the left mouse button and, without releasing it, drag the mouse cursor to the right until column G is highlighted. Release the pressed left mouse button.

all the cells of the columns A:G at once

After the range for copying is selected, click on the «Copy» button located on the «Home» tab.

click on the «Copy» button located on the «Home» tab

Instead of using the «Copy» button, we can hover the mouse cursor over the selected range, right-click and select «Copy».

right-click and select «Copy»

For copying, the «Ctrl» and «C» keys located on the keyboard are also often used. First, press the «Ctrl» and, without releasing it, press the «C» key with the second finger.

Regardless of the chosen copying method, the result will be the same: a copy of the selected fragment gets to the Clipboard and is ready for insertion in any other place, both in this file and in any other file. Around the copied fragment, a temporary dynamic dotted line appears, indicating copies of which cells are currently in the Clipboard.

Paste operation

We stand with the tabular cursor on the cell in which we are going to insert the data on our «Information from secretary» sheet.

Since we are going to insert not one cell, but a whole array of data, we must stand on the upper left cell of this array (in this case it is cell «A1»), below which and to the right of which the entire array will be inserted.

we must stand on the upper left cell «A1», below which and to the right of which the entire array will be inserted

Click on the «Paste» button located on the «Home» tab.

Click on the «Paste» button

Instead of the «Paste» button, you can use the keyboard shortcut «Ctrl» and «V». You can also hover the mouse cursor, right-click and select «Paste».

In the data copied to the «Information from secretary» sheet, not all words are fully readable, since the column width is less than the width of some words.

not all words are fully readable

If we want to align column A by width, that is, to make the width of column «A» such that the longest word in column «A» is visible, then hover the mouse cursor over the right border of column «A» and double-click with the left mouse button.

If we want to align several columns in a similar way, then select these columns from «A» to «G», hover the mouse cursor over the border of any two of them, for example between «A» and «B», and double-click the left mouse button with a small interval. All columns have «moved apart».

To quickly select all columns and all rows of the entire sheet at once, use a left mouse click on the upper left corner area located to the left of column «A» and above row «1». This method of selecting the entire sheet is used not only when adjusting columns or rows in width, but also when quickly copying the contents of the entire sheet at once.

click on the upper left corner area

Thus, we copied, pasted, and aligned the width of the inserted array of initial data.

Now we will copy the data from the second file with the initial data «Information from the HR Department» to a sheet, which we will call «Information from HR». Since we don’t have a second sheet in the file yet, we need to create it. We will do this by hovering the mouse cursor over the name of the sheet, similar to how we did when renaming the sheet, and click the right mouse button. Only now we will choose not «Rename», but «Insert», and then select «Worksheet». Click «Ok».

select «Worksheet». Click «Ok».

Rename this new «Sheet1» to «Information from HR», copy the information from the HR department to it, similar to how we did it with the information from the secretary.

copy the information from the HR department

Since this time, we copied and pasted all the rows and all the columns of the sheet at once, we did not have to additionally align the columns in width.

Let’s create another, third sheet, which we will call «Result». On which we will enter information from both the «Information from HR» sheet and the «Information from secretary» sheet.

Please note: the active sheet is highlighted with a white fill. To move to another sheet, hover the mouse cursor over it and click the left button.

Now, let’s move to another sheet. And let’s go to the «Result» sheet again. The layout of the sheet can also be changed. To do this, hover the mouse cursor over the name of the sheet, right-click, select «Move» or «Copy» and, for example, move to the end.

select «Move» or «Copy» and, for example, move to the end

Editing tables

We proceed to fill out the «Result» sheet.

First, copy the information from the sheet from the Secretary to the «Result» sheet. Select and copy columns A:E with the names Department, Position, Surname, First Name, Patronymic.

Copied.

As it often happens in practice, we are faced with a small problem: the names in the file that was provided to us from one source (from the Secretary) are arranged in a different order. The rows are sorted differently than the rows with surnames in the file that we received from another source (HR department). If the rows with surnames on both source sheets (both from the Secretary and from the HR Department) would have the same order, then copying would be simple. We would select the columns we need (with dates of birth and employment), copy them and paste them. But in our case, this cannot be done, because it will lead to an error – in the file from the HR Department and on the sheet «Information from HR» in the second row is the surname «Borshchev», while in the file from the Secretary and on the sheet «Result» the second row is occupied by «Ivanov».

If we just copy and paste the columns from the «Information from HR» sheet to the «Result» sheet with data, as on the «Information from Secretary» sheet, then the second row with Borshchev’s Date of birth and Date of employment will be substituted into the second row with Ivanov’s Department, Position, Surname, First Name and Patronymic.

error: second row with Borshchev’s and Ivanov’s data

To avoid this error, we will perform preliminary data processing – we will put the rows on the «Result» sheet in the same alphabetical order as they are sorted on the «Information from HR» sheet. So that the line with the surname «Borshchev» on the «Result» sheet would become the second, immediately under the header, as well as on the «Information from HR» sheet. After that, we will be able to copy the columns from the «Information from HR» sheet to the «Result» sheet, and Borshchev’s data will be copied to the row with Borshchev, and not with Ivanov.

Let’s use Sorting for this.

Sort operation

Select the area for sorting (columns A:E or cells A1:E26), click on the «Sort» button on the «Data» tab, select «Sort by…", «Surname», in the window that appears, select the alphabetical sorting order «From A to Z», and click «Ok».

select «Sort by – Surname»

Please note: If we wanted to sort the names of Departments in alphabetical order, and then within these departments by Surname, we would first select «Sort by – Department», and then add a second level of sorting by clicking on «Add Level» in the upper left corner of the menu, and in the second level that appeared we would select «Surname».

first select «Sort by – Department», then by «Surname»

After the sorting is done, the order of the rows with surnames on the «Result» sheet matches the order of the rows on the «Information from HR» sheet.

Note: Sorting (and much more) can also be done using the menu that appears when you right-click. Using the right mouse button to open the menu is a very useful skill that speeds up your work in Excel.

Copy the data from the «Information from HR» sheet to transfer to the «Result» sheet.

Select three columns on the «Information from HR» sheet: «Full name», «Date of employment» and «Date of birth». We don’t need the Full name column for the task, but we use it to make sure that the rows with the surnames of employees after sorting now match everywhere.

rows with the surnames of employees after sorting match everywhere

After visual verification that the order of the rows is correct (that is, the last name from column «F» with the full name coincided with the last name from column «C»), the extra column «F» with the Full name must be deleted.

Deleting a column

Select column F by hovering the mouse cursor over the heading F and click the left mouse button. Then click on the right mouse button. In the menu that appears, select «Delete».

select «Delete»

Thus, all the original data has been transferred to the «Result» sheet.

Сreating a new column

Let’s create the columns «Experience of full years on the date of the report» and «Age of full years on the date of the report» ourselves.