Excel courses online. How to learn to work in Excel on your own: program description, recommendations and reviews

Working with Excel: tutorial. Excel is one of the basic programs in the Microsoft Office suite. This is an indispensable assistant when working with invoices, reports, and tables.
Excel allows you to:
program, store huge amounts of information
Build graphs and analyze results
Make calculations quickly
This program is an excellent choice for office work.
Getting started with Excel (Excel)
1. Double-click on the sheet name to enter editing mode. In this panel, you can add a new sheet to the book or delete an unnecessary one. This is easy to do - you need to right-click and select the “Delete” line.

2. It’s easy to create another book - select the “Create” line in the “File” menu. The new book will be placed on top of the old one, and an additional tab will appear on the taskbar.

Working with tables and formulas
3. An important function of Excel is convenient work with tables.

Thanks to the tabular form of data presentation, tables automatically turn into a database. It is customary to format tables; to do this, select the cells and give them individual properties and format.

In the same window, you can perform alignment in a cell; this is done by the “Alignment” tab.

The Font tab has the option to change the font of text in a cell, and the Insert Menu allows you to add and remove columns, rows, and more.

Moving cells is easy - the “Cut” icon on the Home tab will help you with this.

4. No less important than the ability to work with tables is the skill of creating formulas and functions in Excel.

A simple F=ma is the formula, force equals mass times acceleration.

To write such a formula in Excel, you need to start with the “=” sign.

Printing a document
5. And the main stage after the work is completed is printing the documents.

Excel is one of the most powerful applications in the entire Office suite. It is used not only by accountants and economists, but also by ordinary people. The program is designed to work with numbers and tables, making it possible to present information in the most convenient form for perception: as charts and graphs. Here you can carry out complex calculations and perform various mathematical operations. In addition, the user does not need special knowledge; it is enough to learn how to work in Excel.

What is this office application?

Excel works with files that form a kind of book consisting of separate sheets. Letters, symbols and numbers are entered into table cells. They can be copied, moved or deleted. If necessary, various operations are carried out with them: text, mathematical, logical and others. Beginners who are just learning how to work in Excel should know that any information can be displayed on the screen in the form of graphs or charts.

How to create a file?

First of all, you need to open the document. To create it, you need to click on the program shortcut or go to the application through “Start”.

By default, the name is “Book 1,” but you can enter any name in the “File name” line. While working, you should periodically save data to avoid loss of information in the event of a computer crash or freeze.

You can easily switch between sheets by clicking on the corresponding inscription at the bottom of the page. If there are a lot of tabs, it is better to use the arrows on the keyboard. To insert a sheet, you need to find the “Insert” item in the “Home” menu. It will display all the possible actions that apply to the sheets, such as adding or deleting. Tabs can also be moved.

"Face" of the program

Before you figure out how to work in Excel, it’s worth studying the interface. The tools are located at the top and bottom of the window, and the rest of the area is occupied by rectangles, which are cells. The peculiarity of spreadsheets is that actions can be performed in some cells, and the result can be displayed in others.

Each table has columns that are designated by letters of the English alphabet. Lines are numbered on the left. Thus, any cell has its own coordinates. You can enter both data and formulas in each cell. Before entering the latter, you must put the “=” symbol.

Each cell has its own characteristic

To understand how to learn to work in Excel correctly, the user must know that before entering values, it is necessary to set the dimension of the column or cell. It will depend on how the data is measured. To do this, right-click on the selected range and select “Format Cells” in the dialog box.

If the entered number is greater than 999, you must set the division by digits. You should not enter spaces yourself.

To display data correctly, you cannot enter more than one individual value in one cell. Also, do not enter enumerations separated by commas or other characters. Each value must have its own cell.

How to enter data?

Users who know will have no problem entering data. To do this, you need to click on the cell and type letters or numbers on the keyboard. To continue working, you must press “Enter” or TAB. Line breaks are performed using the ALT + “ENTER” combination.

When entering a month or number in order, just enter the value in the initial cells, and then drag the marker to the required range.

Wrap text

Most often, users are interested in learning how to work with text in Excel. If necessary, it can be word-hyphenated. To do this, you need to select certain cells and in the “Home” tab you need to find the “Alignment” option, and then select “Text Wrap”.

If you want to automatically change the width and height of a cell according to the entered text, you should do the following: go to the “Home” tab and select “Format” in the “Cells” group. Next you need to select the appropriate action.

Formatting

To format numbers, you need to select the cell and find the “Number” group in the “Home” tab. After clicking on the arrow next to the “General” item, you can select the required format.

To change the font, you need to select a specific range and go to the “Home” menu, “Font”.

How to create a table?

Knowledge of how to work in Excel is unlikely to be useful to a user if he does not know how to create a table. The easiest way is to highlight a specific range and mark the boundaries with black lines by clicking on the corresponding icon at the top of the page. But often a non-standard table for forms or documents is required.

First of all, you need to decide what the table should look like in order to set the width and length of the cells. Having selected the range, you need to go to the “Format Cells” menu and select “Alignment”. The “Merge Cells” option will help remove unnecessary borders. Then you need to go to the “Borders” menu and set the required parameters.

Using the Format Cells menu, you can create different table options by adding or removing columns and rows, and changing borders.

Knowing how to work in an Excel table, the user will be able to create headings. To do this, in the “Table Formatting” window, you need to check the box next to the “Table with headers” item.

To add elements to a table, you must use the Design tab. There you can select the required parameters.

What are macros for?

If a user has to frequently repeat the same actions in a program, knowledge of how macros work in Excel will be useful. They are programmed to perform actions in a certain sequence. Using macros allows you to automate certain operations and alleviate monotonous work. They can be written in different programming languages, but their essence does not change.

To create a macro in this application, you need to go to the “Tools” menu, select “Macro”, and then click “Start Recording”. Next, you need to perform those actions that are often repeated, and after finishing the work, click “Stop recording”.

All these instructions will help a beginner figure out how to work in Excel: keep records, create reports and analyze numbers.

Microsoft Excel spreadsheet processor. Purpose of the program. Program interface Customize the interface: quick access panels, ribbons, column header areas. Scale.

Entering and editing information.

Entering information.

Editing data (replacing, deleting, adding, correcting errors).

Select columns, rows, cell ranges, unrelated cells, worksheet. Delete cell contents, copy, paste.

Working with sheets (inserting, moving, copying, deleting).

Changing the table structure.

Add and remove cells, rows and columns. Resizing columns and rows. Selecting cell width based on content. Setting the same width for several columns and rows.

Merging cells.

Cell formatting:

Working with fonts, aligning cell contents, rotating text, adjusting indents.

Copy format according to sample. Format clearing.

Creating borders for a table (using the borders button, the border tab, and drawing individual borders).

Ways to create a fill for cells.

Lesson 2

Creating number sequences.

Creating lists.

Restriction on data entry.

Imposing conditions on data input.

Generating an error message when entering data.

Generating a message for data entry.

Working with data formats.

Microsoft Excel data types and formats: text, numbers, dates, time, currency format. Their setup.

Problems in using formats.

Custom format.

Create a custom format for text, dates and times.

Organizing calculations in Excel.

Entering a formula into a cell. Copying formulas using autocomplete.

Formulas with percentages. Absolute and relative links. Create a formula with an absolute reference. Creating a formula with data from different sheets, different books. Named cells. Create a formula with cell names. Editing and deleting names.

Influencing and dependent cells.

Preparing a document for printing.

Setting page parameters.

Creating Headers and Footers

Setting through rows and columns.

Pre-press preparation of the document. Using Preview.

Print the selected area. Setting the print area.

Lesson 3

Functions.

Using simple functions (sum, average, minimum, maximum, number).

Ways to work with functions. Using Help. Entering functions using the Function Wizard. Categories of functions.

Logical functions: IF, AND, OR, IF ERROR. Nested logical functions.

Mathematical and static functions: SUMIF, COUNTIF, AVERAGEIF

Text functions CONCATENATE, LEFT, uppercase, lowercase, PROP.

Array functions: GPR, VPR, TRANSPORT.

Solving practical problems using functions.

Copying calculation results and formulas using paste special.

Review of possible errors in formulas.

Lesson 4

Diagrams. Graphics in documents.

Creating diagrams. Chart Wizard.

Setting chart properties.

Construction of various types of diagrams.

Print the chart.

Inserting graphic elements. Inserting a picture.

SmartArt objects.

Conditional formatting.

The concept of conditional formatting.

Creating conditional formatting (comparing with a value, with the result of a formula or function.

Copy conditional formatting.

Editing and removing conditional formatting.

Autoformat.

Lesson 5

Working with databases.

The concept of a database.

Sorting.

Removing duplicates.

Subtotals.

Grouping, creating structure.

Hiding and showing data.

Freeze rows and columns when viewing.

Dividing the sheet into parts when viewing.

Data consolidation.

Applying filters. Selection of data through filtering.

Advanced filter. Creating conditions for an advanced filter.

Search and replace.

Pivot tables.

Creating pivot tables.

Changing formats for pivot tables.

Setting up pivot tables.

Editing a pivot table.

Updating pivot table data.

Lesson 6

Notes (creating, editing, deleting).

Creating hyperlinks.

Data protection.

Setting a password to open a book.

Protecting the data entry sheet.

Permission to work with individual cells and prohibition of work on other cells.

If you've never used a spreadsheet to create documents before, we recommend reading our guide to Excel for Dummies.

You'll then be able to create your first spreadsheet with tables, graphs, math formulas, and formatting.

Detailed information about the basic functions and capabilities of the table processor.

Description of the main elements of the document and instructions for working with them in our material.

By the way, to work more efficiently with Excel tables, you can familiarize yourself with our material.

Working with cells. Filling and formatting

Before taking specific actions, you need to understand the basic element of any .

An Excel file consists of one or several sheets divided into small cells.

A cell is a basic component of any Excel report, table or graph. Each cell contains one block of information. This could be a number, date, monetary amount, unit of measurement, or other data format.

To fill out a cell, simply click on it with the pointer and enter the required information.

To edit a previously filled cell, double-click on it.

Rice. 1 – example of filling cells

Each cell on the sheet has its own unique address. Thus, you can carry out calculations or other operations with it.

When you click on a cell, a field will appear at the top of the window with its address, name and formula (if the cell is involved in any calculations).

Select the cell “Share of shares”. Its location address is A3. This information is indicated in the properties panel that opens. We can also see the content.

This cell has no formulas, so they are not shown.

More cell properties and functions that can be applied to it are available in the context menu.

Click on the cell with the right mouse button.

A menu will open with which you can format the cell, analyze the contents, assign a different value, and other actions.

Rice. 2 – context menu of the cell and its main properties

Sorting data

Often users are faced with the task of sorting data on a sheet in Excel. This feature helps you quickly select and view only the data you need from the entire table.

It’s already in front of you (we’ll figure out how to create it further in the article). Imagine that you need to sort data for January in ascending order.

How would you do it? Simply retyping a table is extra work, and if it is large, no one will do it.

There is a special function for sorting in Excel. The user is only required to:

• Select a table or block of information;
• Open the “Data” tab;
• Click on the “Sorting” icon;

Rice. 3 – “Data” tab

• In the window that opens, select the table column on which we will carry out actions (January).
• Next is the sorting type (we group by value) and, finally, the order - ascending.
• Confirm the action by clicking on "OK".

Rice. 4 – setting sorting parameters

The data will be sorted automatically:

Rice. 5 – the result of sorting the numbers in the “January” column

Similarly, you can sort by color, font and other parameters.

Mathematical calculations

The main advantage of Excel is the ability to automatically carry out calculations while filling out the table. For example, we have two cells with values ​​2 and 17. How can we enter their result into the third cell without doing the calculations ourselves?

To do this, you need to click on the third cell in which the final result of the calculations will be entered.

Then click on the function icon f(x) as shown in the image below.

In the window that opens, select the action you want to apply. SUM is the sum, AVERAGE is the average, and so on.

A complete list of functions and their names in the Excel editor can be found on the official Microsoft website.

We need to find the sum of two cells, so click on “SUM”.

Rice. 6 – select the “SUM” function

There are two fields in the function arguments window: “Number 1” and “Number 2”. Select the first field and click on the cell with the number “2”.

Its address will be written into the argument line.

Click on “Number 2” and click on the cell with the number “17”. Then confirm the action and close the window.

If you need to perform mathematical operations with three or more cells, simply continue to enter the values ​​of the arguments in the fields “Number 3”, “Number 4” and so on.

If the value of the summed cells changes in the future, their sum will be updated automatically.

Rice. 7 – result of calculations

Creating tables

You can store any data in Excel tables.

Using the quick setup and formatting function, it is very easy to organize a personal budget control system, a list of expenses, digital data for reporting, etc. in the editor.

They have an advantage over a similar option in other office programs.

Here you have the opportunity to create a table of any size. The data is easy to fill out. There is a function panel for editing content.

In addition, the finished table can be integrated into a docx file using the usual copy-paste function.

To create a table, follow the instructions:

• Open the Insert tab. On the left side of the options panel, select Table. If you need to consolidate any data, select the “Pivot Table” item;
• Using the mouse, select the space on the sheet that will be allocated for the table. And also you can enter the location of the data in the element creation window;
• Click OK to confirm the action.

Rice. 8 – creating a standard table

To format the appearance of the resulting sign, open the contents of the designer and in the “Style” field, click on the template you like.

If desired, you can create your own view with a different color scheme and cell highlighting.

Rice. 9 – table formatting

Result of filling the table with data:

Rice. 10 – completed table

For each table cell, you can also configure the data type, formatting, and information display mode. The designer window contains all the necessary options for further configuration of the sign, based on your requirements.

The Microsoft Office Excel program is a table editor in which it is convenient to work with them in every possible way. Here you can also set formulas for basic and complex calculations, create graphs and diagrams, program, creating real platforms for organizations, simplifying the work of an accountant, secretary and other departments dealing with databases.

How to learn to work in excel on your own

The excel 2010 tutorial describes in detail the program interface and all the features available to it. To start working independently in Excel, you need to navigate the program interface, understand the taskbar, where commands and tools are located. To do this, you need to watch a lesson on this topic.

At the very top of Excel we see a ribbon of tabs with thematic sets of commands. If you move the mouse cursor over each of them, a tooltip appears detailing the direction of action.

Under the tab ribbon there is a “Name” line, where the name of the active element is written, and a “Formula line”, which displays formulas or text. When performing calculations, the “Name” line is converted into a drop-down list with a default set of functions. You just need to select the required option.

Most of the excel window is occupied by the work area, where tables, graphs are actually built, and calculations are made. . Here the user performs any necessary actions, using commands from the tab ribbon.

At the bottom of excel on the left side you can switch between workspaces. Additional sheets are added here if it is necessary to create different documents in one file. In the lower right corner there are commands responsible for convenient viewing of the created document. You can select the workbook viewing mode by clicking on one of the three icons, and also change the scale of the document by changing the position of the slider.

Basic Concepts

The first thing we see when opening the program is a blank sheet, divided into cells that represent the intersection of columns and rows. The columns are designated by Latin letters, and the rows by numbers. It is with their help that tables of any complexity are created and the necessary calculations are carried out in them.

Any video lesson on the Internet describes creating tables in Excel 2010 in two ways:

To work with tables, several types of data are used, the main of which are:

• text,
• numerical,
• formula.

By default, text data is aligned to the left of cells, and numeric and formula data is aligned to the right.

To enter the desired formula into a cell, you need to start with the equal sign, and then by clicking on the cells and putting the required signs between the values ​​​​in them, we get the answer. You can also use the drop-down list with functions located in the upper left corner. They are recorded in the “Formula Bar”. It can be viewed by making a cell with a similar calculation active.

VBA to excel

The programming language built into the Visual Basic for Applications (VBA) application makes it easier to work with complex data sets or repetitive functions in Excel. Programming instructions can be downloaded on the Internet for free.

In Microsoft Office Excel 2010, VBA is disabled by default. In order to enable it, you need to select “Options” in the “File” tab on the left panel. In the dialog box that appears, on the left, click “Customize Ribbon”, and then on the right side of the window, check the box next to “Developer” so that such a tab appears in Excel.

When starting to program, you need to understand that an object in Excel is a sheet, workbook, cell and range. They obey each other, so they are in a hierarchy.

Application plays a leading role . Next come Workbooks, Worksheets, Range. Thus, you need to specify the entire hierarchy path to access a specific cell.

Another important concept is properties. These are the characteristics of objects. For Range it is Value or Formula.

Methods represent specific commands. They are separated from the object by a dot in VBA code. Often when programming in Excel, the Cells (1,1) command is needed. Select. In other words, you need to select a cell with coordinates (1,1), that is, A 1.