Excel Agility: Disaster-Proofing Spreadsheets
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Taxation and Accounting, Finance, Business Skills, Software
- Credit: CPE 2.0, ATAOP 1.5, ATAAA 1.5, ATAPU 1.5, ATATX 1.5
-
Topics typically covered:
- Learning the mouse trick that lets you quickly make a copy of an existing worksheet.
- Improving the stability of Excel by deleting accumulations of temporary files in Windows.
- Protecting a worksheet to ensure users are limited to changing only specific areas of a worksheet.
- Tweaking Excel’s Auto Recover settings to raise the odds of recovering your work after an Excel crash.
- Protecting sensitive information by marking key worksheets as Very Hidden.
- Filtering a cleaned-up accounts receivable aging report to display only overdue amounts.
- Recovering previous copies of Excel files from Google Drive.
- Managing the external data security warning that may appear when you link external data into Excel spreadsheets.
- Accessing files closed without saving as well as interim back-ups Excel makes while you're working in a workbook.
- Protecting hidden sheets from within a workbook.
- Exploring Excel’s Scenario Manager feature that enables you to store various sets of inputs, such as best case, worst case, and most likely, without having to replicate worksheets or workbooks.
- Enabling a workbook-specific setting that will create an automatic back-up of critical workbooks.
- Recall the section of the Excel Options dialog box where the Auto Recover setting resides that controls how often Excel creates a back-up copy of your workbooks.
- Recall the location of the Always Create Backup option.
- Recall how to duplicate worksheets more efficiently by holding down a key while dragging the worksheet tab.
- Introduction
- Excel Versions 00:04:09
- AutoRecover Settings 00:05:02
- AutoRecover Spreadsheets 00:08:24
- Automatic Backup of Key Excel Workbooks 00:14:51
- AutoSave Feature (Microsoft 365) 00:22:46
- Other Ways to Create Fall-Back Positions 00:27:56
- Password Protecting Macros 00:29:14
- Password Protect the Workbook 00:37:46
- Recovering Lost Passwords 00:40:50
- Mouse Trick to Duplicate Worksheets 00:45:52
- Scenario Manager Feature - Steps 1- 8 00:49:01
- Scenario Manager Feature - Steps 9 - 13 00:54:27
- Scenario Manager Feature - Steps 14 - 16 00:54:28
- Clean A/R Aging with Power Query Step 1 00:56:14
- Clean A/R Aging with Power Query - Steps 1- 8 00:59:42
- Clean A/R Aging with Power Query - Steps 9 - 18 01:03:59
- Clean A/R Aging with Power Query - Steps 19 - 27
- External Data Security Warning 01:20:29
- Protecting a Worksheet 01:23:59
- Protecting a Workbook 01:29:25
- Repairing Damaged Workbooks 01:32:33
- Delete Temporary Files 01:35:40
- Undo/Redo Multiple Steps at Once 01:39:26
- Document Recovery 01:40:47
- Thank you for attending! 01:42:46
- .XLK 00:16:19
- .XLSX 00:17:06
- AutoRecover 00:05:37, 00:10:15
- AutoSave 00:22:23, 00:24:35
- Cell 00:52:12, 01:24:16
- Column 00:57:40, 01:08:56
- Dialog Box 00:15:56, 01:15:07
- Filter 01:18:02
- Format Painter 00:43:26
- Formula 00:57:07
- Formula Bar 01:39:57
- Macro 00:29:39, 00:34:16
- Microsoft 365 00:01:50, 00:04:35
- Microsoft OneDrive 00:21:20, 00:23:27
- Microsoft SharePoint 00:22:36
- Power Query 00:46:20, 01:04:36, 01:06:06, 01:18:35
- Power Query Editor 01:03:54
- Protect Workbook 01:29:23
- Query 01:18:27
- Quick Access Toolbar 01:26:58
- Redo Command 01:39:37
- Row 01:06:26
- Scenario Manager 00:49:10, 00:51:36, 00:57:08
- Spreadsheet 00:01:02, 00:15:41, 01:32:51
- Undo Command 01:39:36
- VBA - Visual Basic for Applications 00:32:20, 00:35:55
- What-If Analysis 00:52:21
- Workbook 00:08:42, 00:18:34, 00:24:24, 00:29:32, 00:33:36, 00:46:11, 00:56:39, 01:01:17
- Worksheet 00:09:48, 00:29:47, 00:50:11, 01:19:35, 01:24:17
.XLK: This file extension connotes an Excel Backup Workbook generated by the Always Create Backup setting for a given workbook. This setting must be enabled on an individual workbook basis. Such files cannot be opened in Excel for Mac unless you change the file extension to XLS or XLSX.
.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.
AutoRecover: The Auto-Recover feature saves copies of all open Excel files at a user-definable fixed interval. The files can be recovered if Excel closes unexpectedly, for example, during a power failure.
AutoSave: Excel AutoSave is a tool that automatically saves a new document that you've just created, but haven't saved yet. It helps you not to lose important data in case of a computer crash or power failure.
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.
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 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.
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.
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.
Microsoft OneDrive: Microsoft OneDrive is a file-hosting service operated by Microsoft. First released in August 2007, it allows registered users to store, share and sync their files. OneDrive also works as the storage backend of the web version of Microsoft 365 / Office.
Microsoft SharePoint : SharePoint is a web-based collaborative platform that integrates natively with Microsoft 365. Launched in 2001,[6] SharePoint is primarily sold as a document management and storage system, although it is also used for sharing information through an intranet, implementing internal applications, and for implementing business processes.
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.
Protect Workbook: To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets, you can protect the structure of your Excel workbook with a password.
Query: A database query extracts data from a database and formats it in a readable form. A query must be written in the language the database requires; usually, that language is Structured Query Language (SQL). For example, when you want data from a database, you use a query to request that specific information.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
Redo Command: Like the undo action, redo can be performed multiple times by using the same keyboard shortcut over and over. The Excel Ribbon also has a redo button right next to the undo button; it is represented by an icon with an arrow pointing to the right. After using the Undo button on the Quick Access toolbar, Excel 2010 activates the Redo button to its immediate right. If you delete an entry from a cell and then click the Undo button or press Ctrl+Z, the ScreenTip that appears when you position the mouse pointer over the Redo button appears as Redo Clear (Ctrl+Y).
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.
Scenario Manager: The Scenario Manager feature allows you to create scenarios that store up to 32 inputs. You can then swap out sets of inputs on a worksheet by applying a scenario or creating reports that compare the output of scenarios. If you have more than 32 inputs that you wish to save, you can create and then apply two or more scenarios sequentially.
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.
VBA - Visual Basic for Applications : Visual Basic for Applications is a computer programming language developed and owned by Microsoft. With VBA you can create macros to automate repetitive word- and data-processing functions, and generate custom forms, graphs, and reports. VBA functions within MS Office applications; it is not a stand-alone product.
What-If Analysis: What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of What-If Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables. Scenarios and Data tables take sets of input values and determine possible results.
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.
