CONESTOGA COLLEGE SCHOOL OF BUSINESS
EXCEL WORKBOOK STANDARDS
PROPER WORKBOOK SET-UP
For accounting applications of Excel workbooks it is always advisable to use certain rules of thumb since the
workbook will often be used by someone other than the designer (such as a data entry clerk).
The following are some of the rules to abide by whenever possible.
1. Input and reports should be separated. They should be set up on separate worksheets.
2. User input should be restricted as much as possible to the input sheet.
3. All descriptions and narratives should be very detailed to eliminate any doubt for the user.
4. Data should only have to be entered once by the user.
5. All report dates must be cell referenced to an input cell or calculated automatically by the computer.
STANDARD REQUIREMENTS FOR ALL EXCEL ASSIGNMENTS
The following are expected on all assignments:
1. All printouts must have a header set-up as follows:
• Left - Your Section and Instructor's Initials (e.g. 1A1DH)
• Middle - The Assignment Number
• Right - Your Name
2. The first sheet on all workbooks is to be a Documentation Sheet with the following:
• Company name in large letters
• The date the workbook was prepared. This cannot be the formula =NOW() or =TODAY(), since these
change each time the workbook is reopened.
• The purpose of the workbook. (e.g. to produce financial statements from a trial balance)
• Instructions to the user. These are instructions to future users, not to yourself or your teacher. For
example, if the workbook produces financial statements from a trial balance, the instructions may read:
1. Enter the financial statement date in the area provided on the input sheet.
2. Enter the trial balance in the yellow area on the input sheet (enter credits as negative numbers),check
for a zero total, then click on the appropriate sheet tab to view or print the reports.
3. Remember! The assignments are examples of actual on-the-job workbooks. They must be set up so the
input could be entered by someone other than yourself with limited knowledge of Excel and/or accounting.
4. Assume that all reports will be presented to your boss and thus must be professional in appearance. This
means the proper use of: headings, dollar signs, number formatting, indenting, underlining,centering, etc.
5. Abbreviations should only be used when the full word will not fit.
6. Input sheet cells that are for user entry should be unlocked and coloured for ease of data entry. Each
worksheet should be protected without a password prior to submission.
7. All numeric and date cells in the output sheets should be cell referenced to other cells within the sheet or to
other sheets. Numbers or dates are not to be typed or copied into the output areas.
8. If the date is to be entered by the user, then there should be one cell in the input area to enter it. All other
dates in the workbook must be cell referenced to this cell.
9. There should not be any blank columns in input or reports.
10. All report printouts must be centred horizontally on the page using:
11. Use portrait orientation for all printouts (rather than landscape), unless instructed otherwise.
12. Gridlines should not be used on financial reports.
LS1/4/2011 Page 1 of 3
DOLLAR SIGN RULE
For all financial reports the use of the dollar sign ($) must be consistent. This can be achieved by either
using the format used in you accounting text book or by using the following simple rule:
A dollar sign appears only in the following numbers:
1. The first number in a column.
2. The last number in a column when that number is a double-underlined total. (e.g. Total Assets or
Total Liabilities and Owners Equity on a balance sheet)
3. The next number in a column below a double-underlined total. (e.g. the first liability listed below
total assets on a balance sheet)
CORRECTING ROUNDING ERRORS ON FINANCIAL REPORTS
When the possibility of rounding errors exists on a financial report, it must be eliminated by using the
"precision as displayed" option. To achieve this do the following:
….Tools….Options….Calculation….Precision as displayed
DATES IN HEADINGS
1. Make an input cell (on the Input Sheet) for the user to enter the report date. Instructions must
be given to the user as to how the date must be entered and which date they are to enter. If the
workbook is producing financial statements for the calendar year 1999, then the appropriate
input cell and instructions could be:
Enter the year-end date 12/31/2001
Note that this will result in the date being entered in recognizable date format for any date
arithmetic that may be needed in the workbook.
2. To make the heading on the reports refer to the input cell created, use the concatenate feature. This
allows two or more text items to be combined into one. Assume that the input cell for the date above
has been named Date. The formula for the date line on the Income Statement will be:
=CONCATENATE("For the year ended ",TEXT(Date,"mmmm dd, yyyy"))
or ="For the Year Ended "&TEXT(Date,"mmmm dd, yyyy")
- The "&" connects any two strings. The cell named Date is not a string, but referring to it in a
=TEXT() function converts it to a string.
- Any typed text must be enclosed in quotations ("").
- "mmmm dd, yyyy" is used to state the format desired for the date.
- The above assumes the use of proper case rules (i.e.. first letter capitalized). To display the date
line all in upper case (i.e.. Capital) letters, use:
=CONCATENATE("For the year ended ",UPPER(TEXT(Date,"mmmm dd, yyyy")))
or ="FOR THE YEAR ENDED "&UPPER(TEXT(Date,"mmmm dd, yyyy"))
UNDERLINING AND ACCOUNTING FORMAT
To be consistent, all numbers in financial reports should be in accounting format. Underlining for
column totals should be done using single and double underlining format rather than using borders.
LS1/4/2011 Page 3 of 3
Each day is represented by a number in Excel. For example the day January 1, 2000 is represented by the
number 36526 and January 2, 2000 is 36527 and so on. To get this number into a cell, simply type in the date as
mm/dd/yy or =DATE(yyyy,mm,dd). The display of this date can then be changed to a number of different styles
by using Format–›Cells–›Number–›Date
Because the date is represented by a number (eg. 36526), numeric operations can be done on it. For example:
=DATE(2000,1,1)+30 equals 31-Jan-2000
=DATE(2000,1,1)-DATE(1999,6,30) equals 185 which is the number of days from June 30,
1999 to January 1, 2000
Also note that cell references can be incorporated into the formula. For example:
=DATE(A1,A2,A3) is a valid date when:
cell A1 contains the year
cell A2 contains the month
cell A3 contains the day
1. =NOW() will give the current day and time
2. =TODAY() will give the current day only
3. If the current date is Thursday June 15, 2000, then the following formulas will give the shown
4. To calculate the last day of a month, use a formula that calculates the first day of the next month, then
subtracts one. For Example:
Cell A1 contains the year (say 2004)
Cell A2 contains the month (say 2)
=DATE(A1,A2+1,1)-1 will give the date Feb 29, 2004
LS1/4/2011 Page 4 of 3
Preparation date: January 15, 2010
Prepared by: Richard Farrar
Purpose: To prepare journal entries and the related Accrued Payroll T-account for the gross payroll for
Preparation date: January 15, 2010
Prepared by: Richard Farrar
Purpose: To prepare journal entries and the related Accrued Payroll T-account for the gross payroll for the m
Instructions to User:
Enter all amounts in $ (without $ signs or commas) unless otherwise indicated
Enter all % amounts as a decimal (e.g. 10.0% entered as 0.10)
Enter the report date for the output in the format MONTH dd, yyyy in cell provided ->.
Enter the last day of the first payweek (Saturday) in the month in the format
MONTH dd, yyyy in the cell provided ->
Enter the Direct Labour and Indirect Labour amounts for the month in the appropriate
cells in the area below shaded green
Enter the amounts for employer share of benefits and payroll taxes and deductions
from gross pay, stated as a % of gross pay, in the appropriate cells shaded blue below.
the gross payroll for the month ended 30-Jun-10
ABSOLUTE CELL REFERENCE
Absolute cell referencing is used when cell formulas are to be copied to other cells. It can best be
explained by examples.
CASE 1: COPYING ACROSS
Consider the following input area:
A B C
COGS % 60%
21 ³ COGS % ³ Jan-9760%³ ³
Sales $ 10,000 $ 15,000
42 ³COGS³ Jan-91 ³Feb-91 ³
The COGS formulas are to be entered in B4 & C4. The formula for January in B4 would be as
To get the COGS for the month of February, the formula in cell B4 (ie January COGS) would be
copied to cell C4. The problem is that when this is done the resulting formula in cell C4 will be:
The reference to C3 is correct (Feb Sales) but the reference to C1 is incorrect (it should be to B1). In
this case we always want to refer back to column B for the COGS %. This can be done by placing
a $ in front of the B in the formula in cell B4 before copying it to other cells. The $ in front of a letter
will cause that letter to remain the same in all target cells when copying. Thus the formula in cell B4
should be: =$B1*B3
When this formula is now copied to C4 the result will be =$B1*C3, which is now correct.
CASE 2: COPYING DOWN
Consider the following area:
A B C
1 Mark %
2 Student 1 15
3 Student 2 16
4 Student 3 12
6 Maximum 20
To fill in the % area the following formula would be entered into cell C2:
When this formula is copied down to cell C3 the result in C3 will be:
The reference to B3 is correct but the reference to B7 is incorrect (it should be to B6). In this case we
always want to refer back to row 6 for the total mark. This can be done by placing a $ in front of
the 6 in the formula in cell C2 before copying it to other cells. Thus the formula in cell C2 should be:
When this formula is now copied to C3 the result will be =B4/B$6, which is now correct.
To make a cell reference in a formula always point to a particular cell (example C3), then simply put a
$ in front of the letter and the number of the cell, ie. =$C$3.
A reference to a named range is always absolute when copying.
When entering a formula, the F4 function key will automatically put the $ in for you.
INTERMEDIATE EXCEL - EEE2000
Gives the sum of the numeric values in the range specified.
Rounds the number x to n decimal places. x can be any number or cell formula.
Gives the minimum value in the range stated. Range is in the format a1..a10
Same as above except gives the maximum.
Gives the average of the numeric values in the range specified.
The condition is a logical formula such as +C10>0 or +C11*M21=B6 or +A15="yes".
Value-if-true and value-if-false are the desired results if the condition is true or false respectively.
The results can be numbers, formulas with cell references or strings. If they are strings, then
they must be enclosed in quotation marks (""). For example, if the result is to display the word
yes if the condition is true, then value-if-true in the formula is "yes".
A compound condition can be set by using AND(condition1,condition2,..) or OR(condition1,
condition2,…). For example assume cell C2 contains the age of an individual and cell B2
contains the sex of that individual. If you want another cell to display the words "High Risk" for
all men 60 or older, and "Low Risk" otherwise then the formula would be:
=IF(AND(C2>=60,B2="M"),"High Risk","Low Risk")
Similarly if you wanted High Risk displayed for persons that are either male or 60 or older then
the formula would be:
=IF(OR(C2>=60,B2="M"),"High Risk","Low Risk")
It is possible to have an if within an if. This is referred to as a nested if. For example the
formula with AND above can also be:
=IF(C2>=60,IF(B2="M","High Risk","Low Risk"),"Low Risk")