Get ahead before the holidays. Individual webinars are $149 throughout October. *Pricing automatically applied in cart.

Excel Agility: Taming Large Spreadsheets

Access this expert-led webinar instantly, available anytime on-demand.

Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00 $149.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide participants through various techniques to enhance their efficiency and productivity in Excel. Topics covered include viewing a workbook on two monitors for improved multitasking, viewing two worksheets on one monitor for easy comparison, and utilizing Zoom to Selection for focused analysis. You'll also learn how to maximize screen real estate with the buried Full Screen mode, utilizing the Split feature for simultaneous viewing and editing two or more areas of a worksheet. Other techniques include grouping and ungrouping rows and columns, leveraging the Watch Window for monitoring key data, using hyperlinks as a navigation aid for quick access to relevant information, and utilizing the Navigation task pane in Excel for Microsoft 365 for streamlined navigation and organization.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019, or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations. 

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Who should attend:

Professionals seeking to use Microsoft Excel more effectively.

Topics covered:

• Creating a macro you can use in any workbook in order to display all sheets at once.

• Minimizing the file size of large workbooks by way of the Excel Binary Workbook format.

• Choosing between custom views within a workbook to hide/display multiple worksheets at once.

• Running a macro within any workbook to display all sheets at once.

• Reviewing volatile worksheet functions and determining how such functions can affect calculation performance in Excel.

• Using the Watch Window to easily monitor the ramifications of even minor changes to your workbooks.

• Using the SUM function to drill through two or more worksheets.

• Creating custom views that will enable you to display all worksheets in a workbook at once as well as hide/display selected worksheets.

• Navigating purposefully through worksheets by way of clickable hyperlinks.

• Utilizing the New Window and Arrange Windows commands to view two different worksheets simultaneously.

• Making the same edits on multiple worksheets at once by grouping worksheets.

• Streamlining Custom Views by adding a drop-down list to Excel's Quick Access Toolbar.

Learning objectives:

• State which feature in Excel allows you to combine two or more columns together.

• State which version(s) of Excel offer the ability to extract data from PDF files by way of Power Query.

• State the functionality that the Flash Fill feature offers.

Level: Basic
Format: Live Webcast
Instructional Method: QAS Self-Study (Traditional)
NASBA Field of Study: Computer Software & App (2 hours)
Program Prerequisites: None
Advance Preparation: No
  1. Introduction 
  2. Topics at a Glance 00:01:15
  3. Presenting with Microsoft 365 for Windows 00:02:21
  4. Section 1: Navigating Large Workbooks 00:03:56
  5. Worksheet Tab Navigation Tricks 00:04:53
  6. Using the Navigation Pane (Microsoft 365) 00:09:35
  7. Streamlining Navigation with Hyperlinks 00:15:25
  8. Section 2: Adjusting the View 00:22:58
  9. Viewing a Workbook on Two Monitors 00:24:03
  10. Viewing Two Worksheets on One Monitor 00:28:43
  11. Zoom to Selection 00:33:31
  12. Fixing Skewed Scroll Bars in Excel 00:37:09
  13. Full-Screen Mode 00:43:09
  14. Restoring Full-Screen View 00:47:12
  15. Section 3: Locking and Splitting for Clarity 00:52:23 
  16. Freeze Panes Feature 00:52:24
  17. Creating a Freeze Sheet Panes Shortcut 00:58:16
  18. Splitting Worksheets 01:00:57
  19. Grouping Rows and Columns for Easy Expansion 01:11:46
  20. Section 4: Managing Multiple Worksheets 01:15:31
  21. Group Worksheets 01:17:05
  22. Hiding Multiple Worksheets 01:21:53
  23. Creating a Custom View to Unhide All Worksheets 01:25:45
  24. Quarters Only Custom View 01:28:34
  25. Streamlining Access to Custom Views 01:30:24
  26. Section 5: Specialized Techniques 01:35:17
  27. Using the Watch Window in Excel 01:35:31
  28. Using Excel’s Binary Workbook Format 01:39:11
  29. What We Covered 01:41:01
  30. Now It’s Your Turn—But I’m Here If You Need Me! 01:41:58
  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]

    View Full Profile

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 [email protected].

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.

  • Arrange All Command 00:29:05
  • Binary Workbook 00:02:12
  • Cell 00:17:42, 00:37:53, 00:54:44, 01:26:52, 01:35:39
  • Column 00:00:36, 00:32:48, 00:34:05, 00:38:36, 00:52:32, 01:12:04
  • Custom View 00:01:47, 01:16:46, 01:25:50, 01:28:48
  • Dialog Box 00:29:29, 00:47:37, 00:58:29, 01:35:43
  • Format Painter 00:18:12
  • Formula Bar 00:44:00, 00:48:56
  • Freeze Panes 00:52:44, 00:56:06, 00:58:39, 01:01:53
  • Full-Screen View Command 00:23:54, 00:47:14
  • Group Worksheets 01:15(30)
  • Hyperlink 00:15:32
  • Keyboard Shortcut 00:05:58, 00:08:52, 00:36:46, 00:48:41, 00:53:37, 00:53:58, 01:18:52
  • Macro 01:22:26, 01:39:(42)
  • Microsoft 365 00:02:24
  • Navigation Task Pane 00:03:45, 00:04:12, 00:09:37, 00:10:24
  • Power Query 01:26:05
  • Quick Access Toolbar 00:46:20, 00:48:20, 00:53:10, 01:22:48, 01:30:28
  • Ribbon 00:16:09, 00:43:56, 00:53:24
  • Row 00:34:46, 00:39:08, 00:52:32, 01:01:36, 01:12:45
  • Scroll Bar 00:08:03:, 00:23:38, 00:34:49, 00:37:12
  • Split Command 01:01:(52)
  • Split Feature 01:00:58
  • Spreadsheet 00:00:07, 00:04:52, 00:15:47, 00:22:57, 00:54:34
  • Total Row 01:01:39
  • Watch Window 01:35:17
  • Workbooks 00:01:56, 00:09:47, 00:18:13, 00:24:33, 01:16:48, 01:28:27, 01:35:26
  • Worksheet 00:00:35, 00:05;21, 00:11:07, 00:33:16, 00:52:28, 01:01:25, 01:12:16, 01:15:39, 01:27:04
  • Worksheet Tab 00:00:45
  • Zoom to Selection 00:33:47

Arrange All Command: The Arrange All command provides you with a number of options for arranging multiple workbooks on screen simultaneously, depending on your particular needs. You can access the Arrange All command by selecting VIEW?Arrange All.

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.

Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

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

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.

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.

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 (or link) is a clickable element—text, image, or button—on a webpage that directs users to another location, such as a new web page, a specific spot on the same page, or a file. They enable web navigation, enhance user experience by providing more information, and are crucial for SEO.

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.

Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.

Navigation Task Pane: The Navigation pane in Excel is an easy way to understand a workbook's layout, see what elements exist within the workbook, and navigate directly to those elements. Find and access elements such as tables, charts, PivotTables, and images within your workbook. Once you've opened the Navigation pane, it displays on the right side of the Excel window.The Navigation pane also makes it easier for those with visual impairments to access all parts of the workbook. It can improve how tools such as screen readers interpret your workbook.

Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.

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

Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

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.

Split Command: The split command takes the place of frozen panes, just as Freeze Panes replaces an existing split

Split Feature: The Split feature in Excel (View > Split) divides the worksheet window into separate, independently scrollable panes, allowing you to view and compare different, non-adjacent areas of the same, large dataset at once. It is ideal for comparing data in rows or columns that are far apart without constantly scrolling.

Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.

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.

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.

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

Zoom to Selection: You can zoom to the current selected range of cells with a single click of the Zoom to Selection icon.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Frequently Asked Questions

Excel's Group and Outline feature allows you to organize large spreadsheets into collapsible sections, making it easy to show or hide detail rows while keeping summary rows visible. To group rows, select the rows you want to collapse, go to Data > Group > Group, and Excel adds a bracket on the left side with a minus (−) button to collapse and a plus (+) button to expand. Multiple group levels can be nested—for example, grouping individual months under a quarter, and quarters under a year. The numbered buttons (1, 2, 3) in the top-left corner of the grid collapse all groups to the corresponding level simultaneously. For financial statements with supporting detail—income statement line items with underlying account details below each line—grouping provides a professional expand/collapse capability without VBA. Auto Outline (Data > Group > Auto Outline) attempts to detect groups automatically based on summary rows created with SUM formulas. To remove grouping, select the grouped rows or columns and use Data > Ungroup. Grouping differs from hiding rows because it provides visible expand/collapse controls that users can interact with easily. For workbooks with 1,000+ rows organized into logical sections, grouping dramatically improves navigation and presentation—letting users focus on the level of detail relevant to their task.
Hyperlinks within Excel workbooks provide instant one-click navigation to specific sheets, cells, or ranges—essential for large workbooks where scrolling through many sheets is inefficient. To insert a hyperlink, right-click a cell and select Link (or press Ctrl+K), then choose Place in This Document. You can link to a specific cell reference on any sheet, or to a Named Range—making named ranges doubly useful as both formula references and navigation targets. Descriptive link text like 'Go to Revenue Summary' or 'Return to Table of Contents' guides users clearly. Creating a dedicated Table of Contents sheet with hyperlinks to each section of a complex workbook dramatically improves usability for all users of the file. Hyperlinks to external files, URLs, or email addresses are also supported through the same dialog. For keyboard-only navigation, pressing Ctrl+K on a selected cell opens the insert hyperlink dialog. To follow a hyperlink, click it; to select the cell without following the link, use the arrow keys to navigate to it or Ctrl+click. To remove a hyperlink, right-click and select Remove Hyperlink. For workbooks distributed to management or external stakeholders who may not be familiar with the file structure, a linked Table of Contents transforms a complex multi-sheet workbook into a navigable, user-friendly document.
Excel provides two features for viewing multiple parts of a spreadsheet simultaneously. Split Window (View > Split) divides the current sheet into two or four independently scrollable panes, each showing a different area of the same worksheet. You can drag the split bar to resize the panes. This is ideal for comparing data at two distant locations—such as reviewing row 1 headers against row 1,000 data, or comparing totals at the bottom with inputs at the top. New Window (View > New Window) opens a second instance of the same workbook in a separate window. Using View > Arrange All lets you tile multiple windows side by side. This approach is better when you need to compare data between different sheets in the same file. For workbooks with linked sheets where you want to verify that a formula in the summary sheet is pulling the correct value from a detail sheet, New Window with side-by-side arrangement is particularly useful. On dual-monitor setups, dragging each window to a separate monitor provides the maximum viewing area for both. The View Side by Side option (View > View Side by Side) synchronizes scrolling between two windows—useful when comparing two similar reports side by side and wanting both to scroll in unison. These tools eliminate the need to constantly Alt+Tab between windows or sheets when working with large or multi-sheet workbooks.
Workbooks with many sheets become difficult to navigate as sheet tabs fill the tab bar. Several techniques help manage this. Right-clicking the sheet navigation arrows (the small left/right arrows to the left of the tab bar) displays a full list of all sheet names as a clickable menu—the fastest way to jump to any sheet in a workbook with 20+ tabs. Color-coding tabs by category (right-click tab > Tab Color) helps visually group related sheets. Renaming all sheets with meaningful names is the most impactful organizational step. Hiding sheets that contain reference data or calculations not needed for daily use (right-click > Hide) reduces clutter; to unhide, right-click any visible tab and select Unhide. For large workbooks with logically distinct sections, creating a Table of Contents sheet with hyperlinks to each section (or key sheets) provides the most professional navigation experience. In Excel 365, the Navigation Pane (View > Navigation) lists all sheets, tables, charts, and named ranges in a searchable panel—providing the most comprehensive navigation tool available. For programmatic navigation in a VBA context, storing sheet names as constants or in a reference table prevents breaking macros when sheets are renamed. These organizational practices collectively determine whether a large workbook is manageable or a navigation nightmare for daily users.
Excel Themes control the coordinated set of fonts, colors, and effects applied across an entire workbook—ensuring all charts, tables, and formatted cells use a harmonious visual palette. To apply a theme, go to Page Layout > Themes and select from the gallery of built-in themes, or browse for custom themes. The Fonts and Colors dropdowns within the Themes group let you change just the typography or color palette while keeping the rest of the theme intact. Creating a custom color theme (Page Layout > Colors > Customize Colors) defines your organization's brand colors as the Accent1 through Accent6 palette—these become the colors used by default in charts, cell fills, and Table styles throughout the workbook. Custom themes can be saved as .thmx files and shared across the organization to ensure all Excel files use consistent branding. Applying a theme after the fact updates all theme-dependent formatting instantly—a significant advantage over manually reformatting colors. For workbooks distributed externally to clients or presented in board meetings, applying a professional theme takes seconds and elevates the visual quality without any design expertise. Combined with Cell Styles that use theme colors, consistent formatting becomes maintainable and updatable across all related workbooks by simply reapplying the shared theme file.