Description
In financial management, an Excel accountant with time-saving data analysis techniques is key to unlocking actionable insights and maximizing efficiency.
In this webinar, author and Excel expert David H. Ringstrom will bring to your attention numerous features and functions that fly under the radar for many Excel users. The Table feature offers tremendous automation and data integrity improvements in any version of Excel, as do dynamic array functions such as SORT, FILTER, and UNIQUE in Excel 2021 and Microsoft 365. Learn how to update multiple worksheets and corral large workbooks by hiding and unhiding various worksheets at once. You'll also see how to automate repetitive data analysis with Power Query.
Learning Objectives
- To explain the menu in Excel where the Table feature resides.
- To recognize a dynamic array function from a list of worksheet functions.
- To discuss how to use Power Query to automate repetitive analysis.
Area Covered:
- I am creating an in-cell list using Excel's Data Validation feature.
- Creating custom views will enable you to display all worksheets in a workbook at once and hide/display selected worksheets.
- It diagnoses the new #CALC error that can arise from improperly crafted dynamic array formulas.
- I am displaying data subsets dynamically using the new FILTER worksheet function.
- I am filtering a cleaned-up accounts receivable aging report to display only overdue amounts.
- I am learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
- I am learning how to use a single line of programming code to unhide all worksheets within a workbook.
- By grouping worksheets, you are making the same edits on multiple worksheets at once.
- I am managing cumbersome lists of data using the Table feature.
- Managing data security prompts that may appear when you link external data into Excel spreadsheets.
- They are removing duplicates from a list with the new UNIQUE function.
- Removing the Table feature from a worksheet if it's no longer needed.
- We are restoring Full Screen View in Excel 2013 and later and enabling a keyboard shortcut for this feature.
- Sorting lists of data dynamically from elsewhere in a spreadsheet with the new SORT function.
- We are streamlining Custom Views by adding a drop-down list to Excel's Quick Access Toolbar.
- We are streamlining list filtering in Excel 2013 and later using the Slicer feature with tables.
- I am troubleshooting the #SPILL! The error can arise in certain circumstances about dynamic array formulas in Excel 2021 and Microsoft 365.
- Understanding how the Table feature automates formula management within lists is essential.
- It is understanding the limitations and frustrations of manually hiding and unhiding worksheets within a workbook.
- I use Power Query to clean up accounting reports and remove pitfalls like blank rows, merged cells, missing data, and more.
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, etc.
Who Should Attend:-
- Accountants
- CPAs
- CFOs
- Controllers
- Income Tax Preparers
- Enrolled Agents
- Financial Consultants
- IT Professionals
- Auditors
- Human Resource Personnel
- Bookkeepers
- Excel Users
- Marketers
- Government Personnel
David H. Ringstrom, CPA,
