Excel Resources

Custom functions

To get into VBA mode hit ALT-F11.

Here is an 18 minute video of Jordan teaching how to create a custom function in an ATP. LINK

Advanced Functions

Descriptions

These functions are most powerful when used together like “MATCH(ISNUMBER())”.

  1. AND() – Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE
  2. OR() – Use the OR function to return a TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE
  3. NOT() – Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.
  4. COUNTBLANK() – Counts empty cells in a specified range of cells
  5. ISNUMBER() – TRUE if the value is numeric; otherwise FALSE
  6. MATCH() – Searches for a specified item in a range of cells, and then returns the relative position of that item in the range
  7. MATCH(ISNUMBER()) – Useful when you want to know if a specific number exists in a large set of data in another table, if match doesn’t find the number it will return an error, the ISNUMBER part will return false, and you will know that the data is not found in the the other table.
  8. INDEX(MATCH()) – Used similarly to VLOOKUP and HLOOKUP except you can specify which column in your array you want to search in.
  9. LEN() – Returns the length of the string
  10. MID() – Returns a specified number of characters from a starting point somewhere in the middle of a string
  11. LEFT() – Returns a specified number of characters from the left of a string
  12. RIGHT() – Returns a specified number of characters from the left of a string
  13. ROWS() – Returns number of rows in an array

Examples

AND() – Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE

See the intramural worksheet on the advanced class file. Use to determine eligibility.

OR() – Use the OR function to return a TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE

See the intramural worksheet on the advanced class file. Use to determine eligibility.

NOT() – Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

Not sure of a good example of when to use it

COUNTBLANK() – Counts empty cells in a specified range of cells

If you want to know how many times a field was left blank in rows or columns. Could use together with the ROWS function…                                                                =ROWS(range) – COUNTBLANK(range). Would give you the number of cells in the range that are not blank.

COUNTIF() – Counts cells if criteria are met

This is listed here because you can count the number of cells that contain text by doing this =COUNTIF(range,”*”). Apparently an “*” symbolizes “is text”.

ISNUMBER() – TRUE if the value is numeric; otherwise FALSE

Determines whether a cell contains a number. Used often

MATCH() – Searches for a specified item in a range of cells, and then returns the relative position of that item in the range

Extremely useful when used with other functions like INDEX() or ISNUMBER()

ISNUMBER(MATCH()) – Useful when you want to know if a specific number exists in a large set of data in another table, if match doesn’t find the number it will return an error, the ISNUMBER part will return false, and you will know that the data is not found in the the other table.

Check whether a value exists in another table, useful in a variety of circumstances. I used it in an auditing class and it saved me lots of time.

INDEX(range,MATCH(),column) – Used similarly to VLOOKUP and HLOOKUP except you can specify which column in your array you want to search in.

Looks up a value and returns a value relative to the position of the lookup value. If you wanted to return the ID number of a student by looking up the Name or find their name by looking up the number.

LEN() – Returns the length of the string

Could check to see whether people have entered too few or too many digits in a field. Also, used in conjunction with other functions like MID() can be powerful.

MID() – Returns a specified number of characters from a starting point somewhere in the middle of a string

If certain parts of ID numbers have specific meanings like the first two digits signify their home state and the middle 3 signify their eye color, you can use this function to extract certain parts of the ID number and evaluate them using =INDEX(MATCH()) or =VLOOKUP()

LEFT() – Returns a specified number of characters from the left of a string

You can use this if the numbers you want to extract are at the beginning of the string.

RIGHT() – Returns a specified number of characters from the left of a string

You can use this if the numbers you want to extract are at the end of the string.

ROWS() – Returns number of rows in an array

Use with some other functions to automate some manual processes like determining how many fields there are in a set of data.

 

Slicers

Slicers are a great way to filter your data in a Pivot Table or just a regular table. They make it easy to see exactly what is being filtered and shown in tables and graphs that correspond to the slicer.

How to Insert Slicers

To follow along with this practice, follow this link to a Google Sheet and download the file as an Excel Spreadsheet: Slicer Practice

It should look like this:

Beginning

 

In order to use slicers, you need to make this data into a pivot table or a regular table. We will make it into a regular table by highlighting the data and in the Home tab, in the formatting group, click on format as table. Select the format you want and make sure that the the “My table has headers” box is checked.

Now it should look like this:

2

 

In order to insert a slicer you have to click somewhere in the table, go to the insert tab, in the Filters group and press the Slicer button. Select what you would like to filter your table by. (You can select more than one).

We are going to filter by State as shown in the picture below:

3

 

Now let’s find all the employees that live in Alaska. Click on the state symbol “AK”. The bottons which are being filtered show up in blue. This should show you all the employees that live in Alaska along with all their information.

4

 

Next, we will show how slicers help manipulate multiple graphs at one time.