Spreadsheet Skyscrapers vs. Spreadsheet Sprawl

Learning Objectives

• Appending data from two or more worksheets into a self-updating consolidated list with Power Query.
• Comparing transposing wide reports versus unpivoting the report.
• Configuring Power Query queries to update automatically in the most efficient manner possible.
• Creating a self-updating list of worksheets in any workbook with Power Query.
• Eliminating total rows after unpivoting data in Power Query.
• Exploring how the INDIRECT worksheet function can automate pulling values from a group of individual worksheets.
• Exploring the Queries & Connections task pane that shows data connections used within a given workbook.
• Graphing the relationship between how difficult spreadsheet tasks become as workbooks gain ever more worksheets and worksheets gain ever more columns.
• Introducing the Power Query feature in Excel.
• Making the same edits on multiple worksheets at once by grouping worksheets.
• Navigating purposefully through worksheets by way of clickable hyperlinks.
• Navigating through workbooks and identifying non-blank worksheet cells with the Navigation task pane in Microsoft 365.

Course Description

In this webinar, David demonstrates how to avoid spreadsheet sprawl by reorienting your data into skyscrapers. Typically, spreadsheet users arrange data horizontally across columns, which can complicate data analysis in Excel. David illustrates how to reorganize existing spreadsheets by utilizing the Unpivot feature in Power Query to orient data vertically instead of horizontally, which opens up a range of Excel features to enhance your productivity.

Excel expert David H. Ringstrom 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 course.

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.

This CPE course on Excel covers the following topics:

• The Price of Spreadsheet Sprawl
• Workbook Statistics Feature (Excel 2016+)
• Hide and Unhide Worksheets
• Group Worksheets
• INDIRECT Function
• INDIRECT with Monthly Worksheets
• Drill Through Worksheets
• Navigation Task Pane (Microsoft 365)
• List of Worksheets With Power Query
• Creating a Workbook Index
• Creating Hyperlinks to the Index
• Unpivot Columns with Power Query
• Cleaning Up Power Query Results

Overview

• The price of spreadsheet sprawl

  5 mins
• Group worksheets

  18 mins
• Indirect with monthly worksheets

  31 mins
• List of worksheets with power query

  46 mins
• Transposing wide reports

  65 mins
• Cleaning up power query results

  85 mins
• Power query properties

  101 mins