Freeze data in excel. Freeze both rows and columns at the same time. Display information curves
This stuff has an answer on how to freeze a string in excel. We will see how to freeze the first (top) or arbitrary row, as well as how to freeze an area in excel.
Freezing a row or column in Excel is an extremely useful feature, especially for creating lists and tables with a lot of data arranged in cells and columns. By pinning a row, you can easily keep track of the headings of certain columns, which means you save time instead of endlessly scrolling up and down.
Let's look at an example. In the image below, we see the first row, which is the column headings.
Agree, it will be inconvenient for us to enter and read data from the table if we do not know or see the headers at the top. Therefore, we will fix the upper term in excel as follows. In the upper menu of Excel, we find the item "VIEW", in the submenu that opens, select "SECURE AREAS", and then select the sub-item "SECURE UPPER ROW".
These simple actions to freeze a line allow you to scroll through the document and at the same time understand the meaning of row headers in Excel. I repeat - this is convenient when there are a large number of lines.
I must say that the same effect can be achieved if you first select the cells of the entire first row, and then apply the first sub-item of the already known submenu "FIX AREAS" - "Freeze Areas":
Well, now about how to freeze a region in excel. First, select the area of \u200b\u200bthe Excel cells that you want to constantly see (fix). It can be several lines and an arbitrary number of cells. As you probably already guessed, the path to this pinning function remains the same: the menu item "VIEW" → then "PINCH AREAS" → then the sub-item "PINCH AREAS". The top figure illustrates this.
If for some reason you could not correctly freeze rows or areas in excel, go to the menu "VIEW" → "PINCH AREAS" → select "Unpin areas". Now you can try again slowly to capture the desired lines and areas of the document.
Finally, I will say that the first column also lends itself to fixing. We also act → menu "VIEW" → "FIX AREAS" → select the item "Freeze first column".
Good luck with working with documents in Excel, make no mistakes and save your time using the additional functions and tools of this editor.
Data grouping
When you're preparing a priced product catalog, it's a good idea to think about usability. A large number of positions on one sheet forces you to use 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 easy enough. Select several lines and press the button Group in the tab Data (see fig. 1).
Figure 1 - Grouping 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. The product lines have been combined into a group indicated below them (see Fig. 3). Directories usually have the header first and then the content.
Figure 3 - Grouping rows "down"
This is by no means a program error. Apparently, the developers thought that the grouping of lines is mainly done by the compilers of financial statements, where the final result is displayed at the end of the block.
To group rows up, you need to change one setting. In 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 "top" type. Of course set parameter will also affect the further behavior of the program. However, you will have to uncheck this box to each a new sheet and every new Excel workbook, because the developers did not provide for a "global" setting of the grouping type. Likewise, you cannot use different types of groups within the same page.
Once you've categorized your products, you can collect the categories into larger sections. In total, there are up to nine levels of grouping.
The inconvenience when using this function is the need to press the button OK in the 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 expand and close parts of the catalog by clicking on the plus and minus in the left column (see Figure 6). To expand the entire level, click on one of the numbers at the top.
To bring rows to a higher level of the hierarchy, use the button Ungroup tabs Data... You can completely get rid of the grouping using the menu item Delete structure (see fig. 7). Be careful, it is impossible to undo the action!
Figure 7 - Remove the grouping of rows
Freezing sheet regions
Quite often when working with excel spreadsheets it becomes necessary to fix some areas of the sheet. It can contain, for example, row / column headings, company logo or other information.
If you are freezing the first row or the first column, then everything is very simple. Open the tab View and in the dropdown menu To fix areas select items accordingly Pin top line or Freeze first column (see fig. 8). However, it will not be possible to “freeze” both the row and the column in this way.
Figure 8 - Fixing a row or column
To unpin, select the item in the same menu Unpin areas (clause replaces the string To fix areasif the page is frozen).
However, pinning multiple rows or an area of \u200b\u200brows and columns is not so transparent. You select three lines, click on the item To fix areas, and ... Excel only "freezes" two. Why is that? An even worse option is possible, when the areas 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 it off as an oversight of the developers, because the only correct use of this function looks different.
You need to click on the cell below the rows that you want to freeze, and, accordingly, to the right of the docked columns, and only then select the item To fix areas... Example: in Figure 9, a cell is highlighted B 4... This means that three rows and the first column will be fixed, which will remain in place when scrolling the sheet both horizontally and vertically.
Figure 9 - Fixing an area of \u200b\u200brows and columns
You can apply a background fill to docked areas to indicate to the user special behavior of these cells.
Rotate the sheet (replace rows with columns and vice versa)
Imagine this situation: you worked for several hours on a set of spreadsheets in Excel and suddenly realized that you had misdesigned the structure - the column headings should have been written row by row or row by column (it doesn't matter). Do you want to 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 - Source 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. - Go to an empty sheet or free space of the current sheet. Important note: cannot be inserted over the current data!
- Insert data with a keyboard shortcut
and in the insert options menu, select the option Transpose (see fig. 11). Alternatively, you can use the menu Paste from the tab the main (see fig. 12).
Figure 11 - Insert with transposition
Figure 12 - Transpose from the main menu
That's all, the table has been rotated (see Fig. 13). At the same time, the formatting is preserved, and the formulas are changed in accordance with the new position of the cells - no routine work is required.
Figure 13 - Result after turning
Show formulas
Sometimes a situation arises when you cannot find the required formula among a large number of cells, or you simply do not know what and where to look for. In this case, the ability to display not the result of calculations, but the original formulas, will come in handy.
Click the button Show formulas in the tab Formulas (see Figure 14) to change the presentation of the data on the worksheet (see Figure 15).
Figure 14 - "Show formulas" button
Figure 15 - Formulas are now 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 Influence cells from the 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
Hiding dependencies at the click of a button Remove arrows.
Wrapping rows in cells
Quite often in Excel workbooks there are long labels 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 into cells
Select cells with long labels and press the button Wrap text on The main tab (see fig. 18) to switch to multi-line display (see fig. 19).
Figure 18 - Button "Text wrapping"
Figure 19 - Multi-line display of text
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 preference, you can go in two ways:
- First create a label 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. To begin with, I concatenated six lines into one using the button Merge and center on The main tab (see fig. 20) and entered a generalizing inscription (see fig. 21).
Figure 20 - Button for merging cells
Figure 21 - First, create a horizontal signature
Figure 22 - Text rotation button
You can further reduce the column width (see Figure 23). Done!
Figure 23 - Vertical cell text
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, specify an arbitrary angle and alignment (see Fig. 24).
Figure 24 - We set an arbitrary angle of rotation of the text
Conditionally formatting cells
Opportunities conditional formatting appeared in Excel for a long time, but by the 2010 version they were significantly improved. You may not even have to understand the intricacies of creating rules, because the developers have provided for many blanks. Let's take a look at how to use conditional formatting in Excel 2010.
The first thing to do is select the cells. Further on The main tab click Conditional formatting and select one of the blanks (see fig. 25). The result will be displayed on the sheet immediately, so you don't have to go through the options for a long time.
Figure 25 - Selecting a preset conditional formatting
The histograms look quite interesting and reflect well the essence of price information - the higher it is, the longer the segment.
Color bars and icon sets can be used to indicate various states, such as transitions from critical costs to acceptable costs (see Figure 26).
Figure 26 - Color scale from red to green with intermediate yellow
You can combine histograms, scales, and icons in the same range of cells. For example, the bar graphs and icons in Figure 27 show acceptable and excessively low device performance.
Figure 27 - The histogram and a set of icons reflect the performance of some conventional devices
To remove conditional formatting from cells, select them and from the conditional formatting menu, choose Remove rules from selected cells (see fig. 28).
Figure 28 - Removing the Conditional Formatting Rules
Excel 2010 uses presets for quick access to conditional formatting capabilities. customizing your own rules is far from obvious to 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 according to various conditions. A full description of this functionality is beyond the scope of this article.
Using filters
Filters allow you to quickly find the information you need in a large table and present it in a compact form. For example, from a long list of books you can choose works by Gogol, and from the price list of a computer store - Intel processors.
Like most other operations, the filter requires cell selection. However, you do not need to select the entire table with data; it is enough to mark the lines above the required data columns. This greatly increases the usability of the filters.
After the cells are selected, in the tab the main press the button Sort and filter and select the item Filter (see fig. 29).
Figure 29 - Create filters
The cells will now turn into drop-down lists, where you can set the selection parameters. For example, we look for all references to Intel in the column Name of product... To do this, select the text filter Contains (see fig. 30).
Figure 30 - Create a text filter
Figure 31 - Create a filter by word
However, it is much faster to achieve the same effect by writing a word in the field Search the context menu shown in Figure 30. Why then call an additional window? It is useful if you want to specify multiple selection conditions or select other filtering options ( does not contain, starts with ..., ends with ...).
For numeric data, other parameters are available (see Figure 32). For example, you can select the 10 largest or 7 smallest values \u200b\u200b(the number is configurable).
Figure 32 - Numeric filters
Excel filters provide a fairly rich feature comparable to the SELECT query in database management systems (DBMS).
Display information curves
Information Curves (Sparklines) are new in Excel 2010. This feature allows you to display the dynamics of changes in numeric parameters directly in a cell, without having to build a chart. Changes in numbers will be immediately shown on the micrograph.
Figure 33 - Excel 2010 Sparkline
To create a sparkline, click on one of the buttons in the block Infocurves in the tab Insert (see Figure 34), and then set the range of cells to build.
Figure 34 - Inserting a hot curve
Like charts, information curves have many options to customize. More detailed manual how to use this functionality is described in the article.
Conclusion
This article covered some useful Excel 2010 features that speed up work, improve appearance tables or usability. It doesn't matter if you create the file yourself or use someone else's - Excel 2010 has functions for all users.
We have repeatedly talked about the spreadsheet editor from Microsoft - about excel program... The entire package of office programs is rightfully the market leader. And this leadership is easily explained by the presence of huge functionality. Moreover, with each version it is replenished.
In fact, about a tenth of all functions are not used by an ordinary user, so many do not even know about their existence and do manually what can be done automatically. Or they waste time adjusting to certain limitations and inconveniences. Today we want to tell you how to anchor an area in Excel. In other words, the program has a function thanks to which you can fix the header of the document for your convenience.
Fixing the area (header) in Microsoft Excel
Well, we will not delay any longer. Let's take a look at the process of fixing the area you need as a fixed header, which will always remain on top (or side), wherever you scroll the document. So, you must do the following to achieve the desired result:- First of all, go to the "View" tab, then click on the "Freeze areas" button;
- Now you must select the desired pinning option. There are only three of them:
Thus, the choice of a particular item depends solely on your needs.
Freeze both rows and columns at the same time
Be that as it may, these options are only suitable if you want to select one row or one column anywhere in the document. But what if you need to freeze both the row and the column at once so that they are displayed permanently? Let's figure it out:
As you can see, there is nothing complicated in both cases. It takes just a few minutes. That is why the MS Office suite of office programs (including Excel) is really the most popular software for editing all types of text and graphic data: any action can be automated or simplified as much as possible, the main thing is to remember how all this is done.
This instruction is relevant not only for Excel 2013, on which it was demonstrated, but also for older ones - the difference may be in the arrangement of elements, nothing more. Therefore, if you have a different version, do not be lazy to make a couple of extra mouse movements.