If you've never used a spreadsheet to create documents before, we recommend reading our Excel guide for dummies.
After that, you will be able to create your first spreadsheet with tables, graphs, mathematical formulas and formatting.
Detailed information about basic functions and opportunities spreadsheet processor MS Excel. 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, it is necessary to understand the base element any document in Excel. An Excel file consists of one or more sheets divided into small cells.
A cell is the basic component of any Excel report, table or graph. Each cell contains one block of information. This can be a number, date, currency, unit of measure, or other data format.
To fill a cell, simply click on it with the pointer and enter necessary information. To edit a previously filled cell, click on it double click mice.
Rice. 1 - an example of filling cells
Each cell on the sheet has its own unique address. Thus, calculations or other operations can be carried out with it. When you click on a cell, a field with its address, name and formula (if the cell is involved in any calculations) will appear in the upper part of the window.
Select the cell "Percentage of shares". Its 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 used in relation to it are available in the context menu. Click on a cell right key manipulator. A menu will open with which you can format the cell, parse the contents, assign a different value, and other actions.
Rice. 2 - cell context menu 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.
Before you is an already completed table (we will figure out how to create it later in the article). Imagine that you need to sort the data for January in ascending order. How would you do it? Banal reprinting of the table is extra work, besides, if it is voluminous, no one will do this.
There is a dedicated function for sorting in Excel. The user only needs:
- Select a table or block of information;
- Open the "Data" tab;
- Click on the "Sort" icon;
Rice. 3 - tab "Data"
- In the window that opens, select the column of the table over which we will perform actions (January).
- Next, the sort type (we're grouping by value) and finally the order, ascending.
- Confirm the action by clicking on "OK".
Rice. 4 - setting sorting options
will happen automatic sorting data:
Rice. 5 - the result of sorting the numbers in the column "January"
The same way you can sort by color, font and other parameters.
Mathematical calculations
The main thing Excel advantage– the possibility of automatic calculations in the process of filling the table. For example, we have two cells with values 2 and 17. How to enter their result in the third cell without doing the calculations yourself?
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 f(x) function icon 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. Full list functions and their names in the Excel editor can be found on the official website Microsoft.
We need to find the sum of two cells, so click on "SUM".
Rice. 6 - selection of 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 to the argument string. Click on "Number 2" and click on the cell with the number "17". Then confirm the action and close the window. If you need to perform mathematical operations with three or big amount cells, just keep entering the values of the arguments in the fields "Number 3", "Number 4" and so on.
If in the future the value of the summed cells will change, their sum will be updated automatically.
Rice. 7 - the result of the calculations
Creating tables
Excel spreadsheets can store any kind of data. Using the function quick setup and formatting, in the editor it is very easy to organize a personal budget control system, a list of expenses, digital data for reporting, and more.
Tables in Excel take precedence over the same option in Word and others office programs. Here you have the opportunity to create a table of any dimension. The data is easy to fill. There is a function panel for editing content. Besides, finished table can be integrated into docx file via normal function copy-paste.
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 item " pivot table»;
- Using the mouse, select a place on the sheet that will be reserved for the table. You can also 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 appearance the resulting plate, open the contents of the constructor and in the "Style" field, click on the template you like. If desired, you can create your own view with a different colors and cell selection.
Rice. 9 - table formatting
The result of filling the table with data:
Rice. 10 - completed table
For each table cell, you can also set the data type, formatting, and information display mode. The constructor window contains all the necessary options for further configuration of the plate, based on your requirements.
Adding Graphs/Charts
To build a chart or graph, you need to have a ready-made plate, because the graphic data will be based precisely on information taken from individual rows or cells.
To create a chart/graph, you need:
- Fully select the table. If you want to create a graphic element only to display the data of certain cells, select only them;
- Open the insert tab;
- In the Recommended Diagrams box, select the icon that you think best describes visually tabular information. In our case, this is the volume pie chart. Bring the pointer to the icon and select the appearance of the element;
Similarly, you can create scatter plots, line charts and dependency schemes of table elements. All received graphic elements can also be added to text documents Word.
There are many other functions in the Excel spreadsheet editor, however, for initial work the techniques described in this article will suffice. In the process of creating a document, many users independently master more advanced options. This is due to a convenient and understandable interface. latest versions programs.
Thematic videos:
Most users computer systems on the Windows based from installed package Microsoft Office certainly come across the MS Excel application. For novice users, the program causes some difficulties in mastering, however, working in Excel with formulas and tables is not so difficult as it might seem at first glance, if you know the basic principles underlying the application.
What is Excel?
At its core, Excel is a full-fledged mathematical machine for performing many arithmetic, algebraic, trigonometric and other more complex operations, operating with several basic data types that are not always related specifically to mathematics.
Working with Excel spreadsheets involves using more features with joins and calculations, and plain text, and multimedia. But in its original form, the program was created precisely as a powerful mathematical editor. Some, however, at first take the application for some kind of calculator with advanced features. Deepest delusion!
Work in Excel with tables for beginners: the first acquaintance with the interface
First of all, after opening the program, the user sees the main window, which contains the main controls and tools for work. In more later versions when the application starts, a window appears in which it is proposed to create new file, named "Book 1" by default, or select a template for further action.
Working with Excel tables for beginners at the first stage of acquaintance with the program should come down to creating empty table. For now, let's look at the main elements.
The main field is occupied by the table itself, which is divided into cells. Each is numbered, thanks to two-dimensional coordinates - the row number and the letter designation of the column (for example, we take Excel 2016). This numbering is needed so that in the dependency formula it is possible to clearly identify exactly the cell with data, the operation on which will be performed.
From above, as in the rest office applications, the main menu bar is located, and a little lower is the toolkit. Under it is special string, in which formulas are entered, and a little to the left you can see a window with the coordinates of the active on this moment cell (on which the rectangle is located). At the bottom there is a sheet panel and a horizontal slider, and below it there are buttons for switching the view and scaling. On the right is vertical stripe to move up/down the sheet.
Basic data entry types and simple operations
At first, it is assumed that work in Excel with tables by a novice user will be mastered by operations familiar to him, for example, in the same Word text editor.
As usual, in the table, you can copy, cut or paste data, enter text or numeric data.
But the input is somewhat different from that produced in text editors. The fact is that the program is initially configured to automatically recognize what the user writes in the active cell. For example, if you enter the string 1/2/2016, the data will be recognized as a date, and the date will appear in the cell instead of the entered numbers in a simplified form - 02/01/2016. Changing the display format can be quite simple (we will dwell on this a little later).
The same is true with numbers. You can enter any numeric data, even with an arbitrary number of decimal places, and they will be displayed in the form in which everyone is used to seeing them. But, if an integer is entered, it will be presented without the mantissa (the decimal places as zeros). You can also change this.
But at the end of data entry, many novice users try to move to the next cell using the keyboard arrows for this (similar to how this can be done in Word tables). And it doesn't work. Why? Yes, only because working with Excel tables is different from text word editor quite palpable. The transition can be made by pressing Enter keys or by setting the active rectangle to another cell with the left mouse click. If after writing something in the active cell, press Esc key, the entry will be cancelled.
Sheet Actions
Working with sheets at first should not cause difficulties. The bottom panel has special button adding sheets, after clicking on which will appear new table from automatic transition on it and assigning a name (“Sheet 1”, “Sheet 2”, etc.).
By double-clicking, you can activate the renaming of any of them. You can also use the right click menu to call additional menu, which has several basic commands.
Cell Formats
Now the most important thing is the cell format is one of basic concepts, which defines the data type that will be used to recognize its content. You can call format editing through the right-click menu, where the corresponding line is selected, or by pressing the F2 key.
The window on the left shows all available formats, and the data display options are shown on the right. Looking at the date example shown above, the format is set to "Date" and the desired appearance is set to the right (for example, February 1, 2016).
For mathematical operations several formats can be used, but in the most simple case choose numeric. On the right there are several types of input, a pointer to the number of decimal places in the mantissa and a field for setting the separator of groups of digits. Using others number formats(exponential, fractional, monetary, etc.), you can also set the desired parameters.
Default for automatic recognition data is in a common format. But when you enter text or several letters, the program can spontaneously convert it into something else. Therefore, to enter text for the active cell, you need to set the appropriate parameter.
Working in Excel with formulas (tables): an example
Finally, a few words about formulas. And to begin with, consider the example of the sum of two numbers located in cells A1 and A2. The application has an auto-sum button with some additional features(calculation of the arithmetic mean, maximum, minimum, etc.). It is enough to set the active cell located in the same column below, and when you select the amount, it will be calculated automatically. The same works for horizontal values, but active cell for the amount you need to set to the right.
But you can also enter the formula manually (working with Excel tables also implies such a possibility when automatic action not provided). For the same amount, put an equal sign in the formula bar and write the operation in the form A1 + A2 or SUM (A1; A2), and if you want to specify a range of cells, use this form after the equal sign: (A1: A20), after which it will be the sum of all numbers in cells from the first to the twentieth inclusive is calculated.
Construction of graphs and charts
Working with Excel tables is also interesting because it involves the use of a special automated tool for constructing dependency graphs and charts based on selected ranges.
To do this, there is a special button on the panel, after clicking on which you can select any parameters or the desired view. After that, the chart or graph will be displayed on the sheet as a picture.
Cross links, data import and export
The program can also establish data links located on different sheets, use files of a different format or objects located on servers on the Internet, and many other add-ons.
Besides, excel files you can export to other formats (for example, PDF), copy data from them, etc. But the program itself can open files created in other applications ( text formats, databases, web pages, XML documents, etc.).
As you can see, the possibilities of the editor are almost unlimited. And, of course, there is simply not enough time to describe them all. Only the basics are given here, but the interested user will have to read the help information in order to master the program at the highest level.
There are some things you can live without. For example, an electric can opener, or a sausage cutter. This list one could continue indefinitely, however, the meaning is probably already clear. IN ordinary life they are simply not needed. Of course, there is a certain degree of probability that someday they will also find a use.
For example, you expect a large number of guests and you need to open 10 cans of green peas, and then cut 20 loaves of sausage into thin slices. Certainly, under such circumstances, the above things will be very useful. But this special cases. In the usual routine, it is easier for us to open a can of canned food in the usual way, as well as cut off a couple of slices of sausage for a sandwich.
The same is true with computer programs. Just study them general development, in the hope that someday they will be needed, would be a waste of time. Of course, during the training, in any case, you improve your general computer skills. For example, hold the mouse more confidently in your hands, start typing faster and faster, navigate unfamiliar interfaces more freely ... Nevertheless, first of all, it is worth mastering programs that will really help you in your work or personal life.
MS Excel program is a universal tool and will suit almost any person who, on duty, has to calculate something, maintain databases, build diagrams, and the like. Moreover, the word "calculate" is often scary ordinary people. Something like a transposition of a matrix or differential equations immediately appears.
In fact, even in ordinary life there is a place for calculations. Let's look at a few simple examples.
The first is the most banal: you need to keep a table of expenses and income. If you are not lazy and write down at least large expenses there, you can draw conclusions about the structure of expenses and correct them.
Second: you need to make a diagram according to the existing table. In Excel, this is done in just a few minutes (of course, if you know how).
Fourth: it is necessary to maintain a small database of the company's customers.
All these and many other tasks become easy to solve if you use Microsoft Excel.
By the nature of my work, I constantly have to analyze digital data. This used to take a lot of time, because I preferred to work with a regular calculator. However, when the boss once again hinted at the presence of constant errors in the calculations and asked to expedite the provision of reports with the results of business trips, "bad" thoughts began to creep into my head that it was time to shake the old days and start training.
How can you learn Excel
Everyone comes to Excel in their own way. However, few people manage to master the program on their own, using textbooks. The thing is, there is usually no time for that. The authorities don't really care that you have to learn something. He wants a result.
Therefore, you have to deal with snatches. IN free time. Buying books at your own expense.
There is another tactic: you can endlessly terrorize your colleagues by asking them question after question, asking them to show how this or that operation is done. Colleagues are different. Some people try to help you at first. Others immediately come up with a way to make you "get rid" yourself. For example, giving advice in a deliberately dismissive tone, showing with all your appearance that you are an impenetrable dumbass, and without a single hope of correction.
Alas, selfeducation usually goes slowly. Sometimes a person simply falls into a stupor, because tightly rests on an insurmountable barrier. Day after day, nothing happens, and there is no one to ask especially. Colleagues, seeing you approaching their table, immediately take on such a puzzled look that they simply do not dare to ask them questions.
What to do, you think, and decide to look for some MS Excel courses.
MS Excel courses
Here, as they say, whoever is lucky. If you are lucky enough to find a good teacher who really works with the program in practice, and did not study it from books a month before the start of your courses, consider that half the battle is done.
If your boss agrees to let you go to study at work time- will be even better. And if he also comes down to paying for your education, then you can consider yourself a real darling of fate.
What if everything turns out the other way around?
You will drive to the training center after work, when your head is already boiled from production tasks. You will pay for the Excel course a considerable amount out of your own pocket. And with all this, you will come across a teacher who can talk about the program only within the framework of the Excel for Dummies book. Any question "aside" from the general line will confuse him.
As a result, after graduation, you will be completely convinced that Excel is one of the most complex programs in the world, and you yourself are one of the most stupid students, the shame of letting you out training center and the teacher personally. How can one not return to the good old calculator, causing the anger of the authorities and the smirks of more advanced colleagues.
Even if this sad “second option” doesn’t happen to you, still drive half a city to classes at the end of the working day, waste time on traffic jams, sit in a stuffy classroom under a flickering neon light, adjust to the speed of the slowest student in the group - is it really Is this how learning should be done in the 21st century?
Online MS Excel lessons
Of course not! I immediately dismissed face-to-face courses and started looking for online learning options. In general, there are a lot of completely free ones on the Internet. materials for mastering Excel. At first I was delighted and started, it was, to look through them, but over time it became clear that free lessons very disjointed, difficult to find sequels, etc.
Sometimes the quality of the presentation of information left much to be desired.
However, at some point I discovered two courses that seemed to me successful. Alas, one of them was not complete enough and contained only the most. It was suitable only for the most novice users. The second course was thorough, serious. What bribed me most of all was that the analysis of the program's functions was not a simple abstract enumeration, but concrete examples.
Although these were examples not entirely from my field, they helped me master the capabilities of the program, get my hands on it, and absorb the techniques of work. All this made it possible to take subsequent independent steps, acting by analogy.
Online learning versus classic courses
There are disciplines where online learning powerlessly. Everything is built on the live interaction between the mentor and the student. However, if we are talking about the development of a new computer program, then a video lesson will work more successfully than a regular one when both the teacher and the student are in the class at the same time.
If we work with video lessons, then we are not tied to a specific time and place of study. You can practice when you have time. The main thing is not to throw. This rule is very important! Many people who criticize distance learning turn out to be undisciplined slobs. Having paid for the course, they believe that they will learn anyway and are very surprised when month after month passes, and somehow knowledge is not added at all.
Nothing surprising. On weekdays, they usually have no time to study - work, fatigue, TV. And when they sit down for lessons on the weekend, what they mastered a week ago is already almost forgotten. You have to endlessly go back, re-watch the video tutorials that have already been completed. And not just to watch, but to do all the actions yourself. And then there are tasks for independent work.
Another problem is passively watching video tutorials without any attempt to do the suggested exercises with your own hands. Indeed - why do they carry out, when everything is clear anyway. Let's move on...
As a result, without getting practical skills day after day, just looking through the lessons during the lunch break with a cup of coffee in hand, a person does not study, but wastes time. It is not surprising that to distance learning some developed a strong antipathy.
Yes, it's not for slackers. If you want to learn something, watch and learn.
Excel Lessons by Andrey Sukhov
As I said, I liked the course. All topics are discussed with specific examples that are much better remembered. I liked the fact that the author recorded not one big video lesson covering the whole section of the course, but broke it into several short videos.
Watching a short video covering a specific situation and its solution is much more convenient. It is very easy to rewind the video if you listened to something. You are constantly being asked to do something on your own. So not only the head, but also the hands remember the sequence of operations.
It is valuable that the author offers to watch the first, introductory part of the course without setting any conditions. It deals with the most basic information and skills, which, nevertheless, are sufficient for solving simple, non-special tasks.
At the same time, there is an opportunity to listen to how the author teaches lessons. Are there any problems with diction, is the pace of presentation of the material normal, are the examples clear, etc.
At one time, I also first subscribed to the first free part, and then paid full course. It cost 1800 rubles. On the one hand, it seems to be expensive. On the other hand, full-time courses are much more expensive. If you buy a textbook, the savings will be very doubtful. Indeed, there are inexpensive books, but training is greatly stretched over time.
The result of learning and implementing Excel
Now the authorities are quite pleased with me. I have fewer errors and I submit reports on time. Yes, there are bosses! I myself am in full of delight. After all, once I compiled and debugged the table, and then all the work consisted only in adding fresh actual numbers to several cells. The program instantly performed the calculation and produced ready-made figures.
If earlier the compilation of a damned report was a real hard labor, now it has turned into a holiday. Everything is done quickly and without errors. Almost on its own :)
Who there said the phrase: "Manual labor on the shoulders of machines?" This is exactly what happened in my case. The time spent learning has paid off handsomely. Thanks to Microsoft for this useful program and Andrey Sukhov for clear and useful information.
Many analytical works and projects are being created. Therefore, the question of how to use Excel is relevant in our time. With the editor you can:
- Work with data in Excel.
- Create tables and graphs.
- Make calculations.
- Analyze totals.
How to start a spreadsheet?
To start using Excel, you need the following: go to the Start menu, then select All Programs, then Microsoft Office, Excel. This option is suitable for office version not earlier than 2003. For others: Start, All Programs, then Microsoft Excel. The third option: Start, Run, write Excel, press Enter.
General view and main functions
Excel is " workbook”, consisting of “sheets”, on which there are columns and rows that form a table. You can select a cell that has its own unique address for entering information by clicking on it with the left mouse button. Having created a table with data, you can style its borders various lines, and change the color of the cells in the Format Cells window. Based on the data that you enter in the table, you can create a chart or graph to visualize the results. With the help of a spreadsheet processor, various calculations are also made, statistical data is displayed, or simply any current information. For each cell, you can set its own format in accordance with the type of input data, for example:
Book sheets can be renamed, deleted or added. Data can be cut, pasted, copied, including using the fill marker (with the data and “pull” it further along the row or column, thereby filling according to the given pattern). For beginners in Excel, there is a guide to using from Microsoft.
Working with formulas and charts in Excel
Spreadsheet Excel is ideal for calculations. For those who are just starting to learn how to work with Excel, there are usually no particular difficulties. To indicate the beginning of the calculation in the table, you need to put the “=” sign, otherwise the entered formula will not be accepted by the system. Working in the processor involves the use of built-in formulas. To use a value, you must select the cell that contains it.
Charts are created to visually display information or totals. Exist:
- Histograms.
- Point.
To build, you need to select the table and open the "Chart Wizard", with the help of which the creation and editing takes place. In Office 2007, this process happens through selecting the chart type on the User Interface ribbon.
excel- powerful processor that allows you to work with text values tables and with their visual display in the form of graphs and diagrams, as well as carry out calculations. - means to have access to a multifunctional tool that is suitable for solving professional problems, as well as for current user calculations.
Grouping data
When you prepare a catalog of products with prices, it would be nice to take care of the usability of it. A large number of positions on one sheet forces the use of a search, but what if the user only makes a choice and has no idea about the name? In Internet catalogs, the problem is solved by creating product groups. So why not do the same in an Excel workbook?
Grouping is pretty easy. Select multiple lines and click the button Group tab Data(see fig. 1).
Figure 1 - Group button
Then specify the type of grouping − line by line(see Fig. 2).
Figure 2 - Selecting the type of grouping
As a result, we get ... not what we need. Rows of products have been combined into a group indicated below them (see Fig. 3). In directories, the title usually comes first, and then the content.
Figure 3 - Grouping rows "down"
This is by no means a program error. Apparently, the developers considered that the grouping of lines is mainly done by the compilers financial reporting, where the final result is displayed at the end of the block.
To group rows "up" you need to change one setting. On the tab Data click on the small arrow in the lower right corner of the section Structure(see Fig. 4).
Figure 4 - The button responsible for displaying the structure settings window
In the settings window that opens, uncheck the box. Totals in rows below data(See Fig. 5) and press the button OK.
Figure 5 - Structure settings window
All groups that you managed to create will automatically change to the "upper" type. Of course set parameter affect the further behavior of the program. However, you will need to uncheck this box to everyone new sheet and each new Excel workbook, because the developers did not provide for a "global" setting of the grouping type. Similarly, you cannot use different types groups within the same page.
Once you've sorted your products into categories, you can organize the categories into larger sections. In total, up to nine levels of grouping are provided.
The inconvenience when using this function is the need to press the button OK in a pop-up window, and it will not be possible to collect unrelated ranges in one go.
Figure 6 - Multilevel directory structure in Excel
Now you can open and close parts of the catalog by clicking on the pros and cons in the left column (see Figure 6). To expand the entire level, click on one of the numbers at the top.
To output lines for more high level hierarchy, use the button Ungroup tabs Data. You can completely get rid of grouping using the menu item Delete Structure(See Fig. 7). Be careful, it is impossible to undo the action!
Figure 7 - Removing the grouping of rows
Pinning sheet regions
Quite often when working with Excel spreadsheets there is a need to fix some areas of the sheet. There may be, for example, row / column headings, a company logo or other information.
If you freeze the first row or the first column, then everything is very simple. Open a tab View and drop down menu To fix areas select items accordingly Pin top line or Freeze first column(see fig. 8). However, at the same time, both the row and the column cannot be “frozen” in this way.
Figure 8 - Freeze a row or column
To remove a pin, select the item in the same menu. Unpin areas(paragraph replaces the line To fix areas if the page has a freeze applied).
But fixing several rows or an area of rows and columns is not so transparent. You highlight three lines, click on an item To fix areas, and... Excel freezes only two. Why is that? An even worse scenario is possible when the regions are fixed in an unpredictable way (for example, you select two lines, and the program puts borders after the fifteenth). But let's not write this off as an oversight by the developers, because the only correct use case for this feature looks different.
You need to click on the cell below the rows you want to freeze, and, accordingly, to the right of the columns to be fixed, and only then select the item To fix areas. Example: in Figure 9, a cell is selected B4. This means that three rows and the first column will be fixed, which will remain in place when the sheet is scrolled both horizontally and vertically.
Figure 9 - We fix the area of \u200b\u200brows and columns
You can apply a background fill to anchored areas to indicate to the user that these cells have special behavior.
Sheet rotation (replacing rows with columns and vice versa)
Imagine this situation: you worked for several hours on a table set in Excel and suddenly realized that you had designed the structure incorrectly - column headings should have been written in rows or rows in columns (it does not matter). Retype everything manually? Never! Excel provides a function that allows you to "rotate" the sheet by 90 degrees, thus moving the contents of the rows into columns.
Figure 10 - Initial table
So, we have some table that needs to be “rotated” (see Fig. 10).
- Select cells with data. It is the cells that are selected, not the rows and columns, otherwise nothing will work.
- Copy them to the clipboard with a keyboard shortcut
or in any other way. - Let's move on to empty sheet or free space current sheet. Important note: pasting over the current data is not allowed!
- Paste data with keyboard shortcuts
and select the option from the paste options menu Transpose(see fig. 11). Alternatively, you can use the menu Insert from tab home(see fig. 12).
Figure 11 - Insert with transposition
Figure 12 - Transpose from the main menu
That's it, the rotation of the table is done (see Fig. 13). In this case, the formatting is preserved, and the formulas are changed in accordance with the new position of the cells - no routine work not required.
Figure 13 - Result after rotation
Formula display
Sometimes there is a situation where you cannot find desired formula among a large number cells, or simply do not know what and where to look for. In this case, you will need the ability to display not the result of calculations, but the original formulas on the sheet.
Click the button Show formulas tab Formulas(See Figure 14) to change how data is presented on the sheet (See Figure 15).
Figure 14 - Button "Show formulas"
Figure 15 - Now formulas are visible on the sheet, and not the results of the calculation
If you find it difficult to navigate the cell addresses displayed in the formula bar, click Influencing cells from tab Formulas(see fig. 14). Dependencies will be shown with arrows (see Fig. 16). To use this feature, you must first highlight one cell.
Figure 16 - Cell dependencies are shown by arrows
Hide dependencies at the click of a button Remove arrows.
Wrap lines in cells
Quite often in Excel workbooks there are long inscriptions that do not fit in the cell in width (see Fig. 17). You can, of course, expand the column, but this option is not always acceptable.
Figure 17 - Labels do not fit in cells
Select cells with long labels and click the button Text wrap on the Home tab (see Figure 18) to switch to multi-line display (see Figure 19).
Figure 18 - Button "Wrap Text"
Figure 19 - Multiline text display
Rotate text in a cell
Surely you have come across a situation where the text in the cells needed to be placed not horizontally, but vertically. For example, to label a group of rows or narrow columns. Excel 2010 provides tools to rotate text in cells.
Depending on your preferences, you can go two ways:
- First create an inscription, and then rotate it.
- Adjust the rotation of the label in the cell, and then enter the text.
The options differ slightly, so we will consider only one of them. First, I combined six lines into one using the button Merge and center on the Home tab (see Fig. 20) and introduced a generalizing inscription (see Fig. 21).
Figure 20 - Merge cells button
Figure 21 - First create a horizontal signature
Figure 22 - Text rotation button
You can further reduce the column width (see Figure 23). Ready!
Figure 23 - Vertical text cells
If there is such a desire, you can set the angle of rotation of the text manually. In the same list (see Fig. 22), select the item Cell alignment format and in the window that opens, enter arbitrary angle and alignment (see Figure 24).
Figure 24 - Set an arbitrary angle of text rotation
Format cells by condition
Opportunities conditional formatting appeared in Excel for a long time, but by the 2010 version they have been significantly improved. You may not even have to understand the intricacies of creating rules, because. the developers have provided a lot of blanks. Let's see how to use conditional formatting in Excel 2010.
The first thing to do is select the cells. Next, on Home tab click Conditional Formatting and select one of the blanks (see Fig. 25). The result will be displayed on the sheet immediately, so you do not have to go through the options for a long time.
Figure 25 - Selecting a conditional formatting template
Histograms look quite interesting and well reflect the essence of price information - the higher it is, the longer the segment.
Color scales and icon sets can be used to indicate various states, such as transitions from critical to eligible costs (see Figure 26).
Figure 26 - Color scale from red to green with yellow in between
You can combine bar charts, bars, and icons in the same cell range. For example, the bar graphs and icons in Figure 27 show acceptable and excessive poor performance devices.
Figure 27 - The histogram and icon set reflect the performance of some conditional devices
To remove conditional formatting from cells, select them and select Conditional Formatting from the menu. Remove rules from selected cells(see fig. 28).
Figure 28 - Delete conditional formatting rules
Excel 2010 uses presets for quick access to the possibilities of conditional formatting, as setting your own rules is far from obvious for most people. However, if the templates provided by the developers do not suit you, you can create your own rules for the design of cells by various conditions. Full description This functionality is beyond the scope of this article.
Using filters
Filters allow you to quickly find the information you need big table and represent it in a compact form. For example, from a long list of books, you can choose the works of Gogol, and from the price list of a computer store - Intel processors.
Like most other operations, the filter requires cells to be selected. However, you do not need to select the entire table with data, it is enough to mark the rows above the desired data columns. This greatly increases the convenience of using filters.
After the cells are selected, on the tab home press the button Sort and filter and select the item Filter(see fig. 29).
Figure 29 - Creating filters
Now the cells will be converted into drop-down lists where you can set the selection options. For example, we are looking for all mentions of Intel in the column Name of product. For this we choose text filter Contains(see fig. 30).
Figure 30 - Creating a text filter
Figure 31 - Create a filter by word
However, it is much faster to achieve the same effect by typing a word in the field Search context menu shown in Figure 30. Why then call additional window? It is useful if you want to specify several selection conditions or select other filtering options ( does not contain, starts with... ends with...).
Other options are available for numeric data (see Figure 32). For example, you can select the 10 largest or 7 smallest values (the number is configurable).
Figure 32 - Numeric filters
Excel filters provide rich enough features comparable to a selection SELECT query in database management systems (DBMS).
Information curve display
Information curves (info curves) - an innovation in Excel 2010. This function allows you to display the dynamics of changes in numerical parameters directly in the cell, without resorting to charting. Changes in numbers will be immediately shown on the micrograph.
Figure 33 - Excel 2010 InfoCurve
To create an infocurve, click one of the buttons in the block Infocurves tab Insert(see Fig. 34), and then set the range of cells to plot.
Figure 34 - Inserting an infocurve
Like charts, information curves have many options to customize. More detailed guide on the use of this functionality is described in the article.
Conclusion
The article discussed some useful features Excel 2010 that speed up work, improve the appearance of tables, or improve usability. It doesn't matter if you create the file yourself or use someone else's - in Excel 2010 there are functions for all users.
