Excel Agility: Spreadsheet Basics Part 1
This introductory course will prepare you to develop and work with Excel spreadsheets. Expert David Ringstrom, CPA, walks you through the basics, providing the knowledge needed to create functional spreadsheets and manipulate large lists of data. David presents an overview of the different types of files you can create, explains Excel’s rows and columns grid, shows you how to remove duplicates from a list, and more.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.
Who Would Be Interested in This Course:
Practitioners who wish to learn the basics of working with Excel spreadsheets.
Your Benefits of Attending:
- Using a custom number format to hide zero amounts within a specific area of a spreadsheet.
- Getting familiar with Excel’s grid of rows and columns.
- Preventing errors from the start by choosing from thousands of free Excel spreadsheet templates.
- Learning the nuances of copying formulas within Excel spreadsheets.
- Saving paper and eliminating frustration by monitoring the Print Scale setting.
- Handling situations where numbers are presented as # signs on-screen and in Print Preview.
- Discerning the nuance of entering text into worksheet cells.
- Surfacing hidden Excel commands instantly by way of the Tell Me feature in Excel 2016.
- Copying formulas efficiently down one or more columns at the same time.
- Understanding Excel’s Freeze Panes feature.
- Recall some basic data analysis techniques.
- Identify how to skip confusing menus by way of time-saving mouse tricks and keyboard shortcuts.
- Recall how to copy formulas down one or more columns at the same time.
NASBA Field of Study:
Computer Software & Applications (2 hours)
Excel Versions - 00:00:40
Getting Oriented in Excel - 00:03:20
Saving Excel Files - 00:07:27
Working Within a Cell - 00:12:24
Entering Text into Worksheet Cells - 00:15:23
Managing Column Widths - 00:18:01
Percentages - 00:23:34
Numbers - 00:27:27
Worksheet Cells - 00:32:25
Creating a Center Text Shortcut - 00:36:41
Copying Formulas - 00:40:25
Working with Dates - 00:44:43
Calculating Our Loan Payment - 00:49:15
Excel’s Order of Operations - 00:54:01
Use F4 to Toggle Absolute References - 00:56:57
Copying Formulas - 01:02:59
Autosum Feature - 01:07:20
Monitoring Print Scale - 01:11:27
Applying Borders and Colors - 01:15:03
Managing Worksheets - 01:17:40
Duplicating Excel Worksheets - 01:21:15
Using Excel Templates - 01:26:35
Minimize Workbook Clutter - 01:27:59
Worksheet Tab Navigation Tricks - 01:30:04
Manage Error Checking Prompts
Filtering Data - 01:35:13
Remove Duplicates - 01:37:13
Quick Analysis - 01:39:00
Streamlining PDF Creation - 01:40:30
Tell Me Feature - 01:41:13
Absolute References - 00:56:57
Autosum - 01:07:20
Center Text - 00:36:41
Column Widths - 00:18:01
Filtering - 01:35:13
- Percentages - 00:23:34
- Numbers - 00:27:27
- Worksheet Cells - 00:32:25
- Dates - 00:44:43
Formula Copying - 00:40:25, 01:02:59
Order of Operations - 00:54:01
PDF Creation - 01:40:30
Print Scale - 01:11:27
Quick Access Toolbar - 00:40:20
Remove Duplicates - 01:37:13
Save As - 00:07:27
Shortcut - 00:36:41Templates - 01:26:35
.PDF: Portable Document Format, a universal document format created by Adobe that allows cross-platform compatibility of documents.
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.
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.
Error Checking Prompts : Error checking prompts are little green triangles that appear in the left-hand corner of cells where Excel perceives that formulas may have one or more errors. An exclamation mark icon appears when you click on a cell containing such a prompt, which you can click on to display a set of error handling options.
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.
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.
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.
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.
Quick Analysis Feature : A new feature in Excel 2013 that calls often-overlooked features to your attention, such as pivot tables, recommended charts, totals, tables, and sparklines.
Remove Duplicates: This feature first appeared in Excel 2007. This Data tab command allows you to reduce a list of items to a list of unique constituents. This action required the Advanced Filter command in Excel 2003 and earlier.
Scale to Fit: The Scale to Fit section of Excel's Page Layout menu allows you to specify how many pages wide and/or pages tall a printout should fit to. Alternatively you can adjust the Scale factor to shrink the text to a specific percentage. The Scale factor should remain at 64% or above to keep text readable.
Templates: Documents designed to serve as a starting point so that information does not have to be recreated. Templates also make it much harder for users to inadverently save over a master copy when replicating a document or spreadsheet.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively. To learn more about David, you can view his LinkedIn profile and follow him on ... View Full Profile