Course outline
Module 1
Module 1: Excel Fundamentals and Navigation
Excel Fundamentals and Navigation
Knowledge check
Q1. Which cell reference refers to the cell in column C, row 5?
- A. 5C
- B. C5 ✓
- C. C$5
- D. R5C
Why: In Excel's A1 reference style, the column letter comes first followed by the row number, so column C, row 5 is written as C5.
Q2. What is the keyboard shortcut to move to the last cell of the current data region in a worksheet?
- A. Ctrl + Home
- B. Ctrl + End ✓
- C. Ctrl + Shift + End
- D. Alt + End
Why: Ctrl + End moves the active cell to the last used cell in the worksheet, which is the intersection of the last used row and last used column.
Q3. Which element of the Excel interface displays the address or name of the currently selected cell?
- A. Formula Bar
- B. Name Box ✓
- C. Status Bar
- D. Ribbon
Why: The Name Box, located to the left of the Formula Bar, shows the reference or defined name of the active cell or selected range.
Q4. What does a workbook contain in Excel?
- A. Only one worksheet
- B. One or more worksheets ✓
- C. Only charts and images
- D. Only formulas
Why: An Excel workbook is the file itself and can contain one or more worksheets, each of which holds a grid of cells.
Q5. Which key do you press to cycle through the absolute and relative reference types of a selected cell reference while editing a formula?
- A. F2
- B. F4 ✓
- C. F9
- D. F12
Why: Pressing F4 while the cursor is on a cell reference in the formula bar toggles between relative, absolute, and mixed reference forms.
Module 2
Module 2: Data Cleaning and Preparation
Data Cleaning and Preparation
Knowledge check
Q1. Which Excel function is most useful for removing extra spaces from text entries before analysis?
- A. CLEAN
- B. TRIM ✓
- C. LEN
- D. PROPER
Why: TRIM removes leading, trailing, and repeated internal spaces from text, leaving single spaces between words. CLEAN removes non-printable characters, LEN counts characters, and PROPER capitalizes text, so TRIM is the correct choice for fixing spacing issues.
Q2. What is the keyboard shortcut to open the Remove Duplicates dialog box after selecting your data range?
- A. Ctrl + D
- B. Alt + D + U ✓
- C. Ctrl + Shift + R
- D. F5
Why: Alt + D + U is the sequence of key presses that opens Remove Duplicates from the Data ribbon in Excel for Windows. Ctrl + D fills down, Ctrl + Shift + R is not a standard duplicate-removal shortcut, and F5 opens the Go To dialog.
Q3. Which function would you use to convert a column of dates stored as text into true Excel date values?
- A. TEXT
- B. DATEVALUE ✓
- C. VALUE
- D. DATE
Why: DATEVALUE converts a date written as text, such as '3/15/2024', into a serial number that Excel recognizes as a date. TEXT does the opposite by formatting a value as text, VALUE converts numeric text only, and DATE builds a date from separate year, month, and day arguments.
Q4. What does the IFERROR function do?
- A. It highlights cells that contain errors
- B. It deletes rows containing errors
- C. It returns a specified value when a formula produces an error ✓
- D. It corrects spelling errors in text
Why: IFERROR(value, value_if_error) checks a formula and returns the alternative value you specify, such as 0 or blank, whenever the formula would return an error like #N/A or #DIV/0!. It does not highlight or delete anything, and it does not check spelling.
Q5. Which tool in the Data tab lets you split a single column of combined text, such as 'Smith,John', into separate columns?
- A. Flash Fill
- B. Text to Columns ✓
- C. Consolidate
- D. Subtotal
Why: Text to Columns splits one column into several based on a delimiter such as a comma, space, or tab, or on fixed widths. Flash Fill can infer patterns but is not a splitting tool by itself, Consolidate combines data from multiple ranges, and Subtotal calculates grouped totals.
Module 3
Module 3: Formulas and Functions for Analysis
Formulas and Functions for Analysis
Knowledge check
Q1. Which function returns the average of a range of numeric cells in Excel?
- A. SUM
- B. AVERAGE ✓
- C. COUNT
- D. MEDIAN
Why: AVERAGE calculates the arithmetic mean of the numeric values in a specified range. SUM adds values, COUNT counts numeric cells, and MEDIAN returns the middle value, so only AVERAGE returns the mean.
Q2. What does the COUNTIF function do?
- A. Adds all values that meet a condition
- B. Counts cells in a range that meet a specified condition ✓
- C. Returns the largest value in a range
- D. Counts all non-empty cells in a range
Why: COUNTIF counts only the cells in a range that satisfy a given criterion, such as =COUNTIF(A1:A10,">50"). Counting all non-empty cells is the job of COUNTA, and adding values by condition is SUMIF.
Q3. Which formula correctly uses VLOOKUP to find the price for the item in cell E2 within the table A2:B20, where prices are in column 2?
- A. =VLOOKUP(E2,A2:B20,2,FALSE) ✓
- B. =VLOOKUP(A2:B20,E2,2,FALSE)
- C. =VLOOKUP(E2,B2:B20,1,FALSE)
- D. =VLOOKUP(E2,A2:B20,1,TRUE)
Why: VLOOKUP takes the lookup value first, then the table array, then the column index number, then the match type. Using FALSE requests an exact match, and column 2 of A2:B20 holds the prices. The other options reverse the arguments or point to the wrong column.
Q4. What is the result of =IF(B2>=60,"Pass","Fail") when B2 contains 55?
- A. Pass
- B. Fail ✓
- C. TRUE
- D. 55
Why: The IF function tests whether the condition B2>=60 is true. Since 55 is less than 60, the condition is false, so the function returns the second argument, "Fail".
Q5. Which function would you use to find the highest value in a dataset?
- A. MIN
- B. MAX ✓
- C. LARGE
- D. RANK
Why: MAX returns the largest numeric value in a range. MIN returns the smallest value, while LARGE returns the nth largest value and RANK gives a value's position, so MAX is the direct answer.
Module 4
Module 4: Lookup and Reference Techniques
Lookup and Reference Techniques
Knowledge check
Q1. Which function searches for a value in the first column of a range and returns a value from a specified column in the same row?
- A. HLOOKUP
- B. VLOOKUP ✓
- C. INDEX
- D. MATCH
Why: VLOOKUP (Vertical Lookup) searches the first column of a table array for a lookup value and returns a value from a column you specify in the same row.
Q2. In the formula =VLOOKUP(A2, Sheet2!A:C, 3, FALSE), what does the value 3 represent?
- A. The third row of the lookup table
- B. The number of matches to return
- C. The column index number to return from the table ✓
- D. The row where the search begins
Why: The third argument of VLOOKUP is col_index_num, which specifies which column in the table array (counting from the first column as 1) should supply the returned value. Here, column 3 (column C) is returned.
Q3. What does the FALSE argument at the end of a VLOOKUP formula indicate?
- A. Return an error if the value is not found
- B. Perform an approximate match
- C. Perform an exact match ✓
- D. Ignore case sensitivity
Why: Setting range_lookup to FALSE (or 0) forces VLOOKUP to find an exact match. TRUE (or omitted) allows an approximate match, which requires the lookup column to be sorted.
Q4. Which combination of functions is commonly used as a more flexible alternative to VLOOKUP because it can look to the left of the lookup column?
- A. SUM and COUNT
- B. IF and AND
- C. INDEX and MATCH ✓
- D. CONCATENATE and LEFT
Why: INDEX returns a value from a given position in a range, and MATCH finds the position of a value. Together they can look in any column direction, unlike VLOOKUP, which can only return values to the right of the lookup column.
Q5. If VLOOKUP cannot find the lookup value, which error does it typically return?
- A. #VALUE!
- B. #DIV/0!
- C. #REF!
- D. #N/A ✓
Why: VLOOKUP returns #N/A when it cannot find the lookup value in the first column of the table array. This error can be handled with IFERROR or IFNA to display a custom message.
Module 5
Module 5: PivotTables and Data Summarization
PivotTables and Data Summarization
Knowledge check
Q1. What is the primary purpose of a PivotTable in Excel?
- A. To permanently delete duplicate rows from a dataset
- B. To summarize and analyze large amounts of data by grouping and aggregating values ✓
- C. To create charts that update automatically with cell formatting
- D. To convert text values into numeric values
Why: PivotTables let you quickly summarize large datasets by grouping data into categories and applying aggregate functions such as sum, count, or average, without changing the source data.
Q2. Which area of the PivotTable Fields pane should you drag a field into to create column headings that spread across the table?
- A. Filters
- B. Values
- C. Rows
- D. Columns ✓
Why: Fields placed in the Columns area become column headings across the top of the PivotTable, while Rows fields create row labels down the left side.
Q3. By default, what summary function does Excel apply when you drag a numeric field into the Values area?
- A. Average
- B. Count
- C. Sum ✓
- D. Maximum
Why: Excel defaults to Sum for numeric fields in the Values area. Text fields default to Count, so the function you see depends on the data type of the field.
Q4. What happens to the original source data when you change the layout or filters of a PivotTable?
- A. The source data is sorted to match the PivotTable layout
- B. The source data is permanently filtered and hidden rows are deleted
- C. The source data is unchanged, since the PivotTable is a summary view of it ✓
- D. The source data is converted into a table with merged cells
Why: A PivotTable is a dynamic summary that reads from the source range. Rearranging fields or applying filters changes only the PivotTable view and leaves the underlying data intact.
Q5. Which action should you take after your source data has been updated with new rows so the PivotTable reflects those changes?
- A. Recreate the PivotTable from scratch each time
- B. Delete the PivotTable and copy the data into a new sheet
- C. Use the Refresh command on the PivotTable Analyze tab ✓
- D. Save the workbook and close it without any further action
Why: PivotTables store a snapshot of the source data, so you must refresh them to pull in new or changed records. The Refresh command on the PivotTable Analyze tab updates the summary.
Module 6
Module 6: Charts and Data Visualization
Charts and Data Visualization
Knowledge check
Q1. Which chart type is most appropriate for showing how a part-to-whole relationship changes over time?
- A. Pie chart
- B. Stacked area chart ✓
- C. Scatter plot
- D. Histogram
Why: A stacked area chart displays both the total and the contribution of each component across time periods, making it ideal for part-to-whole trends. A pie chart only shows a single snapshot, and scatter plots and histograms serve different purposes.
Q2. Which chart type is best for comparing values across different categories?
- A. Line chart
- B. Clustered column chart ✓
- C. Scatter plot
- D. Area chart
Why: A clustered column chart places bars side by side for each category, making direct comparisons between categories easy. Line charts are better for trends over time, while scatter plots show relationships between two numeric variables.
Q3. In Excel, which keyboard shortcut inserts a default chart from selected data quickly?
- A. Ctrl + C
- B. Alt + F1 ✓
- C. Ctrl + Shift + F
- D. Alt + Enter
Why: Pressing Alt + F1 creates an embedded chart from the selected data using the default chart type. Ctrl + C copies, Ctrl + Shift + F opens font formatting, and Alt + Enter inserts a line break within a cell.
Q4. What is the main purpose of a scatter plot in Excel?
- A. To show parts of a whole
- B. To display the relationship between two numeric variables ✓
- C. To show cumulative totals over time
- D. To group data into frequency ranges
Why: A scatter plot places data points on an X and Y axis, revealing correlation, clusters, or outliers between two numeric variables. The other options describe pie charts, area charts, and histograms respectively.
Q5. Which element of a chart explains what each color or pattern represents?
- A. Data labels
- B. Axis title
- C. Legend ✓
- D. Gridlines
Why: The legend identifies each series in the chart by matching colors or patterns to series names. Data labels show values, axis titles describe the axes, and gridlines are reference lines that aid reading values.
Module 7
Module 7: Dashboards and Reporting
Dashboards and Reporting
Knowledge check
Q1. Which Excel feature is best suited for quickly summarizing large datasets into an interactive report that can be filtered and rearranged?
- A. Conditional Formatting
- B. PivotTable ✓
- C. Data Validation
- D. Freeze Panes
Why: PivotTables summarize large datasets by grouping and aggregating values, and they let users filter and rearrange fields interactively, which makes them ideal for reporting.
Q2. What is the main purpose of a slicer in an Excel dashboard?
- A. To split a cell into two columns
- B. To provide a visual button-based filter that controls one or more PivotTables or PivotCharts ✓
- C. To calculate running totals
- D. To protect a worksheet from edits
Why: Slicers are visual filter controls with clickable buttons. Connected to PivotTables or PivotCharts, they make it easy for dashboard users to filter data without using dropdown menus.
Q3. Which chart type is most appropriate for showing how a value changes over time?
- A. Pie chart
- B. Line chart ✓
- C. Scatter plot
- D. Bubble chart
Why: Line charts display data points connected over a continuous time axis, making trends and changes over time easy to see.
Q4. In a dashboard, what does a KPI (Key Performance Indicator) represent?
- A. A random sample of raw data
- B. A measurable value that shows progress toward an important business goal ✓
- C. The formula used to build a chart
- D. The file name of the workbook
Why: A KPI is a metric tied to a specific objective, such as monthly sales against target. Dashboards highlight KPIs so viewers can assess performance at a glance.
Q5. Which practice helps make an Excel dashboard easier to read at a glance?
- A. Using as many colors and fonts as possible
- B. Placing all raw data on the dashboard sheet
- C. Keeping the layout uncluttered and highlighting key figures with clear labels ✓
- D. Hiding all chart titles
Why: Effective dashboards minimize clutter, use consistent formatting, and emphasize key metrics with clear labels so the most important information is quickly understood.