Spreadsheet Skyscrapers vs. Spreadsheet Sprawl

Author: David H. Ringstrom

In this presentation Excel expert David H. Ringstrom, CPA, shows you how to overcome spreadsheet sprawl by orienting your data into skyscrapers instead. Invariably spreadsheet users tend to orient data going across columns, but doing so inadvertently hijacks your experience in Excel and makes it harder to analyze data. You'll see how to reorient existing spreadsheet, such as by using the Unpivot feature in Power Query to reorganize your data vertically versus horizontally, which then unlocks a host of Excel features you can use to work more effectively.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 and earlier) 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.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

Publication Date: June 2022

Designed For
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Adding rows to a blank pivot table to create instant reports
• Creating a pivot table to transform lists of data into on-screen reports
• Filtering lists based on cell color
• Gaining control of long lists of data by filtering instead of sorting
• Inserting totals into lists with a few mouse clicks by way of Excel's SUBTOTAL function
• Repositioning or removing subtotals within pivot tables
• Streamlining the filtering of lists in Excel 2013 and later by using the Slicer feature with tables
• Understanding the limitations and frustrations of manually hiding and unhiding worksheets within a workbook

Learning Objectives

• Identify which menu the Unpivot Columns command appears on in Power Query
• Recognize how to recall the locations where the Filter command appears in Excel's menu interface
• Identify the items that can be added to a PivotTable from the PivotTable field list
• Describe which ribbon tab the WorkBook Statistics command appears on
• Identify which key allows you to group a series of adjacent worksheets at once
• Recognize how many arguments HYPERLINK has
• Identify which feature in Power Query enables you to combine two or more columns together
• Recognize which command allows you to extract data from an Excel worksheet directly into the Power Query editor

Level
Basic

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
None

Advance Preparation
None