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.
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
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.
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:
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.
Inserting a Pivot Table
PivotTable
PivotTable Geography
When you do so, the create PivotTable dialog box appears.
PivotTable will be a new worksheet.