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 the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation, as well as in his detailed handouts. You will also receive an Excel workbook that includes most of the examples used during the webcast.
Office 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 2019, Excel 2016, and so on.
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: Self-Study credit (Please note that to receive Self-Study credit you will need to complete a final exam after viewing the webinar.)
50% of attendees recommend this webinar
3 out of 5 stars
from 2 customer reviews
3 out of 5
Terry B.
3 out of 5
John S.