EXCEL

to perform a data validation

(Data→Data Tools→Data Validation)

Data validation allows you to:

-Decrease the amount of bad data entered into the worksheet (garbage in, garbage out) -Set parameters over required data

How to insert a pivot table:

-first you have to identify which data you want to summarize. -Select the data that you want to summarize -Then click on 'Insert'→'Pivot Tables'. Check to make sure that the range selection is correct. Once you are satisfied with both the range and where the new pivot table will be created, click 'Ok'.

SUMIF

a function that sums a range of cells based on a single criterion.

Concatenate

EX: put first and last name into 1 cell instead of being in 2 separate cells

An instance where text to columns would be used:

For example, if there is one cell that contains both first and last name of an individual you can separate the first and last names into separate columns by using 'text to columns'.

For instance assume a Professor wanted to see which students passed or failed a course. They would use an ........ to easily get the result.

IF function The IF function tells Excel to say "PASSED" in cell D2 if the student received a grade greater than or equal to the value in B2 and return "FAILED" if they received a score less than the value in cell B2.

The OR function is used in conjunction with the

IF statement

The AND function is used in conjunction with the

IF statement.

How to insert a pivot chart:

In the 'Pivot Table Tools' menu, under 'Analyze' tab, select 'Pivot Chart'

To insert a relative cell reference:

In the cell that you want the value or data to be referenced in, click the = sign and then click on the cell that you want to reference

The way to check on the cells included in a named range is to go to the ....... . There you can see what cells are included in the named ranges or named cells and you can edit here if need be.

Name Manager (Formulas/Name Manager)

IF FUNCTION

Returns a response based on specified criteria. If a cell meets the specified criteria, the IF function returns the value_if_true argument. If a cell does not meet the specified criteria, the IF function returns the value_if_false argument.

For example, it is required to find the total amount per region. We can use the ....... function because there is only one criterion - the region.

SUMIF

logical functions

TRUE/FALSE IF OR AND IFERROR COUNTIF & COUNTIFS SUMIF & SUMIFS

OR FUNCTION

Tests whether the logical arguments are TRUE or FALSE. If any are TRUE, the OR function returns TRUE. If all are FALSE, the OR function returns FALSE.

AND FUNCTION

Tests whether the logical arguments are TRUE or FALSE. If they are all TRUE, the AND function returns TRUE to the cell. If any are FALSE, the AND function returns FALSE.

IFFERROR FUNCTION

Tests whether the value expression is an error. IFERROR returns value_if_error if the expression is an error, or value of the expression if it is not an error.

COUNTIF & COUNTIFS FUNCTION

The COUNTIF function counts cells out of the selected range of cells that meet identified criteria.

How to name a range of cells:

To name a range of cells, simply select all the cells in the range and in the name box enter in what you want the cells to be called. For example, using the sales tax rates from Lesson #2, let's select all of the counties and tax rates and label them the 'salestaxtable'. NOTE: you cannot leave spaces between words in your name box. It needs to be one continuous word or separated by underscores.

how to name a cell

To name an individual cell, simply select a cell and in the name box enter in what you want the cell to be called. For example, in cell A1 we will name it as taxrate. NOTE: you cannot leave spaces between words in your name box. It needs to be one continuous word or separated by underscores.

mixed referencing

When you want to lock either the row or the column, but not both $A2 A$2

Some benefits of using COUNTIFS:

You have more than one condition or criteria that needs to be evaluated to a perform a count

SUMIFS

a function sums a range of cells based on multiple criteria

Data validation

allows you to create an Excel spreadsheet with input controls, to decrease the risk of incomplete or inaccurate data

h-lookup function

allows you to match records to each other by using a look-up value when data is in columns (horizontal position)

v-lookup function

allows you to match records to each other by using a look-up value when data is in rows (vertical position)

PIVOT TABLES

allows you to summarize and analyze data, allowing you to reorganize data by dragging fields, by showing or hiding fields, modifying the level of detail shown, or the way in which items are shown in the pivot table.

pivot charts

allows you to visually display the data summarized in a pivot table

To insert an IF function

click on functions, and search for 'IF'. You will notice that the function allows you to enter in a logical test, followed by what value to enter if the logical test is met and what value to enter if the logical test is not met.

If you are unsure if your name (as you try to name a cell or a range of cells) was accepted, simply

click on the arrow in the name box and it will show you all named cells and ranges.

If you add a row or column to a named range, will it automatically be included in the named range?

it depends. It depends if you add a row or column to the middle of the named range or to the beginning or end.

when using the AND function with an IF statement, you need to

manually enter in the 'AND' function into the toolbar.

when using the OR function with an IF statement, you need to

manually enter in the 'OR' function into the toolbar.

The COUNTIFS function counts the values in cells based on

multiple conditions or criteria that you specify.

cell reference

refers to a cell or a range of cells on a worksheet and can be used in a formula so that the value or data in the cell can be used to perform a calculation

The IFFERROR function allows Excel to

replace error warnings with different values. For instance, assume a company was trying to figure out the cost per unit of a good in inventory. By taking the total value (Column A) divided by the total quantity on hand (Column B), they would be able to determine the average cost per unit.

lookup function

returns a value from a range of either one row or one column (called a vector), or from an array. With the vector form, you assign a specific range for Excel to find the value from. With the array form, Excel automatically picks the corresponding value from the last row or last column.

For the SUMIFS function, we would enter

the range that needs to be summed first and then list out different criteria

The true false logical functions return the word "..." when a statement in true and "..." when a statement is false.

true false

Text to columns

used for copying data from data files (.dat), from notepad files, or text files (txt). The data in this file can be copied into excel and formatted so that the data is separated by column

absolute reference

used to lock the exact address of a cell, regardless of the position of the cell that contains the formula. example is: =$A$3

How to use the SUMIF function

you need to identify the range of the criteria first, so we would select the range of cells that contain the region information. Next, out of the region list we need to indicate which particular region we are required to summarize the sales. In this step we can choose the cell with particular region out of the region list, or we can type the required region in brackets. Then, select the range that needs to be summed, which is "Amount" column in this case.

Naming of cells and ranges of cells in Excel is used to:

•Allow for easier input of complex formulas •Eliminates the need for absolute referencing

Instance where an absolute reference would be used:

•If there is a constant in a calculation throughout a spreadsheet ---Such as an interest rate on a loan or monthly amount of depreciation

Additional things that can be performed under data validation:

•Limit the numerical values entered (whole numbers, decimals, etc.) o Require all currency entries to have 2 decimal places o Require all general ledger numbers to be a whole number •Allow date and time to be within an acceptable range o For transactions, need to use the current month •Set the text to be a certain length o Zip code is 5 digits (see example below) o Phone number is 10 digits •As previously covered in this lesson, we can add a list of acceptable choices o Refer to drop down list above •Also, there is an option to enter in a custom formula

A drop down list in Excel is used to:

•Limit what data can be entered, such as general ledger account number, customer name, etc. •Allow the user to select an option from only the available choices •Put parameters on the data that can be accepted if the user can type in the field

Instances where a v-lookup would be used:

•Match data from one location to another, based on a defined set of criteria --For example, you can calculate sales tax for a sales transaction by using a sales tax rate table by matching the state in the sales transaction to the state in the sales tax table --You can determine a grade for students based on their average in comparison to the grade rubric.

Instances where a cell reference would be used:

•Reference cell's data from one cell to another cell (or another worksheet) •Reference a total from one cell to another cell (or another worksheet) --Total from an A/R subsidiary ledger referenced to the balance sheet

Instances where a pivot table would be used:

•Summary of financial and other types of data •High level summary of data •Detailed level summary of data •Ability to change parameters of the pivot table with minimal effort

Instances where a logical function would be used, to name a few:

•To determine if the data housed in a cell meets certain criteria; •To determine results based on specified criteria; •To provide efficiency in data collection

What a V-Lookup can do that a Lookup cannot

•Use TRUE/FALSE in the function to tell Excel whether it must find an exact match or if it can be close and still pull the corresponding value. •Tell Excel which column or row specifically to pull the corresponding value from, rather than it automatically pulling from the last column or last row.