How to Filter Data in the Excel Checkbook Registers

Applies to Excel templates: "Georges Budget for Excel", "Georges Excel Checkbook Register", "Georges Excel Checkbook for Mac"

(back to All Support Topics)

 

What is Excel filtering?

When you have a large set of rows in Excel and in the case of a checkbook registers, a large number of transactions, Excel filtering allows you to create a subset of the total rows based on different criteria in each column. The way it works is when you apply a filter based on certain criteria, Microsoft Excel display only the rows that meet the filter criteria and hides the rows that do not. You can then remove the filters to display all rows. You can also get a subtotal of the Amount field based on just the filtered rows so you can create a report right within the checkbook registers.

What criteria can you filter on?

Each column can be filtered based on different criteria and the criteria is based on the content in the column. For example, the Date column can be filtered based on different date ranges and specific calendar dates and predefined date ranges such as This Month or Last Month. The Description column can be filtered on different payees. The Category column can be filtered based on the different budget categories entered in the column. The Amount field can be filtered based on amounts entered in the column, and other comparison criteria such as smaller than, greater than, and equal to (<,>, and =). Since you can combine filters on different columns, you can create a filter, for example to display all the transactions where you spent money at restaurants the last month that were greater than $50 and get a subtotal of them. There is also a custom auto filter where you can filter a single column on more than one criteria using and / or.

Help Topic: Filter and Search data in the Registers

The Filter (also known as AutoFilter) command displays the Filter arrows located on the column headings of the Registers. To filter, click an arrow and choose a filter option. You can apply filters on several different columns. The sort portion of the Filters is disabled. Only use the Filter command / Filter arrows within the Registers and not any other sheet. If you do not see the Filter arrows in the column headings, click "Toggle Filter" button at the top of the registers to Show / Hide the Filter arrows in column headings.

 

 

 

 

 

Do not use the Advanced Filter command located on the Data Tab of Excel's Ribbon. Using the Advanced Filter may delete, overwrite, or hide data in Georges Budget for Excel. To filter data, use the Filter arrows in the column headings of the Registers.

When you apply a filter, the status bar at the bottom of Excel provides data regarding how many rows are displayed.

Make sure you clear any filtered data to display all records when you are done filtering. To clear the filters, click the Filter arrows located in the column headings and click the "Clear Filter From" command. This needs to be done for every column that has a filter applied.

Also, for "Georges Budget for Excel", clicking the New Transaction button, the Sort by Date button, the Clear All Filters button, the Toggle Filter button, the Insert Row button, and the Delete Empty Rows & Sort button at the top of a Register will remove any applied Filters from the corresponding Register. If the Recategorize button makes changes to categories, it will also remove any applied Filters.

Also, for "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac", clicking the New Transaction button, the Sort by Date button, the Clear Rec button, the Toggle Filter button, the Insert Row button, and the Delete Empty Rows & Sort button at the top of a Register will remove any applied Filters from the corresponding Register.

The reports and charts that are in the Accounts Summary and Reports & Charts sheet (Monthly Actual vs Budget Report and Yearly Actual vs Budget Report) are based on all transactions in all the Registers, even if the Filter command is used to filter out (hide) some or all the transactions from a particular Register(s).

Each Excel filter in the columns headings has a search box that you can use to search what is in a particular column and select items to be filtered.

Create a $ subtotal of filtered (non hidden) transactions

Specifically in reference to "Georges Budget for Excel", the total amount in cell reference G4005 of the Registers is to be used in conjunction with the Filter arrows that are located in the column headings of the Registers. After a filter(s) are applied this total will sum the amounts of the displayed rows and exclude the amounts in the rows that were filtered out (hidden). Depending on what you are trying to filter and assuming the register includes split transactions (main split transaction and their related split itemizations), you may want to filter out the main split transactions or their related split itemizations to avoid adding split transactions (main split transaction and their related split itemizations) twice in this total (cell reference G4005).

Specifically in reference to "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac", the total amount in cell reference G1505 of the Registers is to be used in conjunction with the Filter arrows that are located in the column headings of the Registers. After a filter(s) are applied this total will sum the amounts of the displayed rows and exclude the amounts in the rows that were filtered out (hidden).

See Excels Help for more info on how to use Filters (AutoFilters).

 

(back to All Support Topics)