Welcome to the sixth post of this series – Select row and column differences. Hope you are enjoying this series. Click here to read to navigate to go to special.
I’m sure this is one technique that will be useful for auditors, reviewers, quality checkers (all these are one job but different tags called in different companies. There might be some more which I’m not aware!). In the companies, I worked before there used to be one preparer and one reviewer (for a four-eye check or a quick sense check before the report is sent to the stakeholders. Reviewers job is to make sure the values are correct as per the inputs received and all formulas are correct and end result makes sense. This is one technique I adopted when I used to review the data.
Row differences:
Row differences select cells that are different to active cell from all the cells you selected in a row. By different, I mean cell values if they are constants or the formula differences if the cells contain formulae. When one selects a range of cells from a row one need to be careful in selecting the active cell. By default, excel selects the first cell as active cell. To change the active cell in the selection press Enter or Tab. Ensure you have the correct cell activated after which you wanted to find the differences in a row.
If more than one row is selected, the comparison is done for each individual row of that selection, and the cell that is used in the comparison for each additional row is located in the same column as the active cell.
Keyboard Shortcut: Press F5, then Alt+S+W
Below is a random data I picked to illustrate the same.
This is a gross profit calculation for four fruits sold in a market per week (I’ve shown data for two weeks). Gross profit is Sales minus COGS (Cost of goods sold). In this example, for week 1, gross profit for Apple should be C25-C26 (=50). Think as a reviewer! Check the formula in cell C27. It should be C25-C26 (Sales – COGS). If it is fine, select row 27 in this example from columns C: F. Press F5, then Alt+S+W. You will select the differences in row due to formula. The selected cells are wrongly calculated cells. (Now it’s your duty to mark your preparer an error! Well, if you do not want to screw him just let him know about the error. Am just kidding!!)
Check below the table and formulas for gross profit in table. If you rightly audit the formulae, you will observe gross profit for Grapes and Orange is not calculated using sales and COGS of Banana and Orange respectively (E27 shows D25 – D26 where it has to be E25 – E26, and F27 shows E25-E26 where it has to be F25 – F26). Same is the case with gross profit for week 2 for Banana (Cell D30). Formula was carried forwarded from apple gross profit calculation.
R-C diffs
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 23 | ||||||
| 24 | Apple | Banana | Grapes | Orange | ||
| 25 | Week 1 | Sales | 100 | 200 | 400 | 200 |
| 26 | COGS | 50 | 100 | 200 | 100 | |
| 27 | Gross Profit | 50 | 100 | 100 | 200 | |
| 28 | Week 2 | Sales | 80 | 120 | 1000 | 120 |
| 29 | COGS | 40 | 70 | 600 | 70 | |
| 30 | Gross Profit | 40 | 40 | 400 | 50 | |
| 31 |
Worksheet Formulas
|
Copy the above data and formulas to your excel and play around.
Select range C27:F27 (Active cell is C27), Go to special and select row differences. You will see E27 and F27 are selected as they are different from the row (Formulas pointing to different column unlike the active cell in your selection). Below is a visual selecting the multiple rows and finding the differences.
