Excel Quiz 1 (CIS)

What is Excel?

a spreadsheet tool used for data entry, calculations, analysis

data is stored in rows and columns

Order of Operations

parantheses, exponents, multiplication, division, addition, and subtraction - PEMDAS

Excel follows this order when executing commands

Cell reference: relative cell address and steps for relative cell address

changes when a formula is copied relative to where it is being copied

1. Write out your function/formula

2. Find the little black “+” sign in the lower right corner

3. Drag and fill the other cells

4. Only the formula will be copied, not the number!

Absolute cell address and steps

Remains the same when the formula is coped

1. Write out your function/formula

2. Add “$” to your row and column to freeze them so that they don’t change —> The first $ is for the column, and the second $ is for the row

3. Find the little black + in the lower right corner

4. Drag and fill other cells

5. Only certain aspects of the formula will be copied and the areas you absolute reference will stay the same so that you operate on that value

Ex. F7/ $F$8 (the first part is relative/ the second part is absolute/fixed)

Mixed Cell reference: and steps

remains the same for the row or the column when the formula is copied

1. Write out your function/formula

2. Sometimes absolute referencing doesn’t work, you may only want the row or the column in that case, only use $ for what area of the cell you want to fix —> $F(row) F$(column)

3. Find the little black “+” sign in the lower right corner

4. Drag and fill the other cells

5. Only partial aspects changed

Excel IF Functions

the IF function runs a logical test and return one value for a TRUE result and another value for a FALSE result

The result from an IF can be a value, a cell reference, or even another formula

For example to “pass” score above 70: =IF(A1>70, “Pass”, “Fail”)

Steps for If functions

1. to write out your IF formula, either click “insert function from ribbon” or type in “=” into the cell

2. Type in “=IF”. You will see IF(logical_test, value_if_true,value_if_false) —> this check whether a condition is met, and returns one value if TRUE and another value if FALSE

3. The test is usually a <,>,= test between two cell values, and the true, false values are typically words)

COUNTIF Function

counts the number of cells in a range that satisfy some condition

=COUNTIF(range, criteria)

=COUNTIF(range 1, criteria 1,range 2, criteria 2…)

CountIF steps

1. To write out your COUNTIF formula, either click “insert function from ribbon” or type in “=” into the cell. 

2. Type in “=COUNTIF”. You will see COUNTIF(range, criteria) ← this will count each of the cells specified by a given condition or criteria.

3. Range: the range (from one cell to another ex: A1:B4) of cells you want evaluated

Criteria: the condition or criteria in the form of a number, expression, or text that defines which cells will be counted.

sumif

• Like COUNTIF, but rather than counting the number of cells in the range SUMIF adds their contents together

• SUMIF can identify the cells in one range based on criteria applied to another range (COUNTIF can not do this)

=SUMIF(criteria range, criteria, sum range)

=SUMIFS(sum range, criteria range 1, criteria 1, criteria range 2, criteria 2,...)

SUMIF Steps

Steps:

1. To write out your SUMIF formula, either click “insert function from ribbon” or type in “=” into the cell. 

2. Type in “=SUMIF”. You will see SUMIF(range, criteria, sum_range) ← this adds the cells specified by a given condition or criteria.

3. Range: the range (from one cell to another ex: A1:B4) of cells you want evaluated

Criteria: the condition or criteria in the form of a number, expression, or text that defines which cells will be added.

Sum_Range: are the actual cells to sum. If omitted, the cells in the range are used

Conditional Formatting

• Used to automatically highlight certain cells or rows to make them stand out

• When applying conditional formatting to a range of rows, always start by selecting a cell in the topmost row of the range

• Remember** in the “manage list of rules” the upper rules override those below them

Pivot Tables

• A powerful tool for grouping and aggregating data to summarize and analyze

• Allows you to see comparisons, patterns, and trends in your data

• It also allows you to extract the insights and significance from a large, detailed data set

• Pivot Tales works a bit differently depending on the platform and operating system you are using to run Excel

** Before you start:

• Check the dataset/range of data you are analyzing has no blank lines or rows

Ensure every column has a header

Pivot table steps

Steps to Create a Pivot Table:

1. 1. Prepare Your Data

• Ensure your data is in a table format. Each column should have a header, and all associated data should be below the headers.

• Remove any blank rows or columns to avoid errors.

2. Insert a Table (Optional but Recommended)

• Select your data range.

• Click on Insert > Table.

• Ensure "My table has headers" is checked.

• Click OK.

3. Insert a Pivot Table

• Click anywhere in your table.

• Go to Insert > Pivot Table.

• Ensure the correct Table/Range is selected.

• Choose where to place the pivot table (a New Worksheet is recommended to keep the original data intact).

• Click OK.

4.  Explore Pivot Table Fields

• The PivotTable Fields pane will appear on the right.

• It displays column headers from your dataset.

5.  Add or Remove Fields in the Pivot Table

• To add a field, click the checkbox next to the field name in the PivotTable Fields pane.

• To remove a field, uncheck the checkbox next to the field name.

• To move a field from one PivotTable area to another, click and drag the field to the target area in the PivotTable Fields pane.

6. Summarize Values (Sum, Max, Count, etc.)

• Right-click on any value in the table.

• Select Summarize Values By.

• Choose Sum, Max, Count, Average, etc.

• Sort and Organize Data

• Right-click on a value in the pivot table.

• Select Sort > Largest to Smallest or Smallest to Largest.

2. Use Filters for Specific Analysis

• Drag a field to Filters.

• Click the dropdown and select a specific product to filter the data.

3. Adjust Pivot Table Look and Feel

• Click on the pivot table.

• Go to Design tab.

• Change colors, add banded rows/columns, or adjust grand totals.

4. Create a Pivot Chart

• Click anywhere in the pivot table.

• Go to Insert > Column Chart (or another chart type).

• The chart updates dynamically with the pivot table.

5. Use Slicers for Quick Filtering

• Click on the pivot table.

• Go to PivotTable Analyze > Insert Slicer.

• Choose a field (e.g., Region) and click OK.

• Click a slicer button to filter the pivot table and chart instantly.

6. Use Timelines for Date-Based Analysis

• Click inside the pivot table.

• Go to PivotTable Analyze > Insert Timeline.

• Choose a date field and click OK.

• Adjust the timeline slider to filter data by time period.

7. Refresh the Pivot Table When Data Changes

• Click inside the pivot table.

• Go to PivotTable Analyze > Refresh.

• If your data expands, update the range by going to Change Data Source.

8. Watch video for better understanding: https://www.youtube.com/watch?v=dvbLrwD2SpA 

VLOOKUP

• VLOOKUP (Vertical Lookup)

  • Excel function to look up data

  • Lookup table must be organized vertically

  • Approximate or exact matching

• VLOOKUP accepts 4 parameters: 3 are required, 1 is optional

  • Lookup value

  • Cell range to check for the value you are looking up

  • Column # for Return Value

  • Optional: Specify Approximate or Exact match

    • The approximate match is the default-leave blank or specify “true”

    • For exact: specify the option “false” or 0

    • #N/A

      • VLOOKUP can’t find what it’s been aksed to look for 

      • Check Lookup Value or the data in the range

    • #NAME?

      • Usually caused by a typo

      • To avoid typos, used the Formula Wizard

Steps for VLOOKUP Function

Start the VLOOKUP Function:

• To write out your VLOOKUP formula, either click Insert Function from the ribbon or type = into the cell.

• Type =VLOOKUP. You will see VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), which specifies the arguments needed for the function to work.

2. Lookup Value:

• The lookup_value is the value you want to search for in the table.

3. Define the Table Array:

• The table_array is the range of data that includes both the lookup value and the value you want to retrieve.

• Select the full table that includes the search column and result column.

4. Specify the Column Index Number:

• The col_index_num tells Excel which column in the table array contains the result.

• Count the columns from left to right, starting at 1.

5. Choose an Exact or Approximate Match:

• The range_lookup argument defines whether you want an exact or approximate match.

• Enter FALSE for an exact match (recommended for most lookups).

• Enter TRUE for an approximate match (used for searching within ranges, like grade cutoffs).

6. Final VLOOKUP Formula Example:

• If you are searching for the price of a cookie listed in cell E2, and your cookie price table is in A1:B10, use the formula:

• =VLOOKUP(E2, A1:B10, 2, FALSE)

7. Handling Errors: #N/A Issue

• If the lookup value is not found, #N/A appears.

• Check spelling or use IFERROR to display a custom message:

• =IFERROR(VLOOKUP(E2, A1:B10, 2, FALSE), "Not Found")

8. Using Absolute References to Prevent Errors:

• Copying the formula down can shift the table range.

• Use absolute or mixed references (with $) to lock the table range:

• =VLOOKUP(E2, $A$1:$B$10, 2, FALSE)

9. Convert Data Range into an Excel Table for Dynamic Lookups

• Select the table range and click Insert > Table.

• Name the table for easy reference (e.g., "CookieTable").

• Modify the formula using the table name:

• =VLOOKUP(E2, CookieTable, 2, FALSE)

GOALSEEK

• Allows you to find a single input value needed to achieve a specific goal

GOALSEEK Steps

Steps:

1. Open Your Excel Workbook

2. Go to the Data Tab

• Click on the Data tab in the ribbon.

3. Open Goal Seek

4. Click on What-If Analysis in the Forecast group.

• Select Goal Seek from the dropdown menu.

• Set the Goal Seek Parameters

5. Set Cell: Select the cell containing the formula you want to adjust.

6. To Value: Enter the desired result you want in the formula cell.

7. By Changing Cell: Select the input cell Excel should adjust to reach the target value.

8. Run Goal Seek

9. Click OK, and Excel will calculate the required input value. Apply or Cancel Changes

10. If the solution meets your needs, click OK to apply it. If not, click Cancel to revert to the original values.

SOLVER

• Microsoft Excel Add-In Program

• Used for What-If Analysis

• Find an Optimal, Minimum, or Maximum Value for an Objective, subject to (optional) Constraints

• Excel Solver Model

Solver Example

1. Organize your data in a structured format, with decision variables, constraints, and the objective function in separate cells.

2. Click on Data tab and select Solver in the Analysis group.

3. In the Set Objective field, select the cell containing the value you want to optimize.

4. Choose Maximize, Minimize, or set a specific value.

5. In the By Changing Variable Cells field, select the cells that Solver can adjust to achieve the objective.

6. Add Constraints

7. Click Add under Subject to the Constraints section.

8. Specify constraints by selecting a cell, choosing a condition (≤, =, ≥), and entering a value or reference cell.

9. Click Add for additional constraints or OK when done.

10. Choose Solving Method