General Workpaper Functionality and Tips for Entering Data
These tips and workpaper functionality descriptions are generally applicable to PPC’s Workpapers for Nonprofit Organizations, enabling you to more efficiently and accurately navigate through the workbooks/worksheets. However, review the comments (see description below) within individual workbooks for differences from these general data input tips/functionality characteristics.
Excel Comments. The workbooks in PPC’s Workpapers for Nonprofit Organizations rely heavily on Excel comments (comments) that appear as little red triangles (flags) in the upper right corner of an individual cell. The first worksheet in each workbook has a comment in cell A2. This comment provides the purpose of the workbook and/or worksheet and general data entry instructions and functionality descriptions applicable to the workbook. Comments are also commonly located in cells having column/row captions. These comments generally provide more specific descriptions of the purpose, worksheet functionality, and/or data input guidance for that particular row, column, area, or cell within the worksheet. Please review these comments for any data input and workpaper functionality that may differ from the general guidelines provided in this section.
White Cells v. Shaded Cells. White (or clear) cells surrounded with a thin gray border are generally the only valid cells for data input. Cells shaded with a pale yellow, medium grey, black, or checkered pattern should not include any input data. The gray, black, and checkered cells generally simplify the data input process by indicating cells that are within a data input area but do not affect the workbook’s calculations. The cells shaded in pale yellow are reserved for the worksheet formulas and column/row headings. Note that many column/row headings have imbedded formulas that should not be overwritten with data entry.
Note: In some spreadsheets, some white cells surrounding the data input areas have formulas that provide notes or warnings if certain data is input. Please use caution when entering data in cells that are not designated as data input cells.
Copying Input Data. It is common to copy data inputs from one cell to others that have the same input (e.g. monthly lease payments over a 5-year period). However, in many instances cells in different columns of the same row (or different rows of the same column) contain different conditional formatting and/or data validation rules. (See below). Use caution when copying data from one cell to another. Unless Paste Special | Values is used, the conditional formatting/data validation will become corrupt and not function accurately, compromising the accuracy of the workbook.
Conditional Formatting and Data Validation. Many workbooks utilize extensive conditional formatting and/or data validation. Data validation is generally applied through drop-down lists that restrict the acceptable data inputs (e.g. “Yes” or “No”). Data validation is also used to restrict the value of an input (e.g. less than or equal to zero). In this instance, if data is entered that does not agree with the restricted range, a pop-up box indicates the invalid data entry and provides the user with options to correct the entry.
Conditional formatting is generally used to assist in proper data entry. Most often, a red border around an individual cell or cells indicates that an input row was skipped or data is missing from the outlined cell within the row. However, for certain workbooks that are used to document differences (variances) greater than a certain percentage and/or amount, a blue border indicates missing data and a red border indicates the cell’s value resulted in a variance greater than the criteria set.
Inserting and Deleting Line Items. The workbooks do not have a standard number of data input rows. Some workbooks may contain many more rows than is necessary, but do not delete any input rows unless one of the comments (see above) instructs that excess rows may be deleted. If the worksheet allows deletion of rows, select the excess rows and then select the menu path Home | Delete | Delete Sheet Rows. (For earlier versions of Excel, select the menu path Edit | Delete | Entire Row | OK.) Otherwise, the best alternative would be to “hide” the excess rows by selecting the excess rows and then selecting the menu path Home | Format | Hide & Unhide | Hide Rows (or for earlier versions of Excel, select the menu path Format | Row | Hide). Note that some worksheets include a “button” that will automatically hide/show unused rows.
If a workbook does not contain enough input rows, it is generally acceptable to add input rows. Once again, refer to the workbook’s comments for any instructions concerning adding rows. When adding rows, select an interior input row and select the menu path Home | Insert | Insert Sheet Rows. (For earlier versions of Excel, select the menu path use the menu path Insert | Rows.) After inserting the row(s), make sure any shaded yellow cells have formulas in them. If not, copy any formulas from the respective column in a row immediately above or below the added row. (Note that some worksheets include a “button” that will automatically add a new row with any necessary formulas.)
Note: When adding or deleting rows, do not select the first or last row of any section. This is to ensure that the formulas and most of the formatting remain intact. Also, when deciding whether to delete or add rows, make sure the other worksheets in the same workbook do not have the same number of rows with formulas pulling information from the rows on the worksheet in question. If the workbook does have multiple worksheets with the same number of input rows, the same number of rows should be added to/deleted from the identical area of the worksheet. (Some worksheets include a “button” that will automatically add a new row or hide unused rows for multiple worksheets.)
Text Box Functionality. Certain text boxes are designed for easier text input than traditional Excel worksheet cells. Generally, when a text box is entered, the text box borders generally will turn green and will return to the normal grey color when exiting the text box. (However, some worksheets do not have this functionality). The text boxes have the capability to expand to accommodate as much input as necessary, and will shrink back to the default size as text is removed. The text boxes will "word wrap", but also accommodate a "hard return" by pressing the "Enter" key. However, formatting of text (such as bold, underline, italics, etc.) and "spellcheck" is not available and number/percentage formats will have to be input manually (as in MS Word). Also, icon or mouse commands for cut/paste functionality are unavailable, but the equivalent keyboard commands [ctrl + c] and [ctrl + v] will function. To exit a text box, select the "Tab" key or click the mouse outside of the text box.
Note: Do not save or close this workbook when “inside” a text box. This could affect Excel’s performance. If this occurs, “close out” all active Excel files, “close out” of Excel, and then restart the Excel application.
Rollforward and Clear Workbook Functionality. One of the most useful features of PPC’s Workpapers for Nonprofit Organizations is to rollforward data to the following year. Many workbooks include this functionality by simply selecting the gray “rollforward to next year” button near the top of the initial worksheet. A pop-up box will appear to verify the intent to rollforward the data.
Most workbooks include a gray “Clear worksheet(s)” button near the top of the initial worksheet that will clear all input data cells for the entire workbook. The workbook should be saved before using the clear functionality. Upon selecting the “clear button”, a pop-up box will appear to verify the intent to clear all inputs.
Note: If the Shared Data functionality is utilized, the rollforward and clear workbook process breaks the link with the shared data. Thus, the client name and balance sheet date will be removed and will either have to be manually input or the Shared Data link will have to be reestablished. The shared data link can be reestablished by using the menu path PPC | Share Data and inputting the client name and the engagement. (Keep in mind, that the information entered will be shared with the other workbooks in the folder.)