Go To Special series. Select Row and Column Differences

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

Excel 2007

Worksheet Formulas Cell Formula C27 =C25-C26 D27 =D25-D26 E27 =D25-D26 F27 =E25-E26 C30 =C28-C29 D30 =C28-C29 E30 =E28-E29 F30 =F28-F29

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.

Apologies: Above visual has crossed the blog area for posting. But I wanted to show this somehow and hence this visual.

This type of data (Very small, isn’t it?) is unlikely in our day-to-day activities. We work on data spreading to large number of rows and columns. This is when select row differences option comes handy. Simply, select the differences in rows colour them, review and correct.

Column differences:

Column differences select cells that are different to active cell from all the cells you selected in a column. In row differences, your selection was row and here your selection is column. Nothing else differs. But, I just want to reiterate on selecting. Select a column. Activate the cell to which you want to find the differences in that column. To change the active cell press Enter or Tab. We can select differences in constants as well as formulae.

Below is an example for Column differences. I have just little tweaked the above example to fit the column differences example!!

R-C diffs

	C	D	E	F	G	H	I
1		Week1			Week2
2	Product	Sales	COGS	Gross Profit	Sales	COGS	Gross Profit
3	Apple	100	50	50	80	40	40
4	Banana	200	100	100	120	70	40
5	Grapes	400	200	200	1000	600	400
6	Lemon	200	100	100	120	70	50
7	Orange	300	150	100	250	150	100
8	Pine Apple	300	150	150	250	150	100
9	Sapota	100	50	150	80	40	40
10	Strawberry	200	100	50	120	70	50

Excel 2007

Worksheet Formulas Cell Formula F3 =D3-E3 F4 =D4-E4 F5 =D5-E5 F6 =D6-E6 F7 =D6-E6 F8 =D7-E7 F9 =D8-E8 F10 =D9-E9 I3 =G3-H3 I4 =G3-H3 I5 =G5-H5 I6 =G6-H6 I7 =G7-H7 I8 =G8-H8 I9 =G9-H9 I10 =G10-H10

Copy the above data and formulas to your excel and play around. It will be interesting to see your comments.

I’ve hardly seen any colleagues using this technique. I found this handy and thought you would find this handy too.

Share your experience:

Do you use this option more often? Share your experiences, how do you deal with this type of data. Have another tip do this stuff? Also, do not forget to leave your comments on this post. I learn a lot from you all. I’ll be posting soon, seventh post of this series “Go to Special – Select Precedents and Dependents”.

Links related to this series:

Go To Special Series. Select Comments
Go To Special Series. Select Constants and Formulas
Go To Special Series. Select Blanks
Go To Special Series. Select Current Region and Current Array
Go To Special Series. Select Objects