Filtering and Formatting Excel Data
Author: David H. Ringstrom
||3 hours for CPAs
Many users are unaware of the many data visualization and manipulation tools available in Microsoft Excel. In this session Excel expert David Ringstrom, CPA shows you various ways to streamline filtering tasks, including little known shortcuts such as Custom Views and the hidden AutoFilter command. David will also discuss Conditional Formatting, shows you how to how to resolve thorny issues like removing non-breaking spaces from reports you copy from cloud-based software or text with carriage returns.
David’s materials cover Excel 2016, 2013, 2010, and 2007. When applicable, some slides include alternate instructions for Excel 2003. In this presentation, he demonstrates techniques in Excel 2010, as he finds it’s presently the version most widely utilized by Excel users. David’s detailed handouts serve as reference tools you can fall back on after completing one of his courses. He also provides an Excel workbook that includes a majority of the examples he uses during each session.
Publication Date: October 2015
Users who work with large data sets in Excel
- Avoid retyping data or clunky formulas by way of Excel's Text to Columns command.
- By-pass the filter drop-down lists by filtering based on cell contents with a single keystroke or mouse click.
- Identify duplicates in a list by using Conditional Formatting.
- Learn which features you can't use when one or more tables exist in a workbook, such as Custom Views.Learn which features you can't use when one or more tables exist in a workbook, such as Custom Views.
- Quickly whip unwieldy data into the format required for pivot table analysis.
- See how the Table feature allows you to transform filtering and automate formula entry.
- See two techniques for determining if conditional formatting has been applied to a worksheet, and if so, where.
- Simplify repetitive tasks by creating your own keyboard shortcuts.
- Transform dates and numbers into various formats without retyping by way of custom number formats.
- Transform text by way of Excel's UPPER, LOWER, PROPER, and TRIM functions.
- Use Conditional Formatting to color code your data, identify duplicates, and apply icons.
- Use Custom Views to quickly apply different filter settings with just a couple of mouse clicks.
- Use Excel's CODE function to identify pesky non-printing characters amidst your data.
- Use the CLEAN and TRIM functions to eliminate non-printing characters such as tabs and carriage returns along with spaces from your data.
- Use the SUBSTITUTE function to strategically replace unwanted characters within your data.
- Use the Text to Columns feature to eliminate unwanted text without retyping, or to separate text into separate columns.
- Wrangle long lists of data by filtering instead of sorting.
- Identify the Quick Access Toolbar command that can enable you to filter data with one click.
- Recognize which Excel function capitalizes the first letter of each word.
- Discriminate between worksheet functions to use when extracting characters from a cell.
NASBA Field of Study
Computer Software & Applications (3 hours)