This article presents a collection of simple and nifty Excel tools. What is called tips & tricks in English. In some cases, I provide a link to the original source. This only means that it was there that I first met the description of the method.
Excel has an additional (alternative) drag- and- drop context menu with useful options (Alternate Drag and Drop menu). To access it, select a cell or range of cells. Move to the right edge of the data. Right-click and drag the border to the right to the new area. Release the mouse button.
The Auto Filter option for Pivot Tables is disabled, but you can trick Excel into applying a regular AutoFilter to the Pivot Table headers by selecting one cell outside of the Pivot Table. An alternative is to use Filter by value for strings.
If you need to find the sum of digits in a line of text, you can use the algorithm: 1 * number of ones in a line + 2 * number of twos in a line + … + 9 * number of nines in a line = result
Some engineering design problems require the use of tables to calculate parameter values. Since the tables are discrete, linear interpolation can be used to obtain an intermediate value for the parameter. What if there are two control parameters? You can perform calculations using a single mega-formula.
The table includes the height above the ground (control parameter) and wind speed (calculated parameter). For example, if you need to find the wind speed corresponding to a height of 47 meters, then you should apply the formula: 130 + (180 – 130) * 7 / (50 – 40) = 165 m/s.
Cyclic formulas are generally evil, but sometimes they are useful. If you need to rewrite text in a cell , you can use VBA code. However, there is a non-trivial way to accomplish the task using a formula.
Excel uses the option in tables to sort the values by default. But you can also use the SMALL and LARGE functions, respectively, to sort the range in ascending and descending order.
If you want to generate non-repeating random numbers between 1 and 100, Excel offers the RAND () and RANDBETWEEN () functions by default, but both of them generate random numbers with a lot of duplicates. The problem can be solved by creating an array of unused numbers in the range, and then choose from them at random. The note is also interesting for the method of working with mega formulas.
Background: I usually solve such problems with the help of three additional columns, but PGC01 (this is the nickname of the forum member) offered a rather big formula to cope with this problem in one fell swoop. To understand the formula, you first need to familiarize yourself with the operation of the SMALL function (array; k).
Despite the fact that the usual type of A1 links is dominant, there are situations when the R1C1 style is significantly more effective. This style is more convenient for recording macros, VBA programming, using the INDIRECT function, and conditional formatting.
The SUM function sums up all cells in a range, whether they are hidden or not. If you want to sum only the visible rows, use the INTERMEDIATE.TOTALS function. This function is more often used in tables, but nothing prevents you from using it like a normal worksheet function.
The INDIRECT function is used when you need to refer to a cell whose address will be determined based on calculations. INDIRECT will also handle when the sheet to be referenced is precomputed. Bill Jelen describes a trick that works even if the sheet name is a date. Moreover, INDIRECT allows you to refer to a range of cells, which is then used inside the VLOOKUP or SUMIF functions.
If you have many worksheets with the same structure, for example 12 worksheets with sales results for each month, you can find the amount in the same cell on each worksheet using a 3D formula. If the sheets are named Jan, Feb, etc., and you need to find the sum in cell B4 for all months, use the formula = SUM (Jan: Dec! B4).
Few people know that the space operator allows you to find a value at the intersection of sets. For example, if you created a horizontal named range Boston and a vertical range named Sales, the formula = SUM (Boston Sales) finds the Boston sales value.
Before the introduction of VBA, macros are written in the language of xlm (Excel Macro). The language used macro functions, i.e. Excel 4.0 macro sheet functions. This language is still maintained by Microsoft for compatibility with previous versions of Excel. Language xlm among others contains the function Poluchit.Yacheyku (GET.CELL), which provides much more information than the present function CELL (). In fact, Get. Cell can tell you about 66 different attributes of a cell, while the CELL function only returns 12 parameters.
Beginning with version 2010, Excel introduced a flexible NETWORKDAYS.INTL function that allows you to calculate the number of working days for any length of a work week and any (but constant) set of weekends.
If you need to find the number of the first non empty cell in a row (column), or the value of the first non empty cell, you can use one little known property of the INDEX function to return not one value, but an array.
While the IF function and most similar functions easily convert logical TRUE / FALSE values to numeric 1/0 values, SUMPRODUCT does not. To deal with problems, use the minus minus operation, or put all the criteria in one argument, using multiplication (*) for a Boolean AND criterion and addition (+) for an OR.
If you’re not a novice user, you may be interested in various books based on Excel Gurus Expand Your Horizons: Do the Impossible with Microsoft Excel. Specifically, you will learn about Boolean algebra, minus minus operator, INDIRECT function, timer, R1C1 references, dates before 1900.
Hope the above article is helpful. Get more tutorials like this please visit us on our official web page.