On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Skills, Software, Human Resources, Finance
- Credit: CPE 2.0, ATATX 1.5, ATAOP 1.5
-
In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide you through various topics to enhance your accounting report automation using Excel. Learn how to clean up your reports using Excel's Macro recorder to filter data efficiently, delete unnecessary rows, convert text to columns, apply number formatting, calculate percentages of sales, and then stop recording. Discover the best practices for saving workbooks containing macros and managing macro security notification prompts. You'll then see why you don't necessarily need to use macros because you can create code-free automation solutions with Power Query. David will show you how to make refreshable connections to reports that you can transform by filtering rows and making other changes, and return the results to Excel, where you can add supplemental formulas as needed. Don't miss out on this opportunity to streamline your accounting reports and save valuable time.
David is the author of “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.
Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.
Topics typically covered:
- Transforming an accounting report by way of Power Query.
- Recording a contact info macro.
- Preserving macros by saving workbooks in the XLSM format versus the default XLSX format.
- Utilizing the Relative References setting for creating Excel macros to be played back on any cell versus specific cells.
- Adding clickable shapes to Excel spreadsheets as a visual means of launching Excel macros.
- Navigate Excel menus entirely by keyboard shortcuts.
- Making sense of the Enable Content prompt related to macros in Excel.
- Refreshing results from Power Query and editing the underlying queries.
- Automating the cleanup of an accounting report in Microsoft Excel with a recorded macro.
- Understanding the nuance of creating a percentage of column in Power Query.
Learning objectives:
- Identify the Excel feature that can be used to create Excel macros without manually writing any programming code.
- Identify the feature that allows you to separate data into multiple columns.
- State which menu the Get Data command appears on in Excel 2019 and later.
- Introduction
- Please Ask Questions Today! 00:02:58
- Excel Versions 00:04:12
- Accounting Report Macro: Clean-Up 00:05:41
- Accounting Report Macro: Filtering 00:19:21
- Accounting Report Macro: Delete Rows 00:27:01
- Accounting Report Macro: Text to Columns 00:33:04
- Accounting Report Macro: Number Format 00:32:40
- Accounting Report Macro: % of Sales 00:38:30
- Accounting Report Macro: Stop Recording 00:42:53
- Creating a Clickable Shape 00:45:40
- Saving Workbooks That Contain Macros 00:52:19
- Macro Security Notification Prompt 00:58:35
- Recording a Simple Contact Info Macro 00:59:39
- Running the Contact Info Macro 01:02:37
- Re-recording with Relative References 01:05:24
- Playing Back the Re-Recorded Macro 01:08:32
- Creating an Icon for Our Macro 01:10:29
- Power Query: Linking to Report 01:15:02
- Power Query: Merge Columns 01:18:20
- Power Query: Filtering Rows 01:20:00
- Power Query: % of Column - Steps 26 - 34 01:26:15
- Power Query: % of Column Steps 35 - 42 01:26:41
- Power Query: Return Results to Excel 01:37:55
- Three Ways to Manually Refresh Queries 01:38:23
- Speaker Closing 01:41:16
- Presentation Closing 01:41:49
- .CSV 00:24:06
- .XLSX 00:24:08
- Cell 00:22:22, 00:26:36, 00:30:38, 00:38:35, 00:43:02, 00:59:56, 01:03:45, 01:08:43
- Column 00:07:56, 00:23:11, 00:32:44, 01:18:24, 01:23:56
- Copy Command 00:21:01
- Dialog Box 00:09:38, 00:10:24
- Filter 00:21:38, 00:23:18, 00:28:37, 01:18:16, 01:20:03
- Format 00:31:33, 00:36:33
- Format Painter 00:38:38
- Formula 00:19:35, 00:21:13, 00:38:53, 01:31:31
- Keyboard Shortcut 00:12:36, 00:18:38, 00:27:10, 00:44:16
- Macro 00:00:06, 00:01:40, 00:05:52, 00:08:39, 00:10:29, 00:30:50, 00:44:06, 00:47:43 00:52:22, 01:02:51, 01:08:52, 01:37:50
- Macro Recorder 00:08:42, 00:12:15, 00:14:22, 00:18:37, 00:21:45, 00:34:04, 00:43:09, 00:59:42, 01:26:20
- Microsoft 365 00:04:16
- Paste Command 00:21:09
- Personal Macro Workbook 00:13:38, 01:00:53, 01:03:59
- Power Query 00:00:06, 00:02:05, 01:15:05, 01:26:27, 01:33:07, 01:38:24
- Power Query Editor 01:38:51
- Refresh 01:38:28
- Relative References 01:05:37, 01:10:07
- Row 00:08:00, 00:17:52, 00:22:06
- Text File 00:06:35, 00:15:16, 00:17:19
- Text to Columns Wizard 00:30:17
- Total Row 00:08:02, 00:26:28
- Workbook 00:11:11, 00:14:15, 00:17:15, 00:43:58, 00:53:08, 01:15:17
.CSV: Comma-Separated Value files are text files where each field of data is separated by a comma. This is an effective means to export data from QuickBooks that you, in turn, wish to analyze in Excel.
.XLSX: A file with the. xlsx file extension is a Microsoft Excel Open XML Spreadsheet (XLSX) file created by Microsoft Excel. You can also open this format in other spreadsheet apps, such as Apple Numbers, Google Docs, and OpenOffice.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Copy Command: If you want to copy the contents of the selected cell, press Ctrl + C. The dotted border around the cell is so-called marching ants. If you see marching ants around a cell, it means that the content has been copied or cut. if you rather use the mouse to copy, to copy the contents of the selected cells to another location, select these cells, hold down the Ctrl key, then move your cursor over their border, so that the cursor will change to an arrow with a plus sign next to it.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
Format Painter: The Format Painter copies formatting from one place and applies it to another. For example, if you have written text in Word, and have it formatted using a specific font type, color, and font size you could copy that formatting to another section of text by using the Format Painter tool.
Formula: A formula is an expression which calculates the value of a cell.
Keyboard Shortcut: A keyboard shortcut is a series of one or several keys that invoke a software program to perform a preprogrammed action. This action may be part of the standard functionality of the operating system or application program, or it may have been written by the user in a scripting language.
Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.
Macro Recorder: A feature in Excel that allows you to transcribe actions you take in Excel into programming code.
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
Paste Command: You can paste the contents into a cell using the Ctrl + V keyboard shortcut.
Personal Macro Workbook: A hidden workbook that typically serves as a repository for macros you wish to always be available in any Excel workbook you have open.
Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.
Power Query Editor: Power BI Desktop also comes with Power Query Editor. Use Power Query Editor to connect to one or many data sources, shape and transform the data to meet your needs, then load that model into Power BI Desktop.
Refresh: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.
Relative References: When you reference a worksheet cell in an Excel formula, this defaults to a relative reference, meaning the column letters and row numbers change automatically when you copy or move the formula. Conversely, unless you turn on the Relative Reference feature, macros you record will capture the absolute address of any cells that you effect during the recording process. Relative References instruct the Macro Recorder to transcribe the number of rows or columns to move to next rather than to a specific cell address.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
Text Files : Raw data files that often have file extensions such as .TXT or .CSV. TXT files are sometimes tab-delimited (meaning each field is separated by a tab character) while CSV files are comma-delimited.
Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
