On Demand Webinar

Excel Agility: Tips & Tricks Part 1

Webinar Details $219

  • Rated:
  • Webinar Length: 100 Minutes
  • Guest Speaker:   David Ringstrom
  • Topic:   Business Administration, Business Skills, Finance, Software, Taxation and Accounting
  • Credit:   CPE 2.0
All Access Membership

Excel offers many features and techniques you can implement to get your work done in a fraction of the time you might currently be spending. This jam-packed session presented by David Ringstrom, CPA, is designed to help you speed up your work in Excel. David explains a variety of Excel’s time-saving features and shares helpful tips, such as how to filter data faster, streamline repetitive tasks, create keyboard shortcuts, and access folders and workbooks easily.

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 may benefit from using Excel more effectively and efficiently.


Your Benefits of Attending:  

  • Overcoming user interface annoyances by making simple adjustments to Excel’s options.
  • Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
  • Inserting totals into lists with a few mouse clicks by way of Excel’s SUBTOTAL function.
  • Seeing how to customize the default workbook that all new workbooks will be based on going forward.
  • Streamlining repetitive tasks by way of Excel’s Quick Access Tool bar.
  • Understanding how the Table feature automates formula management within lists.
  • Leveraging Excel’s Quick Access Toolbar to create a shortcut that enables you to filter lists with a key stroke instead of multiple mouse actions.
  • Restoring “classic” Print Preview functionality in Excel 2010 and later.
  • Gaining control of long lists of data by filtering instead of sorting.
  • Simplifying repetitive tasks by creating your own keyboard shortcuts.
  • Streamlining filtering of lists in Excel 2013 and later by using the Slicer feature with tables.
  • Surfacing hidden Excel commands instantly by way of the Tell Me feature in Excel 2016.

Learning Objectives:

  • Identify how to avoid retyping text by using Excel features and worksheet functions.
  • Recall how to easily compile large lists of data by filtering vs. sorting.
  • Describe the mouse action that reveals a hidden menu for moving columns/rows.

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


  1. Introduction

  2. Excel Versions 00:00:42

  3. Recent Items Menu 00:03:20

  4. Maximize Recent Items Menu 00:10:14

  5. Automatic Backup of Key Excel Workbooks 00:15:30

  6. Customizing Excel’s Default Workbook 00:20:31

  7. File Close Trap (Excel 2013+) 00:27:46

  8. Finding Worksheet Functions 00:30:54

  9. Quick Access Toolbar 00:37:01

  10. Undo/Redo Multiple Steps at Once 00:42:50

  11. Creating a Freeze Sheet Panes Shortcut 00:44:58

  12. Restoring Classic Print Preview 00:47:32

  13. Monitoring Print Scale on Quick Access Toolbar 00:50:46

  14. Paste Column Widths 00:56:04

  15. Subtotal Feature 01:00:20

  16. Formatting Subtotals 01:05:23

  17. Filtering Data 01:07:58

  18. Filtering Keyboard Shortcut 01:10:45

  19. Add Clear Filter to Quick Access Toolbar 01:13:36

  20. Filter - Custom Views 01:15:02

  21. Table Feature 01:19:08

  22. Slicers with Tables (2013+) 01:24:39

  23. Formula Management within a Table 01:27:02

  24. Recommended Charts (2013+) 01:29:41

  25. Self-Expanding Charts 01:32:02

  26. Merge Cells 01:33:29

  27. Creating a Center Shortcut 01:35:21

  28. Center Across Selection 01:35:58

  29. Automating Center Across Selection 01:37:34

  30. Shortcuts for Running a Macro 01:41:11

  31. Creating an Icon for Our Macro 01:44:34

  32. Text Box Feature 01:46:29

  33. Presentation Closing 01:48:39

  • .XLK 00:17:35, 00:19:59
  • Autofilter 01:11:06
  • Clear Filter 01:13:36
  • Column Width 00:56:29
  • Custom Views 01:19:19
  • Filter 01:07:59
  • Freeze Panes 00:45:01
  • Fx Button 00:32:27
  • Macro 01:44:50
  • Macro Recorder 01:37:54
  • Merge Cell 01:33:32
  • Name Box 00:32:15
  • Paste Special 00:57:47
  • Print Preview 00:47:42
  • Print Scale 00:51:41
  • Quick Access Toolbar 00:37:05,00:52:39, 00:57:19, 01:13:51, 01:44:44
  • Recommended Charts Feature 01:30:10
  • Redo Command 00:43:30
  • Slicer 01:24:47
  • Subtotal Feature 01:00:25
  • Table Feature 01:19:10
  • Text Box Feature 01:46:37
  • Total Row 01:21:57
  • Undo Command 00:43:13

Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.

Macro Recorder: A feature in Excel that allows you to transcribe actions you take in Excel into programming code.

Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.

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.

Freeze Panes: This command on the View tab of Excel’s ribbon interface, or the Window menu in Excel 2003 and earlier, allows you to ensure that one or more rows and/or columns always remain on-screen as you scroll down through a worksheet.

Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.

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.

Custom Views: This feature stores a snapshot of the hidden/visible status of columns, rows, and worksheets, along with print settings and filter settings.

Recommended Charts Feature: A feature in Excel 2013 and later that enables beginners to get a jump start on creating charts, while also allowing experienced users to view data to be charted in a variety of formats.

Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.

Print Preview : Print preview refers to formatting a document for the printer but then displaying it on the display screen instead of printing it.

Slicer: You can insert slicers in Excel to quickly and easily filter pivot tables. Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click

Subtotal Feature: Available on the Data tab or menu of all versions of Excel, the Subtotal feature can be used to automatically insert totals within a list of data in a Excel spreadsheet.

.XLK: This file extension connotes an Excel Backup Workbook generated by the Always Create Backup setting for a given workbook. This setting must be enabled on an individual workbook basis. Such files cannot be opened in Excel for Mac unless you change the file extension to XLS or XLSX.

Print Scale : A measure in Excel that shows how much a printed page has been reduced in size. The Scale command appears on the Page Layout tab in Excel 2007 and later. The average person will find documents printed in a scale of 63% or less to be frustrating to read.

Fx Button: Excel Functions (fx) Excel has prewritten formulas called functions to help simplify making complicated calculations. A function takes a value or values, performs an operation, and returns a result to a cell. The values that you use with a function are called arguments.

Undo Command: The Undo feature in Excel 2010 can quickly correct mistakes that you make in a worksheet. The Redo button lets you “undo the Undo.” The Undo button appears next to the Save button on the Quick Access toolbar, and it changes in response to whatever action you just took; the Redo button becomes active whenever you use Undo.

Redo Command: Like the undo action, redo can be performed multiple times by using the same keyboard shortcut over and over. The Excel Ribbon also has a redo button right next to the undo button; it is represented by an icon with an arrow pointing to the right. After using the Undo button on the Quick Access toolbar, Excel 2010 activates the Redo button to its immediate right. If you delete an entry from a cell and then click the Undo button or press Ctrl+Z, the ScreenTip that appears when you position the mouse pointer over the Redo button appears as Redo Clear (Ctrl+Y).

Column Width: You can specify the number of characters for the column width. Column widths can also be changed in Data View by clicking and dragging the column borders.Column width for proportional fonts is based on average character width. Depending on the characters used in the value, more or fewer characters may be displayed in the specified width. Column width affects only the display of values in the Data Editor. Changing the column width does not change the defined width of a variable.

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.

Autofilter: The basic Excel filter (also known as the Excel Autofilter) allows you to view specific rows in an Excel spreadsheet while hiding the other rows. When the Excel autofilter is added to the header row of a spreadsheet, a drop-down menu appears in each cell of the header row.

Clear Filter: To clear a filter for one column in a multicolumn range of cells or table, click the Filter button Applied filter icon on the heading, and then click Clear Filter from <Column Name>.

Merge Cell: Merge cell is a function in database software that allows multiple adjacent cells to be combined into a single larger cell. This is done by selecting all cells to be merged and choosing the "Merge Cells" command. A close-up of a spreadsheet on a computer screen.


Guest Speaker

  • David H. Ringstrom, CPA

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or training@auroratrainingadvantage.com.

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.