Every month, I update the Trial Balance sheet from our accounting package by overwriting the existing data with that of the new month. The intention is to produce the Balance Sheet and Income Statement for the new month, and use said sheets in the management pack.
A problem arises if our bookkeeper adds a new general ledger account during the month. The new account will be included in the new trial balance data, but that cell (account) will not be referenced from the Balance Sheet or Income Statement, which will result in the Balance Sheet being out of balance. I will then have to find the new account in the trial balance sheet and insert it in either the Balance Sheet or the Income Statement, with the necessary vlookup. No accounts are deleted in the accounts package, only new ones added.
Usually I can pick up if a new account is added quite quickly, but this month I cannot. There seems to be a new account (/accounts) added that is (/are) not referenced in either the Balance Sheet or Income Statement, but I cannot trace such account/s. I have tried using the Precedent and Dependent features in Excel (2013), without success.
Can anyone assist me in resolving the problem? Our bookkeeper is not available to ask, and I need to get the management account pack out to our directors.
Many thanks
Excel offers various spreadsheet auditing tools including that of the spreadsheet auditing toolbar. The main function is to help you identify cells worked with and for which purposes as this will help you track errors and find the cause of the errors. The spreadsheet auditing toolbar is thus most helpful to identify causes of errors and to then rectify them. It can be viewed by following a few simple steps:
- ·On the Menu bar select Formula and then Formula Auditing .
The Trace Precedents button on the toolbar is used for identification of cells being used for a formula. When you click on a cell and then on the Trace Precedents option a blue line will appear that will indicate what cells are used in the formula in the highlighted cell through the use of line arrows. If you want to remove the arrows simply select Remove Precedent Arrows or make use of the Remove All Arrows option.
Trace Precedents Excel Shortcut
We’d like to confirm if the Trace Precedents and Trace Dependents options are grayed out. If so, click File Options Advanced, under the sections Display options for this workbook, select All under For objects, show to make these functions activated. In addition, you can repair the Office Excel 2013 application. On the Formulas tab of the ribbon, Excel has some tools to show these relationships with arrows. With C11 selected, the Trace Precedents button shows that C8, C9, and C11 all contribute directly to the result. Use remove arrows to clear arrows off the screen. Dependents are formula cells that depend on other cells. These two buttons allow you to trace (and turn off trace) precedents in a formula. A precedent cell is one that is referred to by a formula in another cell. B9 through F9 are precedents, because they are referred to in the formula in cell G9. As you can see, the trace precedents button shows you the relationship between the cells. Trace precedents and dependents arrows are useful for reasonably simple formulas, but they can quickly turn into pick-up sticks in more complex scenarios: Another easy way to highlight cells referenced in a formula is to press F2 to edit the cell containing the formula in question.
What if the cell selected looks at a worksheet other than the one currently on?
Trace Precedents: When you click this button, Excel draws arrows to the cells (the so-called direct precedents) that are referred to in the formula inside the selected cell.
Trace Precedents In Excel 2013 Pdf
A dotted line appears and you can be taken to the relevant worksheet by double clicking on the dotted line. The GO TO option dialogue box opens and you can select the worksheet where you want to go to. Once selected, it will take you to the relevant spreadsheet.
How To Trace Precedents Excel
You can follow the steps through ourTips & Tricks Video on using Spreadsheet Auditing. The short video also addresses other buttons on the auditing toolbar such as the Trace Dependents tool which is useful for identifying patterns in a worksheet.