course description
in this course, 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 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.
who should attend: practitioner seeking to use microsoft excel more effectively
level: intermediate
topics covered:
- creating a self-updating list of worksheets in any workbook with power query.
- eliminating total rows after unpivoting data in power query.
- exploring the queries & connections task pane that shows data connections used within a given workbook.
- introducing the power query feature in excel.
- 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.
- transforming reports that are dozens or more columns wide into filterable rows by way of the unpivot command in power query.
- using indirect to create a single formula that can retrieve data from any worksheet in a workbook by simply copying and pasting.
learning objectives:
- 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.
review our course policies and procedures page for further information