Excel Agility: Nuances and Quirks
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Software, Business Administration, Business Skills, Finance, Taxation and Accounting
- Credit: ATAAA 1.0, ATATX 1.0, ATAOP 1.0, CPE 2.0
-
You’ll learn about some of Excel’s quirks and obstacles that an unaware user can encounter when working in Excel. In this course, Excel expert David Ringstrom, CPA, focuses on frustrating problems can arise when someone using a newer version of Excel shares a workbook with you (or vice versa), or, in other cases, features can sometimes unceremoniously vanish from Excel. David explains how to overcome such obstacles, thereby empowering you to work more efficiently in Excel.
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 to work more effectively in Excel and avoid what can be frustrating nuances.
Your Benefits of Attending:
- Seeing how to quickly duplicate a group of two or more worksheets.
- Managing cumbersome lists of data using the Table feature.
- Learning which features, such as Custom Views, you can’t use when one or more tables exist in a workbook.
- Utilizing the Split Worksheet feature properly to lock certain rows at the bottom of the Excel window.
- Saving time by seeing how to enter data sideways.
- Learning how to use the undocumented DATEDIF worksheet function.
- Using the Fill Series command to create a series of numbers with just a few mouse clicks.
- Learning how the Table feature allows you to transform filtering tasks.
- Learning the risks—and rewards—of double-clicking on the Fill Handle feature in Excel.
- Tweaking Excel’s AutoRecover settings to raise the odds of recovering your work after an Excel crash.
- Double-clicking tricks for navigating within a large worksheet.
- Avoiding frustration by understanding the nuances between Enter and Edit modes.
Learning Objectives:
- Recall how to apply mouse actions and keyboard shortcuts to overcome default features or behaviors in Excel.
- Recognize that some features may work in one version of Excel but not another.
- Define how the Table feature allows you to transform filtering tasks.
Level:
Basic
Format:
On-demand Webcast
Instructional Method:
Self-Study
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
None
Advance Preparation:
None
Introduction
Excel versions 00:00:42
AutoRecover Spreadsheets 00:03:20
AutoRecover Settings 00:08:44
Fill Series 00:11:24
Entering Data Sideways 00:15:29
Split Worksheet 00:19:45
Table Feature 00:24:36
Table Feature Total Row 00:34:28
Features that Table Disables 00:37:42
Self-Expanding Charts 00:43:04
Sparklines Feature (Excel 2010+) 00:48:05
Self-Expanding Sparklines 00:53:29
Disabled Sparklines 00:56:06
Allow Editing Directly in Cells 01:00:10
Quick Acces Toolbar 01:04:20
Use F2 to Toggle Enter/Edit Modes 01:07:25
Build a Pivot Table 01:09:54
GetPivotData Function 01:12:33
Undocumented DATEDIF Function 01:18:30
Double-Click to Skip the OK Button 01:20:36
Double-Click Trick: Excel Fill Hanle 01:20:52
Duplicating Excel Worksheets 01:22:59
Duplicating Multiple Worksheets 01:24:24
Double-Click to Navigate a Worksheet 01:25:34
Hidden Menu for Moving Columns/Rows 01:27:08
Worksheet Navigation Shortcuts 01:29:38
Understanding the Used Range 01:32:05
Resetting the Used Range 01:34:01
Office 365 Shared Workbooks 01:36:01
Restore Workbook Sharing/Track Changes 01:38:47
Missing Folders Link on Recent Menu 01:40:30
Thread Comments (Office 365) 01:42:42
- Fill Command 00:13:24
- Series Command 00:13:29
- Split Command 00:19:51
- SUM Function 00:29:11
- ALT-F1 00:44:55, 01:34:56
- Options Command 01:04:11
- Keyboard Shortcuts 01:04:29
- Point Mode 01:07:54
- GetPivotData Function 01:13:31
- DATEDIF Function 01:18:36
- Visual Basic Editor 01:35:05
DATEDIF: A worksheet function in Excel that works in any version of Excel but that mysteriously doesn't appear in Excel's online help documentation. DATEDIF has three arguments: Date1, Date2, and Interval. Keep in mind that DATEDIF does not count the starting period, so you may need to add 1 to its result.
DATEDIF Function: The Excel DATEDIF function returns the difference between two date values in years, months, or days.
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.
Fill Command: Excel’s Fill command lets you fill an adjacent group of cells with the same text or numbers, or with text or numbers that form a series. In its simplest form it’s like a faster version of copy and paste.
Fill Handle: The little notch in the bottom right-hand corner of the selected cell or block of cells. You can drag the fill handle to copy the contents to other cells, double-click to copy contents down a column, or right-drag to reveal a hidden context menu.
GETPIVOTDATA: This feature allows users to craft formulas that refer to specific data within the pivot table, as opposed to a specific cell address, so that formulas maintain integrity even if the pivot table fields are rearranged. This feature can be enabled or disabled as needed.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
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.
Series Function: Represents a data series in the active chart. This function is used only in charts; you cannot use it in worksheets.
Sparkline: A sparkline is a very small line chart, typically drawn without axes or coordinates. It presents the general shape of the variation in some measurement, such as temperature or stock market price, in a simple and highly condensed way. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values.
Split Command: The split command takes the place of frozen panes, just as Freeze Panes replaces an existing split
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.
Used Range: The Used Range of an Excel spreadsheet starts at cell A1 and extends down and/or across to the last cell that has been edited any point in the life of the spreadsheet, even if that cell has since been erased.
Visual Basic Editor : Excel’s development interface that can be used to edit and create macros, user forms, class modules, custom worksheet functions, and other enhancements to Excel’s functionality. The programming interface for Microsoft Excel that can be accessed by way of the Visual Basic command on Excel’s Developer tab or by pressing Alt-F11.
