Formula Auditing mode di menu apa?

Categories: Basic Excel Tags: Excel Formula Auditing, Trace Precedents

Table of Contents Show

What is Formula Auditing?
#1. Trace Precedents
#2. Trace Dependents Function
#3. Remove Arrows
#4. Showing Formulas
#5. Evaluating a Formula
Error Checking
Features of Formula Auditing
Things to Remember
Formula auditing mode dijalankan dari menu apa?
Apa yang dimaksud dengan Formula auditing?
Page setup dijalankan dari menu apa?
Apa yang dimaksud dengan sel dalam program Microsoft Excel?

The formula function in Excel is one of the most useful things any business owner or employee can use, especially when large volumes of data need to be evaluated. Sometimes you are given completed worksheets that you need to do extra analysis on. It is sometimes difficult to see where all of the functions in the file are and where they are coming from. When this happens, knowing how to use the Formula Audit toolbar is essential.

Formula Audits can be done several different ways. If you click on the Excel ribbon tab called Formulas, you can see the section labeled Formula Auditing. You may have to customize the ribbon to see this option. Below are the various ways that you can audit a formula.

Trace Precedents

Trace Precedents shows you all of the cells used to calculate a certain cell’s value. When active, you see a blue box around the cells and an arrow showing the direction of information flow.

To use this function, click on the cell that has the formula in it and hit the Trace Precedents button. All of the cells used in the formula of that cell will be outlined in blue. You can use the Remove Arrows button in this same section to get rid of the blue arrows.

If there are no trace precedents, then you will receive an error message from Excel.

Using Trace Dependents Function

This function allows you to see all of the formulas that particular cell is used in. For example, if you have a value that is used in multiple formulas in your spreadsheet, you can click on that cell, hit the Trace Dependents button, and all of the formula cells where that value is used will show up in blue with arrows pointing from that cell to the formulas that it is used in. You can use the Remove Arrows button in this same section to get rid of the blue arrows.

Using Show Formulas Function

This function is very useful when you want to see which cells are formula driven, as well as when you want to do a thorough review of all of your formulas at a glance. Instead of selecting each individual cell and look at the function bar, all you have to do is click on the Show Formulas button in the Formula Auditing section and all of the cell formulas appear instead of their values.

Error Checking

Error checking is useful if you have an extremely large spreadsheet with multiple tabs and you are not sure if all of the formulas are pulling through to the end correctly. By clicking on the Error Checking button, you can trigger Excel to look at all formulas and show any errors that need to be debugged.

Formula auditing is extremely useful if you are a given a spreadsheet with large amounts of important data. It could take a long time to review each individual formula separately, so using this function in Excel is a must.

PRYOR+ 7-DAYS OF FREE TRAINING

Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.

The Formula Auditing is one of the most useful tools in Excel any entrepreneur or employee can use, especially while dealing with large volumes of data. Sometimes you are only given completed Excel worksheets on which you need to put extra analysis. Many times, it becomes difficult to notice all of the functions that are present in a file. When you land up with this situation, always use the Excel inbuilt Formula Audit toolbar.

The tutorial we will cover the definition of Formula Auditing, different methods for formula auditing and troubleshooting formulas, step-by-step analysis of each method.

What is Formula Auditing?

"Formula auditing is an essential tool in Excel that enables users to show the relationship between formulas and cells."

Excel Formula Auditing toolbar helps the user to quickly and easily find:

the cells contribute to calculating a formula present in the active cell.
the formulas that refer to the active cell.

The output of Formula Auditing is presented graphically by arrow lines, thereby making the entire formula visualization effortless. It allows the user to show all the formulas in the active worksheet with a single command. In case your formulas are further referring to cells present in a different workbook, it also opens that workbook.

Microsoft Excel provides several different methods to audit a Formula. If you click on the Formulas ribbon tab of Excel, you will find the Formula Auditing section. However, many users might also need to customize the ribbon to see this option. Following are the different ways using which you can audit a formula:

Trace Precedents
Trace Dependents
Remove Arrows
Show Formulas
Error Checking

All these methods help you with formula auditing and troubleshooting formulas. Moving ahead in this tutorial, we will learn in detail about each method.

#1. Trace Precedents

Trace Precedents displays tracer arrows from the cells showing the direction of information flow. You see a blue box around the cells when this method is active. However, one can press this button multiple times to catch additional levels.

Let's understand the method using an example:

As shown in the above table, we are given formula in the D2 cell for calculating the GST amount for your Service amount.

Select the required cell and press the F2 button to enable the edit mode. You will notice that the precedents cells got bordered with various colors and written in the same color and cell reference.

As a result, you will notice that cell A2 is highlighted with blue color, and the border color is also filled using the same color.

Though it is simple and easy, we have a more suitable method to check precedents for the formula cell.

Following are the steps to use Trace Precedents in your Excel worksheet:

Select the formula cell.
Go Formula option in Excel ribbon tab. In the "Formula Auditing" group click on the Trace Precedents option.

NOTE: If there occur no trace precedents in the cell, Excel will throw an error message.

As a result, you will see an arrow as shown below.

In the above image, the precedent cells are shown with blue dots.

#2. Trace Dependents Function

This function lets the user see all the formulas in which a particular cell is used. For instance, let's say you have a value used in multiple formulas in your Excel worksheet. In that case, you select the formula cell, click on the Trace Dependents option, and all formula cells where that same value is used will be displayed in blue with colored arrows pointing from that cell to the formulas wherever it is used.

In the example below, we have calculated the 10% interest for the base amount and the GST amount.

Here we have used the formula =A4*E2 for the first cell and =C4*E2 for the second cell. Therefore, we can conclude that E2 is a dependent cell for the cells A4 and C4. Following are the steps to use Trace Dependents function for the cell E2 in your Excel worksheet:

Select the dependent cell E2.
Go Formula option in Excel ribbon tab. In the "Formula Auditing" group click on the Trace Dependents option.
As a result, you will see a two-headed arrow appearing from E2 to A4 and E2 to C4, showing A4 and C4 are dependent on E2.

Later, if you want to get rid of these arrows, you can use the Remove Arrows option available in the same section.

#3. Remove Arrows

In the above two options, we covered Trace Precedent and Trace Dependent. Using both methods, we have displayed tracer arrows from the cells showing the direction of information flow. But what if we want to remove the arrows afterward?

Don't worry; Excel has provided an inbuilt option in the Formula Auditing section to remove the arrow quickly. Following are the steps to remove the arrows from the cell E2 in your Excel worksheet:

Go to the Formula tab in Excel ribbon tab. In the "Formula Auditing" group click on the Remove Arrow option.
As a result, all the arrows from the active Excel worksheet will disappear.

Note - If you want to trace dependents of a cell, make sure a formula should reference the cell in another cell; else, it will throw an error message.

#4. Showing Formulas

When you are working on an Excel worksheet that carries multiple formulas, you often need to check the formulas to ensure things are working smoothly. Excel has provided the Show Formula option in the formula auditing section to quickly display all the available formulas in your active worksheet. Sounds great, isn't it!

The worksheet below contains the GST report and 10% interest figure for our base amount.

Here we have entered the formula in three of the cells. Following are the steps to display all the cells containing formulas in your active Excel worksheet:

Go to the Formula tab in Excel ribbon tab. In the "Formula Auditing" group click on the Show Formulas option.
All the Formulas in the active worksheet will be displayed, so that at once you will get to know which cells contain formulas and what the formulas are.
Refer to the below image to see all the formulas.

#5. Evaluating a Formula

Sometimes you are given a pre-made worksheet wherein complex formulas are used. To uncover the step-by-step working of a complex formula, you can utilize the Excel Evaluate Formula command.

Let's suppose we have incorporated the NPV formula in cell E4. To Evaluate the formula, follow the below-given steps:

Error Checking

Errors are common when we deal with functions and formulas. Therefore, evaluating a formula is essential as it checks the specified formula or function error. It is a good practice to check all the errors in your worksheet once all the calculations are done.

Let's understand the same using a simple calculation.

As you can see the above calculation has computed an error #NAME? in the cell E4. Following are the steps to check errors that occurs in your Excel worksheet using the Error checking method:

Click on the error cell i.e., E4.
Go to the Formula tab in Excel ribbon tab. In the "Formula Auditing" group click on the arrow next to Error Checking option.
In the drop-down list, you will notice three options, i.e., Error-checking, Trace errors, and Circular References. In our case, as you see, the third option is deactivated, representing that the Excel workbook contains no circular references.
Select the Trace Error option from the drop-down menu.
The cells required to compute the active cell are represented by blue arrows.
Click on Remove Arrows. Again, click on the drop-down arrow next to Error Checking option. From the drop-down list select the Error Checking option.
The Error Checking window will be displayed.

Observe the following points of the Error Checking window:?

All the error information will be displayed on the left side of the window pane. In our case, it has indicated that the error has occurred because our formula contains some unrecognized text.
The Help on this error option will direct you to the Microsoft page displaying all the steps to correct the error.
As the name suggests, the Show Calculation Steps option displays the Evaluate Formula dialog box.
If you click on the Ignore Error option, Excel will close the Error Checking window at once and if you click on Error Checking option again, Excel will ignore this error.
The Edit in Formula Bar option takes you to the cell formula in the formula bar, so you can further edit the formula in the cell.

Features of Formula Auditing

Below given are significant features of Formula Auditing:

Formula Auditing allows easy Auditing of formula dependents and precedents, including object dependencies (charts, pivot tables, pivot charts, form controls, formula validation, etc)
Formula Auditing quickly helps the user to find if them occurs any circular references in their Excel worksheet.
It quickly helps to trace the errors.
It lessens the time in your workbook calculation and helps to quickly to find errors in your formulas.
Formula Auditing check columns for formula inconsistencies.
Formula Auditing allows to check all the errors in your worksheet once all the calculations are done

Things to Remember

You can also show the dates in the number format if we click on the 'Show Formulas' option.
If you want to evaluate an Excel formula, you can take advantage of the shortcut key "F9".

Formula auditing mode dapat ditemukan pada menu (B) Insert.

Apa yang dimaksud dengan Formula auditing?

Formula auditing digunakan untuk mencari informasi bagaimana sebuah formula/rumus excel dihasilkan. Informasi yang bisa ditelusuri adalah data apa saja yang mempengaruhi sebuah formula (trace precedants) atau formula mana saja yang dipengaruhi oleh sebuah data (trace dependents).

Perintah page setup di Microsoft Word 2003 dapat dijalankan dari menu "File". Perintah page Setup digunakan untuk mengatur halaman.

Apa yang dimaksud dengan sel dalam program Microsoft Excel?

Sebuah sel merupakan persimpangan dari baris dan kolom. Dengan kata lain, itu adalah tempat di mana baris dan kolom bertemu. Kolom diidentifikasi oleh huruf (A, B, C), sedangkan baris diidentifikasi dengan angka (1, 2, 3). Setiap sel memiliki nama sendiri, atau alamat sel, berdasarkan kolom dan baris.