On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Software, Taxation and Accounting
- Credit: ATAOP 1.5, ATATX 1.5, ATAAA 1.5, CPE 2.0
-
In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide participants through various essential Excel skills. David will start from the beginning with Excel with topics such as saving Excel files, entering text into worksheet cells, and managing column widths. He'll demonstrate the nuances that can arise when of formatting numbers and worksheet cells, copying formulas and entering dates. David will build a loan amortization table from scratch, which enables him to cover a wide range of Excel topics and features. You'll learn how to calculate loan payments with the PMT worksheet function, master Excel's order of operations, and see how the F4 shortcut key can toggle cell addresses in formulas between absolute, mixed, and relative references. Other techniques include the AutoSum feature and making use of the Freeze Panes feature. Attendees will gain practical knowledge and tips 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.
- Introduction
- Please Ask Questions Today 00:02:45
- Excel Versions 00:05:06, 00:05:31
- Getting Oriented in Excel 00:06:53
- Saving Excel Files 00:15:16
- Working Within a Cell 00:20:42
- Entering Text Into Worksheet Cells 00:24:44
- Managing Column Widths 00:31:30
- Formatting Percentages 00:40:47
- Formatting Numbers 00:44:42
- Formatting Worksheet Cells 00:53:31
- Copying Formulas 00:58:45
- Entering Dates 01:02:22
- Calculating Our Loan Payment 01:10:01
- Excel’s Order of Operations 01:14:39
- Use F4 to Toggle Absolute References 01:20:12
- Copying Formulas 01:24:43
- Verifying Our Work 01:30:20
- AutoSum Feature 01:32:41
- Freeze Panes Feature 01:33:32
- Print Headers/Footers 01:35:08
- Monitoring Print Scale 01:37:47
- Applying Borders and Color 01:41:49
- Instant Amortization Schedule 01:42:23
- Using Excel Templates 01:43:19
- Search (Tell Me) Feature (Excel 2016+) 01:44:19
- Thanks For Attending! 01:46:47
- Presentation Closing 01:47:09
- .XLSB 00:19:53
- .XLSM 00:17:42
- .XLSX 00:17:35
- Absolute References 01:21:28, 01:28:45
- Accounting Format 00:45:18, 00:48:00
- Artificial Intelligence (AI) 00:38:57, 01:11:16
- AutoFill 01:27:19
- AutoSum 01:32:41
- Cell 00:07:40, 00:10:08, 00:12:51, 00:20:52, 00:25:48, 00:29:56, 00:42:06, 00:45:45, 01:03:33, 01:27:09, 01:29:00
- Cell Reference 00:59:21, 01:20:27, 01:21:02
- ChatGPT 00:39:01, 00:48:56
- Column 00:07:26, 00:08:48, 00:31:34, 00:37:37, 01:20:53
- Column Headings 00:53:49, 01:35:39
- Currency Format 00:45:24, 00:48:12
- Dialog Box 01:35:19
- Fill Handle 00:59:53, 01:05:13, 01:27:12, 01:29:06
- Format 00:20:06, 00:32:39, 00:43:44, 00:45:56, 01:03:56
- Formula 00:09:18, 01:10:33, 01:14:00
- Formula Bar 00:09:05, 00:21:40, 00:25:24, 00:30:11, 00:59:40, 01:00:46
- Freeze Panes 01:33:32
- Keyboard Shortcut 00:09:40, 00:17:24, 00:33:04, 00:564:17
- Macro 00:17:43,00:18:23
- Microsoft 365 00:05:04, 00:37:20, 01:14:09
- Name Box 00:08:05, 00:11:29
- Number Formatting 00:40:37, 00:44:52, 01:20:24
- Order of Operations 01:14:43
- Page Layout Menu 01:35:15, 01:36:04
- PDF 00:18:27
- PMT Function 01:11:31
- Print Scale 01:37:47
- Relative Reference 01:21:14
- Row 00:07:25, 00:08:48, 00:50:27, 01:34:46
- Spreadsheet 00:02:07, 00:07:20, 00:15:20, 00:43:03
- Undo Command 00:25:43, 00:33:00, 00:54:59
- Workbook 00:07:11, 00:27:03, 01:40:32
- Worksheet 00:08:09, 00:09:57, 00:14:35, 00:20:49, 00:34:12, 01:40:35
.XLSB: A file with the XLSB file extension is an Excel Binary Workbook file. They store information in binary format instead of XML like with most other Excel files (like XLSX). Since XLSB files are binary, they can be read from and written to much faster, making them extremely useful for very large spreadsheets.
.XLSM : The .XLSM file extension signifies a Macro-Enabled Excel Workbook. Such workbooks may contain programming code that can automate repetitive tasks in Excel. If prompted, do not enable macros in .XLSM workbooks of unknown provenance because viruses and malware are sometimes transmitted by tricking users into opening such workbooks.
.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.
Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.
Accounting Format: Like the Currency format, the Accounting format provides options for decimal places and a currency symbol, and it automatically uses a comma to separate thousands. Unlike Currency, there are no options for negative numbers. The Accounting format places parentheses around all negative numbers by default.
Artificial Intelligence (AI): Artificial intelligence is intelligence demonstrated by machines, as opposed to the natural intelligence displayed by humans or animals.
AutoFill: Use the Auto Fill feature to fill cells with data that follows a pattern or are based on data in other cells.
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.
Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed
ChatGPT: ChatGPT, which stands for Chat Generative Pre-trained Transformer, is a large language model-based chatbot developed by OpenAI and launched on November 30, 2022, notable for enabling users to refine and steer a conversation towards a desired length, format, style, level of detail, and language used.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Column Headings : The column heading or column header is the gray-colored row containing the letters (A, B, C, etc.) used to identify each column in the worksheet. The column header is located above row 1 in the worksheet.
Currency Format: The Currency format places the dollar sign right next to the number.
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.
Fill Handle: The little notch in the bottom right-hand corner of the selected cell or block of cells. You can drag the fill handle to copy the contents to other cells, double-click to copy contents down a column, or right-drag to reveal a hidden context 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.
Formula: A formula is an expression which calculates the value of a cell.
Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.
Freeze Panes: This command on the View tab of Excel’s ribbon interface, or the Window menu in Excel 2003 and earlier, allows you to ensure that one or more rows and/or columns always remain on-screen as you scroll down through a worksheet.
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.
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.
Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.
Number Formatting: Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. To apply a number format, just select one or more cells and choose a format.
Order of Operations : The sequence with which Excel carries out arithmetic operations. Unless superseded by enclosing portions of a calculation in parentheses, Excel first divides, then multiplies, then adds, and finally subtracts.
PDF: Portable Document Format, a universal document format created by Adobe that allows cross-platform compatibility of documents.
PMT Function: The PMT function enables you to calculate a loan payment based on providing an interest rate, period of the loan, and amount to be borrowed or lent. The interest rate must be on the same footing as the term of the loan, so if the loan period is expressed in months, be sure to divide the interest rate by 12.
Page Layout Menu: The Page Layout Tab holds all the options that allow you to arrange your document pages just the way you want them. You can set margins, apply themes, control of page orientation and size, add sections and line breaks, display line numbers, and set paragraph indentation and lines.
Print Scale : A measure in Excel that shows how much a printed page has been reduced in size. The Scale command appears on the Page Layout tab in Excel 2007 and later. The average person will find documents printed in a scale of 63% or less to be frustrating to read.
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.
Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.
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.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
