In this practical webinar, Excel expert, David Ringstrom, CPA, demonstrates how you can create financial statements for any month of the year on just one worksheet. In order for you to accomplish this, you will learn how to automate the process by implementing a variety of Excel functions and features, improve the integrity of your spreadsheets, incorporate Check Figures and Alarms into your work, and much more.
You will see each technique demonstrated at least twice: first, on a PowerPoint slide with
numbered steps, and second, in Excel 2019. You will identify differences in Excel 2016, 2013, or
2010 and will receive detailed handouts. You will also receive an Excel workbook that includes
the examples presented during the webinar.
You will learn how to:
- Use the SUMIFS function to sum values for a single account or a range of account numbers
- Explore the nuances of data exported from accounting programs, such as extraneous worksheets, blank columns, and extraneous rows
- Extract data dynamically for a given month or year to date by way of the OFFSET function
- Modify queries that return data from other sources into an Excel worksheet
- Create self-updating links to databases, spreadsheets, text files, and other data sources using Microsoft Query
- Create an in-cell list by way of Excel’s Data Validation feature
- Use the Query Wizard within Microsoft Query to choose the relevant columns from your accounting report export
- Use the MONTH function to return the month portion of a date or month name
- Use the Trusted Document feature in Excel 2010 and later to suppress the Data Connection security prompt
- Return data to an Excel spreadsheet from Microsoft Query
Upon course completion, you will be able to:
- State the location of the Conditional Formatting command within Excel’s menus
- State what object type Microsoft Query considers worksheet tabs to be within Excel workbooks
- Identify the purpose of the MONTH function in Excel
Suggested Audience: Practitioners who can benefit from learning how to create monthly financial statements more effectively in Excel.
Prerequisites: None. Experience with Excel Spreadsheets Recommended.
Advance Preparation: None
Program Level: Intermediate
Field of Study: Computer & Software Applications (2 hours)
Format: Live Webcast
Credit Type: Group Internet Based for the Live Program. [If you attend the program on-demand (after the live date), you will receive Self-Study credit. Please note that to receive Self-Study credit you will need to complete a final exam after viewing the webinar.]