Working with xel tables. Formulas in Excel

Anyone who uses a computer in their daily work has, in one way or another, encountered the Excel office application, which is part of the standard Microsoft Office package. It is available in any version of the package. And quite often, when starting to get acquainted with the program, many users wonder whether they can use Excel on their own?

What is Excel?

First, let's define what Excel is and what this application is needed for. Many people have probably heard that the program is a spreadsheet editor, but the principles of its operation are fundamentally different from the same tables created in Word.

If in Word a table is more of an element in which a text or table is displayed, then a sheet with an Excel table is, in fact, a unified mathematical machine that is capable of performing a wide variety of calculations based on specified data types and formulas by which this or that mathematical or algebraic operation.

How to learn to work in Excel on your own and is it possible to do it?

As the heroine of the film “Office Romance” said, you can teach a hare to smoke. In principle, nothing is impossible. Let's try to understand the basic principles of the application's functioning and focus on understanding its main capabilities.

Of course, reviews from people who understand the specifics of the application say that you can, say, download some tutorial on how to work in Excel, however, as practice shows, and especially the comments of novice users, such materials are very often presented in a too abstruse form, and It can be quite difficult to figure out.

It seems that the best training option would be to study the basic capabilities of the program, and then apply them, so to speak, “by scientific poking.” It goes without saying that you first need to consider the basic functional elements of Microsoft Excel (the program lessons indicate exactly this) in order to get a complete picture of the principles of operation.

Key elements to pay attention to

The very first thing the user pays attention to when launching the application is a sheet in the form of a table, in which cells are located, numbered in different ways, depending on the version of the application itself. In earlier versions, columns were designated by letters, and rows by numbers and numbers. In other releases, all markings are presented exclusively in digital form.

What is it for? Yes, only so that it is always possible to determine the cell number for specifying a certain calculation operation, similar to how coordinates are specified in a two-dimensional system for a point. Later it will be clear how to work with them.

Another important component is the formula bar - a special field with an “f x” icon on the left. This is where all operations are specified. At the same time, the mathematical operations themselves are designated in exactly the same way as is customary in the international classification (equal sign “=”, multiplication “*” division “/”, etc.). Trigonometric quantities also correspond to international notations (sin, cos, tg, etc.). But this is the simplest thing. More complex operations will have to be mastered with the help of the help system or specific examples, since some formulas may look quite specific (exponential, logarithmic, tensor, matrix, etc.).

At the top, as in other office programs, there is the main panel and the main menu sections with the main operation items and quick access buttons to a particular function.

and simple operations with them

Consideration of the question is impossible without a key understanding of the types of data entered in table cells. Let us immediately note that after entering some information, you can press the enter button, the Esc key, or simply move the rectangle from the desired cell to another - the data will be saved. Editing a cell is done by double-clicking or pressing the F2 key, and upon completion of data entry, saving occurs only by pressing the Enter key.

Now a few words about what can be entered in each cell. The format menu is called up by right-clicking on the active cell. On the left there is a special column indicating the data type (general, numeric, text, percentage, date, etc.). If the general format is selected, the program, roughly speaking, itself determines what exactly the entered value looks like (for example, if you enter 01/01/16, the date January 1, 2016 will be recognized).

When entering a number, you can also use an indication of the number of decimal places (by default, one character is displayed, although when entering two, the program simply rounds the visible value, although the true value does not change).

When using, say, a text data type, whatever the user types will be displayed exactly as typed on the keyboard, without modification.

Here's what's interesting: if you hover the cursor over the selected cell, a cross will appear in the lower right corner, by pulling it while holding down the left mouse button, you can copy the data to the cells following the desired one in order. But the data will change. If we take the same date example, the next value would be January 2, and so on. This type of copying can be useful when specifying the same formula for different cells (sometimes even with cross calculations).

When it comes to formulas, for the simplest operations you can use a two-pronged approach. For example, for the sum of cells A1 and B1, which must be calculated in cell C1, you need to place the rectangle in the C1 field and specify the calculation using the formula “=A1+B1”. You can do it differently by setting the equality “=SUM(A1:B1)” (this method is more used for large gaps between cells, although you can use the automatic sum function, as well as the English version of the SUM command).

Excel program: how to work with Excel sheets

When working with sheets, you can perform many actions: add sheets, change their names, delete unnecessary ones, etc. But the most important thing is that any cells located on different sheets can be interconnected by certain formulas (especially when large amounts of information of different types are entered).

How to learn to work in Excel on your own in terms of use and calculations? It's not that simple here. As reviews from users who have mastered this spreadsheet editor show, it will be quite difficult to do this without outside help. You should at least read the help system of the program itself. The simplest way is to enter cells in the same formula by selecting them (this can be done both on one sheet and on different ones. Again, if you enter the sum of several fields, you can write “=SUM”, and then simply select one by one while holding down the Ctrl key the necessary cells.But this is the most primitive example.

Additional features

But in the program you can not only create tables with various types of data. Based on them, in a couple of seconds you can build all kinds of graphs and diagrams by specifying either a selected range of cells for automatic construction, or specifying it manually when entering the corresponding menu.

In addition, the program has the ability to use special add-ons and executable scripts based on Visual Basic. You can insert any objects in the form of graphics, video, audio or anything else. In general, there are enough opportunities. And here only a small fraction of everything that this unique program is capable of is touched upon.

What can I say, with the right approach, it can calculate matrices, solve all kinds of equations of any complexity, find, create databases and connect them with other applications like Microsoft Access and much more - you just can’t list it all.

Bottom line

Now, it’s probably already clear that the question of how to learn to work in Excel on your own is not so easy to consider. Of course, if you master the basic principles of working in the editor, setting the simplest operations will not be difficult. User reviews indicate that you can learn this in a maximum of a week. But if you need to use more complex calculations, and even more so, work with reference to databases, no matter how much anyone wants it, you simply cannot do without special literature or courses. Moreover, it is very likely that you will even have to improve your knowledge of algebra and geometry from the school course. Without this, you can’t even dream of fully using the spreadsheet editor.

Microsoft Excel is convenient for creating tables and making calculations. A workspace is a set of cells that can be filled with data. Subsequently – format, use for building graphs, charts, summary reports.

Working in Excel with tables for novice users may seem difficult at first glance. It differs significantly from the principles of creating tables in Word. But we'll start small: by creating and formatting a table. And at the end of the article, you will already understand that you cannot imagine a better tool for creating tables than Excel.

HOW TO CREATE A TABLE IN EXCEL FOR DUMMIES. Step by step

Working with tables in Excel for dummies is not rushed. You can create a table in different ways, and for specific purposes, each method has its own advantages. Therefore, first let’s visually assess the situation.

Take a close look at the spreadsheet worksheet:

This is a set of cells in columns and rows. Essentially a table. Columns are indicated in Latin letters. Lines are numbers. If we print this sheet, we will get a blank page. Without any boundaries.

First let's learn how to work with cells, rows and columns.

Video on the topic: Excel for beginners

How to select a column and row

To select the entire column, click on its name (Latin letter) with the left mouse button.

To select a line, use the line name (by number).

To select several columns or rows, left-click on the name, hold and drag.

To select a column using hot keys, place the cursor in any cell of the desired column - press Ctrl + spacebar. To select a line – Shift + spacebar.

Video on the topic: TOP 15 best Excel tricks

How to change cell borders

If the information does not fit when filling out the table, you need to change the cell borders:

Move manually by clicking the cell border with the left mouse button.

When a long word is written in a cell, double-click on the column/row border. The program will automatically expand the boundaries.

If you need to maintain the column width, but increase the row height, use the “Wrap Text” button on the toolbar.

To change the width of columns and height of rows at once in a certain range, select an area, increase 1 column/row (move manually) - the size of all selected columns and rows will automatically change.

Note. To return to the previous size, you can click the “Cancel” button or the hotkey combination CTRL+Z. But it works when you do it right away. Later it won't help.

To return the lines to their original boundaries, open the tool menu: “Home” - “Format” and select “Auto-fit line height”

This method is not relevant for columns. Click “Format” - “Default Width”. Let's remember this number. Select any cell in the column whose borders need to be “returned”. Again, “Format” - “Column Width” - enter the indicator specified by the program (usually 8.43 - the number of characters in the Calibri font with a size of 11 points). OK.

How to insert a column or row

Select the column/row to the right/below the place where you want to insert the new range. That is, the column will appear to the left of the selected cell. And the line is higher.

Right-click and select “Insert” from the drop-down menu (or press the hotkey combination CTRL+SHIFT+"=").

Mark the “column” and click OK.

Advice. To quickly insert a column, select the column in the desired location and press CTRL+SHIFT+"=".

All these skills will come in handy when creating a table in Excel. We will have to expand the boundaries, add rows/columns as we work.

Step-by-step creation of a table with formulas

We manually fill in the header - the names of the columns. We enter the data and fill out the lines. We immediately put the acquired knowledge into practice - we expand the boundaries of the columns, “select” the height for the rows.

To fill out the “Cost” column, place the cursor in the first cell. We write “=”. Thus, we signal to Excel: there will be a formula here. Select cell B2 (with the first price). Enter the multiplication sign (*). Select cell C2 (with quantity). Press ENTER.

When we move the cursor to a cell with a formula, a cross will form in the lower right corner. It points to the autocomplete marker. We grab it with the left mouse button and drag it to the end of the column. The formula will be copied to all cells.

Let's mark the boundaries of our table. Select the range with the data. Click the button: “Home” - “Borders” (on the main page in the “Font” menu). And select “All borders”.

Column and row borders will now be visible when printing.

Using the Font menu, you can format Excel table data as you would in Word.

Change, for example, the font size, make the header “bold”. You can center the text, assign hyphens, etc.

HOW TO CREATE A TABLE IN EXCEL: STEP-BY-STEP INSTRUCTIONS

The simplest way to create tables is already known. But Excel has a more convenient option (in terms of subsequent formatting and working with data).

Let's make a “smart” (dynamic) table:

Go to the “Insert” tab - “Table” tool (or press the hotkey combination CTRL + T).

In the dialog box that opens, specify the range for the data. Please note that the table has subheadings. Click OK. It's okay if you don't guess the range right away. The “smart table” is mobile and dynamic.

Note. You can take a different path - first select a range of cells, and then click the “Table” button.

Now enter the necessary data into the finished frame. If you need an additional column, place the cursor in the cell designated for the name. Enter the name and press ENTER. The range will automatically expand.

If you need to increase the number of lines, hook it in the lower right corner to the autofill marker and drag it down.

HOW TO WORK WITH A TABLE IN EXCEL

With the release of new versions of the program, working with tables in Excel has become more interesting and dynamic. When a smart table is formed on a sheet, the “Working with Tables” - “Design” tool becomes available.

Here we can give the table a name and change its size.

Various styles are available, the ability to convert the table into a regular range or a summary report.

Features of dynamic MS Excel spreadsheets huge. Let's start with basic data entry and autofill skills:

Select a cell by left-clicking on it. Enter a text/numeric value. Press ENTER. If you need to change the value, place the cursor in the same cell again and enter new data.
When you enter duplicate values, Excel will recognize them. Just type a few characters on the keyboard and press Enter.

To apply a formula to an entire column in a smart table, just enter it in the first cell of that column. The program will copy to other cells automatically.

To calculate the totals, select the column with the values plus an empty cell for the future total and press the “Sum” button (the “Editing” tool group on the “Home” tab or press the hotkey combination ALT+"=").

If we click on the arrow to the right of each header subheading, we will get access to additional tools for working with table data.

Sometimes the user has to work with huge tables. To see the results, you need to scroll through more than one thousand lines. Deleting rows is not an option (the data will be needed later). But you can hide it.

For this purpose, use numerical filters (picture above). Uncheck the boxes next to the values that should be hidden.

In the third part of the materials “Excel 2010 for Beginners” we will talk about algorithms for copying formulas and a system for tracking errors when compiling them. In addition, you will learn what functions are and how to present data or calculation results graphically using charts and sparklines.

Introduction

In Part 2 of the Excel 2010 for Beginners series, we explored some table formatting options, such as adding rows and columns, and merging cells. In addition, you learned how to perform arithmetic operations in Excel and how to create simple formulas.

At the beginning of this material, we will talk a little more about formulas - we will tell you how to edit them, talk about the error notification system and error tracking tools, and also find out what algorithms are used in Excel to copy and move formulas. Next, we'll introduce another important concept in spreadsheets: functions. Finally, you will learn how in MS Excel 2010 you can present data and the results obtained in a visual (graphical) form using charts and sparklines.

Formula editing and error tracking system

All formulas that are in the table cells can be edited at any time. To do this, simply select the cell with the formula and then click on the formula bar above the table, where you can immediately make the necessary changes. If it is more convenient for you to edit the content directly in the cell itself, then double-click on it.

After finishing editing, press the Enter or Tab keys, after which Excel will recalculate taking into account the changes and display the result.

Quite often it happens that you entered the formula incorrectly or after deleting (changing) the contents of one of the cells referenced by the formula, an error occurs in the calculations. In this case, Excel will certainly notify you about this. An exclamation mark within a yellow diamond will appear next to the cell containing the incorrect expression.

In many cases, the application will not only notify you that there is an error, but will also indicate what exactly was done wrong.

Decoding errors in Excel:

##### - the result of executing a formula using date and time values is a negative number or the processing result does not fit in the cell;
#VALUE!- an invalid type of operator or formula argument is used. One of the most common mistakes;
#DIV/0!- the formula attempts to divide by zero;
#NAME?- the name used in the formula is incorrect and Excel cannot recognize it;
#N/A- uncertain data. Most often, this error occurs when a function argument is defined incorrectly;
#LINK!- the formula contains an invalid cell reference, for example, a cell that has been deleted.
#NUMBER!- the result of the calculation is a number that is too small or too large to be used in MS Excel. The range of displayed numbers is from -10,307 to 10,307.
#EMPTY!- the formula specifies the intersection of areas that actually do not have common cells.

Let us remind you once again that errors can appear not only due to incorrect data in the formula, but also due to the content of incorrect information in the cell to which it refers.

Sometimes, when there is a lot of data in the table, and the formulas contain a large number of references to various cells, then serious difficulties may arise when checking the expression for correctness or finding the source of the error. To make the user's work easier in such situations, Excel has a built-in tool that allows you to highlight influencing and dependent cells on the screen.

Influential cells are the cells referenced by formulas, and dependent cells, on the contrary, contain formulas that refer to the addresses of cells in the spreadsheet.

To graphically display the connections between cells and formulas using the so-called dependency arrow, you can use the commands on the ribbon Influential cells And Dependent cells in Group Formula dependencies in the tab Formulas.

For example, let's see how the final result of savings is formed in our test table, compiled in the previous two parts:

Despite the fact that the formula in this cell looks like “=H5 - H12”, Excel, using dependency arrows, can show all the values that are involved in calculating the final result. After all, cells H5 and H12 also contain formulas that have links to other addresses, which in turn can contain both formulas and numerical constants.

To remove all arrows from a worksheet, in the group Formula dependencies on the tab Formulas, press the button Remove arrows.

Relative and absolute cell addresses

The ability to copy formulas in Excel from one cell to another and automatically change the addresses contained in them exists thanks to the concept relative addressing. So what is it?

The fact is that Excel understands the addresses of cells entered into the formula not as a link to their real location, but as a link to their location relative to the cell in which the formula is located. Let's explain with an example.

For example, cell A3 contains the formula: “=A1+A2”. For Excel, this expression does not mean that you need to take the value from cell A1 and add the number from cell A2 to it. Instead, he interprets this formula as “taking a number from a cell located in the same column, but two rows higher, and adding it with the value of a cell located in the same column, located one row higher.” When copying this formula to another cell, for example D3, the principle of determining the addresses of the cells included in the expression remains the same: “take a number from a cell located in the same column, but two rows higher and add it with...”. Thus, after copying to D3, the original formula will automatically take the form “=D1+D2”.

On the one hand, this type of link gives users an excellent opportunity to simply copy the same formulas from cell to cell, eliminating the need to enter them over and over again. On the other hand, in some formulas it is necessary to constantly use the value of one specific cell, which means that the reference to it should not change and depend on the location of the formula on the sheet.

For example, imagine that in our table the values of budget expenditures in rubles will be calculated based on dollar prices multiplied by the current exchange rate, which is always written in cell A1. This means that when you copy a formula, the reference to that cell should not change. Then in this case one should use not relative, but absolute link, which will always remain the same when copying an expression from one cell to another.

Using absolute references, you can command Excel when copying a formula:

keep the column reference permanently, but change the column references
change row references but keep column reference
keep references to both columns and rows constant.

$A$1 - the link always refers to cell A1 (absolute link);
A$1 - the link always refers to row 1, and the path to the column can change (mixed link);
$A1 - the link always refers to column A, and the row path can change (mixed link).

To enter absolute and mixed links, use the “F4” key. Select the cell for the formula, enter the equal sign (=) and click on the cell to which you want to set an absolute link. Then press the F4 key, after which the program will place dollar signs ($) in front of the column letter and row number. Pressing F4 repeatedly allows you to move from one type of link to another. For example, a reference to E3 will cycle through $E$3, E$3, $E3, E3, and so on. If desired, $ signs can be entered manually.

Functions

Functions in Excel are predefined formulas that perform calculations in a specified order using given values. In this case, calculations can be both simple and complex.

For example, determining the average value of five cells can be described by the formula: =(A1 + A2 + A3 + A4 + A5)/5, or you can use a special AVERAGE function, which will reduce the expression to the following form: AVERAGE(A1:A5). As you can see, instead of entering all cell addresses into the formula, you can use a specific function, specifying their range as an argument.

There is a separate tab on the ribbon for working with functions in Excel. Formulas, on which all the main tools for working with them are located.

It should be noted that the program contains more than two hundred functions that can facilitate the performance of calculations of varying complexity. Therefore, all functions in Excel 2010 are divided into several categories, grouping them according to the type of tasks they solve. What exactly these tasks are becomes clear from the names of the categories: Financial, Logical, Text, Mathematical, Statistical, Analytical and so on.

You can select the required category on the ribbon in the group Function Library in the tab Formulas. After clicking on the arrow next to each category, a list of functions opens, and when you hover the cursor over any of them, a window with its description appears.

Entering functions, like formulas, begins with an equal sign. After comes function name, in the form of an abbreviation of capital letters indicating its meaning. Then in parentheses indicate function arguments- data used to obtain the result.

The argument can be a specific number, an independent cell reference, a whole series of references to values or cells, or a range of cells. At the same time, some functions have arguments that are text or numbers, others - time and dates.

Many functions can take several arguments at once. In this case, each of them is separated from the next by a semicolon. For example, the function =PRODUCT(7, A1, 6, B2) calculates the product of four different numbers indicated in parentheses, and accordingly contains four arguments. Moreover, in our case, some arguments are specified explicitly, while others are the values of certain cells.

You can also use another function as an argument, which in this case is called nested. For example, the function =SUM(A1:A5; AVERAGE(B5:B10)) sums the values of cells in the range from A1 to A5, as well as the average value of the numbers located in cells B5, B6, B7, B8, B9 and B10.

Some simple functions may have no arguments at all. So, using the =TDATE() function you can get the current time and date without using any arguments.

Not all functions in Ecxel have a simple definition, like the SUM function, which adds up selected values. Some of them have complex syntactic writing and also require many arguments, which must also be of the correct types. The more complex the function, the more difficult it is to compose it correctly. And the developers took this into account by including in their spreadsheets an assistant for composing functions for users - Function Wizard.

To start introducing a function using Function Wizards, click on the icon Insert function (fx), located to the left of Formula lines.

Also the button Insert function you will find it on the ribbon above in the group Function Library in the tab Formulas. Another way to open the Function Wizard is with a keyboard shortcut Shift+F3.

After opening the assistant window, the first thing you will have to do is select a function category. To do this, you can use the search field or drop-down list.

In the middle of the window a list of functions of the selected category is displayed, and below is a brief description of the function selected by the cursor and help on its arguments. By the way, the purpose of a function can often be determined by its name.

Having made the necessary selection, click on the OK button, after which the Function Arguments window will appear.

In the upper left corner of the window the name of the selected function is indicated, under which there are fields for entering the necessary arguments. To the right of them, after the equal sign, the current values of each argument are indicated. At the bottom of the window there is help information indicating the purpose of the function and each argument, as well as the current result of the calculation.

Links to cells (or their range) in fields for entering arguments can be entered either manually or using the mouse, which is much more convenient. To do this, simply left-click on the desired cells on the open sheet or circle their required range. All values will be automatically entered into the current input field.

If the Function Arguments dialog box prevents you from entering the necessary data, blocking the worksheet, you can temporarily reduce it by clicking on the button on the right side of the argument input field.

Pressing it again will restore the normal size.

After entering all the required values, all you have to do is click on the OK button and the calculation result will appear in the selected cell.

Diagrams

Quite often, the numbers in the table, even if properly sorted, do not provide a complete picture of the calculation results. To get a visual representation of the results, MS Excel allows you to create various types of charts. This can be either a regular histogram or graph, or a radar, pie or exotic bubble chart. Moreover, the program has the ability to create combination charts from various types, saving them as a template for future use.

A chart in Excel can be placed either on the same sheet where the table is already located, in which case it is called “embedded”, or on a separate sheet, which will become called a “chart sheet”.

As an example, let’s try to visualize the monthly expenses data indicated in the table we created in the previous two parts of the “Excel 2010 for Beginners” materials.

To create a chart based on tabular data, first select the cells whose information you want to display graphically. The appearance of the chart depends on the type of data selected, which should be in columns or rows. Column headings should be above the values, and row headings should be to the left of them. In our case, we select cells containing the names of months, expense items and their values.

Then, on the ribbon in the tab Insert in Group Diagrams select the desired chart type and view. To see a brief description of a particular type and type of diagram, you need to hold the mouse pointer over it.

In the lower right corner of the block Diagrams there is a small button Create a chart, with which you can open a window Inserting a chart, displaying all types, types and templates of charts.

In our example, let's select the 3D Stacked Bar Chart and click OK, and the chart will appear on the worksheet.

Also pay attention to the appearance of an additional bookmark on the ribbon. Working with charts, containing three more tabs: Constructor, Layout And Format.

On the tab Constructor You can change the chart type, swap rows and columns, add or remove data, choose its layout and style, and also move the chart to another sheet or another tab in the workbook.

On the tab Layout contains commands that allow you to add or remove various chart elements, which can be easily formatted using the bookmark Format.

Tab Working with charts appears automatically whenever you select a diagram and disappears when you work with other elements of the document.

Formatting and editing charts

When first creating a chart, it is very difficult to determine in advance which type of chart will most clearly represent the selected tabular data. Moreover, it is quite likely that the location of the new diagram on the sheet will not be where you would like it to be, and its dimensions may not suit you. But it doesn’t matter - the original type and appearance of the diagram can be easily changed, you can also move it to any point in the workspace of the sheet or adjust the horizontal and vertical dimensions.

How to quickly change the chart type on a tab Constructor in Group Type located on the left, click the button Change chart type. In the window that opens on the left, first select the appropriate chart type, then its subtype and click OK. The diagram will be automatically rebuilt. Try to select the type of diagram that will most accurately and clearly demonstrate the purpose of your calculations.

If the data in the chart is not displayed as expected, try swapping the display of rows and columns by clicking the button, Row column in Group Data on the tab Constructor.

Having selected the desired type of diagram, you can work on its appearance by applying the layouts and styles built into the program. Excel, due to its built-in solutions, provides users with ample opportunities to choose the relative arrangement of chart elements, their display, as well as color design. Selecting the desired layout and style is done on the tab Constructor in groups with self-explanatory names Chart Layouts And Chart styles. Moreover, each of them has a button Extra options, revealing a full list of proposed solutions.

However, a chart that is created or formatted using built-in layouts and styles is not always completely satisfying to users. The font size is too large, the legend takes up too much space, the data labels are in the wrong place, or the chart itself is too small. In a word, there is no limit to perfection, and in Excel, everything that you don’t like can be corrected yourself to your “taste” and “color”. The point is that a chart consists of several main blocks that you can format.

Chart area - the main window where all other components of the diagram are located. By hovering the mouse cursor over this area (a black crosshair appears) and holding down the left mouse button, you can drag the diagram to any part of the sheet. If you want to change the size of the diagram, then move the mouse cursor over any of the corners or the middle of the side of its frame, and when the pointer takes the form of a double-sided arrow, drag it in the desired direction.

Chart area - includes vertical and horizontal axes, a series of data, as well as main and additional grid lines (walls).

Data series - data presented in graphical form (diagram, histogram, graph, etc.). May have data labels that display exact numbers for the rows or series of the chart.

Value axis and category axis - numerical parameters located along the vertical and horizontal lines, based on which you can evaluate the chart data. May have their own division labels and headings.

Grid lines - visually represent the values of the axes and are placed on the side panels called walls.

Legend - decoding the meanings of rows or lines.

Any Excel user has the opportunity to independently change the styles and artistic presentation of each of the above chart components. You can choose fill color, border style, line thickness, add volume, shadows, glow and anti-aliasing to selected objects. At any time, you can change the overall size of the diagram, increase/decrease any area of it, for example, enlarge the diagram itself and reduce the legend, or even cancel the display of unnecessary elements. You can change the angle of the diagram, rotate it, make it three-dimensional or flat. In a word, MS Excel 2010 contains tools that allow you to give your diagram the most convenient image for your perception.

To change chart components, use the tab Layout, located on the tape in the area Working with charts.

Here you will find commands with the names of all parts of the diagram, and by clicking on the corresponding buttons, you can proceed to formatting them. There are other, easier ways to change chart components. For example, you just need to hover the mouse cursor over the desired object and double-click on it, after which the window for formatting the selected element will immediately open. You can also use the commands of the context menu, which is called by right-clicking on the desired component.

It's time to transform the appearance of our test chart using different methods. First, let's increase the size of the diagram a little. To do this, place the mouse cursor in any corner of the diagram area and, after changing its appearance to a double-sided arrow, drag the pointer in the desired direction(s).

Now let's edit the appearance of the data series. Double-click with the left mouse button on any colored cylindrical area of the diagram (each row is marked with its own unique color), after which the settings window of the same name will open.

Here, on the left in the column, a list of parameters that you can manually change for a given diagram component is displayed, and on the right is an editing area with the current values.

Here you can choose various row display options, including shape type, gaps between shapes, area fill, border color, and so on. Try changing the parameters in each section yourself and see how this will affect the appearance of the diagram.

As a result, in the window Data series format we removed the front gap, made the side gap equal to 20%, added a shadow on the outside and a little volume on top.

Now right-click on any colored cylindrical area and select the item in the context menu that opens Add data signatures. After this, monthly values for the selected expense item will appear on the chart. Do the same with all the remaining rows. By the way, the data signatures themselves can also be formatted later: change the font size, color, its style, change the location of the values, and so on. To do this, also use the context menu by right-clicking directly on the value itself and select the command Data signature format.

To format the axes, let's use the tab Layout on the tape at the top. In Group Axles click on the button of the same name, select the desired axis, and then Additional Primary Horizontal/Vertical Axis Options.

The principle of arrangement of control elements in the window that opens Axis format not much different from the previous ones - the same column with parameters on the left and a zone of changeable values on the right. Here we didn’t change anything much, just adding light gray shadows to the value labels of both the vertical and horizontal axes.

Finally, let's add a title to the chart by clicking on tab Layout in Group Signatures by button Chart title. Next, let’s reduce the legend area, increase the plotting area and see what we got:

As you can see, Excel's built-in chart formatting tools really provide ample opportunities for users, and the visual presentation of tabular data in this figure is strikingly different from the original version.

Sparklines or infocurves

Concluding the topic of charts in spreadsheets, let's look at the new visualization tool that became available in Ecxel 2010. Sparklines or infocurves- These are small charts located in one cell that allow you to visually display changes in values directly next to the data.

Thus, taking up very little space, sparklines are designed to demonstrate the trend of data in a compact graphical form. It is recommended to place infocurves in the adjacent cell with the data used.

For approximate construction of sparklines, let's use our ready-made table of monthly income and expenses. Our task will be to build information curves showing monthly trends in changes in budget revenue and expenditure items.

As mentioned above, our small charts should be located next to the data cells that are involved in their formation. This means that we need to insert an additional column into the table to place them immediately after the data for the last month.

Now select the desired empty cell in the newly created column, for example H5. Next on the ribbon in the tab Insert in Group Sparklines select the appropriate curve type: Schedule, bar chart or Win/Loss.

After this a window will open Creating Sparklines, in which you will need to enter a range of cells with data based on which the sparkline will be created. This can be done either by typing a range of cells manually, or by selecting it with the mouse directly in the table. If necessary, you can also specify a place to place sparklines. After clicking OK, an info curve will appear in the selected cell.

In this example, you can visually observe the dynamics of changes in total income for the half-year, which we displayed in the form of a graph. By the way, in order to build sparklines in the remaining cells of the “Salary” and “Bonuses” lines, there is no need to do all the above steps again. It’s enough to remember and use the autofill function we already know.

Move the mouse cursor to the lower right corner of the cell with the already created sparkline and after the black crosshair appears, drag it to the upper edge of cell H3. Release the left mouse button and enjoy the result.

Now try to complete sparklines for expense items yourself, only in the form of histograms, and for the overall balance, the win/loss type is most suitable.

Now, after adding sparklines, our pivot table takes on this interesting look:

Thus, taking a quick glance at the table and without reading into the numbers, you can see the dynamics of income generation, peak expenses by month, months where the balance was negative and where it was positive, and so on. Agree, in many cases this can be useful.

Just like charts, sparklines can be edited and customized in their appearance. When you click on a cell with an infocurve, a new tab appears on the ribbon Working with sparklines.

Using the commands located here, you can change the sparkline data and its type, control the display of data points, change the style and color, control the scale and visibility of axes, group and set your own formatting options.

Finally, it is worth noting one more interesting point - you can enter plain text into the cell containing the sparkline. In this case, the infocurve will be used as the background.

Conclusion

So, now you know that with Excel 2010 you can not only build tables of any complexity and perform various calculations, but also present the results graphically. All this makes Microsoft spreadsheets a powerful tool that can satisfy the needs of both professionals who use it to compile business documents and ordinary users.

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 MS Excel spreadsheet processor. Description of the main elements of the document and instructions for working with them in our material.

Working with cells. Filling and formatting

Before proceeding with specific actions, you need to understand the basic element of any document in Excel. 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 “Share of Shares” cell. 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.

In front of you is an already filled out table (we’ll figure out how to create it later 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 perform 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 on three or more cells, simply continue entering the argument values in the Number 3, Number 4, and so on fields.

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.

Tables in Excel have an advantage over a similar option in Word and 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.

Adding graphs/charts

To build a chart or graph, you need to have a ready-made plate, because graphical data will be based precisely on information taken from individual rows or cells.

To create a chart/graph you need:

Select the table completely. If you need to create a graphic element only to display the data of certain cells, select only them;
Open the insert tab;
In the recommended charts field, select the icon that you think will best visually describe the tabular information. In our case, this is a three-dimensional pie chart. Move the pointer to the icon and select the appearance of the element;

In a similar way, you can create scatter plots, line diagrams, and table element dependency diagrams. All received graphic elements can also be added to Word text documents.

The Excel spreadsheet editor has many other functions, however, the techniques described in this article will be sufficient for initial work. In the process of creating a document, many users independently master more advanced options. This happens thanks to the convenient and understandable interface of the latest versions of the program.

Thematic videos:

There are often statistics lovers who do not like to count and calculate. They like to study, analyze ready-made figures and keep scrupulous records. Now, with the advent of electronic processors, unlimited opportunities are provided for such people. There is nothing easier than keeping statistical and other records in Excel from the Microsoft office suite.

Basic Concepts and Functions

When starting to work in Excel with tables, it is important for beginners to understand the basic concepts and principles of operation of this program. Like any Windows program, Excel has a traditional interface for such programs.

The menu includes sections common to all components of the Microsoft Office package: home, insert, page layout, view, review. There are also tabs unique to this program: formulas and data.

The appearance of the workspace is presented in the form of a page divided into cells. Each cell has its own number or coordinates. For this purpose, the leftmost column is numbered and the first top line has its own numbering in the form of Latin letters. The cell coordinates are determined by the intersection of a vertical column with a letter and a horizontal row with a number.

Each cell is a data store. It can be anything: numbers, text, a formula for calculations. You can apply different properties and types of data formatting to any cell. To do this, click on the cell with the right mouse button. In the menu that opens, select the “Cell Format” section.

All cells are combined into sheets. At the bottom of the program window there are labels with the names of the sheets. By default these are Sheet1, Sheet2 and Sheet3, as well as a shortcut to create a new sheet. All sheets can be renamed at your discretion. To do this, move the cursor over the shortcut and press the right mouse button. Select the appropriate command from the menu that appears. In addition, you can also delete, copy, move, paste, hide and protect a sheet.

Due to the large number of sheets in one Excel file, such files are also called books. Books are given names and can be easily stored by sorting them into folders.

Creating tables

Basically, you need to know how to work with Excel tables, so it is important for everyone from a beginner to an advanced programmer to understand what working with tables in Excel is based on. Unlike Word analogues, Excel ones have a number of significant advantages:

They can be used to make calculations and calculations.
They can sort data according to selected criteria. Most often in ascending or descending order.
They can be linked to other pages and made dynamic, that is, when data in related fields changes, data in other related fields will change.
Information from such data banks can be used to build histograms, graphs and other interactive elements, which is very convenient for visually presenting data.

These are not all the advantages of Excel documents. Creating calculated fields in Excel is extremely simple. Here are the necessary steps to obtain such an object:

Having tried to make one small sign, any teapot will be able to easily create objects of different configurations and types. By practicing with them, over time, many master other useful properties of Excel elements and are happy to use them for their needs.

Calculation using formulas

Those who have mastered the initial basics of how to work in Excel with tables will be able to further improve themselves. After all, unlike its Word counterpart, Excel data storage provides unlimited possibilities for various calculations. It is enough to learn a small skill on how to insert formulas and perform calculations in these objects.

To create a calculated field in Excel, just select the cell and press the “=” sign on your keyboard. After this, calculations of the values contained in any cells of this table become available. To add or subtract values, select the first cell, then put the desired calculation sign and select the second cell. After pressing Enter, the calculation result will appear in the initially selected cell. Thus, it is possible to perform various calculations of any cells that meet the requirements of the calculations being performed.

To make working with tables in Excel even easier, numerous formulas are used. For example, to find the sum of several cells located in one row or in one column, there is a “sum” formula. To use it, just select a cell and click on “=” in the line above the top line of the sheet on the left side, select the desired formula from the list that opens after clicking on the small triangle in the window with formulas.

Having learned to use this simple technology, many statistics lovers have freed themselves from a lot of painful calculations and unintentional errors that are characteristic of humans, but are absent in computers.