Excel Agility: Taming Large Spreadsheets
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
-
This comprehensive presentation is designed to help Excel users who get frustrated when they attempt to manage their large spreadsheets. Excel expert David Ringstrom, CPA, demonstrates a number of ways you can successfully tame cumbersome spreadsheets. His helpful tips enable you to save time, improve the integrity of large workbooks, avoid repetitive tasks, and much more.
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 efficiently in large Excel workbooks as well as improve the integrity of their spreadsheets.
Topics Covered:
- Comparing the risks and benefits of 64-bit versus 32-bit Microsoft Excel.
- Restoring Full Screen View in Excel 2013 and later as well as enabling a keyboard shortcut for this feature.
- Navigating large workbooks with ease by way of a hidden menu as well as keyboard shortcuts.
- Learning how to use a single line of programming code to unhide all worksheets within a workbook.
- Understanding Excel’s Freeze Panes feature.
- Using the Group command to interactively hide/unhide columns (and/or rows) within Excel worksheets.
- Utilizing the Split Worksheet feature properly to lock certain rows at the bottom of the Excel window.
- Discovering resources for making unwieldly spreadsheets calculate faster.
- Exploring how the INDIRECT worksheet function can automate pulling values from a group of individual worksheets.
- Hiding multiple worksheets within a workbook at once.
- Navigating purposefully through worksheets by way of clickable hyperlinks.
Learning Objectives:
- State how to hide multiple worksheets within a workbook simultaneously.
- Identify how to apply the Custom Views feature to manage the hidden/visible status of columns, rows, worksheets, print settings, and more.
- Recall how to implement the Split Worksheet feature to lock certain rows at the bottom of the Excel window.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience with Excel Spreadsheets
Advance Preparation:
None
Introduction
Excel Versions 00:00:50
Viewing Two Worksheets at Once 00:03:28
Viewing Two Worksheets at Once (cont.) 00:09:31
Viewing Excel on More Than One Monitor 00:13:20
Zoom to Selection 00:17:21
Full-Screen View 00:21:15
Toggle Full-Screen Shortcut 00:26:25
Freeze Panes Feature 00:29:01
Creating a Freeze Panes Shortcut 00:33:13
Split Worksheet 00:35:53
Group and Ungroup Rows and Columns 00:40:06
Worksheet Selection Shortcuts 00:44:51
Double-Click to Navigate a Worksheet 00:48:42
Watch Window 00:51:08
Use Hyperlinks as a Navigation Aid 00:57:16
Worksheet Tab Navigation Tricks 01:01:09
Group Worksheets 01:03:50
Hiding Multiple Sheets 01:06:20
Unhide All Worksheets in a Workbook 01:08:35
Automating Unhiding Worksheets 01:11:52
Automating Unhiding Worksheets (cont.) 01:16:23
Automating Unhiding Worksheets (cont.) 01:18:26
Running Unhide Worksheets Macro 01:19:55
All Worksheets Custom View 01:21:49
Quarters Only Custom View 01:24:36
Streamlining Custom Views 01:27:25
Restoring Classic Print Preview 01:30:41
Drill Through Worksheets 01:33:48
INDIRECT Function 01:38:11
Understanding Volatile Worksheet Functions 01:41:41
Improving Calculation Speed 01:42:43
32-Bit versus 64-Bit Excel 01:43:22
Using Excel’s Binary Workbook Format 01:44:26
Conclusion 01:45:52
- Binary Workbook 01:44:35
- Custom Views 01:07:31, 01:22:31
- Freeze Panes 00:29:02
- Full-Screen View Command 00:21:19
- Group Worksheets 01:03:50
- Hyperlink 00:57:55
- INDIRECT Function 01:38:11
- Macro 01:12:03, 01:20:00
- Macro Recorder 01:12:19
- Print Preview 01:30:49
- Quick Access Toolbar 00:26:32
- Split Command 00:36:00
- Visual Basic Editor 01:08:55
- Volatile Worksheet Functions 01:41:51
- Watch Window 00:51:13
- Workbook 00:03:33
- Worksheet Tab 00:03:52, 01:01:09
- Zoom to Selection 00:18:02
Binary Workbook: A file with the XLSB file extension is an Excel Binary Workbook file. They store information in binary format instead of XML like with most other Excel files (like XLSX). Since XLSB files are binary, they can be read from and written to much faster, making them extremely useful for very large spreadsheets.
Custom Views: This feature stores a snapshot of the hidden/visible status of columns, rows, and worksheets, along with print settings and filter settings.
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.
Full-Screen View Command: To view more data on the screen, you can temporarily switch to the full screen view. Full-screen view hides the Microsoft Office Fluent user interface Ribbon, the formula bar, and the status bar. To have access to the hidden elements again, you have to return to the normal screen view.
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.
Hyperlink: A Hyperlink is a reference to a document, a location or an action that the reader can directly follow by selecting the link.
Indirect Function: The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.
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.
Print Preview : Print preview refers to formatting a document for the printer but then displaying it on the display screen instead of printing it.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
Split Command: The split command takes the place of frozen panes, just as Freeze Panes replaces an existing split
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.
Volatile Worksheet Functions: Volatile functions recalculate each time any change is made to a worksheet. Used en masse, volatile worksheet functions can degrade calculation performance in Excel spreadsheets. Most Excel worksheet functions are nonvolatile, meaning they only recalculate when a directly related cell is changed.
Watch Window: A formula auditing and workbook navigation feature that enables users to monitor the value and formulas within key worksheet cells as well as easily navigate to said cells.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheet Tab: A sheet, sheet tab, or worksheet tab is used to display the worksheet that a user is currently editing. By clicking a worksheet tab (located at the bottom of the window), users may move between the various worksheets. Every Excel file may have multiple worksheets, but the default number is three.
Zoom to Selection: You can zoom to the current selected range of cells with a single click of the Zoom to Selection icon.
