Topic 2: IF, IFS, IFERROR, TEXT, HOUR, WEEKDAY, GETPIVOTDATA

What button needs to be toggled to view/hide your list of PivotTable fields?

Field List

Where is the Field List button in Excel?

Off to the right in the PivotTable Analyze tab

Fields in “Columns” show up as columns show up as columns in your PivotTable, Fields in “Rows” show up as rows in your PivotTable, and fields in “Values” are the…

Data that is summarized

True or False: Slicers are generally easier to use than filters

True

What PivotTable formatting and options are available by right-clicking the PivotTable?

PivotTable Options, Value Field Settings, Refresh, and Sort Columns

How to create a slicer

• Click inside the PivotTable

• On the menu bar: Insert slicer

• Select one or more slicers to add

• Right click slicer settings to choose single or multi-select

How to create a timeline for a PivotTable

• Click inside the PivotTable

• On the menu bar: insert timeline

• Choose between days, months, quarters, years as you wish

The structure of an IF statement is =IF(logical_test,[value_if_true],[value_if_false]) (do not use the brackets when typing the formula). What does logical_test represent?

A value or logical expression that can be evaluated as TRUE or FALSE

IF Statements | “Values” that are text should be in _________. Numeric values should not.

Quotes

Example of a logical test in an IF statement

[A cell] > [some number or word or other]

Operators that can be used in IF statements

>, >=, <, <=, =, <>, ““, <>:””, etc.

The structure of an IFS statement is =IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, logical_test3, value_if_true3, ...). Why is the order of logical tests important?

IFS tests multiple IF statements and returns the result of the FIRST true test.

The structure of an IFERROR statement is =IFERROR(value,value_if_error). What does value represent?

The value, reference, or formula to check for an error

The structure of an IFERROR statement is =IFERROR(value,value_if_error). What does value_if_error represent?

The value to return if an error is found

Why should we be careful with IFERROR functions?

Sometimes error messages help you find a mistake, and IFERROR will bluntly remove them all

The structure of a TEXT function is =TEXT(value, format_text). What does value represent?

The number to convert

The structure of a TEXT function is =TEXT(value, format_text). What does format_text represent?

The number format to use

Say Cell A1 contains “4-July-2026” and you want to convert it to “2026” in another cell. Write a formula that allows you to do that.

=TEXT(A1,”yyyy”)

Say Cell A1 contains “4-July-2026” and you want to convert it to “26” in another cell. Write a formula that allows you to do that.

=TEXT(A1,”yy”)

Say Cell A1 contains “4-July-2026” and you want to convert it to “Jul” in another cell. Write a formula that allows you to do that.

=TEXT(A1,”mmm”)

Say Cell A1 contains “4-July-2026” and you want to convert it to “July” in another cell. Write a formula that allows you to do that.

=TEXT(A1,”mmmm”)

Say Cell A1 contains “4-July-2026” and you want to convert it to “4” in another cell. Write a formula that allows you to do that.

=TEXT(A1,”d”)

Say Cell A1 contains “4-July-2026” and you want to convert it to “Sat” in another cell. Write a formula that allows you to do that.

=TEXT(A1,”ddd”)

Say Cell A1 contains “4-July-2026” and you want to convert it to “Saturday” in another cell. Write a formula that allows you to do that.

=TEXT(A1,”dddd”)

The structure of an HOUR function is =HOUR(serial_number). What does serial_number represent?

A valid Excel time

The structure of a WEEKDAY function is =WEEKDAY(serial_number,[return_type]). What does serial_number represent?

The date for which you want to get the day of the week

The structure of a WEEKDAY function is =WEEKDAY(serial_number,[return_type]). What does return_type represent?

A number representing the day of the week mapping scheme (this is optional). The default is 1.

The structure of a GETPIVOTDATA function is =GETPIVOTDATA(data_field,pivot_table,[field1,item1],…). What does data_field represent?

The name of the value field to query

The structure of a GETPIVOTDATA function is =GETPIVOTDATA(data_field,pivot_table,[field1, item1],…). What does pivot_table represent?

A reference to any cell in the pivot table to query

The structure of a GETPIVOTDATA function is =GETPIVOTDATA(data_field,pivot_table,[field1,item1],…). What does field1, item1 represent?

A field/item pair