AE111: ELECTRONIC FINANCIAL ACCOUNTING & REPORTING

Chapter 2: Special Excel Functions for Excel-Based AIS

By: Robert E. Regala

1. DESCRIPTION
Welcome to the “Special Functions for Excel-Based Accounting Information System”
module! In today’s fast-paced, data-driven business environment, the ability to design
and manage a robust Accounting Information System (AIS) in Excel is more than just a
technical skill—it’s a strategic advantage. This module is a preparatory stage to
creating and Excel-based AIS. mastering the essential functions, tools, and techniques
at this stage will mold the learner into an expert who is able to create dynamic
accounting information systems that are able to handle voluminous and ever-increasing
data.

Throughout this journey, you’ll not only learn how to use Excel’s most powerful features
but also why they matter in the real-world context of accounting and financial
management. You’ll gain the confidence to use functions that allows sophisticated
manipulation of data, troubleshoot errors with ease, and manage data accuracy and
integrity with precision. Consider this your opportunity to elevate your Excel skills from
routine use to professional mastery—empowering you to add value, solve problems, and
make informed decisions in any accounting role you take on.

2. LEARNING OBJECTIVES

Design dynamic reports that update automatically if data and criteria changes.
Learners will be able to master the Excel functions that are needed to create this
report. The functions in this module will also enable learners to design and create
an Excel-Based Accounting Information System (AIS) by progressively
developing proficiency in using sophisticated Excel functions that enable core
data manipulation and analysis, handle errors and ensure data integrity, and
validate data entry:

Enabling Learning Objectives:

a. Master conditional logic and advanced calculations using functions such as
IF, SUMIFS, COUNTA, etc. for programming spreadsheet behavior, enabling
learners to master the use of Excel functions for implementing sophisticated
conditional logic and performing advanced calculations, effectively
"programming" spreadsheet behavior to automate data analysis and
decision-making.

b. Develop expertise in cross-sheet data linking and extraction by mastering the

ability to efficiently retrieve and integrate specific information from various
tables, potentially located on different worksheets or even external files, within
an Excel-based Accounting Information System (AIS). The core of this
objective is efficient use of the VLOOKUP function, which is designed to link
two different tables using a unique identifier like an order number, effectively
pulling specific data (e.g., a sale amount) from a larger dataset into another
location.
 c. Develop proficiency in comprehensive data integrity and error handling
techniques. Learners will acquire the skills to ensure the accuracy and
reliability of data within an Excel-based Accounting Information System (AIS),
while also effectively managing and presenting potential errors. This involves
mastering functions like IFERROR, which allows for the display of custom
messages or blank cells instead of unsightly Excel error codes.

d. Master efficient data normalization and management tools and best practices,
such as creating user-friendly data entry forms for streamlined record input,
utilizing the Remove Duplicates tool to maintain clean master data lists,
employing naming ranges for improved formula readability and management,
leveraging the autofill handle for rapid formula application, and effectively
importing text and CSV files using both classic and Power Query methods for
external data integration.

3. PRE-ASSESSMENT (NOT GRADED): [Link]

4. LEARNING MATERIALS

Developing proficiency in sophisticated Excel functions and techniques is paramount

for any learner aiming to design a robust Accounting Information System (AIS)
because these skills essentially transform a basic spreadsheet into a powerful,
automated data management and analysis tool. Functions like IF allow for
"programming" spreadsheet behavior, enabling dynamic decision-making and
conditional outcomes, similar to basic coding. Advanced calculations with SUMIFS
and other “IF functions” empower users to aggregate and analyze data based on
multiple complex criteria, offering a significant time-saving alternative to manual
filtering and summarization for large datasets.

Furthermore, mastering VLOOKUP for cross-sheet data linking and extraction is

critical, as it facilitates the seamless integration of information from various tables,
ensuring that data, such as sales amounts by order number, can be efficiently
retrieved and utilized across different parts of the system. The ability to leverage
tools like the autofill handle and naming ranges further enhances efficiency by
rapidly replicating formulas and improving formula readability and management,
making the creation and maintenance of complex systems far more manageable.

Beyond automation and efficiency, developing expertise in these techniques is

crucial for ensuring comprehensive data integrity and effective error handling, which
are foundational to the reliability of any AIS. Learners will gain the ability to diagnose
and resolve a wide array of common Excel errors, including #DIV/0!, #N/A, #NAME?,
#NULL!, #NUM!, #REF!, #VALUE!, and ######, by understanding their root causes
and applying appropriate solutions.

Functions like IFERROR are indispensable for presenting user-friendly messages or

blank cells instead of unsightly error codes, significantly improving the professional
appearance and usability of financial reports. Additionally, techniques such as using
the TRIM function to clean data by removing excess spaces and employing the
Remove Duplicates tool are vital for maintaining accurate and consistent master
data lists, preventing lookup failures and ensuring reliable analysis. Finally, the
ability to create user-friendly data entry forms streamlines record input, minimizing
manual errors and enhancing overall system usability, ultimately contributing to a
robust and dependable Excel-based AIS.

STUDY GUIDE

Watch Tutorial Videos and Apply Learnings Using Data in Excel

A. Fundamental Excel Functions for Data Manipulation and Analysis

Excel offers a rich set of functions that enable users to perform complex calculations,
automate tasks, and extract meaningful insights from data.
1.1. The IF Function: Introducing Basic Programming Logic
TUTORIAL VIDEO:
01. Using the IF Function in Excel to Program Your Spreadsheets
LINK: [Link]
LENGTH: 00:05:57
CHANNEL: Technology for Teachers and Students

The IF function is a powerful tool for incorporating conditional logic into

spreadsheets, akin to "simple programming." It allows Excel to make decisions based
on specified criteria.
• Core Concept: The IF function evaluates a condition and returns one value if
the condition is true, and another if it's false. This is described as an "if-then
statement," where "if this is true then do this."

• Syntax (Simplified): =IF(condition, value_if_true, value_if_false)The "then"

portion of the formula is denoted by the first comma after the condition.

• The "if not" or "if false" portion is denoted by the second comma.

• Example Application: Determining "Pass" or "Fail" for student scores.

• =IF(C3>69, "Pass", "Fail") – This formula checks if the score in cell C3 is greater
than 69. If true, it displays "Pass"; otherwise, it displays "Fail."

• Efficiency: The autofill handle (the small square in the lower right corner of a
selected cell) can be used to quickly copy IF formulas down a column, applying
the logic to multiple rows without retyping.

1.2. COUNT, COUNTA and COUNTIFS: Counting Data

TUTORIAL VIDEOS
02. COUNT and COUNTA Functions
LINK: [Link]
LENGTH: 00:06:57
CHANNEL: Technology for Teachers and Students
03. The Excel COUNTIF and COUNTIFS Functions
LINK: [Link]
LENGTH: 00:04:52
CHANNEL: Technology for Teachers and Students
These functions help determine the number of entries in a given range.
• COUNT Function Purpose: "Only counts cells in that range that have a number
in them." If the selected range contains only text, it returns zero.

• COUNTA Function Purpose: "Count all" cells in a range that contain any type
of data, including "text." This is useful for counting total records, regardless of
data type.

• COUNTIFS Function Purpose: Count the number of cells that meet multiple
criteria across one or more ranges. COUNTIF checks only one condition.
However, since COUNTIFS is also able to check with only one condition, it is
best to use the plural version always.

Status Bar Alternative: For quick counts, sum, and average, selecting a column or
range will display this information directly on the status bar at the bottom of the
Excel window. This can be customized to show additional metrics like minimum
and maximum.

1.3. SUMIFS and AVERAGEIFS: Conditional Aggregation

TUTORIAL VIDEOS
04. The Excel SUMIFS Function
LINK: [Link]
LENGTH: 00:06:11
CHANNEL: Technology for Teachers and Students

05. The Excel AVERAGEIFS Function

LINK: [Link]
LENGTH: 00:07:19
CHANNEL: Technology for Teachers and Students

These functions allow for summing or averaging data based on one or more criteria,
providing powerful analytical capabilities.
• SUMIFS Function Purpose: Calculates the sum of a range of cells that meet
multiple criteria. "This function is just a fantastic function very useful very
helpful." Its singular form is SUMIF which sums only one condition. However,
since SUMIFS is also able to sum with only one condition, it is best to use the
plural version always.

o Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,

criteria2], ...)

o Example Application: Calculating the "cost of blu-rays" or "mystery DVDs."

 o Efficiency: Using CTRL + SHIFT + DOWN ARROW is a recommended
method for selecting a large sum_range or criteria_range.

• AVERAGEIFS Function Purpose: Calculates the average of a range of cells that

meet multiple criteria. Similar to SUMIFS in its ability to apply multiple
conditions. It also has a singular form, AVERAGEIF, but like SUMIF, it is
recommended to use the plural version always.

1.4. VLOOKUP, HLOOKUP, MATCH, and INDEX: Data Extraction and Positioning
TUTORIAL VIDEOS:
06. VLOOKUP, MATCH and INDEX
LINK: [Link]
LENGTH: 00:11:57
CHANNEL: Technology for Teachers and Students

07. How to Extract Data from a Spreadsheet using VLOOKUP, MATCH and INDEX
LINK: [Link]
LENGTH: 00:15:53
CHANNEL: Tuts+ Computer Skills

These functions are crucial for extracting specific data from large datasets and
understanding data positioning within a spreadsheet.
• VLOOKUP (Vertical Lookup):Purpose: "Links two different tables" to "grab
actual data from a worksheet" based on a "unique identifier." The 'V' stands
for vertical, meaning it searches down columns.

o Syntax: =VLOOKUP(lookup_value, table_range, column_number,

[range_lookup])lookup_value: The unique identifier (e.g., an order number).

o table_range: The entire table where the data is located. It's recommended
to use a range name for this to simplify formulas and ensure absolute
referencing when autofilling.

o column_number: The column number within the specified table_range (not

the worksheet column letter) that contains the desired data.

o [range_lookup] (Optional): TRUE for approximate match (data must be

sorted ascending), FALSE for an exact match. "When we want to plug in an
order number and find the sale amount of that order number, we really don't
want an approximation."

• HLOOKUP (Horizontal Lookup): Works "the exact same way as VLOOKUP" but
with data arranged "across rows" (H is for horizontal).

• MATCH Function Purpose: Determines the "positioning of data" or "where your

data are located rather than what is the actual value of the data." It tells you
the relative position (row or column number) of a lookup value within a
specified range.
 o Syntax: =MATCH(lookup_value, lookup_array, [match_type]) match_type:
Can be -1 (closest value above, data sorted descending), 0 (exact match),
or 1 (closest value below, data sorted ascending, default).

• INDEX Function Purpose: "Kind of like the opposite of the MATCH function," it
"deals with two dimensions," rows and columns. You provide a row and
column number, and it returns the value in that specific cell within a given
array.

o Syntax: =INDEX(array, row_num, [column_num])

Combined Power: When used together, MATCH and INDEX can provide a more
flexible and robust alternative to VLOOKUP, especially when the lookup
column is not the first column in the table.

B. Data Cleaning: Removing Duplicates

TUTORIAL VIDEOS
08. Remove Duplicate Tool
LINK: [Link]
LENGTH: 00:03:16
CHANNEL: Technology for Teachers and Students

09. Using the Trim Function in Excel

LINK: [Link]
LENGTH: 00:05:06
CHANNEL: Technology for Teachers and Students

The "Remove Duplicates" tool helps in cleaning and refining datasets by eliminating
redundant entries.
• Location: Data tab -> Data Tools group -> "Remove Duplicates."

• Functionality: By default, it "probably going to check all of your columns."

However, users can "unselect all and then choose" specific columns to search
for duplicates (e.g., only "Band" names to get a list of unique bands).

• Application: Useful for creating lists of unique items or simplifying datasets

for specific analytical purposes. Users can save the resulting unique list as a
separate spreadsheet.

The TRIM function in Excel is used to clean up text by removing extra spaces. What
it does is removes all leading spaces (before the text), all trailing spaces (after the
text), and reduces any multiple spaces between words to just a single space. It is
important, especially for AIS work to ensure data consistency, as extra spaces can
cause errors in lookups (VLOOKUP, MATCH, etc.) because "Apple " (with a space) is
not the same as "Apple". It also prevents error by avoiding mismatches when
comparing or matching imported data (common in accounting systems).

C. Resolving Common Excel Errors

TUTORIAL VIDEOS
Excel often displays error messages when formulas are incorrect or data is
problematic. Understanding these errors is crucial for effective troubleshooting. The
IFERROR function is a versatile tool for making spreadsheets "not so ugly" by
replacing error messages with blank cells or custom messages.
TUTORIAL VIDEO
10. DIV/0, N/A, & NAME?
LINK: [Link]
LENGTH: 00:14:26
CHANNEL: Technology for Teachers and Students

3.1. DIV/0 Error: Division by Zero

• Cause: Occurs when a formula attempts to divide a number by zero, which is
mathematically impossible. "Dividing by 0 as you probably know is not
possible."

• Solution: Ensure the divisor is not zero.

• Use the IFERROR function to display a blank or a custom message (e.g., "No
purchases") instead of the error.

• Example: =IFERROR(D2/E2, "") (displays blank) or =IFERROR(D2/E2, "No

purchases")

3.2. N/A Error: Not Available

• Cause: Most commonly arises when VLOOKUP or similar lookup functions
cannot find the specified lookup value in the designated range.

• Common Causes and Fixes: Misspellings: Ensure the lookup value is spelled
exactly as it appears in the data.

• Extra Spaces: Leading or trailing spaces can cause a mismatch. The TRIM
function (=TRIM(cell)) can remove excess spaces from data.

• Number Formatting: If dealing with numbers, ensure that the number format
of the lookup value matches the format in the lookup range (e.g., both are
numbers or both are text).

• Solution: Employ the IFERROR function to display a user-friendly message

(e.g., "Not in inventory") when a lookup value is not found.

• Example: =IFERROR(VLOOKUP(...), "Not in inventory")

3.3. NAME Error: Unrecognized Name

• Cause: Occurs when Excel does not recognize a name used in a formula. This
usually points to a misspelling or an undefined name.

• Common Causes and Fixes:Misspelled Named Cells: If referencing a named

range (e.g., height), ensure the name is spelled correctly. The Name Box
(upper-left corner) lists all defined names.
 • Misspelled Functions: Typing a function name incorrectly (e.g., sum as su,
average as avg).

• Incorrect Quote Usage: Using quotes when they shouldn't be used (e.g.,
around a named range) or omitting them when they are required (e.g., around
text strings in IF function results).

• Solution: Carefully check the spelling of all names and functions in the
formula.

TUTORIAL VIDEO
11. NULL, NUM & #####
LINK: [Link]
LENGTH: 00:10:01
CHANNEL: Technology for Teachers and Students

3.4. NULL Error: Intersection of Non-Intersecting Ranges

• Cause: Occurs when a formula, especially SUM, tries to identify an
"intersection of two ranges" but there is no actual overlap. This typically
happens when a space is used between range references where a comma or
other operator should be.

• Solution: Replace the space with a comma to correctly separate the ranges
for the intended operation.

• Incorrect: =SUM(A1:A8 B10:B15)

• Correct: =SUM(A1:A8, B10:B15)

3.5. NUM Error: Invalid or Out-of-Range Numbers

• Cause: Occurs when a formula attempts to produce an "invalid number" or
asks Excel to perform an "impossible calculation." This can also happen if a
number is "too big for Excel or too small for Excel."

• Common Causes and Fixes:Impossible Calculations: Trying to calculate the

square root of a negative number.

• Numbers Beyond Excel's Range: Extremely large (greater than 1 followed by

308 zeros) or extremely small (less than -1 followed by 308 zeros) numbers.

• Solution: Review the formula and data to ensure valid mathematical

operations. The IFERROR function can again be used to mask these errors.

• Example: =IFERROR(SQRT(C7), "")

3.6. ##### Error: Content Too Wide

• Cause: The simplest error, this occurs when the numeric content of a cell is
too wide to be displayed within the current column width. "Excel cannot fit the
contents of this cell in the space provided."

• Solution:Manually Resize: Click and drag the line between column letters to
expand the column.
 • AutoFit: "Double click on the line between D and E in this case" to
automatically resize the column to fit its contents.

TUTORIAL VIDEO
12. REF and VALUE
LINK: [Link]
LENGTH: 00:06:07
CHANNEL: Technology for Teachers and Students

3.7. REF Error: Invalid Cell Reference

• Cause: Occurs when a formula refers to cells that "no longer exist" because
they have been deleted or corrupted. This is often the result of deleting entire
rows or columns that were referenced in a formula.

• Solution:Undo: The quickest fix is to undo the deletion (CTRL + Z).

• Manual Correction: If undo is not an option, manually edit the formula to refer
to valid cells.

3.8. VALUE Error: Incorrect Data Type or Argument

A. Cause: Occurs when a formula uses "the wrong function argument" or tries to
"combine different types of data that really don't go together" (e.g., text
multiplied by a number).

B. Common Causes and Fixes:Mixing Data Types: Attempting arithmetic

operations on text values.

C. Extra Spaces in Numbers: A number with an accidental space might be

interpreted as text.

D. Non-Numeric Characters: Including symbols (other than allowed formatting

like dollar signs) within a number might cause it to be treated as text.

E. Solution: Inspect the data in the referenced cells to ensure they are of the
correct type for the operation. Remove any non-numeric characters or extra
spaces in numeric cells.

D. Streamlining Data Entry: Easy Data Entry Forms

TUTORIAL VIDEO
13. Creating data entry forms
LINK: [Link]
LENGTH: 00:09:51
CHANNEL: Technology for Teachers and Students

Excel's built-in "Form" feature provides a more user-friendly interface for data entry,
especially for wide spreadsheets with many columns.

CHECK YOUR UNDERSTANDING

Answer each question in 2-3 sentences.
1. What is the primary purpose of the Excel IF function, and how does it resemble
basic programming?
2. Explain how to denote the "then" and "if not" (or "if false") parts of an IF function
formula in Excel.
3. Describe the DIV/0 error in Excel. What causes it, and how can the IFERROR
function be used to address it?
4. When does the N/A error typically appear in Excel, especially in relation to
functions like VLOOKUP? Provide one common cause and a method to prevent its
display.
5. What is a "Name" error in Excel, and what are two common reasons it might
occur?
6. Briefly explain the purpose of Excel's Data Entry Forms. What is a prerequisite for
creating them, and how do they streamline data entry?
7. What is the main difference between the VLOOKUP and HLOOKUP functions? How
are they similar?
8. Explain the concept of "lookup value," "table range," and "column number" as
arguments in the VLOOKUP function.
9. What is the purpose of the Excel MATCH function? How does it differ from the
INDEX function?
10. Describe the difference between the COUNT and COUNTA functions in Excel.
Provide an example of when you would use each.

5. LEARNING SUMMARY
You have learned and applied the powerful tools and techniques you've explored to
transform your Excel spreadsheets into dynamic and efficient data management
systems and prepare you for the exciting task ahead—designing an Excel-based AIS.
You've gained foundational knowledge in conditional logic with the IF function, akin to
simple programming, allowing your sheets to make decisions. Crucially, you've learned
to diagnose and resolve common Excel errors like DIV/0, N/A, Name, NULL, NUM, #####,
REF, and VALUE, often utilizing the IFERROR function to create cleaner, more professional
spreadsheets.
Your ability to extract and organize data has been enhanced by understanding VLOOKUP,
a cornerstone for linking tables and retrieving specific information, complemented by
the positional insights offered by MATCH and INDEX functions. Furthermore, you now
know how to streamline data entry with custom forms, clean data by removing
duplicates, and import text files efficiently. Practical functions like SUMIFS and
AVERAGEIFS enable complex calculations based on multiple criteria, while COUNT and
COUNTA provide quick numerical and text-based tallies. By continuing to practice these
skills, including leveraging time-saving features like the autofill handle, you are well-
equipped to manage, analyze, and present your data with greater accuracy and
efficiency.
 Watch the Summary Video:

LINK: [Link]
LENGTH: 00:06:22

To effectively design a multi-sheet workbook as an Accounting Information System

(AIS) in Excel, learners would need to master several special functions and tools that
enable robust data management, analysis, and error handling. A module centered on this
topic would cover:
A. Core Data Manipulation and Analysis Functions:
IF Function: This function is crucial for adding "programming" logic to spreadsheets,
allowing for conditional statements where if a condition is true, one action occurs,
and if false, another occurs. This is essential for categorizing transactions (e.g.,
"Paid" vs. "Pending"), applying business rules, or flagging certain entries based on
criteria.
COUNT, COUNTA and COUNTIFS Functions: These functions are used for counting
records. COUNT specifically counts cells containing numbers. COUNTA counts
cells that are not empty, including text. COUNTIFS counts the number of cells that
meet multiple criteria across one or more ranges. These are useful in an AIS for
tracking the number of transactions, employees, or inventory items.
SUMIFS Function: This powerful function allows users to sum values that meet one
or more specified criteria. For an AIS, this is vital for generating reports such as total
expenses for a particular department, total revenue for a specific product, or total
costs for items purchased within a certain date range.
AVERAGEIFS Function: Similar to SUMIFS, this function calculates the average of
values that meet multiple criteria. It can be used for performance analysis within an
AIS, like calculating the average cost of goods sold for a specific category.
VLOOKUP Function: A fundamental function for extracting and linking data across
different tables or sheets. It works by looking up data vertically (down columns)
 using a unique identifier and returning a corresponding value from a specified
column. This is indispensable for an AIS, for example, to pull customer names from
a customer ID or product prices from a product code on different sheets. Learners
should understand its syntax, including the lookup value, table range, column
number, and the true/false argument for approximate or exact matches.

B. Data Integrity and Error Handling

IFERROR Function: This function is essential for creating user-friendly spreadsheets
by allowing Excel users to display a custom message (or nothing at all) instead of
Excel's default error messages (like #DIV/0! or #N/A). This significantly improves
the readability and professionalism of an AIS dashboard or report.
TRIM Function: This function helps clean text data by removing extra spaces from
text, especially leading, trailing, or multiple spaces between words. This is important
because extra spaces can cause lookup functions like VLOOKUP to fail. Data
consistency is crucial for an accurate AIS.

Understanding and Fixing Common Errors: Learners must be able to diagnose and
resolve various Excel errors that can impact an AIS:
#DIV/0!: Occurs when a number is divided by zero or an empty cell. Solutions include
preventing division by zero or using IFERROR.
#N/A: Often appears when VLOOKUP cannot find a specified value, possibly due to
misspellings, extra spaces, or inconsistent number formatting (e.g., numbers stored
as text).
#NAME?: Indicates that Excel doesn't recognize a text entry in a formula, usually
from misspelling a function name or a named range.
#NULL!: Arises when a space is used in a formula where a comma or other symbol
is expected, leading Excel to search for an intersection between ranges that don't
overlap.
#NUM!: Occurs when a formula produces an invalid number (e.g., square root of a
negative number).
#REF!: Signifies an invalid cell reference, typically when cells or columns that a
formula refers to have been deleted.
#VALUE!: Appears when a formula uses the wrong argument type or attempts to
combine incompatible data types, such as multiplying text by a number.
######: Indicates that the column is not wide enough to display the full content of
the cell caused by a number that is too large or too small for Excel to display. This is
a formatting issue rather than a formula error.
C. Efficient Data Entry and Management Tools:
Data Entry Forms: Excel can create user-friendly forms that present data vertically,
making it much easier to enter records, especially for spreadsheets with many
columns, thus reducing entry errors. This tool simplifies the process of adding new
transactions or records to an AIS.
 Remove Duplicates Tool: This feature allows users to quickly identify and remove
duplicate entries based on selected columns or the entire dataset. It is crucial for
maintaining clean and unique master data lists (e.g., customer lists, product
catalogs) within an AIS.
Naming Ranges: Giving descriptive names to ranges or entire tables makes formulas
more readable and easier to manage, especially when copying them across multiple
cells or sheets. This is a best practice for structuring complex AIS workbooks.
Autofill Handle: A simple yet powerful tool for efficiently copying formulas or series
down or across cells, saving significant time when applying calculations to large
datasets.

6. POST-ASSESSMENT (NOT GRADED): [Link]

7. TEAM AND INDIVIDUAL ACTIVITY. Will be conducted in-person.

8. SUMMATIVE TEST. Will be conducted in-person and graded.

9. LEARNING OBJECTIVE ACTIVITIES:

HANDS-ON APPLICATION OF EXCEL FUNCTIONS USING SAMPLE DATA
Passcode and instructions will be given in class.

A. Fundamental Excel Functions for Data Manipulation and Analysis

Act2a Using the IF function with one criterion

SAMPLE DATA: Act2 Sales [Link]
Instructions:
1. Normalize (Correct) all erroneous "Segment" data.

2. Replace values of column “Segment” with the following (may be done

simultaneous with 1):
GOV (for Government)
ENT (for Enterprise)
CHP (for Channel Partners)
MID (for Mid Market)
SMB (for Small Business)

3. Insert Column “AccOfc”.

4. Use IF function to replace values with the following based on the corresponding
criteria (one formula for all rows of data):
AccOfc Segment
HEAD GOV, ENT
REGN CHP
LCAL For the rest of the blank rows
You may use the logical functions "OR" or “AND” to shorten your nested IF
formula.
Act2b IF function with multiple criteria
SAMPLE DATA: Act2 Sales [Link] (Use the updated file after Activity 2a)
Instructions:
1. Make a copy of the "Act2a" sheet and name the new sheet "Act2b".

2. Use IF to replace values of “AccOfc” with the following based on the

corresponding criteria:
Write one formula and copy to all remaining cells.
AccOfc Segment Model
HEAD GOV, ENT 7101, 6101, & 5101
REGN CHP All models
LCAL For the rest of the blank rows
You may use the logical functions "OR" and "AND" to shorten your nested IF
formula.

3. Using only COUNTIFS function, create a dynamic report showing the frequency
distribution by ACCOFC, by SEGMENT, then by MODEL.

Act2c IF function with complex criteria

SAMPLE DATA: Act2 Sales [Link]
(Use the updated file after Activities 2a and 2b)
Instructions:
1. Make a copy of the "Act2b" sheet and name the new sheet "Act2c".

2. Normalize (Correct) all erroneous "Product" data to the following unique values
(use "Codes"):
CODES ACTUAL NAME
AMA Amarilla
BEL Belloponte
CAR Carretera
MON Montana
RIZ Rizonetta
SOR Sorrento

3. Insert column "CommRate" after "AccOfc".

4. Use IF to replace values of “CommRate” with the following based on the

corresponding criteria. Write one formula and copy to all remaining cells.
CommRate Segment Product AccOfc
20% ENT All REGN
15% GOV, CHP AMA, MON, SOR REGN, LCAL
10% GOV, CHP BEL, CAR, RIZ REGN, LCAL
5% Rest All except HEAD
You may use the logical functions "OR" and "AND" to shorten your nested IF
formula.
5. Create a Pivottable report showing the frequency distribution according to the
following:
1. Set Rows to SEGMENT, then PRODUCT
2. Set Columns to ACCOFC, then COMMRATE
3. Set Values to count of COMMRATE frequency

As a pre-requisite to the following activities, name the columns and the data (click on
the column) of the “Top1KMovies” to the range names below. To name the ranges,
Click the column and go to the “Name Box” (found beside the “Formula Bar”) and type
the corresponding names:
Column Range Name
RANK as “Rank_col”
TTLE as “Ttle_col”
INFO as “Info_col”
YEAR as “Year_col”
DIST as “Dist_col”
BUDG as “Budg_col”
DOMO as “Domo_col”
DOMS as “Doms_col”
INTS as “Ints_col”
WOWS as “Wows_col”
RLSD as “Rlsd_col”
GNRE as “Gnre_col”
RUNT as “Runt_col”
LICE as “Lice_col”
These names, in place of their actual ranges, will be used throughout the remaining
activities when referring to columns in the “Top1KMovies” sheet.

Act2d Using COUNT, COUNTA, and COUNTIFS

SAMPLE DATA: Act2 Top1k [Link]
Instructions: In the “Count” sheet, use the COUNT, COUNTA, or COUNTIFS function
to compute for the number of movies that meet the criteria specified:
1. Number of movies with valid budget information

2. Number of movies with budget information (not necessarily valid)

3. Highest earning movies in a specific year

4. Highest earning movies by a specific distributor

5. Low budget movies that became blockbusters

6. Poor opening but became a blockbuster in the same country

7. Poor domestic but became hit in other countries

8. Billion-dollar blockbusters that are not action movies

9. PG-rated blockbusters with under $10M but netted between $100M & $999M

Act2e-1 Using COUNTIFS, AVERAGEIFS, and SUMIFS

SAMPLE DATA: Act2 Top1k [Link]
Instruction: In the “IFS” sheet, use the:
1. COUNTIFS to calculate the number of blockbuster movies a distributor released
under the “COUNT” column.

2. AVERAGEIFS to calculate the average worldwide sales of blockbuster movies a

distributor released under the “AVE” column.

3. SUMIFS to calculate the worldwide sales of blockbuster movies a distributor

released under the “SUM” column.

Act2e-2 Using the SUMIFS Function with Multiple Criteria

SAMPLE DATA: Act2 Top1k [Link]
Instructions:
1. In the “IFSMulti” sheet, use the SUMIFS to calculate the worldwide sales of
blockbuster movies a distributor released under the corresponding License
columns “PG-13”, “PG”, “R”, “G”, and “NA”.

2. Use conditional highlighting to show top 3 movies under each license criterion,
as well as the “Total” column.
See solution in “IFSMulti” sheet.

Activity 2f (VLOOKUP, HLOOKUP, MATCH and INDEX Functions)

Sample Data: Act2 Top1k [Link]

Act2f-1 Using the VLOOKUP Function

SAMPLE DATA: Act2 Top1k [Link]
Instructions:
1. In the “Top1KMovies” sheet select range from B1 to N1001. Name this range
MOVTITLES. This will be the name for the range you have selected, starting with
the “TTLE” column and not including the “RANK” col.

2. In the “Look1” sheet, go to cell B3, where you would type the movie title.

3. Go to the “Data” tab, and click on the “Data Validation” command in “Data Tools”
group. This will bring out the “Data Validation” dialogue box.

4. In the dialogue box, under the “Settings” tab, set the “Validation Criteria” “Allow”
to “List,” and the “Source” to the column “TTLE” of the “Top1KMovies” sheet.
 5. A filter button will appear beside cell B3. Click it to select the movie title you wish
to see details of. You can also type any movie title. If the title exists, it will display
the movie title as is; otherwise, an error message “The value doesn’t match the
data validation restrictions defined for this cell” will appear, which means that the
movie you selected is not in the Top 1K movies. Type or select a valid movie title.

6. Use the VLOOKUP Function to populate the cells in green background based on
the title of the movie. Note that “RANK” will not be populated because it is before
the “TTLE” column (the start of the name range MOVTITLES) and Excel does not
allow the VLOOKUP Function to find values in columns to the left of the lookup
value.

7. Use conditional highlighting to show top 3 movies under each License criterion,
as well as the “Total” column.

Act2f-2 Using the INDEX and MATCH Functions Instead of the VLOOKUP Function
SAMPLE DATA: Act2 Top1k [Link]
Instructions:
1. In the “Look2” sheet, repeat steps 1-4 in Act2f-1, but naming the range “MOVIES”
instead.
2. Use a combination of the INDEX and MATCH Function to substitute for VLOOKUP
to populate the cells in green background based on the title of the movie. Note
that “RANK” will now be populated because the name range MOVIES starts at this
column. The INDEX function allows users to choose values in columns to the left
or right of the looked-up value by the MATCH function.

B. Data Cleaning: Removing Duplicates and extra spaces in texts

Act2g Identifying and Removing Duplicates

SAMPLE DATA: Act2 Top1k [Link]
Instructions:
1. In the “Top1KMovies” sheet, select all Titles under the “TTLE” column of the
“Top1kMovies” sheet.

2. Copy and paste VALUES only in the “Dups” sheet just below the header.

3. Select the “Title” header and all data under it.

4. Go to “Data” tab, and click “Remove Duplicates” in the “Data Tools” group.

5. In the dialogue box “Remove Duplicate Warning”, select “Continue with the current
selection” and press “Remove Duplicates.”

6. Press Ok in the next dialogue box that appears. Excel will alert the user that 12
duplicates will be removed, representing the movie titles that have the same
name. Press Ok in the next dialogue box to remove the duplicates.
 7. To determine which movies have the same titles, go to “Cnt” column and use the
COUNTIFS function to count the number of instances the movie title appears in
the list of movies.

8. Use the Filter tool to select movie titles that have 2 or more counts. This will show
the movies that have the same titles.

Act2h Using the TRIM Function to Normalize Data

SAMPLE DATA: Act2 Top1k [Link]
Instruction: In the “Trim” sheet, use the TRIM function in the cells under the
“Trimmed” column to remove unwanted extra spaces from the movie titles in the
“Title” column. Notice that the values of the column “Len2” (the length of the text in
the “Trimmed” column) are less than “Len1” (the length of the text in the “Title”
column).

C. Resolving Common Excel Errors

Act2i Identifying and Resolving Errors in Excel Functions and Formulas

SAMPLE DATA: Act2 Top1k [Link]
Instructions:
1. Open the “Look2” sheet.

2. Go to any cell and divide “Worldwide Sales” by zero. The #DIV/0! error appears.
Click “Undo”. #DIV/0! occurs when a number is divided by zero or an empty cell.
Solutions include preventing division by zero or using IFERROR.

3. Go to cell B3. Type your first name as the movie title. The #N/A error appears in
all cells referring to the movie title. Click “Undo”. #N/A often appears when
VLOOKUP cannot find a specified value, possibly due to misspellings, extra
spaces, or inconsistent number formatting (e.g., numbers stored as text).

4. Go to cell C6. Edit the function by deleting “V” from the function name. The
#NAME? error appears. Click “Undo”. #NAME? indicates that Excel doesn't
recognize a text entry in a formula, usually from misspelling a function name or a
named range.

5. Go to any cell. Type “=E12 E8” (assuming the space (“ ”) was typed erroneously
instead of a mathematical operation. The #NULL! Error appears. Click “Undo”.
#NULL! arises when a space is used in a formula where a comma or other symbol
is expected, leading Excel to search for an intersection between ranges that don't
overlap.

6. In the same cell, type “SQRT(-4)”. The #NUM! error appears. Click “Undo.”
#NUM! occurs when a formula produces an invalid number (e.g., square root of a
negative number).
 7. Open “Top1KMovies” sheet. Delete the column “TTLE”. Go back to “Look2” sheet.
The #REF! error appears in all cells where the function refers to B3, the movie title.
Click “Undo.” #REF! signifies an invalid cell reference, typically when cells or
columns that a formula refers to have been deleted.

8. Go to cell B3 and select the movie title “Avengers: Infinity War.” Go to cell E13,
where the text “BUDGET NA” appears (IFERROR was used in the formula to display
this text if the “Budget” value is not a number). Type “=E12-E8” (compute the net
income by subtracting “Worldwide Sales” and “Budget.” However, since the
“Budget” is not a number, the #VALUE! error appears. Click “Undo.” #VALUE!
appears when a formula uses the wrong argument type or attempts to combine
incompatible data types, such as multiplying text by a number.

9. Right-mouse click on column E and set the “Column Width” to 5. All cells
displaying numbers are replaced with “##” (maybe more “#” depending on the
length of the number and the width of the column). Click “Undo” and set the
column E width back to 12. The error indicates that the column is not wide
enough to display the full content of the cell caused by a number that is too large
or too small for Excel to display. This is a formatting issue rather than a formula
error. To correct this, widen the column width.

D. Streamlining Data Entry: Easy Data Entry Forms

Act2j Using the Form Tool for Data Entry and Input Validation
SAMPLE DATA: Act2 COA Data [Link]
Instructions:
A. Add the Form tool to the Quick Access Toolbar

1. Add the "Form" command to the Quick Access Toolbar by clicking the “Customize
Quick Access Toolbar” -> "More Commands" -> "Commands Not in Ribbon."
Alternately, you can add it by clicking “File”->”Options”->Quick Access Toolbar.
Choose commands from “Commands Not in The Ribbon” and scroll down on the
list of commands. Click “Add > >” to add the form to the Quick Access Toolbar.

2. Open “COA” sheet. The data must first be converted into an Excel Table (select
data, then CTRL + T). Choose your preferred style from the “Table Styles” group.

B. Add data validation to restrict values during data entry minimizing errors

3. Select data under column A, “ACCT_ID” (do not include header and the first entry
with “0” as code). Go to “Data” tab. Click “Data Validation” in the Data Tools
group. In the dialogue Settings tab, set the following validation rule:
“Allow”= Whole number
“Minimum” = 100
“Maximum” = 999
This forces Excel to allow only whole numbers from 100 to 999 as valid input
values.

4. Select data under column B, “ACCT_DE” (do not include header). Go to “Data” tab.
Click “Data Validation” in the Data Tools group. In the dialogue Settings tab, set
the following validation rule:
 “Allow”= Text length
“Minimum” = 3 characters
“Maximum” = 30 characters
This forces Excel to allow only texts with at least 3 and at most 30 characters.

5. Select data under column C, “ACCT_EL” (do not include header). Go to “Data” tab.
Click “Data Validation” in the Data Tools group. In the dialogue Settings tab, set
the following validation rule:
“Allow”= List
“Source” = “A,L,E”
This forces Excel to allow only the letters specified as valid input values: “A” for
Asset; “L” for Liability; and “E” for Equity.

6. Select data under column D, “ACCT_CL” (do not include header). Go to “Data” tab.
Click “Data Validation” in the Data Tools group. In the dialogue Settings tab, set
the following validation rule:
“Allow”= List
“Source” = Click button on the right. In the range input, click “AcctCls”
sheet, then select all the values under the column “Code” (do
not include header)
This forces Excel to allow only the codes from the range as valid inputs.

No validation rule will be defined for column E, “ACCT_OF”.

7. Select data under column F, “ACCT_NB” (do not include header). Go to “Data” tab.
Click “Data Validation” in the Data Tools group. In the dialogue Settings tab, set
the following validation rule:
“Allow”= List
“Source” = “C,D”
This forces Excel to allow only the letters specified as valid input values. “C” for
Credit and “D” for Debit.

Your Chart of Accounts table is now ready for data input.

C. Input additional accounts into the table

1. Click the Form Tool from the Quick Access Toolbar.

2. Start adding more accounts below to the table. Use the buttons to the right to
manage your data entry.
ACCT_ID ACCT_DE ACCT_EL ACCT_CL ACCT_OF ACCT_NB
0 Income Summary E RE C
111 Cash on hand A CA D
151 Leasehold Improvements A NC D
AccDep-Leasehold
219 Improvements A NC C
310 Accounts Payable L CL C
410 Long-Term Payables L LT C
 510 Capital, Regala E EC C
519 Withdrawal, Regala E EC D
600 Income Summary E ES C
710 Sales Revenue E RE C
811 Purchases E CS D
820 Salaries & Benefits E XA D
841 Rent E XS D

3. When you’re done adding, click “Close”.

10. GLOSSARY OF KEY TERMS

Arguments: The values or cell references that a function needs to perform its
calculation. Arguments are enclosed in parentheses after the function name and
separated by commas.

Autofill Handle: A small square in the lower-right corner of a selected cell or range.
When dragged, it copies the cell's content or formula to adjacent cells, automatically
adjusting references as needed.

AVERAGEIFS Function: An Excel function used to calculate the average of cells that
meet multiple specified criteria.

Column Index Number: In lookup functions like VLOOKUP, this refers to the numerical
position of the column (starting from 1 for the leftmost column of the lookup range)
from which to retrieve data.

COUNT Function: An Excel function that counts the number of cells in a range that
contain numbers.

COUNTA Function: An Excel function that counts the number of cells in a range that
are not empty (i.e., contain any type of data, including numbers, text, or errors).

COUNTIFS Function: An Excel function that counts the number of cells in a range that
that meet one or multiple criteria.

Criteria (in Forms): A feature within Excel Data Entry Forms that allows users to filter
records based on specific conditions, making it easier to find particular entries.

Criteria Range: In "IFS" functions (e.g., SUMIFS, AVERAGEIFS), this is the range of
cells that Excel will examine to find specific criteria.

Data Entry Forms: A built-in Excel feature that creates a user-friendly, vertical
interface for entering, viewing, and modifying records in a spreadsheet, especially
helpful for wide tables.

DIV/0 Error: An Excel error message that appears when a formula attempts to divide
a number by zero, which is mathematically impossible.
HLOOKUP Function: (Horizontal Lookup) An Excel function used to look for data in
the top row of a table or range and return a value from a specified row in the same
column.

IF Function: An Excel logical function that performs a logical test and returns one
value if the condition is true and another value if the condition is false.

IFERROR Function: An Excel function that allows you to specify a value or action to
perform if a formula results in an error. It helps to display cleaner results by handling
errors gracefully.

INDEX Function: An Excel lookup and reference function that returns the value or
reference of the cell at the intersection of a particular row and column in a given
range.

Lookup Array: In the MATCH function, the range of cells that is searched for the
lookup value.

Lookup Value: The value that you want to find in the first column (VLOOKUP) or row
(HLOOKUP) of the table array.

MATCH Function: An Excel lookup and reference function that searches for a
specified item in a range of cells and returns the relative position of that item.

NAME Error: An Excel error message that appears when Excel does not recognize text
in a formula, often due to a misspelled function name or a reference to a non-existent
named range.

Named Range: A user-defined name given to a cell or a range of cells in an Excel

workbook. It makes formulas easier to read and understand, and provides an
absolute reference.

N/A Error: (Not Available) An Excel error message indicating that a formula cannot
find what it's looking for, commonly occurring with lookup functions like VLOOKUP
when the lookup value is not found.

NULL Error: An Excel error message that usually indicates a space was used in a
formula where a comma or other operator was expected, leading Excel to look for the
intersection of two ranges that don't intersect.

NUM Error: An Excel error message that appears when a formula produces an invalid
numeric result, such as attempting to calculate the square root of a negative number
or producing a number too large or too small for Excel to display.

Quick Access Toolbar (QAT): A customizable toolbar located above the ribbon in
Excel, allowing users to add frequently used commands for easy access.
REF Error: (Reference Error) An Excel error message that occurs when a formula
refers to an invalid cell, typically because the referenced cells or columns have been
deleted or moved.

Remove Duplicates Tool: An Excel feature that identifies and deletes duplicate
entries within a selected range or table, allowing users to obtain a list of unique
values.

Restore (in Forms): A button in Excel Data Entry Forms that discards any changes
made to the current record since it was last saved or navigated away from, reverting
to its original state.

Status Bar: The bar at the bottom of the Excel window that displays information
about the current workbook, such as "Ready," "Enter," and often provides quick
statistics like "Count," "Sum," and "Average" for selected cells.

SUMIFS Function: An Excel function used to add up cells that meet multiple specified
criteria.

Sum Range: In "IFS" functions (e.g., SUMIFS, AVERAGEIFS), this is the range of cells
that Excel will sum or average if the criteria are met.

Table (Excel Table): A structured range of data in Excel that has specific features for
easier management and analysis, such as automatic formula copying, structured
referencing, and built-in filtering.

Table Array: In lookup functions like VLOOKUP, the range of cells that contains the
data to be searched.

TRIM Function: An Excel text function that removes all spaces from text except for
single spaces between words. Useful for cleaning data with accidental extra spaces.

VALUE Error: An Excel error message that indicates a formula contains an incorrect
data type or argument. This often occurs when trying to perform mathematical
operations on non-numeric text, or when mixing incompatible data types.

VLOOKUP Function: (Vertical Lookup) An Excel function used to look for data in the
first column of a table or range and return a value from a specified column in the
same row.

- end of module -
ASSESSMENT TOOLS (NOT SHARED TO STUDENTS)
2. ANSWERS TO “CHECK YOUR UNDERSTANDING” QUESTIONS

1. What is the primary purpose of the Excel IF function, and how does it resemble
basic programming?
The primary purpose of the Excel IF function is to allow users to introduce
conditional logic into their spreadsheets, similar to an "if-then" statement in
programming. It tests a condition and performs one action if true, and another if
false. This capability makes spreadsheets more dynamic and automated.

2. Explain how to denote the "then" and "if not" (or "if false") parts of an IF function
formula in Excel.
In an IF function formula, the "then" portion is denoted by the first comma after
the logical test. The "if not" or "if false" portion is denoted by the second comma,
which separates the value to return if the condition is true from the value to return
if the condition is false. Values to be displayed as text must be enclosed in
quotation marks.

3. Describe the DIV/0 error in Excel. What causes it, and how can the IFERROR
function be used to address it?
The DIV/0 error occurs when a formula attempts to divide a number by zero,
which is mathematically impossible. To fix it, the IFERROR function can be
wrapped around the division formula. This allows Excel to display a custom
message or a blank cell instead of the unsightly error.

4. When does the N/A error typically appear in Excel, especially in relation to
functions like VLOOKUP? Provide one common cause and a method to prevent
its display.
The N/A error commonly appears when using lookup functions like VLOOKUP if
the value being searched for is "not available" or not found within the specified
data range. Common causes include misspellings, extra spaces, or incorrect data
formatting (e.g., a number formatted as text). The IFERROR function can be used
to display a more user-friendly message, like "Not in inventory," instead of N/A.

5. What is a "Name" error in Excel, and what are two common reasons it might
occur?
A "Name" error in Excel indicates that the formula contains unrecognized text,
often because a named range or function name has been misspelled. Two
common reasons for this error are misspelling a defined name for a cell (e.g.,
"heihgt" instead of "height") or incorrectly typing a built-in Excel function (e.g.,
"sum" instead of "SUM" or "AVG" instead of "AVERAGE" if AVG is not a defined
name).

6. Briefly explain the purpose of Excel's Data Entry Forms. What is a prerequisite for
creating them, and how do they streamline data entry?
Excel's Data Entry Forms provide a vertical interface for inputting records into a
spreadsheet, making data entry easier and less prone to mistakes, especially for
wide tables. A prerequisite for using these forms is that the data must first be
converted into an Excel Table. These forms streamline entry by providing clear
fields and navigation buttons (Find Next, New, Delete, Criteria, Restore).
 7. What is the main difference between the VLOOKUP and HLOOKUP functions?
How are they similar?
The main difference between VLOOKUP and HLOOKUP lies in how they search for
data: VLOOKUP (Vertical Lookup) searches for data arranged down columns,
while HLOOKUP (Horizontal Lookup) searches for data arranged across rows.
They are similar in that both functions are used to extract specific data from a
larger dataset based on a unique identifier.

8. Explain the concept of "lookup value," "table range," and "column number" as
arguments in the VLOOKUP function.
In the VLOOKUP function:
• The "lookup value" is the unique identifier you are searching for (e.g., an order
number).
• The "table range" is the entire block of data where VLOOKUP will perform its
search.
• The "column number" is the numerical position of the column within the
specified "table range" that contains the data you want to retrieve, counting
from the leftmost column of the range.

9. What is the purpose of the Excel MATCH function? How does it differ from the
INDEX function?
The Excel MATCH function is used to find the relative position of an item in a range
of cells. It tells you where a value is located within a list (e.g., the 5th item). The
INDEX function, in contrast, is used to return the value of a cell at a specified
intersection of a row and column within a given range. They are often used
together, with MATCH providing the position for INDEX to retrieve the value.

10. Describe the difference between the COUNT and COUNTA functions in Excel.
Provide an example of when you would use each.
The COUNT function in Excel only counts cells within a specified range that
contain numbers. The COUNTA function, however, counts cells that contain any
type of data, including numbers, text, or error values, essentially counting non-
empty cells. You would use COUNT to find out how many numerical entries are in
a column (e.g., total units sold), and COUNTA to find out how many total records
there are, regardless of data type (e.g., total entries in a column of names).

3. PRE-ASSESSMENT: [Link]
True or False (5 items)
1. When using the VLOOKUP function in Excel, if you have already defined a range name
for your data table (e.g., "data"), you are still required to go back to the source data
sheet and select the entire table with an absolute reference in your formula.
Answer: False. If you have created a range name for your data table, you do not need
to go back to the source data sheet and select the entire table as an absolute
reference. You can simply use the defined range name (e.g., typing "data") directly in
your VLOOKUP formula, which can be easier than dealing with absolute references
in long formulas.
2. The DIV/0, N/A, and NUM errors in Excel can all be effectively hidden or replaced with
custom messages within a cell by wrapping the original formula with the IFERROR
function.
Answer: True. The IFERROR function is a versatile tool for handling various Excel
errors. It can be used to prevent the display of unsightly error messages like:
▪ DIV/0 errors, by printing nothing or a custom message like "no purchases".
▪ N/A errors, by displaying a message such as "not in inventory".
▪ NUM errors, by producing a blank space or a custom output when an invalid number
is generated.
3. The COUNT function in Excel will return a non-zero value for a column that contains
only text entries, as long as those cells are not empty.
Answer: False. The COUNT function is designed to count only cells within a specified
range that contain numbers. If a column contains only words or text, the COUNT
function will return a value of zero, even if the cells are filled. To count cells that
contain any type of data (numbers, text, or errors), you would use the COUNTA
function.
4. The primary purpose of the VLOOKUP function is to extract the actual value of data,
whereas the MATCH function is concerned with the positioning or location of data,
and the INDEX function then uses specified row and column positions to return a
value from a data range.
Answer: True.
▪ VLOOKUP is used to retrieve actual data from a worksheet.
▪ MATCH is primarily focused on identifying the position or location of data within a
range, rather than its value.
▪ The INDEX function is used to return the value in a specific cell by providing its row
and column numbers within a given range. These three functions can be used
together or individually to efficiently manage and extract information from large
datasets.
5. In an Excel IF function, the 'then' and 'if not' (or 'if false') portions of the formula are
clearly denoted by the literal words "then" and "else" within the formula, similar to
basic programming "if-then" statements.
Answer: False. While the IF function is compared to "if-then" statements in
programming, the literal words "then" or "else" are not used in the Excel formula.
Instead, the 'then' portion of the formula is denoted by the first comma after the
logical test, and the 'if not' (or 'if false') portion is denoted by the second comma.

Identification (5 items)
1. When utilizing the VLOOKUP function in Excel, if a range name (e.g., "data") has
already been defined for your data table, you can bypass the need to select the entire
table with an absolute reference by simply typing the __________ __________ directly
into your formula.
 Answer: range name (or word data, or name). If you have created a range name for
your data table, such as "data," you do not need to go back to the source data sheet
and select the entire table as an absolute reference. Instead, you can simply type the
defined range name, which is often easier to manage in long formulas. This also
means you don't have to press F3 to select it from a list, you can just type it in.
2. The Excel function designed to count only cells within a specified range that contain
numbers is __________, while the __________ function is used to count cells that
contain any type of data, including text and errors.
Answer: COUNT, COUNTA. The COUNT function specifically counts cells with
numbers, returning zero if a column only contains text. In contrast, the COUNTA
function (short for "count all") counts any non-empty cell, regardless of whether it
contains numbers, text, or errors, which is why it might return a count that includes
header rows if a whole column is selected.
3. The #NULL! error in Excel commonly occurs when a __________ is mistakenly used in
a formula where a comma or other symbol is expected, leading Excel to incorrectly
interpret the formula as an attempt to find an intersection between two ranges that
do not intersect.
Answer: space. A space used in place of a comma or other appropriate symbol in a
formula can cause the #NULL! error. Excel interprets the space as an attempt to
identify an intersection between two ranges, and if no such intersection exists, the
error is produced.
4. To hide or replace various Excel error messages, such as #DIV/0!, #N/A!, or #NUM!,
with a blank or custom message, you can wrap the original formula with the
__________ function.
Answer: IFERROR. The IFERROR function is explicitly presented as a solution to clean
up spreadsheets by replacing unsightly error messages with either blank spaces or
custom text. This applies to #DIV/0! errors, #N/A! errors, and #NUM! errors.
5. When using Excel's built-in data entry forms, a crucial prerequisite is that your data
must first be converted into a/an __________, which then allows you to access the
form button, typically added to the Quick Access Toolbar.
Answer: table. Creating an Excel Table is identified as a necessary first step or
"prerequisite" before the quick data entry forms can be utilized. Once the data is in
an Excel Table, the 'Form' button can be accessed via the Quick Access Toolbar.

Multiple Choice (5 items)

1. What must be done to ensure that text, such as "Pass" or "Fail," is correctly displayed
as the result of an Excel IF function?
A. The text must be enclosed in single quotation marks.
B. The text must be enclosed in double quotation marks.
C. The text must be enclosed in curly braces {}.
D. No special characters are needed; Excel automatically recognizes text.
Answer: B. The text must be enclosed in double quotation marks. When you want
the IF function to "print" text on the screen as its output (either for the 'true' or 'false'
 portion), you must put that text within quotation marks. The example explicitly shows
putting "pass" and "fail" in quotes within the formula.

2. In the VLOOKUP function, setting the last argument to 'FALSE' indicates that the user
is looking for what type of match?
A. An approximate match
B. A partial match
C. An exact match
D. Any match that is close
Answer: C. An exact match. The last argument in VLOOKUP is an optional argument
that can be set to 'TRUE' or 'FALSE'. 'FALSE' means "don't give me an approximation,
give me an exact amount.". Conversely, 'TRUE' means an approximation is
acceptable.

3. Which of the following actions is most likely to result in an Excel #VALUE! error?
A. Dividing a number by zero.
B. Referencing a deleted cell in a formula.
C. Attempting to multiply a text entry by a number.
D. Misspelling a function name, such as "sum" as "su".
Answer: C. Attempting to multiply a text entry by a number. The #VALUE! error often
occurs when you're trying to combine different types of data that don't go together,
such as multiplying a word by a number. Other common causes include accidentally
putting a space in a number cell (making it text) or typing symbols with numbers in
the same cell.
▪ Option A (dividing by zero) causes a #DIV/0! error.
▪ Option B (referencing a deleted cell) causes a #REF! error.
▪ Option D (misspelling a function name) causes a #NAME! error.

4. When using the autofill handle to copy an Excel formula down a column, such as an
IF or VLOOKUP function, what is actually copied and how is this typically done for
large datasets without dragging?
A. The displayed value is copied by dragging the green square.
B. The formula's literal text is copied by re-typing it for each cell.
C. Only absolute references are copied, requiring manual adjustment for relative
ones.
D. Copy the formula, and for large datasets, double-click the Crosshair.
Answer: D. Copy the formula, and for large datasets, double-click the Crosshair. The
autofill handle copies whatever is in the cell, which is the formula itself, not just the
displayed word or value. For a large number of rows (e.g., 200), instead of clicking
and dragging, you can double-click the Crosshair (the small square in the lower right
corner of the cell), and it will automatically fill down the column until it encounters a
gap.

5. Which Excel function is specifically mentioned as a way to "repair data" by removing

extra spaces from text entries, often to resolve #N/A errors in VLOOKUP scenarios?
A. CLEAN
B. TRIM
 C. SUBSTITUTE
D. REPLACE
Answer: B. TRIM. The TRIM function is demonstrated as a method to "repair that
data to make sure that there are no extra spaces" in text, particularly when extra
spaces might be causing #N/A errors in VLOOKUP attempts.

4. POST-ASSESSMENT: [Link]
True or False (5 items)
1. If you use the autofill handle by double-clicking the little green square in the corner
of a cell containing an IF function that outputs text (e.g., "Pass" or "Fail"), Excel will
copy the displayed word down the column, rather than the underlying formula.
Answer: False. When you use the autofill handle, Excel copies whatever is in the cell,
which is the underlying formula itself, not just the displayed word or value. The word
“pass” is not really in the cell. You can see in the formula bar what's actually in the
cell; and that's what's going to get copied, the formula.
2. In the VLOOKUP function, the "column number" argument specifies the letter of the
column (e.g., 'C' for column C) on the worksheet where the desired data is located.
Answer: False. The "column number" argument in VLOOKUP refers to the column's
numerical position within the selected data table range, not its letter on the
worksheet. Don't get that confused with the fact that you can see column H. We're
referring to the actual column number of the data table not the column of the
worksheet.
3. The Excel #NULL! error primarily appears when a formula attempts to divide a
number by zero or by an empty cell.
Answer: False. The #NULL! error occurs when a formula includes a space where a
comma or other symbol should be, indicating that Excel is trying to find an
intersection between two ranges that do not intersect. Dividing by zero or an empty
cell typically results in a #DIV/0! error.
4. Setting the optional last argument of the VLOOKUP function to 'TRUE' will ensure that
Excel returns an exact match for the lookup value.
Answer: False. For an exact match in VLOOKUP, the optional last argument must be
set to 'FALSE'. Setting it to 'TRUE' indicates that an approximate match is acceptable.
5. When you select an entire column in Excel that contains only text values (e.g., names
or descriptions) and no numbers, the Status Bar will automatically display the count,
sum, and average of the selected cells.
Answer: False. While the Status Bar will display a count even for columns with only
words, it can't give as much data (like sum or average) if the column contains no
numbers. The sum and average are automatically provided especially when selecting
a column with numbers.

Identification (5 items)
1. The IF function enables Excel users to incorporate programming-like logic into their
spreadsheets, drawing parallels to if-then statements. Within the syntax of an IF
formula, the "then" portion, indicating what to do if the condition is true, is
functionally represented by the first ___________.
Answer: comma. The IF function is described as allowing Excel users to put in some
programming into Excel spreadsheets, resembling if-then statements commonly
found in programming. When constructing an IF formula, such as IF(C3 > 69, "Pass",
"Fail"), the "then" part of the statement ("if C3 is greater than 69 then print pass") is
not literally typed as "then". Instead, Excel's IF function uses the first comma in the
formula to denote this "then" condition. This comma separates the logical test (e.g.,
C3 > 69) from the value or action to be performed if the test is true (e.g., "Pass").
2. While the VLOOKUP function is primarily used to extract the actual _______ of data
from a worksheet, the MATCH and INDEX functions are more concerned with the
_______ of data.
Answer: value, positioning. The tutorial video distinguishes the functions by stating
that VLOOKUP helps grab actual data from a worksheet, implying it retrieves the
content or value. In contrast, the MATCH and INDEX functions are really concerned
with the positioning of data where your data are located rather than what is the actual
value of the data.
3. The #NULL! error in Excel typically arises when a formula attempts to identify an
_______ between two ranges that do not intersect, most commonly caused by using
a _______ where a comma or other symbol should be.
Answer: intersection, space. The primary reason for the #NULL! error is when a
"space" is used in a formula when you actually should use a comma or some other
symbol. This space causes Excel to try to identify an intersection of two ranges, and
since there is no intersection between the specified ranges, the error is produced.
4. A mandatory prerequisite for utilizing Excel's easy data entry forms is that the data
must first be converted into an official Excel _______.
Answer: table. The tutorial video explicitly states that "the next thing we need to do
is take this data and turn it into a table, that's a prerequisite of using these quick
forms".
5. When counting records in Excel, the COUNT function will only tally cells that contain
_______, whereas the COUNTA function will count cells that contain _______ or _______.
Answer: numbers, text, numbers. The COUNT function "only counts cells in that
range that have a number in them". In contrast, the COUNTA function (Count All)
"count[s] the total number of Records but you want it to include text" and also counts
cells with numbers.

Multiple Choice (5 items)

1. When applying the IFERROR function to a formula in Excel, what is its primary effect
on how errors are presented to the user?
A. It prevents Excel from performing the calculation that would result in an error.
B. It automatically corrects the logical or syntactical mistake causing the error.
 C. It allows the formula to display a custom message, a blank cell, or nothing at all
instead of the standard error code.
D. It highlights the erroneous cell in red and suggests a specific fix from a predefined
list.
Answer: C. It allows the formula to display a custom message, a blank cell, or nothing
at all instead of the standard error code. The IFERROR function serves to guard
against errors by allowing the user to specify what to display on the screen if an error
occurs. This can be two quotes, or a quotation mark a space and another quotation
mark for a blank cell, or a message to the person who's looking at the spreadsheet
like "no purchases" or "not in inventory". It does not prevent the calculation, correct
the underlying error, or provide specific fix suggestions.
2. Which of the following is the most common reason for encountering a #NAME! error
in an Excel formula?
A. A numerical calculation resulting in a number too large or too small for Excel to
display.
B. An attempt to perform arithmetic operations on cells containing mixed data types,
such as text and numbers.
C. Deletion of cells or columns that were directly referenced by a formula.
D. A misspelling of a function name or a reference to a non-existent named range
within the formula.
Answer: D. A misspelling of a function name or a reference to a non-existent named
range within the formula. The #NAME! error indicates that Excel thinks you might
have used the wrong name. This occurs when a named cell is misspelled (e.g.,
“heigh” instead of “height”) or when a function name is mistyped (e.g., leaving the 'M'
off SUM or using AVG instead of AVERAGE). Other options correspond to different
errors: A is a #NUM! error, B is a #VALUE! error, and C is a #REF! error.
3. To ensure the VLOOKUP function returns an exact match for the lookup_value from
the table_array, how must the optional fourth argument (range_lookup) be
configured?
A. It must be set to FALSE, specifically requesting an exact match.
B. It must be set to TRUE, indicating that only exact matches are acceptable.
C. It should be omitted, as exact match is the default behavior.
D. It must be set to 1 for an exact match, and 0 for an approximate match.
Answer: A. It must be set to FALSE, specifically requesting an exact match. The
source explicitly states that for the optional fourth argument, setting it to "false
means don't give me an approximation give me an exact amount". Setting it to TRUE
would allow for an approximate match.
4. When creating a VLOOKUP formula that will be copied down many rows, using a
named range for the table_array argument is preferred over a standard cell range
(e.g., A1:H200) primarily because:
A. Named ranges automatically expand or contract as data is added or removed from
the table.
B. A named range automatically acts as an absolute reference, preventing lookup
range shifts during autofill.
C. Named ranges allow the VLOOKUP function to perform lookups across multiple,
non-contiguous tables.
D. Formulas using named ranges compute faster than those with standard cell
references.
 Answer: B. A named range automatically acts as an absolute reference, preventing
lookup range shifts during autofill. The tutorial video notes that it could be a little
messy when you're using an absolute reference in in a long formula. Therefore, it's
to create a range name. When you give a name to a range or to a whole table, then
you could use that name in your formulas. This implies the named range handles
the absolute referencing, which is crucial for correct autofilling.
5. Which of the following situations is explicitly cited as a cause for the #NUM! error in
Excel?
A. An attempt to divide a number by zero or an empty cell.
B. Entering a numerical value that is too large or too small for Excel to display.
C. Combining a number with a text string in a mathematical operation.
D. Referencing a deleted cell or range within a formula.
Answer: B. Entering a numerical value that is too large or too small for Excel to
display. The #NUM! error occurs when a formula tries to produce an invalid number.
The tutorial video gives examples of impossible calculation[s]" like “the square root
of negative 4” and a number so big it was bigger than the number one with three
hundred and eight zeros after it. It also states that if you try to type in or calculate a
number that is smaller than minus one with three hundred and eight trailing zeros,
that's also going to produce a num error".

5. LEARNING OBJECTIVE ACTIVITY

HANDS-ON APPLICATION OF EXCEL FUNCTIONS USING SAMPLE DATA
A. Fundamental Excel Functions for Data Manipulation and Analysis
Act2a Using the IF function with one criterion
SAMPLE DATA: Act2 Sales [Link]
Instructions:
5. Normalize (Correct) all erroneous "Segment" data.

6. Replace values of column “Segment” with the following (may be done

simultaneous with 1):
GOV (for Government)
ENT (for Enterprise)
CHP (for Channel Partners)
MID (for Mid Market)
SMB (for Small Business)

7. Insert Column “AccOfc”.

8. Use IF function to replace values with the following based on the corresponding
criteria (one formula for all rows of data):
AccOfc Segment
HEAD GOV, ENT
REGN CHP
LCAL For the rest of the blank rows
You may use the logical functions "OR" or “AND” to shorten your nested IF
formula.
Act2b IF function with multiple criteria
SAMPLE DATA: Act2 Sales [Link] (Use the updated file after Activity 2a)
Instructions:
4. Make a copy of the "Act2a" sheet and name the new sheet "Act2b".

5. Use IF to replace values of “AccOfc” with the following based on the

corresponding criteria:
Write one formula and copy to all remaining cells.
AccOfc Segment Model
HEAD GOV, ENT 7101, 6101, & 5101
REGN CHP All models
LCAL For the rest of the blank rows
You may use the logical functions "OR" and "AND" to shorten your nested IF
formula.

6. Using only COUNTIFS function, create a dynamic report showing the frequency
distribution by ACCOFC, by SEGMENT, then by MODEL.
See solution in sheet Act2b

Act2c IF function with complex criteria

SAMPLE DATA: Act2 Sales [Link]
(Use the updated file after Activities 2a and 2b)
Instructions:
6. Make a copy of the "Act2b" sheet and name the new sheet "Act2c".

7. Normalize (Correct) all erroneous "Product" data to the following unique values
(use "Codes"):
CODES ACTUAL NAME
AMA Amarilla
BEL Belloponte
CAR Carretera
MON Montana
RIZ Rizonetta
SOR Sorrento

8. Insert column "CommRate" after "AccOfc".

9. Use IF to replace values of “CommRate” with the following based on the

corresponding criteria. Write one formula and copy to all remaining cells.
CommRate Segment Product AccOfc
20% ENT All REGN
15% GOV, CHP AMA, MON, SOR REGN, LCAL
10% GOV, CHP BEL, CAR, RIZ REGN, LCAL
5% Rest All except HEAD
You may use the logical functions "OR" and "AND" to shorten your nested IF
formula.
See solution in pseudo-code in sheet Act2cPseudo
10. Create a Pivottable report showing the frequency distribution according to the
following:
1. Set Rows to SEGMENT, then PRODUCT
2. Set Columns to ACCOFC, then COMMRATE
3. Set Values to count of COMMRATE frequency
See solution in sheet Act2cSum

As a pre-requisite to the following activities, name the columns and the data (click
on the column) of the “Top1KMovies” to the range names below. To name the ranges,
Click the column and go to the “Name Box” (found beside the “Formula Bar”) and type
the corresponding names:
Column Range Name
RANK as “Rank_col”
TTLE as “Ttle_col”
INFO as “Info_col”
YEAR as “Year_col”
DIST as “Dist_col”
BUDG as “Budg_col”
DOMO as “Domo_col”
DOMS as “Doms_col”
INTS as “Ints_col”
WOWS as “Wows_col”
RLSD as “Rlsd_col”
GNRE as “Gnre_col”
RUNT as “Runt_col”
LICE as “Lice_col”
These names, in place of their actual ranges, will be used throughout the remaining
activities when referring to columns in the “Top1KMovies” sheet.

Act2d Using COUNT, COUNTA, and COUNTIFS

SAMPLE DATA: Act2 Top1k [Link]
Instructions: In the “Count” sheet, use the COUNT, COUNTA, or COUNTIFS function
to compute for the number of movies that meet the criteria specified:
10. Number of movies with valid budget information

11. Number of movies with budget information (not necessarily valid)

12. Highest earning movies in a specific year

13. Highest earning movies by a specific distributor

14. Low budget movies that became blockbusters

15. Poor opening but became a blockbuster in the same country

16. Poor domestic but became hit in other countries

17. Billion-dollar blockbusters that are not action movies

18. PG-rated blockbusters with under $10M but netted between $100M & $999M
See solution in “Count” sheet.

Act2e-1 Using COUNTIFS, AVERAGEIFS, and SUMIFS

SAMPLE DATA: Act2 Top1k [Link]
Instruction: In the “IFS” sheet, use the:
4. COUNTIFS to calculate the number of blockbuster movies a distributor released
under the “COUNT” column.

5. AVERAGEIFS to calculate the average worldwide sales of blockbuster movies a

distributor released under the “AVE” column.

6. SUMIFS to calculate the worldwide sales of blockbuster movies a distributor

released under the “SUM” column.
See solution in “IFS” sheet.

Act2e-2 Using the SUMIFS Function with Multiple Criteria

SAMPLE DATA: Act2 Top1k [Link]
Instructions:
3. In the “IFSMulti” sheet, use the SUMIFS to calculate the worldwide sales of
blockbuster movies a distributor released under the corresponding License
columns “PG-13”, “PG”, “R”, “G”, and “NA”.

4. Use conditional highlighting to show top 3 movies under each license criterion,
as well as the “Total” column.
See solution in “IFSMulti” sheet.

Act2f-1 Using the VLOOKUP Function

SAMPLE DATA: Act2 Top1k [Link]
Instructions:
8. In the “Top1KMovies” sheet select range from B1 to N1001. Name this range
MOVTITLES. This will be the name for the range you have selected, starting with
the “TTLE” column and not including the “RANK” col.

9. In the “Look1” sheet, go to cell B3, where you would type the movie title.

10. Go to the “Data” tab, and click on the “Data Validation” command in “Data Tools”
group. This will bring out the “Data Validation” dialogue box.

11. In the dialogue box, under the “Settings” tab, set the “Validation Criteria” “Allow”
to “List,” and the “Source” to the column “TTLE” of the “Top1KMovies” sheet.

12. A filter button will appear beside cell B3. Click it to select the movie title you wish
to see details of. You can also type any movie title. If the title exists, it will display
 the movie title as is; otherwise, an error message “The value doesn’t match the
data validation restrictions defined for this cell” will appear, which means that the
movie you selected is not in the Top 1K movies. Type or select a valid movie title.

13. Use the VLOOKUP Function to populate the cells in green background based on
the title of the movie. Note that “RANK” will not be populated because it is before
the “TTLE” column (the start of the name range MOVTITLES) and Excel does not
allow the VLOOKUP Function to find values in columns to the left of the lookup
value.

14. Use conditional highlighting to show top 3 movies under each License criterion,
as well as the “Total” column.
See solution in “Look1” sheet.

Act2f-2 Using the INDEX and MATCH Functions Instead of the VLOOKUP Function
SAMPLE DATA: Act2 Top1k [Link]
Instructions:
3. In the “Look2” sheet, repeat steps 1-4 in Act2f-1, but naming the range “MOVIES”
instead.
4. Use a combination of the INDEX and MATCH Function to substitute for VLOOKUP
to populate the cells in green background based on the title of the movie. Note
that “RANK” will now be populated because the name range MOVIES starts at this
column. The INDEX function allows users to choose values in columns to the left
or right of the looked-up value by the MATCH function.
See solution in “Look2” sheet.

B. Data Cleaning: Removing Duplicates and extra spaces in texts

Act2g Identifying and Removing Duplicates
SAMPLE DATA: Act2 Top1k [Link]
Instructions:
9. In the “Top1KMovies” sheet, select all Titles under the “TTLE” column of the
“Top1kMovies” sheet.

10. Copy and paste VALUES only in the “Dups” sheet just below the header.

11. Select the “Title” header and all data under it.

12. Go to “Data” tab, and click “Remove Duplicates” in the “Data Tools” group.

13. In the dialogue box “Remove Duplicate Warning”, select “Continue with the current
selection” and press “Remove Duplicates.”

14. Press Ok in the next dialogue box that appears. Excel will alert the user that 12
duplicates will be removed, representing the movie titles that have the same
name. Press Ok in the next dialogue box to remove the duplicates.
15. To determine which movies have the same titles, go to “Cnt” column and use the
COUNTIFS function to count the number of instances the movie title appears in
the list of movies.

16. Use the Filter tool to select movie titles that have 2 or more counts. This will show
the movies that have the same titles.
See solution in “Dups” sheet.

Act2h Using the TRIM Function to Normalize Data

SAMPLE DATA: Act2 Top1k [Link]
Instruction: In the “Trim” sheet, use the TRIM function in the cells under the
“Trimmed” column to remove unwanted extra spaces from the movie titles in the
“Title” column. Notice that the values of the column “Len2” (the length of the text in
the “Trimmed” column) are less than “Len1” (the length of the text in the “Title”
column).

C. Resolving Common Excel Errors

Act2i Identifying and Resolving Errors in Excel Functions and Formulas
SAMPLE DATA: Act2 Top1k [Link]
Instructions:
1. Open the “Look2” sheet.

2. Go to any cell and divide “Worldwide Sales” by zero. The #DIV/0! error
appears. Click “Undo”. #DIV/0! occurs when a number is divided by zero or
an empty cell. Solutions include preventing division by zero or using IFERROR.

3. Go to cell B3. Type your first name as the movie title. The #N/A error appears
in all cells referring to the movie title. Click “Undo”. #N/A often appears when
VLOOKUP cannot find a specified value, possibly due to misspellings, extra
spaces, or inconsistent number formatting (e.g., numbers stored as text).

4. Go to cell C6. Edit the function by deleting “V” from the function name. The
#NAME? error appears. Click “Undo”. #NAME? indicates that Excel doesn't
recognize a text entry in a formula, usually from misspelling a function name
or a named range.

5. Go to any cell. Type “=E12 E8” (assuming the space (“ ”) was typed
erroneously instead of a mathematical operation. The #NULL! Error appears.
Click “Undo”. #NULL! arises when a space is used in a formula where a
comma or other symbol is expected, leading Excel to search for an intersection
between ranges that don't overlap.

6. In the same cell, type “SQRT(-4)”. The #NUM! error appears. Click “Undo.”
#NUM! occurs when a formula produces an invalid number (e.g., square root
of a negative number).

7. Open “Top1KMovies” sheet. Delete the column “TTLE”. Go back to “Look2”

sheet. The #REF! error appears in all cells where the function refers to B3, the
 movie title. Click “Undo.” #REF! signifies an invalid cell reference, typically
when cells or columns that a formula refers to have been deleted.

8. Go to cell B3 and select the movie title “Avengers: Infinity War.” Go to cell E13,
where the text “BUDGET NA” appears (IFERROR was used in the formula to
display this text if the “Budget” value is not a number). Type “=E12-E8”
(compute the net income by subtracting “Worldwide Sales” and “Budget.”
However, since the “Budget” is not a number, the #VALUE! error appears. Click
“Undo.” #VALUE! appears when a formula uses the wrong argument type or
attempts to combine incompatible data types, such as multiplying text by a
number.

9. Right-mouse click on column E and set the “Column Width” to 5. All cells
displaying numbers are replaced with “##” (maybe more “#” depending on the
length of the number and the width of the column). Click “Undo” and set the
column E width back to 12. The error indicates that the column is not wide
enough to display the full content of the cell caused by a number that is too
large or too small for Excel to display. This is a formatting issue rather than a
formula error. To correct this, widen the column width.

D. Streamlining Data Entry: Easy Data Entry Forms

Act2j Using the Form Tool for Data Entry and Input Validation
SAMPLE DATA: Act2 COA Data [Link]
Instructions:
A. Add the Form tool to the Quick Access Toolbar

8. Add the "Form" command to the Quick Access Toolbar by clicking the
“Customize Quick Access Toolbar” -> "More Commands" -> "Commands Not
in Ribbon." Alternately, you can add it by clicking “File”->”Options”->Quick
Access Toolbar. Choose commands from “Commands Not in The Ribbon” and
scroll down on the list of commands. Click “Add > >” to add the form to the
Quick Access Toolbar.

9. Open “COA” sheet. The data must first be converted into an Excel Table (select
data, then CTRL + T). Choose your preferred style from the “Table Styles”
group.

B. Add data validation to restrict values during data entry minimizing errors

10. Select data under column A, “ACCT_ID” (do not include header and the first
entry with “0” as code). Go to “Data” tab. Click “Data Validation” in the Data
Tools group. In the dialogue Settings tab, set the following validation rule:
“Allow”= Whole number
“Minimum” = 100
“Maximum” = 999
This forces Excel to allow only whole numbers from 100 to 999 as valid input
values.
11. Select data under column B, “ACCT_DE” (do not include header). Go to “Data”
tab. Click “Data Validation” in the Data Tools group. In the dialogue Settings
tab, set the following validation rule:
“Allow”= Text length
“Minimum” = 3 characters
“Maximum” = 30 characters
This forces Excel to allow only texts with at least 3 and at most 30 characters.

12. Select data under column C, “ACCT_EL” (do not include header). Go to “Data”
tab. Click “Data Validation” in the Data Tools group. In the dialogue Settings
tab, set the following validation rule:
“Allow”= List
“Source” = “A,L,E”
This forces Excel to allow only the letters specified as valid input values: “A”
for Asset; “L” for Liability; and “E” for Equity.

13. Select data under column D, “ACCT_CL” (do not include header). Go to “Data”
tab. Click “Data Validation” in the Data Tools group. In the dialogue Settings
tab, set the following validation rule:
“Allow”= List
“Source” = Click button on the right. In the range input, click
“AcctCls” sheet, then select all the values under the
column “Code” (do not include header)
This forces Excel to allow only the codes from the range as valid inputs.

No validation rule will be defined for column E, “ACCT_OF”.

14. Select data under column F, “ACCT_NB” (do not include header). Go to “Data”
tab. Click “Data Validation” in the Data Tools group. In the dialogue Settings
tab, set the following validation rule:
“Allow”= List
“Source” = “C,D”
This forces Excel to allow only the letters specified as valid input values. “C”
for Credit and “D” for Debit.

Your Chart of Accounts table is now ready for data input.

C. Input additional accounts into the table

1. Click the Form Tool from the Quick Access Toolbar.

2. Start adding more accounts to the table. Use the buttons to the right to
manage your data entry.
ACCT_ID ACCT_DE ACCT_EL ACCT_CL ACCT_OF ACCT_NB
0 Income Summary E RE C
111 Cash on hand A CA D
151 Leasehold Improvements A NC D
 AccDep-Leasehold
219 Improvements A NC C
310 Accounts Payable L CL C
410 Long-Term Payables L LT C
510 Capital, Regala E EC C
519 Withdrawal, Regala E EC D
600 Income Summary E ES C
710 Sales Revenue E RE C
811 Purchases E CS D
820 Salaries & Benefits E XA D
841 Rent E XS D

3. When you’re done adding, click “Close”.

- end of chapter -