Case 19-8: Excel Data — Handout 1
Excel Clean-Up Guide
Benefits of Properly Prepared Data
• Data prepared in common and generally accepted format facilitates an expectation that a data
file has a certain and predictable standard look, feel, and behavior.
• Excel analyzation tools work with properly prepared data in a predictable manner. For
example, if a column contains a U.S. state and uppercase, lowercase, and proper case data are
encountered, generating totals by state will reflect totals per case variations within the “state”
column, and not by state.
• Certain Excel functions, such as VLOOKUP, may not perform properly if a cell is not
formatted left justified.
Excel Data Preparation — Detailed Step-by-Step Instructions
Step 1 — Establish Column Names (Fieldnames) in Row 1.
Row 1 in Excel will become the fieldnames used by the Excel Analytics Toolbar. Use the following
rules for fieldnames:
• The first character in the name must be alpha (A–Z).
• The rest of the fieldname may contain 0–9, A–Z, or an underscore ( _ ).
• No spaces or special characters like %, &, ©, $, or # are allowed.
Make column names abbreviated. Abbreviated means “as short as possible yet recognizable.”
Examples include DOCNO, CUSTNO, DESC, INV_DATE, AMOUNT, ACCT, and PART_NO.
Step 2 — Make sure the first row of data appears in Row 2, immediately below the row containing
fieldnames.
Step 3 — Eliminate nondata items, such as blank rows, columns, page breaks, headings or titles,
subtotals, and totals.
Below the column names in Row 1, the data should begin in Row 2 and be contiguous, with no blank
rows or blank columns, subtotals or totals, or any nondata such as page breaks or report headings.
Consider sorting on a column with predictable content. Example: Your spreadsheet looks like a
report and contains page headers, page breaks, titles, column names, blank lines, dashed lines, totals,
and subtotals repeatedly throughout. You notice Column C is a customer number ranging from
000000 to 999999 and every row that contains an invoice has a customer number in Column C. In
Column C, on the “discard-lines” containing items such as report headings, totals, subtotals, and
blank lines, there are no 0–9 values. By sorting on Column C, all the usable invoice data will fall into
Case 19-8 — Handout 1: Excel Clean-Up Guide Page 2
Copyright 2019 Deloitte Development LLC
All Rights Reserved.
a large, contiguous block (000000–999999). Dashed lines will appear above that block, and all items
such as blank lines, totals, subtotals, and headings, will fall below. Simply delete the dashed lines at
the top, and all of the items such as headings, blank lines, totals, and subtotals, which fell below. The
end result is column names in Row 1 and a contiguous block of data from Row 2 downward. Make
sure the data still foots to your account balance being tested or other expected amount.
Step 4 — Make sure dates and numeric data are formatted in a consistent manner.
Numbers: Format columns containing numbers using the 0.00 format. (Format cells > Number >
Negative numbers: -1234.10) Make sure to specify decimal places for consistent formatting.
Dates: Format dates using the date formatting convention of MM/DD/YY.
• Format cells > Date > Type: 03/14/12 > Locale (location): English (United States). For
example, March 31, 2015, should look like 03/31/15. Note the leading zero.
Step 5 — Make sure text columns are formatted in a consistent manner to ensure Excel functions
execute in a consistent, predictable manner.
Consider the following examples and how they would make data analytics using Excel usage more
difficult.
• If you were to total the accounts receivable by state, and the state column contains uppercase,
lowercase, and proper case data, you would get totals by each of the state-variations based on
case, not by state.
• If you were to perform a VLOOKUP in Excel, leading spaces (spaces on the left) cause
matching issues.
• Some data, such as account numbers and customer numbers, may physically be numeric in
Excel but should be converted to text and left justified in order to perform key functions in
Excel.
Step 6 — Save your spreadsheet, and you are ready for Excel functionality.
Case 19-8: Excel Data — Handout 2
E-mail From Manager — Data Clean-Up
From: Manager
To: Staff Member
Subject: Data Clean-Up Request
Attachments: AR Open Invoice Report.xlsx (Handout 3)
Hi,
I just received the Accounts Receivable Open Invoice Report that I requested from the client. Upon
review, I have determined that the excel data file needs to be cleaned up, and we should be able to
perform the clean-up procedures ourselves. As such, I am asking you to clean up and format the data
received so the information is usable in Excel.
I suggest that you use the Excel Clean-Up Guide (Handout 1) to assist you in performing the
appropriate procedures.
Lastly, the account balance per the general ledger is $45,780,335.21. Please verify that the detail
agrees to that balance.
Thank you,
Case 19-8: Excel Data — Handout 6
E-mail From Manager — Data Manipulation
From: Manager
To: Staff Member
Subject: Data Manipulation Request
Attachments: AR Open Invoice Report.xlsx (Handout 4)
Hi,
Now that you have performed the necessary data clean-up procedures, and the data file is ready for
use in Excel, I would like you to perform the following:
• The Accounts Receivable Open Invoice Report does not include the customer names;
however, we are provided with a customer key that has the customers’ names by customer
number. There is only one unique customer number per customer. Use the VLOOKUP
formula to input the customer names into the report itself.
• Once the customer names are added, please create a PivotTable that shows the credit limit by
customer name for only those customers located in Colorado. Note that each customer only
has one credit limit regardless of how many invoices it has.
Thank you,
The post Case 19-8: Excel Data — Handout 1 Excel Clean-Up Guide appeared first on Versed Writers.