On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: CPE 2.0, ATAHR 1.5, ATAAA 1.5, ATAOP 1.5
-
In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide participants through various payroll-related Excel techniques. Topics covered include contrasting using Flash Fill versus the TEXT function to reformat Social Security Numbers. You'll see how to calculate total payroll and total payroll taxes with the SUMPRODUCT function for data analysis, and understand the nuance of adding up time values in Excel. David will also show how to calculate employee tenure with the DATEDIF function, optimize work schedules with the NETWORKDAYS.INTL function, and applying heat mapping techniques to salary data. He'll also contrast using VLOOKUP in any version of Excel versus XLOOKUP in Excel 2021 and Excel for Microsoft 365 for looking up data from lists. Attendees will gain valuable insights and skills to enhance their Excel proficiency and efficiency.
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.
Who should attend:
Professionals seeking to use Microsoft Excel more effectively.
- Utilizing the RANDBETWEEN worksheet function to create a series of random numbers.
- Gleaning the nuances of adding time values together in Microsoft Excel.
- Improving the integrity of Excel PivotTables with the Table feature.
- Discovering four different ways to remove data from a PivotTable report.
- Adding rows to a blank PivotTable to create instant reports.
- Using Flash Fill to quickly insert dashes into a column of Social Security or telephone numbers.
- Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting.
- Preventing errors from the start by choosing from thousands of free Excel spreadsheet templates.
- Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
- Removing Conditional Formatting when it’s no longer needed within a spreadsheet.
- Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates.
- Determining how to calculate the last day of the current month, as well as future or prior months, with the EOMONTH function.
- State which XLOOKUP argument causes an alternate value to be displayed in lieu of #N/A.
- Recall the print scale threshold below which a print-out typically becomes difficult to read.
- Name the menu tab that contains the Remove Duplicates feature
- Introduction
- Please Ask Questions Today 00:02:36
- Excel Versions 00:04:42
- Flash Fill SSNs 00:06:16
- TEXT Function for Social Security Numbers 0014:21
- SUMPRODUCT Function 00:18:35
- Adding Time Values 00:23:13
- Using DATEDIF to Calculate Tenure 00:30:00
- NETWORKDAYS.INTL Function 00:41:06
- EOMONTH Function 00:45:10
- Semi-Monthly Series of Dates 01:
- Calculating Preceding Friday 00:
- Heat Mapping Salaries 00:53:31
- Remove Conditional Formatting 01:00:52
- Conditional Formatting - Top 10 01:02:10
- Conditional Formatting - Equal To 01:05:07
- Payroll Budget Example - Steps 1 - 12 01:10:37
- Payroll Budget Example - Steps 13 - 24 01:16:23
- Initiating a Pivot Table 01:28:24
- Adding Fields to a Pivot Table 01:32:28
- Pivot Table Drill-Down 01:32:30
- Adding Another Row Field 01:33:58
- PivotTable Number Formatting 01:34:11
- Adding Columns to PivotTables 01:36:52
- 4 Ways to Remove Fields 01:37:50
- RANDBETWEEN Function 01:37:59
- Choosing Random Sets of Employees 01:39:56
- Free Payroll-Related Templates 01:41:59
- Presenter Closing 01:44:14
- Presentation Closing 01:44:30
- Artificial Intelligence (AI) 00:06:20
- AutoSum 00:25:36
- Cell 00:16:44, 00:19:15, 00:24:44, 01:01:16, 01:08:00
- Column 00:09:53, 00:12:24, 01:15:12, 01:35:51
- Concatenation 00:18:19
- Conditional Formatting 00:58:08, 01:05:23
- DATEDIF 00:30:40, 00:39:19
- Dialog Box 00:16:51, 01:05:31
- Drill Down 01:32:30
- EOMONTH Function 00:45:13
- Field 01:37:17
- Flash Fill 00:06:22, 00:07:19, 00:12:08
- Format 00:16:56, 00:54:19
- Formula 00:18:46, 01:12:13
- Heat Mapping 00:53:31
- Macro 00:24:18, 00:27:30, 01:34:20
- Macro Recorder 00:24:23, 00:27:31, 01:34:14
- Microsoft 365 00:05:09
- NETWORKDAYS.INTL 00:41:10
- Pivot Table 01:28:32
- RANDBETWEEEN 01:38:26
- Row 01:36:58
- SUM 00:20:17, 00:23:41, 00:29:50
- SUMPRODUCT 00:18:35, 00:20:31
- Table 01:11:17, 01:29:38
- TEXT Function 00:14:23
- Top 10 Filter 01:02:10
- Undo Command 00:10:40
Artificial Intelligence (AI): Artificial intelligence is intelligence demonstrated by machines, as opposed to the natural intelligence displayed by humans or animals.
AutoSum: The AutoSum feature appears on both the Home menu and the Formulas menu as a Greek sigma symbol. When you click AutoSum, or press Alt-= Excel adds a sum function to the current cell or cells that you've selected.
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.
Concatenation: A technique that allows you to join two or more pieces of text together. Although its simplest to use the ampersand (&), you can also use the CONCATENATE function in Excel.
Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.
DATEDIF: A worksheet function in Excel that works in any version of Excel but that mysteriously doesn't appear in Excel's online help documentation. DATEDIF has three arguments: Date1, Date2, and Interval. Keep in mind that DATEDIF does not count the starting period, so you may need to add 1 to its result.
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.
Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.
EOMONTH: The Microsoft Excel EOMONTH function calculates the last day of the month after adding a specified number of months to a date. The result is returned as a serial date. The EOMONTH function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel.
Field: In a PivotTable or PivotChart, a category of data that is derived from a field in the source data. PivotTables have row, column, page, and data fields. PivotCharts have series, category, page, and data fields.
Flash Fill: Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note: Flash Fill is only available in Excel 2013 and later.
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.
Formula: A formula is an expression which calculates the value of a cell.
Heat Mapping: To create a heat map in Excel, simply use conditional formatting. A heat map is a graphical representation of data where individual values are represented as colors.
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.
NETWORKDAYS.INTL: The Microsoft Excel NETWORKDAYS.INTL function returns the number of work days between 2 dates, excluding weekends and holidays.The NETWORKDAYS.INTL function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the NETWORKDAYS.INTL function can be entered as part of a formula in a cell of a worksheet.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
RANDBETWEEEN: The Microsoft Excel RANDBETWEEN function returns a random number that is between a bottom and top range. The RANDBETWEEN function returns a new random number each time your spreadsheet recalculates.
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.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet
SUMPRODUCT: The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.
TEXT Function: The TEXT function enables you to convert a number in Excel to any number of text formats. For instance, the format code mmmm d, yyyy would transform the date 1/1/2018 into January 1, 2018.
Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.
Top 10 Filter: Use the Top 10 filter feature in an Excel pivot table, to see the Top or Bottom Items, or find items that make up a specific Percent or items that total a set Sum. You can summarize your data by creating an Excel Pivot Table, and then use Value Filters to focus on the top 10, bottom 10 or a specific portion of the total values in your data.
Undo Command: The Undo feature in Excel 2010 can quickly correct mistakes that you make in a worksheet. The Redo button lets you “undo the Undo.” The Undo button appears next to the Save button on the Quick Access toolbar, and it changes in response to whatever action you just took; the Redo button becomes active whenever you use Undo.
