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

1. Master the Excel functions that are needed to create an Excel-Based Accounting
Information System (AIS). Learners will be able to progressively achieve this by
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 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): To Follow

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. VLOOKUP, HLOOKUP, MATCH, and INDEX: Data Extraction and Positioning
TUTORIAL VIDEOS:
02. VLOOKUP, MATCH and INDEX
LINK: [Link]
LENGTH: 00:11:57
CHANNEL: Technology for Teachers and Students

03. 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.

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."

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.

1.4. COUNT and COUNTA: Counting Data

TUTORIAL VIDEO
06. COUNT and COUNTA Functions
LINK: [Link]
LENGTH: 00:06:57
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.

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.

B. Streamlining Data Entry: Easy Data Entry Forms

TUTORIAL VIDEO
07. 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.
1. Accessing the Form Tool: Add the "Form" command to the Quick Access
Toolbar via "More Commands" -> "Commands Not in Ribbon."

2. The data must first be converted into an Excel Table (select data, then CTRL +
T).

• Benefits: Presents data vertically, making it easier to navigate and enter

information for spreadsheets with like 14 columns or 15 columns.

• Provides navigation buttons: "Find Next," "Find Previous," and "New" for
adding new records.

• Restore Button: Allows users to undo changes made within the form before
saving (by clicking "Close" or pressing "Enter").

• Criteria Button: Enables searching and filtering records within the table by
specifying criteria in the form fields. This is useful for finding specific entries
quickly.

C. Data Cleaning: Removing Duplicates

TUTORIAL VIDEO
11. Remove Duplicate Tool
LINK: [Link]
LENGTH: 00:03:16
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.

D. 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
08. 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
09. 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
10. 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

• 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).

• Common Causes and Fixes:Mixing Data Types: Attempting arithmetic

operations on text values.

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

interpreted as text.

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

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

• 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.

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

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.

6. POST-ASSESSMENT (NOT GRADED): To Follow

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
A. Fundamental Excel Functions for Data Manipulation and Analysis
B. Streamlining Data Entry: Easy Data Entry Forms
C. Data Cleaning: Removing Duplicates
D. Resolving Common Excel Errors

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).
• 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 -