Excel Agility: Tips & Tricks Part 2
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
You’ll learn even more time-saving ways to reduce the time and effort it takes to complete your work in Excel in this presentation. Excel expert David Ringstrom, CPA, demonstrates shortcuts that allow you to streamline repetitive tasks and redundant data entry, embed lists you use frequently, transform filtering tasks, and more. In addition, David explains the benefits associated with Excel’s Text Box feature, the Personal Macro Workbook, and the Quick Analysis feature.
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 who can benefit by learning a variety of techniques and shortcuts that will speed up their work in Excel.
Your Benefits of Attending:
- Separating first/last names into two columns without using formulas or retyping.
- Streamlining redundant data entry with a handy keyboard shortcut.
- Pasting a table as a raw list elsewhere in a workbook.
- Editing .PDF documents in Word 2013 and later—useful for unlocking data you wish to edit in Excel.
- Streamlining the process of printing spreadsheets to .PDF format.
- Applying formatting to multiple locations within a worksheet by way of the Format Painter feature.
- Mastering the nuances—and the power—of Excel’s Transpose feature.
- Avoiding the need to retype data or clunky formulas by way of Excel’s Text to Columns command.
- Navigating large workbooks with ease by way of a hidden menu as well as keyboard shortcuts.
- Discovering the Custom Lists feature in Excel, which enables you to embed frequently used lists into Excel’s Options dialog box for use with any spreadsheet.
Learning Objectives:
- Describe how to format multiple locations in a worksheet using the Format Painter feature.
- Identify the feature that helps you transform filtering tasks.
- Recall how to embed frequently used lists.
Level:
Basic
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
None
Advance Preparation:
None
Introduction
Excel Versions 00:00:52
Navigation Trick within Excel’s Open Dialog Box 00:03:30
Maximizing the Favorites Bar 00:06:03
Ensure E-Mail Attachments Get Attached 00:10:15
Streamlining PDF Creation 00:14:05
One-Click Access for Creating PDF Files 00:16:01
Emailing Spreadsheets as a PDF 00:18:32
Editing PDF Documents (Word 2013+) 00:21:16
Closing All Files at Once 00:26:32
Introduction to Flash Fill (Excel 2013+) 00:29:26
Flash Fill SSNs (Excel 2013+) 00:29:26
Using Text to Columns for Names 00:36:44
Remove Duplicate Feature 00:40:40
Custom List Feature 00:44:04
Special Sorting Options 00:49:51
Managing Borders 00:54:13
Paste Special - All Except Borders 00:58:21
Format Painter Double-Click Trick 01:00:39
Strikethrough Text/Numbers 01:04:21
Alt-Down Arrow 01:08:50
Use F2 to Toggle Enter/Edit Modes 01:12:55
Text to Speech Command 01:17:26
Transpose Data 01:21:50
Combine Multiple Worksheets 01:27:18
Combine Multiple Worksheets (cont.) 01:30:53
Combine Multiple Worksheets (cont.) 01:32:08
Combine Multiple Worksheets (cont.) 01:33:53
Worksheet Tab Navigation Tricks 01:35:12
Group Worksheets 01:38:14
Presentation Closing 01:43:18
- .PDF 00:14:05, 00:18:32
- Alt-Down Arrow 01:08:56
- Borders 00:54:22
- Custom Lists 00:44:13
- Dialog Box 00:03:53
- Edit Mode 01:13;11
- Enter Mode 01:13:09
- Favorites 00:06:19
- Flash Fill 00:29:43, 00:37:16
- Format Painter 01:00:42
- Group Worksheets
- Microsoft Query 01:27:48
- Paste Special 00:58:29
- Point Mode 01:13:09
- Quick Access Toolbar 00:27:10
- Ref Edit Field 01:14:13
- Remove Duplicates 00:40:43
- Scroll Bar 01:01:52, 01:03:56
- Strikethrough 01:04:35
- Text to Columns Wizard 00:37:40, 00:38:47
- Text to Speech 01:17:33
- TRANSPOSE 01:21:50
- Union Statement 01:32:48
Alt-Down Arrow: Alt-Down Arrow gives an easy means to speed through repetitive data entry within a column. Second, it allows you to access Excel's Filter arrows as well as Data Validation lists without using the mouse.
Borders: In the Microsoft Excel program, "borders" is a built-in tool that lets users access predefined border styles to add a border around two or more cells on a spreadsheet. You can also use the borders tool to create your own custom border.
Custom Lists: The Custom Lists feature in Excel enables you to store frequenly used lists in Excel for use in any spreadsheet by simply typing one of the items on the list and then dragging the fill handle down or to the right.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Edit Mode: Excel defaults to Point or Enter mode when you click in a RefEdit field within an Excel dialog box. RefEdit fields allow you to select one or more cells to be used by an Excel feature. Press F2 within any of these fields to be able to use your arrow keys to navigate within the field to make changes to the cell reference.
Enter Mode: When you type the equal sign to begin the formula, Excel goes into Enter mode, which is the mode you use to enter text (such as the formula’s operands and operators).
Favorites: The Spreadspeed Favorites tool offers two ways to increase your productivity with Excel. First, you can bookmark a worksheet (down to the range level), so you can quickly open the file and navigate to a sheet, table, or cell. Second, the favorite Formulas tab lets you save custom formulas for later re-use.
Flash Fill: Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note: Flash Fill is only available in Excel 2013 and later.
Format Painter: The Format Painter copies formatting from one place and applies it to another. For example, if you have written text in Word, and have it formatted using a specific font type, color, and font size you could copy that formatting to another section of text by using the Format Painter tool.
Group Worksheets : To group two or more worksheets, hold down the Shift key to select a range of sheets or the Ctrl key to select individual sheets. When sheets are grouped, any changes are made simultaneously on any other worksheets presently included in the group.
Microsoft Query : A feature on Excel's Data menu that enables you to connect Excel spreadsheets to external data sources such as accounting software, databases, text files, Excel workbooks, and more.
PDF: Portable Document Format, a universal document format created by Adobe that allows cross-platform compatibility of documents.
Paste Special : Paste special is a common feature in productivity software such as Microsoft Office and OpenOffice. It is very commonly used in Word, Excel, Writer, and Calc to provide special formatting or calculations when pasting content into a document. If you want to paste only a specific aspect of the copied data like its formatting or value, you would use one of the Paste Special options.
Point mode: If you press any keyboard navigation key (such as Page Up, Page Down, or any arrow key), or if you click any other cell in the worksheet, Excel enters Point mode. This is the mode you use to select a cell or range as a formula operand. When you’re in Point mode, you can use any of the standard range-selection techniques. Note that Excel returns to Enter mode as soon as you type an operator or any character.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
Ref Edit Field: Allows the user to select a range of cells in Excel. The RefEdit control returns a text string that represents a range address.
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.
Scroll Bar: A vertical or horizontal bar commonly located on the far right or bottom of a window that allows you to move the window viewing area up, down, left, or right. Scrolls through a range of values when you click the scroll arrows or when you drag the scroll box. You can move through a page (a preset interval) of values by clicking the region between the scroll box and either scroll arrow.
Strikethrough: It means to draw a line through a value in a cell. In Microsoft Word, the option for striking through a text is there on the home tab of the ribbon. ... When it comes to Excel, we don't have any direct option to apply strikethrough to a cell. No button or an option is there on the ribbon. The keyboard shortcut is Ctrl-5 with Shift key
TRANSPOSE: The Microsoft Excel TRANSPOSE function returns a transposed range of cells. For example, a horizontal range of cells is returned if a vertical range is entered as a parameter. Or a vertical range of cells is returned if a horizontal range of cells is entered as a parameter.
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.
Text to Speech: A hidden feature in Excel 2007 and later by which Excel can read the contents of one or more cells out loud. You can add the Text to Speech commands to the Quick Access Toolbar in Excel 2007 and later, and alternatively, the Excel Ribbon in Excel 2010 and later.
Union Statement: The Union method in Excel VBA returns a Range object that represents the union of two or more ranges
