Large&Small function
Can be use to create Top 10 and Last 10
ROWvsROWS
The first oner returns the number of the row that belongs to the cell reference. The second one returns the number of rows in a range.
Choose can be used as a replacement of
SUMIF, COUNTIF
Text and regional settings
If used in a machine with different locale format it will not get converted and it will result in error
TEXT when to use
It’s useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols
TEXT be careful when using ..
The TEXT function will convert numbers to text, which may make it difficult to reference in later calculations. It’s best to keep your original value in one cell, then use the TEXT function in another cell. Then, if you need to build other formulas, always reference the original value and not the TEXT function result
N what it does and what can substitute
- It returns the value of a cell. In the case o numbers=numbers, in the case of date formated returns the numeric value of the date, TRUE/FALSE=1/0 …
- Can be use to include comments in the formula (use + and the function N() and is the + because Nof text returns zero, so efectivelly I’m adding up zero to it
GetPivotData when to use instead of using directly the Pivot Table
When needed to customize a Graph for example
Indirect, what is it?
Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
Indirect disavantage
Its allways calculating and it can hamper the performance of Excel
Combo Box, Cell Link is
Returns the position of the value selected, not the content of it
Check Box, returns True/False and I can use
- Conditional Formatting basedon the result.
* Can use If to return a value
Option Buttons, if using more than 2 and the others should be separate selection
Need to use Group box control
List Box is similar to the
Combo box, but the values are displayed without having touse the drop down arrow
Charts trick how to add a series easyly
Position in a chart area and select the table and drag it to the right to include the new series
Excel Charts Series can take… but not …
- Can take ranges or name (name manager)
* Will not take formulas
Index():
Index() before or after colon “:” returns an address instead of the value of the cell. This is handy when creating a chart with a dynamic range via the use of Name Manager
Data series using Names
Using only the name will result in an error The name of the sheet need to be included (notation)
Combo Box range orientation and solution
- It works with vertical range only. If horizontal is used, it returns the first value only
- Use TRANPOSE with Dynamic Array: 1.- Highlight the area where you are going to transpose vertical 2.- In the formula bar enter TRANSPOSE and select the horizontal range 3.- Click CTRL+SHIFT+ENTER
How to avoid empty spaces in a combo box list
By using Name Manager and a Really cool way to avoid empty values in a drop list by using the match with the highest possible value of a list and the option 1 (last value)
Creating Dynamic Ranges for graph
- Using Data Validation
- Creating a formula and assigning a name via Name Manager.
- Remember to use Sheet1! or Name of the sheet in the Name Manager range reference
