- Excel tutorial: how to do a case sensitive VLOOKUP
- Tutorial : How to convert Excel tables to HTML
- Excel Tutorial : How to count and add cells by color
- Excel tutorial: How to use search and replace functions
- Tuto Excel: enregistrer une seule feuille d'un classeur
- Excel tutorial: Insert and delete page breaks in a spreadsheet
- Exercice d'application langage C -3
- Exercice d'application langage C -1

**Tutorial responsive accounting application with advanced excel**

...

Formulas

VLOOKUP

The VLOOKUP function searches vertically (top to bottom) the leftmost column of a table until a value that matches or exceeds the one you are looking up is found.

The elements being looked up must be unique and must be arranged or sorted in ascending order; that is, alphabetical order for text entries, and lowest-to-highest order for numeric entries.

The syntax is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).

An example of the formula is: VLOOKUP(E2,D2:M3,2,TRUE) The English translation is using the value found in the cell E2, look in the range of D2 to M3 row by row. If you find a value that matches or exceeds the value in E2, using that row, go over 2 columns to the right, grab the value there and bring it back.

There are two range_lookup argument options; TRUE or FALSE

TRUE

Is the default answer, so you may leave it out of the formula Looks for an approximate match

If it finds an exact match it will use it.

If it doesn’t find an exact match, it will use the last item before it got greater

Alphabetical: Looking for Cat. If elements are Apple, Bird, Carpet, Dog; then Carpet would be returned because Dog exceeds Cat alphabetically.

Numeric: Looking for 5.25. If elements are 3.0, 4.0, 5.0, 6.0, 7.0, then 5.0 would be used.

The last number before 5.25 was exceeded.

FALSE

Looks for an exact match.

If it finds an exact match it will use it.

If it doesn’t find an exact match, it will return #N/A

Alphabetical: Looking for Cat. If elements are Apple, Bird, Carpet, Dog; then #N/A would be returned.

Numeric: Looking for 5.25. If elements are 3.0, 4.0, 5.0, 6.0, 7.0, then #N/A would be returned because there is no exact match.

...

COUNTIFS

Recall quickly the COUNT and IF commands.

COUNT

The COUNT function counts the number of cells that contain numbers and counts numbers within the list of arguments.

The syntax is COUNT( value1, value2, …)

Continuing on with our SUM formula from above, let’s not only add up the values of the range A1:A4, but let’s count how many numbers are included within the range, i.e. how many cells within the range has a value in it.

The formula is =COUNT(A1:A4). The English translation is count how many cells within the range has a value in it and display the result.

Notice that the range is exactly the same as our SUM, A1:A4, which includes four rows. The value returned in cell A7 is three, because only three of the four rows have values in them.

...

If you are trying to count text, use the COUNTA formula which counts the non-blank cells.

IF

The formula makes a statement/question, if the answer is true then one response is obtained. If the answer if false, then another answer is obtained.

The syntax is =IF(logical_test,value_if_true,value_if_false)

Continuing on with our SUM formula from above, let’s add some verbage to emphasize whether the result is greater or less than twenty.

The formula is =if(A5<20,”Amount is less than twenty”,”Amount is more than twenty”). The English translation is if the value found in A5 is less than twenty THEN display the comment ‘Amount is less than twenty’ ELSE display the comment ‘Amount is more than twenty’.

COUNTIF

The COUNT function counts the number of cells in a range, that meets single criteria.

COUNTIFS

The COUNT function counts the number of cells in a range that meets multiple criteria.

Filters

Ribbon Tour

Quick Filtering

The secret to filtering is not to have a space between your titles and your data. In fact, Excel is so smart, that you do not even have your data selected, but may if you prefer.

Select your data and left click on the filter icon in the Sort & Filter

Group.

Notice that a chevron appears to the left of each header.

By selecting the chevron to the left of Vendor Name, a dialog box appears displaying all unique text filters found in the range as well as other common sort icons.

If you only want a particular filter, deselect the (Select All) box and check the filter you desire.

In the below screen shot, Kendell Kilborn is selected. Notice the hidden rows to the left. Those represent data lines for mileage paid to individuals other than Kendell. No data is lost, it is just currently hidden.

Also note that the icon to the left of the vendor name now displays the filter icon.

This so at a glance the user may see that the data range has been filtered.

Filtering by Multiple Criteria

The filtering tool is fine when you only want one item. However the power of the advance filter tool really shines when you want to sort by multiple criteria. There are several thou shalts of advanced filtering.

Thou Shalts of Advanced Filtering

1 The headers in the criteria range must be exactly as they are in the list range

l 2 There must be at least one blank row between the criteria range and the list range

Steps For Advanced Filtering

Create a criteria range by inserting a few rows and copying the header from the data range.

Although not required, it is often best to have the range above your data for simplicity.

Type in the criteria you want to filter by.

Have your curser somewhere in the data range

Select the Advanced icon with your left mouse button.

The list range most likely will be your data. If not, you will need to correct it.

Select your criteria range.

- The range must include the headers of the criteria range
- The rows with criteria
- All columns in the range

Select OK

:

The results appear below.

Saving the Filtered Data

Now that the data has been filtered it would be great to save it so you can manipulate it further. To do so is a rather straight forward process. Basically you will go to where you want to save it, Sheet2 in our example, and go through the filtering process that we did above with just a couple of twists.

Steps For Advanced Filtering

On the destination worksheet (Sheet2 for example) place the cursor in a blank cell.

Select the Advanced icon with your left mouse button.

Under Action, select copy to another location

- In the list range, select the range finder icon.

The appears. Navigate to the appropriate worksheet and select the data range not forgeting the headers, and click on the little icon at the bottom right.

- Do the same for the criteria range.
- For the copy to range, select the first cell and select OK

Performing Calculations on Filtered Data

Excel’s traditional formulas do not work on filtered data since the function will be performed on both the hidden and visible cells. To perform functions on filtered data one must use the subtotal function.

The syntax is SUBTOTAL(function_num, range_reference1, range_reference2,….)The following functions may be performed with the subtotal. The function_num within the syntax relates to the numbered function.

...

An example of the formula is: =SUBTOTAL(9,E12:F19) The English translation is using the ninth subtotal function, which is SUM, add up all of the data within the range that is selected by the filter.

For comparison, included is the SUM function for the same range which brought back the total for all of the data cells, hidden or displayed.

PivotTables

Defined

The foundation of what is a PivotTable report is explained as follows:

As long as you can connect to the data, whether it be locally in the same workbook or remotely in other locations, you can built PivotTable reports that rearrange the raw data and change it into meaningful information

A pivot table is an interactive way to quickly summarize large amounts of data; to analyze numerical data in detail and to answer unanticipated questions. They are especially designed for:

- Querying large amounts of data in many user-friendly ways
- Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas
- Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data.
- Moving rows to columns or columns to rows (or “pivoting”0 to see different summaries of the source data.
- Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data to enable you to focus on the information that you want.

Thou Shalts in PivotTable Land

1 Headers should be in columns, not rows

2 No blank rows between the headers and the data

3 Best to have the pivot table on a separate worksheet so it does not accidently clobber the data

4 Best to have simple data, rows and columns of data.

5 Best to format your area as a table, especially when you will be adding data to it. The table is automatically expanded when data is added to the next row. Now when you launch create a pivot table the range will be the table name, and not the cell addresses

Basic PivotTable Data

Pivot Tables work best when you have simple data in rows and columns.

- Headers are across the top in the first row
- Data consists of a single row across numerous columns
- There is not a blank row between the headers and the rows

Inserting a Pivot Table

- Select any active cell within your data table
- Navigate to the Insert tab and select

PivotTable

PivotTable Geography

When you do so, the create PivotTable dialog box appears.

- Excel will guess the data range that you will want; correct it if it is wrong.
- The default destination for the

PivotTable will be a new worksheet.

- When doing so, a new sheet will automatically be added to your workbook.
- It is good to use a new worksheet for the pivot table so that your source data doesn’t accidently get clobbered.