Excel Tips for Beginners

Excel General Tips on Excel

Double Paint Brush

Paintbrush

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

Scroll Sheets
More Sheets

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.

Excel database

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.

Excel autofilter setting

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.

Excel autofilter selection

Let’s say you select “Peter” in the drop-down list of the filter. Here is what you see:

Excel autofilter arrows

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:

Example Excel auto filters

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” .

Excel Auto Filter

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

Excel Database 1

This is not a database recognized by Excel because row 2 is not empty.

Excel Database 2

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.

Excel Database 3

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.

Excel database 4

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.

Excel Database

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.

Excel Sort Icon

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:

Excel Sort Window

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.

Excel Sum
Excel Sum

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:

Excel Calculator

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/MonthProductCityQtyAmount
JanuaryBroomsNew York5361072
FebruaryBroomsNew York7561512
MarchBrushesNew York6541308
JanuaryBrushesNew York365730
FebruaryBroomsMontreal7581516
MarchBrushesMontreal445890
JanuaryBroomsMontreal255510
FebruaryBrushesNew York6541308
MarchBroomsNew York324648
JanuaryBrushesMontreal156312
FebruaryBrushesMontreal7531506
MarchBroomsMontreal135270

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?

 MontrealNew YorkTotal
Brooms1,1481,6162,764
Brushes1,3541,6733,027
Total2,5023,2895,791

How much of each products were sold by city?

 MontrealNew YorkTotal
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:

Excel Pivot Table Window

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.:

Excel Name Box

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.

Excel define names

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.

SignsWhat 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).

SUMPRODUCT Data

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.

SUMPRODUCT Reports


** 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.

 ABDDE
1Client #NameAddressCityState & ZIP
236596Tiger Auto33 WoodsMiamiFL 10230
386598Phil Lumber555 MakesBostonMA 34567
458971David Eng.1200 DuvallCharlestonSC 10004
587456Stewart Inc.5673 PayneSan FranciscoCA 27002

Tired of typing names, addresses, cities and states on invoices?  

 AB
11Client #:86598
12Name:=INDEX(A2:E5,MATCH(B11,A2:A5,0),2)
13Address:=INDEX(A2:E5,MATCH(B11,A2:A5,0),3)
14City:=INDEX(A2:E5,MATCH(B11,A2:A5,0),4)
16State & 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.

FunctionsWhat it Does
DATEReturns the serial number of a particular date
DAYConverts a serial number to a day of the month
HOURConverts a serial number to an hour
MINUTEConverts a serial number to a minute
MONTHConverts a serial number to a month
NOWReturns the serial number of the current date and time
SECONDConverts a serial number to a second
TIMEReturns the serial number of a particular time
TODAYReturns the serial number of today’s date
WEEKDAYConverts a serial number to a day of the week
YEARConverts a serial number to a year
DATEDIFCalculates 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.

FunctionsWhat it Does
FVReturns the future value of an investment
NPERReturns the number of periods for an investment
PMTReturns the periodic payments
RATEReturns 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?

 ADescriptions
148Number of periods (years, months, weeks..etc)
2$550Periodic payment
3$24,000Total amount of loan
40The balance left to pay at the end of the period. If you omit this argument Excel uses “0”.
50Payment 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.
65.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.

FunctionsWhat it Does
CELL Returns information about formatting, location, or contents of a cell
ISERRORReturns TRUE if the value is any error value
ISNUMBERReturns TRUE if the value is a number
ISTEXTReturns 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.

FunctionsWhat it Does
ANDReturns TRUE if all its arguments are TRUE
IFSpecifies a logical test to perform
ORReturns 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.

FunctionsWhat it Does
INDEXUses an index to choose a value from a reference or array
MATCHLooks up values in a reference or array
INDIRECTReturns a reference indicated by a text value
OFFSET Returns a reference offset from a given reference
ADDRESSReturns 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.

FunctionsWhat it Does
ABSReturns the absolute value of a number
MODReturns the remainder from division
POWERReturns the result of a number raised to a power and allows the calculation of cubic root and other roots
ROUNDRounds a number to a specified number of digits
ROUNDUPRounds a number up, away from zero
SQRTReturns a positive square root
SUBTOTALReturns a subtotal in a list or database
SUMAdds its arguments
SUMIFAdds the cells specified by a given criteria
SUMPRODUCTReturns the sum of the products of corresponding array components
TRUNCTruncates 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.

FunctionsWhat it Does
AVERAGEReturns the average of its arguments
AVERAGEAReturns the average of its arguments, including numbers, text, and logical values
COUNTCounts how many numbers are in the list of arguments
COUNTACounts how many values are in the list of arguments
RANKReturns the rank of a number in a list of numbers
LARGEReturns 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 FunctionsWhat it Does
CONCATENATEJoins several text items into one text item
FINDFinds one text value within another (case-sensitive)
LEFTReturns the leftmost characters from a text value
LENReturns the number of characters in a text string
MIDReturns a specific number of characters from a text string starting at the position you specify
REPTRepeats text a given number of times
RIGHTReturns the rightmost characters from a text value
TEXTFormats a number and converts it to text
TRIMRemoves spaces from text
VALUEConverts 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)

 ABCD
1MonthStateNameAmount
2MayMIJohn2
3JunNYPeter6
4MarPAJohn8
5DecNHPeter3
6NovRIJohn4
7OctFLPeter6

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)

 ABCD
1MonthStateNameAmount
2MayMIJohn2
3JunNYPeter6
4MarPAJohn8
5DecNHPeter3
6NovRIJohn4
7OctFLPeter6

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:

Excel Array Formulas

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:

Excel move sheet

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:

Excel Select all

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” :

Excel Page Set up

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:

Excel Data Valition General

In the ” Allow” text box select ” List” and the ” Source” text box appears:

Excel data validation list

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).

Excel Conditional Formatting

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.

Text box Icon

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.

Excel Drawing Toolbar
Excel Hyperlink Icon

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.

Excel Example No Text box

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.

Excel Example Text Box

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.

Excel Drawing Toolbar
Drag

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:

Excel Text Box Inside

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.

Excel Text Box

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 Drawing Tool Bar

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.

Excel Window Maximize

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.

Excel Workbook Selector

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):

Excel Status Bar

and a special menu comes up:

Excel Split Screen Menu

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

KeyAloneShiftCtrlAltShift Ctrl
F1HelpWhat’s This Help Insert Chart Sheet 
F2Edit ModeEdit Comment Save As 
F3Paste Name FormulaPaste FunctionDefine Name Names From Labels
F4Repeat ActionFind AgainClose WindowQuit Excel 
F5GotoFindRestore Window Size 
F6Next PanePrev PaneNext WorkbookSwitch To VBAPrev Workbook
F7Spell Check Move Window  
F8Extend SelectionAdd To SelectionResize WindowMacro List 
F9Calculate AllCalculate WorksheetMinimize Workbook  
F10Activate MenuContext MenuRestore Workbook 
F11New ChartNew WorksheetNew Macro SheetVB Editor 
F12Save AsSaveOpenPrint
A  Select All Formula Arguments
B  Bold  
C  Copy  
DFill DownData Menu 
EEdit Menu 
FFindFile MenuFont Name
G  Goto  
H  ReplaceHelp Menu 
I  ItalicsInsert Menu 
J 
KInsert  Hyperlink 
L 
M     
N  New Workbook  
O  Open WorkbookFormat MenuSelect Comments
PPrintFont Size
Q 
RFill Right 
S  Save  
T   Tools Menu 
U  Underline  
VPaste 
WClose WorkbookWindow Menu 
XCut 
Y  Repeat Active  
Z  Undo  
` (~)  Toggle Formula View General Format
1 (!)Cell FormatNumber Format
2 (@)Toggle BoldTime Format
3 (#)Toggle ItalicsDate Format
4 ($)  Toggle Underline Currency Format
5 (%)  Toggle Strikethru Percent Format
6 (^)  a Exponent Format
7 (&)aApply Border
8 (*)OutlineSelect Region
9 (()Hide RowsUnhide Rows
0 ())  Hide Columns Unhide Columns
  Delete SelectionControl MenuNo Border
= (+)Formula  Auto SumInsert dialog
[Direct DependentsDirect Precedents
]All DependentsAll Precedents
; (semicolon)Insert DateSelect Visible CellsInsert Time
‘ (apostrophe)   StyleCopy Cell Value Above
: (colon)  Insert Time  
/  Select Array Select Array
\Select DifferencesSelect Unequal Cells
InsertInsert ModeCopy 
DeleteClearDelete To End Of Line 
HomeBegin Row Start Of Worksheet  
EndEnd Row End Of Worksheet  
Page UpPage Up Previous WorksheetLeft 1 screen 
Page DownPage DownNext WorksheetRight 1 screen 
Left ArrowMove LeftSelect LeftMove Left Area 
Right ArrowMove RightSelect RightMove Right Area 
Up ArrowMove UpSelect UpMove Up Area  
Down ArrowMove DownSelect DownMove Down AreaDrop down list 
Space BarSpaceSelect RowSelect ColumnControl BoxSelect All
TabMove RightMove LeftNext WindowNext ApplicationPrevious Window
BackSpace  Goto Active Cell  

IBS Gurgaon – Mid Term For IBA – Dec 2019

1.a. the difference between predictive analytics and prescriptive analytics is the outcome of the analysis. Predictive analytics provides you with the raw material for making informed decisions, while prescriptive analytics provides you with data-backed decision options that you can weigh against one another.

b. nominal data (also known as nominal scale) is a type of data that is used to label variables without providing any quantitative value. … Unlike ordinal data. One of the most notable features of ordinal data is that, nominal data cannot be ordered and cannot be measured.

c. Dataframe and list are two different data structures present in R. Dataframe is widely accepted data structure which is also present in python because of its tabular structure. The dimensions of a dataframe is represented by number of rows and columns while a list is multidimensional.

Dataframes are quite common and used as datasets in modelling and analysis purposes. But lists are very useful in heaping i.e. creating an object that has more than one different objects. Lists are heterogeneous in real sense because not only they can gave different data types like dataframes but also can have different data structures.

2. 

  1. na.omit() & na.pass() – Two functions that help with this task are is.na() which way turns a true value for every NA value it finds and na. omit() that removes any rows that contain an NA value. na.pass returns the object unchanged.
  2. tail() – show bottom 6 records of data frame by default
  3. rbind() – R rbind Function. rbind() function combines vector, matrix or data frame by rows. The column of the two datasets must be same, otherwise the combination will be meaningless.
  4. lm() – the lm(), or “linear model,” function can be used to create a simple regression model. The lm() function accepts a number of arguments (“Fitting Linear Models,” n.d.). 
  5. rep() – the function replicates its values a specified number of times.

3. a.RESULT = data.frame(

name = c(‘Anastasia’, ‘Dima’, ‘Katherine’, ‘James’, ‘Emily’, ‘Michael’, ‘Matthew’, ‘Laura’, ‘Kevin’, ‘Jonas’),

score = c(12.5, 9, 16.5, 12, 9, 20, 14.5, 13.5, 8, 19),

attempts = c(1, 3, 2, 3, 2, 3, 1, 1, 2, 1),

qualify = c(‘yes’, ‘no’, ‘yes’, ‘no’, ‘no’, ‘yes’, ‘yes’, ‘no’, ‘no’, ‘yes’)

b. summary(RESULT)

c. dim(RESULT)

d. str(RESULT)

e. RESULT<-RESULT[order(score),]

f. head(RESULT,3)

g. subset(RESULT, attempts==1 & qualify==”yes”)

h. mean(RESULT), max(RESULT), min(RESULT)

4. 

5.

Increase Efficiency in use of Excel

KeyDescription
CTRL+SHIFT+(Unhides any hidden rows within the selection.
CTRL+SHIFT+)Unhides any hidden columns within the selection.
CTRL+SHIFT+&Applies the outline border to the selected cells.
CTRL+SHIFT_Removes the outline border from the selected cells.
CTRL+SHIFT+~Applies the General number format.
CTRL+SHIFT+$Applies the Currency format with two decimal places (negative numbers in parentheses).
CTRL+SHIFT+%Applies the Percentage format with no decimal places.
CTRL+SHIFT+^Applies the Exponential number format with two decimal places.
CTRL+SHIFT+#Applies the Date format with the day, month, and year.
CTRL+SHIFT+@Applies the Time format with the hour and minute, and AM or PM.
CTRL+SHIFT+!Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
CTRL+SHIFT+*Selects the current region around the active cell (the data area enclosed by blank rows and blank columns).
 In a PivotTable, it selects the entire PivotTable report.
CTRL+SHIFT+:Enters the current time.
CTRL+SHIFT+”Copies the value from the cell above the active cell into the cell or the Formula Bar.
CTRL+SHIFT+Plus (+)Displays the Insert dialog box to insert blank cells.
CTRL+Minus (-)Displays the Delete dialog box to delete the selected cells.
CTRL+;Enters the current date.
CTRL+`Alternates between displaying cell values and displaying formulas in the worksheet.
CTRL+’Copies a formula from the cell above the active cell into the cell or the Formula Bar.
CTRL+1Displays the Format Cells dialog box.
CTRL+2Applies or removes bold formatting.
CTRL+3Applies or removes italic formatting.
CTRL+4Applies or removes underlining.
CTRL+5Applies or removes strikethrough.
CTRL+6Alternates between hiding objects, displaying objects, and displaying placeholders for objects.
  
CTRL+8Displays or hides the outline symbols.
CTRL+9Hides the selected rows.
CTRL+0Hides the selected columns.
CTRL+ASelects the entire worksheet.
 If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the current region and its summary rows. Pressing CTRL+A a third time selects the entire worksheet.
 When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog box.
 CTRL+SHIFT+A inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula.
CTRL+BApplies or removes bold formatting.
CTRL+CCopies the selected cells.
 CTRL+C followed by another CTRL+C displays the Clipboard.
CTRL+DUses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+FDisplays the Find and Replace dialog box, with the Find tab selected.
 SHIFT+F5 also displays this tab, while SHIFT+F4 repeats the last Find action.
 CTRL+SHIFT+F opens the Format Cells dialog box with the Font tab selected.
CTRL+GDisplays the Go To dialog box.
 F5 also displays this dialog box.
CTRL+HDisplays the Find and Replace dialog box, with the Replace tab selected.
CTRL+IApplies or removes italic formatting.
CTRL+KDisplays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.
CTRL+NCreates a new, blank workbook.
CTRL+ODisplays the Open dialog box to open or find a file.
 CTRL+SHIFT+O selects all cells that contain comments.
CTRL+PDisplays the Print dialog box.
 CTRL+SHIFT+P opens the Format Cells dialog box with the Font tab selected.
CTRL+RUses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
CTRL+SSaves the active file with its current file name, location, and file format.
CTRL+TDisplays the Create Table dialog box.
CTRL+UApplies or removes underlining.
 CTRL+SHIFT+U switches between expanding and collapsing of the formula bar.
CTRL+VInserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an object, text, or cell contents.
CTRL+WCloses the selected workbook window.
CTRL+XCuts the selected cells.
CTRL+YRepeats the last command or action, if possible.
CTRL+ZUses the Undo command to reverse the last command or to delete the last entry that you typed.
 CTRL+SHIFT+Z uses the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.
  
Function keys 
  
KeyDescription
F1Displays the Microsoft Office Excel Help task pane.
 CTRL+F1 displays or hides the ribbon.
 ALT+F1 creates a chart of the data in the current range.
 ALT+SHIFT+F1 inserts a new worksheet.
F2Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.
 SHIFT+F2 adds or edits a cell comment.
 CTRL+F2 displays the Print Preview window.
F3Displays the Paste Name dialog box.
 SHIFT+F3 displays the Insert Function dialog box.
F4Repeats the last command or action, if possible.
 CTRL+F4 closes the selected workbook window.
F5Displays the Go To dialog box.
 CTRL+F5 restores the window size of the selected workbook window.
F6Switches between the worksheet, ribbon, task pane, and Zoom controls. In a worksheet that has been split (View menu, Manage This Window, Freeze Panes, Split Window command), F6 includes the split panes when switching between panes and the ribbon area.
 SHIFT+F6 switches between the worksheet, Zoom controls, task pane, and ribbon.
 CTRL+F6 switches to the next workbook window when more than one workbook window is open.
F7Displays the Spelling dialog box to check spelling in the active worksheet or selected range.
 CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ENTER, or ESC to cancel.
F8Turns extend mode on or off. In extend mode, Extended Selection appears in the status line, and the arrow keys extend the selection.
 SHIFT+F8 enables you to add a nonadjacent cell or range to a selection of cells by using the arrow keys.
 CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.
 ALT+F8 displays the Macro dialog box to create, run, edit, or delete a macro.
F9Calculates all worksheets in all open workbooks.
 SHIFT+F9 calculates the active worksheet.
 CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
 CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
 CTRL+F9 minimizes a workbook window to an icon.
F10Turns key tips on or off.
 SHIFT+F10 displays the shortcut menu for a selected item.
 ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message.
 CTRL+F10 maximizes or restores the selected workbook window.
F11Creates a chart of the data in the current range.
 SHIFT+F11 inserts a new worksheet.
 ALT+F11 opens the Microsoft Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
F12Displays the Save As dialog box.
  
Other useful shortcut keys 
KeyDescription
ARROW KEYSMove one cell up, down, left, or right in a worksheet.
 CTRL+ARROW KEY moves to the edge of the current data region (data region: A range of cells that contains data and that is bounded by empty cells or datasheet borders.) in a worksheet.
 SHIFT+ARROW KEY extends the selection of cells by one cell.
 CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell.
 LEFT ARROW or RIGHT ARROW selects the tab to the left or right when the ribbon is selected. When a submenu is open or selected, these arrow keys switch between the main menu and the submenu. When a ribbon tab is selected, these keys navigate the tab buttons.
 DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open. When a ribbon tab is selected, these keys navigate up or down the tab group.
 In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.
 DOWN ARROW or ALT+DOWN ARROW opens a selected drop-down list.
BACKSPACEDeletes one character to the left in the Formula Bar.
 Also clears the content of the active cell.
 In cell editing mode, it deletes the character to the left of the insertion point.
DELETERemoves the cell contents (data and formulas) from selected cells without affecting cell formats or comments.
 In cell editing mode, it deletes the character to the right of the insertion point.
ENDMoves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on.
 Also selects the last command on the menu when a menu or submenu is visible.
 CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column. If the cursor is in the formula bar, CTRL+END moves the cursor to the end of the text.
 CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner). If the cursor is in the formula bar, CTRL+SHIFT+END selects all text in the formula bar from the cursor position to the end—this does not affect the height of the formula bar.
ENTERCompletes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).
 In a data form, it moves to the first field in the next record.
 Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.
 In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button).
 ALT+ENTER starts a new line in the same cell.
 CTRL+ENTER fills the selected cell range with the current entry.
 SHIFT+ENTER completes a cell entry and selects the cell above.
ESCCancels an entry in the cell or Formula Bar.
 Closes an open menu or submenu, dialog box, or message window.
 It also closes full screen mode when this mode has been applied, and returns to normal screen mode to display the Ribbon and status bar again.
HOMEMoves to the beginning of a row in a worksheet.
 Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.
 Selects the first command on the menu when a menu or submenu is visible.
 CTRL+HOME moves to the beginning of a worksheet.
 CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.
PAGE DOWNMoves one screen down in a worksheet.
 ALT+PAGE DOWN moves one screen to the right in a worksheet.
 CTRL+PAGE DOWN moves to the next sheet in a workbook.
 CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.
PAGE UPMoves one screen up in a worksheet.
 ALT+PAGE UP moves one screen to the left in a worksheet.
 CTRL+PAGE UP moves to the previous sheet in a workbook.
 CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.
SPACEBARIn a dialog box, performs the action for the selected button, or selects or clears a check box.
 CTRL+SPACEBAR selects an entire column in a worksheet.
 SHIFT+SPACEBAR selects an entire row in a worksheet.
 CTRL+SHIFT+SPACEBAR selects the entire worksheet.
 If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the current region and its summary rows. Pressing CTRL+SHIFT+SPACEBAR a third time selects the entire worksheet.
 When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet.
 ALT+SPACEBAR displays the Control menu for the Microsoft Office Excel window.
TABMoves one cell to the right in a worksheet.
 Moves between unlocked cells in a protected worksheet.
 Moves to the next option or option group in a dialog box.
 SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.
 CTRL+TAB switches to the next tab in dialog box.
 CTRL+SHIFT+TAB switches to the previous tab in a dialog box.