Final exam guide for C1: Nested functions, PivotTables, advanced filters, and form controls

If you're having trouble with your C1 - Advanced certification exercises, make sure you can answer "Yes" to these questions before reaching out to a teacher:

• Did you verify the arguments in your complex nests? In C1, functions like SUM(OFFSET(...)) or XLOOKUP require pinpoint accuracy. Ensure all parentheses are closed, and error text strings (like "Not Found") are wrapped in double quotes.

• Are your Slicers manually connected to all three Pivot Tables? Also, verify that the "Cell Link" for your Option Buttons points directly to the cell that drives your CHOOSE or INDEX functions for the dynamic chart.

• Are you following the strict format for advanced filters? To validate your filter exercises correctly, the headers must be 100% identical to the table. Dates must follow the MM/DD/YYYY order, and you must use operators like <> for "not equal to" or the * wildcard when requested.

• Do the range names exactly match your formulas? When using SUM(INDIRECT(...)), the name you assigned to the range (e.g., JANUARY) must match the text in the reference cell exactly. A typo or extra space will prevent the function from recognizing the array.

If you’ve answered all these questions and you’re still having issues, check the following guide with common errors in the C1 final exam and how to solve them: 

Problem #1: XLOOKUP returns an error or misses the "Not Found" text.
Solution: Check the fourth argument of the function. The syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]). If you need a specific message to appear during an error, that text must be in quotes (e.g., "Not Found") inside that fourth argument.

Problem #2: SUM-OFFSET-MATCH nested return the wrong total.
Solution: The issue is usually within the OFFSET arguments. Remember:

1. Rows and columns: These indicate how far you move from the starting cell. If you start on a header, verify if your offset should be 0 or 1.

2. Height and width: Use MATCH or numbers to define the size of the range to sum. If you are summing 3 months, the "width" must be 3.

Problem #3: Pivot Tables aren't reacting to the slicer.
Solution: This is a connection error. Simply inserting a Slicer isn't enough. You must right-click the Slicer, select "Report Connections," and manually check every PivotTable you want to filter simultaneously.

Problem #4: The Pivot Table Calculated Field isn't applying the correct percentage.
Solution: When creating formulas in "Fields, Items, & Sets," always insert field names from the list rather than typing them to avoid typos. If the logic is conditional, use the IF function. For example: =IF('Sales'>10000, 'Sales'*8%, 'Sales'*3%). 

Problem #5: Advanced Filters aren't finding the records (dates or wildcards).
Solutions:

1. Dates: To find data in specific date ranges (e.g., first semester), use two columns with the same date header, one with >=01/01/2024 and another with <=06/30/2024.

2. Wildcards: To find items containing a specific letter, use asterisks: *T*.

3. Not equal to: To find items excluding a specific category, use the <> operator.

Problem #6: The Option Button or Combo Box isn't updating the chart.
Solution: Check the "Cell Link" in the Format Control settings. If the button isn't linked to the specific cell feeding your CHOOSE or INDEX function, the chart will remain static even when you click the control.

Problem #7: The UNIQUE function returns a #SPILL! error.
Solution: This is a dynamic array function. It requires the cells below and to the right of the formula to be completely empty. If text, a hidden space, or a table is in the way, the data cannot "spill" and will return an error.

Are you still having trouble with a specific C1 task? Check out our related article for more tips on high-level automation and complex data visualization!