Other EXCEL features

Article is dedicated to those users who continue to work in MS EXCEL 2007, but are already thinking of upgrading to a new version.

the fill handle - small black square that appears in the lower right corner of the selected cell or a selected range. The fill handle is used to fill adjacent cells based on the contents of the selected cells.

add-ins "analysis ToolPak" will help to simplify the calculations for statistical or engineering analysisfor.

Normally when you create a formula, the user specifies the parameter values and the formula (equation) returns the result. For example, you have the equation 2*a+3*b=x, given the parameters a=1, b=2, it is required to find x (2*1+3*2=8). Tool Selection setting allows to solve the inverse problem: to choose the parameter value at which the equation returns the desired target outcome X. For example, if a=3, is required to find a parameter value b for which X is equal to 21 (the answer is b=5). Pick up the parameter manually boring, so in MS EXCEL there is a tool Selection option.

 Consider the standard filter (Data/ Sort & filter/ Filter) – AutoFilter. It is a convenient tool for the selection of the table rows match the conditions specified by the user.

Calculate subtotals in MS EXCEL table. For example, the table contains sales information for several different categories of goods calculate the cost of each category.

To simplify the management of logically related data in EXCEL 2007 introduced new spreadsheet format. The use of tables in EXCEL 2007 format reduces the likelihood of incorrect data entry, simplifies the insertion and removal of rows and columns simplifies formatting of tables. 

form controls (combo Box, check Box, Counter, etc.) help you to quickly change the data on the sheet in a certain range, to turn on and off options, make choices, etc. In principle, you can do without them, but they do manage the data on the sheet more visible and reduce the likelihood of entering incorrect data.

check Box returns TRUE (if checked) or FALSE (if unchecked).

Item Counter allows you to change the values in a certain range with a certain step (1, 2, 3, ...). By default, the range of values is defined from 0 to 30000, step =1.

Element the scroll bar allows to change the values in a certain range with a step (1, 2, 3, ...), if you press buttons with arrows, and with increased pitch, if you click on the strip itself in the side of the slider. This element has much in common with Counter.

the elements of the Switch allow selection of values from a given range 1, 2, 3, ... This element has much in common with Flag.

form control List displays a list of multiple items that can be chosen by the user. This element has much in common with a combo Box.

the combo Box is a combination text field and drop-down list. The combo box is more compact than list, but in order to display a list of items that the user must click the arrow. The combo box should be used when you want to be able to enter in the list or choose only one item. This control displays the current value. This element has much in common with the List.

a Scenario is a tool in MS EXCEL from the group Analysis of "what-if" (the Data Tab/ Group Working with data). the script Manager allows you to create and use various values of the initial data in the model, and also generates automatic reports showing the results of a calculation.

Trends in series of values (e.g., price fluctuations, sales) can be tracked in EXCEL with charts or conditional formatting. In this article we will look at sparklines (sparklines) which appeared in MS EXCEL 2010. Sparklines are located inside cells and are minigraphic that visualize the behavior of the data, maximum/ minimum values, and other singular points. 

it is Often necessary to distance textowo string from one cell in several. This can be either the full name "Ivan Ivanovich Ivanov" or the address "Moscow, Northern Blvd., 133" or passport details. Use the text import wizard.

Article is dedicated to those users who continue to work in MS EXCEL 2003.

the formula bar is the bar at the top of the Excel window that you use to enter and edit values or formulas in the worksheet cells. In the formula bar displays the value or formula contained in the active cell.

the Name Box is to the left of the formula bar. You can use this field as the information to display the address of the active cell, Names ranges, or to create new Names.

Surely You've used the built-in autocomplete lists. For example, if you enter into a cell January, and then the value to copy fill handle down into multiple cells, all these cells will automatically be filled in the subsequent names of the months: February, March... The good news is, in EXCEL you can create a custom autocomplete lists.

Current region is a data region around the active cell bounded by blank rows and blank columns.

Tool Progression gives us some great possibilities. With the help of it you can fill a range of cells with data according to a certain law: in an arithmetic or geometric progression. You do not even need to know the formula. For the values in the date format provided for the generation of specific sequences.

a Dialog Transition, called by F5, allows you to quickly navigate not only between the defined in the book of names cells and ranges, but also remembers the last 4 places (address ranges) you've visited, and those places can be on all the currently open workbooks.