Excel Agility: Disaster-Proofing Spreadsheets

1. Introduction
2. Disaster-Proofing Spreadsheets: Topic Overview 00:00:44
3. Presenting with Microsoft 365 for Windows 00:04:27
4. Section 1: Recovery and Backup Essentials 00:05:53
5. Undo/Redo Multiple Steps at Once 00:08:47
6. AutoRecover Settings 00:13:31
7. Recovering Unsaved or AutoRecovered Workbooks 00:21:21
8. Automatic Backup of Key Excel Workbooks 00:24:56
9. AutoSave Feature (Microsoft 365) 00:30:17
10. Deleting Temporary Files 00:37:31
11. Section 2: Workbook and Worksheet Protection 00:42:15
12. Unlocking Input Cells 00:43:40
13. Adding a Lock/Unlock Cell Toolbar Icon 00:47:21
14. Protecting a Worksheet 00:51:02
15. Protecting a Workbook 00:54:09
16. Password Protecting Workbooks 00:57:41
17. Section 3: Repair and Restore 01:03:06
18. Repairing Damaged Workbooks 01:03:18
19. Recovering Excel Workbooks 01:08:48
20. Section 4: Interactive Tools and External Data 01:13:14
21. Scenario Manager Feature (1/3) 01:15:36
22. Scenario Manager Feature (2/3) 01:20:15
23. Scenario Manager Feature (3/3) 01:23:45
24. Using Power Query to Link to P&L (1/6) 01:24:00
25. Using Power Query to Link to P&L (2/6) 01:34:30
26. Using Power Query to Link to P&L (3/6) 01:36:47
27. Using Power Query to Link to P&L (4/6) 01:39:43
28. Using Power Query to Link to P&L (5/6) 01:41:27
29. Using Power Query to Link to P&L (6/6) 01:41:46
30. External Data Security Warning 01:42:34
31. Time to Practice—Help’s Nearby 01:43:20

• 

  David H. Ringstrom, CPA

  David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]

  View Full Profile

• .CSV 00:31:47
• .XLS 00:31:53
• .XLSB 00:33:09 
• .XLSX 00:32:00
• AutoRecover 00:06:42, 00:16:02, 00:24:44, 01:10:14
• AutoSave 00:07:53, 00:25:07, 00:30:46, 00:34:25
• Binary Workbook 00:32:29
• Cell 00:01:21, 00:09:00, 00:42:46, 00:43:49, 00:47:30, 01:13:37
• Dialog Box 00:25:30, 00:47:16, 00:51:18, 00:57:38, 01:20:14, 01:23:56, 01:36:35
• External Data Connections 01:42:50
• Format 00:26:56
• Formula 00:01:29
• Formula Bar 00:10:13
• HTML 00:32:56
• Keyboard Shortcut 00:48:19
• Microsoft 365 00:04:50, 01:08:40
• Microsoft OneDrive 00:01:05, 00:07:59, 00:25:08, 00:30:19
• Microsoft SharePoint 00:01:06, 00:07:59, 00:25:09
• Open and Repair  01:04:16
• Power Query 00:02:48, 00:03:54, 01:24:01, 01:30:36, 01:36:59
• Quick Access Toolbar 00:47:45
• Redo Command 00:06:10
• Scenario Manager 00:02:17, 01:13:28, 01:15:36
• Spreadsheet 00:00:22, 00:03:47, 00:21:26
• Temporary Files 00:08:07, 00:41:55
• Undo Command 00:06:04, 00:08:42
• Workbook 00:01:11, 00:07:33, 00:22:14, 00:31:49, 00:42:19, 00:54:40, 01:15:06
• Worksheet 00:01:19, 00:42:20, 00:51:08, 01:15:46, 01:20:28

.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.

.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.

.XLS: Spreadsheets compatible with Excel 2003 and earlier have a .XLS extension. Such spreadsheets can be used in Excel 2007 and later, but certain features will be disabled unless you convert the document to a newer format, such as .XLSX, .XLSM, or .XLSB.

.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.

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.

Binary Workbook: 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.

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: 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.

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.

HTML: Hypertext Markup Language is a document format commonly used for Web pages, but you can save Office documents in this format as well.

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.

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.

Open and Repair: This hidden feature enables you to attempt to to repair Excel workbooks that have data corruption. To access the feature click the arrow on the Open button within Excel's Open dialog box that you use to open existing workbooks.

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.

Temporary Files: Files created by the Windows operating system or applications to temporarily store data during tasks such as installations, autosaves, or caching. Typically located in the Temp folder, these files are intended for short-term use and may be safely deleted after their associated processes complete.

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.