Nowadays, spreadsheets are used in many areas, they make it easier to work with numbers, statistics and other accounting information. And Microsoft Excel is the undoubted leader among such programs, and specialists from many fields need to use it daily. This program is quite simple, and anyone can learn Excel on their own. There are various training courses, many teachers can even study individually, but this article will allow you to learn Excel on your own for free. For the best result, you should take excel spbshb.ru training courses for beginners in St. Petersburg.

First, some general information. The file in this program is called "Book" and consists of "sheets". Each sheet can contain a variety of data that can be linked together using special formulas. To understand how to master Excel on your own, you need to understand the appearance of the program. When creating a new file, a window appears that contains the following elements:

• Quick Access Toolbar in the upper left corner (contains the function of saving and rolling back to the previous action);
• Next comes the line main menu, with which the document is edited;
• For each menu item there is button ribbon with a set of intuitive changes that can be applied to the file as needed (it is located under the main menu);
• Below comes formula bar. Learning Excel on your own is impossible to imagine without studying them, this is the main tool of the program;
• Then follows directly Workspace, which consists of columns (letter-coded) and rows (numerically-coded) that make up the cells.

Before you learn how to work in Excel on your own, you need to pay attention to the cells.

They can contain both numeric data and text - by right-clicking on it and selecting the "Format Cells" option, you can select the desired option. When inserting text, keep in mind that its length can exceed the length of one cell. In this case, you can either merge several cells into one (such a function is available in the button ribbon), or push the cell boundaries by dragging from the edge to the side (in this case, the cursor changes). In the ribbon of buttons, there are special functions for changing the bit depth, setting percentages, or searching for the desired values ​​in the cells.

The program also has a very useful "Autocomplete" function, without knowing which learning Excel from scratch on its own becomes very tedious. It is enough to enter a number into the cell and drag the square in its lower right corner (both down and to the side), and the processor will put down the number row itself. This also works for days of the week, months, and complex numbers. Autocomplete can also be used for formulas. Columns and rows can also be ranked, which is useful when making lists. They can be either ordinal or marked. This allows you to create complex tables with different types of data.

Learning Excel on your own would be incomplete without formulas.

With their help, you can link data from several cells (or even sheets), they facilitate mathematical and statistical calculations. To enter a formula, you need to select a cell, put the “=” sign in the formula bar, and then enter all the necessary information. After pressing Enter, the program will calculate the result. Formulas can use both numeric values ​​and cells. It is important to keep in mind that when autocomplete, the names of the cells in the formula will change to the following, and to avoid this, you must put the “$” sign in front of the “unchanged” cell (for example, “=$C$1*5”). You can use this sign only before a column or row value, then the other parameter will be changed. How to master excel on your own - try different formulas to understand how they work. The program has a huge set of them, so every specialist will find something useful for himself.

A cell can also contain a link to another cell or a hyperlink. To create it, you must enter the "=" sign and indicate the cell to which you want to refer. In this case, the same “$” sign is used to fix the value. For hyperlinks, use the "Ctrl + K" command, which allows you to link a cell to a website or other document. If you want to learn Excel on your own, then you definitely need to try this feature.

A separate place is occupied by charts and graphs, indispensable in statistics and in the preparation of business plans.

To compose them, there is a special icon in the button ribbon. You can not ignore the visual design of the tables. Using the "Format Cells" option, you can set borders, determine the alignment of the test, highlight the cell with a color. The Quick Access Toolbar has a preview that will let you see how your document looks.

In order to understand how to learn Excel on your own, you need to try using the studied functions. Only constant practice will help to understand this program. You need to try creating tables with different formulas to see their purpose, use different types of graphs and charts, try to change the appearance of the document. This will allow you to remember the location of the necessary buttons and elements, and learning excel on your own will become as fast as possible.

Which allow you to optimize the work in MS Excel. And today we want to bring to your attention a new batch of tips to speed up the action in this program. Nikolay Pavlov, the author of the "Planet Excel" project, which is changing people's idea of ​​what can actually be done with this wonderful program and the entire Office package, will talk about them. Nikolay is an IT trainer, developer and expert on Microsoft Office products, Microsoft Office Master, Microsoft Most Valuable Professional. Here are the techniques he personally tested for faster work in Excel. ↓

Quickly add new data to a chart

If for your already built chart on the sheet there is new data that needs to be added, then you can simply select the range with the new information, copy it (Ctrl + C) and then paste it directly into the chart (Ctrl + V).

This feature was only introduced in the latest version of Excel 2013, but it's worth upgrading to the new version early. Suppose that you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated ones (Ivanov I.I.). To perform such a transformation, you just need to start writing the desired text in the adjacent column manually. On the second or third line, Excel will try to predict our actions and perform further processing automatically. It remains only to press the Enter key to confirm, and all names will be converted instantly.

In a similar way, you can extract names from emails, glue full names from fragments, etc.

Copying without breaking formats

You most likely know about the "magic" autofill marker - a thin black cross in the lower right corner of the cell, by pulling which you can copy the contents of the cell or the formula to several cells at once. However, there is one unpleasant nuance: such copying often violates the design of the table, since not only the formula is copied, but also the cell format. This can be avoided if, immediately after dragging with a black cross, you click on the smart tag - a special icon that appears in the lower right corner of the copied area.

If you select the option “Copy only values” (Fill Without Formatting), then Microsoft Excel will copy your formula without format and will not spoil the design.

The latest version of Excel 2013 has the ability to quickly display your geodata on an interactive map, such as sales by city, etc. To do this, go to the "App Store" (Office Store) on the "Insert" tab (Insert) and install the Bing plugin from there Maps. This can also be done via a direct link from the site by clicking the Add button. Once a module has been added, it can be selected from the My Apps drop-down list on the Insert tab and placed on your worksheet. It remains to select your data cells and click on the Show Locations button in the map module to see our data on it.

If desired, in the plugin settings, you can select the type of chart and colors for display.

If your book has more than 10 worksheets, it becomes difficult to navigate through them. Right-click on any of the sheet tab scroll buttons in the lower left corner of the screen.

Have you ever matched the input values ​​in your Excel calculation to get the output you want? At such moments, you feel like a seasoned artilleryman, right? Just a couple of dozen iterations of "undershoot - flight", and here it is, the long-awaited "hit"!

Microsoft Excel can do this fitting for you, faster and more accurately. To do this, on the Insert tab, click the What If Analysis button and select the Parameter Selection command (Insert - What If Analysis - Goal Seek). In the window that appears, specify the cell where you want to select the desired value, the desired result, and the input cell that should change. After you click OK, Excel will take up to 100 "shots" to find the total you want with an accuracy of 0.001.

If this detailed review did not cover all the useful MS Excel features that you know about, share them in the comments!

Hello.

From my own experience, I will say one obvious thing: many novice users underestimate Excel (and, I would say, even very much underestimate). Maybe I’m judging from personal experience (when I couldn’t add 2 numbers before) and had no idea why Excel was needed, and then becoming an “average” user in Excel, I was able to solve tasks ten times faster than I used to sit and “think” ...

Purpose of this article: not only show how to perform this or that action, but also show the potential features of the program for novice users who do not even know about them. After all, having even very basic skills in working in Excel (as I said earlier) - you can speed up your work several times!

Lessons are a small instruction on how to perform a particular action. I chose the topics for the lessons myself, based on questions that I often have to answer.

Lesson topics : sorting the list by the desired column, adding numbers (sum formula), filtering rows, creating a table in Excel, drawing up a graph (chart).

Excel 2016 Tutorials

1) How to sort the list alphabetically, in ascending order (according to the required column / column)

Such tasks are very common. For example, there is a table in Excel (or you copied it there) and now you need to sort it by some column / column (for example, a table as in Fig. 1).

Now the task: it would be nice to sort it in ascending digits in December.

First you need to select the table with the left mouse button: note that you need to select those columns and columns that you want to sort (this is an important point: for example, if I had not selected column A (with people's names) and sorted by "December" - then the values ​​​​from column B would have been lost relative to the names in column A. That is, connections would be broken, and Albina would not be with "1", but with "5", for example).

After highlighting the table, go to the next section: " Data/Sort» (see Fig. 2).

Then you need to set up sorting: select the column by which to sort and the direction: ascending or descending. There is nothing special to comment on here (see Fig. 3).

2) How to add several numbers in a table, sum formula

Also one of the most popular tasks. Let's see how to solve it quickly. Let's say that we need to add up three months and get the total amount for each participant (see Fig. 5).

We select one cell in which we want to get the amount (in Fig. 5 - it will be "Albina").

Actually, in the window that appears, you need to specify (select) the cells that you want to add. This is done very simply: select with the left mouse button and press the “OK” button (see Fig. 7).

After that, you will see the result in the previously selected cell (see in Fig. 7 - the result is "8").

In theory, such an amount is usually required for each participant in the table. Therefore, in order not to enter the formula again manually, you can simply copy it into the desired cells. In fact, everything looks simple: select a cell (in Fig. 9 - this is E2), in the corner of this cell there will be a small rectangle - “stretch” it to the end of your table!

As a result, Excel will calculate the sum of each participant (see Fig. 10). Everything is simple and fast!

3) Filtering: leave only those rows where the value is greater (or where it contains...)

After the amount is calculated, very often, it is required to leave only those who have completed a certain barrier (for example, did more than the number 15). To do this, Excel has a special feature - filter.

First you need to select the table (see Fig. 11).

Small arrows should appear. If you click on it, the filter menu will open: you can select, for example, numeric filters and configure which rows to show (for example, the “greater than” filter will leave only those rows in which the number in this column is greater than you specify).

By the way, note that the filter can be set for each column! A column where there is text data (in our case, people's names) will be filtered by slightly different filters: namely, here it is not more and less (as in numeric filters), but “begins” or “contains”. For example, in my example, I entered a filter for names that begin with the letter "A".

Rice. 14. Name text contains (or starts with...)

Pay attention to one point: the columns in which the filter works are marked in a special way (see the green arrows in Fig. 15).

All in all, the filter is a very powerful and useful tool. By the way, in order to turn it off, just in the top Excel menu - “press” the button of the same name.

4) How to create a table in Excel

This kind of question sometimes gets me lost. The fact is that Excel is one big spreadsheet. True, it has no borders, no sheet layout, etc. (as it is in Word - and this misleads many).

Most often, this question refers to the creation of table borders (table formatting). This is done quite easily: first select the entire table, then go to the section: " Home/Format as table". In the pop-up window, select the design you need: the type of frame, its color, etc. (see Fig. 16).

Rice. 16. Format as a table

The result of formatting is shown in fig. 17. In this form, this table can be transferred, for example, to a Word document, make a clear screenshot of it, or simply present it to the audience on the screen. In this form, it is much easier to "read".

5) How to build a graph/chart in Excel

To build a chart, you will need a ready-made table (or at least 2 columns with data). First of all, you need to add a chart, for this click: " Insert/Pie/3D Pie" (for example). The choice of diagram depends on the requirements (which you follow) or your preferences.

Then you can choose its style and design. I recommend not to use weak and dull colors (light pink, yellow, etc.) in the charts. The fact is that usually a diagram is made to show it - and these colors are poorly perceived both on the screen and when printed (especially if the printer is not the best).

Actually, it remains only to specify the data for the chart. To do this, click on it with the left mouse button: on top, in the Excel menu - the section “ Working with charts

Rice. 23. Received chart

Actually, on this and this diagram I will sum up the results. In the article I have collected (as it seems to me) all the most basic questions that novice users have. Having dealt with these basic features, you yourself will not notice how new “chips” will begin to learn faster and faster.

By learning to use 1-2 formulas, many other formulas will be "created" in the same way!

How to learn to work in Excel?

Microsoft Excel is a very powerful program that facilitates the work of many professionals from different industries. It is used by secretaries, accountants, directors, storekeepers, programmers, and many others. The program simplifies work with any kind of formulas, tables, charts and other data.

With its help, charts, forms, reporting, balance sheets, databases, order histories, taxes, salaries, price lists, summaries, schedules, and much, much more are compiled.

Excel makes it possible to enter, correct, and store any data arrays, while working with graphic objects, charts and graphs of various types and purposes.

A huge advantage is the connection between the operands, in which changing one value automatically recalculates all data associated with it. This connection is carried out with the help of formulas.

How to master the program?

It will take a lot of time to master this program, since the functionality is huge, the possibilities are very wide, and a thorough study of all the available functions and options for working with them is required.

You can study both independently and take professional exce courses l https://spbshb.ru/excel-courses. By visiting professional courses, you can easily figure out how to work with Excel tables, because this is the main tool of the program and most of the time work takes place in the spreadsheet environment. Instructors with many years of experience will show and tell all the main functions of the program, as well as reveal professional secrets that can greatly facilitate subsequent work in the program.

How to learn to work in excel on your own?

There are many tutorials available online for self-study. These are articles devoted to specific functions, and lessons step by step revealing the capabilities of the program and how to use them in different tasks, and video tutorials from professionals, and even webinars with live communication. Some of the lessons are presented for free, but you will have to pay for some, usually these are author's lessons and methods for using the program from professionals.

How to work in Excel?

The first step is to figure out how to work with a table in excel, since this is the main element that contains information for subsequent work with it.

A table is made up of cells, and each cell can contain certain data. You can add data to a cell simply by selecting it and starting to enter it from the keyboard. You can edit a cell with already entered data by double-clicking on it, in which case the editing function will start and you can delete or change any character contained in the cell.

Formulas

The second most important in the program are formulas, with their help you can perform a variety of tasks.

Formulas are divided into subtypes: arithmetic, for calculating seniority, age, time, adding negative numbers, rounding, highlighting with color and font, for calculating in several cells, etc. There are a lot of formulas, each performs a specific role, with their help you can perform many calculations and perform a huge number of manipulations between data in different cells of tables.

Each formula must be studied separately, considering its purpose and methods of use.

Let's take a look at a few useful tricks.

The application has a huge number of functions and ways to work with them. Some methods are more convenient and faster than others, let's look at the most interesting of them below.

How to deal with extra spaces?

Very often, when copying information, extra spaces appear, the program perceives them as text, and they should be deleted either manually (if there are not many spaces), or using the “Search and Replace” function. In the latter option, you need to enter a space in the search bar by simply clicking on it or copying it from the text, and click on the “Replace All” button. Excel will automatically find all spaces and remove them.

Variety of sheets

The program has the ability to create an unlimited number of sheets in which you can put any data. The most useful thing is that the cells in all sheets are interconnected and with the help of formulas you can call up and apply data from any cell in any available sheet. This opens up huge opportunities for generating reports and databases.

Select all cells

If you want to select all the cells on the sheet, then there is no need to do this with the mouse, just press CTRL + A.

Making cells with solid lines.

In some cases, you may need to beautifully design certain cells. To do this, select them, and call the menu by pressing the right mouse button. Next, click "Format Cells" - "Border", and select the necessary lines. You can limit a cell to the top, bottom, left, or right. If several cells are selected, then restrictions can be applied between them by clicking on the appropriate button.

Data search

A very useful feature for finding the right data. To call the search, press CTRL + F, a search window will open with the ability to enter the search phrase. If you are looking for a part of a phrase, for example, not the word “formula”, but all its spellings, then enter “for”, and the remaining characters can be replaced either with “?” or “*”. The question mark is responsible for one character, i.e. “for??”, there are values: formula, formula, formulas, etc. An asterisk means any number of characters, i.e. “for*”, there are: formula, formulas, formulas, formulas, formulas, etc.

Outcome

The program is very useful and diverse, capable of performing a huge number of functions and can be useful to specialists from different fields. It can take more than one year to master it, but it's worth it.

Purchase of scrap metal

The main and priority activity of the company "Metallrutorg" is the purchase of scrap metal. It is beneficial for any supplier to work not with small resellers, but with a permanent partner who offers favorable prices and loyal conditions. Many enterprises are wondering how to make additional profit, and how to turn over unnecessary scrap metal? In this case, the main thing is to find a reliable company that is officially registered and […]



Convenient movement in the Crimea

Of course, the city transport of Crimea in the form of shuttle minibuses runs regularly and on schedule. But this type of movement provides a lot of inconvenience. The famous peninsula of Crimea is one of the most picturesque resorts. In addition to the majestic rocky mountains, it is famous for its incredibly healing air. Well, the friendly Black Sea completes the list of the main advantages of the Crimea. The peninsula is quite far away for most Russians, […]



Road in Crimea

Many people who want to spend their holidays in the South choose Crimea. This is due to the fact that the island is incredibly picturesque, in addition, there are a huge number of different areas. For example, purely resort areas where you can have a great time on the beach or hiking places where you can go to the mountains or other places of interest for hiking. That is why the island is so popular […]

Possession of a set of office applications, especially word and excel, is today a necessary, and in some cases mandatory skill in many professions and areas of life. Our word and excel training program for beginners is intended primarily for those who need to master these programs comprehensively and thoroughly, with a detailed description of all the necessary tools and settings. Word and Excel courses, this is the minimum, having mastered which, you will be able to work independently in almost any industry, performing basic office tasks for the circulation of documents and various types of reporting. Many people believe that learning Word and Excel for dummies is not enough to quickly advance in education and in the profession, but under such a somewhat joking name lies a well-designed Word and Excel training program that allows you to master the subject easily and efficiently, and most importantly, the excel learning program for beginners step by step allows you to clarify the direction being mastered at each stage.

Functions and features of Microsoft Excel

MS Excel is a universal and multifunctional spreadsheet editor, whose functions are not limited to creating and editing spreadsheets. With this application, you can significantly increase the efficiency and speed of solving a wide range of professional tasks - from creating databases, analyzing them and grouping them using filters to complex calculations with various coefficients, visualization and forecasting.

Excel skills are indispensable for professionals working in the following areas:

• Financial and economic analytics;
• Accounting;
• Marketing and sociological research;
• Scientific activity;
• Banking and work with loans;
• IT-sphere, SEO-optimization and promotion;
• Working with databases in various fields.

In the above areas, you will need advanced Excel skills, but for managers and other office workers, in most cases, basic spreadsheet skills are sufficient. Firstly, it will be a great advantage in finding employment, and secondly, it will really help you cope with work easier and faster.

Demand for Word and Excel Specialists

Despite the fact that it is now possible to learn how to work in Excel from scratch in almost any training center for computer technology, there are not enough specialists who are proficient in these programs at the proper level. Most people who come to ms office excel courses and think that they can more or less work in these programs, in fact, do not even reach the very entry level. But, the correct understanding of our skills allows us not only to choose the right training course, but also to be able to apply for a certain job. The demand for Word and Excel specialists is one side of the coin, the other side is their professionalism and skill. Therefore, everyone needs to go through excel for dummies, at least in order to understand at what level of proficiency in word and excel programs we are, and whether we are doing it right that we are studying excel from scratch or do we need ms excel training at a higher level. And even if you have an initial level of knowledge of Excel and Word programs, but you did the study yourself, then in the courses you will be able to systematize your knowledge and find a lot of new things in seemingly understandable things. Well, if you are trained at more complex levels of office software, then it is better to immediately resort to the services of specialized centers - you will save not only time and money, but also your nerves.

You will need knowledge of the following MS Excel tools:

• Data storage - creation of tables, databases, lists, sheets and books;
• Data processing methods - search, filtering, formatting and grouping by parameters;
• Calculation, analysis and forecasting methods based on available data;
• Data visualization in graphs and charts;
• Logical, text, mathematical and statistical functions;
• Formulas for fast computational operations with large data arrays;
• Macros, pivot tables and other tools.

Where to start learning, with Word or Excel

Traditionally, the study begins with the word, the program allows you to fundamentally master the keyboard and the basic skills of working with text and information of a different nature. After mastering the Word program, the Excel program is easier to master, if only because you have a confident typing skill on the keyboard. Working in excel training for dummies implies computer skills not only at a basic level, but also at the level of confident use. If Word is a program that is necessary for almost everyone, as a tool for creating, editing, sending and receiving any document, then Excel is a specialized program that may not be necessary for everyone, but mastering this tool, even at an elementary level, will open before you lots of possibilities. Therefore, in addition to Word, it is highly recommended to take Excel for dummies training. This bundle - word and excel, is very popular as a popular skill for any specialist.

How to quickly master Word and Excel

For experienced computer users, using the main components of the software package at a superficial level is not difficult. Opening a file, reading or typing text, saving a document - these are actions that absolutely every person who works with a computer faces. Having mastered any application included in Microsoft Office, you will be able to understand the logic of the interface and menus, and basic operations will become intuitive in the future.

The situation is quite different if you are a novice PC user or if you need Office tools for professional purposes. In the first case, the lack of skills in using office applications, in particular, the Word text editor, is a serious obstacle to further mastering all the capabilities of a personal computer. Therefore, the study of Office for beginners should be conducted on a systematic basis with a detailed mastery of the basic functions, menus and toolbars.

Mastering MS Office for professional use is even more difficult - most ordinary users do not know about 95% of the capabilities of the Word text editor and the Excel spreadsheet editor, if their work does not require it.

Self-study requires a lot of time, self-discipline and the ability to concentrate, so the best solution would be to study at specialized courses dedicated to learning Word and Excel from scratch. This will help you Computer literacy courses (Windows, Word, Excel) and Excel courses (Excel) - basic level that take place in the GCCPE. If you aspire to become a professional spreadsheet editor, you are at your service.