Spreadsheet Skyscrapers vs. Spreadsheet Sprawl

Level: Basic

Available Durations:

• 50 minutes
• 60 minutes
• 75 minutes
• 90 minutes
• 100 minutes
• 120 minutes

Description:

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 spreadsheets, 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.

Topics/Areas Typically Covered:

• 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.

Learning Objectives/Why You Should Attend:

• State which menu the Unpivot Columns command appears on in Power Query.
• 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.

About the Instructor:

David H. Ringstrom, CPA is the owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. Throughout his career, David has spoken at conferences on Microsoft Excel, and written dozens of freelance articles about spreadsheets. He offers Excel and Access training and consulting services nationwide.

Pricing and Format Options:

Click here to learn about the presentation format and view pricing information.

Click here to view other topics.