Business Info Systems: Pivot Tables

Logical "IF" Functions

"If A1 contains the formula shown, what happens to the D1 cell reference when the formula is copied from A1 to ...." •Used to evaluate data and provide conditional output •Output you see in the cell is determined based on logical test •Three arguments: •logical_test •[value_if_true] •[value_if_false] •Test results (argument 1) must evaluate to either TRUE or FALSE

Anatomy of a Cell Reference

'[workbook]worksheet'!cellreference -Examples: •=SUM(A1:A10) is assumed to be a reference to cell range A1:A10 in the current worksheet. •=SUM('StoreSales'!A1:A10) is assumed to be a reference to cell range A1:A10 in the StoreSales worksheet in the current workbook. •=SUM('[AcctDept]StoreSales'!A1:A10) is assumed to be a reference to cell range A1:A10 in the StoreSales worksheet in a workbook named AcctDept.

Cell References

-A cell reference identifies the location of a cell or group of cells in the worksheet. -Each cell can be located by means of its cell reference consisting of the column letter and row number that intersect at the cell's location. -To "USE" the data stored in a cell (say, in a formula) you simply refer to the cell's reference (i.e., it's name).

List Vs. Range

-A group of discontiguous (or noncontiguous) cells is a properly called a "LIST". -Cells that are Ctrl-Click selected are by definition discontiguous even if they are physically contiguous. -A range of cells is really a special type of list where the cells in the range were selected simultaneously.

Mixed Cell References

-A mixed cell reference is used when referencing a cell within the formula where part of the cell address is preceded by a dollar sign to lock—either the column letter or the row value—as absolute and leaving the other part of the cell as relatively referenced. -In the formula =$B10, the B column would remain unchanged but the row reference is relative and would change when the formula is copied -Note: successful use of mixed cell references requires that you think about which "direction" you are planning to copy cells containing formulas. Often requires some "patching"!

Absolute Cell References

-An absolute cell reference is used when a formula needs to be copied and the reference to one or more cells within the formula should not change as the formula is copied. -A dollar symbol ($) creates an absolute cell reference when placed in front of both the column and row designation. -Example: •=$A$1 * B1. •When copied, Excel will NOT apply its relative cell referencing rules to the A1 cell reference but will apply them to B1. •Think of the $ sign as "protecting" the column or row from Excel.

Value Field Settings & Number Format

-By default, Excel uses SUM for the values in a pivot table. You can change this by going to the "Value Field Settings". Choose any of the options in the "Summarize value field by" list. -In order to format your values in the pivot table, use the "Number Format" button. DO NOT use the normal formatting options on the home page.

What-If Sensitivity Analysis (Data Table)

-By using What-If Analysis tools in Excel, you can use several different sets of values in one or more formulas to explore all the various results. -If you have a formula that uses one or two variables, or multiple formulas that all use one common variable, you can use a Data Table to see all the outcomes in one place. -Using Data Tables makes it easy to examine a range of possibilities at a glance.

Grouping

-If you have a date field in your pivot table, you can group by a certain date and time options. 1.) Highlight the field you want to group by. 2.) Select "Group Selection" on the "Analyze" tab. 3.) Choose how you would like to view the data.

Named Ranges

-Named ranges are used in formulas and functions in place of default cell references -Press F3 to open the Paste Name dialog box, and then select the named range to insert -Or, type the formula, and is you begin to type the name of the range, a list of named ranges will appear. Select the appropriate named range. -You can spot a named ranges in the cell's context menu by looking for the "name tag" icon.

Pivot Table Basics

-Non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. -You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it.

Pivot Tables

-Powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data. -You can group data into categories, break down data, filter data to include or exclude categories, and build charts.

Filtering - Using Slicers

-Slicers can also be used for quick filtering. 1.) Select "Insert Slicer" on the "Analyze" tab. 2.) Choose a field or fields from the dialogue box. 3.) Click options in the slicers to filter on that particular data. 4.) Click the button with the checkmarks if you want to select multiple items to filter by. 5.) Highlight the slicer and hit your delete key to get rid of the slicer.

Pivot Charts

1.) To create a visualization (chart) from your pivot table, choose "PivotChart" on the "Analyze" tab. •Select your chart type. •All options are the same as normal chart options.

Creating What-If Sensitivity Analysis (Data Table)

-The data table must be based on a formula. 1.) Start creation of the data table by creating the base. The upper leftmost cell of the data table must either point to a cell with a formula in it, or must be a formula. 2.) Next, create the variables for your table. Row is the value going horizontally (In the example, Row 7). Column is the value going vertically (In the example, column B). 3.) Highlight the whole data table. 4.) Click the Data tab. 5.) Click What-If Analysis. 6.) Select Data Table. Row Input Cell = Your Row Variables will replace which cell in your formula? (In the example, Quantity). Column Input Cell = Your Column Variables will replace which cell in your formula? (In the example, Cost).

Nested "IF" Statement

-Use of an IF function as an argument within another IF function. -Usually combines multiple logical functions into a single formula. -If last year's spending was less than $500 display "Low Spending" else if last year's spending was >= 500 but less than $1,500 then display "Moderate Spending" else if spending was >= $1500 display "High Spending"

VLOOKUP Function

-Used mostly to display data from one worksheet (or workbook) into another. -Four arguments: •lookup_value •table_array •col_index_num •[range_lookup]

VLOOKUP & HLOOKUP

-VLOOKUP: •Locates data in a table based on comparison values •Comparison values must be located in 1st column of the lookup table range. I.e., to the left of the data you want to find. •Finds exact matches or approximate matches •V stands for vertical -HLOOKUP: •Identical, except data is organized horizontally •H stands for horizontal

Authoring Robust Cell Formulas

-When data changes, formulas should not "break". I.e., they should represent a new (and correct answer) with the new data. -Your formulas should only need to change when: a)the structure of the input data changes or. b)If you change the structure of your spreadsheet.

Relative Cell Referencing (RCR)

-When formulas containing a cell reference are copied, the formal cell references will be adjusted. -Cell references in the copied formula reflect their new location relative to the old location. -This is known as relative cell reference (RCR). -RCRs enable you to reuse a formula with no manual changes. -Cell references in formulas will also be adjusted for newly inserted rows or columns.

Range of Cells

-While references often refer to individual cells (ex: A1), they can also refer to a group or range of adjacent cells. -A cell range is are identified/named by the cell references of the cells in the upper left and lower right corners of the range. -The two cell references used to name a range are separated by a colon ( : ) which tells Excel to include all the cells between these start and endpoints.

Filtering

-You can click and drag a field down to the "Filters" area. This will add the field above the pivot table, giving you the ability to use the values in that field to filter your data in the pivot table. -You can also filter using "Colum Labels" and "Row Labels": •"Label Filters" will filter based on your rows. •"Value Filters" will filter on the value field.

Before You Create A Pivot Table...

-Your data should be organized in a tabular format and should not contain rows or columns that are entirely empty/blank. -Each column should contain data that is consistent with the field characterized by column label. For example, you shouldn't mix dates and text in the same column. -The rows represent an "observation" and its corresponding field values from the population of interest. -PivotTables work on a snapshot of your data, called the cache, so your actual data doesn't get altered in any way.

Filtering - Top 10 (3 Steps)

1.) Choose Top or Bottom. 2.) Change the # of top or bottom to 10. 3.) Choose what you want to filter.

How to create a Pivot Table...

1.) Click a cell in the source data or table range. 2.) "Insert" > "Pivot Table".

Pivot Table Dialogue Box...

1.) Excel will display the Create PivotTable dialog with your range or table name selected. 2.) In the Choose where you want the PivotTable report to be placed section, select New Worksheet, or Existing Worksheet. For Existing Worksheet, you'll need to select both the worksheet and the cell where you want the PivotTable placed. OR... Insert recommended Pivot Table: click a cell and go to "Insert" > "Recommended PivotTables".

Sorting

By clicking on the drop list arrow next to "Column Labels" or "Row Labels", you can Sort: •Ascending (A à Z) •Descending (Z à A) •Custom Sort by choosing "More Sort Options" where you will be able to sort ascending or descending and pick which field to sort by.

Basic Pivot Table Structure

In the Field Name area at the top the PivotTable Fields list, select the check box for any field you want to add to your PivotTable.

Label Filters

Will filter based on your rows. "Label Filters" adds in: Begins With, Does Not Begin With, Ends With, Does Not End With, Contains, Does Not Contain.

Value Filters

Will filter on the value field. "Value Filters" allow you to filter by: Conditional, =, <>, >,<, >=, <=, Between & Not Between, and Top 10.

"IF" Function

•Comparison operators •= Equal to •> Greater than •< Less than •<> Not equal to •>= Greater than or equal to •<= Less than or equal to

Logical Conjunction Functions

•Evaluate multiple logical tests •Enable linking or joining of functions or formulas to perform logical test •Logical Conjunction Functions •AND •OR •NOT

AND Function

•Evaluates data using the binary logic AND operator. •You can enter up to 30 logical tests! •If ALL logical tests are true, the word TRUE will display. •If ANY of the logical tests are false, the word FALSE will display. •TRUE is displayed in column F that meets ALL three logical tests •FALSE is displayed if the results are false for ANY of the three logical tests •Because of sorting, all TRUE outputs are grouped at the top of the worksheet

OR Function

•Major difference from AND Function: •If any logical test is true, word TRUE will be displayed •Is used where the existence of just one criterion is required to trigger a decision

AND-OR

•Used to evaluate data based on logical tests •Can be used independently (i.e., in a cell by itself) •Often (and primarily) used in the logical test argument of an IF Function •EX: •=IF(AND(B2<>0,B3>10),"OK","BAD DATA") ^ AND() is a function just like any other Excel function