Learn how to manage workbooks by saving a workbook as a template and by using the Excel Options available in Excel 2016. Author Jen McBee demonstrates how to create Excel templates and control how formulas are calculated, enable error checking rules, ena
- [Instructor] In Excel 2016, there are many options you can use to customize Excel. One of the options you have available to you is to save a workbook in a different file format. During the Excel 2016 MOS Expert exam, you may be asked to save a workbook as a template, so I'll demonstrate the steps to go through to save it as a template, and show you where you can then find that template if you want to reuse the document. We'll also look at the formula options, and talk about how you can configure calculation settings, and also error checking rules.
I'll demonstrate how to customize the ribbon by adding the Developer tab, and we'll look at the Trust Center, so that we can talk about the different ways you can have Excel handle documents with macros. I have Excel open, but let's go ahead and open the exercise file together. I'll click on Open, Browse. Browse back to my Desktop to the Exercise Files. Go into Chapter 2, here's my Manage document. This document is a way for me to track travel expenses for my employees, and I'm ready to start entering the information for November, but before I do, I want to save this as a document template.
I'll go to File and choose Save As. Browse back to my Chapter 2 folder. Change the Save as type to an Excel Template. Notice how it takes me to a Custom Office Templates folder. I'll go ahead and change the name to Travel Expenses, and click Save. Now, if I want to create a new document from this template, I can go to File, click New. Click on the Personal link, and here's my Travel Expenses document.
Quick and easy. Let's go ahead and now talk about the options you have available where you can customize Excel. We'll go to File, down to Options, and we're going to focus on the Formulas tab. Calculation options are right at the top of the window. By default, all of your calculations are set to automatically calculate, but during the exam, you may be asked to change the workbook calculation to either Automatic except for data tables, or to Manual.
Notice that if you select Manual, you have a sub-option to recalculate the workbook before saving. So pay close attention to the instructions during the exam. If you're asked to change the calculation options to Manual, but not to recalculate the workbook before saving, you'll want to remove the checkmark. If you're asked to go ahead and configure Excel to recalculate the workbook before saving, you can go ahead and leave that checkmark in there. Another area that you might be tested on are the error checking rules, and these rules determine whether or not an error icon pops up if there happens to be an error in that particular cell.
So make sure you're comfortable with all of the error checking rules and familiar with what is in this group. Next, let's look at Customize the Ribbon. We're currently seeing all of the tabs that are displayed in my document. I do have the Developer tab turned on, and just by removing the checkmark, I can hide or display any of my tabs. So on the exam, if you're asked to display the Developer tab, you'll want to come in to Customize the Ribbon, make sure that it has a checkmark next to it and click OK.
Now this is a global setting, so once you turn it on, it's going to remain turned on. During the live exam, you're in a controlled environment, so even if you're in a project that has the Developer tab showing, when you move on to another project, you might see that the Developer tab has been turned off, so there's a difference in what you'll see while you're working on your own computer versus what you may see during the exam. I'm going to go ahead and go down to the Trust Center.
This is where we can tell Excel how we want it to treat documents that have macros embedded. I'm going to click on Trust Center, go to Macro Settings, and these are your four options. So on the exam, you may be asked to disable all macros without notification, disable all macros with notification, disable all macros except digitally signed macros, and enable all macros which of course is not recommended. So just remember where these are, and you'll be all set to take on that task regarding the Trust Center Macro Settings in Excel.
The course begins with an overview of the certification program and its costs. Next, Jennifer walks you through all of the certification objectives, including hands-on experience with downloadable sample documents. She wraps up with a full-length practice test that emulates exam 77-728, together with solutions to each of the exam challenges.
- Managing workbooks
- Referencing data in another workbook
- Protecting worksheets and workbooks
- Creating custom formats
- Creating custom styles and themes
- Creating advanced formulas
- Defining named ranges and objects
- Crafting advanced charts and tables