Best Microsoft Excel Bloggers

Friday, October 28, 2011

Moving Individual Fields (Controls & Labels) in Access 2007

Moving Individual Fields in an Access 2007 Form

Moving fields in Access used to be pretty simple - NOT ANYMORE. Microsoft has added a Control Layout feature so if you want to move controls and /or labels separately then you need to become friends with the Remove icon located on the contextual Arrange tab in the Control Layout group!  The default is that all the controls and labels move together as a unit.
Oh and make sure you are in Design View- not the new Layout View they added.

In Access 2007, if you want to move an individual field it requires an additional step as the default is for all of the fields to move together.

  • In Design view, select the control and/or label you wish to move
  • Click on the Remove icon - found on the contextual Arrange tab on the Control Layout group
  • Now you can move the individual control and/or label.

Wednesday, October 19, 2011

Spreadsheet Controls under Sarbanes-Oxley Section 404

This is based on an EBook Joe Helstrom, CPA wrote for entitled Spreadsheet Controls Under Sarbanes-Oxley Section 404

Spreadsheet Controls 

Spreadsheets have become pervasive in most companies and have many uses. Those that are used in the financial reporting process are of most concern to the assessment of the effectiveness of internal controls over financial reporting mandated by Sarbanes-Oxley. Several steps are recommended to accomplish the needed assessment related to spreadsheets. The first would be to get a handle on the population of spreadsheets that are used in the company. Secondly, determine whether the spreadsheet is used in the financial reporting process. Next, identify risk factors of the spreadsheet and grade the overall risk. Next, identify any compensating controls that reduce or mitigate the identified risks. Lastly, determine what remediation steps are necessary, if any, for the identified spreadsheets.

It should be noted that a high risk spreadsheet used in financial reporting, even with compensating controls, may not be able to achieve an adequate level of control in a spreadsheet environment. It may be necessary to migrate the process to an application where the information technology controls are developed and maintained by the information technology function.

Inventory all spreadsheets
The beginning of the “top down” approach would be to identify all spreadsheets used by the organization in the financial reporting process. This would include financial reporting, plant accounting, treasury, tax and operations. This can be done at the department level by asking each department head or supervisor to create a list of all spreadsheets used with the following information:

Spreadsheet name
• Location of the spreadsheet file
• Department using the spreadsheet
• Description of spreadsheet purpose
• Spreadsheet users that have access

In addition to the above, the IT staff can be enlisted to query the company’s networks for spreadsheet files. This will help ensure that the inventory is complete.

Determine how current spreadsheets are being used

Once the spreadsheet inventory has been completed, an assessment of spreadsheet use must be performed. The first step is to segregate the spreadsheets into categories. These categories may include financial, operational and analytical.


The spreadsheets that fall into the “financial” category will carry the most risk potential. These will include spreadsheets that:

Validate account balances

• Support transactions or journal entries

• Compute financial statement disclosures

• Perform financial reporting controls

Operational and analytical spreadsheets may also be important depending on the organization. However, these generally are used for operational decisions rather than in the financial reporting process.

Determine the risk factors of the spreadsheet

The financial spreadsheets (as well as any others that are significant to the financial reporting process), must be assessed for risk. Risk factors will include:

The use of the spreadsheet and the use of the spreadsheet output

• Materiality of the affected account balance or disclosure

• Potential errors in downloaded data such as an incomplete download or a download of incorrect  data.

• Whether the spreadsheet uses complex calculations, formulas or macros.

• Number of individuals using the spreadsheet

• Size of the spreadsheet

• How well the spreadsheet is documented

These risk factors must be assessed along with the use of the spreadsheet and a risk rating should be assigned. As an example, a spreadsheet that is used for financial reporting disclosure, uses downloaded data, contains complex calculations and is used by a number of people would have a high risk rating. A similar spreadsheet used solely for analytical purposes would likely carry a moderate to low risk. Additionally, a spreadsheet that is used for a key financial control would likely carry a higher risk rating than one that is used to provide a list of documents. This is a subjective determination. It must be well documented so that a reviewer can assess the conclusions drawn by the company.


Evaluate compensating controls for risk factors

Certain organizations have already put controls in place to reduce the risk of material financial reporting error related to spreadsheets. These controls must be evaluated in light of the risk factors noted above and, once again, a determination must be made as to the effectiveness of the compensating controls. Compensating controls may include:

Downloaded data has control totals that are compared the source data and validated by the user.

• If applicable, control totals or logic controls are used to validate user input.

• A logic inspection of the spreadsheet by an independent party is performed and documented prior to spreadsheet use.

• Spreadsheets are protected against unauthorized changes.

• Spreadsheet versions are used and, before a new version is utilized, it is tested and approved.

• Access to the spreadsheet is limited to authorized users via network access limitations and/or use of spreadsheet passwords.

• Spreadsheet documentation is adequate and up to date.

Once again, a determination must be made as to the adequacy of compensating controls. This can be a grade of either “Good”, “Moderate” or “Ineffective”. It is also a subjective determination.


Documentation of procedures

The spreadsheet inventory, description of use, risks and compensating controls should be summarized in a spreadsheet or workpaper. The documentation should also include your risk and control grades as well as a testing strategy for those spreadsheets that are deemed to have adequate compensating controls. Keep in mind that once a control has been identified, it still must be tested.



For those spreadsheets whose compensating controls are moderate to ineffective, there should be changes made to enhance the compensating controls. Excel supports many compensating controls.


Keep in mind that a spreadsheet may not be appropriate for high risk accounts. In cases where the risk is high and the balance is material, migration to an application supported by information technology staff and control environment may be warranted.

Friday, October 14, 2011

Converting a Date to a Calendar Quarter

This is a guest post from Joe Helstrom, CPA.
I thought you would be interested in this as Excel is not that user-friendly when it comes to dates.

How to Convert a Date to a Calendar Quarter

This tip involves some math. We're going to use just the numeric month portion of the date and divide by three. So, if the month is 3 (March), 3 divided by 3 = 1 (First quarter). If the month is 6 (June), 6 divided by 3 = 2 (Second quarter). If the month is 9 (September), 9 divided by 3 = 3 (Third quarter).

What happens when the math is a little messier? If the month is 2 (February), 2 divided by 3 = .66666. We know that this should be in the 1st quarter. If the month is May, 5 divided by 3 = 1.6666 and we know that this should be in the second quarter. The Roundup function needs to be used.

The Excel Roundup function "rounds up" a number. The syntax for Roundup is =Roundup(cell reference, Number of digits). If the number of digits argument is zero, it rounds up to the nearest integer.

So, if we have a date of 2/12/2011 in cell A3, we first need to know the month. The Month function will provide that. In cell B3, type =Month(A3). The result is 2.

We'll combine the Month function into the Roundup function. Using the same cell, B3, type =Roundup(Month(A3)/3,0).

This function takes the numeric month, divides it by 3 and rounds it up to the nearest integer. In this case 2 divided by 3 = .66666 which is rounded up to 1 (First quarter).

Try it on other dates. If, in cell A4, the date is 5/17/2011, in cell B4 type, =Roundup(Month(A4/3,0). Your answer is 2 (Second quarter).

If you have a series of dates that you want to convert to quarters, just copy the formula. You have a quick, easy way to convert dates to calendar quarters.
For courses that address these and other Excel tips and provide CPE to CPAs, visit or for non-CPE Excel courses.

Tuesday, October 11, 2011

Sorting Pivot Data

Sorting Pivot Data

Pivot tables are very flexible and it is easy to sort data.


In this example, I want to sort the Ship Via carriers in descending order.

1. Click and select the Ship Via labels in the pivot table (cells C5.H5)

2. Click on the Data Ribbon and select the descending Sort icon

You should see that the Ship Via carriers are now sorted with UPS first (see below) and note that there is a small arrow showing a descending sort order beside Ship Via in cell C4.

An alternative way to sort your data is to click the drop-down arrow beside the field in the pivot table

1. Click on the drop-down arrow beside Ship Via –(the Column Label at C4)

2. Click Sort A to Z and you will see the Products reorder themselves again.

3. Click the Undo icon so that the data is sorted in descending order again.

You can also use a custom sort that you have created however custom sorts are lost when you refresh a pivot table.

Ms. Excel- Resident Excel Geek