EXCEL training tips and tricks
Tips &Tricks
EXCEL GUIDE
Tips &Tricks
To better use Excel
Motto
Once we learn how to perform an action in Excel, we tend to continue using the same method again and again. Sometimes, we know there must be an easier way to do things, but it requires effort to find out how, so we continue to use the method that we know will get us there in the end.
This project lists a number of simple Excel tips and tricks. If you spend just a fewminutes learning these tips, you are likely, in the long-term, to save hours of time developing your spreadsheets and also to produce Excel spreadsheets that have a much more professional look.
Once you start to use the following Excel tips and tricks, you will look back and wonder how you ever managed without them!
Contents
Part 1 - Not another Excel Book 6
Chapter I. Excel Basics .. 6
Learn to calculate in Excel . 6
Boolean Notions 8
Chapter II. Excel Decisional Functions 11
IF Function .. 11
IFERROR .. 12
SUMIF 12
SUMIFS . 13
SUMPRODUCT . 15
Chapter III. Excel Math & Statistical Functions .. 16
MOD .. 16
ROMAN . 17
ROUND . 17
ROUNDDOWN . 18
ROUNDUP 18
SUBTOTAL 19
AVERAGE . 20
AVERAGEA .. 22
COUNT .. 22
COUNTA 23
COUNTBLANK . 23
COUNTIF .. 24
MAX .. 25
MAXA . 25
MIN 25
MINA . 26
MODE 26 Chapter IV. Data Reference & Reference Functions . 28
VLOOKUP . 31
HLOOKUP . 34
INDEX .. 36
INDIRECT . 41
MATCH .. 43
COLUMN .. 45
ROW .. 45
Chapter V. Text Functions . 46
CLEAN .. 43
CONCATENATE 45 LEFT .. 45
LEN 43
MID 45 RIGHT .. 45
UPPER .. 43
LOWER . 45
SEARCH 45
TRIM . 43
REPT . 50
Chapter VI. Date & Time Functions .. 52
NOW .. 52
TODAY .. 52
DATE . 53
TIME .. 53
DATEVALUE . 54
. 58
DAYS360 .. 58
Chapter VII. Data Types & Data Conversion . 59
BAHTTEXT 61
DOLLAR 60
FIXED 61 TEXT . 61
Chapter VIII. Working with 2d data (tables) .. 65
Conditional Cell Formatting . 65
Pivot Tables . 72
Pivot Table Page Field .. 75
Freeze Panes .. 76
Freeze Panes Using the Active Cell .. 76
Filter Data in a Range or Table .. 78
Types of filters 78
Apply a Filter .. 79
Hide or Show Worksheets or Workbooks 85
Correct common errors when entering formulas .. 87
Correct an error value .. 89
Part 2 -How to… 90
How to check HP Roles on a profiling report .. 90
How to create a budget upload from a Marketing Plan 93
How to check the Partner Led/MSA Led correspondence in a Marketing Plan .. 95
How to check for blank cells in a Marketing Plan .. 97
How to check the values correspondence in a Marketing Plan .. 98
How to create Variable Dropdown Lists .. 99
How to search for many activity IDs in a Claim Form Report . 101
How to convert text to columns .. 105
How to use freeze panes on a CAT report 107
How to highlight desired cells on a WOS LFM Top 50 report .. 108
How to see the claiming status per partner on a CST report .. 110
Part 3 -Tips & Tricks 1122
Part 1 Not another Excel Book
Chapter I. Excel Basics
Learn to calculate in Excel
Formulas are equations that perform calculations on values in your worksheet.
A formula always starts with an equal sign (=)
It can contain any or all of the following: functions, references, operators, and constants.
Functions: A prewritten formula that, depending on a defined set of arguments, returns a value or values. Arguments can be: references, constants, expressions, but there are also functions that use no argument at all.
Ex: The PI() function returns the value of pi: 3.142
The order in which they are arranged in the formula is called syntax.
References: A2 returns the value in cell A2.
Constants: A value that is not calculated and, therefore, does not change.
Ex: Numbers or text values entered directly into a formula, such as 2.
Operators: A sign or symbol that specifies the type of calculation to perform within an expression.
Ex: The ^ (caret) operator raises a number to a power, the * (asterisk) operator multiplies
Formulas in Excel can include operators that combine arguments to produce intermediate results that are used as arguments for the formula, called expressions.
Operators work on operands. For example, in the expression "1+3", 1 is the first operand and 3 is the second operand.
Arithmetic operator | Meaning | Example |
+ (plus sign) | Addition | 3+3 |
– (minus sign) | Subtraction/Negation | 3–1–1 |
* (asterisk) | Multiplication | 3*3 |
/ (forward slash) | Division | 3/3 |
% (percent sign) | Percent | 20% |
^ (caret) | Exponentiation | 3^2 (the same as 3*3) |
Comparison operator | Meaning | Example |
= (equal sign) | Equal to | A1=B1 |
> (greater than sign) | Greater than | A1>B1 |
< (less than sign) | Less than | A1<B1 |
>= (greater than or equal to sign) | Greater than or equal to | A1>=B1 |
<= (less than or equal to sign) | Less than or equal to | A1<=B1 |
<> (not equal to sign) | Not equal to | A1<>B1 |
Text operator | Meaning | Example |
& (ampersand) | Connects, or concatenates, two values to produce one continuous text value | "North" & "wind" pro |
Reference operator | Meaning | Example |
: (colon) | Range operator, which produces one reference to all the cells between two references, including the two references | B5:B15 |
, (comma) | Union operator, which combines multiple references into one reference | SUM(B5:B15,D5:D15) |
< > (space) | Intersection operator, returns the common cells of the two references | SUM(B5:D15 A1:D5) |
Computing order
Order | Operator | Operation |
1st | : | Range |
2nd | <space> | Intersection |
3rd | , | Union |
4th | -- | Negation |
5th | % | Percentage |
6th | ^ | Exponentiation |
7th | * and / | Multiplication and division |
8th | + and - | Addition and subtraction |
9th | & | Concatenation |
10th | = < > <= >= <> | Comparison |
Boolean Notions
The Boolean functions in Excel evaluate an expression to either TRUE or FALSE, called truth values. In Boolean algebra these two values are represented by 1, respective 0.
Truth tables
|
|
AND – tests a number of user-defined conditions and returns a result of:
- TRUE if ALL of the conditions evaluate to TRUE
Or
- FALSE otherwise (i.e. if ANY of the conditions evaluate to FALSE)
Syntax: AND ( logical_test1, [logical_test2], )
The arguments, logical_test1, [logical_test2], etc, are conditions that evaluate to either TRUE or FALSE.
Note that any numeric values, except zero are treated as the logical value TRUE, and the value zero is treated as the logical value FALSE.
Examples
Formulas: Results:
|
|
Note that, in the above example spreadsheet:
• the function in cell C1 evaluates to TRUE, as BOTH of the supplied conditions are TRUE
• the function in cell C2 evaluates to FALSE, as the third condition, B2>12, is FALSE
• the function in cell C3 evaluates to FALSE, as ALL of the supplied conditions are FALSE
OR– tests a number of supplied conditions and returns either:
- TRUE if ANY of the conditions evaluate to TRUE
Or
- FALSE otherwise (i.e. if ALL of the conditions evaluate to FALSE)
Syntax: OR ( logical_test1, [logical_test2], )
The logical_test arguments are one or more conditions that evaluate to either TRUE or FALSE.
Note that any numeric values, except zero are treated as the logical value TRUE, and the value zero is treated as the logical value FALSE.
Examples
Formulas: Results:
A | B | C | A | B | C | ||
1 | 5 | 10 | =OR( A1>0, A1<B1 ) | 1 | 5 | 10 | TRUE |
2 | 5 | 10 | =OR( A2>0, A2>B2, B2>12 ) | 2 | 5 | 10 | TRUE |
3 | 5 | 10 | =OR( A3<0, A3>B3, B3>12 ) | 3 | 5 | 10 | FALSE |
Note that, in the above examples:
• the function in cell C1 evaluates to TRUE, as BOTH of the supplied conditions are TRUE
• the function in cell C2 evaluates to TRUE, as the first condition, A2>0, is FALSE
• the function in cell C3 evaluates to FALSE, as ALL of the supplied conditions are FALSE
NOT – receives a logical value and simply returns the opposite logical value.
If supplied with the value TRUE, the Not function returns FALSE and if supplied with the value FALSE, the function will return the value TRUE.
Syntax: NOT ( logical )
Examples
Formulas: Results:
Chapter II. Excel Decisional Functions
IF– returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE
Syntax: IF( logical_test, value_if_true, value_if_false )
logical_test - The condition that is to be tested and evaluated as either TRUE or FALSE value_if_true - The result returned if the supplied logical_test evaluates to TRUE value_if_false - The result returned if the supplied logical_test evaluates to FALSE
Nesting the Excel If Function
The IF function is frequently 'nested' in Excel. I.e. the value_if_true or the value_if_false argument is replaced with another call to the IF function.
Excel 2007 and Excel 2010 allow up to 64 levels of nesting. For Example, the following formula has 8 levels of nesting:
=IF(A1=1,"red",IF(A1=2,"blue",IF(A1=3,"green",IF(A1=4,"brown",
IF(A1=5,"purple",IF(A1=6,"orange",IF(A1=7,"yellow",
IF(A1=8,"grey",IF(A1=9,"pink","black")))))))))
If you do find yourself using multiple levels of nesting, you should probably consider other Excel functions that can be used to obtain the same result more succinctly.
Example 1
The following example shows the Excel If function applied to two sets of numbers. In this example, the logical_test checks whether the corresponding value in column B is equal to 0, and the function returns:
Example 2
The logical_test within the Excel If function can be any type of expression that returns a TRUE or FALSE result. The following example shows some more examples of the function, using different types of logical_test.
Example 3
The following example shows nesting of the Excel If function. The value_if_true argument is a further IF function, therefore:
• If the value in column B is equal to 0, a further call to 'If' is made, to test the value in column C
• If the value in column B is not equal to zero, the function returns the value in column A divided by the value in column B
IFERROR – tests whether the value expression is an error. IFERROR returns value_if_error if the expression is an error or value of the expression if it is not an error.
Syntax: IFERROR (value,value_if_error)
Value - The initial value or expression that should be tested
Value_if_error - Value or expression to be returned if the initial Value argument returns an error
Examples
SUMIF – finds values in a supplied array, that satisfy a given criteria, and returns the sum of the corresponding values in a second supplied array
Syntax: SUMIF (range, criteria, [sum_range])
Range - An array of values (or range of cells containing values) to be tested against the criteria
Criteria - The condition to be tested against each of the values in range
[sum_range] - An optional array of numeric values (or cells containing numbers) which are to be added together, if the corresponding range entry satisfies the supplied criteria
SUMIFS – finds values in one or more supplied arrays, that satisfy a set of criteria, and returns the sum of the corresponding values in a further supplied array.
Syntax: SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], )
sum_range - An array of numeric values (or cells containing numbers) which are to be added together, if the corresponding range entries satisfy all the supplied criteria
criteria_range1, [criteria_range2], - Arrays of values (or ranges of cells containing values) to be tested against the respective criteria1, criteria2, (The supplied criteria_range arrays must all have the same length as the sum_range)
criteria1, [criteria2], - The conditions to be tested against the values in criteria_range1, [criteria_range2],
The function can handle up to 127 pairs of criteria_range and criteria arguments. Each of the supplied criteria can be either:
- a numeric value (which may be an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2008, TRUE), or
- a text string (eg. "Name", "Thursday"), or
- an expression (eg. ">12", "<>0")
and can be supplied to the function either directly, as a reference to a cell, or as a value returned from another function or formula.
Note that if your criteria is a text string or an expression, this must be supplied to the function in quotes.
Also note that the Excel Sumifs function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be equal.
Example
To find the sum of sales in the North area during quarter 1:
=SUMIFS( D2:D13, A2:A13, 1, B2:B13, "North" )
In this example, the Excel Sumifs function identifies rows where:
- The value in column A is equal to 1
And
- The entry in column B is equal to "North"
and calculates the sum of the corresponding values from column D.
This formula finds the sum of the values
$223,000 and $125,000 (from cells D2 and D3), which gives the result $348,000.
SUMPRODUCT – returns the sum of the products of the corresponding values in two or more supplied arrays
Syntax: SUMPRODUCT (array1, array2, [array3], )
array1, array2, … - where the array arguments are arrays of numeric values, which may
be supplied to the function directly, or as one or more cells or ranges of cells containing numeric values. All of the supplied arrays must be of equal length and non-numeric values in the supplied arrays are treated as the value zero.
Examples
Formulas
Results
Chapter III. Excel Math & Statistical Functions
MOD – returns the remainder of a division between two supplied numbers.
Syntax: MOD (Number, Divisor)
The arguments are:
These arguments can be supplied to the function either as simple numbers, as references to cells containing numbers, or as values returned from other functions. This is shown in the examples below.
Examples
The following spreadsheets show simple examples of the Excel Mod function. The format of the function is shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.
FormulasResults
ROMAN – converts an Arabic number to Roman - i.e. for a supplied number, the function returns a text string depicting the roman numeral form of the number.
Syntax: ROMAN (number, [form])
Where the arguments are as follows:
number - The number that is to be converted to a Roman numeral (must be an integer between 0 and 1999) |
[form] - An optional argument that specifies the form of the Roman numeral returned This can take any of the following values: 0 - Classic form 1 - More Concise 2 - More Concise 3 - More Concise 4 - Simplified form TRUE - Classic form FALSE- Simplified form If the [form] argument is omitted, it takes on the default value of 0 (denoting the classic form is used). |
TIP! If any of the arguments are input as decimal values, they will be truncated to integers. If the supplied number is < 0 or > 3999, it will not be recognized as a number.
Examples
The following spreadsheet shows example of the Excel Roman function used to convert the number 9999 to different forms of Roman numerals. The spreadsheet on the left shows the format of the function, and the spreadsheet on the right shows the result.
Formula Result
ROUND – rounds a supplied number up or down, to a specified number of decimal places.
Syntax: ROUND( Number, Num_Digits )
The arguments are:
Number The initial number
Num_Digits The number of decimal places to round the supplied Number to.
The Number and Num_Digits arguments can be supplied as either simple numbers, references to cells containing numbers, or as values returned from other functions or formulas.
Examples
The following spreadsheets show the Excel Round function used with several different argument values:
FormulasResults
TIP! It is often a good idea to use the ROUND function when comparing two numbers in Excel, especially if the numbers are the result of multiple mathematical calculations. This is because multiple calculations may result in the introduction of rounding errors, which may cause small inaccuracies in the numbers stored in Excel.
For example, the decimal 5.1 may, due to rounding errors, be stored in cell A1 of your spreadsheet, as 5.10000000000001. When compared to the exact value 5.1, the value in cell A1 will not be equal to the exact value 5.1.
ROUNDDOWN – rounds a supplied number down, towards zero, to a specified number of decimal places.
Syntax: ROUNDDOWN(Number, Num_Digits)
The arguments are:
Number | The initial number |
Num_Digits | The number of decimal places to round the supplied Number down to. |
The Number and Num_Digits arguments can be supplied as either simple numbers, references to cells containing numbers, or as values returned from other functions or formulas.
Examples
The following spreadsheets show the Excel Rounddown function used with different Num_Digits values on both positive and negative numbers:
FormulasResults
The above examples show how the Rounddown function will always round towards zero, to the specified number of decimal places. - ie. If the supplied Number is positive, it is rounded down, but if the number is negative it is rounded up (towards zero).
ROUNDUP-rounds a supplied number up, away from zero, to a specified number of decimal places.
Syntax: ROUNDUP( Number, Num_Digits )
Where the arguments are:
Number The initial number
Num_Digits The number of decimal places to round the supplied Number up to.
Examples
The following spreadsheets show the Excel Roundup function used with different Num_Digits values on both positive and negative numbers:
FormulasResults
The above examples show how the Roundup function will always round away from zero, to the specified number of decimal places. - ie. If the supplied Number is positive, it is rounded up, but if the number is negative it is rounded down (away from zero).
SUBTOTAL - performs a specified calculation (eg. the sum, product, average, etc.) for a supplied set of values.
Syntax: SUBTOTAL ( Function_num, Ref1, [Ref2], )
out using the Autofilter. Filtered out cells are not included in any Subtotal calculations. This is illustrated in the examples below.
Examples
The Subtotal function is used to calculate the sum and the average monthly sales per team. All of the cells in the range of sales figures are visible, so all values are included in the subtotal calculations:
FormulasResult
AVERAGE- returns the arithmetic mean of a list of supplied numbers.
Syntax: AVERAGE (number1, [number2], )
Where the number arguments are a set of one or more numeric values, or arrays of numeric values, for which you want to calculate the average - these values can be supplied directly to the function, or as one or more cells or ranges of cells containing numeric values.
AVERAGE vs. AVERAGEA
The AVERAGE Function is very similar to the AVERAGEA function. The only difference between these two functions is the way in which logical values or text values within arrays or references are treated in the calculation of the arithmetic mean. This is shown in the table below:
Examples
The following spreadsheet shows the Excel Average function used to calculate the arithmetic mean of the set of values in cells A1-A5. Although the same 5 values are provided to each of the functions in cells B1-B4, in each case, the values are provided to the function in different ways.
Formulas: Results:
COUNT-returns the number of numeric values in a supplied set of cells or values. This count includes both numbers and dates. Syntax:COUNT (value1, [value2], )
value1, value2, … - one or more values or references to cell ranges.
Numbers and dates are always counted as numeric values by the Excel Count function. However, text representations and logical values are counted differently, depending on whether they are supplied as a value in a range of cells, or if they are supplied directly to
the function.
The table below summarizes which values are and which are not treated as numeric values by the Excel Count function:
Value Within a Range of Cells | Value Supplied Directly to Function | |
Numbers | ARE counted | ARE counted |
Dates | ARE counted | ARE counted |
Logical Values | NOT counted | ARE counted |
Text Representations of Numbers & Dates | NOT counted | ARE counted |
Other Text | NOT counted | NOT counted |
Errors NOT counted NOT counted
Example 1 - Values Supplied from a Range of Worksheet Cells
The following spreadsheet shows several examples of the Excel Count function, with the values supplied to the function as one or more ranges of cells.
Formulas: Results:
In the above example:
• The numbers and the date, 01/01/2010 are counted by the function.
• The text value "text", the logical value FALSE, and the error value #N/A are not counted by the function.
• The example in cell C3 uses two ranges that intersect, and both ranges include the cell A1. In this case, Excel counts the cell A1 (which DOES contain a numeric value) twice - once for each range that it is contained in.
Example 1 - Values Supplied Directly to the Excel Count Function
The following spreadsheet shows several examples of the Excel Count function, with the values supplied directly to the function.
Formulas: Results:
Note, in the above example:
• The number 100 and the date 01/01/2010 are counted by the function.
• The text representations of the number "100" & the date, "01/01/2010", and the logical value FALSE, are counted by the function.
• The text string "text" and the error #N/A are not counted by the function.
COUNTA - returns the number of non-blanks in a supplied set of cells and/or values.
Syntax: COUNTA (value1, [value2], )
value1, value2, … - one or more values or references to cell ranges.
Note that, if a cell contains an empty text string or a formula that returns an empty text string, this cell is counted as a non-blank by the Counta function.
Examples
Formulas: Results:
COUNTBLANK - returns the number of blank cells in a supplied range of cells.
Syntax: COUNTBLANK (range)
The range argument specifies the range of cells in which you want to count blank cells.
Note that, if a cell contains an empty text string or a formula that returns an empty text string, this cell is counted as a blank by the Countblank function.
Examples
Formulas: Results:
Gives the result 3
Formulas: Results:
Gives the result 9
COUNTIF - returns the number of cells (of a supplied range), that satisfy a given criteria.
Syntax: COUNTIF (range, criteria)
Formulas: Results:
A | B | C | D | A | B | C | D | ||
1 | Sunday | 07-Sep-2008 | TRUE | 1 | Sunday | 07-Sep-008 | TRUE | ||
2 | Monday | 08-Sep-2008 | 2.1 | TRUE | 2 | Monday | 08-Sep-2008 | 2.1 | TRUE |
3 | Wednesday | 10-Sep-2008 | 2 | TRUE | 3 | Wednesday | 10-Sep-2008 | 2 | TRUE |
4 | Thursday | 11-Sep-2008 | 3 | FALSE | 4 | Thursday | 11-Sep-2008 | 3 | FALSE |
5 | Wednesday | 17-Sep-2008 | 2.5 | FALSE | 5 | Wednesday | 17-Sep-2008 | 2.5 | FALSE |
6 | Tuesday | 23-Sep-2008 | 3 | FALSE | 6 | Tuesday | 23-Sep-2008 | 3 | FALSE |
7 | Wednesday | 24-Sep-2008 | 6 | FALSE | 7 | Wednesday | 24-Sep-2008 | 6 | FALSE |
8 | Sunday | 05-Oct-2008 | 4 | FALSE | 8 | Sunday | 05-Oct-2008 | 4 | FALSE |
9 | Saturday | 11-Oct-2008 | FALSE | 9 | Saturday | 11-Oct-2008 | FALSE | ||
10 | 10 | ||||||||
11 | =COUNTIF( A1:A9, "Wednesday" ) | 11 | 3 | ||||||
12 | =COUNTIF( A1:A9, "<>Wednesday" ) | 12 | 6 | ||||||
13 | =COUNTIF( B1:B9, ">01/10/2008" ) | 13 | 2 | ||||||
14 | =COUNTIF( C1:C9, 0 ) | 14 | 2 | ||||||
15 | =COUNTIF( C1:C9, ">=3" ) | 15 | 4 | ||||||
16 | =COUNTIF( D1:D9, TRUE ) | 16 | 3 |
The example below shows the COUNTIF function used to identify duplicates in a column containing reference numbers. Note that the function in this example is written so that it highlights only the second, third, etc instance of a duplicate value. - It does not highlight the first instance of the value.
MAX - returns the largest value from a supplied set of numerical values.
Syntax: MAX( number1, [number2], )
The number arguments are a set of one or more numeric values, or arrays of numeric values, which you want to return the largest value of.
If an argument is supplied to the function as a reference to a cell, or an array of cells, the Max function will ignore blank cells, and text or logical values contained within the supplied cell range. However, logical values and text representations of numbers that are supplied directly to the function will be included.
Example
Formulas: Results:
MAXA - function returns the largest value from a supplied set of numerical values, while counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1.
Syntax: MAXA (number1, [number2], )
The number arguments are a set of one or more numeric values, or arrays of numeric values, that you want to return the largest value of.
Excel MAXA vs. Excel MAX
The MAXA function is very similar to the function. The only difference is when an argument is supplied to the function as a reference to a cell or an array of cells. In this case the Max function ignores logical and text values, while the Maxa function counts the logical value TRUE as 1, the logical value FALSE as 0 and text values as 0.
Example
Formulas: Results:
MIN - returns the smallest value from a supplied set of numerical values.
Syntax: MIN( number1, [number2], )
The number arguments are a set of one or more numeric values, or arrays of numeric values, that you want to return the smallest value of.
If an argument is supplied to the function as a reference to a cell, or an array of cells, the Min function will ignore blank cells, and text or logical values contained within the supplied cell range. However, logical values and text representations of numbers that are supplied directly to the function will be included in the calculation.
Excel MIN vs. Excel MINA
The MINAFunction is very similar to the MIN Function. The only difference is when an argument is supplied to the function as a reference to a cell or an array of cells. In this case the MIN function ignores logical and text values, while the Mina function counts the logical value TRUE as 1, the logical value FALSE as 0 and text values as 0.
Examples
Formulas: Results:
MINA - returns the smallest value from a supplied set of numerical values, while counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1.
Syntax: MINA( number1, [number2], )
the number arguments are a set of one or more numeric values, or arrays of numeric values, that you want to return the smallest value of.
Examples
Formulas: Results:
MODE – returns the statistical mode (the most frequently occurring value) of a list of supplied numbers. If there are 2 or more most frequently occurring values in the supplied data, the function returns the lowest of these values Syntax:MODE( number1, [number2], )
where the number arguments are a set of one or more numeric values, or arrays of numeric values, for which you want to calculate the mode. Text and logical values within a supplied array are ignored by the function.
Examples
Formulas: Results:
MODE Function – Common Problem
The Mode function returns the wrong value, or returns the #VALUE! error, even though the supplied values all appear to be numeric.
Possible Reason & Solution
Text values, including text representations of numbers, are ignored by the Mode function. Therefore, this problem may arise if the values in the supplied array are text representations of numbers, instead of actual values (read more about Excel data types on the Excel Formatting page)
This problem can be solved by converting all array values into numeric values. To do this:
1. Use the mouse to select the cells you want to convert (this must not span more than one column)
2. From the Data tab at the top of your Excel workbook, select the Text to Columns
option
3. Make sure the Delimited option is selected and click next
4. Make sure all the delimiter options are unselected and then click next again
5. You should now be offered a selection of Column Data Formats. Select General and click the Finish button
Note that the Text to Columns only converts values; it will not change the data type of a cell containing a function.
Chapter IV. Data Reference & Reference Functions
Reference in Excel
In Excel there are two types of cell references, these are Relative and Absolute.
Relative References
By default, Excel cell references are relative references. This means that a simple cell reference, used within an Excel cell, will be adjusted when copied to other cells.
For example, in the spreadsheet on the left-hand side below, cell E1 contains a reference to cell A1. The spreadsheet on the right shows the result of cell E1 being copied to cells F1-G1 and cells E2-G3. It is seen that:
• when copied from column E, into the columns F and G, the reference to cell A1 adjusts to reference cells in columns B and C
• when copied from row 1, into the rows 2 and 3, the reference to cell A1 adjusts to reference cells in rows 2 and 3
A | B | C | D | E | A | B | C | D | E | F | G | |
1 | 3 | 12 | 21 | =A1 | 1 | 3 | 12 | 21 | =A1 | =B1 | =C1 | |
2 | 5 | 15 | 26 | 2 | 5 | 15 | 26 | =A2 | =B2 | =C2 | ||
3 | 7 | 16 | 27 | 3 | 7 | 16 | 27 | =A3 | =B3 | =C3 | ||
4 | 9 | 18 | 29 | 4 | 9 | 18 | 29 | =A4 | =B4 | =C4 |
Absolute References
The relative referencing feature that is used by default for Excel cell references is useful for functions and formulae, as we frequently require cell references used in a formula to adjust when copied to other cells.
However, there are occasions when we need Excel cell references to remain constant when copied to other cells. In this case, we can use the $ symbol before a column or row reference, to change a cell reference into an absolute cell reference. This is shown in the example below, in which the $ sign is added to the reference A1 that is contained in the cell E1, changing this reference to $A$1.
As shown in the spreadsheet on the right, when the reference =$A$1 is copied to cells F1G1 and E2-G3, the reference remains constant:
A | B | C | D | E | A | B | C | D | E | F | G | |
1 | 3 | 12 | 21 | =$A$1 | 1 | 3 | 12 | 21 | =$A$1 | =$A$1 | =$A$1 | |
2 | 5 | 15 | 26 | 2 | 5 | 15 | 26 | =$A$1 | =$A$1 | =$A$1 | ||
3 | 7 | 16 | 27 | 3 | 7 | 16 | 27 | =$A$1 | =$A$1 | =$A$1 | ||
4 | 9 | 18 | 29 | 4 | 9 | 18 | 29 | =$A$1 | =$A$1 | =$A$1 |
Absolute and relative referencing can be mixed within a single cell reference. For example in the example below, the reference =$A1 uses absolute referencing for the column and relative referencing for the row. The result of this is that the row reference adjusts as cell E1 is copied into rows 2 and 3, but the column reference remains constant (referring to column A) when cell E1 is copied to other columns. The results of this are shown in the spreadsheet on the right below.
A | B | C | D | E | A | B | C | D | E | F | G |
1 | 3 | 12 | 21 | =$A1 | 1 | 3 | 12 | 21 | =$A1 | =$A1 | =$A1 |
2 | 5 | 15 | 26 | 2 | 5 | 15 | 26 | =$A2 | =$A2 | =$A2 | |
3 | 7 | 16 | 27 | 3 | 7 | 16 | 27 | =$A3 | =$A3 | =$A3 | |
4 | 9 | 18 | 29 | 4 | 9 | 18 | 29 | =$A4 | =$A4 | =$A4 |
A further example of mixed absolute and relative Excel cell references is shown in the example below. In this case, the reference =A$1 in cell E1, uses relative referencing for the column and absolute referencing for the row. The results obtained by copying cell E1 into adjacent cells are shown in the spreadsheet on the right below:
A | B | C | D | E | A | B | C | D | E | F | G |
1 | 3 | 12 | 21 | =A$1 | 1 | 3 | 12 | 21 | =A$1 | =B$1 | =C$1 |
2 | 5 | 15 | 26 | 2 | 5 | 15 | 26 | =A$1 | =B$1 | =C$1 | |
3 | 7 | 16 | 27 | 3 | 7 | 16 | 27 | =A$1 | =B$1 | =C$1 | |
4 | 9 | 18 | 29 | 4 | 9 | 18 | 29 | =A$1 | =B$1 | =C$1 |
References to Ranges
The relative and absolute referencing rules apply to ranges, as well as to individual Excel cell references. For example:
The reference $A1:$E1 becomes: $A1:$E1 when copied across to other columns $A2:$E2 when copied down by one row |
The reference A$1:A5 becomes: B$1:B5 when copied across to the right by one column A$1:A6 when copied down by one row |
The reference A$1:C$3 becomes: B$1:D$3 when copied across to the right by one column A$1:C$3 when copied to any other row |
Named Ranges & Dropdown Lists
The simplest kind of Excel drop down list is created using the Excel data validation options. To do this:
1. Enter your possible options values into a column (cells A1 to A10) - you can always hide this column later if you don't want it visible.
2. Select the cell (or cells) that you want your drop-down list to appear in
3. In the Data Tab from the top of the spreadsheet and then select Data Validation > Data_ Validation
4. The Validation menu will appear. Within this menu:
i. In the first box, select the option 'List'. This should cause further options to appear.
ii. Ensure the option 'In-cell dropdown' is ticked
iii. In the 'Source' option box, enter the range of cells where you typed in your original list of options (eg. $A$1:$A$10).
iv. Click OK. The dropdown list for cell C2 should now look like this:
The range selected for the dropdown list can be named. To name a range, select all the range, go to the Name box that appears at the left end of the Formula bar, type the name you want for the range and press Enter:
This way, in the Data Validation window, you can just type =Friends in the Source box, to create the dropdown list.
Excel Lookup and Reference Functions
VLOOKUP – 'looks up' a given value in the left-hand column of a data array (or table), and returns the corresponding value from another column of the array.
Syntax: VLOOKUP ( lookup_value, table_array, col_index_num, [range_lookup] )
where the arguments are:
lookup_value | - | The value that you want to look for, in the left-hand column of the supplied data array | |||
table_array | - | The data array or table, that you want to search the left hand column of, for the supplied lookup_value | |||
col_index_num | - | The column number, within the supplied array, that you want the corresponding value to be returned from | |||
[range_lookup] | - | An optional logical argument, which can be set to TRUE or FALSE, meaning : | |||
TRUE - if the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value (Note: If range_lookup is set to TRUE, the left-hand column of the table_array must be in ascending order) | |||||
FALSE - if the function cannot find an exact match to the supplied lookup_value, it should return an error |
Example 1
In the spreadsheet below, columns A and B list an inventory of grocery items, and their prices, and cell E2 of the spreadsheet shows a simple example of the Vlookup function being used to look up the price of an item from the inventory.
The above Vlookup function returns the price for "Cornflakes", which is $3.50.
In this example:
-the lookup_value is the text string "Cornflakes", which is located in cell D2
-the table_array is columns A-B of the spreadsheet
-the col_index_num is set to 2, to denote that the value returned should be taken from column 2 of the table_array
-the range_lookup argument is set to FALSE, to indicate that we only want a result to be returned if an exact match to the lookup_value is found
Example 2
In the spreadsheet below, columns A-C list the grades that are assigned to examination marks lying within the ranges 0-44%, 45%-54%, etc.
Cell F2 shows the score of 52% that was achieved by the student "Anne" in an examination. The Vlookup function in cell G2 looks up this score in column A of the spreadsheet and returns the associated grade from column C. Note that, in this example, if the exact score of 52% is not found in column A, we want, instead, to use the nearest value below this score.
The above Vlookup function returns the grade for the score 52%, which is E.
In this example:
-the lookup_value is the value 52%, which is located in cell F2
-the table_array is the range A2-C7 of the spreadsheet
-the col_index_num is set to 3, to denote that the value returned should be taken from column 3 of the table_array
-the range_lookup argument is set to TRUE, to indicate that, if an exact match to the lookup_value is not found, we want to use the closest value below the lookup_value
Vlookup Function – Common Errors
#N/A - Occurs if the Vlookup function fails to find a match to the supplied lookup_value The cause of this will generally depend on the supplied [range_lookup] argument:
if [range_lookup] = - the #N/A error is likely to be because the smallest value in the TRUE left-hand column of the table_array is greater than the supplied
(or is omitted) lookup_value
if [range_lookup] = - the #N/A error is likely to be because an exact match to the
FALSE lookup_value is not found in the left-hand column of the table_array
#VALUE! - Occurs if either:
The supplied col_index_num argument is < 1 or is not recognised as a
-
numeric value or
- The supplied range_lookup argument is not recognised as TRUE or FALSE
#REF! - Occurs if either:
- the supplied col_index_num argument is greater than the number of columns in the supplied table_array
or
- the formula has attempted to reference cells that do not exist. This can be caused by relative referencing errors when the Vlookup is copied to other cells.
HLOOKUP – 'looks up' a given value in the top row of a data array (or table), and returns the corresponding value from another row of the array.
Syntax:HLOOKUP ( lookup_value, table_array, row_index_num, [range_lookup]) The arguments are:
lookup_value - | The value that you want to look for, in the first row of the supplied data array | ||
table_array - | The data array or table, that you want to search the first row of, for the supplied lookup_value | ||
row_index_num - | The row number, within the supplied array, that you want the corresponding value to be returned from | ||
[range_lookup] - | An optional logical argument, which can be set to TRUE or FALSE, meaning : | ||
TRUE - FALSE - | if the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value (Note: If range_lookup is set to TRUE, the top row of the table_array must be in ascending order) | ||
if the function cannot find an exact match to the supplied lookup_value, it should return an error |
Example 1
Cells A2-F6 of the spreadsheet below, show the exam scores for 5 students in 4 different subjects. If you want to look up a specific score (eg. Biology) for one of the students (eg. Ed), this can be done using the Hlookup function, as shown in cell B10 of the spreadsheet.
In the example below, the formula is shown in cell B10 of the spreadsheet on the left, and the result is shown in the spreadsheet on the right.
Formulas: Results:
In the above example, the Hlookup function searches through the top row of the table_array (the range A2-A6), to find a match for the lookup_value (the name "Ed", in cell A10). When the the name 'Ed' is found, the function returns the corresponding value from the 5th row of the lookup_table.
This is illustrated on the right. The function finds the name 'Ed' in the top row of the table_array and then returns the value from the 5th row of the table_array.
If we change the name in the individual spreadsheet from 'Ed' to 'Cara', the Hlookup functions would automatically recalculate the functions to display the exam results for Cara.
Example 2
Cells A1-F3 of the spreadsheet below, show body types relating to body mass index (BMI), for the ranges 0 - 18.4, 18.5 - 24.9, 25.0 - 29.9 and over 30.
Cell C6 shows the user's current BMI, which is 23.5, and cell C7 shows the Hlookup function that is used to look up the body type that relates to this BMI.
The Hlookup function in the above spreadsheet returns the result "Normal Weight", which is the correct body type for a BMI of 23.5.
Note that, in this example, the range_lookup argument is set to TRUE, to tell that function that, if it cannot find an exact match to the supplied lookup_value, it should use the closest match below this value. Therefore, for all BMIs up to and including 18.4 the function would return "Underweight", for all BMIs between 18.5 and 24.9, the function would return "Normal Weight", etc.
Hlookup Function – Common Errors
#N/A - Occurs if the Hlookup function fails to find a match to the supplied lookup_value
The cause of this will generally depend on the supplied range_lookup:
if range_lookup = - the #N/A error is likely to be because the smallest value in the
TRUE lookup row is greater than the supplied lookup_value
(or is omitted)
the #N/A error is likely to be because an exact match to the
if range_lookup = - lookup_value is not found in the lookup row FALSE
#VALUE! - Occurs if either:
The supplied row_index_num argument is < 1 or is not recognised as a numeric
-
value
or
- The supplied range_lookup argument is not recognised as TRUE or FALSE
#REF! - Occurs if the supplied row_index_num argument is greater than the number of rows in the supplied table_array
INDEX Function
The Array format – is used when you want to look up a reference to a cell within a single range.
Syntax: INDEX (array, row_num, [colum_num] )
The arguments are:
array | - | The specified array or range of cells | |
row_num | - | Denotes the row number of the specified array If set to zero or blank, this defaults to all rows in the supplied array | |
colum_num | - | Denotes the column number of the specified array If set to zero or blank, this defaults to all columns in the supplied array | |
The function is best explained through the following examples:
Example 1
In the following example, the Index function returns a reference to row 5 of the supplied range, which is cell C5. This evaluates to the value 8
Formula Result
Example 2
In the following example, the Index function returns a reference to row 5 and column 2 of the supplied range, which is cell D5. This evaluates to the value 3
Formula Result
Example 3
In the following example, as the supplied col_num is set to 0, the Index function returns a reference to all of row 5 of the supplied range, which is the range C5:D5.
The sum of this range evaluates to the value 11
Formula Result
Array Formulas:
To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.
Example 4
In the following example, the supplied row_num is set to 0, and so the Index function returns a reference to all of column 2 of the supplied range. I.e. the function returns a reference to the rangeD1:D5.
In this case, the function is input into cells A1:A5, as an Array Formula. Therefore, the values of the five cells returned by the Index function are displayed in cells A1:A5.
Formula Result
The Reference Format– is used to extract references from ranges that are made up of many areas.
Syntax: INDEX ( reference, row_num, [colum_num], [area_num] )
Where the arguments are:
reference | - | The specified range of cells Note: If multiple areas are input directly into the function, the individual areas should be separated by commas and surrounded by brackets - ie. ( A1:B2, C3:D4, etc) |
row_num - | Denotes the row number of the specified array If set to zero or blank, this defaults to all rows in the supplied array | |
colum_num - | Denotes the column number of the specified array If set to zero or blank, this defaults to all columns in the supplied array | |
area_num - | If the initial specified range is made up of more than one area, the area_num argument specifies the number of the area to be used (Note that the areas are numbered by the order they are specified) |
This form of the Index function is illustrated in the following examples:
Example 1
In the following example, the Index function returns a reference to row 4 and column 2 of the 1st area (ie. C1:D5) in the supplied range. This is cell D4. This evaluates to the value 5
Formula Result
Example 2
In the following example, the Index function returns a reference to row 3 and column 1 of the 3rd area (ie. B10:D12) in the supplied range. This is cell B12, which evaluates to the value 7
Formula Result
Example 3
In the following example, as the supplied col_num is blank, the Index function returns a reference to all of row 3 of the 3rd area (ie. B10:D12) of the supplied range. This is the range B12. The sum of this range evaluates to the value 10.
Formula Result
Index Function – Common Errors
#REF! - Occurs if either:
the supplied row_num argument is greater than the number of rows in the
-
supplied range
-the supplied col_num argument is greater than the number of columns in the
supplied range the supplied area_num argument is greater than the number of areas in the
-
supplied range
#VALUE! - Occurs if any of the supplied row_num, col_num or area_num arguments are not numeric values
INDIRECT– the function takes a text string and converts this into a cell reference.
Excel does not understand the text string "B1" to mean a reference to the cell B1. Therefore, if you extract or build up a reference to a cell or range using text, you will need to use the INDIRECT function to convert this into a reference that Excel can understand. Syntax:INDIRECT( Ref_text, A1 )
The arguments are:
Ref_text | - | The text describing the reference |
A1 | - | An optional logical argument that defines the style of the Ref_text reference. This can be either : • True - to denote that the reference is in A1 style • False - to denote that the reference is in R1C1 style If this argument is omitted, it takes on the default value "True" |
Examples
The following spreadsheets show simple examples of the Excel INDIRECT function. The format of the function is shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.
Note that, in the above examples:
• Although the Excel INDIRECT function always returns a cell (or range) reference, in cells A1 - A3, the cells are evaluated and it is this value that is shown in the cell.
• In cell A3, the ROW function has been used to return the reference to the current row number (ie. 3), and use this to form part of the cell reference.
• In cell A4, the INDIRECT function returns a reference to the range C1:E1, which has been fed into the Excel SUM function. This SUM therefore evaluates to the sum of 8 + 9 + 0 = 17
MATCH– the function finds the relative position of a value in a supplied array. This can be either an exact match or the position of the closest match (above or below), if an exact match is not found.
Syntax:MATCH( lookup_value, lookup_array, [match_type] ) The arguments are:
lookup_value - | The value that you want to look up | ||
lookup_array - | The data array that is to be searched | ||
[match_type] - | An optional logical argument, which can set to 1, 0 or -1 to return the following results : | ||
1 - | if the function cannot find an exact match, it should use the closest match below the lookup_value. (If this option is used, the lookup_array must be in ascending order). if the function cannot find an exact match, it should return an error. (If this option is used, the lookup_array does not need to be ordered). if the function cannot find an exact match, it should use the closest match above the lookup_value. (If this option is used, the lookup_array must be in descending order). | ||
0 - | |||
-1 - | |||
If the [match_type] argument is omitted from the function, this is set to 1 by default. |
The Match function can be used to match numeric values, logical values, or text strings.
Note that, when used with text strings, the function is NOT case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be a match.
It is also useful to know the wildcards that can be used with text strings, when the [match_type] argument is set to 0 (requiring an exact match). These are:
Wildcards
? - | replaces any single character |
* - | replaces any number of characters |
These are shown in the examples below.
Example 1
Formulas: Results:
As the [match_type] argument set to 0, the text strings in the lookup_array (cells A1-A5) do not need to be ordered.
In this case the function only returns a result if an exact match to the lookup_value is found. Otherwise, the function returns an error.
Example 2
The following spreadsheet also shows the Excel MATCH function used with the [match_type] argument set to 0, but in this case the function is used to look up numeric values.
Formulas: Results:
Example 3
Here, the [match_type] argument is set to 1, so the data in the lookup_array must be in ascending order, and the function returns the position of the exact match to the lookup_value if this is found, or the position of the closest value below the lookup_value if an exact match is not found.
Formulas: Results:
Note that the [match_type] argument could have been omitted from the functions in this spreadsheet, as this argument takes the value 1 by default.
Match Function – Common Errors
#N/A - Occurs if the match function fails to find a match for the lookup_value. This may be either :
if [match_type] = 0 - an exact match for the lookup_value is not found within the lookup_array
if [match_type] = 1- the first value in the lookup_array is larger than the lookup_value
(or is omitted) (note that, if the array is in ascending order, this means there is no closest match below or equal to the lookup_value)
if [match_type] = -1- the first value in the lookup_array is smaller than the lookup_value (and therefore, if the array is in descending order, there is no closest match above or equal to the lookup_value)
If you have checked that you have the correct match_type argument value and that the lookup_value (or a closest match) exists in the lookup_array, it may be that the match has failed because either:
- there are unseen spaces in either the lookup_value or the values in the lookup_array
or
- the lookup_value and the data in the lookup_array have different data types
Either of these cause the lookup_value and the values in the lookup_array to be seen by Excel as containing different values, and will therefore cause the Match function to fail.
Common Match Function Problem -The Excel Match function returns the wrong result
Possible Reason: If your Excel Match function simply returns the wrong result, this is likely due to the lookup_array not being ordered correctly.
Check the following:
1. Make sure the [match_type] argument is set to the correct value (Note that if this argument is omitted, it will default to 1)
2. If you have set the [match_type] argument to 1 or -1, check that the lookup_array is correctly ordered:
- if [match_type] is set to 1, the lookup_array should be in ascending order
- if [match_type] is set to -1, the lookup_array should be in descending order
ROW – returns the first row number within a supplied reference, or if no reference is supplied, the function returns the number of the current row in the currently active Excel spreadsheet.
Syntax: ROW ( [reference] )
Examples
Formulas: Results:
|
|
|
| |||||||||||||||||||
| ||||||||||||||||||||||
| ||||||||||||||||||||||
| ||||||||||||||||||||||
| ||||||||||||||||||||||
| ||||||||||||||||||||||
|
COLUMN – returns the first column number within a supplied reference, or if no reference is supplied, the function returns the number of the current column in the currently active Excel spreadsheet.
Syntax: COLUMN ( [reference] )
Examples
Formulas: Results:
|
|
|
|
|
| ||||||||||||||||||||
|
| ||||||||||||||||||||||||
|
|
Chapter V. Text Functions
CLEAN– removes the non-printable characters, represented by numbers 0 to 31 of the 7bit ASCII code, which are often found at the start of data that has been imported into Excel from other applications.
Syntax: CLEAN (text)
The 'text' argument can be supplied to the function either directly, as a text string returned from another formula, or a reference to a cell containing text.
Examples
CONCATENATE– joins together a series of supplied text strings or other values, into one combined text string.
Syntax: CONCATENATE (text1, [text2], )
The text arguments are a set of one or more text strings or other values that you want to join together.
Examples
Formulas:
Results:
TIP! The operator & (ampersand) performs the same operation as CONCATENATE function, following this syntax: = A2&” ”&B2
LEN – returns the length of a supplied text string.
Syntax:LEN (text) where the text argument is the text string that you want to find the length of.
Examples
Formulas Results
LEFT – returns a specified number of characters from the start of a supplied text string.
Syntax:LEFT( text, [num_chars] ) The function arguments are:
text | - | The original text string |
[num_chars] | - | An optional argument that specifies the number of characters to be returned from the start of the supplied text. If omitted, the [num_chars] argument takes on the default value of 1 |
Examples
Formulas Results
MID – returns a specified number of characters from the middle of a supplied text string.
Syntax: MID (text, start_num, num_chars)
The function arguments are:
text | - | The original text string. | ||||
start_num | - | An integer that specifies the position of the first character that you want to be returned. | ||||
num_chars | - | An integer that specifies the number of characters (beginning with start_num), to be returned from the supplied text. | ||||
Examples
Formulas Results
RIGHT – returns a specified number of characters from the end of a supplied text string.
Syntax: RIGHT (text, [num_chars])
The function arguments are:
text | - | The original text string |
[num_chars] | - | An optional argument that specifies the number of characters to be returned from the end of the supplied text If omitted, the [num_chars] argument takes on the default value of 1 |
Examples
Formulas Results
UPPER – converts all characters in a supplied text string to upper case.
Syntax: UPPER (text)
The text argument is the original text string that you want to convert to upper case.
Examples
Formulas Results
LOWER – converts all characters in a supplied text string to lower case.
Syntax: LOWER (text)
The text argument is the original text string that you want to convert to lower case.
Examples
Formulas Results
TRIM – takes a supplied text string and removes any spaces, except for single spaces between words or characters.
Syntax: TRIM (text)
The 'text' argument can be supplied to the function either directly, as a text string returned from another formula, or a reference to a cell containing text.
Examples
SEARCH – returns the position of a specified character or string within a supplied text string.
Syntax: SEARCH (search_text, within_text, [start_num])
The function arguments are:
search_text | - | The character or text string that you wish to search for |
within_text | - | The text string that is to be searched |
[start_num] | - | An optional argument that specifies the position of the character from which the search should begin. If omitted, this takes on the default value of 1 (i.e. begin the search at the start of the within_text string) |
Examples
Formulas: Results:
Formulas:
Results:
The SEARCH function is not case-sensitive. If you want to perform a case-sensitive search, use the Excel FIND function instead: FIND (search_text, within_text, [start_num])
REPT – returns a supplied text string, repeated a specified number of times.
Syntax: REPT (Text, Number_times )
The arguments are:
Text | - | The string to be repeated |
Number_times | The number of times that the supplied Text is to be repeated. Note : | |
- | ||
- If Number_times is zero, the function returns an empty string - If Number_times is a decimal, it is truncated to an integer |
Examples
Formulas Results
Chapter VI. Date & Time Functions
TODAY– returns the current date.
The function has no arguments and therefore, the syntax of the function is simply
Syntax: TODAY ()
Examples
Formulas: Results:
|
|
|
| ||||||||||||
|
| ||||||||||||||
|
|
NOW – returns the current date and time.
The function has no arguments and therefore, the syntax of the function is
Syntax:NOW ()
The NOW function updates every time your Excel worksheet is refreshed, and so any cells containing the function will be continually changing.
Examples
The following example shows use of the NOW function, combined with the If function, to highlight when an appointment is due.
The above alert formula could be made more sophisticated by adding more conditions to the IF function, to highlight when the appointment time has been passed, etc.
The above appointment alert will only work if your spreadsheet is being continually updated. While the spreadsheet is unused, the contents of cell B2 will stay the same as at the last evaluation.
DATE – returns an Excel date, according to integers representing a year, month and day.
Syntax: DATE (year, month, day)
The function arguments are integer values, representing the year, month and day of the date to be returned.
Examples
Formulas: Results:
TIME – accepts three integer arguments representing hours, minutes and seconds, and returns an Excel time.
Syntax: TIME (hour, minute, second)
The hour, minute and second arguments are integer values that can be supplied to the function either directly, as values returned from other formulas, or as references to cells containing integers.
Examples
Formulas: Results:
|
|
|
|
|
|
|
|
|
| ||||||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||||||||||||||
|
|
DATEVALUE – converts a text representation of a date, into an Excel date.
Syntax:DATEVALUE( date_text )
The date_text argument is a text string representing a date.
Note that the Datevalue function follows simple rules that are the same as if you type a date directly into a cell. These are:
If just a date and month are provided, the date returned will have the current year
The default settings for the year are that one- and two-digit years are interpreted as follows:
- The numbers 0 through to 29 are interpreted as the years 2000 to 2029
- The numbers 30 through to 99 are interpreted as the years 1930 to 1999
Examples
These rules are illustrated in the Datavalue examples below:
Formulas: Results:
– returns a date that is a supplied number of working days (excluding weekends and holidays) ahead of a given start date. The function allows the user to specify which days are counted as weekends.
Syntax: ( start_date, days, [weekend], [holidays] )
Where the arguments are as follows:
start_date | - | The initial date, from which to count the number of workdays | |||
days | - | The number of workdays to add onto start_date | |||
[weekend] | - | An optional argument, which specifies which weekdays should be counted as weekends. This can be either a number or a string. These are explained below: | |||
Possible number values for the | Possible string values for the | ||||
[weekend] argument are: | [weekend] argument consist of a series of seven 0's and 1's which represent the seven weekdays, starting from Monday. Each 1 denotes a day that should be counted as a weekend and each 0 represents a working day. For example, | |||||||||
[weekend] | days counted as weekend | |||||||||
1 (or Sat & Sun omitted) 2 Sun & Mon 3 Mon & Tue | ||||||||||
0000100 - | denotes Fridays only counted as weekend days denotes Thursdays and Fridays counted as weekend days denotes Fridays, Saturdays and Sundays counted as weekend days | |||||||||
0001100 - | ||||||||||
4 Tue & Wed | ||||||||||
5 Wed & Thu 6 Thu & Fri 7 Fri & Sat | ||||||||||
0000111 - | ||||||||||
The string "1111111" is not valid. | ||||||||||
11 | Sunday only | |||||||||
12 | Monday only | |||||||||
13 | Tuesday only | |||||||||
14 | Wednesday only | |||||||||
15 | Thursday only | |||||||||
16 Friday only | ||||||||||
17 | Saturday only | |||||||||
[holidays] | - | An optional argument, which specifies an array of dates (in addition to weekends) that are not to be counted as working days | ||||||||
Note that the start_date and [holidays] arguments should be input as either:
• References to cells containing dates, or
• Dates returned from formulas
If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.
Examples
Formulas:
Results:
• In the example in cell D2 the holidays array has been omitted. Therefore the calculation excludes Saturdays and Sundays but includes all other weekdays, including the holidays at Christmas and New Year.
• In the example in cell D3 the [weekend] argument is 1 (specifying weekends on Saturdays and Sundays) and the holidays array (in cells B2 - B4) is provided to the function. Therefore the calculation excludes Saturdays and Sundaysand the listed Christmas and New Year holidays.
• In the example in cell D4 Fridays, Saturdays and Sundays are specified as weekends, but no holiday array has been supplied to the function. Therefore the calculation excludes Fridays, Saturdays and Sundays but includes all other weekdays, including the holidays at Christmas and New Year.
Note also that, as recommended by Microsoft, in all three calls to the function, the start_date and [holidays] arguments have been supplied as cell references.
Common Errors - Function
#NUM! - Occurs if either:
the supplied start_date plus the supplied days argument results in an
-
invalid date
or
the supplied [weekend] argument is invalid (see above explanation of
-
this argument)
#VALUE! - Occurs if either:
the supplied start_date or any of the values in the supplied [holidays]
-
array are not valid dates
or
- the supplied days argument is non-numeric
DAYS360 – returns the number of days between 2 dates, based on a 360-day year (12 x 30 months).
Syntax:DAYS360 ( Start_date, End_date, [Method] ) The arguments are as follows:
Start_date - | The start of the period |
End_date - | The end of the period |
[Method] - | An optional logical argument, which gives the method to be used in the calculation. This can be either: FALSE (or omitted) - US (NASD) method used or TRUE - European method used |
US vs. European Method
US Method:
? If start date is last day of month it is set to 30th of that month ? If end date is last day of month, then:
§ If start date is last day of month the end date is set to the 1st of the following month § Otherwise, the end date is set to 30th of that month European Method:
• If start date is last day of month it is set to 30th of that month
• If end date is last day of month, then it is set to 30th of that month
Note that the Start_date and End_date arguments should be input as either:
• References to cells containing dates, or
• Dates returned from formulas
If you attempt to input the Start_date and End_date as text, there is a chance that Excel may misinterpret this due to different date systems, or date interpretation settings.
Warning:Although you can input the dates as serial numbers, this is not recommended as the serial numbers may vary across different computer systems.
Examples
FormulasResults
In the above example:
• In the function is cells B1 and B4 the Method argument has been omitted. Therefore, the US (NASD) method is used.
• In cells B2, the Method is set to TRUE, so the European method is used. Note that this gives a different result from the function in cell B1, which uses the same two dates with the US (NASD) method.
Note also that, as recommended by Microsoft, in all four calls to the Days360 function, the Start_date and End_date arguments have been supplied as either cell references or the return values from functions.
Chapter VII. Data Types & Data Conversion
Data Types
There are 3 general types of data that can be stored in a cell:
• text
• a logical value
• a number
Text values will always be represented as simple text.
Logical values are displayed as either TRUE or FALSE.
However, numbers can be displayed in many different ways in an Excel Cell. As illustrated above, a numeric value may represent a date, a time, a percentage, a currency, etc.
This is illustrated by the spreadsheet below, which shows different ways of formatting Excel cells containing numeric values.
A | B | C | |
1 | Formatted Value | Underlying Value | |
2 | A number formatted as a percentage: | 59% | 0.59 |
3 | A number formatted as a currency: | $54.27 | 54.27 |
4 | A number formatted as a date: | 01 August 2008 | 39661 |
5 | A number formatted as a time: | 12:27 PM | 0.51875 |
5 | A number formatted as a fraction: | 1 3/4 | 1.75 |
How Excel aligns the data (by default — it can be changed) depends on the type of data:
• Text: Excel aligns text to the left side of the cell. If the text is too wide to fit, Excel extends that data past the cell width if the next cell is blank. If the next cell is not blank, Excel displays only enough text to fit the cell width. Widening the column displays additional text.
• Number: If the data is a whole value, such as 34 or 5763, Excel aligns the data to the right side of the cell.
TIP! If a value displays as scientific notation (such as 1.2345E+11) or number signs (######), it means the value is too long to fit into the cell. You need to widen the column.
• Date: If you enter a date, such 12/16, Dec 16, or 16 Dec, Excel automatically returns 16Dec in the cell, but the Formula bar displays the entire date.
Data Conversion
More specifically, the data entered in a cell can take as many forms as listed in Format Cells tab: General, Number, Currency, Accounting, Date, Time, Percentage, Text etc.
To change the type of data entered in a cell, you can right click the cell, go to Format Cells, and then select the most appropriate format from the list:
You may find these features summarized, under Number section, in the Home tab Ribbon:
Here you have shortcuts for Currency, Percentage and Accounting types, as well as for increasing or decreasing the number of decimals.
From the dropdown list you can select the type you want:
Functions to Convert Excel Data Types
BAHTTEXT – converts a number into Thai text, with the suffix "Baht".
Syntax: BAHTTEXT (number)
Examples
Formulas: Results
DOLLAR – rounds a supplied number to a specified number of decimal places and then converts this into text, using a currency format.
Syntax: DOLLAR (Number, [Decimals])
Where the arguments are:
Number | - | The number to be converted into a text string |
[Decimals] | - | An optional numerical argument which specifies the number of decimal places to be shown after the decimal point -If Decimals is omitted, it takes on the default value of 2 If Decimals is negative, the supplied Number is rounded up to the left - of the decimal point If Decimals is a decimal (rather than an integer), it is truncated to an - integer |
Examples
FormulasResults
Note that the Excel Dollar function uses the currency that is set as the default on your computer. The above examples use UK pounds sterling (£).
FIXED – rounds a supplied number to a specified number of decimal places and then converts this into text.
Syntax: FIXED (Number, [Decimals], [No_commas]) Where the arguments are:
Number - | The number to be converted into a text string An optional numerical argument which specifies the number of decimal places to be shown after the decimal point Note : -If Decimals is omitted, it takes on the default value of 2 If Decimals is negative, the supplied Number is rounded up to the left of the decimal point If Decimals is a decimal (rather than an integer), it is - truncated to an integer |
[Decimals] - | |
An optional logical argument which specifies if the returned text should separate thousands by commas. Possible values are : TRUE - Commas are not included in the resulting text FALSE - Commas are included in the resulting text If the [No_commas] argument is omitted, it takes on the default value of FALSE. | |
[No_commas] - |
Examples
FormulasResults
TIP! Once a number has been converted, using the DOLLAR and FIXED functions, it is stored in Excel as text. Therefore, it cannot be used in numeric calculations. If you want to be able to use the numbers in calculations, you may prefer to keep the values as numbers in Excel and just change the formatting into the required numeric format.
TEXT – converts a supplied value into text, in a user-specified format.
Syntax: TEXT (value, format_text) The function arguments are:
value | - | A numeric value, that you want to be converted into text | |||
format_text | - | A text string that defines the formatting that you want to be applied to the supplied value. The format definitions that can be used in the Excel Text function are shown in | |||
the table below: | |||||
0 - Forces the display of a digit in its place | |||||
# - Display digit if it adds to the accuracy of the number (but don't display if a leading zero or a zero at the end of a decimal) | |||||
. - Defines the position that the decimal place takes | |||||
d - Day of the month or day of week d = one or two digit representation (eg. 1, 12) dd = 2 digit representation (eg. 01, 12) ddd = abbreviated day of week (eg. Mon, Tue) dddd = full name of day of week (eg. Monday, Tuesday) | |||||
m - Month (when used as part of a date) m = one or two digit representation (eg. 1, 12) mm = two digit representation (eg. 01, 12) mmm = abbreviated month name (eg. Jan, Dec) mmmm = full name of month (eg. January, December) | |||||
y - Year yy = 2-digit representation of year(eg. 99, 08) yyyy = 4-digit representation of year(eg. 1999, 2008) | |||||
h - Hour h = one or two digit representation (eg. 1, 20) h = two digit representation (eg. 01, 20) | |||||
m - Minute (when used as a part of a time) m = one or two digit representation (eg. 1, 55) m = two digit representation (eg. 01, 55) | |||||
s - Second s = one or two digit representation (eg. 1, 45) ss = two digit representation (eg. 01, 45) | |||||
AM/PM - Indicates that a time should be represented using a 12-hour clock, followed by "AM" or "PM" |
Examples
Formulas: Results:
Wildcard Characters
The wildcards can be used when you work with functions that take text arguments and you may need to replace some characters.
The wildcards are:
? - matches any single character
* - matches any sequence of characters
~ - if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.
Wildcards are commonly used in decisional and lookup functions where arguments are textrelated criteria: MATCH, IF, SUMIF, SUMIFS, COUNTIF etc.
MATCH Function Example
Chapter VIII. Working with 2d data (tables)
If you are working on a spreadsheet and want to be able to quickly see differences in numbers at a glance, you can use the new Conditional Cell Formatting feature. With this you can change the background of a cell based on the data in the cell, almost like an inline chart.
Once you select the cells that you want to format, you can find the Conditional Formatting drop-down on the Home ribbon, which gives you loads of pre-set rules to choose from, like color scales.
You can also choose to use icons instead:
You can create your own rule to color only cells that fit within a certain range, or greater than a certain number.
This rule will only color cells greater than a certain amount, with the color you specify.
This can be very useful, and you should note that you can apply a second rule to the same range. For instance, I added a second rule that colored items larger than 2000 with green instead of red.
You can even create a completely customized rule by choosing the New Formatting Rule option.
A second option, Format only cells that contain. This will allow you to set up your values. When you click this option, the dialogue box changes to this:
The part you're interested in is the bottom part, under the heading Edit the Rule Description. It says Cell Value and Between, in the drop down only need to type a value for the two boxes that are currently blank in the image above. You can then click the Format button to choose a color.
So type 0 in the first box and 50 in the second one:
Then click the Format button. You'll get another dialogue box popping up. This is just the Format Cells one though. Click on the Fill tab and choose a color. Click OK and you should see something like this under Edit the Rule Description:
The Preview is showing the color you picked. So you've said, "If the Cell Value is between 0 and 50 then color the cell Red".
Click OK on this dialogue box to get back to Excel. You should find that one of the cells has turned red. To format the rest of the cells, click on Conditional Formatting on the Styles panel again. From the menu, click on Manage Rules:
You'll get yet another complex dialogue box popping up! This one:
Your first rule is already there - Cell Value Between.
Now you can add New Rules, similar to the one you've just set up. Click the New Rule button then. You'll see the exact same dialogue boxes you used to set up the first rule. Set a new color for the next scores - 51 to 60. Choose a color, and keep clicking OK until you get back to the Rules Manager dialogue box. It should now look something like this one:
In conclusion you can set up rules that will color an excel files like this:
Formatting your spreadsheet in this way allows you to see at a glance relevant information. In the spreadsheet above, it's obvious who's failing - just look for the red cells!
If you’ve applied too many rules and want to start over, just select the cells and use the Clear Rules option to remove all the formatting.
This is one of those really useful features that make Office 2007 & 2010 a lot better than the previous versions.
Excel Pivot Tables are tables that are produced by Excel, to summaries large amounts of data in a spreadsheet. This is best explained by an example.
This is how you can create a Pivot Table in Excel 2007 or Excel 2010, using an example spreadsheet of company sales figures during 2010:
For this advanced pivot table example we will create a pivot table that shows the total sales for each month of the year, broken down by sales region and sales rep.
The process for creating this Pivot Table is described below:
1. Select any cell within the data range or select the entire data range to be used in your Pivot Table.
Note: If you select a single cell in the data range, Excel will automatically identify, and select the whole data range for your Pivot Table. In order for Excel to successfully do this, the following must be satisfied:
- Each column in the data range has a header and
- The data does not contain any blank rows
2. Click on the Pivot Table button, which is generally located on the left, within the 'Insert' tab.
3. You will be presented with the window entitled 'Create PivotTable' (shown on the right).
Make sure that the selected range is the range that you want to use for your Pivot Table.
There is also an option asking where you want the Pivot Table to be placed. If you are not sure, select the option 'New worksheet'
Click OK
4. Excel will present you with an empty Pivot Table, and a 'Pivot Table Field List', which contains the data fields for your Pivot Table. Note that these are the headers from your data spreadsheet.
We want the Pivot Table to show the sums of the sales figures for each month, broken down by region and sales rep.
Therefore, from the 'Pivot Table Field List':
• Drag the 'Date' field into the area marked 'Row Labels'
• Drag the 'Amount' field into the area marked '? Values'
• Drag the 'Region' field into the area marked 'Row Labels'
• Drag the 'Sales Rep.' field into the
area marked 'Row Labels'
TIP! Going to Options > Options > Display > Classic PivotTable layout, this enables the dragging of the fields directly to the pivot.
The values field can be configured, we left click on the ?Sum of Amount? go to Value Field Settings… and select the needed calculation in Summarize value field by (example give, to calculate sum,
.
5. The resulting Pivot Table (shown on the right) will be populated with the daily sales totals for each sales region and each sales rep.
However, we want to group the dates bymonth. To do this:
• Right click on any of the dates in the left hand column of the Pivot Table ? Select the option Group
• A window will pop up. Select the
option Months
(note that you can also group dates and times by other time periods, such as quarters, days, hours, etc)
• Click OK
The resulting Pivot Table is shown below:
As required, the final pivot table shows the total monthly sales, broken down by sales region and sales rep.
The Pivot Table might be improved visually, by formatting. For example, if columns B - G are formatted as currencies, this will make the Pivot Table easier to read. This has been done in the spreadsheet used in the following section (below).
Pivot Table Page Field
The Pivot Table page field allows you to view the data for a single value in one of your data fields. For example, in the pivot table above, you could view just the data for the North sales region or just the data for the South sales region. This is illustrated below.
In order to view just the data for the 'North' sales region, return to the 'Pivot Table Field List', and drag the 'Region' field header into the page drop area, as shown below.
You will see that the 'Region' field is now located in the page drop area, at the top left of the Pivot Table. Use the drop-down list for this field to select the Region 'North'. The resulting Pivot Table, which shows just the sales for the North region, is shown on the right.
You can also quickly view just the sales for the South region by selecting 'South' from the drop down menu.
When working with very large spreadsheets headings located at the top and down the left side of the worksheet often disappear if you scroll too far to the right or down. Without the headings, it's hard to keep track of which column or row of data you are looking at.
To avoid this problem use Excel's freeze panes feature. It "freezes" or locks specific columns or rows of the spreadsheet so that they remain visible at all times.
Freeze Panes Using the Active Cell
The Freeze Panes option freezes all the rowsabove the active celland all the columnsto the left of the active cell.
To freeze only those columns and rowsyou want to stay on screen, click on the cell to the right of the columns and just below the rows that you want to remain on screen.
Example
To keep rows 1, 2, and 3 on the screen and columns A and B:
a. Click in cell C4 with the mouse to make it the active cell.
b. Click on the View tab.
c. Click on Freeze Panes from the ribbonto open the features drop down list.
d. Click on Freeze Panes option in the list.
e. A black border should appear to the right of column B in the worksheetand below row 3 indicating that the areas above and to the right of the lines have been "frozen".
f. Scroll to the right in the worksheet. If you scroll far enough, the columns to the right of column B will begin disappearing while columns A and B will stay on the screen.
g. Scroll down through the worksheet. If you scroll far enough, the rows below row 3 will begin disappearing while rows 1, 2, and 3 will stay on the screen.
Additional options can be found under Click on the View tab > Freeze Panes:
- Freeze top row and
- Freeze first column
To Unfreeze Columns and Rows
a. Click on the View tab.
b. Click on Freeze Panes from the ribbonto open the features drop down list.
c. Click on Unfreeze Panes option in the list.
d. The black border(s) showing the frozen columns and rows should disappear from the worksheet.
Using AutoFilter to filter data is a quick and easy way to find and work with a subset of data in a range of cells or table. Once you have filtered data in a range of cells or table, you can either succumb reapply a filter to get up-to-date results, or clear a filter to redisplay all of the data.
Filtered data displays only the rows that meet criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) that you specify and hides rows that you do not want displayed. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data without rearranging or moving it.
You can also filter by more than one column. Filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data.
When you use the Find dialog box to search filtered data, only the data that is displayed is searched; data that is not displayed is not searched. To search all the data, clear all filters.
Types of filters
Using AutoFilter, you can create three types of filters:
• by a list values,
• by a format
• by a given criteria.
Each of these filter types is mutually exclusive for each range of cells or column table. For example, you can filter by cell color or by a list of numbers, but not by both; you can filter by icon or by a custom filter, but not by both.
To determine if a filter is applied, note the icon in the column heading:
• A drop-down arrow means that filtering is enabled but not applied.
TIP! When you hover over the heading of a column with filtering enabled but not applied, a screen tip displays "(Showing All)".
• A Filter button means that a filter is applied.
TIP! When you hover over the heading of a filtered column, a screen tip displays the filter applied to that column, such as "Equals a red cell color" or "Larger than 150".
When you reapply a filter, different results appear for the following reasons:
• Data has been added, modified, or deleted to the range of cells or table column.
• The filter is a dynamic date and time filter, such as Today, This Week, or Year to Date.
• Values returned by a formula have changed and the worksheet has been recalculated.
TIP! For best results, do not mix storage formats, such as text and number or number and date, in the same column. If there is a mix of storage formats, the command that is displayed is the storage format that occurs the most. For example, if the column contains 3 values stored as number and 4 as text, the filter command that is displayed is Text Filters.
Apply a Filter
To apply a filter, you can select the top cell of a rage or top row of the table you want to filter, and do one of the following:
1. On the Home tab, in the Editing group, click Sort & Filter, and then Filter
Or
2. Press Ctrl+Shift+L (This is a very useful shotcut that applies and clears a filter over selected cells.)
According to the data stored in a column, when you click on the arrow in the column header, the specific options to Sort & Filter your data will display.
Filter Texts
You can do one of the following:
Select from a list of text values
• In the list of text values, select or clear one or more text values to filter by.
The list of text values can be up to 10,000. If the list is large, clear (Select All) at the top, and then select the specific text values to filter by.
To filter for blanks of nonblanks:
• To filter for nonblanks, in the AutoFilter menu at the top of the list of values, select (Select All), and then at the bottom of the list of values, clear (Blanks).
• To filter for blanks, in the AutoFilter menu at the top of the list of values, clear (Select All), and then at the bottom of the list of values, select (Blanks).
The (Blanks) check box is available only if the range of cells or table column contains at least one blank cell.
Create criteria
1. Point to Text Filters and then click one of the comparison operator commands, or click Custom AutoFilter.
For example, to filter by text that begins with a specific character, select Begins With, or to filter by text that has specific characters anywhere in the text, select Contains.
2. In the Custom AutoFilter dialog box, in the box on the right, enter text or select the text value from the list.
For example, to filter by text that begins with the letter "J", enter J, or to filter by text that has "bell" anywhere in the text, enter bell. If you need to find text that shares some characters but not others, use a wildcard character.
3. Optionally, you can filter by one more criteria:
• To filter the table column or selection so that both criteria must be true, select And.
• To filter the table column or selection so that either or both criteria can be true, select Or.
Filter numbers
Select from a list of numbers
? In the list of numbers, select or clear one or more numbers to filter by.
The list of numbers can be up to 10,000. If the list is large, clear (Select All) at the top, and then select the specific numbers to filter by.
Create criteria
1. Point to Number Filters and then click one of the comparison operator commands or click Custom Filter.
For example, to filter by a lower and upper number limit, select Between.
2. In the Custom AutoFilter dialog box, in the box or boxes on the right, enter numbers or select numbers from the list.
For example, to filter by a lower number of 25 and an upper number of 50, enter 25 and 50.
Optionally, you can add one more criteria:
• To filter the table column or selection so that both criteria must be true, select And.
• To filter the table column or selection so that either or both criteria can be true, select Or.
2. In the second entry, select a comparison operator, and then in the box on the right, enter a number or select a number from the list.
Filter dates or times
Select from a list of dates or times
? In the list of dates or times, select or clear one or more dates or times to filter by.
By default, all dates in the range of cells or table column are grouped by a hierarchy of years, months, and days. Selecting or clearing a higher level in the hierarchy selects or clears all nested dates below that level. For example, if you select 2006, months are listed below 2006, and days are listed below each month.
The list of values can be up to 10,000. If the list of values is large, clear (Select All) at the top, and then select the values to filter by.
Create criteria
1. Point to Date Filters and then do one of the following:
Common filter – A common filter is one based on a comparison operator.
1. Click one of the comparison operator commands (Equals, Before, After, or Between) or click Custom Filter, for more specific options.
Dynamic filter – The criteria can change when you reapply the filter.
1. Click one of the pre-defined date commands, and then click OK.
For example, to filter all dates by the current date, select Today, or by the following month, select Next Month.
Notes
• The commands under the All Dates in the Period menu, such as January or Quarter 2, filter by the period no matter what the year. This can be useful, for example, to compare sales by a period across several years.
• This Year and Year to Date are different in the way that future dates are handled. This Year can return dates in the future for the current year, whereas Year to Date only returns dates up to and including the current date.
When applying date filters, you have to take into consideration the following aspects:
• All date filters are based on the Gregorian calendar.
• Fiscal years and fiscal quarters always start in January of the calendar year.
• If you want to filter by days of the week, format the cells to show the day of the week. If you want to filter by the day of the week regardless of the date, convert them to text by using the TEXT function. However, the TEXT function returns a text value, and so the filter command that is displayed would be Text Filters, not Date Filters.
• For the list of dates at the bottom of the AutoFilter menu in a date filter, you can change the hierarchical grouping of dates to a nonhierarchical list of dates. For example, you can filter for just two-digit years by manually selecting them from a nonhierarchical list.
1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
2. In the Display options for this workbook section, select a workbook, and then clear the Group dates in the AutoFilter menu check box.
Filter by cell color, font color, or icon set
If you have manually or conditionally formatted a range of cells, by cell color or font color, you can also filter by these colors. You can also filter by an icon set created through a conditional format.
? Make sure that the table column contains data formatted by cell color, font color, or an icon set (No selection is required).
Select Filter by Color, and then depending on the type of format, select Filter by Cell Color, Filter by Font Color, or Filter by Cell Icon.
Filter by selection
You can quickly filter data with criteria that is equal to the contents of the active cell.
1. In a range of cells or table column, right click a cell containing the value, color, font color, or icon you want to filter by.
2. Click Filter, and then do one of the following:
• To filter by text, number, or date or time, click Filter by Selected Cell's Value.
• To filter by cell color, click Filter by Selected Cell's Color.
• To filter by font color, click Filter by Selected Cell's Font Color.
• To filter by icon, click Filter by Selected Cell's Icon.
Reapply a Filter
To reapply a filter after you change the data, click a cell in the range or table, and then on the Home tab, in the Editing group, click Sort & Filter and select Reapply.
Clear a Filter
You can clear a filter for a specific column or clear all filters.
Clear a filter for a column
? To clear a filter for one column in a multicolumn range of cells or table, click the
Filter button on the heading, and then click Clear Filter from <Column Name>.
Clear all filters in a worksheet and redisplay all rows
? On the Data tab, in the Sort & Filter group, click Clear.
You can hide any worksheet in a workbook to remove it from view. You can also hide the workbook window of a workbook to remove it from your workspace. The data in hidden worksheets and workbook windows is not visible, but it can still be referenced from other worksheets and workbooks. You can display hidden worksheets or workbook windows as needed.
By default, all workbook windows of workbooks that you open are displayed on the taskbar, but you can hide or display them on the taskbar as needed.
Hide a worksheet
1. Select the worksheets that you want to hide.
To select | Do this |
A single sheet | Click the sheet tab. If you don't see the tab that you want, click the tab scrolling buttons to display the tab, and then click the tab. |
Two or more adjacent sheets | Click the tab for the first sheet. Then hold down SHIFT while you click the tab for the last sheet that you want to select. |
Two or more | Click the tab for the first sheet. Then hold down CTRL while you click the |
nonadjacent sheets tabs of the other sheets that you want to select.
All sheets in a workbook | Right-click a sheet tab, and then click Select All Sheets on the shortcut menu. |
2. On the Home tab, in the Cells group, click Format.
3. Under Visibility, click Hide & Unhide, and then click Hide Sheet.
Display a hidden worksheet
1. On the Home tab, in the Cells group, click Format.
2. Under Visibility, click Hide & Unhide, and then click Unhide Sheet.
3. In the Unhide sheet box, double-click the name of the hidden sheet that you want to display.
Note You can unhide only one worksheet at a time.
Note If worksheets are hidden by Visual Basic for Applications (VBA) code that assigns the property xlSheetVeryHidden, you cannot use the Unhide command to display those hidden sheets. If you are using a workbook that contains VBA macros and you encounter problems when working with hidden worksheets, contact the owner of the workbook for more information.
Hide a workbook window
? On the View tab, in the Window group, click Hide.
Note When you exit Excel, you will be asked if you want to save changes to the hidden workbook window. Click Yes if you want the workbook window to be hidden the next time that you open the workbook.
Display a hidden workbook window
1. On the View tab, in the Window group, click Unhide.
Note If Unhide is unavailable, the workbook does not contain hidden workbook windows.
2. Under Unhide workbook, double-click the workbook window that you want to display.
Hide or display workbook windows on the Windows taskbar
1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
2. Under Display, clear or select the Show all windows in the Taskbar check box.
Chapter IX. Error Correction
Correct common errors when entering formulas
The following table summarizes some of the most common errors that a user can make when entering a formula, and explains how to correct those errors:
MAKE SURE THAT YOU… | MORE INFORMATION | |||
Start every function with the equal sign (=) | If you omit the equal sign, what you type may be displayed as text or as a date. For example, if you type SUM(A1:A10), Microsoft Office Excel displays the text string SUM(A1:A10) and does not perform the calculation. If you type 11/2, Excel displays the date 2- Nov (assuming the cell format is General) instead of dividing 11 by 2. | |||
Match all open and close parentheses | Make sure that all parentheses are part of a matching pair (opening and closing). When you use a function in a formula, it is important for each parenthesis to be in its correct position for the function to work correctly. For example, the formula =IF(B5<0),"Not valid",B5*1.05)will not work because there are two closing parentheses and only one open parenthesis (there should be only one opening and one closing parenthesis). The formula should look like this: =IF(B5<0,"Not valid",B5*1.05). | |||
Use a colon to indicate When you refer to a range of cells, use a colon (:) to separate the a range reference to the first cell in the range and the reference to the last cell in the range. For example, A1:A5.
Enter all required arguments | Some functions have required arguments. Also, make sure that you have not entered too many arguments. | ||
Enter the correct type of arguments | Some functions, such as SUM, require numerical arguments. Other functions, such as REPLACE, require a text value for at least one of their arguments. If you use the wrong type of data as an argument, Excel may return unexpected results or display an error. | ||
Nest no more than 64 functions | You can enter, or nest, no more than 64 levels of functions within a function. | ||
Enclose other sheet names in single quotation marks | If the formula refers to values or cells on other worksheets or workbooks, and the name of the other workbook or worksheet contains a nonalphabetical character, you must enclose its name | ||
within single quotation marks ( ' ).
Place an exclamation point (!) after a worksheet name when you refer to it in a formula | For example, to return the value from cell D3 in a worksheet named Quarterly Data in the same workbook, use this formula: ='Quarterly Data'!D3. |
Include the path to Make sure that each external reference contains a workbook name external workbooks and the path to the workbook.
A reference to a workbook includes the name of the workbook and must be enclosed in brackets ([]). The reference must also contain the name of the worksheet in the workbook.
For example, to include a reference to cells A1 through A8 on the worksheet named Sales in the workbook (that is currently open in Excel) named Q2 , the formula looks like this: =[Q2 ]Sales!A1:A8.
If the workbook that you want to refer to is not open in Excel, you can still include a reference to it in a formula. You provide the full path to the file, such as in the following example: =ROWS('C:\My Documents\[Q2 ]Sales'!A1:A8). This formula returns the number of rows in the range that includes cells A1 through A8 in the other workbook (8).
NOTE If the full path contains space characters, as does the preceding example, you must enclose the path in single quotation marks (at the beginning of the path and after the name of the worksheet, before the exclamation point).
Enter numbers without formatting | Do not format numbers when you enter them in formulas. For example, if the value that you want to enter is $1,000, enter 1000 in the formula. If you enter a comma as part of a number, Excel treats it as a separator character. If you want numbers displayed so that they show thousands or millions separators, or currency symbols, format the cells after you enter the numbers. For example, if you want to add 3100 to the value in cell A3, and you enter the formula =SUM(3,100,A3), Excel adds the numbers 3 and 100 and then adds that total to the value from A3, instead of adding 3100 to A3. Or, if you enter the formula =ABS(-2,134), Excel displays an error because the ABS function accepts only one argument. |
Correct an error value
If a formula cannot correctly evaluate a result, Excel displays one of the below error values. Each error type has different causes, and different solutions.
ERROR | DESCRIPTION |
##### | Excel displays this error when a column is not wide enough to display all the characters in a cell, or a cell contains negative date or time values. For example, a formula that subtracts a date in the future from a date in the past, such as =06/15/2008-07/01/2008, results in a negative date value. |
#DIV/0! | Excel displays this error when a number is divided either by zero (0) or by a cell that contains no value. |
#N/A | Excel displays this error when a value is not available to a function or formula. |
#NAME? | This error is displayed when Excel does not recognize text in a formula. For example, a range name or the name of a function may be spelled incorrectly. |
#NULL! | Excel displays this error when you specify an intersection of two areas that do not intersect (cross). The intersection operator is a space character that separates references in a formula. For example, the areas A1:A2 and C3:C5 do not intersect, so entering the formula =SUM(A1:A2 C3:C5) returns the #NULL! error. |
#NUM! Excel displays this error when a formula or function contains invalid numeric values. |
#REF! Excel displays this error when a cell reference is not valid. For example, you may have deleted cells that were referred to by other formulas, or you may have pasted cells that you moved on top of cells that were referred to by other formulas.
Excel can display this error if your formula includes cells that contain different data
#VALUE! types. If smart tags are turned on and you position the mouse pointer over the smart tag, the ScreenTip displays "A value used in the formula is of the wrong data type." You can typically fix this problem by making minor changes to your formula.
Part
How to…
How to check HP Roles on a profiling report
Supposing that you have to check if your contacts are flagged as Admin and IPG Contact for MDF communication, or if your GMs have the IPG Contact flag as well, to be correctly counted in the reports, you can do this:
1. Pull a report from BOSS, with all the partners in your country and the necessary details:
3. Return to the original table, delete the HP role column, select the Contact Id column, and remove the duplicates: Data > Data Tools > Remove Duplicates, and Click Ok to Expand Selection.
4. Normally, now you should only have unique contacts in the list. To check this, select the Contact Id column once again, and go to Conditional formatting > Highlight Cells Rules > Duplicate Values. All the duplicates will be colored:
You must check and eliminate these as well, to have all unique Contact Ids in the table, and then you sort the column A – Z.
5. Go to the pivot, sort the Contact Id column A – Z as well, then copy the entire pivot and paste it at the right of the initial table:
6. To check that the contact Ids correspond, type =I2=E2 in the next available Column, and copy the formula down to the last row. All rows should return TRUE.
7. Now you can delete all the unnecessary columns, refresh the filter (select the top row and press Ctrl+Shift+L twice), and you finally have the report you need.
8. For MDF checking, you select 1 on Admin Contacts and 1 on IPG Contacts.
9. For GM checking, well… you get the point! ;)
How to create a budget upload from a Marketing Plan
If you need to know the total amount per partner, in a Marketing Plan, either for upload and check against the Budget Upload Sheet, you can easily create a pivot:
1. Select all the cells you need from the Marketing Plan
2. Create a pivot (Insert > Pivot >Pivot Table), with the following selection:
3. Your Pivot will now have all the information that you need:
You’ll have the total amount/partner, that total amount/fund, and split into MSA Led/
Partner Led.
How to check the Partner Led/MSA Led correspondence in a Marketing Plan
To make sure that the selected fund type at the beginning also reflects in the Fund
Name and Name of Marketing plan, you can easily use this trick:
1.Hide all the unnecessary columns form the Plan, keeping only those you need:
2.Then, in the next column available, just type the function =SEARCH(J5,AC5) and copy it until the last row of the plan.
SEARCH returns the position of the text you are searching for, within another text. So, if it returns a valid number, the fund types are the same. Otherwise, it will return an error. Also, SEARCH is not case-sensitive, so it will recognize
?Partner led? in ?Partner Led? as well.
3.If you want to do it more specific, you can use the function
=IFERROR(SEARCH(J5;AC5);"Wrong Name"). It will look like this:
How to check for blank cells in a Marketing Plan
To check if there are blank cells in the Plan (a common rejection reason from MIF), you can insert, at the end of the first column (cell E57 in this case), the function =COUNTBLANK(E5:E56) and copy it until the last column of the plan:
This way you can easily identify if there are any blank cells in the mandatory columns for upload, like the Justification column:
How to check the values correspondence in a Marketing Plan
To check the correctness and correspondence of the amounts in the plan, you just have to compare them, creating simple equations:
1. Hide all the unnecessary columns, keeping only those you need
2. In the next free column, type =W5=X5+Y5 and copy it until the last row of the plan. The function will return TRUE for every Total Activity Cost that equals the sum between HP Contribution and Partner Contribution. FALSE will indicate you an error in the plan:
3. A similar comparison can be used to check the HP contribution % in the Total Cost (=Z5=X5/W5), or if the Allocated Budget = HP contribution (=AG5=X5).
How to create Variable Dropdown Lists
Suppose you have a list of members of staff, split into teams. You might want to select a team from a drop down list, and then be able to select a member of that team from a second drop down list.
In order to do this, you need to begin with a list of members of each team, as shown below:
You also need to create a range of cells that will show the team members for any selected team. In the case of the example above, the team name could be typed into cell G2, and then a formula in cells E2-E6 could return the members of the selected team.
In the example above, the following formula, which combines the Hlookup and the Row functions, would produce the names of the team members for any team name entered into cell G2:
=HLOOKUP($G$2, $A$1:$C$6, ROW(), 0)
This is shown in the spreadsheet below.
In the above spreadsheet the formula "=$G$2" is used to reflect the selected team name in cell E1 and the Hlookup formula in cells E2-E6 is used to display the members of the selected team.
A drop down list that takes its possible values from cells E2-E6 will therefore also depend on the value of cell G2.
Now just create a drop-down list in cell G4, using Data_Validation feature. This result is shown below.
To make your spreadsheet look that bit more professional, it is a good idea to hide columns A-C, which contain the original data, or place this data onto a different worksheet.
A further finishing touch could be to add a further drop-down list to cell G2, from which a team name can be selected. This therefore means that your final sheet has a drop-down list, that varies, depending on the selection from another drop-down list.
How to search for many activity IDs in a Claim Form Report
If you have to search for MSA Claim Forms only for the some given approved activities during the quarter, you can do this:
1. Go to the Claim Forms file, to the right quarter spreadsheet, and select your Organization.
2. Copy the IDs from the source (email, file given by the requester) and paste them at the end of the Activity ID column (K) in your Spreadsheet, selecting ?Match destination formatting?, to align the format as much as possible.
3. Make sure you have no empty rows between the Claim Forms table and the copied activities, and no additional spaces in your copied cells.
§ You can check if there are additional unspotted spaces in the cell, using the function =LEN(<activity ID cell>), if the result is greater than 8, the normal length of an activity ID, then you’ll know that additional characters are given by spaces.
§ In this case, you can remove them using the function =TRIM(<activity ID cell>), to clean the cells, and apply the LEN function again, to see if now your activity IDs have 8 characters.
§ If you still have greater lengths, this may be due to spaces put at the beginning, which are not detected by TRIM, so you may use the function =RIGHT(<activity ID cell>,8).
4. Now the activity IDs will have the correct length, so you may copy them and Paste as values at the end of the K Column, deleting all the additional computing.
5. Select the whole column K, go to Conditional formatting > Highlight Cells Rules > Duplicate Values. This way the duplicates will be colored differently from the rest of the IDs. If one or more of the IDs you pasted is not colored, it means they are not on the respective sheet (they may belong to other quarters):
6. Using the filter, you can then select all the activities needed, filtering by color. You now ave the Claim Forms for all the activity IDs you need.
TIP! If you want to keep the original layout of the Claim Forms (when copied to a new book, the columns are all shrunk and cannot be changed), copy some empty rows of the original file, paste them into your new book, selecting Keep Source Column Widths, and then select and paste the Claim Forms.
How to convert text to columns
In case someone writes a mail and creates a table right there in the mail (without copy pasting it from another source), the figures will show like this:
If we try to copy this in an excel it will look like this:
As you may notice, all the text/figures have been noted under column A/line
Of course this looks rather confusing and we can’t really use this in a report, so what we can do in this case is to try and convert this back to a table:
1. We select the data
2. We copy paste it into an excel file
3. We select the data and go to Data > Text to columns. Here we have several options to choose from, in accordance to our needs. If we want excel to delimit the text by space then we leave delimited and select space at the second tab option., which can ultimately lead to a delimitation that can be further used in formula:
How to use freeze panes on a CAT report
In case we want to run a report from CAT but the resulting excel file is very big and if we scroll down we might confuse which column is assigned to what data, then we can freeze the top pane by going to View > Freeze panes > Freeze top row
In case we want to freeze several columns and rows we can go to a cell and then go to View > Freeze panes, it will then freeze all the columns and rows before the selected cell (for example if you select cell E5 it will freeze rows 1 to 5 and columns A to E)
Whenever you want to unfreeze the columns and rows you can just go to View > Unfreeze panes
Please note that you can only have one freeze panes rule on a worksheet at a time, if you want to change it you will need to unfreeze the previous one and create a new one.
How to highlight desired cells on a WOS LFM Top 50 report
We are interested in highlighting the cells greater or equal to 3 with green while the ones with less than 2 must have red.
1. We select the column that we want to place a conditional formatting rule on (in our case column ?wos?)
2. We go to Conditional Formatting > New rule
Here we have the following rule creation:
3. While having the same column selected we go to Conditional Formatting > Highlight Cells Rule > Less than …
Our table will now show:
Thus highlighting the numbers that are greater or equal to 3 with green and the ones lower than 3 with red.
We can also manage the rules by going to Conditional Formatting > Manage rules (the selected cell must be the one containing the rules).
Here you can view an overview of the rules we previously set and you can delete, edit or add new rules.
How to see the claiming status per partner on a CST report
If you need an overview on partners claiming: claims executed/in progress per partner per quarter, you must select your cells, and then from Insert Tab -> Insert Pivot Table, and select the following fields:
The final report will look like this:
CST arranges to see payments per partner (make sure the text is converted to number, otherwise it won’t be able to calculate the sum*)
*How to convert the text to number: press the first cell (e.g AN2) and press Shift + Arrow Down + End, this will select the whole column. Then go to the ! mark and select convert to number:
Part 3
Tips & Tricks
How to better write a VLOOKUP
Use named ranges A better option is to use named ranges in lookup formulas:
=VLOOKUP(valSalesPerson,tblData,3,FALSE) is much more easier to read and understand than =VLOOKUP(G5,$B$5:$G$17,3,FALSE)
Make table / list references Absolute If you still prefer to use cell references, make
sure the table references are absolute, like this: $B$5:$G$17 instead of B5:G17. Check for Errors VLOOKUP is a powerful formula, alright. But ask it to look up a value that is not the data and it acts up. So you need to handle this. The easiest method is to use IFERROR() formula. Like this: =IFERROR(VLOOKUP(…),?Oops, nothing found!?). Use 1 and 0 for last argument You can use 1 and 0 for last argument in VLOOKUP to make the formula shorter. The formula =VLOOKUP(value, range, column #, FALSE) is same as =VLOOKUP(value, range, column #, 0). Similarly you can use 1 for TRUE.
Evenmore, you can even omit the last argument if it is 0, like this: =VLOOKUP(value, range, column #, ) Remember, you must place a comma (,) after the column number if you are planning to use this.
Use VLOOKUP only when you need it Do you know that formulas like SUMIF() or
SUMPRODUCT() can effectively replace VLOOKUP() formulas? For example, the formula
=SUMIF(lookup-range, lookup value, return column range) gives same value as =VLOOKUP(lookup value, total range, 2, false)? *assuming there is only one match, return column range has numbers.
Useful Key Shortcuts
CTRL + SHIFT + L Applies the Filter to a selected Row/Removes existing Filter
ALT + ENTER When you're typing text into a cell, by default hitting Enter takes you to the next cell. However, to enter a new line in the same cell, this combination will correctly wrap your text.
F9 Calculates all worksheets. After changing references into a formula and recopying it all the way down, you may notice that it either fills in the same value, or the values [Type a quote from the document don’t change at all. F9 will refresh the references, filling the correct values. or the summary of an interesting
point. You can position the text box
F4 Switches between Absolute and Relative references, when entered in a formula. anywhere in the document. Use the Text Box Tools tab to change the
(A2 / $A$2 / $A2 / A$2 / A2) formatting of the pull quote text
box.]
CTRL + SHIFT + Right ARROW Selects all the columns, up to the last one active.
Pressing it again will expand the selection to the last column of the worksheet.
CTRL + SHIFT + Down ARROW Selects all the rows, up to the last one active.
Pressing it again will expand the selection to the last row of the worksheet.
. Select all the cells and create a pivot: