Excel General Tips on Excel
Double Paint Brush
Double-click on the paint brush to copy a format to many cells or ranges. Select the cell with the format that you want to copy then double-click on the paint brush. When you move to the cell where you want to paste the format you will notice that a paint brush follows the cursor. Paste the format to any number of cells or ranges, When it is finished go back to the paint brush on the toolbar and single click on it to deactivate the function.
Entering Dates
To enter today’s date in a cell just hold the CTRL key and press semi-colon( ). To enter the time hold CTRL/SHIFT and press colon (:)
If you want the day’s date to appear and change each time you open your spreadsheet, use the formula =Now() formatting the cell to show a date. If you format the cell to show a time, it will show the time when the workbook was last opened.
Too Many Worksheets
If there are more worksheets in your workbook than there is room to show all their tabs at the bottom of the screen, you then left click on one of the arrows to scroll right and left and find your worksheet. INSTEAD right click on the set of arrows. A list of all your worksheets is shown. You just click on the one that you want to access. If you have more than 15 worksheets, select in the list and choose your worksheet from hundreds.
Full Menus
Here is a tip that you will use only once. If you work with a recent version of Excel don’t you hate it when going to the menu only a partial list of sub-menus is offered to you? Right click on the menu bar, choose “Customized” then “Options” and check the “Always show full menus” check box.
Zeros
If you have a table full of formulas in your spreadsheet and you don’t want to show the “0” go to “Tools/Options/View” and choose to hide them by unchecking “Zero Values” . But if you want to hide only the zeros within the table and not the ones at the end of rows and columns select the inside of the table and go to “Format/Conditional Formatting” and choose the color “white” for the font of the cells where the value is equal to “0” . Learn more on Conditional Formatting.
The Dollar Sign ($)
When you need to copy/paste formulas from one cell to another cell or other cells you must start using the dollar sign to make certain addresses are relative or absolute. To do so you go to the formula bar (the window at the top of the screen where you enter values and formulas) you click before each column or line addresses and you add a $. Instead click on one of the addresses and click on the ” F4″ key of your keyboard. Two $ appear. Click again and one $ disappear. Click again and the other $ disappear while the first one comes back. Click again and there are no $. Click again and they both come back.
Absolute and Relative References: the Dollar Sign ($) in Excel
Working with Excel is a lot of fun but there is one hard, dull and painstaking lesson that you must learn before the REAL fun begins:
FIRST A TIP: If you already know about absolute and relative reference you might not know about this tip. To add dollar signs to your formulas go to the formula bar, select an address and click on the F4 key at the top of your keyboard. Click once and see, click twice and see, click three times and see, click four times and see start all over again and see.
The dollar sign is the key to doing miracles with Excel. Here is why you need to understand this concept to make any significant step in doing more with Excel. In 2 words:
Copying Formulas
To illustrate the concept of absolute and relative reference I am using the multiplication table example.
Try this example. Reproduce the following table in Excel. It is a simple multiplication table as we all studied in grade school. We want to find a way to enter a SINGLE formula in cell B2 and copy it all over the table. If we can’t do that we will have to enter manually 144 formulas.
In cell B2 enter =B1*A2 and the result is right for cell B2. But try to copy this formula all over the table. In other cells the results are wrong including 479,001,600 in cells M2, M13 and B13. Now try the right formula in B2 =B$1*$A2. Enter it manually and copy it all over the table. It works. Below I explain the concept of Absolute and Relative References and I show you how to do it easily.
You will not develop multiplication tables in the future but what you are about to learn will allow you to use the most powerful function in Excel SUMPRODUCT and develop automated reports that will save you a lot of time. You will discover a world of possibilities that are just impossible without the comprehension of the concept of relative and absolute reference. Come back to this page until you understand you will be thanking yourself for years to come.
It was NO FUN learning your multiplications and it will be no fun to learn what follows but where would you be if you didn’t master your multiplications. After this the real fun begins with Excel.
The formula =B1*A2 is entirely RELATIVE. It means that if you copy it up & down and sideways all the REFERENCES of the formula will change (B, 1, A and 2).
In cell B2 enter again =B1*A2 and copy the formula all over the table. Lets fix the first line.
Look in cell M2 and notice that the formula is =M1*L2 but it should be =M1*A2. To fix this problem on line 2 we will make the A ABSOLUTE in the original formula so that when we copy the formula sideways the A will remain an A. So just insert a dollar sign before the A in the formula and copy it sideways and see that cell M2 now shows a result of 12.
Go back to cell A2 where the formula is now =B1*$A2 and add another dollar sign before the 1. We are doing this because we want the formula to always look at row 1 when we copy it down. We are making the 1 absolute. Copy it down and see that cell B13 now shows a result of 12.
Copy the formula all over the table and you will see the MAGIC 144 in cell M13.
Try this exercise often by adding and removing the dollars signs making each reference absolute and relative. At a certain point enlightenment will hit you and you will understand absolute and relative reference.
THE TIP: Now that you know about absolute and relative reference you will greatly enjoy this tip. To add dollar signs to your formulas go to the formula bar, select an address and click on the F4 key at the top of your keyboard. Click once and see, click twice and see, click three times and see, click four times and see start all over again and see.
CTRL, ALT, Function Keys and the mouse
On your keyboard you see some function keys at the top (F1, F2…). Some of these keys perform interesting functionalities in Excel. Other interesting functionalities in Excel are activated when you hold the CTRL or the ALT key while pressing on some letters. The right click of the mouse also gives access to nice functionalities.
Here are these shortcuts by order of importance. I use the ones at the top of the list every day.
CTRL/x cuts (and deletes) the value or formula in a cell or set of cells to be pasted somewhere else.
CTRL/c picks up the value or formula of a cell or a set of cells to be pasted somewhere else.
CTRL/v pastes (I call it CTRL/Velcro)
CTRL/f to find a value in a row a column (that you have selected) or a sheet. To find a value in the whole workbook select all sheets (see how below).
CTRL/h to find and replace.
ALT/Enter to force a line break within a cell.
The F4 key allows you to add dollar signs to addresses AUTOMATICALLY. See chapter 11 on absolute and relative references.
CTRL/a selects all the cells of a sheet. You can also use your mouse and click on the small gray square between the ” 1″ and the ” A” at the top left corner of the sheet.
CTRL/Arrow Down will send you to the last cell in a column
Shift/CTRL/Arrow Down will select all the cells from where you are in the column to the last non-empty cell of the column
Apply the same logic to Up arrow, the Right arrow and the Left arrow
CTRL/Enter: Select many cells and then enter a value in the formula bar (just above the sheet on your screen). Hold the CTRL key and click Enter. The same value is entered in all the cells.
To replace a word just click on it and start writing its replacement. No need to delete it.
Many different contextual menus are available when you use the right button of the mouse. Right click on a chart, a cell, the columns and rows headings, a text box right click on everything and you will discover wonders.
When you select a cell, many cells, a row or a column you can right click on the mouse and discover the VERY important ” Clear Contents” . This functionality doesn’t make the selection disappear like the ” Delete” functionality it just clears the contents of the selected item. When you start working with Excel databases and automated reports using SUMPRODUCT it is vital that you use ” Clear Contents” instead of ” Delete” .
To select 2 or more cells that are not contiguous click on the first one hold the CTRL key down and select the others.
To select many contiguous cells click on the first one hold the SHIFT key and click on the last one.
To select 2 or more sheets click on the tab of the first one hold the CTRL key down and select the others. The word [Group] appears beside the workbook name at the top of your screen. To deactivate the grouping click on any single tab. When a group of sheets is selected anything you enter in cell A1 of the active sheet (for example) is also entered on the other sheets.
To select all the sheets click on the tab of the first one hold the SHIFT key and click on the tab of the last one.
If you have deactivated the calculation “Tools/Options/Calculation/Manual” and you want a sheet to be recalculated use the F9 key
Database Filters in Excel
Filtering data is one of the most powerful tools to analyze data. It is easy to learn and to use.
What is filtering data?
Let’s say that you have a very simple database with 5 fields (date, name, product, quantity and amount). For analytical purposes you don’t want to see the records (rows) for ALL the clients but only for one.
That is when you start using filters.
How hard is this? Two clicks on the button.
Autofilter
Discover the AUTOFILTER. Place the cursor anywhere within the database in your spreadsheet and go to the menu “Data/Filter/AutoFilter” and small arrows appear in the title cells of each field. Click again and they disappear.
When you click on the small arrow in the field (Name) you see a drop-down list containing all the names in the field. Notice that the names are in alphabetical order. At this point you see all the names in the database and you can find names that are incorrectly spelled. This first step allows you to clean your database. If you use the filter on “Amounts” they are shown to you in ascending order in the drop-down list and you can identify numbers that could be wrong. So without even activating the filters you can analyze the quality of the data and make any correction before you start filtering the database.
Let’s say you select “Peter” in the drop-down list of the filter. Here is what you see:
Notice that the small arrow in the field “Name” has turned blue and that the row numbers are also blue. This means that a filter is active.
To deactivate the filter go back to the drop-down list of the “Name” field and select “(All)” . IF you have activated filters in many field’s you can also go to the menu “Data/Filter/Show All” .
You now know how to use a filter in one field using a single criteria. You can also activate a filter in many different fields or use more than one criteria in a single field. You can use arguments for numerical values like “Equal to” , “Greater than” , “Smaller than” . With alphabetical values you can use arguments like “Contains” , “Does not contain” , “Begins with” and others.
We will see all these filtering techniques but let’s start with filtering a database using a single criteria in many fields.
Single Criteria Multiple Fields
You might want to see only the records (rows) showing “Peter” buying “Bikes” . To do so you will select “Peter” in the drop-down list of “Name” and ” Bike in the drop-down list of “Product” . You should then see only two records.
Copy/Paste Filtered Data
If you want to copy/paste this set of records on another sheet click anywhere in the database, go to the menu ” Edit/Go to/Special/Current Region” . Copy and paste on the other sheet.
Single Field Multiple Criteria
You can use up to two criteria in a single field (“Peter” and “Mary” ). In the drop-down list of the ” Name” field choose “Custom” . The following dialog window appears:
There are 4 text boxes and two radio buttons in the window. The first text box is set at “Equal to” . Select “Peter” in the second one, select the “Or” radio button, select “Equal to” in the third text box and “Mary” in the last one. Click on “OK” .
Remember that although you want “Peter” AND “Mary” you must use the OR argument. If you ask for “Peter” AND “Mary” no records will be filtered because there are no single record where the “Name” field is “Peter” AND “Mary”
If you want to use more than 2 criteria you will need to use the ” Advanced Filters” .
Other Custom Filters
In two of the text boxes of the “Custom” autofilter window you can select any of the 12 conditions. You will use some with numerical values and others with text values. You can use the question mark or the asterisk as wild characters or series of characters. For example all three “Equals to…Pet*r” , “Equals to…P**er” and “Equals to…P?r” will filter the records for “Peter” .
The Excel Database Functionalities
We are surrounded with databases. All accounting programs, sales programs, inventory programs and other business programs include a database. There is the main database in your corporation, there are databases on the WEB and all kinds of other departmental databases.
You can bring data from all these databases into Excel to analyse the data and create automated reports. You can also develop very useful databases with Excel. As a matter of fact if people knew Excel better Access would not exist.
With Excel you can develop analyses and reports that would be impossible or unaffordable to develop even with very sophisticated database programs like PeopleSoft, JDE, Oracle and others. Corporations that can afford these million dollars systems rely often on Excel to analyse the data and design reports to support very important decision making processes. Some of the most powerful analysis tools in Excel are database functionalities like sorting (lesson 5), filtering (lesson 6), subtotals (lesson 4B) and pivot tables (lesson 4C). There is even a form to enter data into an Excel database. It is there as soon as Excel recognizes your set of data as a database.
VERY IMPORTANT NOTE:
Excel needs to recognize your set of data as a database or you will not have access to any of the database functionalities from the “Data” menu item (the basic Sort and Filter or the more advanced Form, Subtotals and Pivot Table).
The DATABASE is a set of columns (called fields by the database people) that include a SINGLE title cell in each column. Select a different format for the title cells as oppose to the other cells of the table so that Excel understands that it is working with a DATABASE. I use bold font in the title cells and I add a border at the bottom of the cells. Use ” Text Wrap” in ” Format/Cells/Alignment” to write more than one line of text in one cell and use ” Alt/Enter” to force a line break within the lines.
The database MUST be surrounded by empty rows (top and bottom) and empty columns (right and left). If you database starts in cell A1, no need to add an empty row at the top or an empty column on the left.
The Excel database goes from the row of title cells to the last row (called record by the database people) that carries at least one value in any of the fields.
If you want to make sure that your database is recognized by Excel, click anywhere in it and go to Edit/Go to/Specials/Current Region. What is then selected is your database.
Here are 4 examples of set of data not recognized as a database by Excel and one real database.
This is not a database recognized by Excel because there are two rows of title cells
This is not a database recognized by Excel because row 2 is not empty.
This is not a database because columns B, D, F and H are empty. Remove the empty columns and you have a single database recognized by Excel.
Here is a database recognized by Excel. There are 5 fields (columns) (Date, Name, Product, Quantity, Amount) and 7 records (rows of data). Column E is not empty, the title cell is there and row 4 is not empty there is a data in field 1. There can be many empty cells in an Excel database (except for title cells) but never any empty rows or columns.
Once you set of data is recognized as a database you can sort and filter data, you can use the form to enter new data and you can calculate subtotals and develop pivot tables.
Sort Data in Excel
NOTE: If Excel recognizes your set of data as a database you can sort the data
Before you get into functions and formulas, here is a powerful data analysis tool that you can use: sorting data.
In the small database above you can sort the data to discover the largest quantity, you can sort the data by date, by product or by buyer. Once the data is sorted you can use the calculator (lesson 1) to find some quick totals by product or by client. You can do the same thing with a large database with thousands of records (lines). You can sort the data by date AND product to see what sells best on certain dates.
If you try to sort column “A” and the other columns don’t follow or the title cells are part of the sorting, it is because Excel doesn’t recognize your set of data as a database. Go back to the requirements page. Once this is done here is how you sort data.
To sort data by date select cell A2 (first record or data row) and go to the icons on the toolbar and click on the A/Z one. The data is sorted by date beginning with the earliest. Go back to the toolbar and click on the other icon and the records are sorted by date beginning with the latest date. Notice that all the records with the same date are together one after the other. What are the busiest days?
Select B2 and use the icons to sort the records alphabetically by client. All the records pertaining to one client are together. You can select those records and copy/paste them on another sheet.
Select C2 and use the icons to sort the records alphabetically by product.
You want to see the largest sale? Select D2 and click on the icon Z/A and the first record is the one with the largest amount. The A/Z icon will sort the records starting with the smallest amount.
Now let’s say that you want to sort the records by client AND by date to see how frequently a client buys. Click anywhere in the database. Go to the menu ” Data/Sort” and the following window appears:
From here you can sort the data using three different criteria. In the text boxes select your criteria and the order and then click on “OK” . Once the data is sorted you can use the calculator to find some quick totals.
The Calculator/Validator
The most simple yet extremely useful analytical tool in Excel is the Calculator.
Here is a tip that 75% of Excel users ignore and that you will be using daily from now on.
Select two or more cells with numbers in them and take a look at the bottom of your screen, you will see . It is the sum of the selected cells. If you right click on , you will see this:
you can select this functionality to show the sum, the count, the average…. or nothing at all. If you chose to count non-empty cells just select any cells (numerical or not) and the result appears at the bottom of your screen.
When will you use this tool?
– You will first use the calculator to validate certain totals in a report before issuing it. You make sure that formulas work correctly.
– You can rapidly calculate some significant totals in a set of data that doesn’t show any totals. You can count the number of values or entries. When you discover the database functionalities and the SUMPRODUCT function you can automate your report and validate the results using the filters and the calculator.
– You will also use the calculator to calculate some totals that do not exist in reports that are designed by others. For example you can calculate quarterly subtotals from a report showing only monthly numbers or calculate easily “Year to Date” .
– When you discover the comparative analyses in lesson 2 you can look at a report and its raw data on the same screen. The raw data can be in the same workbook, in a different workbook or even in a different program. While showing the data and the report on a split screen you can validate the totals in the report using the calculator in both views.
– You can use the calculator to analyse the data dynamically and then decide that such analysis is worthy of a special automated report and create it. The calculator then becomes a report designing tool
Excel Pivot Tables
The pivot table is a powerful analysis tool that allows the analyst to organise any database into tables with all kinds of sub-totals (SUM, COUNT, AVERAGE…) comparing sales of products by stores, sales of products by cities, sales of products by stores, by month AND by cities, etc.
First let’s demystify the pivot table. The pivot table is a powerful data analyzing tool but less than 1% of the thousands of people that I have worked with use it. Before you get into pivot tables be sure that you know how to use the filters in
Here is an example of a pivot table:
You have data supplied to you as TXT or CSV files or your import data from a database with Microsoft Query in the following format:
| Date/Month | Product | City | Qty | Amount |
| January | Brooms | New York | 536 | 1072 |
| February | Brooms | New York | 756 | 1512 |
| March | Brushes | New York | 654 | 1308 |
| January | Brushes | New York | 365 | 730 |
| February | Brooms | Montreal | 758 | 1516 |
| March | Brushes | Montreal | 445 | 890 |
| January | Brooms | Montreal | 255 | 510 |
| February | Brushes | New York | 654 | 1308 |
| March | Brooms | New York | 324 | 648 |
| January | Brushes | Montreal | 156 | 312 |
| February | Brushes | Montreal | 753 | 1506 |
| March | Brooms | Montreal | 135 | 270 |
Imagine such a table in your spreadsheet with 50,000 lines of data and you want to create a table answering to the following questions:
How many of each products were sold by city?
| Montreal | New York | Total | |
| Brooms | 1,148 | 1,616 | 2,764 |
| Brushes | 1,354 | 1,673 | 3,027 |
| Total | 2,502 | 3,289 | 5,791 |
How much of each products were sold by city?
| Montreal | New York | Total | |
| Brooms | $2,296 | $3,232 | $5,528 |
| Brushes | $2,708 | $3,346 | $6,054 |
| Total | $5,004 | $6,578 | $11,582 |
To complete such a task instantaneously, you will use the pivot table. To create the first pivot table (Products by City), copy the raw data in Excel. Select the table go to “Menu/Data/PivotTable and PivotChart Report” . In the first dialog box, choose “Microsoft Excel list or database” , click “Next” . In the second dialog box, accept the “Range” by clicking “Next” . In the third dialog box Click on “Layout…” and you will see the following dialog box:
Drag the small gray buttons on your right onto the white shape in the middle, “Qty” over “ATA” , Product over “RW” and “City” over “COLUMN” . Click “OK” and then “Finish” .
You have just created your first pivot table.
To create the second Pivot Table with the same data, right click anywhere on the first Pivot Table, select “Wizard/Layout” . Drag the “QTY” button off “DATA” and replace it by “Amount” . To create any other Pivot Table from the same data, right click anywhere on the first Pivot Table, select “Wizard/Layout” and move the gray buttons around. For example, “Product” over “ROW” and “Date/Month” over “COLUMN” will give you a pivot table about “Sales of products by month” .
Try this one: “Product” over “ROW” and, “City” and “Date/Month” over “COLUMN” . You have now “Sales of products by city and by month” .
Name Fields in Excel
In Excel you can name cells and sets of cells. Naming cells can be useful to simplify the writing of formulas specially the use of the dollar signs to make references absolute.
For example:
=A1 and =nfMyField are the same if you have named cell A1 “nfMyField”
=SUM(A1:A34) and =SUM(nfMyField) are the same if you have named the range A1 to A34 “nfMyField”
You will notice that it becomes very useful with SUMPRODUCT and INDEX/MATCH formulas.
Instead of writing a formula like this:
=SUMPRODUCT(($G$2:$G$20=$A2)*($H$2:$H$20=B$1)*($I$2:$I$20))
you will write this:
=SUMPRODUCT((nfColG=$A2)*(nfColH=B$1)*(nfCoI))
Instead of writing a formula like this:
=INDEX($G$2:$G$20,MATCH(B1,$H$2:$H$20,0)1)
you will write this:
=INDEX(nfColG,MATCH(B1,nfColH,0),1)
In these 2 previous formulas MAKE SURE that both named fields have the same number of cells.
Naming a field in Excel
To name a cell or a set of cells (range) in Excel you select it or them and you go to the Name Box at the top right of the screen just above the column line and beside the formula bar. The “Name Box” tooltip appears when you put the cursor above it.:
In the Name Box above you see B3 which is the address of the cell that is selected. Click in the name box and write nfMyField and THEN CLICK ENTER. The selected cell or set of cells in now named. Select any other cell, go to the small arrow right of the name box and click on it. You see a list of your named field. Select nfMyField and the cell or the set of cells that you have named gets selected. Always use the prefix ” nf” when you name a field you will not regret it when you start programming in VBA.
Remember that after naming a field in the Name Box YOU MUST CLICK ON ENTER.
Maintaining Named Fields in Excel
Sometimes you want to delete a named field or modify the cell or cells that it comprises. Go to “Insert/Name/Define” and you will see the following dialog box.
If you want to delete a named field select it in the list and when it appears in the box over the list click on “delete” . To modify its range go to the box “Refers to” change the addresses and then CLICK ON ADD. Don’t forget to CLICK ON ADD.
As you can see instead of using the quick method above (in the Name Box) to name fields you can also do it in this dialog window.
Signs and Formulas in Excel
Here are the 16 spreadsheet signs used to develop Excel formulas. 14 of them are basic one is essential if you want to do more with Excel and the other one is rarely used.
| Signs | What it Does |
| = | Equals |
| ( | Open parenthesis |
| ) | Close parenthesis |
| , | Separating arguments |
| : | From A1 to A23 A1:A23 |
| + | Plus and is also used to separate arguments in SUMPRODUCT formulas |
| – | Minus |
| * | Multiplies and is also used to separate arguments in SUMPRODUCT formulas |
| / | Divides |
| < | Smaller than: used mostly within IF formulas |
| > | Greater than: used mostly within IF formulas |
| ” “ | What is within is text |
| & | Working with text, assembling strings (chains of characters), concatenation |
| Separating arguments (Metric system) | |
| $ | Absolute/Relative References |
| ^ | Returns the result of a number raised to a power |
| ‘ | Transforms any content into text |
Examples
=A will result in the error message #Name? because Excel doesn’t know a function by the name of A. =”A” will result in A because you are saying with the double quotes that you want this cell to carry the character A.
=A1 will result in the value of cell A1 be it a number, a date or a string of character.
=3 will result in the number 3
=A1+A2+A3 will result in the sum of cells A1, A2 and A3. You can also use the SUM function =SUM(A1:A3) the colon meaning from/to.
=10/A1 will result in 10 divided by the value of cell A1. If cell A1 is empty or contains zero you end up with the error message #DIV/0!.
=IF(A1> 90,”A” ,”B” ) in plain English this formula says if the value of cell A1 is greater than 90 then the value of the cell in which resides this formula should be the letter A otherwise it should be the letter B. Notice the commas separating the three arguments of this IF formula. IF(condition, value if condition is true, value if condition is false)
=IF(A1< > 100,0,100) in plain English this formula says if the value of cell A1 is different than 90 then the value of the cell in which resides this formula should be 0 otherwise it should be 100
=IF(A1< =100,0,100)in plain English this formula says if the value of cell A1 is smaller then or equal to 100 then the value of the cell in which resides this formula should be 0 otherwise it should be 100
If in cell A1 you have “Peter” and in cell A2 you have “Clark” the formula =A1 & A2 in A3 will result in “PeterClark” . If you want a space between the first name and surname you will use the formula =A1 & ” ” & A2 telling Excel to insert a space (Space between double quotes) between the values of cell A1 and cell A2.
You must learn to master the use of the dollar sign ($) if you want to start developing long and complex formulas that you would want to copy/paste. To insert $ signs within an address select it in the formula bar and click on the F4 key once, twice, three or four times as needed.
If in cell A1 you have the formula =B6 it will become =B7 when you copy/paste it in cell A2 and it will become =C6 if you copy/paste it in cell B1 because the row and column are relative.
If in cell A1you have the formula =$B$6 you can copy/paste it anywhere, the address doesn’t change because the row and column are absolute.
If in cell A1you have the formula =$B6 it will become =B7 when you copy/paste it in cell A2 and it will remain =$B6 if you copy/paste it in cell B1 because the row is relative but the column are absolute.
If in cell A1you have the formula =B$6 it will remain =B$6 when you copy/paste it in cell A2 and it will become =C$6 if you copy/paste it in cell B1 because the row is absolute but the column is relative
ISERROR Function in Excel
Sometimes a formula will return an error message like #DIV/0, #NA and others. There is one way to avoid such results and it is by protecting you formulas with the ISERROR function. For example the formula =B1/A1 will return a #DIV/0 if A1 is empty or equal to zero. The formula will then look like this:
=IF(ISERROR(B1/A1),0,B1/A1)
In plain English: should the result of A1 divided by B1 be an error change the result to 0 else show the result of B1/A1.
The is a somewhat easy approach to do this specially if you are working with long formulas. First develop your formula without the ISERROR protection =B1/A1 then follow these steps.
insert between the equal sign and the original formula this piece of formula:
=IF(ISERROR(),0,9)B1/A1
Then copy the original formula at two places between the parentheses of the ISERROR condition and replacing the 9:
=IF(ISERROR(B1/A1 ),0,B1/A1 )B1/A1
Finally remove the original formula at the end of the new one.
=IF(ISERROR(B1/A1 ),0,B1/A1 )
The SUBTOTAL Function in Excel
The Excel function SUBTOTAL is a function that can execute 11 types of calculations. SUBTOTAL is a very useful formula for analysis of data directly from the database. It will sum, count, calculate average….of the records that have been filtered in a database.
The syntax is very easy:
=SUBTOTAL(9,A2:A50))
In plain English: Execute calculation number 9 on all filtered records of range A2 to A50. If no filter is applied the calculation is executed using all the cells in the range.
Calculation types:
1: average, 2: count (numbers), 3: count (non blanks), 4: maximum, 5: minimum, 6: product, 7: standard deviation (sample), 8: standard deviation (population), 9: sum, 10: variance (sample), 11: variance (population)
Types 7, 8, 10 and 11 are
Similarly for Hidden rows, we can use numbers from 101 to 111 (Not workable on XP)
SUMPRODUCT
Microsoft announces the arrival of 3 extraordinary new functions in Excel 2007.They say that SUMIFS , COUNTIFS and AVERAGEIFS (with an “S”) will revolutionize the reporting practices allowing users to automate most of their reports without any macros or programming….but the revolution has already begun.
Example of Data, Report and Chart:
You get data from central databases, accounting, sales and manufacturing programs and you need to develop reports. See the lesson 1-8 on Excel databases and all the functionalities that become available when Excel recognize your set of data as a database (sorting, filtering, pivot tables, subtotals, outlines, form).
In your set of data there can be 500 lines, 65,000 lines and even 1,000,000 lines (Excel 2007). The order of the data may differ from time to time data can be sorted by date, by product, by city, etc.. There can be more columns on salesperson,, time, department, store, etc. SUMPRODUCT will handle it all.
** The totals include all the data in the database not just the data in the picture above.
You need a report and chart (Sales by City ($) as above) that feeds on the set of data. This report must be refreshed AUTOMATICALLY when you add data to the database, when you modify the data or when you copy/paste another set of data. There could also be other reports (Sales by Month ($), Sales by City (Quantity), Sales by Month and City ($), etc.) that feed on the same set of data and they need to be refreshed AUTOMATICALLY all together. Sometimes you need to sort the data or filter it and it should not change the reports.
To achieve such a miracle you will develop ONE SUMPRODUCT formula in the top/left cell of each table (Bikes/Montreal in the example above) and be able to copy/paste it all over the table. This formula shouldn’t need to be modified whatever the dataset.
With SUMPRODUCT you can sum, calculate averages, use one or ten criterias, use any number of values per criteria. You can use SUMPRODUCT with other functions and use SUMPRODUCT to do things that VLOOKUP cannot do.
INDEX/MATCH Formulas in Excel
With INDEX/MATCH you can automate your invoices, quotation workbooks, business models. You can also develop reports with data coming from many different sources. With these formulas you can virtually create relational databases in Excel and develop reports downstream. They are more powerful and versatile than LOOKUP formulas.
The quantities sold are in one data set, the name and the address of the client is in another set of data and then the product description is in a third set of data…no problem, a single INDEX/MATCH formula copied all the way down your column solves this problem. You now have a data set including sales, products and clients.
Stop entering data manually in your workbooks and reports use the easy to master INDEX/MATCH formula.
The INDEX/MATCH formula is a formula using 2 functions INDEX and MATCH.
With the example below, the formula =INDEX(A2:E5,1,2) would return ” Tiger Auto” because the formula translated in plain English says, what is the value found in the rage ” A2:E5″ in the first row and the second column.
The formula =MATCH(B11,A2:A5,0) in cell B12 would return 2 because the value in B11 (86598) is on the second row of the range A2:A5. The zero at the end of the formula tells Excel that you want an exact match. If you were looking for a number, a 1 instead of a zero would tell Excel to use the next higher value as a -1 would mean use the next lower value.
With MATCH you don’t have to sort the range or use FALSE or TRUE.
Now let us replace the row number in the INDEX formula by the MATCH formula:
=INDEX(A2:E5,MATCH(B11,A2:A5,0),2)
and whenever you change the value in cell B11, a new value appears in cell B12.
Here are some formulas combining INDEX and MATCH functions.
| A | B | D | D | E | |
| 1 | Client # | Name | Address | City | State & ZIP |
| 2 | 36596 | Tiger Auto | 33 Woods | Miami | FL 10230 |
| 3 | 86598 | Phil Lumber | 555 Makes | Boston | MA 34567 |
| 4 | 58971 | David Eng. | 1200 Duvall | Charleston | SC 10004 |
| 5 | 87456 | Stewart Inc. | 5673 Payne | San Francisco | CA 27002 |
Tired of typing names, addresses, cities and states on invoices?
| A | B | |
| 11 | Client #: | 86598 |
| 12 | Name: | =INDEX(A2:E5,MATCH(B11,A2:A5,0),2) |
| 13 | Address: | =INDEX(A2:E5,MATCH(B11,A2:A5,0),3) |
| 14 | City: | =INDEX(A2:E5,MATCH(B11,A2:A5,0),4) |
| 16 | State & ZIP | =INDEX(A2:E5,MATCH(B11,A2:A5,0),5) |
Each time you enter a Client #, the name, address, city and State are automatically modified. Imagine the time you save and the number of errors that you avoid when you complete your invoices. You can do the same with your products entering the name and extracting the unit price, the product number from a table.
Excel Date & Time Functions and Formulas
There are 20 Excel functions in the date and time category.
Here are the 10 functions that you will use more often.
| Functions | What it Does |
| DATE | Returns the serial number of a particular date |
| DAY | Converts a serial number to a day of the month |
| HOUR | Converts a serial number to an hour |
| MINUTE | Converts a serial number to a minute |
| MONTH | Converts a serial number to a month |
| NOW | Returns the serial number of the current date and time |
| SECOND | Converts a serial number to a second |
| TIME | Returns the serial number of a particular time |
| TODAY | Returns the serial number of today’s date |
| WEEKDAY | Converts a serial number to a day of the week |
| YEAR | Converts a serial number to a year |
| DATEDIF | Calculates the interval between two dates |
The three most important things that you should remember when working with dates and times are:
FORMAT, FORMAT and FORMAT.
For example:
Let’s say that you have the dates 1/16/2005 in cell A1 and 1/22/2005 in cell B1
=B1-A1 in cell C1 will return:
– 6 if the format of cell C1 is either “General” or “Number”
– 1/6/1900 if the format of cell C1 is “Date”
Using the proper formats you might want to work dates even without date & Time functions:
If you have a date in cell A1 and you want the date for the next day in cell B1 (formatted “date” ) the formula will be:
=A1+1
to calculate the date of a week later the formula will be:
=A1+7
Excel Date and Time Functions and Formulas
If you enter the date 2/1/2005 in cell A1 and the number format of the cell is ” General” you will see 38394. This is a ” Serial number” and it is the way Excel works with dates and times. When you format the cell or use one of the functions below the serial number is viewed as times and dates
To enter the date of the day no need to key it in, click and hold the CTRL and SHIFT keys and click on the semi-colon/colon key and there is the date.
To enter the time, click and hold both the Shift and the CTRL keys and click on the colon key (:) and there is the time.
DATEDIF
One note to start. If you go to the menu ” Insert/Function” you won’t find this function. Excel has forgotten it. Here is how it works. Let’s say that you have the dates 1/16/2008 in cell A1 and 1/16/2009 in cell B1:
=DATEDIF(A1,B1,”y”) will return 1 (year)
=DATEDIF(A1,B1,”m”) will return 12 (months)
=DATEDIF(A1,B1,”d”) will return 365 (days)
Excel Financial Functions and Formulas
There are 53 Excel functions in the Financial category.
Here are the 4 functions that you will use more often.
| Functions | What it Does |
| FV | Returns the future value of an investment |
| NPER | Returns the number of periods for an investment |
| PMT | Returns the periodic payments |
| RATE | Returns the interest rate per period |
The financial functions are among the less user friendly, the less intuitive in Excel. Here are 4 examples trying to clarify the matter.
The RATE Function
The question to which RATE brings an answer to is:
– What is the real interest rate for a certain amount each period to pay a loan?
| A | Descriptions | |
| 1 | 48 | Number of periods (years, months, weeks..etc) |
| 2 | $550 | Periodic payment |
| 3 | $24,000 | Total amount of loan |
| 4 | 0 | The balance left to pay at the end of the period. If you omit this argument Excel uses “0”. |
| 5 | 0 | Payment made at the beginning of the period (1) or at the end of the period (0). If you omit this argument Excel uses “0” saying that the payment is made at the end of each period which is usually the reality when you borrow money. |
| 6 | 5.00% | The result with the formula using the RATE function. Note: the format of this cell must be “Percentage” with any number of decimals. In this example the number of decimals is 2 |
Here is the formula in cell A6:
=RATE(A1,-A2,A3,A4,A5)*12
Notes on the formula: The payment argument is negative (-A2); If you use months as periods and you want an annual rate you multiply by 12, if you use a years as periods and you want an annual rate you don’t multiply……; If you don’t use the “Percentage” format in cell A6 the result of this example will be 0.05; The formula could also be =RATE(A1,-A2,A3)*12 the arguments in A4 and A5 being optional
Excel Information Functions and Formulas
There are 17 Excel functions in the information category.
Here are the 4 functions that you will use more often.
| Functions | What it Does |
| CELL | Returns information about formatting, location, or contents of a cell |
| ISERROR | Returns TRUE if the value is any error value |
| ISNUMBER | Returns TRUE if the value is a number |
| ISTEXT | Returns TRUE if the value is text |
Examples of Basic Information Formulas
CELL, MID, FIND
If you want the name and path of the active spreadsheet to be entered automatically in a cell, use the formula: =CELL(“filename” )
if you want only the filename use :
=MID(CELL(“filename” ,A1),FIND(“[” ,CELL(“filename” ,A1))+1,FIND(“]” ,CELL(“filename” ,A1))-FIND(“[” ,CELL(“filename”,A1))-1)
Excel Logical Functions and Formulas
There are 6 Excel functions in the Logical category. See them all with a brief description in appendix 18-F of the Downloadable Tutorial
Here are the 3 functions that you will use more often.
| Functions | What it Does |
| AND | Returns TRUE if all its arguments are TRUE |
| IF | Specifies a logical test to perform |
| OR | Returns TRUE if any argument is TRUE |
Tips
You cannot use an IF formula to change the color of the font or of the cell’s background based on a value (criteria). To do so you will use “Conditional Formatting” .
When you develop a condition for an IF formula it is not case sensitive.
The basic IF formula looks like this =IF(condition,value if condition is True,value if condition is False). So =IF(A1=1,9,8) in plain English means if the value of cell A1 is 1 the value in which this formulas resides is 9 otherwise it is 8.
Remember that you cannot nest more than 7 IFs within the same formula. Nested IFs are IFs within IFs like in =IF(A1> =90,”A” ,IF(A1> =80,”B” ,”C”). In other words if a condition is true you want to test another condition in such cases we are talking about nested IFs. In plain English this formula says: if the value of cell A1 is equal or higher than 90 the result in the cell where this formula resides is the capital letter “A” , if the value in cell A1 is 80 or greater then the result is “B” else the result is “C” . Below you will see a way to go around this limit.
Examples of Basic Excel Logical Formulas
In this section I can only give you examples of simple IF formula because OR and AND are not used by themselves.
IF
The basic IF formula looks like this =IF(A1=100,9,8). In plain English it means if the value of cell A1 is 100 the value in which this formulas resides is 9 otherwise it is 8.
You can also write =IF(A1< > 100,9,8). In plain English it means if the value of cell A1 is DIFFERENT than 100 the value in which this formulas resides is 9 otherwise it is 8. Using the “smaller than sign” (<) and the “greater than sign” (> ) means different than.
You can also write =IF(A1=> 100,9,8). In plain English it means if the value of cell A1 is equal to or greater than 100 the value in which this formulas resides is 9 otherwise it is 8. Remember the order: the equal sign is first.
When you use text value you nee to use the double quotes.
You will write =IF(A1=”Peter” ,9,8).In plain English it means if the value of cell A1 is Peter then the value in which this formulas resides is 9 otherwise it is 8.
You will write =IF(A1=1,”Peter” ,8). In plain English it means if the value of cell A1 is equal to 1 then the value in which this formulas resides is Peter otherwise it is 8.
Finally if you want to say that if the value of cell A1 is equal to 1 the result should be an empty cell or a space you will write:
=IF(A1=1,” ” ,8) for the space (notice the space between the double quotes
=IF(A1=1,,8) or =IF(A1=1,” ” ,8) for the empty cell. It is either nothing between the two commas or a set of double quotes with nothing in between.
Excel Lookup and Reference Functions and Formulas
There are 18 Excel functions in the text category.
Here are the 5 functions that you will use more often.
| Functions | What it Does |
| INDEX | Uses an index to choose a value from a reference or array |
| MATCH | Looks up values in a reference or array |
| INDIRECT | Returns a reference indicated by a text value |
| OFFSET | Returns a reference offset from a given reference |
| ADDRESS | Returns a reference as text to a single cell in a worksheet |
The most important functions in this category
INDEX, MATCH
INDEX/MATCH formulas are so important that they are the subject of an entire workbook with step by step development instructions and examples. See “excel-tutorial-index-match.xls” (see the advertisement below). You will also find applications in “excel-template-invoice.xls” (see the advertisement below) where you enter the name of the client or select it in a drop down list and the address appears automatically in another cell, enter the product number and its description, unit and cost appear automatically. See also how to use INDEX/MATCH to develop scenarios and ” what if” applications in business models “excel-example-scenario.xls” (see the advertisement below). You will also discover how to use INDEX/MATCH to develop cascading drop-down lists where you select a country in a first drop-down list and only cities from this country appear in the second drop-down list. See “excel-tutorial-ddlists.xls” (see the advertisement below).
INDEX/MATCH is also used to create relational databases in Excel. Suppose that the details about your clients are in one database and the sales are in another and you want to analyse sales by cities. Because the client’s number is in both databases (sales and clients), you can analyze sales by city using an INDEX/MATCH formula to bring the cities into the sales database.
The LOOKUP Group
The functions in this group are widely known among advanced users. But once they discover the more powerful and less limited INDEX/MATCH they are kind of pushed aside.
Other Functions
When you start developing more complex business models or when you want to calculate and chart moving averages and moving ” Year to Date” you will need the two following functions.
INDIRECT
If in cell A1 of Sheet1 you have this value (Sheet2!A1) and in cell A2 of Sheet1 you have the following formula:
=INDIRECT(A1) the result will be the value of cell A1 of Sheet2.
OFFSET
The most intellectually challenging function in Excel.
The general format of this function goes as follows:
=SUM(OFFSET(D1,1,1,3,3))
In plain English…sum the range of 3 rows by three columns that starts 1 row below and one column to the right of D1 (the anchor). So if you have 2 in all 9 cells E2 to G4 the result will be 18.
Excel Math and Trig Functions and Formulas
There are 58 Excel functions in the text category.
Here are the functions that you will use more often with tips and examples.
The most important and powerful function in Excel is SUMPRODUCT and it has a entire chapter dedicated to it.
| Functions | What it Does |
| ABS | Returns the absolute value of a number |
| MOD | Returns the remainder from division |
| POWER | Returns the result of a number raised to a power and allows the calculation of cubic root and other roots |
| ROUND | Rounds a number to a specified number of digits |
| ROUNDUP | Rounds a number up, away from zero |
| SQRT | Returns a positive square root |
| SUBTOTAL | Returns a subtotal in a list or database |
| SUM | Adds its arguments |
| SUMIF | Adds the cells specified by a given criteria |
| SUMPRODUCT | Returns the sum of the products of corresponding array components |
| TRUNC | Truncates a number to an integer |
Tips
Read other general tips on formulas in the Introduction to this section on Excel Functions and Formulas
When you specify in the format of a cell that you want only 2 decimals Excel shows only 2 decimals (rounding up) BUT it still uses all the decimals. For example if in cell A1 you enter 2.1456 and format it to show only 2 decimals you will see 2.15. Now if in cell B1 you write the formula =A1 and make the format “General” you will see that Excel is using all 4 decimals (2.1456). This is why you will need to use functions like INT, TRUNC, ROUND, ROUNDUP and ROUNDDOWN if you want to use a specific number of decimals in your calculations.
SUM
=SUM(A1,B6,G6) or =SUM(A1+B6+G6) will return the sum of the values in cells A1, B6 and G6
=SUM(A1:A23) will return the sum of the values in cells A1 to A23
=SUM(A1:A23,F3:F34) will return the sum of the values in cells A1 to A23 plus the sum of the values in cells F3 to F34
In cell B2 of a yearly summary you want to sum the values in cells B2 of each of the monthly sheets. You have named your sheets “January” , “February” ….and you have used: =January!B2+February!B2+March!B2…+December!B2
You can also write this:=SUM(January:December!B2)
TRUNC
I don’t use the INT or ROUNDDOWN functions because TRUNC does the same thing and more. The TRUNC function removes decimals without rounding. If you have 2.2 or 2.7 in cell A1 =TRUNC(A1,0) will return 2. Interestingly enough if you have 12,345 in B1 using a minus sign in the second argument of TRUNC =TRUNC(B1,-3) will return (12,000). Handy when you don’t want to show the hundreds, the tens and units in a report.
ROUND
This function removes decimals rounding up the last decimal if the next one is 5 or over. So if you have 4.126 in cell A1 and use the formula =ROUND(A1,2) the result will be 4.13 if the value in A1 is 4.123 the result will be 4.12.
ROUNDUP
This function does the same thing as the function ROUND but always rounds up. So if you have 4.126 in cell A1 and use the formula =ROUNDUP(A1,2) the result will be 4.13 if the value in A1 is 4.123 the result will still be 4.13.
ABS
=ABS(A1) will return 5 if in cell A1 you have -5 or 5. This functions removes the sign.
MOD
The modulo is what is left after a division. =MOD(20,6) is 2 because you have 3 times 6 in 20 and the rest is 2. Notice the use of the comma to separate the arguments. See an application below in determining the age of a person.
SUMPRODUCT (The best kept secret in Microsoft Excel)
Here is what Excel says you can do with SUMPRODUCT:
Let’s say that you have a series of quantities in cells A1 to A5 and a series of unit prices in B1 to B5. With SUMPRODUCT you can calculate total sales with this formula: =SUMPRODUCT(A1:A5,B1:B5)
Basically SUMPRODUCT sums A1 multiplied by B1 plus A2 multiplied by B2………
In the last 20 years I have used SUMPRODUCT for the purpose presented by Excel once or twice. But I use SUMPRODUCT daily to solve all kinds of other business data problems. It is the most powerful and useful function in Excel. Read chapter 13 that is entirely dedicated to SUMPRODUCT
SUBTOTAL
One of the giant steps (no. 2) that users make is when they learn about the database functionalities in Excel. When you know how to filter data then SUBTOTAL becomes a very interesting function.
The function SUBTOTAL allows (among other operations) to count, to sum or to calculate the average of filtered elements of a database. The function requires two arguments, the second is the range covered by the function and the first is a number between “1” and “11” that specifies the operation to be executed (for ex. “1” is for average, “2” is for count and “9” is for sum).
=SUBTOTAL(9,B2:B45)
SQRT
Extracting a square root is finding the number that multiplied by itself will result in the number that you are testing. Extracting a cubic root is finding the number that multiplied by itself two times will result in the number that you are testing. Extracting the fourth root is finding the number that multiplied by itself 3 times will result in the number that you are testing.
To extract the square root of a number you will use a formula like:
=SQRT(16) that will result in 4 because 4 multiplied by 4 is 16 or
=SQRT(A1) that will also result in 4 if the value in cell A1 is 16.
There are no specific Excel function to extract the cubic root or any other root. You have to trick the POWER function into doing it.
POWER
You can raise a number to a power (multiplying it by itself a certain number of times with this function. Hence:
=POWER(4,2) will result in 16 (4 times 4) or
=POWER(A1,2) will also result in 16 if the value in cell A1 is 4.
You can to trick the POWER function into extracting the square root, the cubic root and any other root by submitting a fraction as second argument. For example you can extract the square root of 16 with the formula =POWER(16,1/2), the cubic root with =POWER(16,1/3) and so on.
ROUND, SUM
=ROUND(SUM(A1:A5),2) will return the sum of A1 to A5 rounded to 2 decimals.
IF, MOD, TRUNC and &
How many dozens are there in 106 units?
With the number of units in cell A1 the formulas in B1:
=TRUNC(A1/12,0) will return the number of complete dozens
this formula in C1:
=MOD(A1,12) will return the number of units left when the total number is divided by 12.
If you want to present the result as “8 dozens and 10 units” in a single cell you will use the following formula combining math & Trig functions and the ampersand (& ) sign:
=TRUNC(A1/12) & “dozens and ” & MOD(A1,12) & ” units”
But what if there are 96 units and you don’t want the result to show as “8 dozens and 0 units” but as “8 dozens” . You will then use this formula:
=IF(MOD(A1,12)=0,TRUNC(A1/12) & ” dozens” ,TRUNC(A1/12) & ” dozens and ” & MOD(A1 12) & ” units” )
INT, TRUNC, MOD and &
You want to determine the age of a person. If in cell ” A3″ you enter the date of birth, and in cell ” B3″ today’s date, the following formula in ” C3″ would give you a good approximation of the age (plus or minus a few days):
=INT((B3-A3)/365) & ” years and ” & TRUNC((MOD((B3-A3) 365))/30) & ” months”
If in cell A3 you enter the date of birth and in B3 you enter the formula =NOW() then each day when you open the workbook the age of the person is re-calculated in cell C3
Excel Statistical Functions and Formulas
There are 80 Excel functions in the statistical category.
Here are the functions (10) that you will use more often with tips and examples.
| Functions | What it Does |
| AVERAGE | Returns the average of its arguments |
| AVERAGEA | Returns the average of its arguments, including numbers, text, and logical values |
| COUNT | Counts how many numbers are in the list of arguments |
| COUNTA | Counts how many values are in the list of arguments |
| RANK | Returns the rank of a number in a list of numbers |
| LARGE | Returns the k-th largest value in a data set |
| SMALL | Returns the k-th smallest value in a data set |
LARGE, SMALL
And what if you want the second or third largest value or the second smallest value. Use LARGE and SMALL like this:
=LARGE(A1:A5,2), =LARGE(A1:A5,3), =SMALL(A1:A5,2)
You can use these functions with dates.
As a matter of facts you can forget about MIN and MAX with:
=LARGE(A1:A5,1), =SMALL(A1:A5,1)
COUNT and COUNTA
If you want to count the number of cells that are not blank COUNT and COUNTA will return a different result if in one of the cells there is a text. OR A SPACE
=COUNT(A1:A5) will return 5 is only numbers OR DATES are present in cells A1 to A5 and 4 if there is a letter, an empty cell OR A SPACE in one of the cells. The SPACE thing is important to remember when you are importing data from an external source.
=COUNTA(A1:A5) will return 5 unless one of the cells is empty. If all the cells contain numbers, letters OR SPACES the result will be 5.
AVERAGE and AVERAGEA
Watch for dates! If you want the average of a range and there is a date within there is a problem because dates are numbers. If all the cells are dates, indeed you can calculate the average date of…. The difference between AVERAGE and AVERAGEA becomes evident when one of the cells contains a text OR A SPACE and don’t forget the SPACE. A cell containing a space is NOT empty.
Excel Text Functions and Formulas
There are 24 Excel functions in the text category.
Here are the functions (10) that you will use more often with tips and examples.
| Excel Text Functions | What it Does |
| CONCATENATE | Joins several text items into one text item |
| FIND | Finds one text value within another (case-sensitive) |
| LEFT | Returns the leftmost characters from a text value |
| LEN | Returns the number of characters in a text string |
| MID | Returns a specific number of characters from a text string starting at the position you specify |
| REPT | Repeats text a given number of times |
| RIGHT | Returns the rightmost characters from a text value |
| TEXT | Formats a number and converts it to text |
| TRIM | Removes spaces from text |
| VALUE | Converts a text argument to a number |
Tips
To Concatenate: To assemble strings of text. When you concatenate the result is always in text format even if your are concatenating numbers.
For example: if you have 1 in cell A1 and 2 in cell A2 the formula =A1+A2 will return 3. If instead of the plus sign (+) you use the ampersand (& ) the formula =A1 & A2 will return 13 because concatenating is not adding it is creating a chain of characters with the content of many cells. The result 13 is not even a number with which you could execute calculations it is a text just like Peter.
The TEXT functions in Excel are great “Time Saving” tools. When you have data that you receive from colleagues, clients or suppliers, when you download data from a database or the Internet and the format is not right for you, you need to RE-ENTER the data manually and this task is time consuming, error prone and very frustrating. The TEXT functions will allow you to do the reformatting automatically.
COUNTIF Function
The general format for the COUNTIF formula is as follow: =COUNTIF(range where criteria applies, criteria)
Applied to the table below, the result of the following formulas shoule be 3:
=COUNTIF(C2:C7,”John”)
or
=COUNTIF(C2:C7,C2)
| A | B | C | D | |
| 1 | Month | State | Name | Amount |
| 2 | May | MI | John | 2 |
| 3 | Jun | NY | Peter | 6 |
| 4 | Mar | PA | John | 8 |
| 5 | Dec | NH | Peter | 3 |
| 6 | Nov | RI | John | 4 |
| 7 | Oct | FL | Peter | 6 |
The COUNTIF function is limited to one criteria (one column). If you want to count the amounts based on month, State and name, discover the very simple SUMPRODUCT function with which you can use 2, 3 or more criteria with “or” , “and” …SUMPRODUCT is like COUNTIF on steroids
SUMIF Function
The general format for the SUMIF formula is as follow: =SUMIF(range where criteria applies, criteria, sum what)
Applied to the following table, the result of the following formula should be 14:
=SUMIF(C2:C7,”John” ,D2:D7)
or
=SUMIF(C2:C7,C2 ,D2:D7)
| A | B | C | D | |
| 1 | Month | State | Name | Amount |
| 2 | May | MI | John | 2 |
| 3 | Jun | NY | Peter | 6 |
| 4 | Mar | PA | John | 8 |
| 5 | Dec | NH | Peter | 3 |
| 6 | Nov | RI | John | 4 |
| 7 | Oct | FL | Peter | 6 |
The SUMIF function is limited to one criteria (one column). If you want to sum the amounts based on month, State and name, discover the very simple SUMPRODUCT function with which you can use 2, 3 or more criteria with “or” or “and” …SUMPRODUCT is like SUMIF on steroids.
Excel VLOOKUP Function
The VLOOKUP function in Excel searches for a value in the leftmost column of a table and returns the value on the same row of another column to the right. So VLOOKUP looks up for ” John” in column A and his address, his age or his balance appear in either columns B, C or D.
Here are usual questions from users:
1- What if the lookup column in my table is not the leftmost column?
With VLOOKUP function it is complicated. The table needs to be reorganize to make the lookup column the left most column of the table. With INDEX/MATCH the lookup column can be anywhere.
2- What if the values in my lookup column are not in alphabetical order?
With INDEX/MATCH instead of VLOOKUP it doesn’t matter. With VLOOKUP you need to add a 5th argument to your formula (FALSE). This means that each time a new row is added the table MUST be re-sorted.
3- Can 2 criteria be used with VLOOKUP? For example, can I find John (A) Baxter (B)’s address ( C) or can I find the price (D) of a 1978 (A), Chevrolet ( B), Malibu (C).
Yes, but it is complicated. Create a new column in which the values of the criteria columns are concatenated and use this new column as criteria (first and last name or year, make and model). Remember that this new column must be the leftmost column of the table.
No need to create a new column with SUMPRODUCT or INDEX/MATCH in array format.
4- In a sales database can I sum all the sales (E) of one product (B) with VLOOKUP?
No. Use SUMPRODUCT. For example, if in A you have dates, in B store numbers, in C products and in D amounts, you can with SUMPRODUCT sum the sales of a product, in a certain store and on a certain date.
VLOOKUP is a function inherited from Lotus-123. There is much better in Excel, more powerful and less limited, it is INDEX/MATCH. INDEX/MATCH replaces all lookup functions (VLOOKUP, HLOOKUP and LOOKUP).
The VLOOKUP function in Excel
This function searches for value in the left-most column of a table and returns the value on the same row of another column to the right. So VLOOKUP looks up for “John” in column A and get shis address, his age or his balance that are in columns B, C or D.
VLOOKUP in Excel is used in two situations:
– On an invoice or other documents you enter the name of a client and in the next few cells an Excel VLOOKUP formula brings in the address 1, address 2, the city, State and ZIP Code. Less manual entries less mistakes. On the same invoice you type in the product number and in other cells its description, unit and unit price are carried over by Excel VLOOKUP’s.
– You have a set of sales data and you want to subtotal sales by cities. Unfortunately the city in which the client lives in not part of the set of data. This information exists in another set of data that you import on sheet 2. With Excel VLOOKUP you can bring the name of the city within your first set of data on sales and calculate the subtotals by city.
IMPORTANT NOTE: The values that your formula is looking up through MUST BE IN ASCENDING ORDER (a, b, c, d) unless the fifth argument is set to FALSE.
Here is a basic formula: =VLOOKUP(“John” ,A1:G32,2, FALSE)
The syntax for these formulas is as follows the first argument (“John” ) is what VLOOKUP is looking for, the second argument (A1:G32) means to search a value in the table A1 to G32 looking up for ” John” in column A, the third argument (2) is the column from which the answer is extracted, and the fifth argument (FALSE) tells Excel that you are looking for an EXACT match and not the next lower value. If the fifth argument is omitted or set to TRUE the formula will return the answer for the next lower value if it doesn’t find EXACTLY what you are looking for and if the values are sorted in ascending order. For example if looking up for John and the formula only finds Albert and Susan it will return the answer for Albert.
=VLOOKUP(“John” ,A1:G32,2, FALSE) looks for ” John” in the first column (A) of the range (A1:G32) and returns the value of the cell in the 2nd column (B) same row.
Use the address of a cell in which the value as first argument in the formula (ex: J20) is changed
=VLOOKUP(J20,A1:G32,2, FALSE) looks for whatever value is in cell J20 in the first column (A) of the range (A1:G32) and returns the value of the cell in the 2nd column (B) same row.
Remember to ALWAYS use ” FALSE” as fifth argument and to make sure that the values in the column that VLOOKUP is looking up within are in ascending order.
VLOOKUP is always looking up within the first column of the table that is submitted as second argument. If column C is to be the look up column change the second argument to C1:G32.
=VLOOKUP(J20,C1:G32,2, FALSE).
LOOKUP Function In Excel
The LOOKUP function allows to look for a value in a column or a row based on a certain value in another column or row. So you look up for ” John” in column A and you get his address in column B or you look up for ” John” in row 1 and you get his address in row 2.
Basically LOOKUP does what both VLOOKUP and HLOOKUP do. But unlike with VLOOKUP and HLOOKUP you cannot specify if you are looking for an exact match or not. LOOKUP is always looking up for an exact match.
LOOKUP is always looking up in the first column or row of the array that you are submitting as second argument.
LOOKUP is used in two situations:
-On an invoice or other documents you enter the name of a client and in the next 2 cells an Excel LOOKUP formula brings in the address 1 and address 2. Less manual entries less mistakes. On the same invoice you type in the product number and, its description, unit and unit price are carried over by Excel LOOKUP’s.
– You have a set of sales data and you want to subtotal sales by cities. Unfortunately the city in which the client lives in not part of the set of data. This information exists in another set of data that you import on sheet 2. With Excel LOOKUP you can bring the name of the city within your set of data on sales and calculate the subtotal by cities.
IMPORTANT NOTE: The values in the set of cells that you are searching within MUST be in ascending order (1,2,3,4…) or in alphabetical order.
LOOKUP
Working vertically with columns
=LOOKUP(5,A1:A6,B1:B6) looks up for 5 in A1:A6, and returns the value from B1:B6 that’s in the same row as the 5. If 5 is not found LOOKUP will use the next lower value (4 or 3…). You cannot specify that you want 5 and nothing else. LOOKUP will also use the next value down alphabetically when you work with strings.
LOOKUP also works with strings (text) but you have to use double quotes:
=LOOKUP(” John” ,A1:A6,B1:B6)
LOOKUP also works with references (addresses) which is interesting when you work with dates as condition:
=LOOKUP(J20,A1:A6,B1:B6) means look up for the date in J20 in cells A1 to A6 and return the value found in cells B1 to B6.
A TIP: Why not use A100 and B100 instead of A6 and B6 so that you can add information in your table (new clients, new accounts…)
Working with horizontally with rows
=LOOKUP(5,A1:H1,A5:H5) looks up for 5 in A1:H1, and returns the value from A5:H5 that’s in the same column as the 5. If 5 is not found LOOKUP will use the next lower value (4 or 3…). You cannot specify that you want 5 and nothing else. LOOKUP will also use the next value down alphabetically when you work with strings.
LOOKUP also works with strings (text) but you have to use double quotes:
=LOOKUP(” John” ,A1:H1,A5:H5)
LOOKUP also works with references (addresses) which is interesting when you work with dates as condition:
=LOOKUP(J20,A1:H1,A5:H5) means look up for the date in J20 in cells A1 to H1 and return the value found in cells A5 to H5.
HLOOKUP Function In Excel
HLOOKUP allows to look for a value in a row based on a certain value in another row. So you look up for ” John” in row 1 and you get his address in row 2.
HLOOKUP (working with rows)
IMPORTANT NOTE: The values within which you are looking up MUST BE IN ASCENDING ORDER (1, 2, 3, 4… or a, b, c, d). This is one of the reasons I switched to the magic function and INDEX/MATCH.
Here is a basic HLOOKUP formula: =HLOOKUP(2,A1:G32,4, FALSE)
The syntax for these formulas is as follow, the first argument (2) is what you are looking for, the second argument (A1:G32) is the range you are looking up within, the third argument (4) is the row from which the answer is extracted, and the fifth argument (FALSE) tells Excel that you are looking for an EXACT match and not the next lower value. If you omit the fifth argument or use TRUE the formula will return the answer for the next lower value if it doesn’t find EXACTLY what you are looking for. For example if you are looking up for Peter and the formula only finds Albert and Suzan it will return the answer for Albert. If you are looking up for 32 and Excel finds only 24 and 56 the formula will return the answer for 24.:
=HLOOKUP(2,A1:G32,4, FALSE) looks for ” 2″ in the first row (1) of the range (A1:G32) and returns the value of the cell in the 4th row (4) same column.
You can also use the address of a cell in which you change the value as first argument in the formula (ex: J20)
=HLOOKUP(J20,A1:G32,4, FALSE) looks for whatever value is in cell J20 in the first row (1) of the range (A1:G32) and returns the value of the cell in the 4th row (4) same column.
Remember to ALWAYS use ” FALSE” as fifth argument.
HLOOKUP is always looking up within the first row of the table that you submit as second argument. If you want the third row to be the look up column you need to change the second argument to a3:G32 and the third argument to 2.
=HLOOKUP(J20,C1:G32,2, FALSE)
A TIP: Why not use K2000 instead of G32 so that you can add information in your table (rows and columns of data) (new clients, 2nd phone number, email address, etc…) without having to change all the formulas that refer to the table.
Array Formulas in Excel
For years I have been promoting the use of ARRAY FORMULAS (SUM) in Excel spreadsheets as the magic formula that solved all the problems when organizing data into reports. When I discovered the SUMPRODUCT formula, I completely abandoned the ARRAY FORMULAS. The SUMPRODUCT formula does the same thing as the ARRAY FORMULA (SUM) but is more accessible to all and much easier to use.
Array formula have been created when computers were much less powerful and memories much smaller. Computers have evolved so much that array formulas are not really useful anymore. Some of them though are indispensable. Here are two examples of things you cannot do without using array formulas.
Before getting into array formulas be sure that you have discovered the extremely powerful SUMPRODUCT (Chapter 13) and INDEX/MATCH (Chapter 14).
MAX
To enter an array formula in a cell you don’t simply use the “Enter” key but you hold the SHIFT and CTRL keys while you click on ENTER. If you do it right you will see special parentheses appear at each end of the formula { }.
Among the functions that are used in array format are MAX and MIN. For example:
In the table above when is the last date at which John Spencer reported? 5/11/2006 The array formula for that is:
{=MAX((A2:A14=”John” )*(B2:B14=”Spencer” )*(C2:C14))}
What is the maximum weight that John Spencer has reached? 162
{=MAX((A2:A14=”John” )*(B2:B14=”Spencer” )*(D2:D14))}
INDEX/MATCH
INDEX/MATCH formulas by themselves are very powerful, less limited and much easier to handle that VLOOKUP. But there are some things that even INDEX/MATCH alone cannot do.
Reporting with Excel
General Tips
To design great reports you need to dsign great analyses. As a first step you validate the data on the data sheet using the database functionalities. Then you validate the reports using the calculator.
Once the data and the reports are validated you can then print them or publish them.
Dynamic Reports
Using drop-down lists (see chapter 1B) you can create a single report for 50 departments and by changing the value in the drop-down list you show the numbers for each department. Then you print it or extract the sheet and send it to the appropriate recipient. See the workbook “excel-example-dynamic-report.xls“.
Drop “Power Point”
In Excel you can add a map of USA and when you click on one of the States you are taken to the sheet that shows the table of data for the selected State. It is called image map. See it at work in the downloadable workbook”excel-example-imagemap.xls” (see the advertisement below).From then on you can forget about learning Power Point. You can develop a real show with Excel and because it is in Excel it looks more credible than in Power Point.
Extracting Reports
To extract the report from a workbook developed with the “Datasheet Approach” you right click on the tab of the report sheet. The following window appears:
DO NOT FORGET to check the “Create a copy” check box. In the “To book:” text box select (new book) and click “OK” . The in this new workbook you want to replace all the formulas by values to avoid having outside links. Select all the cells by clicking on the small square between the row numbers and the columns letters:
Click on the “Copy” icon then go to the menu “Edit/Paste Special” select “Values” and click “OK” . Select cell A1 and save the new workbook with an appropriate name.
Printing Reports
One of the great functionality in Excel allows you to set the size of your report so that anybody with any printer will get the same output. Go to “File/Page Setup/Page” :
Use the “Fit to:” section. You can force the report to print on a single page (1-1) or on many pages (1-22). You can also use the “Adjust to:” . No other program offers you this flexibility.
Excel Drop-Down Lists
Creating a drop-down list in one or many cells of an Excel worksheet is simple, very easy and very useful. With drop-down lists there is no need to key in the same values manually time and time again and the spelling is always right.
Note: The Excel drop-down lists presented below can be created by anybody on regular Excel worksheets. As for drop-down lists for programmers see the ComboBox in the VBA (macros) tutorial presented on the website www.excel-vba.com.
Excel drop-down lists are used extensively when there is a need for users to enter data in an Excel database. It insures that the values are valid and that the spelling is right. DDLists protect the integrity of the databases.
Excel drop-down lists are also used to develop questionnaires that users complete. By using drop-down lists a valid answers is always supplied because the drop-down list limits them to a choice of preset answers.
First method: Open a new workbook and select cell C1 in the first worksheet. Go to the menu bar and select ” Data/Validation” the following window appears:
In the ” Allow” text box select ” List” and the ” Source” text box appears:
In the “Source:” text box write YES,NO separated by a comma and click on “OK” . You now have a drop-down list from which you can select either “YES” or “NO. This is very useful when you create a questionnaire in Excel and the user is expected to answer exclusively by “YES” or “NO”.
Cells addresses as source for Excel drop down list
In the “Source” text box submit a range of cells where you maintain your list. ex(=$G$1:$G$23). Don’t forget the equal sign (=) and the dollar signs($). If you don’t enter the dollar signs and you copy you DDList it will offer values in range G1:G23 in the first one then G2:G24 in the cell below and G3:G25 in the next…
Your list can be dynamic. For example if you list comprises 5 names you would submit range G1 to G6 and if you want to add a sixth name you will need to go back to your DDList and change the range. To avoid this irritant set the range to G1:G25 even if you have only 5 names. From then on you can add 20 names to your list and not have to change the DDLists.
Another irritant appears when you submit a range with many empty cells. When you click on the arrow of the DDList the first empty cell is selected and all the names might not be visible within the DDList unless you use the scroll bar . To avoid this problem submit range G1:G25 but start entering names in G2. The problem is solved because when you click on the small arrow cell G1 is selected within the list and all the names are visible (unless you have more than 8 in which case you need to use the scroll bar.
Excel Conditional Formatting
Let’s say that you want to attract attention to certain data in your reports or databases. You would like that when the value of a cell is greater then $50 the font turns blue and the background color changes to green. You need “Conditional Formatting” .
Select the cell or the cells where you want this to happen go to the menu bar Format/Conditional Formatting and the following window appears (may vary depending on the Excel version that you use).
Select “greater than” in the second text box, enter 50 in the third text box and then click on the “Format” button. Choose blue as color for the font and select the “Patterns” tab and select the color green. Now if you write any number greater than 50 in one of the formatted cells the color of the font and of the background will change. If the value is smaller than 50 the default format is selected.
When you don’t want zero values to appear in a worksheet, you go to “Tools/Options/General” and remove the check for “Zero Values” but if you want not to see only the zero values within the table and not the total cells, select the interior of the table and use a conditional format that says “Cell value/Equal to/0” and then select the color white for the font.
With more and more reports being read on screen the Excel conditional formatting is an interesting tool.
Hyperlinks in Excel
More and more Excel is used instead of Power Point to develop nice presentations. Using Excel for presentations adds credibility to your show it looks less like bells and whistles.
In this era of resource conservation you also need you to make your reports user friendly on screen.
Finally, a report that is well designed and easy to walk through will deserve more attention.
For these reasons hyperlinks become an interesting feature. Like when you are surfing the WWW, you click on a word, an image, a text box or anything else and you are taken somewhere else. This technology is called hyperlinks and it exists within Excel.
With these hyperlinks you can surf from one sheet of the workbook to the other, from one workbook to another (opened or not) and you can open any other file created with any other program (Word, Internet Explorer, etc.).
You can create hyperlinks in Excel and attach them to images, text boxes and any other objects. We strongly suggest that you never create hyperlinks attached to words and cells. They are a mess to manage.
Add text boxes, images or autoShapes in your sheets and attach hyperlinks to them. If you start using a lot of these gizmos make the “Drawing” toolbar visible in Excel. Go to “View/Toolbar/Drawing” . The tool bar will become visible at the bottom of you Excel screen. Click on “AutoShapes” and select one or click on the text box icon go to the sheet and stretch the item. See lesson 2-5 for more information on text boxes and other objects.
To create an hyperlink you select the object, you go to the hyperlink icon and follow the instructions.
You can even open your email program within Excel with an address and a subject already in place.
Text Boxes in Excel
Let’s start this lesson on text boxes by talking about the MERGED CELLS. You want to write the word “Invoice” and you want this word to be centered within cells A2 to D2 like below. You select cells A2 to D2, you go to “Format/Cells/Alignment” and you check the box for “Merge Cells” and you select “Center” in “Horizontal” …and you end up with a major problem trying to modify the width of all 4 columns A to D. As much as possible avoid merging cells. Enter “Invoice” in cell “A2” then select cells A2 to D2. Go to “Format/Cells/Alignment” an in the “Horizontal” text box select “Center Across Selection” . The effect is the same as with merging cells but you are now able to modify the width of all four columns without problems.
Now if you look at the image above you see that the height of row 2 is set by the largest font on the row. You end up with this same height in cell F2 even if what you need to enter is in a much smaller font. Also you could not enter two pieces of information and keep an interesting layout like in the image below.
Notice that all rows have the same height in this example. It is because the word “Invoice” has not been entered in a cell but in an invisible text box. Using these invisible text boxes make developing forms much easier. You can use the font that you want and you can move the text boxes around and adjust their position for much better layouts.
The invisible text boxes
When you want to work with text boxes and other drawings you need to make the “Drawing” toolbar visible at all time. Go to “View/Toolbars” and select “Drawing” . This toolbar becomes visible.
If it is not automatically installed at the bottom of your screen, drag it there. Click on hold and drag.
You are now ready to work with text boxes and other drawings. Click once on the text box icon. Go to the sheet click, hold and stretch a text box. And you get this:
When the editing border of the text box is a series of forward slashes you are in the text editing mode. You can enter text, copy/paste text, format the font… Right click INSIDE the text box and see the options.
If you click on the editing border it becomes a set of dots meaning that you are now editing the text box itself.
To make the text box invisible right click on the editing border, select “Format Text Box” then go to “Colors and Lines” , in “Fill/Color” select “No Fill” and in “Line/Color” select “No Line” .
The text box is an indispensable tool when reporting with Excel. You should always have the “Design” toolbar below the sheet window.
Excel Charts
Excel is the most user friendly chart creator.
In the example below you select cells A1 to C6 and you click on the chart icon of the toolbar
The “Chart Wizard” dialog window appears. You select “Column” in “Chart Type”, you select a “Chart sub-type” and you click on “Finish”. You resize the chart and set it where you want it.
Now right click in the white space around the chart. A menu appears. Select “Format chart area/Properties” and check the “Don’t move or size with cells”.
Now hide row 2 and notice that the chart shows only the data for the rows that are visible (see below). This tip can be useful for making your point in an on screen presentation.
To delete the chart just select it and click on “Delete”.
If you choose to create a “Line” chart and some data are missing yet you don’t want the lines to be broken. Go to the menu bar “Tools/Option/Charts and select “Interpolated.
Comparative Analysis in Excel
Analyzing data can mean comparing the same data from two different sources. In this lesson we look at the functionalities that allow the user to view ON THE SAME SCREEN two or more documents to compare the data in them. You can use the Calculator to validate some totals from the data sheet and the report sheet on the same screen.
Comparing two different workbooks
In Excel you can open more than one workbook. To see them side by side go to the menu bar “Window/Arrange” you now have the choice of viewing all the open workbooks using different arrangements. The most commonly used are horizontal and vertical. You can just look at the data or copy/paste from one to the other. When you want to go back to a single workbook on the screen click on the ” Maximize” icon of the workbook that you want to work in.
Comparing two sheets from the same workbook
You might also want to compare two sheets from the same workbook. To do so you must open other instances of the same workbook. To do so go to the menu bar “Window/New Window” . Once you have done so click again on “Window” and you will see (at the bottom of the list of items) that two instances of the same workbook are open.
You can see that two copies of ” my workbook.xls” are open and that instance # 2 is on screen (the check mark). At this point anything you change in one instance is AUTOMATICALLY changed in all the open instances. You can close the instances like you close any other workbook and it is only when you try to close the last instance that Excel will ask you if you want to save the changes.
To look at a 2 different sheet from your workbook you open two instances, you arrange them as explained in the section above and you select a different sheet in each sub-window.
Use the calculator (lesson 1) to validate your reports. For example, you look at the database sheet in one sub-window and at a report sheet in the other. You use the calculator on the database to validate the results of the report.
Comparing two different files (Excel, Internet and Word)
Sometimes you want to compare data from a Word document or from some website on the Internet with what you have in Excel. You might just want to look at both documents on the same screen or copy/paste from one to the other.
To do so open Excel and the other document (with Word or you browser). Go to the “Start” bar at the bottom of the screen. Right click on the empty space (where we have added asterisks):
and a special menu comes up:
Select “Tile Windows Vertically” and you see both documents at the same time.
Rounding Off a given number to it’s nearest 10th Place
To solve this problem we need to perform two steps:
Step 1: Find the digit at the unit’s place
Step 2: Compare this number with 5 i.e. this number is less than or not. If digit at unit’s place is 5 or more, we need to increase the number to its next 10th place e.g. 4475 or 4478 needs to be converted into 4480 and 4473 or 4474 needs to be converted into 4470.
For performing these two steps, we can have formula in one cell or in two different cells as a combination of the two.
Assume that the value needs to be rounded off is in cell A4
Formula 1 (in cell B4): Find the digit at unit’s place
=MOD(A4,10)
Formula 2 (in cell B1): Compare the unit’s place digit with 5 and then increase / decrease the value to it’s nearest 10th place
=IF(B4<5,(A4-MOD(A4,10)), (A4-MOD(A4,10)+10))
OR
The direct formula could be written as
=IF(MOD(A4,10)<5,(A4-MOD(A4,10)), (A4-MOD(A4,10)+10))
Comparing two series or highlighting the contents of series 1, if those contents are present in series 2.
This is a case of Conditional Formatting, because you are required to highlight the contents of list1 in case those elements are present in list2.
This problem could be solved by any of the below given methods. What we need to do is to check whether the elements in list 1 are appearing in list 2 or not.
Solution 1: We can use VLOOKUP to check the presence of list 1’s element in list 2.
Solution 2: We can use MATCH to check the presence of list 1’s element in list 2.
Solution 3: We can use COUNTIF to check the presence of list 1’s element in list 2.
Concept behind these functions: Here we are searching for the element of list1 in list2 with the help of VLOOKUP / MATCH / COUNTIF functions. So whenever VLOOKUP / MATCH / COUNTIF give some result from list2, conditional formatting will consider the result as true and that particular element will get highlighted. However, if VLOOKUP / MATCH / COUNTIF doesn’t find any match of the element from list1 it will give result as #N/A / #N/A / 0, which is a FALSE result for conditional formatting and that’s why the element will remain as before.
Naming Ranges
A Defined Name is a text descriptor that you can use to describe the meaning of or content of a cell, a range of cells, a constant, or a formula. Once the Defined Name is established, you can use that Name wherever you would ordinarily use the actual cell address or other content. This makes the formulas much easier to understand and maintain.
For example, the formula “=G12*K15” isn’t very meaningful unless you happen to know the meaning of cells G12 and K15. However, if you assign Defined Names to G12 and K15, you can write your formula similar to the following:
=Income*TaxRate
Clearly, using the Defined Names makes the formula much easier to understand and maintain.
Creating A Defined Name
There are two ways of creating a Defined Name (not including doing so with VBA code). The first method is to select the cell or range of cells to which you want to assign a Name. Then, click in the Name Box and type the Name. The Name Box is the white rectangle to the left of the “A” column heading and above the “1” row heading. If you type in the Name Box a Name that is already defined, Excel will display the range referenced by that Name. It will not overwrite and change the existing Defined Name. The second way to create a Name is to select the cell or range of cells to which you want to assign a Name, go to the Insert menu, choose Name, then Define. This will display the Defined Names dialog box. There, type in the Name you want to use. In that dialog, you can also change the cell(s) to which an existing Name refers.
A Defined Name must begin with a letter or an underscore ( _ ) and consist of only letters, numbers, or underscores. Spaces are not permitted in a Defined Name. Moreover, a Defined Name may not be the same as a valid cell reference. For example, the name AB11 is invalid because AB11 is a valid cell reference. Names are not case sensitive.
CAUTION: If you are working on a workbook that will be used in both Excel 2003 and Excel 2007, be aware that many Names that are valid in Excel 2003 are invalid in Excel 2007. This is due to the increased number of columns in Excel 2007. For example, in Excel 2003, the Name ABC1 is valid because it is not a cell reference. However, in Excel 2007, ABC1 is, in fact, a valid cell reference, so the Name is invalid.
No duplicity allowed for Named Ranges
Shortcut keys
| Key | Alone | Shift | Ctrl | Alt | Shift Ctrl |
| F1 | Help | What’s This Help | Insert Chart Sheet | ||
| F2 | Edit Mode | Edit Comment | Save As | ||
| F3 | Paste Name Formula | Paste Function | Define Name | Names From Labels | |
| F4 | Repeat Action | Find Again | Close Window | Quit Excel | |
| F5 | Goto | Find | Restore Window Size | ||
| F6 | Next Pane | Prev Pane | Next Workbook | Switch To VBA | Prev Workbook |
| F7 | Spell Check | Move Window | |||
| F8 | Extend Selection | Add To Selection | Resize Window | Macro List | |
| F9 | Calculate All | Calculate Worksheet | Minimize Workbook | ||
| F10 | Activate Menu | Context Menu | Restore Workbook | ||
| F11 | New Chart | New Worksheet | New Macro Sheet | VB Editor | |
| F12 | Save As | Save | Open | ||
| A | Select All | Formula Arguments | |||
| B | Bold | ||||
| C | Copy | ||||
| D | Fill Down | Data Menu | |||
| E | Edit Menu | ||||
| F | Find | File Menu | Font Name | ||
| G | Goto | ||||
| H | Replace | Help Menu | |||
| I | Italics | Insert Menu | |||
| J | |||||
| K | Insert Hyperlink | ||||
| L | |||||
| M | |||||
| N | New Workbook | ||||
| O | Open Workbook | Format Menu | Select Comments | ||
| P | Font Size | ||||
| Q | |||||
| R | Fill Right | ||||
| S | Save | ||||
| T | Tools Menu | ||||
| U | Underline | ||||
| V | Paste | ||||
| W | Close Workbook | Window Menu | |||
| X | Cut | ||||
| Y | Repeat Active | ||||
| Z | Undo | ||||
| ` (~) | Toggle Formula View | General Format | |||
| 1 (!) | Cell Format | Number Format | |||
| 2 (@) | Toggle Bold | Time Format | |||
| 3 (#) | Toggle Italics | Date Format | |||
| 4 ($) | Toggle Underline | Currency Format | |||
| 5 (%) | Toggle Strikethru | Percent Format | |||
| 6 (^) | a | Exponent Format | |||
| 7 (&) | a | Apply Border | |||
| 8 (*) | Outline | Select Region | |||
| 9 (() | Hide Rows | Unhide Rows | |||
| 0 ()) | Hide Columns | Unhide Columns | |||
| – | Delete Selection | Control Menu | No Border | ||
| = (+) | Formula | Auto Sum | Insert dialog | ||
| [ | Direct Dependents | Direct Precedents | |||
| ] | All Dependents | All Precedents | |||
| ; (semicolon) | Insert Date | Select Visible Cells | Insert Time | ||
| ‘ (apostrophe) | Style | Copy Cell Value Above | |||
| : (colon) | Insert Time | ||||
| / | Select Array | Select Array | |||
| \ | Select Differences | Select Unequal Cells | |||
| Insert | Insert Mode | Copy | |||
| Delete | Clear | Delete To End Of Line | |||
| Home | Begin Row | Start Of Worksheet | |||
| End | End Row | End Of Worksheet | |||
| Page Up | Page Up | Previous Worksheet | Left 1 screen | ||
| Page Down | Page Down | Next Worksheet | Right 1 screen | ||
| Left Arrow | Move Left | Select Left | Move Left Area | ||
| Right Arrow | Move Right | Select Right | Move Right Area | ||
| Up Arrow | Move Up | Select Up | Move Up Area | ||
| Down Arrow | Move Down | Select Down | Move Down Area | Drop down list | |
| Space Bar | Space | Select Row | Select Column | Control Box | Select All |
| Tab | Move Right | Move Left | Next Window | Next Application | Previous Window |
| BackSpace | Goto Active Cell |
Analytics-vs 