At times it may be useful to see where the components of a formula have originated from. Or it may be useful to see where a cell is referenced in other parts of the worksheet. In Excel 2010, this is known as tracing precedents (where the components of a formula have originated from) and dependents (to see where else a cell is referenced). These are both auditing tools available in Excel. Trace precedents Open the worksheet that you would like to audit and click in the cell that contains the formula you would like to audit. On the Formulas tab, click Trace Precedents in the Formula Auditing group. The blue arrows show where the … [Read more...]

## How to Get Unique Values Using a Formula in Excel

To get a list of unique values you can use the AutoFilter or you can try using a formula approach. The COUNTIF function only has two arguments, Range and Criteria. The range would be your data range. In the below example it’s A2:A9. The criteria would be the value you want to count in the range. So if we wanted to count the number of times the number 8 appeared in the data range below, the formula would be =COUNTIF(A2:A:9, 8). This would return 3 because the value 8 occurs three times in the range specified. Instead of setting the criteria to 8 we could point it to a cell containing that value. So the formula would then be … [Read more...]

## Cheat Sheet: Must-Know Excel Tips and Tricks

While Microsoft Excel may not exactly have a reputation as the sexiest computer application in the PC world, it is a useful one. And since chances are good you’ll need to use it over the course of your scholarly, business or personal life, having a few tips and tricks up your sleeve is a good plan. Jump to a Cell To the left of the formula bar, you’ll find a cell listing that changes to show you exactly which row and cell you’re working in. By clicking on this area, you can type in a specific column number and row name to jump directly to that cell. Fix Hinky Numbers Occasionally, Excel will erroneously store certain … [Read more...]

## How To Display Formulas in an Excel Cell

By default, your formulas in Excel 2010 are hidden from view in the worksheet and only displayed in the Formula Bar. However, at times you may want to show the formulas in your worksheet, rather than the results of the formula. Open the workbook that contains the formulas you would like to display. On the Formulas tab, click Show Formulas in the Formula Auditing group to display the formulas in your worksheet. Instead of the results or calculations, the actual formulas, functions and arguments are displayed. This may make it easier for you to identify any errors or inconsistencies in your formulas. To switch the view back to … [Read more...]