Sorting codes sort expenses or revenues, sequence an actual or budget variance report by the largest variance, or sort the row descriptions alphabetically. The following sorting codes are available:
- SORT: Sorts the report in ascending order, based on the values in the specified column.
- ASORT: Sorts the report by the absolute value of the values in the specified column in ascending order. In other words, the sign of each value is ignored in the sort. This format code sequences the values by the magnitude of the variance, whether positive or negative.
- SORTDESC: Sorts the report in descending order, based on the values in the specified column.
- ASORTDESC: Sorts the report in descending order by the absolute value of the values in the specified column.
Show me an example
The following building block excerpt sets up an ascending sort on the values in column D of the report for rows 160 through 490. The excerpt also sets up a descending sort on the absolute values in column G of the report for rows 610 through 940.
| Row Code |
Description |
Format Code |
Related Formulas/Rows/Units |
Normal Balance |
Column |
Link to Financial Data Source |
| 100 |
Sorted by Monthly Variance in Ascending Order |
DES |
|
|
|
|
| 130 |
|
SORT |
160:490 |
|
D |
|
| 160 |
Sales |
|
|
C |
|
4100 |
| 190 |
Sales Returns |
|
|
|
|
4110 |
| |
... |
|
|
|
|
|
| 490 |
Interest Income |
|
|
C |
|
7000 |
| 520 |
|
DES |
|
|
|
|
| 550 |
Sorted by YTD Absolute Variance in Descending Order |
DES |
|
|
|
|
| 580 |
|
ASORTDESC |
610:940 |
|
G |
|
| 610 |
Sales |
|
|
C |
|
4100 |
| 640 |
Sales Returns |
|
|
|
|
4110 |
| |
... |
|
|
|
|
|
| 940 |
Interest Income |
|
|
C |
|
7000 |
The resulting report looks similar to the following:
| Variance Analysis (Sorted by Variance) |
| Denver and San Francisco Regions |
| For the Seven Months Ending July 31, 2007 |
| |
July |
YTD |
| |
Actual |
Budget |
Variance |
Actual |
Budget |
Variance |
| |
|
|
|
|
|
|
| Sorted by Monthly Variance in Ascending Order |
| COGS |
873,872 |
236,144 |
(637,728) |
4,864,274 |
1,590,315 |
(3,273,959) |
| Salaries and Wages |
97,624 |
65,573 |
(32,051) |
653,884 |
441,664 |
(212,220) |
| Sales Discounts |
36,383 |
24,152 |
(12,231) |
241,562 |
162,670 |
(78,892) |
| Sales Returns |
10,917 |
7,246 |
(3,671) |
62,809 |
48,803 |
(14,006) |
| Rent Expense |
12,052 |
9,019 |
(3,033) |
80,444 |
60,748 |
(19,696) |
| Office Expense |
5,023 |
3,291 |
(1,732) |
33,420 |
22,098 |
(11,322) |
| Travel Expense |
7,656 |
7,641 |
(15) |
51,062 |
51,469 |
407 |
| Sales |
1,240,119 |
410,389 |
829,730 |
7,139,288 |
2,764,549 |
4,374,739 |
| |
|
|
|
|
|
|
| Sorted by YTD Absolute Variance in Descending Order |
| Sales |
1,240,119 |
410,389 |
829,730 |
7,139,288 |
2,764,549 |
4,374,739 |
| COGS |
873,872 |
236,144 |
(637,728) |
4,864,274 |
1,590,315 |
(3,273,959) |
| Salaries and Wages |
97,624 |
65,573 |
(32,051) |
653,884 |
441,664 |
(212,220) |
| Sales Discounts |
36,383 |
24,152 |
(12,231) |
241,562 |
162,670 |
(78,892) |
| Rent Expense |
12,052 |
9,019 |
(3,033) |
80,444 |
60,748 |
(19,696) |
| Sales Returns |
10,917 |
7,246 |
(3,671) |
62,809 |
48,803 |
(14,006) |
| Office Expense |
5,023 |
3,291 |
(1,732) |
33,420 |
22,098 |
(11,322) |
| Travel Expense |
7,656 |
7,641 |
(15) |
51,062 |
51,469 |
407 |
Use a sorting code
- Double-click the Format Code cell, and then select a sorting code.
- In the Related Formulas/Rows/Units cell, type the range of row codes to be sorted.
Enter the first and last row codes separated with a colon (:). For example, 160:490 specifies the range 160 through 490.
- In the Column cell, type the letter of the report column to be used for the sort.
Note Include only amount rows in a sort calculation.
Top of Page
