Chances are good that Excel 2019, as it comes when first installed, is not always the best fit for the way you use the program. For that reason, Excel 2019 offers an amazing variety of ways to customize and configure the program’s settings so that they better suit your needs and the way you like to work.
This Tutorial covers the most important methods for customizing Excel settings and features. The tutorial looks at three basic areas where you can tailor the program to your individual needs:
⇒ The first place ripe for customization is the Quick Access toolbar. Not only can you control which Excel command buttons (on and off of the Ribbon) appear on this toolbar, but you can also assign macros you create to this toolbar, making them instantly accessible.
⇒ The second place where you may want to make extensive modifications is to the default settings (also referred to as options) that control any number of program assumptions and basic behaviors.
⇒ The third place where you can customize Excel is in the world of add-ins, those small, specialized utilities (sometimes called applets) that extend the built-in Excel features by attaching themselves to the main Excel program. Excel Add-ins provide a wide variety of functions and are available from a wide variety of sources, including the original Excel 2019 program, the Microsoft Office website, and various and sundry third-party vendors.
Using Excel’s Own Add-ins
Office Add-ins aren’t the only ones that you can use to extend Excel’s features. You can also use built-in add-ins created by Microsoft or third-party Excel Add-ins that you can purchase from a wide variety of vendors. Before you can use any Excel Add-in program, the add-in must be installed in the proper folder on your hard drive, and then you must click the add-in in the Add-Ins dialog box. (See Image Below)
There are two different types of Excel Add-in programs immediately available that you can use to extend the features in Excel 2019:
Excel Add-ins: This group of add-ins (also known as automation add-ins) is designed to extend the data analysis capabilities of Excel. These include Analysis ToolPak, Euro Currency Tools, and Solver.
COM Add-ins: COM (Component Object Model) add-ins are designed to extend Excel’s capability to deal with and analyze large amounts of data in data models (collections of related database tables).
These include Inquire, Microsoft Office Power Pivot for Excel, and Power View.
When you first install Excel 2019, the add-in programs included with Excel are not loaded and therefore are not yet ready to use. To load any or all of these add-in programs, you follow these steps:
1. Click the File menu button, click Excel Options or press Alt+FT to open the Excel Options dialog box, and then click the Add-Ins tab.
The Add-Ins tab lists all the names, locations, and types of the add-ins to which you have access.
2. (Optional) In the Manage drop-down list box at the bottom, Excel Add-Ins is selected by default. If you want to activate one or more of your COM add-ins, click COM Add-Ins from the Manage drop down list.
3. Click the Go button.
If Excel 2019 Add-Ins was selected in the Manage drop-down list box, Excel opens the Add-Ins dialog box (similar to the one shown in Figure below), showing all the names of the built-in add-in programs you can load. If COM Add-Ins was clicked, the COM Add-Ins dialog box appears instead. (See Image Below)
4. Click the check boxes for each add-in program that you want to be loaded in the Add-Ins or COM Add-Ins dialog box.
Click the name of the add-in in the Add-Ins Available list box to display a brief description of its function at the bottom of this dialog box.
5. Click the OK button to close the Add-Ins or COM Add-Ins dialog
Excel Add-in programs are saved in a special file format identified with the.XLL or .XLAM (for Excel Add-in) filename extension. These files are normally saved inside the Library folder (sometimes in their own sub-folders) that is located in the Office 16 folder. The Office 16 folder, in turn, is located in your Microsoft Office folder inside the Program Files folder on your hard drive (often designated as the C:\ drive).In other words, the path is c:\Program Files\Microsoft Office\Office16\Library
After an add-in program has been installed in the Library folder, its name then appears in the list box of the Add-Ins dialog box.