Excel Agility: Techniques for Filtering and Formatting Data

Custom number formats in Excel let you control exactly how values appear in cells—without changing the underlying data. Access custom formats via Format Cells (Ctrl+1) > Number tab > Custom. The format code has up to four sections separated by semicolons: positive;negative;zero;text. Common format code building blocks include 0 (forces a digit, shows 0 if blank), # (shows a digit only if present), . (decimal point), , (thousands separator—one comma at end scales by 1,000), % (multiplies by 100 and adds %), and text in double quotes is displayed literally. For example, "$"#,##0.00_);("$"#,##0.00) shows positive values as $1,234.56 and negatives in parentheses (accounting format). Adding [Red] before a section code displays that section in red. Date formats use d (day), m (month), y (year), h (hours), and s (seconds) in various combinations: dd/mm/yyyy, mmm-yy, or mmmm d", "yyyy. A useful trick: 0.0,," M" displays a large number like 1,250,000 as 1.3 M by scaling down two comma levels. Custom formats are stored in the workbook and can be copied to other files via Paste Special > Formats. For financial reports with specific presentation requirements—such as showing thousands in abbreviated form or applying color-coded negative numbers—custom format codes are an indispensable professional tool.

Excel's date filtering capabilities go far beyond simple value selection. When a column contains dates, AutoFilter's dropdown shows a Date Filters submenu with intelligent options: This Week, Last Month, This Quarter, Year to Date, Between (for a custom date range), Before, After, and more—without requiring any formulas. These dynamic filters update automatically: This Month always shows the current month's data regardless of when the file is opened. To filter by a specific date range, use Date Filters > Between and specify start and end dates. For pivot tables, the Timeline Slicer provides a visual date range selector. Power Query offers date filtering in the column header dropdown, including advanced options like filtering by month number or keeping only dates in the last N days. For AutoFilter-based filtering by fiscal periods not aligned to calendar months, using a helper column that maps each date to the appropriate fiscal period and filtering on that column is the most flexible approach. When working with data exported from accounting systems, ensuring date columns contain actual Excel date values—not text—is a prerequisite for all date filtering to work correctly. Mastering date filters eliminates the need to manually sort and scan date-based reports, dramatically improving efficiency for accountants and analysts who work with transaction-level data in Excel.

Wildcards in Excel allow flexible pattern-matching in filters, COUNTIF/SUMIF formulas, and Find & Replace operations. The question mark (?) matches exactly one character—searching for C?T would match CAT, CUT, or COT. The asterisk (*) matches any number of characters (including zero)—searching for "Smith*" matches Smith, Smithson, and Smithfield. For AutoFilter text filters, click the dropdown arrow, go to Text Filters > Contains (or Begins With, etc.) and type your wildcard pattern, or use Custom Filter to specify the pattern directly. In formulas, wildcards work in COUNTIF, SUMIF, COUNTIFS, SUMIFS, MATCH, and XLOOKUP: =SUMIF(A:A,"North*",B:B) sums all values in column B where column A begins with North. To search for a literal asterisk or question mark (rather than the wildcard meaning), prefix with a tilde: ~* matches an actual asterisk character. Wildcards do not work in IF, VLOOKUP, or most other functions—only in the conditional aggregation family and dedicated lookup/match functions. In Find & Replace (Ctrl+H), wildcards in the Find what field allow batch replacements of patterns, such as replacing all values starting with INV- with INVOICE-. For accountants cleaning exported data with inconsistent account name formats, wildcard-based SUMIF formulas and Find & Replace operations are essential tools.

Format as Table (Home > Format as Table) is one of the quickest ways to both professionally style a data range and convert it to a fully functional Excel Table simultaneously. Select any cell within your data range, click Format as Table, and choose from 60 built-in styles in light, medium, and dark variants. Excel detects the data boundaries automatically and asks whether your table has headers. After clicking OK, the range becomes a structured Table with banded rows, header-row formatting, AutoFilter dropdowns on every column, and the ability to add a Total Row—all in a few clicks. The same result can be achieved with Ctrl+T followed by a manual style selection, but Format as Table combines both steps. All Table benefits apply immediately: auto-expansion as new data is added, structured references for formulas, Slicer connectivity, and seamless Power Query integration. For users unfamiliar with the Table feature who have plain data ranges in their workbooks, Format as Table is the most accessible entry point—it delivers visual and functional improvements without requiring knowledge of structured references or Table Design tab options. The visual differentiation of the styled table also helps non-technical users identify which ranges contain data versus calculations, improving readability for shared workbooks.

Applying identical formatting to multiple sheets at once is accomplished by grouping sheets. Hold Ctrl and click the tab of each sheet you want to include in the group (or click the first sheet tab, then Shift+click the last to select a consecutive range). When sheets are grouped, the tab bar shows [Group] in the title bar as a reminder. Any action performed—entering data, applying formatting, inserting rows, writing formulas—applies to the same location on all grouped sheets simultaneously. For standardizing header formatting, column widths, and print settings across monthly report tabs, grouping saves significant repetitive work. Once formatting is complete, click any non-grouped sheet tab (or right-click a grouped tab and select Ungroup Sheets) to exit group mode. Caution: accidentally entering data while sheets are grouped applies that data to all grouped sheets—a common mistake that can overwrite important data. For formatting-only tasks, grouping is low risk; for data entry, always verify grouping status first. An alternative for global formatting changes is creating a standardized template sheet, formatting it completely, then right-clicking and selecting Move or Copy to create duplicates as needed. For recurring monthly workbooks where all period sheets must look identical, sheet grouping combined with a well-designed template tab eliminates the most tedious manual formatting work.