I think the Pivot Table is the best way to print reports for Auditors – but if you like to use the main tab for subtotals, below is helpful information.
Also - if you will click on the Help button in the Subtotal area - it will take you to information in the Helpful Information tab that is really good documentation when you are using subtotals.
|
|
If you filter in your main budget tab and then try to subtotal, excels places extra subtotals if the spreadsheet was not sorted correctly before filtering. These are the things you can do to avoid this.
1. The BEST option - Just retrieve exactly what you need so that you do not have to filter - example - retrieve Fund 3 only from the settings tab by selecting List of Funds and type 3 for Federal codes. You will not have to filter and you can select the Subtotal button of your choice - for example Subtotal by Program Codes or Account# if you wish to see object totals as well.
|
|
2. If you have retrieved all information and you want to filter and subtotal you have to do the following in the correct order.
· Sort spreadsheet FIRST (prior to filtering). In excel, you must sort and then subtotal by the same order of information. For example, if you want to subtotal by PRC codes, you must Sort by PRC and then subtotal by PRC. You will notice that the Sort Buttons are exact duplicates of the Subtotal buttons..so if you select a Sort button then you must select the same titled Subtotal button.
· Filter for the information that you want to show in your subtotals
Select the Subtotal Button (must match the sort button you selected). For example, if you Sort by Acct#, Filter and Subtotal by Acct#, your information will be in the correct order.
|
|
· Filtering and subtotals - if you filter and then subtotal - you will notice that your information is not color coded - all information will be correct and in the correct order but you will not have the option of blue for object code totals, green for PRC totals. You also will not have the Subtotal view level that is usually in the upper right corner that you can drill up and down on your information.
|
|
· If you have retrieved all information and you want to see Fund 3 with the different color coded totals - Do Not Filter - Select the Sort and Subtotal buttons in the correct order - Sort first, subtotal 2nd. Click on Level 2 in the Subtotal view bar so that it gives you the totals by each Fund. Click on the Plus Sign beside the Fund that you wish to expand (example Fund 3) and you can highlight and select Print Selection to print just the Fund 3 with the color coded totals.
|
|
Select Print, Print Active Sheets and select Print Selection to print only the highlighted section.
|
|