On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Software, Business Administration, Business Skills, Finance, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
You’ll learn how to use Excel’s efficient data visualization and manipulation tools in this valuable course. Excel expert David Ringstrom, CPA, demonstrates how to avoid retyping data and cumbersome formulas, create keyboard shortcuts to simplify repetitive tasks, eliminate duplicates from a list, identify unique items without discarding duplicates, and more.
In addition, David discusses Conditional Formatting and explains how to resolve issues that can be problematic, including how to remove non-breaking spaces from reports you copy from cloud-based software or from text with carriage returns.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.
Who Would Be Interested in This Course:
Practitioners seeking ways to analyze data in Excel more effectively and discover how to simplify filtering and formatting.
Your Benefits of Attending:
- Transforming text by way of Excel’s UPPER, LOWER, PROPER, and TRIM functions.
- Eliminating duplicates from a list with just a few mouse clicks.
- Learning two techniques for determining if conditional formatting has been applied to a worksheet, and if so, where.
- Using the Text to Columns feature in any version of Excel to quickly separate city, state, and ZIP code into separate columns.
- Simplifying repetitive tasks by creating your own keyboard shortcuts.
- Avoiding the need to retype data or cumbersome formulas by way of Excel’s Text to Columns command.
- Learning how the Table feature allows you to transform filtering tasks.
- Using Excel’s CODE function to identify pesky non-printing characters in your data.
- Using Excel’s Advanced Filter feature to identify unique items within a list without discarding duplicates.
- Getting control of long lists of data by filtering instead of sorting.
- Color-coding the top or bottom amounts within a list by way of the Conditional Formatting feature.
- Switching between Custom Views by way of a hidden drop-down list you can enable on Excel’s Quick Access Toolbar.
Learning Objectives:
- Recall how the Table feature can be used to transform filtering tasks.
- Name two techniques that allow you to determine if Conditional Formatting has been applied to a worksheet.
- Identify powerful techniques to quickly tame large sets of data.
Level:
Intermediate
Format:
Live Webcast
Field of Study:
Computer Software & Applications
Program Prerequisites:
Experience working with lists of data in Excel
Advanced Preparation:
None
Introduction
Excel versions 00:0042
Filtering Data 00:03:20
Manage Filter Arrows 00:07:57
Filtering Blank Rows 00 10:08
Ad Hoc Totals while Filtering 00:15:12
Filter Shortcut Menu 00:21:59
Filtering Keyboard Shortcut 00:25:58
Reapply 00:31:23
Custom Filter 00:34:22
Filter by Color 00:36:15
Filter by #N/A Errors 00:38:20
Advanced Filter 00:42:05
Advanced Filter - Duplicates in Place 00:46:25
Filter Function (Office 365 Only) 00:49:22
Using FILTER with Multiple Conditions 00:56:39
Office Insider Program 00:59:12
Filter - Custom Views 00:01:37
Streamlining Custom Views 00:01:53
Table Feature 00:01:07:03
Slicers with Tables (Excel 2013+) 00:01:10:04
Tables Conflict with Custom Views 01:13:34
Remove Table Feature from Worksheet 01:14:56
Text to Columns Feature - Addresses 01:16:59
Text to Columns Feature - Dates 00:01:35
UPPER/LOWER/PROPER/TRIM 01:24:14
=UPPER 01:25:19
=LOWER 01:25:27
=PROPER 01:25:35
=TRIM 01:25:48
Combine Text Concatenating 01:27:08
Data Bars 01:32:12
Color Scales 01:34:14
Icon Sets 01:36:46
Filter by Icon 01:38:24
Conditional Formatting - Top 10 01:40:390
Conditional Formatting -Duplicates 01:44:39
Eliminate Conditional Formatting 01:45:56
- Advanced Filter 00:42:05
- Clear Command 00:09:02
- Filter Command 00:04:23
- LOWER Functon 01:25:27
- MATCH Function 00:38:45
- PROPER Function 01:25:35
- Quick Access Toolbar 00:26:03
- Sort and Filter Command 00:06:03
- SUBTOTAL Function 00:16:12
- SUM Function 00:15:49
- TRIM Function 01:25:48
- UPPER Function 01:25:19
Advanced Filter: Use an Advanced Filter in Excel to create a list of unique items, or to extract specific items to a different worksheet.
Concatenation: A technique that allows you to join two or more pieces of text together. Although its simplest to use the ampersand (&), you can also use the CONCATENATE function in Excel.
Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
LOWER : =LOWER The Microsoft Excel LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function. The LOWER function is a built-in function in Excel that is categorized as a String/Text Function.
PROPER: =PROPER The Microsoft Excel PROPER function sets the first character in each word to uppercase and the rest to lowercase. The PROPER function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
Remove Duplicates: This feature first appeared in Excel 2007. This Data tab command allows you to reduce a list of items to a list of unique constituents. This action required the Advanced Filter command in Excel 2003 and earlier.
SUBTOTAL: A worksheet function that allows you to sum, average, count, and other otherwise analyze data on just the visible cells within a given range.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet.
Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.
TRIM Function : The TRIM function removes extraneous spaces from a cell or string of text once space is kept between each word.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.
UPPER: =UPPER The Microsoft Excel UPPER function allows you to convert text to all uppercase. The UPPER function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.
