Microsoft Excel spreadsheet. 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).
Selection of columns, rows, ranges of cells, unlinked cells, worksheet. Delete cell contents, copy, paste.
Working with sheets (insert, move, copy, delete).
Changing the structure of the table.
Add and remove cells, rows and columns. Resizing of columns, rows. Selection of the width of the cell according to the content. Setting the same width for several columns, rows.
Combining cells.
Cell formatting:
Working with fonts, aligning the contents of cells, rotating text, setting up indents.
Copying the format according to the sample. Format cleaning.
Create borders for the table (using the border button, the Border tab, and drawing individual borders).
Ways to create fill for cells.
Session 2
Creation of number sequences.
Create lists.
Restriction on data entry.
Imposing a condition on data entry.
Formation of an error message when entering data.
Formation of a message for data entry.
Working with data formats.
Data types and formats of Microsoft Excel: text, numbers, dates, time, currency format. Setting them up.
Problems in using formats.
Custom format.
Create a custom format for text, dates and times.
Organization of calculations in Excel.
Entering a formula into a cell. Copy formulas using autocomplete.
Percentage formulas. Absolute and relative links. Create a formula with an absolute reference. Creation of 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.
Create headers and footers
Setting end-to-end rows and columns.
Pre-press preparation of the document. Using preview.
Prints the selected area. Sets the print area.
Session 3
Functions.
Using simple functions (sum, average, minimum, maximum, number).
Ways to work with functions. Using Help. Entering functions using the Function Wizard. Feature categories.
Logical functions: IF, AND, OR, IFERROR. Nested logical functions.
Math and Static Functions: SUMIF, COUNTIF, AVERAGEIF
Text functions CONCATENATE, LEFT, uppercase, lowercase, PROPER.
Array functions: GPR, VLOOKUP, TRANSPOSE.
Solving practical problems using functions.
Copy calculation results and formulas using Paste Special.
An overview of possible errors in formulas.
Session 4
Diagrams. Graphics in documents.
Creation of diagrams. Diagram wizard.
Setting chart properties.
Construction of various types of diagrams.
Chart printing.
Inserting graphic elements. Insert a picture.
SmartArt objects.
Conditional formatting.
Conditional formatting concept.
Create conditional formatting (comparison with a value, with the result of a formula or function.
Copying conditional formatting.
Editing and removing conditional formatting.
AutoFormat.
Session 5
Working with databases.
Database concept.
Sorting.
Removing duplicates.
Subtotals.
Grouping, structure creation.
Hiding and showing data.
Freeze rows and columns while browsing.
Dividing the sheet into parts when viewing.
Data Consolidation.
Applying filters. Filtering data.
Advanced filter. Creation of conditions for the advanced filter.
Search and replace.
Pivot tables.
Create pivot tables.
Changing the formats of pivot tables.
Setting up pivot tables.
Editing a pivot table.
Refresh pivot table data.
Session 6
Notes (create, edit, delete).
Creation of hyperlinks.
Data protection.
Setting a password to open a book.
Protection of the sheet for data entry.
Permission to work with individual cells and prohibition of work on other cells.
This program is used by a large number of people. Andrey Sukhov decided to record a series of training video lessons "Microsoft Excel for Beginners" for beginners and we invite you to familiarize yourself with the basics of this program.
Lesson 1. Overview of the interface (window appearance) of Excel
In the first lesson, Andrey will talk about the Excel interface and its main elements. You will also understand the work area of the program, with columns, rows and cells. So, the first video:
Lesson 2. How to Enter Data into an Excel Spreadsheet
In the second video tutorial on the basics of Microsoft Excel, we will learn how to enter data into a spreadsheet, as well as get acquainted with the autocomplete operation. I think the most effective teaching is the one based on practical examples. Here we are with you and begin to create a spreadsheet that will help us keep the family budget. Based on this example, we will consider the tools of the Microsoft Excel program. So, the second video:
Lesson 3. How to Format Spreadsheet Cells in Excel
In the third video tutorial on the basics of Microsoft Excel, we will learn how to align the contents of the cells of our spreadsheet, as well as change the width of the columns and the height of the rows of the table. Next, we will get acquainted with Microsoft Excel tools that allow you to combine table cells, as well as change the direction of text in cells, if necessary. So, the third video:
Lesson 4. How to format text in Excel
In the fourth video tutorial on the basics of Microsoft Excel, we will get acquainted with the operations of formatting text. For different elements of our table, we will apply different fonts, different font sizes and text styles. We will also change the color of the text and set a colored background for some of the cells. At the end of the lesson, we will receive an almost ready-made family budget form. So, the fourth video:
Lesson 5. How to format a table in Excel
In the fifth video tutorial on the basics of Microsoft Excel, we will finally format the family budget form, which we began to work on in the previous lessons. This tutorial will focus on cell borders. We will set different borders for different columns and rows of our table. By the end of the lesson, we will have a family budget form completely ready for data entry. So, the fifth video:
Lesson 6. How to use the data format in Excel
In the sixth video tutorial on the basics of Microsoft Excel, we will fill our family budget form with data. Microsoft Excel allows you to simplify the data entry process as much as possible and we will get acquainted with these possibilities. Next, I'll talk about the formats of data in cells and how you can change them. By the end of the lesson, we will receive a family budget form filled with initial data. So, the sixth video:
Lesson 7. How to make calculations using Excel spreadsheets
In the seventh video tutorial on the basics of Microsoft Excel, we will talk about the most interesting - about formulas and calculations. Microsoft Excel has a very powerful toolkit for performing various calculations. We will learn how to perform the simplest calculations using spreadsheets, then we will get acquainted with the function wizard, which greatly simplifies the process of creating formulas for performing calculations. So, the seventh video:
Lesson 8. Formatting a document in Excel
In the eighth video tutorial on the basics of Microsoft Excel, we will completely finish the work on the family budget form. We will draw up all the necessary formulas and do the final formatting of the rows and columns. The family budget form will be ready and if you keep your family budget, then you can adjust it to fit your expenses and income. So, the eighth video:
Lesson 9. How to build charts and graphs in Excel
In the final ninth lesson on the basics of Microsoft Excel, we will learn how to create charts and graphs. Microsoft Excel has a very impressive toolkit for visualizing calculation results. In the form of graphs, charts and histograms, you can present any data, both simply entered into a spreadsheet, and data obtained as a result of calculations. So, the final ninth video:
What happens when a dog licks his face
Benefits of drinking coffee
9 most creepy tortures of the ancient world
Microsoft Office Excel is an electronic spreadsheet program. An irreplaceable assistant to any secretary and accountant, because it greatly facilitates the work with formulas, tables and diagrams. The potential of such a tool is so enormous that it takes a long time to master it.
- Acquaintance always begins with the program interface. After launching it, a blank book will appear. Now you can practice using the functions from the Home tab and those commands that can be invoked by the Offline button. It is located on the left, at the top of the screen. The functions on the other taskbars are used on an ad-hoc basis, while the functions on the Home tab are always used.
- Learn to move around the sheet using the keys. For practice, enter the data in several cells. Learn the ways in which data can be edited both in the cell itself and in the formula bar. After selecting one cell, row or column, all kinds of commands will appear for manipulating the numbers in them. Explore these commands. Learn to call the context menu. Check out a variety of pre-built formulas and charts. Also master the cell parameters that can be formatted.
- After Microsoft Office Excel ceases to be a dark forest for you, try to solve any specific problem with its help. You should not immediately take on difficult tasks, lighter ones will allow you to more easily get used to the process.
- Gradually complicate your practices in Microsoft Office Excel. Leave data editing behind and learn to work with tables and formulas. For their comfortable use, you need to master the settings that open using controls and macros. Do not forget to apply the knowledge gained in practice.
- Don't avoid tutorials. There, not only is each function described in detail, but also a lot of tasks are given that can be performed immediately after each lesson in order to consolidate the knowledge gained.
Video lessons
Microsoft Office Excel is a spreadsheet editor in which it is convenient to work with them in every possible way. Here you can also set formulas for elementary and complex calculations, generate 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
Tutorial excel 2010 describes in detail the program interface and all the possibilities available to it. To start working independently in Excel, you need to navigate the program interface, understand the taskbar, where the 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, describing in detail the direction of action.
Under the ribbon of tabs there is a line "Name", where the name of the active element is written, and "Formula Bar", which displays formulas or text. When performing calculations, the "Name" string is converted into a drop-down list with a set of default functions. You just need to choose the option you want.
Most of the excel program window is occupied by a work area, where tables, graphs are actually built, and calculations are made ... Here the user takes any necessary actions. using the commands from the ribbon.
At the bottom of excel on the left side, you can switch between work areas. 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 view mode of the workbook 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 that we see when opening the program is a blank sheet, divided into cells, representing the intersection of columns and rows. Columns are designated by Latin letters, and rows by numbers. It is with their help that tables of any complexity are created, the necessary calculations are carried out in them.
Any video tutorial on the Internet describes the creation of tables in Excel 2010 in two ways:
To work with tables, data of several types are used, the main of which are:
- text,
- numerical,
- formula.
By default, text data is left-aligned, while numeric data and formulas are right-aligned.
To enter the desired formula in a cell, you must start with an equal sign, and then by clicking on the cells and putting the necessary signs between the values in them, we get the answer. You can also use the drop-down list with functions 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
To simplify the work with complex data sets or repetitive functions in Excel, the programming language built into the Visual Basic for Applications (VBA) application allows. The programming instructions can be downloaded from the Internet free of charge.
In Microsoft Office Excel 2010, VBA is disabled by default. In order to enable it, in the "File" tab, select the "Parameters" item on the left of the panel. In the dialog box that appears, on the left, click "Customize the Ribbon", and then on the right side of the window, check the box next to the "Developer" item so that such a tab appears in Excel.
When you start coding, you need to understand that an object in Excel is a sheet, workbook, cell, and range. They obey each other, therefore they are in the hierarchy.
Application plays a leading role. ... This is followed by Workbooks, Worksheets, Range... Thus, you need to write the entire path of the hierarchy in order to refer to a specific cell.
Another important concept is properties. These are the characteristics of objects. For Range, this 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.
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, it makes it possible to present information in the most perceivable form: as diagrams and graphs. Here you can carry out the most 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 workbook, 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 in the form of graphs or diagrams.
How do I 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 the "Start".
By default, the name is Book 1, but you can enter any name in the File name line. While working, it is worth periodically saving 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 arrow keys on your keyboard. To insert a sheet, you need to find the "Insert" item in the "Home" menu. There will be displayed all possible actions applicable to sheets, such as add or remove. You can also move tabs.
The "face" of the program
Before you figure out how to work in Excel, it's worth exploring 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 from 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 how to work in Excel correctly, the user must know that before entering values, it is necessary to set the dimension to a column or cell. It will depend on where the data is being measured. To do this, right-click on the selected range and select the "Format cells" item in the dialog box.
If the entered number is greater than 999, it is necessary to set the division by digits. You should not enter spaces yourself.
For the correct display of data, you cannot enter more than one separate value in one cell. Also, do not enter enumerations separated by commas or other characters. Each value must have its own cell.
How do I enter data?
It will not be difficult for users who know to enter data. To do this, you need to click on a cell and type letters or numbers on the keyboard. To continue working, you must press "Enter" or TAB. A line break is carried out using the combination ALT + "ENTER".
When entering a month or a number in order, it is enough to write the value in the initial cells, and then drag the marker to the required range.
Wrap text
Most often, users are interested in how to learn how to work with text in Excel. You can wrap it by words if necessary. 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, do the following: go to the "Home" tab and in the "Cells" group select the "Format" item. Next, you need to choose the appropriate action.
Formatting
To format numbers, select a 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 certain range and go to the menu "Home", "Font".
How do I create a table?
Knowledge of how to work in Excel is unlikely to be useful to the user if he does not know how to create a table. The easiest way is to select a specific range and mark the borders with black lines by clicking on the corresponding icon at the top of the page. But often a non-standard table is required for forms or documents.
First of all, you need to decide how the table should look 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 the "Alignment" item. The option "Merge cells" will help to remove unnecessary borders. Then you need to go to the "Boundaries" menu and set the required parameters.
Using the Format Cells menu, you can create different variations of tables by adding or removing columns and rows, and changing borders.
Knowing how to work in an Excel spreadsheet, the user will be able to create headers. To do this, in the "Table formatting" box, check the box opposite the "Table with headers" item.
To add elements to the table, you must use the "Design" tab. There you can select the required parameters.
What are macros for?
If the user has to often repeat the same actions in the program, knowledge of how macros work in Excel will come in handy. They are programmed to perform actions in a specific sequence. Using macros allows you to automate certain operations and facilitate repetitive work. They can be written in various programming languages, but their essence remains the same.
To create a macro in this application, you must enter the "Tools" menu, select the "Macro" item, and then click "Start recording". Next, you need to perform those actions that are often repeated, and after the end of 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.
